使用Pandas将具有键重复的数据帧合并

Question

我有两个数据框，都有一个可能有重复的关键列，但是数据框大多数具有相同的重复键。我想在该键上合并这些数据框，但是在这种情况下，当两个数据框具有相同的重复项时，应分别合并这些重复项。此外，如果一个数据框比另一个数据框具有更多的关键字重复项，我希望它的值填充为NaN。例如：

df1 = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K2', 'K2', 'K3'],
                    'A':   ['A0', 'A1', 'A2', 'A3', 'A4', 'A5']}, 
                   columns=['key', 'A'])
df2 = pd.DataFrame({'B':   ['B0', 'B1', 'B2', 'B3', 'B4', 'B5', 'B6'],
                    'key': ['K0', 'K1', 'K2', 'K2', 'K3', 'K3', 'K4']}, 
                   columns=['key', 'B'])

  key   A
0  K0  A0
1  K1  A1
2  K2  A2
3  K2  A3
4  K2  A4
5  K3  A5

  key   B
0  K0  B0
1  K1  B1
2  K2  B2
3  K2  B3
4  K3  B4
5  K3  B5
6  K4  B6

我将尝试获得以下输出。

   key    A   B
0   K0   A0  B0
1   K1   A1  B1
2   K2   A2  B2
3   K2   A3  B3
6   K2   A4  NaN
8   K3   A5  B4
9   K3  NaN  B5
10  K4  NaN  B6

基本上，我想将重复的 K2 键视为 K2_1、K2_2……然后在数据框上执行 how='outer' 合并。你有什么想法吗?

- dcmm88

Answer 1

更快速地重新启动

%%cython
# using cython in jupyter notebook
# in another cell run `%load_ext Cython`
from collections import defaultdict
import numpy as np

def cg(x):
    cnt = defaultdict(lambda: 0)

    for j in x.tolist():
        cnt[j] += 1
        yield cnt[j]


def fastcount(x):
    return [i for i in cg(x)]

df1['cc'] = fastcount(df1.key.values)
df2['cc'] = fastcount(df2.key.values)

df1.merge(df2, how='outer').drop('cc', 1)

更快的答案; 不可扩展的

def fastcount(x):
    unq, inv = np.unique(x, return_inverse=1)
    m = np.arange(len(unq))[:, None] == inv
    return (m.cumsum(1) * m).sum(0)

df1['cc'] = fastcount(df1.key.values)
df2['cc'] = fastcount(df2.key.values)

df1.merge(df2, how='outer').drop('cc', 1)

old answer

df1['cc'] = df1.groupby('key').cumcount()
df2['cc'] = df2.groupby('key').cumcount()

df1.merge(df2, how='outer').drop('cc', 1)

- piRSquared

---

有没有更快的方法？我正在使用大约4M条目的数据帧，虽然合并基本上是瞬间完成的，但是cumcount()调用大约需要一分钟。 - dcmm88

@dcmm88 我特别编写了这个函数，请检查一下是否能改善情况。 - piRSquared

“np.arange... == inv” 这行代码里，你是想用 np.equal 吧？因为至少对我来说，“==”的结果是布尔值。尽管我使用了 np.equal，但仍然在那里遇到了内存错误，也许我的数据框太大了？ - dcmm88

@dcmm88 是的。很糟糕。不具伸缩性。仍在努力做些什么。 - piRSquared

@dcmm88 我已经优化了性能，但需要使用Cython。如果你在使用Jupyter Notebook的话，应该没问题。 - piRSquared

Answer 2

df1.set_index('key', inplace=True)

df2.set_index('key', inplace=True)

merged_df = pd.merge(df1, df2, left_index = True, right_index = True, how= 'inner')
merged_df.reset_index('key', drop=False, inplace=True)

- Adarsh Puri

---

通常情况下，如果您能解释您添加的关键字而不是仅提供代码答案，那会更有帮助。 - M. Zhang

这对我来说并没有去除重复项。 - ferreiradev