Using the groupby function in pandas, how can I create new dataframe columns that hold sums for each-CodePudding

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)