CODEWITHSUNDEEP
pythonpandasgroup-by
emax
emax

Reputation: 7235

Python: how to groupby over two columns?

I have a dataframe df that has information of job and age of people in cities

df
    User   City     Job             Age
0    A      x    Unemployed         33
1    B      x     Student           18
2    C      x    Unemployed         27
3    D      y  Data Scientist       28
4    E      y    Unemployed         45
5    F      y     Student           18

For each city I would like to compute the unemployment rate and the median of the age.

For the unemployment rate I did the following

## Count the people in each city
cust = insDataRed.groupby(['City'])['User'].count() ## Number of people for each city
cust = pd.DataFrame(cust)
cust.columns=['nCust']
cust['City']=cust.index
cust=cust.reset_index(drop=True)

## Count the people unemployed in each city
unempl = df[df['Job'] == 'Unemployed']
unempl = unempl.groupby(['City'])['Job'].count()
unempl = pd.DataFrame(unempl)
unempl.columns=['unempl']
unempl['City']=unempl.index
unempl=unempl.reset_index(drop=True)


# 1. Fraction of Unemployment
unRate = pd.merge(unempl, cust, on = 'City')
unRate['rate'] =(unRate['unempl']/unRate['nCust'])*100

Is there a more elegant solution? How can I compute the median value of the age for each city?

Upvotes: 1

Views: 614

Answers (1)

VnC
VnC

Reputation: 2016

If you want to do it only by the City:

df.groupby(by='City').median()

If you want to group by both City and Job:

df.groupby(by=['City', 'Job']).median()

Get unemployment rate per city:

import pandas as pd

df = pd.DataFrame({
    'User': ['A', 'B', 'C', 'D', 'E', 'F'], 'City': ['x', 'x', 'x', 'y', 'y', 'y'], 
    'Job': ['Unemployed', 'Student', 'Unemployed', 'Data Scientist', 'Unemployed', 'Student'],
    'Age':[33, 18, 27, 28, 45, 18],
})

df['count'] = 1
unmpl = df.groupby(by=['City', 'Job'])['count'].sum().reset_index()

unmpl_by_city = unmpl[unmpl['Job'] == 'Unemployed'].reset_index(drop=True)
count_by_city = df.groupby(by=['City'])['count'].sum().reset_index(drop=True)

frac_by_city = (unmpl_by_city['count'] * 100.0 / 
                count_by_city)

unmpl_by_city['frac'] = frac_by_city
unmpl_by_city

Upvotes: 2

Related Questions