How to merge two dataframes two columns in pandas python

Question

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

Possible duplicate: stackoverflow.com/questions/65892265/… — Quang Hoang
– Quang Hoang, Commented Sep 2, 2022 at 17:28

mozway · Accepted Answer · 2022-09-02 18:12:14Z

2

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

edited Sep 2, 2022 at 18:12

answered Sep 2, 2022 at 17:31

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to merge two dataframes two columns in pandas python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related