Python Pandas For Your Grandpa - 5.4 Challenge: Family IQ
Contents
Setup
Define a person’s Family IQ Score as
Family IQ Score = 0.5 * IQ score + 0.5 * relatives’ IQ score
where relatives’ IQ score is the average IQ score of that person’s parents, full siblings, and children. Given a dataset of people and their IQ scores, determine who has the highest Family IQ score.
import numpy as np
import pandas as pd
generator = np.random.default_rng(2718)
persons = pd.DataFrame({
'id': [ 2, 3, 8, 12, 14, 15, 17, 32, 35, 41, 60, 64, 83, 98],
'mom_id': [35, 41, pd.NA, 35, 41, 2, pd.NA, pd.NA, pd.NA, pd.NA, 8, 12, 35, 2],
'dad_id': [17, 8, pd.NA, 17, 8, 32, pd.NA, pd.NA, pd.NA, pd.NA, pd.NA, 14, 17, 14],
'IQ': np.round(generator.normal(loc=100, scale=20, size=14))
})
print(persons)
## id mom_id dad_id IQ
## 0 2 35 17 106.0
## 1 3 41 8 99.0
## 2 8 <NA> <NA> 56.0
## 3 12 35 17 110.0
## 4 14 41 8 104.0
## 5 15 2 32 109.0
## 6 17 <NA> <NA> 99.0
## 7 32 <NA> <NA> 90.0
## 8 35 <NA> <NA> 80.0
## 9 41 <NA> <NA> 52.0
## 10 60 8 <NA> 97.0
## 11 64 12 14 87.0
## 12 83 35 17 138.0
## 13 98 2 14 97.0
Solution
moms = pd.merge(
left=persons[['id', 'mom_id']],
right=persons[['id', 'IQ']],
how='inner',
left_on='mom_id',
right_on='id',
suffixes=['', '_relative']
)
dads = pd.merge(
left=persons[['id', 'dad_id']],
right=persons[['id', 'IQ']],
how='inner',
left_on='dad_id',
right_on='id',
suffixes=['', '_relative']
)
sibs = pd.merge(
left=persons[['id', 'mom_id', 'dad_id']].dropna(),
right=persons[['id', 'mom_id', 'dad_id', 'IQ']],
how='inner',
on=['mom_id', 'dad_id'],
suffixes=['', '_relative']
)
sibs = sibs.loc[~(sibs.id == sibs.id_relative)]
children = pd.concat((
persons[['dad_id', 'id', 'IQ']].dropna().rename(columns={'dad_id':'id', 'id':'id_relative'}),
persons[['mom_id', 'id', 'IQ']].dropna().rename(columns={'mom_id':'id', 'id':'id_relative'})
))
relatives = pd.concat((
moms[['id', 'id_relative', 'IQ']],
dads[['id', 'id_relative', 'IQ']],
sibs[['id', 'id_relative', 'IQ']],
children[['id', 'id_relative', 'IQ']]
))
avgrelatveIQs = relatives.groupby('id')['IQ'].mean()
persons.set_index('id', inplace=True)
persons['AvgRelIQ'] = avgrelatveIQs
persons['FamilyIQ'] = 0.5 * persons.IQ + 0.5 * persons.AvgRelIQ
persons.FamilyIQ.idxmax()
## 83
Course Curriculum
- Introduction
1.1 Introduction - Series
2.1 Series Creation
2.2 Series Basic Indexing
2.3 Series Basic Operations
2.4 Series Boolean Indexing
2.5 Series Missing Values
2.6 Series Vectorization
2.7 Seriesapply()
2.8 Series View vs Copy
2.9 Challenge: Baby Names
2.10 Challenge: Bees Knees
2.11 Challenge: Car Shopping
2.12 Challenge: Price Gouging
2.13 Challenge: Fair Teams - DataFrame
3.1 DataFrame Creation
3.2 DataFrame To And From CSV
3.3 DataFrame Basic Indexing
3.4 DataFrame Basic Operations
3.5 DataFrameapply()
3.6 DataFrame View vs Copy
3.7 DataFramemerge()
3.8 DataFrame Aggregation
3.9 DataFramegroupby()
3.10 Challenge: Hobbies
3.11 Challenge: Party Time
3.12 Challenge: Vending Machines
3.13 Challenge: Cradle Robbers
3.14 Challenge: Pot Holes - Advanced
4.1 Strings
4.2 Dates And Times
4.3 Categoricals
4.4 MultiIndex
4.5 DataFrame Reshaping
4.6 Challenge: Class Transitions
4.7 Challenge: Rose Thorn
4.8 Challenge: Product Volumes
4.9 Challenge: Session Groups
4.10 Challenge: OB-GYM - Final Boss
5.1 Challenge: COVID Tracing
5.2 Challenge: Pickle
5.3 Challenge: TV Commercials
5.4 Challenge: Family IQ
5.5 Challenge: Concerts