Contents

Python Pandas For Your Grandpa - 5.4 Challenge: Family IQ

2021-02-01 470 words 3 minutes

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