Pandas unack not working

Question

Initially, I had DF with 1 column of actions indexed using DatetimeIndex:

In [371]: dates
2013-12-29 19:21:00    action1
2013-12-29 19:21:01    action2
2013-12-29 19:21:11    action1
2013-12-29 19:21:13    action2
                           ...
In [372]: dates.index
    Out[372]: 
    <class 'pandas.tseries.index.DatetimeIndex'>
    [2013-12-29 19:02:27, ..., 2014-01-13 16:30:31]
    Length: 108957, Freq: None, Timezone: None

I want to build the number of actions of a certain type vs day

So, I grouped the actions by date using agg

grouped = dates.groupby([dates.index.to_period(freq = 'D'), 'actiontype']).agg(len)

What gave me the multi-index series:

...
2014-01-13  action1       435
            action2       2067
..
2014-01-14  action1       455
            action2       1007
...

This seems to be exactly what I need.

But when you tried the unstackseries to get rid of MultiIndex and build my data and got an error:

In [379]: grouped.unstack()

ValueError: freq not specified and cannot be inferred from first element

What is my mistake here? Thanks.

Answer 1

If you need to use .unstack(), and it does not work with this multi-index, then starting with non-indexed data

index                 mydate     action
    0    2000-12-29 00:10:00    action1
    1    2000-12-29 00:20:00    action2
    2    2000-12-29 00:30:00    action2
    3    2000-12-29 00:40:00    action1
    4    2000-12-29 00:50:00    action1
    5    2000-12-31 00:10:00    action1
    6    2000-12-31 00:20:00    action2
    7    2000-12-31 00:30:00    action2

you can do something like

df['day'] = df['mydate'].apply(lambda x: x.split()[0])
counts = df.groupby(['day', 'action']).agg(len)

, datetime datetime, , , . pandas , counts.unstack()

             mydate         
action      action1  action2
day                         
2000-12-29        3        2
2000-12-31        1        2