Merge two DataFrames based on columns and values of a specific column with Pandas in Python 3.x

  • A+
Category:Languages

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 

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!

 


You can melt df2 and then merge using the default 'inner' merge

df3 = df1.merge(df2.melt(id_vars = ['A', 'B'], var_name='date'))      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 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: