Pandas: returns the number of occurrences by date

Question

Suppose I have the following DataFrame

dic = {"Date": ["04-Jan-16", "04-Jan-16", "04-Jan-16", "05-Jan-16", "05-Jan-16"], "Col": ['A', 'A', 'B', 'A', 'B']} df = pd.DataFrame(dic) df Col Date 0 A 04-Jan-16 1 A 04-Jan-16 2 B 04-Jan-16 3 A 05-Jan-16 4 B 05-Jan-16 

I need to return a second DataFrame that lists the number of occurrences of A and B per day. i.e.

  AB Date 04-Jan-16 2 1 05-Jan-16 1 1 

I have a feeling that this concerns "groupby", but I don’t know enough about it to get it in the format above ^

Answer 1

Use the pivot_table method:

 In [116]: df.pivot_table(index='Date', columns='Col', aggfunc='size') \ .rename_axis(None, axis=1) Out[116]: AB Date 04-Jan-16 2 1 05-Jan-16 1 1 

or unstack () :

 In [121]: df.groupby(['Date', 'Col']).size().unstack('Col') Out[121]: Col AB Date 04-Jan-16 2 1 05-Jan-16 1 1 

Answer 2

You can use the built-in pandas for this pd.crosstab

 pd.crosstab(df.Date, df.Col) Col AB Date 04-Jan-16 2 1 05-Jan-16 1 1