Geek Answers Handbook

Re-fetching a multi-index DataFrame

I want to reformat a DataFrame using a multi-index containing both a datetime column and another key. The dataframe looks like this:

import pandas as pd from StringIO import StringIO csv = StringIO("""ID,NAME,DATE,VAR1 1,a,03-JAN-2013,69 1,a,04-JAN-2013,77 1,a,05-JAN-2013,75 2,b,03-JAN-2013,69 2,b,04-JAN-2013,75 2,b,05-JAN-2013,72""") df = pd.read_csv(csv, index_col=['DATE', 'ID'], parse_dates=['DATE']) df.columns.name = 'Params'

Since resampling is only allowed for datatime indexes, I thought unstacking would help another column. And indeed it is, but I cannot lay it down again after that.

 print df.unstack('ID').resample('W-THU') Params VAR1 ID 1 2 DATE 2013-01-03 69 69.0 2013-01-10 76 73.5

But then stacking 'ID' again leads to an index error:

 print df.unstack('ID').resample('W-THU').stack('ID') IndexError: index 0 is out of bounds for axis 0 with size 0

Oddly enough, I can stack another column level with both:

 print df.unstack('ID').resample('W-THU').stack(0)

and

 print df.unstack('ID').resample('W-THU').stack('Params')

An index error also occurs if I change the order (swap) to both column levels. Does anyone know how to overcome this problem?

+6
source share
1 answer

This example executes an odd column "NAME", which is silently deleted, but causes problems when re-stacking. The code below worked for me

 print df[['VAR1']].unstack('ID').resample('W-THU').stack('ID') Params VAR1 DATE ID 2013-01-03 A 69.0 B 69.0 2013-01-10 A 76.0 B 73.5
+8
source

More articles:


All Articles