Difficult groupby() and data analytics question (Python) (How to simplify)

user_12488382 0
Viewed 54

Here is a sample dataset.

import pandas as pd
import numpy as np
df = pd.DataFrame({ 
    'VipNo':np.repeat( range(3), 2 ),
    'Quantity': np.random.randint(200,size=6),
    'OrderDate': np.random.choice( pd.date_range('3/31/2018', periods=365, freq='D'), 6, replace=False)})
print(df)

   VipNo  Quantity  OrderDate
0      0       118 2019-02-16
1      0        49 2019-03-25
2      1       113 2018-05-11
3      1       127 2019-02-18
4      2       124 2018-12-27
5      2        71 2018-05-14

I want to create a new column that shows the percentage of each customer's total quantity purchased in 2018-10-01 - 2019-03-31 compared to that in 2018-03-31 - 2019-03-31. First I want to group the VipNo (each number represents an individual) because a person may have made multiple purchases. My dataset is big so a customer may have ordered multiple times within both of the time range and I would want to use the sum of the orders.

(df.assign(Quantity6=df['OrderDate'].between("2018-10-01","2019-03-31")*df.Quantity)
   .assign(Quantity12=df['OrderDate'].between("2018-03-31","2019-03-31")*df.Quantity)
   .groupby('VipNo')[['Quantity6','Quantity12']]
   .sum()
   .assign(output=lambda x: x['Quantity6']/x['Quantity12'])
)

    Quantity6 Quantity12 output
VipNo           
0   167 167 1.000000
1   127 240 0.529167
2   124 195 0.635897

This code now can achieve this goal and I know I can drop Quantity6 and Quantity12. But all I need is one column "output" which I want to put it in a dataframe I created earlier and I want to keep the code short. How can I create this output column without having to create other unnecessary columns?

Thank you in advance~

pandas data-analysis group-by python
1 Answers

Just a few modifications in your code:

import pandas as pd
import numpy as np
df = pd.DataFrame({ 
    'VipNo':np.repeat( range(3), 2 ),
    'Quantity': np.random.randint(200,size=6),
    'OrderDate': np.random.choice( pd.date_range('3/31/2018', periods=365, freq='D'), 6, replace=False)}
).set_index("VipNo")

(df.assign(Quantity6=df['OrderDate'].between("2018-10-01","2019-03-31")*df.Quantity)
   .assign(Quantity12=df['OrderDate'].between("2018-03-31","2019-03-31")*df.Quantity)
   .groupby('VipNo')[['Quantity6','Quantity12']]
   .sum()
   .assign(output=lambda x: x['Quantity6']/x['Quantity12'])
)["output"].to_frame().join(df)
user_65122530
Related