1

I have DataFrame that looks 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 a DataFrame that 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 a series not a DataFrame which is close but still not what I want. Please help

swimfar2
  • 103
  • 8
howRU
  • 13
  • 3
  • 1
    Does this answer your question? [How can I pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe) – G. Anderson Jan 20 '22 at 15:51
  • that is very close. how can I get pd.pivot( data=df, index='foo', columns='bar', values= count(bar)) – howRU Jan 20 '22 at 15:56

1 Answers1

1

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
BoomBoxBoy
  • 1,770
  • 1
  • 5
  • 23