GroupBy using TimeGrouper does not work

[aschilling]

BUG: TimeGrouper not too friendly with other groups, e.g.

df.set_index('Date').groupby([pd.TimeGrouper('6M'),'Branch']).sum()
should work

---

Hi everybody,

I found two issues with TimeGrouper:

1. TimeGrouper does not work at all:

Let's take the following example:

df = pd.DataFrame({
'Branch' : 'A A A A A B'.split(),
'Buyer': 'Carl Mark Carl Joe Joe Carl'.split(),
'Quantity': [1,3,5,8,9,3],
'Date' : [
DT.datetime(2013,1,1,13,0),
DT.datetime(2013,1,1,13,5),
DT.datetime(2013,10,1,20,0),
DT.datetime(2013,10,3,10,0),
DT.datetime(2013,12,2,12,0),
DT.datetime(2013,12,2,14,0),
]})

gr = df.groupby(pd.TimeGrouper(freq='6M'))

def testgr(df):
print df

gr.apply(testgr)

This will raise the Exception: "Exception: All objects passed were None"

2. With previous Panda's version it was not possible to combine TimeGrouper with another criteria such as "Branch" in my case.

Thank you very much

Andy

[jreback]

You need to set_index as TimeGrouper operates on the index

In [15]: df
Out[15]: 
  Branch Buyer                Date  Quantity
0      A  Carl 2013-01-01 13:00:00         1
1      A  Mark 2013-01-01 13:05:00         3
2      A  Carl 2013-10-01 20:00:00         5
3      A   Joe 2013-10-03 10:00:00         8
4      A   Joe 2013-12-02 12:00:00         9
5      B  Carl 2013-12-02 14:00:00         3

In [16]: df.set_index('Date').groupby(pd.TimeGrouper('6M')).sum()
Out[16]: 
            Quantity
2013-01-31         4
2013-07-31       NaN
2014-01-31        25

[aschilling]

Thanks and is it possible to combine TimeGrouper with another criteria?

[jreback]

In [28]: df.set_index('Date').groupby(pd.TimeGrouper('6M'))['Quantity'].apply(lambda x: x.count())
Out[28]: 
2013-01-31    2
2013-07-31    0
2014-01-31    4
dtype: int64

[aschilling]

Hi jreback, thanks for your reply. Sorry but I do not understand your solution, how can I use it to groupby the TimeGrouper criteria and for example by 'Branch' ?

[jreback]

This is actuallly a bit tricky

In [38]: df.set_index('Date').groupby(pd.TimeGrouper('6M')).apply(lambda x: x.groupby('Branch').sum())
Out[38]: 
                   Quantity
           Branch          
2013-01-31 A              4
2014-01-31 A             22
           B              3

[jreback]

@hayd is there a better way to do this you think?

[hayd]

I'm afraid I have no idea!

[jreback]

I woudl have thought:

df.set_index('Date').groupby([pd.TimeGrouper('6M'),'Branch']).sum()

should work....

[aschilling]

I tried that but already but it raises the exception: "TypeError: 'TimeGrouper' object is not callable"

[jreback]

did you see my prior comment?

[hayd]

I would have thought that would work too....

I had tried a few variations of your solution... None of which I could get working (hence the other issue I posted) :)

[aschilling]

@jreback Yes, but that does not work for me either, because I need to apply a self defined function to the formed GroupBy Object. If I already use the simple function above with your solution:

df.groupby(pd.TimeGrouper('6M')).apply(lambda x: x.groupby('Branch').apply(testgr))

It raises: "AttributeError: 'DataFrame' object has no attribute 'name'"

[jreback]

This is basically a composition operation, you group by time, then apply a function

which happens to group by branch then operates, so u need to operate on the inner function

This is quite tricky in that your function should return a scalar value on the single passed series

In [44]: def testf(s):
   ....:     return s.sum()
   ....: 

In [46]: df.set_index('Date').groupby(pd.TimeGrouper('6M')).apply(lambda x: x.groupby('Branch')['Quantity'].apply(testf))
Out[46]: 
            Branch
2013-01-31  A          4
2014-01-31  A         22
            B          3
dtype: int64

[aschilling]

@jreback Thanks! Unfortunately, it does not solve my problem, as I need to operate on various columns in my function. Is there any possibility to pass the Buyer column to the function?

df.set_index('Date').groupby(pd.TimeGrouper('6M')).apply(lambda x: x.groupby('Branch')[['Buyer', 'Quantity']].apply(testgr)) # doubles the buyer names