how to create column that count number of rows per each date of id-CodePudding

I have df like this

   id   datetime
0   a   2022-01-18
1   a   2022-01-19
2   a   2022-01-20
3   b   2022-01-20
4   b   2022-01-20
5   c   2022-01-18

and I want df which looks something like

    id  2022-01-20  2022-01-19   2022-01-18
0   a   1           1            1
1   b   2           0            0
2   c   0           0            1

I've tried

    df.groupby(['id', 'datetime']).size()
    
id  datetime
a   2022-01-18   1
    2022-01-19   1
    2022-01-20   1
b   2022-01-20   2
c   2022-01-18   1

it returns series not dataframe which is close but still not what I want. pls help

CodePudding user response：

You can do that with the following code

df = df.groupby(['id','date']).size().unstack(fill_value=0).reset_index()
df.columns.name = None

And as a result

df.head()

  id  2022-01-18  2022-01-19  2022-01-20
0  a           1           1           1
1  b           0           0           2
2  c           1           0           0