I am new to python so maybe a silly and easy question.
I have two data frames, the first data frame have a percentage column which needs to applied on column A, B, C in Dataframe2. Any suggestions ?
Dataframe 1
Region Percentage
South 34.4%
North 27.0%
West 21.2%
Eastern 14.8%
other 2.6%
Dataframe 2 Week ending Upper Limit Forecasted value Lower Limit State Forecasting Date
22/05/2022 2112.2 2316.4 2525.9 CA 30/05/2022
29/05/2022 2123.8 2331.1 2523.4 CA 30/05/2022
5/06/2022 2136.5 2340.5 2553.8 CA 30/05/2022
12/06/2022 2148.466667 2353.433333 2562.266667 CA 30/05/2022
19/06/2022 2160.616667 2365.483333 2576.216667 CA 30/05/2022
26/06/2022 2172.766667 2377.533333 2590.166667 CA 30/05/2022
Desired Data frame
Region Week ending Upper Limit Forecasted value Lower Limit State Forecasting Date
South 22/05/2022 726.6 796.8 868.9 CA 30/05/2022
North 22/05/2022 570.3 625.4 682.0 CA 30/05/2022
West 22/05/2022 447.8 491.1 535.5 CA 30/05/2022
Eastern 22/05/2022 312.6 342.8 373.8 CA 30/05/2022
other 22/05/2022 54.9 60.2 65.7 CA 30/05/2022
South 29/05/2022 730.6 801.9 868.0 CA 30/05/2022
North 29/05/2022 573.4 629.4 681.3 CA 30/05/2022
West 29/05/2022 450.2 494.2 535.0 CA 30/05/2022
Eastern 29/05/2022 314.3 345.0 373.5 CA 30/05/2022
other 29/05/2022 55.2 60.6 65.6 CA 30/05/2022
South 5/06/2022 735.0 805.1 878.5 CA 30/05/2022
North 5/06/2022 576.9 631.9 689.5 CA 30/05/2022
West 5/06/2022 452.9 496.2 541.4 CA 30/05/2022
Eastern 5/06/2022 316.2 346.4 378.0 CA 30/05/2022
other 5/06/2022 55.5 60.9 66.4 CA 30/05/2022
Please find below excel image, hopefully above data with excel image would make more sense. I would have love to attach excel if they would have let me. Thanks in advance on any assistance.
CodePudding user response：
Well, let's do this ;)
1. Because we don't have common keys (columns), we can't merge them, so the trick is to make both DF same size.
1.1 Make df1 to 30 rows, repeating same row block:
df1 = pd.concat([df1]*6, ignore_index=True)
1.2 Make df2 to 30 rows repeating by index this time:
df2 = df2.loc[df2.index.repeat(5)].reset_index(drop=True)
2. Now that we have 2 dfs with same size (length), we can concat() them:
df3 = pd.concat([df1, df2], axis=1)
3. Time now to multiply the 3 columns by the "Percentage" (I've removed the "%" to make it easier) one:
df3[["Upper Limit", "Forecasted value", 'Lower limit']] = (df3[["Upper Limit", "Forecasted value", 'Lower limit']].multiply(df3["Percentage"], axis="index")/100).round(1)
df3
Please accept ✅ this answer if it solved your problem :)
Otherwise mention me (using @) in comment while telling me what's wrong ;)