比较熊猫中列行列表和使用过滤器的情况

Question

sales = [(3588, [1,2,3,4,5,6], [1,38,9,2,18,5]),
         (3588, [2,5,7], [1,2,4,8,14]),
         (3588, [3,10,13], [1,3,4,6,12]),
         (3588, [4,5,61], [1,2,3,4,11,5]),
         (3590, [3,5,6,1,21], [3,10,13]),
         (3590, [8,1,2,4,6,9], [2,5,7]),
         (3591, [1,2,4,5,13], [1,2,3,4,5,6])
        ]
labels = ['goods_id', 'properties_id_x', 'properties_id_y']

df = pd.DataFrame.from_records(sales, columns=labels)

df
Out[4]:
goods_id    properties_id_x properties_id_y
0   3588    [1, 2, 3, 4, 5, 6]  [1, 38, 9, 2, 18, 5]
1   3588    [2, 5, 7]   [1, 2, 4, 8, 14]
2   3588    [3, 10, 13] [1, 3, 4, 6, 12]
3   3588    [4, 5, 61]  [1, 2, 3, 4, 11, 5]
4   3590    [3, 5, 6, 1, 21]    [3, 10, 13]
5   3590    [8, 1, 2, 4, 6, 9]  [2, 5, 7]
6   3591    [1, 2, 4, 5, 13]    [1, 2, 3, 4, 5, 6]

具有商品及其特性的df。需要逐行比较properties_id_x和properties_id_y，并且只返回列表中同时包含"1"和"5"的行。不知道该怎么做。

期望输出：

0   3588    [1, 2, 3, 4, 5, 6]  [1, 38, 9, 2, 18, 5]
6   3591    [1, 2, 4, 5, 13]    [1, 2, 3, 4, 5, 6]

Answer 1

选项1:

In [176]: mask  = df.apply(lambda r: {1,5} <= (set(r['properties_id_x']) & set(r['properties_id_y'])), axis=1)

In [177]: mask
Out[177]:
0     True
1    False
2    False
3    False
4    False
5    False
6     True
dtype: bool

In [178]: df[mask]
Out[178]:
   goods_id     properties_id_x       properties_id_y
0      3588  [1, 2, 3, 4, 5, 6]  [1, 38, 9, 2, 18, 5]
6      3591    [1, 2, 4, 5, 13]    [1, 2, 3, 4, 5, 6]

备选方案2:

In [183]: mask = df.properties_id_x.map(lambda x: {1,5} <= set(x)) & df.properties_id_y.map(lambda x: {1,5} <= set(x))

In [184]: df[mask]
Out[184]:
   goods_id     properties_id_x       properties_id_y
0      3588  [1, 2, 3, 4, 5, 6]  [1, 38, 9, 2, 18, 5]
6      3591    [1, 2, 4, 5, 13]    [1, 2, 3, 4, 5, 6]

Answer 2

您还可以使用dict交集。

df["intersect"] = df.apply(lambda x: set(x["properties_id_x"]).intersection(x["properties_id_y"]), axis=1)
df[df["intersect"].map(lambda x: (1 in x) and (5 in x))]

>> 0   3588    [1, 2, 3, 4, 5, 6]  [1, 38, 9, 2, 18, 5]
>> 6   3591    [1, 2, 4, 5, 13]    [1, 2, 3, 4, 5, 6]