How to merge two dataframes two columns in pandas python-CodePudding

I have 2 df as below

[df1] is the main dataframe with normal index 0 1 2 3...

[df2] just like a dictionary dataframe, and the index is datetimeindex

[df1]
       name  date
0      A     2022-01-01
1      B     2022-01-01
2      C     2022-02-02
3      D     2022-02-02
.      .     .
.      .     .
.      .     .

[df2]
            A  B  C  D . . . . 
2022-01-01  1  3  5  7 . . . .
2022-02-02  2  4  6  8 . . . .
.           .  .  .  . . . . . 
.           .  .  .  . . . . . 
.           .  .  .  . . . . .

and I want to get the answer below

[final df]
      name date        value
0     A    2022-01-01  1
1     B    2022-01-01  3
2     C    2022-02-02  6
3     D    2022-02-02  8

how can I do it? thanks a lot

CodePudding user response：

One possibility, using melt and merge:

df1.merge(df2.melt('index', var_name='name').rename(columns={'index': 'date'}))

Alternative if df2's "index" is the index

df1.merge(df2.reset_index()
             .melt('index', var_name='name')
             .rename(columns={'index': 'date'})
)

# or
df1.merge(df2.stack().rename('value'),
          left_on=['date', 'name'], right_index=True)

output:

   index name        date  value
0      0    A  2022-01-01      1
1      1    B  2022-01-01      3
2      2    C  2022-02-02      6
3      3    D  2022-02-02      8