pythonpandasdataframesplit-apply-combine

Pandas combine dataframes by stacking columns with values on matching condition


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

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
...

Solution

  • 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