How to write a nested query with in python pandas?

user_6376935 0
Viewed 569

Hi all I am new to pandas. I need some help regarding how to write pandas query for my required output.

I want to retrieve output data like when 0 < minimum_age < 10 i need to get sum(population) for that 0 to 10 only when 10 < minimum_age < 20 i need to get sum(population) for that 10 to 20 only and then it continues

My Input Data Looks Like:

population,minimum_age,maximum_age,gender,zipcode,geo_id 
50,30,34,f,61747,8600000US61747 
5,85,NaN,m,64120,8600000US64120 
1389,10,34,m,95117,8600000US95117  
231,5,60,f,74074,8600000US74074
306,22,24,f,58042,8600000US58042

My Code:

import pandas as pd
import numpy as np
df1 = pd.read_csv("C:\Users\Rahul\Desktop\Desktop_Folders\Code\Population\population_by_zip_2010.csv")
df2=df1.set_index("geo_id")
df2['sum_population'] = np.where(df2['minimum_age'] < 10,sum(df2['population']),0)
print df2
pandas python-2.7 python-3.x python
1 Answers

You can try pandas cut along with groupby, 

df.groupby(pd.cut(df['minimum_age'], bins=np.arange(0,100, 10), right=False)).population.sum().reset_index(name = 'sum of population')

    minimum_age sum of population
0   [0, 10)     231.0
1   [10, 20)    1389.0
2   [20, 30)    306.0
3   [30, 40)    50.0
4   [40, 50)    NaN
5   [50, 60)    NaN
6   [60, 70)    NaN
7   [70, 80)    NaN
8   [80, 90)    5.0

Explanation: Pandas cut helps create bins of minimum_age by putting them in groups of 0-10, 10-20 and so on. This is how it looks

pd.cut(df['minimum_age'], bins=bins, right=False)

0    [30, 40)
1    [80, 90)
2    [10, 20)
3     [0, 10)
4    [20, 30)

Now we use groupby on the output of pd.cut to find sum of population.

user_62873080
Related