连接两个df及其列

Question

我有两份数据如下：

import pandas as pd

df1 = pd.DataFrame({'Airplanes' : ['U-2','B-52,P-51', 'F-16', 'MiG-21,F-16;A-10', 'P-51','A-10;P-51' ],
                    'Company' : ['Air_1', 'Air_3', 'Air_2','Air_1', 'Air_7', 'Air_3']})
------------------------------
          Airplanes Company
0               U-2   Air_1
1         B-52,P-51   Air_3
2              F-16   Air_2
3  MiG-21,F-16;A-10   Air_1
4              P-51   Air_7
5         A-10;P-51   Air_3
-------------------------------

df2 = pd.DataFrame({'Model' : ['U-2','B-52', 'F-16', 'MiG-21', 'P-51','A-10' ],
                    'Description' : ['Strong', 'Huge', 'Quick','Light', 'Silent', 'Comfortable']})
------------------------------
     Model  Description
0     U-2       Strong
1    B-52         Huge
2    F-16        Quick
3  MiG-21        Light
4    P-51       Silent
5    A-10  Comfortable
------------------------------

我想将df2的信息插入到df1中。特别是，描述列必须出现在df1中，同时尊重df1列“Airplanes”的分隔符。因此，在这种情况下，输出应该是：

---------------------------------------------------------
          Airplanes Company                 Description
0               U-2   Air_1                       Srong
1         B-52,P-51   Air_3                 Huge,Silent
2              F-16   Air_2                       Quick
3  MiG-21,F-16;A-10   Air_1     Light,Quick;Comfortable
4              P-51   Air_7                      Silent
5         A-10;P-51   Air_3          Comfortable;Silent
--------------------------------------------------------

我该怎么做？

Answer 1

您可以使用regex和str.replace

mapper = df2.set_index('Model')['Description'].to_dict()
regex = '|'.join(df2['Model'])
# 'U-2|B-52|F-16|MiG-21|P-51|A-10'

df1['Description'] = df1['Airplanes'].str.replace(regex, lambda m: mapper.get(m.group()))

产出：

          Airplanes Company              Description
0               U-2   Air_1                   Strong
1         B-52,P-51   Air_3              Huge,Silent
2              F-16   Air_2                    Quick
3  MiG-21,F-16;A-10   Air_1  Light,Quick;Comfortable
4              P-51   Air_7                   Silent
5         A-10;P-51   Air_3       Comfortable;Silent

Answer 2

你可以用split做explode，然后用map

df1['new'] = df1.Airplanes.str.split('[,|;]').explode().map(df2.set_index('Model')['Description']).groupby(level=0).agg(','.join)
df1
Out[62]: 
          Airplanes Company                      new
0               U-2   Air_1                   Strong
1         B-52,P-51   Air_3              Huge,Silent
2              F-16   Air_2                    Quick
3  MiG-21,F-16;A-10   Air_1  Light,Quick,Comfortable
4              P-51   Air_7                   Silent
5         A-10;P-51   Air_3       Comfortable,Silent

Answer 3

d=dict(df2.values)

df1['description']=df1['Airplanes'].str.split('[,|;]').apply(lambda x: ','.join([d[i] for i in x]))
df1

    Airplanes   Company     description
0   U-2               Air_1     Strong
1   B-52,P-51         Air_3     Huge,Silent
2   F-16              Air_2     Quick
3   MiG-21,F-16,A-10  Air_1     Light,Quick,Comfortable
4   P-51              Air_7     Silent
5   A-10,P-51         Air_3     Comfortable,Silent```