CODEWITHSUNDEEP
pandas
oammon
oammon

Reputation: 189

pandas groupby causing keyerror

I'm trying transform monthly time series data to quarterly then aggregate sums per quarter. I get a KeyError: 'date' when I run the following. 

abc= abc.set_index('date').to_period('Q').groupby(['origin', 
'date']).agg(sum)

However, when I reset the index as shown below, the code works. Why do I need to reset the index in order to use groupby on the origin and date fields? Is there a way to group without resetting the index? 

abc= abc.set_index('date').to_period('Q').reset_index().groupby(['origin', 
'date']).agg(sum)

Upvotes: 6

Views: 9385

Answers (1)

Allen Qin
Allen Qin

Reputation: 19957

Because when you do:

abc= abc.set_index('date').to_period('Q')

You've changed 'date' to a index.

Later you try to access 'date' as a column which doesn't exist any more, hence the error. a reset_index operation restores 'date' as a column so it works again.

You can retain 'date' as column while setting it as index by doing:

abc= abc.set_index('date', drop=False).to_period('Q').groupby(['origin', 
'date']).agg(sum)

Upvotes: 5

Related Questions