CODEWITHSUNDEEP
python-2.7pandasresampling
tnknepp
tnknepp

Reputation: 6263

Resample pandas dataframe and count instances

If I have a dataframe such as: 

index = pd.date_range(start='2014 01 01 00:00', end='2014 01 05 00:00', freq='12H')
df = pd.DataFrame(pd.np.random.randn(9),index=index,columns=['A'])

df
Out[5]: 
                            A
2014-01-01 00:00:00  2.120577
2014-01-01 12:00:00  0.968724
2014-01-02 00:00:00  1.232688
2014-01-02 12:00:00  0.328104
2014-01-03 00:00:00 -0.836761
2014-01-03 12:00:00 -0.061087
2014-01-04 00:00:00 -1.239613
2014-01-04 12:00:00  0.513896
2014-01-05 00:00:00  0.089544

And I want to resample to daily frequency, it is quite easy: 

df.resample(rule='1D',how='mean')

Out[6]: 
                   A
2014-01-01  1.544650
2014-01-02  0.780396
2014-01-03 -0.448924
2014-01-04 -0.362858
2014-01-05  0.089544

However, I need to track how many instances are going into each day. Is there a good pythonic way of using resample to both perform the specified "how" operation AND track number of data points going into each mean value, e.g. yielding

Out[6]: 
                       A    Instances
    2014-01-01  1.544650    2
    2014-01-02  0.780396    2
    2014-01-03 -0.448924    2
    2014-01-04 -0.362858    2
    2014-01-05  0.089544    2

Upvotes: 2

Views: 2844

Answers (1)

Dan Allan
Dan Allan

Reputation: 35245

Conveniently, how accepts a list:

df1 = df.resample(rule='1D', how=['mean', 'count'])

This will return a DataFrame with a MultiIndex column: one level for 'A' and another level for 'mean' and 'count'. To get a simple DataFrame like the desired output in your question, you can drop the extra level like df1.columns = df1.columns.droplevel(0) or, better, you can do your resampling on df['A'] instead of df.

Upvotes: 4

Related Questions