Python Groupby & Counting first in Series, sorting by month

user_18865803 0
Viewed 40

I have a pandas dataframe (this is an example, actual dataframe is a lot larger):

data = [['345', 1, '2022_Jan'], ['678', 1, '2022_Jan'], ['123', 1, '2022_Feb'], ['123', 1, '2022_Feb'], ['345', 0, '2022_Mar'], ['678', 1, '2022_Mar'], ['901', 0, '2022_Mar'], ['678', 1, '2022_Mar']]

df = pd.DataFrame(data, columns = ['ID', 'Error Count', 'Year_Month'])

The question I want answered is :How many IDs have errors?

I want to get an output that groups by 'Year_Month' and counts 1 for each ID that occurs in each month. In other words, I want to count only 1 for each ID in a single month.  

When I group by 'Year_Month' & 'ID': df.groupby(['Year_Month', 'ID']).count()

it will give me the following output (current output link below) with the total Error Count for each ID, but I only want to count each ID once. I also want the Year_Month to be ordered chronologically, not sure why it's not when my original dataframe is in order by month in the Year_Month column.

My current output

Desired output

pandas-groupby python
3 Answers

Here's one way:

(df
    .groupby(['Year_Month', 'ID']) # group by the two columns
    .sum('Error Count')['Error Count'] # aggregate the sum over error count
    .apply(lambda x: int(bool(x)))) # convert to boolean and back to int
    .to_frame('Error Count') # add name back to applied column
)

                Error Count
Year_Month ID
2022_Feb   123            1
2022_Jan   345            1
           678            1
2022_Mar   345            0
           678            1
           901            0
user_78679680

Are these actually duplicate records? Are you sure you don't want to record that user 123 had two errors in February?

If so, drop duplicates first, then groupby and sum over Error Count. The .count() method doesn't do what you think it does:

df.drop_duplicates(["ID", "Year_Month"]) \
  .groupby(["Year_Month", "ID"])["Error Count"] \
  .sum()

Output:

In [3]: counts = df.drop_duplicates(["ID", "Year_Month"]) \
   ...:            .groupby(["Year_Month", "ID"])["Error Count"] \
   ...:            .sum()

In [4]: counts
Out[4]:
Year_Month  ID
2022_Feb    123    1
2022_Jan    345    1
            678    1
2022_Mar    345    0
            678    1
            901    0
Name: Error Count, dtype: int64

As far as sorting, you'd want to convert "Year_Month" to a datetime object, because right now they're just being sorted as strings:

In [5]: "2022_Feb" < "2022_Jan"
Out[5]: True

Here's how you could do that:

In [6]: counts.sort_index(level=0, key=lambda ym: pd.to_datetime(ym, format="%Y_%b"))
Out[6]:
Year_Month  ID
2022_Jan    345    1
            678    1
2022_Feb    123    1
2022_Mar    345    0
            678    1
            901    0
Name: Error Count, dtype: int64
user_62987120

Here is another way to do that

Converting the sum to a boolean with astype(bool) to return True or False, based on values being 0 or non-zero, and then to 0 or 1 with astype(int)


df.groupby(['Year_Month','ID'])['Error Count'].sum().astype(bool).astype(int)

Year_Month  ID 
2022_Feb    123    1
2022_Jan    345    1
            678    1
2022_Mar    345    0
            678    1
            901    0
Name: Error Count, dtype: int32

To sort, assign the outcome to a dataframe and then apply the ddejohn solution to sort

counts = df.groupby(['Year_Month','ID'])['Error Count'].sum().astype(bool).astype(int)

counts.sort_index(level=0, key=lambda ym: pd.to_datetime(ym, format="%Y_%b")) # ddejohn: https://stackoverflow.com/a/71927886/3494754 answer above


Year_Month  ID 
2022_Jan    345    1
            678    1
2022_Feb    123    1
2022_Mar    345    0
            678    1
            901    0
Name: Error Count, dtype: int32
user_34947540
Related