Pandas:我如何看到数据框架中两个列表之间的重叠?

Question

我有一个包含两列的数据框架，每列都包含列表。我想确定两列中列表之间的重叠。

例如:

df = pd.DataFrame({'one':[['a', 'b', 'c'], ['d', 'e', 'f'], ['h', 'i', 'j']], 
'two':[['b', 'c', 'd'], ['f', 'g', 'h',], ['l', 'm', 'n']]})

one         two
0   [a, b, c]   [b, c, d]
1   [d, e, f]   [f, g, h]
2   [h, i, j]   [l, m, n]

最终，我希望它看起来像:

one         two             overlap
0   [a, b, c]   [b, c, d]       [b, c]
1   [d, e, f]   [f, g, h]       [f]
2   [h, i, j]   [l, m, n]       []

Answer 1

没有有效的向量方法来执行此操作，最快的方法将是具有set相交的列表推导:

df['overlap'] = [list(set(a)&set(b)) for a,b in zip(df['one'], df['two'])]

输出:

one        two overlap
0  [a, b, c]  [b, c, d]  [b, c]
1  [d, e, f]  [f, g, h]     [f]
2  [h, i, j]  [l, m, n]      []

Answer 2

这是一种使用applymap将列表转换为集合并使用set.intersection查找重叠的方法:

df.join(df.applymap(set).apply(lambda x: set.intersection(*x),axis=1).map(list).rename('overlap'))

Answer 3

使用pandas

Pandas的方法是这样的-

f = lambda row: list(set(row['one']).intersection(row['two']))
df['overlap'] = df.apply(f,1)
print(df)

one        two overlap
0  [a, b, c]  [b, c, d]  [b, c]
1  [d, e, f]  [f, g, h]     [f]
2  [h, i, j]  [l, m, n]      []

apply函数逐行查找(axis=1)，并在列one和列two的列表中找到set.intersection()。然后以列表的形式返回结果。

Apply方法不是最快的，但在我看来可读性很好。但既然你的问题没有提到速度作为一个标准，这不会是一个问题。

此外，您可以使用这两个表达式中的任何一个作为lambda函数，因为它们都执行相同的任务—

#Option 1:
f = lambda x: list(set(x['one']) & set(x['two']))
#Option 2:
f = lambda x: list(set(x['one']).intersection(x['two']))

使用Numpy

您也可以使用numpy方法np.intersect1d以及2系列的映射。

import numpy as np
import pandas as pd
df['overlap'] = pd.Series(map(np.intersect1d, df['one'], df['two']))
print(df)

one        two overlap
0  [a, b, c]  [b, c, d]  [b, c]
1  [d, e, f]  [f, g, h]     [f]
2  [h, i, j]  [l, m, n]      []

---

添加一些参考基准-

%timeit [list(set(a)&set(b)) for a,b in zip(df['one'], df['two'])]        #list comprehension
%timeit df.apply(lambda x: list(set(x['one']).intersection(x['two'])),1)  #apply 1
%timeit df.apply(lambda x: list(set(x['one']) & set(x['two'])),1)         #apply 2
%timeit pd.Series(map(np.intersect1d, df['one'], df['two']))              #numpy intersect1d

6.99 µs ± 17.3 ns per loop (mean ± std. dev. of 7 runs, 100,000 loops each)
167 µs ± 830 ns per loop (mean ± std. dev. of 7 runs, 10,000 loops each)
166 µs ± 338 ns per loop (mean ± std. dev. of 7 runs, 10,000 loops each)
84.1 µs ± 270 ns per loop (mean ± std. dev. of 7 runs, 10,000 loops each)