python - 连接两个数据框，其中列值（一组）是另一个数据框的子集

Question

我有两个数据框：

df1 = pd.DataFrame([[set(['foo', 'baz'])],
                    [set(['bar', 'baz'])]], columns=['items'])



    items
0   {foo, baz}
1   {bar, baz}

df2 = pd.DataFrame([[set(['bar', 'baz', 'foo']), 1],
                    [set(['bar', 'baz', 'foo']), 2],
                    [set(['bar', 'baz', 'foo']), 3],
                    [set(['one', 'two', 'bar']), 2]], columns=['items', 'other'])



    items           other
0   {foo, bar, baz} 1
1   {foo, bar, baz} 2
2   {foo, bar, baz} 3
3   {two, one, bar} 2

目标是加入df2其中df1的值df1.items是 的子集的地方df2.items。两列都是 set()

就上下文而言，这是在实施先验算法后将关联规则与客户购买结合起来。

添加预期输出：

df3 = pd.DataFrame([[[set(['foo', 'baz'])], set(['bar', 'baz', 'foo']), 1],
                    [[set(['foo', 'baz'])], set(['bar', 'baz', 'foo']), 2],
                    [[set(['foo', 'baz'])], set(['bar', 'baz', 'foo']), 3],
                    [[set(['bar', 'baz'])], None, None]], columns=['items', 'items', 'other'])


    items           items           other
0   [{foo, baz}]    {foo, bar, baz} 1.0
1   [{foo, baz}]    {foo, bar, baz} 2.0
2   [{foo, baz}]    {foo, bar, baz} 3.0
3   [{bar, baz}]    None    NaN

标签： pythonpandasdataframe

Answer 1

创建你的数据框

import pandas as pd

df1 = pd.DataFrame({'key': [1, 1],
                    'id': [0, 1],
                    'items': [set(['foo', 'baz']), set(['bar', 'baz'])]})

df2 = pd.DataFrame({'key': [1, 1, 1, 1],
                    'items': [set(['bar', 'baz', 'foo']), set(['bar', 'baz', 'foo']), set(['bar', 'baz', 'foo']), set(['one', 'two', 'bar'])],
                    'other': [1, 2, 3, 2]
                   })

然后做一个笛卡尔积

merged_df = df1.merge(df2, on='key')
merged_df

   key  id     items_x          items_y  other
0    1   0  {baz, foo}  {foo, baz, bar}      1
1    1   0  {baz, foo}  {foo, baz, bar}      2
2    1   0  {baz, foo}  {foo, baz, bar}      3
3    1   0  {baz, foo}  {one, bar, two}      2
4    1   1  {baz, bar}  {foo, baz, bar}      1
5    1   1  {baz, bar}  {foo, baz, bar}      2
6    1   1  {baz, bar}  {foo, baz, bar}      3
7    1   1  {baz, bar}  {one, bar, two}      2

定义您的自定义函数，看看它是否适用于一种情况

def check_if_all_in_list(list1, list2):
    return all(elem in list2 for elem in list1)

check_if_all_in_list(merged_df['items_x'][0], merged_df['items_y'][0])
True

创建你的比赛

merged_df['check'] = merged_df.apply(lambda row: check_if_all_in_list(row['items_x'], row['items_y']), axis=1)
merged_df

   key  id     items_x          items_y  other  check
0    1   0  {baz, foo}  {foo, baz, bar}      1   True
1    1   0  {baz, foo}  {foo, baz, bar}      2   True
2    1   0  {baz, foo}  {foo, baz, bar}      3   True
3    1   0  {baz, foo}  {one, bar, two}      2  False
4    1   1  {baz, bar}  {foo, baz, bar}      1   True
5    1   1  {baz, bar}  {foo, baz, bar}      2   True
6    1   1  {baz, bar}  {foo, baz, bar}      3   True
7    1   1  {baz, bar}  {one, bar, two}      2  False

现在过滤掉你不想要的东西

mask = (merged_df['check']==True)
merged_df[mask]

   key  id     items_x          items_y  other  check
0    1   0  {baz, foo}  {foo, baz, bar}      1   True
1    1   0  {baz, foo}  {foo, baz, bar}      2   True
2    1   0  {baz, foo}  {foo, baz, bar}      3   True
4    1   1  {baz, bar}  {foo, baz, bar}      1   True
5    1   1  {baz, bar}  {foo, baz, bar}      2   True
6    1   1  {baz, bar}  {foo, baz, bar}      3   True