如何在数据框架中跨成对行并删除熊猫中的非交流元素

Question

我有此数据框：

import pandas as pd
data = {'small group': [['a1', 'a2'], ['a2', 'a3'],['a3','a4'], ['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4'], ['c1', 'c2'], ['c2', 'c3'], ['f1', 'f2']],
        'all_groups': [[['a1', 'a2'], ['a2', 'a3'],['a3','a4']], [['a1', 'a2'], ['a2', 'a3'],['a3','a4']],
                       [['a1', 'a2'], ['a2', 'a3'],['a3','a4']],
                   [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']], [['d1', 'd2'], ['d2', 'd3'],
                    ['d3', 'd4']], [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']], [['c1', 'c2'], ['c2', 'c3']],
                   [['c1', 'c2'], ['c2', 'c3']], [['f1', 'f2']]],
        'name':[['Alina','Kate'],['Alina','Kate','Diana'],['Kate','Diana'],['Mike','Bob'],['Ian','Lili'],['George','Cloud','Ian','Petro'],['Jone','Petro','Lili'],['Marinet','Yu','Chloe','Ian'],['Nate','Rose']],
        'day': [28, 28,28, 18, 18, 18, 20, 20, 3],

        }
df = pd.DataFrame(data)

输出：

+----+---------------+--------------------------------------------+-------------------------------------+-------+
|    | small group   | all_groups                                 | name                                |   day |
|----+---------------+--------------------------------------------+-------------------------------------+-------|
|  0 | ['a1', 'a2']  | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] | ['Alina', 'Kate']                   |    28 |
|  1 | ['a2', 'a3']  | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] | ['Alina', 'Kate', 'Diana']          |    28 |
|  2 | ['a3', 'a4']  | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] | ['Kate', 'Diana']                   |    28 |
|  3 | ['d1', 'd2']  | [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']] | ['Mike', 'Bob']                     |    18 |
|  4 | ['d2', 'd3']  | [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']] | ['Ian', 'Lili']                     |    18 |
|  5 | ['d3', 'd4']  | [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']] | ['George', 'Cloud', 'Ian', 'Petro'] |    18 |
|  6 | ['c1', 'c2']  | [['c1', 'c2'], ['c2', 'c3']]               | ['Jone', 'Petro', 'Lili']           |    20 |
|  7 | ['c2', 'c3']  | [['c1', 'c2'], ['c2', 'c3']]               | ['Marinet', 'Yu', 'Chloe', 'Ian']   |    20 |
|  8 | ['f1', 'f2']  | [['f1', 'f2']]                             | ['Nate', 'Rose']                    |     3 |
+----+---------------+--------------------------------------------+-------------------------------------+-------+

我需要在分组时将列与成对的名称相交。我需要与所有可能的组合相交，而不仅仅是按顺序结合。我了解如何为所有列表进行此操作，但我不明白如何成对输出。我想使用一个称为相交和聚合的函数。但是也许还有另一种方式

df_new = (df.groupby(['day','all_groups'])
        .agg({'name': intersect})).reset_index()

+----+-------+------------------------------+-------------------+--------------------------------------------+
|    |   day |  pair                        | name_intersect    | all_groups                                 |
|----+-------+------------------------------+-------------------+--------------------------------------------|
|  0 |    28 | [['a1', 'a2'], ['a2', 'a3']] | ['Alina', 'Kate'] | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] |
|  1 |    28 | [['a1', 'a2'], ['a3', 'a4']] | ['Kate']          | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] |
|  2 |    28 | [['a2', 'a3'], ['a3', 'a4']] | ['Kate', 'Diana'] | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] |
|  3 |    18 | [['d1', 'd2'], ['d2', 'd3']] | []                | [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']] |
|  4 |    18 | [['d1', 'd2'], ['d3', 'd4']] | []                | [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']] |
|  5 |    18 | [['d2', 'd3'], ['d3', 'd4']] | ['Ian']           | [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']] |
|  6 |    20 | [['c1', 'c2'], ['c2', 'c3']] | []                | [['c1', 'c2'], ['c2', 'c3']]               |
|  7 |     3 | ['f1', 'f2']                 | []                | [['f1', 'f2']]                             |
+----+-------+------------------------------+-------------------+--------------------------------------------+

，然后我想在name_intersect列中删除空行。并考虑对['d1'，'d2']等案例 [['d1'，'d2']，['d2'，'d3']， ['d3'，'d4']] ，因为使用此对，没有与其他人的交集（我想从此列表中删除这对）。因此，我想获得这样的输出：

+----+-------+------------------------------+-------------------+--------------------------------------------+
|    |   day |  pair                        | name_intersect    | all_groups                                 |
|----+-------+------------------------------+-------------------+--------------------------------------------|
|  0 |    28 | [['a1', 'a2'], ['a2', 'a3']] | ['Alina', 'Kate'] | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] |
|  1 |    28 | [['a1', 'a2'], ['a3', 'a4']] | ['Kate']          | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] |
|  2 |    28 | [['a2', 'a3'], ['a3', 'a4']] | ['Kate', 'Diana'] | [['a1', 'a2'], ['a2', 'a3'], ['a3', 'a4']] |
|  3 |    18 | [['d2', 'd3'], ['d3', 'd4']] | ['Ian']           | [['d2', 'd3'], ['d3', 'd4']]               |
+----+-------+------------------------------+-------------------+--------------------------------------------+

Answer 1

import pandas as pd
data = {'small group': [['a1', 'a2'], ['a2', 'a3'],['a3','a4'], ['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4'], ['c1', 'c2'], ['c2', 'c3'], ['f1', 'f2']],
        'all_groups': [[['a1', 'a2'], ['a2', 'a3'],['a3','a4']], [['a1', 'a2'], ['a2', 'a3'],['a3','a4']],
                       [['a1', 'a2'], ['a2', 'a3'],['a3','a4']],
                   [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']], [['d1', 'd2'], ['d2', 'd3'],
                    ['d3', 'd4']], [['d1', 'd2'], ['d2', 'd3'], ['d3', 'd4']], [['c1', 'c2'], ['c2', 'c3']],
                   [['c1', 'c2'], ['c2', 'c3']], [['f1', 'f2']]],
        'name':[['Alina','Kate'],['Alina','Kate','Diana'],['Kate','Diana'],['Mike','Bob'],['Ian','Lili'],['George','Cloud','Ian','Petro'],['Jone','Petro','Lili'],['Marinet','Yu','Chloe','Ian'],['Nate','Rose']],
        'day': [28, 28,28, 18, 18, 18, 20, 20, 3],

        }
df = pd.DataFrame(data)
newdf = df.shift(1).fillna('[]').reset_index().rename(columns={'name':'newcol'})

b=newdf.newcol.tolist()
a=df.name.tolist()


c = [[x for x in ii if  x in jj] for ii, jj in zip(a, b)]
df['name_intersect']=c


Out:
    small group                      all_groups  ... day  name_intersect
0    [a1, a2]  [[a1, a2], [a2, a3], [a3, a4]]  ...  28              []
1    [a2, a3]  [[a1, a2], [a2, a3], [a3, a4]]  ...  28   [Alina, Kate]
2    [a3, a4]  [[a1, a2], [a2, a3], [a3, a4]]  ...  28   [Kate, Diana]
3    [d1, d2]  [[d1, d2], [d2, d3], [d3, d4]]  ...  18              []
4    [d2, d3]  [[d1, d2], [d2, d3], [d3, d4]]  ...  18              []
5    [d3, d4]  [[d1, d2], [d2, d3], [d3, d4]]  ...  18           [Ian]
6    [c1, c2]            [[c1, c2], [c2, c3]]  ...  20         [Petro]
7    [c2, c3]            [[c1, c2], [c2, c3]]  ...  20              []
8    [f1, f2]                      [[f1, f2]]  ...   3              []

如果要将name_intersect用-1移动列，则

df['name_intersect']=df['name_intersect'].shift(-1).fillna('[]')

Out: 
    

small group                      all_groups                         name  day name_intersect
0    [a1, a2]  [[a1, a2], [a2, a3], [a3, a4]]                [Alina, Kate]   28  [Alina, Kate]
1    [a2, a3]  [[a1, a2], [a2, a3], [a3, a4]]         [Alina, Kate, Diana]   28  [Kate, Diana]
2    [a3, a4]  [[a1, a2], [a2, a3], [a3, a4]]                [Kate, Diana]   28             []
3    [d1, d2]  [[d1, d2], [d2, d3], [d3, d4]]                  [Mike, Bob]   18             []
4    [d2, d3]  [[d1, d2], [d2, d3], [d3, d4]]                  [Ian, Lili]   18          [Ian]
5    [d3, d4]  [[d1, d2], [d2, d3], [d3, d4]]  [George, Cloud, Ian, Petro]   18        [Petro]
6    [c1, c2]            [[c1, c2], [c2, c3]]          [Jone, Petro, Lili]   20             []
7    [c2, c3]            [[c1, c2], [c2, c3]]    [Marinet, Yu, Chloe, Ian]   20             []
8    [f1, f2]                      [[f1, f2]]                 [Nate, Rose]    3             []

现在，删除包含空列表的行> name_intersect 并获取df_final

df_final=df[df.astype(str)['name_intersect'] != '[]']

out:
         small group                      all_groups                         name  day name_intersect
0    [a1, a2]  [[a1, a2], [a2, a3], [a3, a4]]                [Alina, Kate]   28  [Alina, Kate]
1    [a2, a3]  [[a1, a2], [a2, a3], [a3, a4]]         [Alina, Kate, Diana]   28  [Kate, Diana]
4    [d2, d3]  [[d1, d2], [d2, d3], [d3, d4]]                  [Ian, Lili]   18          [Ian]
5    [d3, d4]  [[d1, d2], [d2, d3], [d3, d4]]  [George, Cloud, Ian, Petro]   18        [Petro]

整个代码：

df = pd.DataFrame(data)
newdf = df.shift(1).fillna('[]').reset_index().rename(columns={'name':'newcol'})

b=newdf.newcol.tolist()
a=df.name.tolist()


c = [[x for x in ii if  x in jj] for ii, jj in zip(a, b)]
df['name_intersect']=c

df['name_intersect']=df['name_intersect'].shift(-1).fillna('[]')

df_final=df[df.astype(str)['name_intersect'] != '[]']