QQ - IDE: Pycharm - I am using the below Dataframe Sample format
Name Business SegmentID Revenue Margin OrderQuantity
James Commercial 1001 1500 100 1
Joe Consumer 1002 800 10 1
James Commercial 1003 1900 110 2
James Commercial 1004 1800 105 3
Samuel Commercial 1005 1800 105 1
I want to aggregated it in the below format
Name Revenue Margin OrderQuantity
James 5200 315 6
Joe 800 10 1
Samuel 1800 105 1
What i have done so far ?
Data import from pyodbc, passed to a pandas dataframe
df.groupby(['Name']).Revenue.sum().Margin.sum().OrderQuantity.sum()
I was Unable to get the desired output. is there something i need to be focusing on specifically while using pyodbc.
The groupby aggregate is what you are looking for:
For example:
import numpy as np
import pandas as pd
d = {'Name': ['foo1','foo2','foo3','foo2','foo3'],
'Business': ['bar2','bar3','bar1','bar1','bar1'],
'ID':['1','2','3','4','5'],
'Revenue':[10000,12500,7500,3000,15000],
'Margin':[300,500,100,300,200],
'Quanity':[1,2,2,3,4]}
df = pd.DataFrame(data=d)
Output of df:
Business ID Margin Name Quanity Revenue
0 bar2 1 300 foo1 1 10000
1 bar3 2 500 foo2 2 12500
2 bar1 3 100 foo3 2 7500
3 bar1 4 300 foo2 3 3000
4 bar1 5 200 foo3 4 15000
Then using groupby:
groupby_df_agg = df.groupby(['Name'])[('Revenue', 'Margin', 'Quanity')].agg(['sum'])
print(groupby_df_agg)
Output
Revenue Margin Quanity
sum sum sum
Name
foo1 10000 300 1
foo2 15500 800 5
foo3 22500 300 6
To extend by more categorical variables you can use:
groupby_df_agg = df.groupby(['Name','Business'])[('Revenue', 'Margin','Quanity')].agg(['sum'])
Output
Revenue Margin
sum sum
Name Business
foo1 bar2 10000 300
foo2 bar1 3000 300
bar3 12500 500
foo3 bar1 22500 300