Change pandas pivot table bins range

58 views Asked by At

I have a pandas pivot table and I want to change the bin ranges to calculate from 0.

Hour_Num  (0, 12] (12, 15]    (15, 20]    (20, 24]
today_qty yesterday_qty   today_qty   yesterday_qty   today_qty   yesterday_qty   today_qty   yesterday_qty
channel_name                              
Ajio  22  68  0   55  0   53  0   32
Amazon    3   6   0   3   0   3   0   0
D2C   0   0   0   1   0   0   0   0
Flipkart  25  32  0   18  0   42  0   26
Limeroad  1   0   0   0   0   1   0   0
Meesho    3   7   0   3   0   1   0   0
Myntra    61  102 0   53  0   96  0   55
Nykaa 12  8   0   10  0   14  0   18
Snapdeal  0   0   0   0   0   0   0   1
TataCliq  3   9   0   2   0   5   0   5

I want the bins as (0, 12] (0, 15] (0, 20] (0, 24]. I want to show total no of orders from beginning of the day till 12 PM, 3 PM, 8 PM and midnight 12.

12: 12 PM 15: 3 PM 20: 8 PM 24: Midnight 12

This is my code:

df['Hour_Num'] = pd.cut(df.order_hour,[0,12,15,20,24])

pivot_df = df.pivot_table(index='channel_name', values=(['yesterday_qty','today_qty']), columns=['Hour_Num'], aggfunc=('sum')).fillna(0)

pivot_df = pivot_df.swaplevel(0,1, axis=1).sort_index(axis=1)

I appreciate any hint or solution. Thank you.

0

There are 0 answers