在Python 3.x中将基于列和特定列值的两个DataFrame与Pandas合并 [英] Merge two DataFrames based on columns and values of a specific column with Pandas in Python 3.x

问题描述

您好，我有一个问题无法解决. 我有以下两个DataFrame:

Hello i have a problem which i am not able to implement a solution on. I have following two DataFrames:

>>> df1
A  B   date
1  1  01-2016
2  1  02-2017
1  2  03-2017
2  2  04-2020

>>> df2
A  B  01-2016  02-2017  03-2017  04.2020
1  1    0.10    0.22     0.55     0.77
2  1    0.20    0.12     0.99     0.125
1  2    0.13    0.15     0.15     0.245
2  2    0.33    0.1      0.888    0.64

我要跟随的是DataFrame:

What i want is following DataFrame:

>>> df3
A  B   date      value
1  1  01-2016    0.10
2  1  02-2017    0.12
1  2  03-2017    0.15
2  2  04-2020    0.64

我已经尝试过以下操作:

I already tried following:

        summarize_dates = self.summarize_specific_column(data=df1, column='date')

        for date in summarize_dates:
            left_on = np.append(left_on, date)
            right_on = np.append(right_on, merge_columns.upper())
            result = pd.merge(left=df2, right=df1,
                              left_on=left_on, right_on=right_on,
                              how='right')
            print(result)

这不起作用.您能帮我建议更舒适的实施方式吗?非常感谢！

This does not work. Can you help me and suggest a more comfortable implementation? Manyy thanks in advance!

推荐答案

使用lookup

df1['value']=df2.set_index(['A','B']).lookup(df1.set_index(['A','B']).index,df1.date)
df1
Out[228]: 
   A  B     date  value
0  1  1  01-2016   0.10
1  2  1  02-2017   0.12
2  1  2  03-2017   0.15
3  2  2  04-2020   0.64

这篇关于在Python 3.x中将基于列和特定列值的两个DataFrame与Pandas合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！