CODEWITHSUNDEEP
pythonpandas
Number Logic
Number Logic

Reputation: 894

Pandas groupby stored in a new dataframe

I have the following code:

import pandas as pd
df1 = pd.DataFrame({'Counterparty':['Bank','Bank','GSE','PSE'],
            'Sub Cat':['Tier1','Small','Small', 'Small'],
            'Location':['US','US','UK','UK'],
            'Amount':[50, 55, 65, 55],
            'Amount1':[1,2,3,4]})

df2=df1.groupby(['Counterparty','Location'])[['Amount']].sum()
df2.dtypes
df1.dtypes

The df2 data frame does not have the columns that I am aggregating across ( Counterparty and Location). Any ideas why this is the case ? Both Amount and Amount1 are numeric fields. I just want to sum across Amount and aggregate across Amount1

Upvotes: 2

Views: 3223

Answers (2)

jezrael
jezrael

Reputation: 862581

For columns from index add as_index=False parameter or reset_index:

df2=df1.groupby(['Counterparty','Location'])[['Amount']].sum().reset_index()
print (df2)
  Counterparty Location  Amount
0         Bank       US     105
1          GSE       UK      65
2          PSE       UK      55

df2=df1.groupby(['Counterparty','Location'], as_index=False)[['Amount']].sum()
print (df2)
  Counterparty Location  Amount
0         Bank       US     105
1          GSE       UK      65
2          PSE       UK      55

If aggregate by all columns here happens automatic exclusion of nuisance columns - column Sub Cat is omitted:

df2=df1.groupby(['Counterparty','Location']).sum().reset_index()
print (df2)
  Counterparty Location  Amount  Amount1
0         Bank       US     105        3
1          GSE       UK      65        3
2          PSE       UK      55        4


df2=df1.groupby(['Counterparty','Location'], as_index=False).sum()

Upvotes: 7

A.Kot
A.Kot

Reputation: 7903

Remove the double brackets around the 'Amount' and make them single brackets. You're telling it to only select one column.

Upvotes: 0

Related Questions