[FIXED] Groupby count() not working with datetime field

Issue

Im working with dataframes. I was trying to group the total count of records for each date. Data types of the 2 columns used are:

1. date (datetime64)
2. total_count (int64)

date         total_count
2023-01-27      1
2023-01-27      3
2023-01-27      1
2023-01-27      8
2023-01-27      1

From above, you can see for the above date we should get a result of 14 if we groupby date and use the count() aggregate function. However, I the result I am getting is 5. Below is the code I am using and the result I got.

df.groupby('date')['total_count'].count()

Result:

date         count
2023-01-27    5

I would really appreciate it if anyone could help me figure out what I could be doing wrong here.

Thanks in advance.

Solution

use sum() instead of count()

df.groupby('date')['total_count'].sum()

Answered By - Dean Taler