Python pandas - MultiIndexed Series to Dataframe

Question

(Background: I'm trying to learn Pandas and matplotlib and get some nice graphs out of my irclogs.)

I have managed to parse some data into a multi-indexed Series (let's call itseries):

                        msgs
id    datetime_period
A     2014-07-04 07:00  1
      2014-07-04 08:00  2
      2014-07-08 11:00  5
B     2014-07-08 11:00  1
C     2014-07-04 07:00  2

For graphing, I'd like to organize it into a dataframe looking something like this:

index             A  B  C
2014-07-04 07:00  1  0  2
2014-07-04 08:00  2  0  0
2014-07-08 11:00  5  1  0

How would I go on doing that? I guess I could do something like this:

df = DataFrame({i: series.ix[i] for i in ['A', 'B', 'C'] })

but is there a more 'Pandas' way? (Or if there isn't, how would I retrieve the list ['A', 'B', 'C'] from the series, instead of writing it out explicitly as above?)

Alternatively (in the case it's easier to use), there's also available an original 'raw' dataframe that I used to construct the series in the first place. It contains timestamp for each message posted by each nick, like so:

timestamp              id 
2014-07-04 07:11:00    A
2014-07-04 07:12:32    C
2014-07-04 07:15:03    C
etc.

Answer 1

unstack can be used to pivot a level of a MultiIndex into columns. The fillna replaces missing values with 0, as shown in your desired output.

In [313]: series.unstack(level='id').fillna(0)
Out[313]: 
id                   A  B  C
datetime_period             
2014-07-04 07:00:00  1  0  2
2014-07-04 08:00:00  2  0  0
2014-07-08 11:00:00  5  1  0