Search code examples
pythonpandasdatetimehour

Split overall volume in a date to ratios of hours python pandas

I am working on a requirement where I have two dataframes basically, one with date volume and the other with hour split up % as per the below images. Now, I am trying to obtain a third dataframe which will combine the above two dataframes in such a way the date volume is spread across in 24 hours based on the split up % obtained in dataframe 2. Could you please help me with achieving this? I managed to achieve this in Talend but wanted to perform and try the same in python, Thanks in advance

The below is the peice of code I worked to achive the % of share

weekday_split_group['%_share'] = weekday_split_group['Calls Presented']/weekday_split_group['Total']
weekday_split_group['%_share'].sum()
weekday_split_group[['%_share']]

date volume image enter image description here

hour_split_%(24 hours cycle) enter image description here

Final_output_expected enter image description here

Thanks

Solution

  • Your data is as images so I've synthesized the %_share data.

    Performing a Cartesian product with a merge() gets the data granularity, then a simple multiplication to calculate volume column.

    import datetime as dt
df_pct = pd.DataFrame({"hour":[i for i in range(24)], "%_share":np.random.dirichlet(np.ones(24),size=1)[0]})
df_val = pd.DataFrame({"Date":pd.date_range('2021-01-11','2021-01-12'),
             "yhat":[185.835,182.220]})

(df_val.assign(foo=1).merge(df_pct.assign(foo=1), on="foo")
 .assign(volume=lambda dfa: dfa["yhat"]*dfa["%_share"])
 .drop(columns=["foo","yhat","%_share"])
)

    output

        Date    hour    volume
0   2021-01-11  0   8.75130181190106
1   2021-01-11  1   3.800304203310593
2   2021-01-11  2   4.435534207384316
3   2021-01-11  3   0.8042485649482456
4   2021-01-11  4   0.30780836690202823
5   2021-01-11  5   0.4034771303868087
6   2021-01-11  6   9.757185959437273
7   2021-01-11  7   10.419717656981055
8   2021-01-11  8   17.5343995272983
9   2021-01-11  9   4.697775947037123
10  2021-01-11  10  1.3684239898273962
11  2021-01-11  11  1.842340112885734
12  2021-01-11  12  0.9282440981226737
13  2021-01-11  13  15.003403435577233
14  2021-01-11  14  0.8639868910813613
15  2021-01-11  15  11.385655349816991
16  2021-01-11  16  2.4637722378464177
17  2021-01-11  17  5.96733782226381
18  2021-01-11  18  4.352473102105978
19  2021-01-11  19  3.7736758659074923
20  2021-01-11  20  51.87723769628018
21  2021-01-11  21  1.3543189874277177
22  2021-01-11  22  17.20074788880526
23  2021-01-11  23  6.5416291464649685
24  2021-01-12  0   8.581065010168219
25  2021-01-12  1   3.7263778724527468
26  2021-01-12  2   4.349250912204751
27  2021-01-12  3   0.7886037264501806
28  2021-01-12  4   0.30182065066799896
29  2021-01-12  5   0.3956283945386191
30  2021-01-12  6   9.567381954576154
31  2021-01-12  7   10.217025595044463
32  2021-01-12  8   17.19330740637822
33  2021-01-12  9   4.606391331391312
34  2021-01-12  10  1.3418043932862385
35  2021-01-12  11  1.8065015490625471
36  2021-01-12  12  0.9101872067151698
37  2021-01-12  13  14.711546124416195
38  2021-01-12  14  0.8471799784370309
39  2021-01-12  15  11.164173152762677
40  2021-01-12  16  2.415845116261061
41  2021-01-12  17  5.851256749121055
42  2021-01-12  18  4.267805573039262
43  2021-01-12  19  3.700267529182679
44  2021-01-12  20  50.868083262120564
45  2021-01-12  21  1.327973771835654
46  2021-01-12  22  16.866146206570853
47  2021-01-12  23  6.414376533316364