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