Pandas combine dataframes by stacking columns with values on matching condition

Question

I want to combine dataframes 1 and 2 in the following way:

The date column is one key
The second key is the header of dataframe 1 with the Project variable of dataframe 2
In the new dataframe V1 corresponds to the value in dataframe 1 where these keys match
If the keys don't match S1, S2 and S3 values are null (e.g row 0)
If the keys match S1, S2, S3 values are joined from dataframe 2 (e.g rows 1, 2 and 3)

I have tried combinations of stacking to get to these results but I'm not being able to succeed, any ideas?

**Dataframe 1**
     Date           C0          C1       C2        C3
0  2021-03-24  2547.502499  220.815585  91.2  10.764182
1  2021-02-01  2147.502499  219.815585  62.2   8.764182

**Dataframe 2**
    Project   Date          S1    S2         S3
0      C1  2021-03-24  151.733282  67.2   1.882302
1      C1  2021-02-01     150.1  60.2     0.812302
2      C2  2021-03-24   15.15005  50.9  25.200000
**Expected Result**
     Date     Project    V1           S1         S2       S3
0  2021-03-24   C0   2547.502499     NaN         NaN      NaN
1  2021-03-24   C1   220.815585   151.733282    67.2   1.882302
2  2021-03-24   C2      62.2       15.15005     50.9  25.200000
3  2021-02-01   C1   219.815585      150.1      60.2   0.812302
...

score 1 · Accepted Answer · answered Mar 24 '21 at 23:28

Use stack and merge:

(df1.set_index('Date')
    .stack()
    .reset_index()
    .rename(columns = {'level_1' : 'Project', 0 : 'V1'})
    .merge(df2, on = ['Date','Project'], how = 'left')
)

output:

    Date        Project            V1        S1     S2          S3
--  ----------  ---------  ----------  --------  -----  ----------
 0  2021-03-24  C0         2547.5      nan       nan    nan
 1  2021-03-24  C1          220.816    151.733    67.2    1.8823
 2  2021-03-24  C2           91.2       15.1501   50.9   25.2
 3  2021-03-24  C3           10.7642   nan       nan    nan
 4  2021-02-01  C0         2147.5      nan       nan    nan
 5  2021-02-01  C1          219.816    150.1      60.2    0.812302
 6  2021-02-01  C2           62.2      nan       nan    nan
 7  2021-02-01  C3            8.76418  nan       nan    nan

Thank you for that, seems really simple for the 2 hours I just spent :') I'll try to understand it, thanks!! — beerzy, Mar 24 '21 at 23:46

Pandas combine dataframes by stacking columns with values on matching condition

1 Answers1