如何将两个dataframe映射为新列中重叠项的输出？

Question

我有两个数据帧：

data = {
    'values': ['Cricket', 'Soccer', 'Football', 'Tennis', 'Badminton', 'Chess'],
    'gems': ['A1K, A2M, JA3, AN4', 'B1, A1, Bn2, B3', 'CD1, A1', 'KWS, KQM', 'JP, CVK', 'KF, GF']  
}
df1 = pd.DataFrame(data)

df1

    values       gems
0   Cricket      A1K, A2M, JA3, AN4
1   Soccer       B1, A1, Bn2, B3
2   Football     CD1, A1
3   Tennis       KWS, KQM
4   Badminton    JP, CVK
5   Chess        KF, GF

第二数据帧

data2 = {
    '1C': ['B1', 'K1', 'A1K', 'J1', 'A4'],
    '02C': ['Bn2', 'B3', 'JK', 'ZZ', 'ko'],
    '34C': ['KF', 'CD1', 'B3','ji', 'HU']
}
df2 = pd.DataFrame(data2)

df2

    1C  02C 34C
0   B1  Bn2 KF
1   K1  B3  CD1
2   A1K JK  B3
3   J1  ZZ  ji
4   A4  ko  HU

我希望检查df1['gems']中df2每列中的df1['gems']项，并表示它们的计数和重叠项。预期产出为：

    values    gems                  1C  1CGroup   02C   02CGroup    34C 34CGroup
0   Cricket   A1K, A2M, JA3, AN4    1   A1K       0     NA          0   NA
1   Soccer    B1, A1, Bn2, B3       1   Bn2       2     Bn2, B3     1   B3
2   Football  CD1, A1               0   NA        0     NA          1   CD1
3   Tennis    KWS, KQM              0   NA        0     NA          0   NA
4   Badminton JP, CVK               0   NA        0     NA          0   NA
5   Chess     KF, GF                0   NA        0     NA          1   KF

Answer 1

首先创建一个组表：

df3 = (pd.merge(df1['gems'].str.split(',\s+').explode().reset_index(),
                df2.unstack().reset_index(level=0),
                left_on='gems', right_on=0, how='left'
               )
         .pivot_table(index='index',
                      columns=['level_0'],
                      values='gems',
                      aggfunc=list)
      )

输出：

level_0        02C     1C    34C
index                           
0              NaN  [A1K]    NaN
1        [Bn2, B3]   [B1]   [B3]
2              NaN    NaN  [CD1]
5              NaN    NaN   [KF]

然后生成计数并将所有内容与原始表连接：

pd.concat([df1,
           pd.concat([df3.add_suffix('Group').applymap(lambda x: ','.join(x) if isinstance(x, list) else x),
                      df3.fillna('').applymap(len)],
                     axis=1).sort_index(axis=1)
          ], axis=1)

输出：

      values                gems  02C 02CGroup   1C 1CGroup  34C 34CGroup
0    Cricket  A1K, A2M, JA3, AN4  0.0      NaN  1.0     A1K  0.0      NaN
1     Soccer     B1, A1, Bn2, B3  2.0  Bn2, B3  1.0      B1  1.0       B3
2   Football             CD1, A1  0.0      NaN  0.0     NaN  1.0      CD1
3     Tennis            KWS, KQM  NaN      NaN  NaN     NaN  NaN      NaN
4  Badminton             JP, CVK  NaN      NaN  NaN     NaN  NaN      NaN
5      Chess              KF, GF  0.0      NaN  0.0     NaN  1.0       KF

编辑：字符串联接和计数的备选方案

df3 = (pd.merge(df1['gems'].str.split(',\s+').explode().reset_index(),
                df2.unstack().reset_index(level=0),
                left_on='gems', right_on=0, how='left'
               )
         .pivot_table(index='index',
                      columns=['level_0'],
                      values='gems',
                      aggfunc=', '.join)
      )

pd.concat([df1,
           pd.concat([df3.add_suffix('Group'),
                      df3.applymap(lambda x: x.count(',')+1 if isinstance(x, str) else 0)],
                     axis=1).sort_index(axis=1)
          ], axis=1)

Answer 2

含有findall

对于df2中的每个列，在df1的gems列中查找列值的所有出现次数，然后map使用len来计算出现次数，还可以选择join使用str.join

for c in df2.columns:
    s = df1['gems'].str.findall('|'.join(df2[c]))

    df1[c] = s.map(len)
    df1[c + 'group'] = s.str.join(', ')

---

print(df1)

      values                gems  1C 1Cgroup  02C 02Cgroup  34C 34Cgroup
0    Cricket  A1K, A2M, JA3, AN4   1     A1K    0             0         
1     Soccer     B1, A1, Bn2, B3   1      B1    2  Bn2, B3    1       B3
2   Football             CD1, A1   0            0             1      CD1
3     Tennis            KWS, KQM   0            0             0         
4  Badminton             JP, CVK   0            0             0         
5      Chess              KF, GF   0            0             1       KF

Answer 3

首先str.split和explode列gems和reset_index保留原始索引。然后，对于df2的每一列，merge和分解的gem，groupby原始索引，并根据需要执行count和聚合 与join一起pd.concat合并每个列并连接到原始df1fillna如预期输出中所示，计数列为0

# one row per gem used in the merge
df_ = df1['gems'].str.split(', ').explode().reset_index()

res = (
    df1.join( #can join to df1 as we keep the original index value
        pd.concat([df_.merge(df2[[col]], left_on='gems', right_on=col)
                      .groupby('index') # original index in df1
                      [col].agg(**{col: 'count', # do each aggregation
                                   f'{col}Group':lambda x: ', '.join(x)}) 
                   for col in df2.columns], # do it for each column of df2
                  axis=1))
        .fillna({col:0 for col in df2.columns}) #fill the count columns with 0
)
print(res)
      values                gems   1C 1CGroup  02C 02CGroup  34C 34CGroup
0    Cricket  A1K, A2M, JA3, AN4  1.0     A1K  0.0      NaN  0.0      NaN
1     Soccer     B1, A1, Bn2, B3  1.0      B1  2.0  Bn2, B3  1.0       B3
2   Football             CD1, A1  0.0     NaN  0.0      NaN  1.0      CD1
3     Tennis            KWS, KQM  0.0     NaN  0.0      NaN  0.0      NaN
4  Badminton             JP, CVK  0.0     NaN  0.0      NaN  0.0      NaN
5      Chess              KF, GF  0.0     NaN  0.0      NaN  1.0       KF