python - 在 pandas 中查找具有不同列名的值

Question

我有 2 个 pandas 数据框 df1 和 df2

Name   No
A      1
A      2
B      5

Player Gender
A      F
B      M
C      F

我想使用 df2 中 gender 列的相应值在 df1 数据框中创建一个新列 sex 。用于查找的列是 df1 中的 Name 和 df2 中的 Player。

非常感谢任何帮助

Answer 1

使用map通过 df2 其中 set_index来自玩家列:

df1['sex'] = df1.Name.map(df2.set_index('Player')['Gender'])
print (df1)
  Name  No sex
0    A   1   F
1    A   2   F
2    B   5   M

这与 dict 的 map 相同:

d = df2.set_index('Player')['Gender'].to_dict()
print (d)
{'A': 'F', 'B': 'M', 'C': 'F'}
df1['sex'] = df1.Name.map(d)
print (df1)
  Name  No sex
0    A   1   F
1    A   2   F
2    B   5   M

或者:

print (pd.merge(df1,df2, left_on='Name', right_on='Player')
         .rename(columns={'Gender':'sex'})
         .drop('Player', axis=1))

  Name  No sex
0    A   1   F
1    A   2   F
2    B   5   M

第一个更快:

In [46]: %timeit (pd.merge(df1,df2, left_on='Name', right_on='Player').rename(columns={'Gender':'sex'}).drop('Player', axis=1))
The slowest run took 4.53 times longer than the fastest. This could mean that an intermediate result is being cached.
100 loops, best of 3: 2.53 ms per loop

In [47]: %timeit df1.Name.map(df2.set_index('Player')['Gender'])
The slowest run took 4.78 times longer than the fastest. This could mean that an intermediate result is being cached.
1000 loops, best of 3: 882 µs per loop