Pandas: How to Find a % of a Group?

user_9831961 0
Viewed 100

*** Disclaimer: I am a total noob. I am trying to learn Pandas by solving a problem at work. This is a subset of my total problem but I am trying to solve the pieces before I tackle the project. I appreciate your patience! ***

I am trying to find out what percentage each Fund is of the States total.

Concept: We have funds(departments) that are based in states. The funds have different levels of compensation for different projects. I first need to total(group) the funds so I know the total compensation per fund.

I also need to total(group) the compensation by state so I can later figure out the fund % by state.

I have converted my data to sample code here:

import pandas as pd

#sample data

data = {'Fund':['1000','1000','2000','2000','3000','3000','4000','4000'], 
    'State':['AL','AL','FL','FL','AL','AL','NC','NC'],
    'Compensation':[2000,2500,1500,1750,4000,3200,1450,3000]}

# Create DataFrame (employees) employees = pd.DataFrame(data)

If the pic doesn't come over here is what I did:

print(employees)
employees.groupby('Fund').Compensation.sum()
employees.groupby('State').Compensation.sum()

I've spent a good portion of the day on my actual data trying to figure out how to get the:

Fund's compensation is __% of total compensation for State or..

Fund_1000 is 38% of AL total compensation.

Thanks for your patience and your help!

John

pandas python
3 Answers

Here is one solution. You can first do a groupby to get to the lowest level of aggregation, and then use groupby transform to divide these values by state totals.

agg = df.groupby(['Fund','State'],as_index=False)['Compensation'].sum()
agg['percentage'] = (agg['Compensation'] / agg.groupby('State')['Compensation'].transform(sum)) * 100

agg.to_dict()
{'Fund': {0: '1000', 1: '2000', 2: '3000', 3: '4000'},
'State': {0: 'AL', 1: 'FL', 2: 'AL', 3: 'NC'},
 'Compensation': {0: 4500, 1: 3250, 2: 7200, 3: 4450},
 'percentage': {0: 38.46153846153847,
  1: 100.0,
  2: 61.53846153846154,
  3: 100.0}}
user_45204200

This should do the work:

df['total_state_compensataion'] = df.groupby('State')['Compensation'].transform(sum)
df['total_state_fund_compensataion'] = df.groupby(['State','Fund'])['Compensation'].transform(sum)
df['ratio']=df['total_state_fund_compensataion'].div(df['total_state_compensataion'])
>>>df.groupby(['State','Fund'])['ratio'].mean().to_dict()

out[1] {('AL', '1000'): 0.38461538461538464,
 ('AL', '3000'): 0.6153846153846154,
 ('FL', '2000'): 1.0,
 ('NC', '4000'): 1.0}
user_56490570

You can also calculate and merge data frames...

import pandas as pd

data = {
    "Fund": ["1000", "1000", "2000", "2000", "3000", "3000", "4000", "4000"],
    "State": ["AL", "AL", "FL", "FL", "AL", "AL", "NC", "NC"],
    "Compensation": [2000, 2500, 1500, 1750, 4000, 3200, 1450, 3000],
}
# Create dataframe from dictionary provided
df = pd.DataFrame.from_dict(data)

# first group compensation by state and fund 
df_fund = df.groupby(["Fund", "State"]).Compensation.sum().reset_index()

# Calculate Total by state in new df
df_total = df_fund.groupby("State").Compensation.sum().reset_index()

# Merge dataframes with total column
merged = df_fund.merge(df_total, how="outer", left_on="State", right_on="State")

#Add percentage col to merged dataframe. 
merged["percentage"] = merged["Compensation_x"] / merged["Compensation_y"] * 100
user_148591630
Related