pandas - 比较 Pandas 数据框并添加列

Question

我有如下两个数据框

df1     df2 
A       A   C
A1      A1  C1
A2      A2  C2
A3      A3  C3
A1      A4  C4
A2          
A3          
A4          

“A”列的值在 df2 的“C”列中定义。我想向 df1 添加一个新列，其中 B 列的值来自 df2 列“C”

最终的df1应该是这样的

df1
A   B
A1  C1
A2  C2
A3  C3
A1  C1
A2  C2
A3  C3
A4  C4

我可以遍历 df2 并将值添加到 df1，但由于数据量巨大，因此非常耗时。

    for index, row in df2.iterrows():
           df1.loc[df1.A.isin([row['A']]), 'B']= row['C']

有人可以帮助我理解如何在不循环 df2 的情况下解决这个问题。

谢谢

Answer 1

您可以使用 map按系列:

df1['B'] = df1.A.map(df2.set_index('A')['C'])
print (df1)
    A   B
0  A1  C1
1  A2  C2
2  A3  C3
3  A1  C1
4  A2  C2
5  A3  C3
6  A4  C4

它与 dict 的 map 相同:

d = df2.set_index('A')['C'].to_dict()
print (d)
{'A4': 'C4', 'A3': 'C3', 'A2': 'C2', 'A1': 'C1'}

df1['B'] = df1.A.map(d)
print (df1)
    A   B
0  A1  C1
1  A2  C2
2  A3  C3
3  A1  C1
4  A2  C2
5  A3  C3
6  A4  C4

时间:

len(df1)=7:

In [161]: %timeit merged = df1.merge(df2, on='A', how='left').rename(columns={'C':'B'})
1000 loops, best of 3: 1.73 ms per loop

In [162]: %timeit df1['B'] = df1.A.map(df2.set_index('A')['C'])
The slowest run took 4.44 times longer than the fastest. This could mean that an intermediate result is being cached.
1000 loops, best of 3: 873 µs per loop

len(df1)=70k:

In [164]: %timeit merged = df1.merge(df2, on='A', how='left').rename(columns={'C':'B'})
100 loops, best of 3: 12.8 ms per loop

In [165]: %timeit df1['B'] = df1.A.map(df2.set_index('A')['C'])
100 loops, best of 3: 6.05 ms per loop