python - 将一列合并到多列

Question

我有以下两个数据框DF1：

   location     vaccine1            vaccine2          vaccine3           vaccine4
0   Afghanistan Oxford/AstraZeneca  Pfizer/BioNTech   Sinopharm/Beijing  None
1   Albania     Oxford/AstraZeneca  Pfizer/BioNTech   Sinovac            Sputnik V
2   Algeria     Sputnik V           None              None               None
3   Andorra     Oxford/AstraZeneca  Pfizer/BioNTech   None               None

DF2：

    Vaccine             Efficacy
0   Oxford/AstraZeneca  0.70
1   Pfizer/BioNTech     0.95
2   Sinopharm/Beijing   0.79
3   Sinovac             0.50
4   Sputnik V           0.92

我知道您可以像下面这样合并，但该过程重复 4 次，效率低下：

v1 = pd.merge(df1, vacc_eff, how='left', left_on='vaccine1', right_on='Vaccine')[['location', 'Efficacy']]
v2 = pd.merge(df1, vacc_eff, how='left', left_on='vaccine2', right_on='Vaccine')[['location', 'Efficacy']]

vmerged = pd.merge(v1,v2,on=['location'])

如何将 DF2 列“功效”合并到 DF1 中的每个疫苗列，而无需一次又一次地编写相同的合并函数？

标签： pythonpandasmerge

Answer 1

这是您可以尝试的解决方案，stack + map然后unstack

map_ = vacc_eff.set_index('Vaccine')['Efficacy'].to_dict()

print(
    df1[['location', 'vaccine1', 'vaccine2']].set_index('location')
        .stack().map(map_).unstack()
)

---

             vaccine1  vaccine2
location                       
Afghanistan      0.70      0.95
Albania          0.70      0.95
Algeria          0.92       NaN
Andorra          0.70      0.95