Jack Cooper Jack Cooper - 21 days ago 13
Python Question

Pandas resample and add size as a column

Given a dataframe:

Index A B
2016-01-01 5 10
2016-01-01 1 2
2016-01-02 1 1


Is it possible to resample the DataFrame using sum and add a column to the end of the DataFrame which includes the count of observations in the bins such that the result is:

Index A B Count
2016-01-01 6 12 2
2016-01-02 1 1 1

Answer

You can use Resampler.agg or DataFrameGroupBy.agg:

df1 = df.resample('D').agg({'A':'sum', 'B':['sum', 'size']})
print (df1)
             B        A
           sum size sum
Index                  
2016-01-01  12    2   6
2016-01-02   1    1   1

df2 = df.groupby(level=0).agg({'A':'sum', 'B':['sum', 'size']})
print (df2)
             B        A
           sum size sum
Index                  
2016-01-01  12    2   6
2016-01-02   1    1   1

If need remove MultiIndex in columns:

df1 = df.resample('D').agg({'A':'sum', 'B':['sum', 'size']})
df1.columns = ['B','Count','A'] 
df1 = df1[['A','B','Count']]
print (df1)
            A   B  Count
Index                   
2016-01-01  6  12      2
2016-01-02  1   1      1

df2 = df.groupby(level=0).agg({'A':'sum', 'B':['sum', 'size']})
df2.columns = ['B','Count','A'] 
df2 = df2[['A','B','Count']]
print (df2)
            A   B  Count
Index                   
2016-01-01  6  12      2
2016-01-02  1   1      1