CODEWITHSUNDEEP
python-2.7pandas
g.humpkins
g.humpkins

Reputation: 341

Pandas - Indexing dataframes created with aggregator like .sum()

I am ultimately trying to merge two dataframes together, but I am running into an issue when I try to specify the column on which they should be merged.

Using the following code:

total_payments_by_state = test_data.groupby('Recipient_State')['Total_Amount_of_Payment_USDollars'].sum()

I was able to aggregate a total dollar amount by state. 

Recipient_State
AL                   245.55
AR                   751.22
AZ                  5455.80
CA                  6331.11
CO                   638.29

However, when I then try to index this to just see the variable Recipient_State with the following code:

print total_payments_by_state['Recipient_State']

I receive the following error:

KeyError: 'Recipient_State'

If someone could show me how to resolve this I would appreciate it.

Upvotes: 0

Views: 28

Answers (1)

EdChum
EdChum

Reputation: 394099

When you groupby a column it becomes the index so you can either call total_payments_by_state.reset_index() so that it is restored back as a columnor access the index total_payments_by_state.index

Example:

In [2]:

df = pd.DataFrame({'Recipient_State':['AL', 'AR', 'AZ','CA','CO'], 'Total_Amount_of_Payment_USDollars':np.random.randn(5)})
df
Out[2]:
  Recipient_State  Total_Amount_of_Payment_USDollars
0              AL                           0.401242
1              AR                           1.333060
2              AZ                          -0.515762
3              CA                           0.740358
4              CO                          -0.900249
In [3]:

gp = df.groupby('Recipient_State')['Total_Amount_of_Payment_USDollars'].sum()
gp
Out[3]:
Recipient_State
AL                 0.401242
AR                 1.333060
AZ                -0.515762
CA                 0.740358
CO                -0.900249
Name: Total_Amount_of_Payment_USDollars, dtype: float64
In [4]:

gp.index
Out[4]:
Index(['AL', 'AR', 'AZ', 'CA', 'CO'], dtype='object')
In [5]:

gp.reset_index()
Out[5]:
  Recipient_State  Total_Amount_of_Payment_USDollars
0              AL                           0.401242
1              AR                           1.333060
2              AZ                          -0.515762
3              CA                           0.740358
4              CO                          -0.900249

Upvotes: 1

Related Questions