How to create a pandas dataframe cross tab / summary table / pivot with different calculations

Question

Hi everyone I have a dataframe that looks like the following:

print(df):
TrxID       Price       Profit        Online      Satisfaction
###          10           10           Yes         25%
###          15           20           No          75%
###          18           -10           Yes         55%
###          10           30           No          70%
###          20           -50           Yes         79%
###          30           15           No          90%
###          30           15           No          15%

And I am hoping to create the following summary / cross tab / pivot table:

SatisfactionBins     SalesMade   AveSalePrice   CountofOnline    Profit      Profit/SalesMade     

20%                     1             30              0             15            1500%
40%                     1             10              1             10            1000%
60%                     1             18              1             -10           -1000%
80%                     3             15              1             0              0%
100%                    1             30              0             15            1500%

Is this the right way to go about it?

df['Profit/SalesMade'] = df['Profit'] / df['SalesMade']
probbins = [0,0.2,0.4,0.6,0.8,1]
df['SatisfactionBins'] = pd.cut(dfclubhouse['Satisfaction'],bins=probbins)
df.groupby('SatisfactionBins')[(df['SalesMade'].count()),(df['Price'].mean()), etc etc]

I'm hoping someone might be able to help with 'best practice' in how to code this but im not sure where to go from here. Any help would be great! Thanks!

BENY · Accepted Answer · 2020-05-25 13:31:47Z

1

Your pd.cut solution is correct , just need to change the groupby

df.groupby('SatisfactionBins').agg({'SalesMade':'count','Price':'mean'})

answered May 25, 2020 at 13:31

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SOK Over a year ago

Thanks @YOBEN_S. I have them working for count, mean and sum but any ideas on how to get the count where Online =='Yes' as a column and then also how to apply a percentage calculation df['Profit/SalesMade'] = df['Profit'] / df['SalesMade'] as a new column?

Collectives™ on Stack Overflow

How to create a pandas dataframe cross tab / summary table / pivot with different calculations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related