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.