How do I sum up values in a column into groups that match a given condition by date in pandas?

user_11028689 0
Viewed 99

I have a dataframe like this with age groups

    Date            AgeGroup            Quantity
1   2020-12-08      18 - 29             1
2   2020-12-08      30 - 49             4
3   2020-12-08      50 - 54             0
4   2020-12-08      55 - 59             1
5   2020-12-08      60 - 64             1
6   2020-12-08      65 - 69             0
7   2020-12-08      70 - 74             3
8   2020-12-08      75 - 79             2
9   2020-12-08      80+                 1
....
10  2020-12-09      18 - 29             0
11  2020-12-09      30 - 49             2
12  2020-12-09      50 - 54             1
13  2020-12-09      55 - 59             2
14  2020-12-09      60 - 64             3
15  2020-12-09      65 - 69             0
16  2020-12-09      70 - 74             1
17  2020-12-09      75 - 79             1
18  2020-12-09      80+                 1

I want to sum up the numbers for each date by three broader age groups like so:

1   2020-12-08      18 - 59             6
2   2020-12-08      60+                 7
3   2020-12-08      75+                 3
...
4   2020-12-09      18 - 59             5
5   2020-12-09      60+                 5
6   2020-12-09      75+                 2

(the first age group includes the ages up to 59, the second one has the ages over 60 and the third one has the ages over 75+).

I have tried the following:

df1 = df.loc[(df['AgeGroup'] == '18 - 29') | (df['AgeGroup'] == '30 - 49') | (df['AgeGroup'] == '50 - 54') | (df['AgeGroup'] == '55 - 59') , 'Quantity'].sum()

however, this lacks the breakdown by date as it just gives me the sum for these age groups for all dates.

I have also tried this.

df.groupby(['Date', 'AgeGroup'])['Quantity'].sum()
Date        AgeGroup         
2020-12-08  18 - 29               1
            30 - 49               4
            50 - 54               0
            55 - 59               2
            60 - 64               1
            65 - 69               0
            70 - 74               3
            75 - 79               2

2020-12-09  18 - 29               0
            30 - 49               2
            50 - 54               1
            55 - 59               2
            60 - 64               3
            65 - 69               0
            70 - 74               1
            75 - 79               1

I still cant figure out how to combine these age groups within the dates. thank you for any ideas.

pandas-groupby pandas dataframe python-3.x sum
1 Answers

You can get first numeric value by Series.str.extract, compare by 60 and set by np.where to 2 groups:

m = df['AgeGroup'].str.extract('(\d+)', expand=False).astype(int) < 60
df['AgeGroup'] = np.where(m, '18 - 59', '60+')

df1 = df.groupby(['Date', 'AgeGroup'])['Quantity'].sum()
print (df1)
Date        AgeGroup
2020-12-08  18 - 59     7
            60+         6
2020-12-09  18 - 59     5
            60+         5
Name: Quantity, dtype: int64
user_29010020
Related