Home > front end >  Using the groupby function in pandas, how can I create new dataframe columns that hold sums for each
Using the groupby function in pandas, how can I create new dataframe columns that hold sums for each

Time:09-13

Customer Service Type Revenue
foo. A. 25.
foo. B. 50.
foo. C 15.
bar A 25.
bar. b 10.

How can I get the table above to look like the one below:

Customer Service Type A Revenue Service Type B Revenue Service Type C Revenue
foo. 25 50. 15
bar 25 10 0

CodePudding user response:

You may try

#df = df.astype(str).apply(lambda x : x.str.strip('.'))

out = df.pivot(*df.columns).add_prefix('Service Type ')

CodePudding user response:

I tried this and it worked on my side:

data_stack = {'Customer': ['foo', 'foo', 'foo', 'bar', 'bar'], 'Service Type': ['A', 'B', 'C', 'A', 'B'],
'Revenue': [25,50,15,25,10]}
stack_df = pd.DataFrame(data=data_stack)

Then group the info per columns:

grouped_info = stack_df.groupby(['Customer', 'Service Type']).sum()

Unstack or unpivot the Service Types column:

final_df = grouped_info.unstack(level=-1)

Drop any unneeded columns or multiindex items:

final_df.columns = final_df.columns.droplevel()

Rename columns how you want them:

final_df.set_axis(['Service Type A Revenue', 'Service Type B Revenue', 'Service Type A Revenue'], axis=1, inplace=False)
  • Related