How to do Pandas Groupby Ratio?

user_14391779 0
Viewed 39

I have following dataframe.

          product   Dates   Times      Sale
level_1             
0        ACN    2020-12-23  09:15:00    25
1        ACN    2020-12-23  09:20:00    30
2        ACN    2020-12-24  09:15:00    50
3        ACN    2020-12-24  09:20:00    45
0        ACN2   2020-12-23  09:15:00    80
1        ACN2   2020-12-23  09:20:00    55
2        ACN2   2020-12-24  09:15:00    20
3        ACN2   2020-12-24  09:20:00    110

I want the ratio of Sales of product on same time with previous day for all products.

Say for Product ACN at 9:15 time on 24th december

sale ratio = 50/25=2 (here 25 is from previous day same time)

What i want is this.

      product   Dates   Times      Sale  Sale_ratio
level_1             
0        ACN    2020-12-23  09:15:00    25     Nan
1        ACN    2020-12-23  09:20:00    30     Nan
2        ACN    2020-12-24  09:15:00    50      2
3        ACN    2020-12-24  09:20:00    45      1.5
0        ACN2   2020-12-23  09:15:00    80     Nan
1        ACN2   2020-12-23  09:20:00    55     Nan
2        ACN2   2020-12-24  09:15:00    20      0.25
3        ACN2   2020-12-24  09:20:00    110     2
pandas python
1 Answers

Lets Try groupby, transform row/rwo.shift()

df['Sale_ratio']=df.groupby(['product','Times'])['Sale'].transform(lambda x: x/(x.shift()))



         product   Dates     Times  Sale  Sale_ratio
level_1                                                
0           ACN  2020-12-23  09:15:00    25         NaN
1           ACN  2020-12-23  09:20:00    30         NaN
2           ACN  2020-12-24  09:15:00    50        2.00
3           ACN  2020-12-24  09:20:00    45        1.50
0          ACN2  2020-12-23  09:15:00    80         NaN
1          ACN2  2020-12-23  09:20:00    55         NaN
2          ACN2  2020-12-24  09:15:00    20        0.25
3          ACN2  2020-12-24  09:20:00   110        2.00
user_89869750
Related