I have two dataframes df and df_census_final. df data can be downloaded here and df_census_final can be downloaded here and created with:
import pandas as pd
df = pd.read_csv('population_data.csv')
df_census_final = pd.read_csv('census_data.csv')
The df_census_final is year on year estimate of population by individual age groups and is a look up dataframe to get a specific value, whilst df is the actual dataframe where the calculation will be applied and population by age groups 15-19.
I have two functions which are below where the first function function_check_age_19 is used in the bottom function split_population_18_19:
def function_check_age_19(df,df_census_final):
"""
If the sex, year, and age are equal to the values in the function, then return the population
multiplied by the estimated proportion.
:param df: the dataframe that contains the population data
:param df_census_final: a dataframe with the following columns:
:return: the population multiplied by the estimated proportion.
"""
if df['Sex'] == 'Male' and df['Year'] == 2019 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Male Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2019)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Female' and df['Year'] == 2019 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Female Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2019)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Male' and df['Year'] == 2020 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Male Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2020)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Female' and df['Year'] == 2020 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Female Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2020)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Male' and df['Year'] == 2021 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Male Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2021)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Female' and df['Year'] == 2021 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Female Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2021)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Male' and df['Year'] == 2022 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Male Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2022)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Female' and df['Year'] == 2022 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Female Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2022)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Male' and df['Year'] == 2023 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Male Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2022)].values)/100
return df['population']*estimated_proportion
elif df['Sex'] == 'Female' and df['Year'] == 2023 and df['Age'] == '15-19':
estimated_proportion =(df_census_final['Female Pop %'].loc[(df_census_final['GROUP'] == '19') & (df_census_final['Year'] == 2022)].values)/100
return df['population']*estimated_proportion
else:
return df['population']
def split_population_18_19(df):
"""
It takes a dataframe, creates two new columns, one for 18 year olds and one for 19 year olds, then
adds them together and subtracts them from the total population
:param df: The dataframe that you want to split the population for
:return: A dataframe with the columns:
"""
try:
df['population_18'] = df.apply(lambda row: function_check_age_18(row), axis=1)
df['population_19'] = df.apply(lambda row: function_check_age_19(row), axis=1)
df.loc[df['Age'] != '15-19','population_18'] = 0
df.loc[df['Age'] != '15-19','population_19'] = 0
df['total_population_18'] = df['population_18'] + df['population_19']
df['population'] =df['population'] - df['total_population_18']
df['total_population_18'] = df['total_population_18'].shift(1)
df['total_population_18'] = df['total_population_18'].fillna(0.0)
df['population'] = df['total_population_18'] + df['population']
df.drop(['month','population_18','population_19','total_population_18'],axis=1,inplace=True)
return df
except Exception as e:
print(f"Exception on estimating 18 19 year old population with the error: {e}")
The first function has two dataframe which are passed to it, df and df_census_final but the second only has one. When using .apply in the second function as shown below:
df['population_19'] = df.apply(lambda row: function_check_age_19(row), axis=1)
I get a nonetype returned. i.e. (I am only trying to affect df element-wise) What is the best way to use the .apply function or anything else to pass both the dataframes into the function?
I am refactoring some hard-coded values as shown below:
def function_check_age_19(df):
"""
This function is checking the population of the age group 15-19 for the years
2019, 2020, 2021, and 2022 then mapping the proportion for 19 year olds for each Sex
"""
if df['Sex'] == 'Male' and df['Year'] == 2019 and df['Age'] == '15-19':
return df['population']*(19.851399/100)
elif df['Sex'] == 'Female' and df['Year'] == 2019 and df['Age'] == '15-19':
return df['population']*(20.088195/100)
elif df['Sex'] == 'Male' and df['Year'] == 2020 and df['Age'] == '15-19':
return df['population']*(19.492101/100)
elif df['Sex'] == 'Female' and df['Year'] == 2020 and df['Age'] == '15-19':
return df['population']*(19.745797/100)
elif df['Sex'] == 'Male' and df['Year'] == 2021 and df['Age'] == '15-19':
return df['population']*(19.489112/100)
elif df['Sex'] == 'Female' and df['Year'] == 2021 and df['Age'] == '15-19':
return df['population']*(19.621289/100)
elif df['Sex'] == 'Male' and df['Year'] == 2022 and df['Age'] == '15-19':
return df['population']*(19.442557/100)
elif df['Sex'] == 'Female' and df['Year'] == 2022 and df['Age'] == '15-19':
return df['population']*(19.534988/100)
else:
return df['population']
