I want to show the number of records from the first day to the last day in pandas.
I have an dataframe like this:
day category value
2022-07-01 A 1
2022-07-01 B 2
2022-07-03 A 3
2022-07-05 A 4
2022-07-07 B 5
2022-07-07 B 6
I want to put the value of category in column and show the number by date.
(I want to show the number of records by date from the first date to the last date.)
output may be :
day A B
2022-07-01 1 1
2022-07-02 0 0
2022-07-03 1 0
2022-07-04 0 0
2022-07-05 1 0
2022-07-06 0 0
2022-07-07 0 2
How can I do this?
>Solution :
You can do pd.crosstab then resample
#df.day = pd.to_datetime(df.day)
out = pd.crosstab(df.day,df.category).resample('1D').first().fillna(0).reset_index()
Out[607]:
category day A B
0 2022-07-01 1.0 1.0
1 2022-07-02 0.0 0.0
2 2022-07-03 1.0 0.0
3 2022-07-04 0.0 0.0
4 2022-07-05 1.0 0.0
5 2022-07-06 0.0 0.0
6 2022-07-07 0.0 2.0