从另一个数据帧的选定列中填充NaN值

Question

我有像这样的df1

       id        name  level personality      type  weakness    atk    def     hp  stage
0    53.0     Persian   40.0        mild    normal  fighting  104.0  116.0    NaN    2.0
1   126.0      Magmar   44.0      docile       NaN     water   96.0   83.0  153.0    1.0
2    57.0    Primeape    9.0      lonely  fighting    flying    NaN   66.0   43.0    2.0
3     3.0    Venusaur   44.0       sassy     grass      fire  136.0  195.0   92.0    3.0
4    11.0     Metapod    4.0       naive     grass      fire    NaN  114.0    NaN    2.0
5   126.0      Magmar   96.0      modest      fire     water   62.0  114.0    NaN    1.0
6   137.0     Porygon   96.0     relaxed       NaN  fighting   68.0   50.0  127.0    1.0
7    69.0  Bellsprout   84.0      lonely     grass      fire    NaN    NaN    NaN    1.0
8    10.0    Caterpie    3.0     serious       NaN    flying    NaN    NaN   15.0    1.0
9    12.0  Butterfree   12.0       hasty       NaN    flying   20.0    NaN    NaN    3.0
10   35.0    Clefairy   18.0      impish     fairy    poison   33.0    NaN    NaN    1.0
11   59.0    Arcanine   35.0      gentle      fire     water   45.0   60.0   80.0    2.0
12  111.0     Rhyhorn   31.0     naughty      rock     water   40.0    NaN  175.0    1.0
13  136.0     Flareon   75.0        bold       NaN     water    NaN  143.0    NaN    2.0
14   51.0     Dugtrio   82.0      gentle    ground     water  152.0  161.0  168.0    2.0
15   38.0   Ninetales    5.0       brave      fire     water    NaN  179.0  173.0    2.0
16  102.0   Exeggcute   88.0        rash       NaN      fire    NaN  124.0    NaN    1.0 
........

和df2 as

    weakness      type  count
3       fire     grass     11
10     water      fire      9
0   fighting    normal      6
4     flying  fighting      3
8     poison     fairy      3
6      grass     water      1
9       rock      fire      1
7     ground  electric      1

我想使用df2更新类型列中的NaN值，并在两个dfs中使用匹配的弱点列。例如，在df1的第8行和第9行中，“type”值为NaN。我想用df2更新df1中的弱点列。因此，8,9类型的值应该是“战斗”等。这类似于df2和df1之间的一对多关系

我试过了

df1.update(df2)

及

df1.fillna(df2)

但是他们没有给出期望的输出。任何帮助都将不胜感激

Answer 1

您可以从df2创建一个字典，其中弱点列作为键，类型列作为其各自的值，然后使用该字典使用fillna在df1中使用map创建类型列：

m = dict(zip(df2.weakness,df2.type))
df1.type = df1.type.fillna(df1.weakness.map(m))

印刷品：

>>> df1[['weakness','type']]

    weakness      type
0   fighting    normal
1      water      fire
2     flying  fighting
3       fire     grass
4       fire     grass
5      water      fire
6   fighting    normal
7       fire     grass
8     flying  fighting
9     flying  fighting
10    poison     fairy
11     water      fire
12     water      rock
13     water      fire
14     water    ground
15     water      fire
16      fire     grass

Answer 2

代码内联记录

# Merge both dataframes using "weakness" as key
df = pd.merge(df1, df2[['weakness', 'type']], 
               on="weakness",  suffixes=("", "_y"), how="left")
# Replace nans
df['type'].fillna(df['type_y'], inplace=True)
# Drop additional columns resulted from Merge
df.drop(columns=['type_y'])

Answer 3

1. 从df2创建一个系列，将weakness值映射到type值：

   mapping = df2.set_index("weakness")["type"]

2. 映射df1["weakness"]使用此映射创建默认值：

   defaults = df1["weakness"].map(mapping)

3. 将默认值用作fillna方法的参数：

   df1["type"] = df1["type"].fillna(defaults)