How can I count the number of occurrences of a value in column in a group by in a dataframe? (Python)

jartymcfly 0
Viewed 33

I have a dataframe with 3 columns. One column is a date column, other is an ID column and the other is a flag with 1 and 0 values indicating if that ID has a dog or not. It is something like that:

+-------------+------------+-----------+
|  date_col   |     ID     |    dog    |
+-------------+------------+-----------+
|  2020-01-01 |     1      |     1     |
+-------------+------------+-----------+
|  2020-01-01 |     2      |     0     |
+-------------+------------+-----------+
|  2020-05-01 |     1      |     1     |
+-------------+------------+-----------+
|  2020-05-01 |     2      |     0     |
+-------------+------------+-----------+
|  2020-05-01 |     1      |     1     |
+-------------+------------+-----------+
|  2020-06-01 |     2      |     0     |
+-------------+------------+-----------+
|  2020-07-01 |     3      |     1     |
+-------------+------------+-----------+
|  2020-08-01 |     1      |     1     |
+-------------+------------+-----------+

I would like to groupby the dataframe by date_col to count how many distinct ID-s have a dog in each date. The results should be something likt this:

+-------------+------------+-----------+
|  date_col   |  dog_count_uniqueID    |
+-------------+------------+-----------+
|  2020-01-01 |            1           |
+-------------+------------+-----------+
|  2020-05-01 |            1           |
+-------------+------------+-----------+
|  2020-06-01 |            0           |
+-------------+------------+-----------+
|  2020-07-01 |            1           |
+-------------+------------+-----------+
|  2020-08-01 |            1           |
+-------------+------------+-----------+

How can I do it?

unique count dataframe pandas python
1 Answers

Try this:

# Extract unique dates from the original frame
dates = df["date_col"].unique()

(
    df[df["dog"] == 1]     # select rows where dog = 1
    .groupby("date_col")   # group by date
    ["ID"].nunique()       # count unique IDs
    .reindex(dates, fill_value=0)  # add back the days with no dog
)
Code Different0
Related