Pandas dataframe groupby and percent based on multiple columns-CodePudding

I am looking to compute percent of 2 columns and augment to the original dataframe.

import numpy as np
import pandas as pd
np.random.seed(0)
df = pd.DataFrame({'state': ['CA', 'WA', 'CO', 'AZ'] * 3,
               'office_id': list(range(1, 7)) * 2,
               'counts': list(range(1, 3)) * 6,
               'sales_year': [np.random.randint(2019, 2021) for _ in range(12)],
               'sales': [np.random.randint(100000, 999999) for _ in range(12)]})

state_office = df.groupby(['state', 'office_id']).agg({'sales': 'sum'})
state = df.groupby(['sales_year']).agg({'sales': 'sum'})
state_office.div(state, level='state') * 100

I would like to compute the percent of sales for each group ['state', 'office_id', 'sales_year] and add to a new column called 'aggr_sales' (I would like to retain the original column names)
Compute percent of count for each group and add to a new column 'aggr_counts' (I would like to retain the original column names)
I would like to have a single dataframe with both the percents.

Appreciate any inputs.

Thanks, S

CodePudding user response：

Here is an answer for grouped state sales_year:

df['aggr_counts'] = (df.groupby(['state', 'sales_year'])
                       ['sales'].apply(lambda x: 100*x/x.sum())
                     )

output:

   state  office_id  counts  sales_year   sales  aggr_counts
0     CA          1       1        2019  474564   100.000000
1     WA          2       2        2020  835831    37.219871
2     CO          3       1        2020  836326    35.053616
3     AZ          4       2        2019  410744    29.372909
4     CA          5       1        2020  270584    25.895015
5     WA          6       2        2020  939052    41.816341
6     CO          1       1        2020  704474    29.527195
7     AZ          2       2        2020  641377   100.000000
8     CA          3       1        2020  774343    74.104985
9     WA          4       2        2020  470775    20.963789
10    CO          5       1        2020  845048    35.419188
11    AZ          6       2        2019  987633    70.627091