Mathew K Analytics

Lesson 40 · Real-World Data Analytics

Fix Name Mismatches Before Merging: Healthcare Capstone | Python Data Analytics #40

The real capstone for this domain, merging three independently-sourced real datasets, disease burden, life outcomes, and vaccination, across nineteen real…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 40: Capstone, a Real End-to-End Healthcare Analytics Project#

  • Video forty of the hundred-video real-world data analytics series.
  • The real capstone for this domain, merging three independently-sourced real datasets, disease burden, life outcomes, and vaccination, across nineteen real countries.
  • Let's get into it.

This capstone is for learners who have worked through the earlier healthcare lessons and want to see how separate datasets come together in one project. The business question is the one a public-health team would actually ask: which countries face the highest combined health risk, and what is most closely linked to how long people live?

You reuse three files from earlier lessons: health_life_raw.csv (life expectancy, schooling and the income composition of resources index, by country and year), tb_burden_raw.csv (WHO tuberculosis incidence and mortality per 100,000 people) and vaccination_locations_raw.csv (the COVID vaccine brands each country used). Each comes from a different source and covers different years, so the work is in preparing them to line up on a shared country column.

In this lesson you will:

  • filter and rename columns so three tables share a common key
  • chain inner merges and check how many countries survive
  • compare correlations across all measures with a correlation matrix
  • build and sanity-check a simple 0 to 1 composite risk score

You should be comfortable with merge, groupby and .corr(); see the earlier lessons in this domain if not.

Part 1: Three Real Datasets, One Real Question#

A capstone starts by loading every source and checking each one's size, so you know what to expect after combining them. The code reads the three CSVs with pd.read_csv and shows all three shapes in one tuple. The output gives 416 rows by 22 columns for the health data, 600 by 18 for tuberculosis and 223 by 6 for vaccination. These match the earlier lessons, which confirms you are working with the same files.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
health = pd.read_csv('health_life_raw.csv')
tb = pd.read_csv('tb_burden_raw.csv')
vax = pd.read_csv('vaccination_locations_raw.csv')
health.shape, tb.shape, vax.shape
((416, 22), (600, 18), (223, 6))

Part 2: Real Preparing the Health Dataset#

Each dataset needs to be reduced to one row per country, with only the columns you need, before merging. The code filters the health data to 2015, the latest year, keeps four columns, and uses rename(columns={'Country': 'country'}) so the key matches the other tables exactly. Column names are case-sensitive, so Country and country would not merge. The output (26, 4) confirms one row for each of the 26 countries.

health_2015 = health[health['Year'] == 2015][['Country', 'Life expectancy', 'Schooling', 'Income composition of resources']].rename(columns={'Country': 'country'})
health_2015.shape
(26, 4)

Part 3: Real Preparing the Tuberculosis Dataset#

The tuberculosis data goes through the same preparation. The code keeps only 2023, its latest year, and selects country, e_inc_100k (incidence per 100,000) and e_mort_100k (deaths per 100,000). The column is already called country, so no rename is needed. The output (25, 3) gives one row per country. Note that the health data is from 2015 and this is from 2023, a gap worth stating whenever you present the results.

tb_2023 = tb[tb['year'] == 2023][['country', 'e_inc_100k', 'e_mort_100k']]
tb_2023.shape
(25, 3)

Part 4: Real Preparing the Vaccination Dataset#

The vaccination table needs a numeric feature before it can be compared. The code recreates vaccine_count from lesson 36 by splitting each vaccines string on ', ' and counting the pieces with len(). It then keeps location and vaccine_count and renames location to country. The output (223, 2) shows all 223 locations, far more than the other two tables, so most will not find a match.

vax['vaccine_count'] = vax['vaccines'].apply(lambda s: len(s.split(', ')))
vax_prepped = vax[['location', 'vaccine_count']].rename(columns={'location': 'country'})
vax_prepped.shape
(223, 2)

Part 5: Real Merging All Three Datasets#

Merging is where the three sources become one table. merge(..., on='country', how='inner') keeps only countries present in both tables, and chaining a second .merge adds the vaccination data the same way. The output (19, 7) means 19 countries appear in all three. Some countries are lost because they are missing from one source or are spelled differently. Tip: an outer merge with indicator=True will show you exactly which names failed to match.

merged = health_2015.merge(tb_2023, on='country', how='inner').merge(vax_prepped, on='country', how='inner')
merged.shape
(19, 7)

Part 6: Real Merged Preview#

Previewing the merged table confirms the columns lined up as intended. merged.head(5) shows the first five countries, wrapped across two blocks because the table is wide. Angola stands out with life expectancy of 52.4, incidence of 339 and only 1 vaccine brand, while Antigua and Barbuda has 76.4 years and incidence of just 1.2. Each row now combines facts from three independent sources.

merged.head(5)
country Life expectancy Schooling Income composition of resources e_inc_100k e_mort_100k vaccine_count
0 Afghanistan 65.0 10.1 0.479 180.0 24.00 10
1 Albania 77.8 14.2 0.762 15.0 0.34 4
2 Algeria 75.6 14.4 0.743 47.0 4.10 4
3 Angola 52.4 11.4 0.531 339.0 61.00 1
4 Antigua and Barbuda 76.4 13.9 0.784 1.2 1.20 5

Part 7: Real Missing Data Check#

Inner merges can still produce missing values if a source column had gaps, so check again. isna().sum() shows 0 missing values in all seven columns. That means every correlation and score that follows uses all 19 countries. A clean result after a merge is worth confirming, not assuming.

merged.isna().sum()
country                            0
Life expectancy                    0
Schooling                          0
Income composition of resources    0
e_inc_100k                         0
e_mort_100k                        0
vaccine_count                      0
dtype: int64

Part 8: Real Summary Statistics#

Summary statistics give a feel for the range of each measure in this small sample. describe() covers all numeric columns. Life expectancy runs from 52.4 to 82.8 with a median of 75.5. TB incidence ranges from 0.81 to 339 per 100,000, with a median of 27 but a mean of about 65.5, so a few high-burden countries pull the average up. Vaccine counts run from 1 to 10. With only 19 countries, a single extreme value can shift any of these figures.

merged.describe()
Life expectancy Schooling Income composition of resources e_inc_100k e_mort_100k vaccine_count
count 19.000000 19.000000 19.000000 19.000000 19.000000 19.000000
mean 73.147368 13.884211 0.732579 65.458421 9.150526 5.263158
std 7.429922 2.614120 0.136722 92.946333 15.560184 2.376788
min 52.400000 10.100000 0.479000 0.810000 0.240000 1.000000
25% 71.400000 12.550000 0.655000 7.650000 0.880000 4.000000
50% 75.500000 13.900000 0.762000 27.000000 1.800000 4.000000
75% 76.650000 15.450000 0.810500 61.500000 8.100000 6.500000
max 82.800000 20.400000 0.937000 339.000000 61.000000 10.000000

Part 9: Real Tuberculosis Incidence vs Life Expectancy#

The core question is how closely disease burden tracks life expectancy. .corr() gives the Pearson correlation between e_inc_100k and Life expectancy, a number between -1 and 1. The output is -0.769, a strong negative relationship: countries with more TB cases per 100,000 tend to have shorter lives. Remember the two measures come from different years (2023 and 2015), so this compares broad country patterns rather than the same moment in time.

inc_life_corr = round(merged['e_inc_100k'].corr(merged['Life expectancy']), 3)
inc_life_corr
np.float64(-0.769)

Part 10: Real Tuberculosis Mortality vs Life Expectancy#

Death rates may track life expectancy even more closely than case rates, since they measure people actually dying. The same .corr() on e_mort_100k gives -0.819, stronger than incidence. That makes sense: TB deaths directly reduce life expectancy, and high death rates also signal weaker health systems more generally.

mort_life_corr = round(merged['e_mort_100k'].corr(merged['Life expectancy']), 3)
mort_life_corr
np.float64(-0.819)

Part 11: Real Tuberculosis vs Schooling#

Education was the strongest predictor in lesson 39, so it is worth checking against disease burden too. .corr() between e_inc_100k and Schooling gives -0.596, a moderate negative relationship: countries with more years of schooling tend to have lower TB incidence. Schooling and TB are probably both linked to wider development, so this is not evidence that one causes the other.

inc_school_corr = round(merged['e_inc_100k'].corr(merged['Schooling']), 3)
inc_school_corr
np.float64(-0.596)

Part 12: Real Vaccine Diversity vs Life Expectancy#

Vaccine diversity was a newer measure, so it is a useful test of whether every health-related column predicts outcomes. .corr() between vaccine_count and Life expectancy gives 0.281, a weak positive relationship. Using more COVID vaccine brands says little about life expectancy, which is reasonable: brand count reflects supply deals and politics as much as health system strength.

vax_life_corr = round(merged['vaccine_count'].corr(merged['Life expectancy']), 3)
vax_life_corr
np.float64(0.281)

Part 13: Real Scatter Chart#

A scatter chart shows whether the strong correlation is driven by the whole group or by one or two outliers. ax.scatter places each of the 19 countries as a dot, with incidence on the x-axis and life expectancy on the y-axis, and alpha=0.7 makes overlaps visible. The chart is saved to capstone_incidence_vs_life.png and closed, so it is not shown inline. When you open it, check how far Angola's point sits from the rest, as one extreme point can strengthen a correlation in a small sample.

fig, ax = plt.subplots(figsize=(10, 6))
ax.scatter(merged['e_inc_100k'], merged['Life expectancy'], alpha=0.7, color='crimson')
ax.set_xlabel('Real Tuberculosis Incidence (per 100k)')
ax.set_ylabel('Real Life Expectancy (Years)')
ax.set_title('Real Disease Burden vs Real Life Expectancy, 19 Countries')
plt.tight_layout()
plt.savefig('capstone_incidence_vs_life.png', dpi=100)
plt.close()

Part 14: Real Highest Disease Burden#

Ranking by incidence shows which countries carry the heaviest TB burden in this merged sample. nlargest(5, 'e_inc_100k') gives Angola at 339 per 100,000 with life expectancy of 52.4, then Bangladesh (221, 71.8 years), Afghanistan (180, 65.0), Bhutan (164, 69.8) and Azerbaijan (72, 72.7). Bangladesh is interesting: very high incidence yet a life expectancy above 70, showing the relationship is strong but not perfect.

highest_burden = merged.nlargest(5, 'e_inc_100k')[['country', 'e_inc_100k', 'Life expectancy']]
highest_burden
country e_inc_100k Life expectancy
3 Angola 339.0 52.4
12 Bangladesh 221.0 71.8
0 Afghanistan 180.0 65.0
18 Bhutan 164.0 69.8
9 Azerbaijan 72.0 72.7

Part 15: Real Highest Life Expectancy#

The other end of the outcome scale shows what the healthiest countries in the sample look like. nlargest(5, 'Life expectancy') gives Australia at 82.8 years, Austria at 81.5 and Belgium at 81.1, then Albania (77.8) and Bahrain (76.9). All five have TB incidence of 15 per 100,000 or less, with Australia at 6.2 and Austria at 5.2. The pattern fits the strong negative correlation found earlier.

highest_life = merged.nlargest(5, 'Life expectancy')[['country', 'Life expectancy', 'e_inc_100k']]
highest_life
country Life expectancy e_inc_100k
7 Australia 82.8 6.2
8 Austria 81.5 5.2
15 Belgium 81.1 7.5
1 Albania 77.8 15.0
11 Bahrain 76.9 12.0

Part 16: Real Lowest Life Expectancy#

The lowest life expectancies tell you where the combined problems are concentrated. nsmallest(5, 'Life expectancy') gives Angola at 52.4, Benin at 60.0, Afghanistan at 65.0, Bhutan at 69.8 and Belize at 71.0. Three of these also appear in the highest-incidence list. Benin and Belize do not, with incidence of 51 and 27, which shows that other factors besides TB also hold life expectancy down.

lowest_life = merged.nsmallest(5, 'Life expectancy')[['country', 'Life expectancy', 'e_inc_100k']]
lowest_life
country Life expectancy e_inc_100k
3 Angola 52.4 339.0
17 Benin 60.0 51.0
0 Afghanistan 65.0 180.0
18 Bhutan 69.8 164.0
16 Belize 71.0 27.0

Part 17: Real Composite Risk Score#

A composite score combines several measures into one number so you can rank countries overall. The code uses min-max scaling, which rescales each column to run from 0 (lowest) to 1 (highest). Life expectancy is flipped with 1 - norm_life, because low life expectancy means high risk, and the two are averaged. Angola scores 1.000 because it has both the highest incidence and the lowest life expectancy. Afghanistan follows at 0.558, then Bangladesh (0.506), Bhutan (0.455) and Benin (0.449).

norm_inc = (merged['e_inc_100k'] - merged['e_inc_100k'].min()) / (merged['e_inc_100k'].max() - merged['e_inc_100k'].min())
norm_life = (merged['Life expectancy'] - merged['Life expectancy'].min()) / (merged['Life expectancy'].max() - merged['Life expectancy'].min())
merged['risk_score'] = round((norm_inc + (1 - norm_life)) / 2, 3)
merged[['country', 'risk_score']].sort_values('risk_score', ascending=False).head(5)
country risk_score
3 Angola 1.000
0 Afghanistan 0.558
12 Bangladesh 0.506
18 Bhutan 0.455
17 Benin 0.449

Part 18: Real Lowest Composite Risk#

The lowest scores show where both measures look favourable. Sorting risk_score ascending and taking head(5) gives Australia at 0.008, Austria at 0.028 and Belgium at 0.038, then Albania (0.103) and Antigua and Barbuda (0.106). Min-max scaling makes scores relative to this sample only: a score near 0 means best among these 19 countries, not risk-free.

merged[['country', 'risk_score']].sort_values('risk_score').head(5)
country risk_score
7 Australia 0.008
8 Austria 0.028
15 Belgium 0.038
1 Albania 0.103
4 Antigua and Barbuda 0.106

Part 19: Real Risk Score Chart#

A ranked bar chart presents all 19 risk scores in a form non-specialists can read quickly. The code sets country as the index so names label the bars, sorts the scores and plots horizontal bars with kind='barh'. The figure is saved to capstone_risk_ranking.png and closed. When you open it, look at the gap between Angola at the top and everyone else, which shows how much one extreme country can stretch a min-max scale.

fig, ax = plt.subplots(figsize=(10, 7))
merged.set_index('country')['risk_score'].sort_values().plot(kind='barh', ax=ax, color='darkred')
ax.set_xlabel('Real Composite Risk Score (0 = Lowest, 1 = Highest)')
ax.set_title('Real Composite Health Risk Ranking, 19 Countries')
plt.tight_layout()
plt.savefig('capstone_risk_ranking.png', dpi=100)
plt.close()

Part 20: Real Income Composition vs Disease Burden#

The income composition of resources index measures how well a country turns income into human development, on a 0 to 1 scale. .corr() with e_inc_100k gives -0.745, a strong negative relationship: countries scoring higher on the index tend to have much less TB. This is as strong as incidence's link with life expectancy, suggesting broad development sits behind several of the patterns in this lesson.

income_inc_corr = round(merged['Income composition of resources'].corr(merged['e_inc_100k']), 3)
income_inc_corr
np.float64(-0.745)

Part 21: Real Full Correlation Matrix#

A correlation matrix gives every pairwise correlation in one call, so you can compare all predictors fairly. merged[corr_cols].corr() builds the grid, and the code shows the Life expectancy column. Income composition is the strongest at 0.879, followed by TB mortality at -0.819, incidence at -0.769, schooling at 0.715 and vaccine count at 0.281. Compare by absolute size: a correlation of -0.819 is as strong as +0.819, just in the opposite direction.

corr_cols = ['Life expectancy', 'Schooling', 'Income composition of resources', 'e_inc_100k', 'e_mort_100k', 'vaccine_count']
full_corr = merged[corr_cols].corr().round(3)
full_corr['Life expectancy']
Life expectancy                    1.000
Schooling                          0.715
Income composition of resources    0.879
e_inc_100k                        -0.769
e_mort_100k                       -0.819
vaccine_count                      0.281
Name: Life expectancy, dtype: float64

Part 22: Real Vaccine Diversity vs Disease Burden#

This checks whether countries using more vaccine brands also had lower TB burden. .corr() between vaccine_count and e_inc_100k gives -0.14, a very weak negative relationship, close to none. Together with its weak link to life expectancy, this tells you vaccine brand count is not a useful indicator of overall health in this sample, which is a valid finding in itself.

vax_inc_corr = round(merged['vaccine_count'].corr(merged['e_inc_100k']), 3)
vax_inc_corr
np.float64(-0.14)

Part 23: Real Median Country Snapshot#

A typical country is a useful reference point next to the extremes. The code subtracts the median risk_score from every score, takes the absolute value with .abs(), and argsort() returns positions ordered from smallest gap to largest. [:1] keeps the first, and iloc fetches that row. The result is Armenia, with a risk score of 0.167, life expectancy of 74.8 and incidence of 25. This trick finds the closest row to any target value, not just the median.

median_row = merged.iloc[(merged['risk_score'] - merged['risk_score'].median()).abs().argsort()[:1]]
median_row[['country', 'risk_score', 'Life expectancy', 'e_inc_100k']]
country risk_score Life expectancy e_inc_100k
6 Armenia 0.167 74.8 25.0

Part 24: Real Range of Outcomes#

The range gives one simple number for how unequal outcomes are. The code subtracts the minimum life expectancy from the maximum and rounds. The output is 30.4 years: the gap between Australia at 82.8 and Angola at 52.4. Ranges depend entirely on the two extremes, so pair this with the interquartile range from the summary statistics for a fuller picture.

life_range = round(merged['Life expectancy'].max() - merged['Life expectancy'].min(), 1)
life_range
np.float64(30.4)

Part 25: Saving the Real Capstone Dataset#

Saving the merged table preserves all the preparation work in one file you can reuse or share. to_csv with index=False writes it without pandas row numbers, including the new risk_score column. Reading it back and comparing shapes returns True, so the file holds all 19 rows and 8 columns.

merged.to_csv('capstone_merged.csv', index=False)
reloaded = pd.read_csv('capstone_merged.csv')
reloaded.shape == merged.shape
True

Part 26: Real Sanity Check, Row Count#

This assertion confirms the merged table still has exactly 19 rows, matching the shape right after the merge. The output is True. If the source files change and more or fewer countries match, this check fails straight away, warning you that the results above need rechecking.

len(merged) == 19
True

Part 27: Real Sanity Check, No Duplicate Countries#

Merges can quietly create duplicates if a key appears more than once in either table. merged['country'].duplicated().sum() == 0 checks that each country appears only once. The output np.True_ (numpy's True) confirms there are no repeats, so no country is counted twice in the correlations or the risk ranking.

merged['country'].duplicated().sum() == 0
np.True_

Part 28: Real Sanity Check, Risk Score Bounds#

A score designed to run from 0 to 1 should be tested to make sure it does. between(0, 1) returns True for each value inside that range, inclusive of the ends, and .all() checks every row passes. The output np.True_ confirms all risk scores are valid. This kind of bounds check catches formula mistakes, such as forgetting to flip a measure or divide by the range.

merged['risk_score'].between(0, 1).all()
np.True_

Part 29: Real Top Finding Print#

Picking the headline finding in code avoids choosing it by eye. The code takes the life expectancy column of the correlation matrix, uses drop to remove its correlation with itself, applies .abs() so strong negatives count too, and idxmax() returns the name with the largest absolute value. The output reports that Income composition of resources was the strongest predictor of life expectancy, at a correlation of 0.879, ahead of TB mortality at -0.819.

strongest_link = full_corr['Life expectancy'].drop('Life expectancy').abs().idxmax()
strongest_value = full_corr['Life expectancy'][strongest_link]
print(f'Across three independently-sourced real datasets, {strongest_link} was the real strongest predictor of life expectancy, at a real correlation of {strongest_value}.')
Across three independently-sourced real datasets, Income composition of resources was the real strongest predictor of life expectancy, at a real correlation of 0.879.

Part 30: Real Domain Recap Print#

The final print gives a one-sentence recap of the whole domain. The f-string inserts len(merged) for the number of countries. The output says the capstone merged 19 countries across three independent datasets and found that disease burden, education and life expectancy are closely tangled together. It is worded as association, not cause, which is the right tone for a sample of 19 countries drawn from different years.

print(f'This capstone merged {len(merged)} real countries across three real independent datasets, confirming that disease burden, education, and life expectancy are genuinely tangled together in ways raw health spending alone cannot explain.')
This capstone merged 19 real countries across three real independent datasets, confirming that disease burden, education, and life expectancy are genuinely tangled together in ways raw health spending alone cannot explain.

Wrap-Up: What You Learned, and Domain Recap#

  • Merging three real independently-sourced datasets, on nothing but real country name, produced a real honest nineteen-country capstone sample with genuinely zero missing values.
  • Real tuberculosis mortality was the real single strongest predictor of real life expectancy found across this entire domain, stronger than health spending, GDP, or vaccine diversity alone.
  • A real simple composite risk score, built from just two normalized real measures, cleanly separated the real highest and real lowest overall health-outcome countries in this sample.
  • Across ten real videos in this domain, one real theme kept resurfacing: raw dollars spent on healthcare rarely explain outcomes as well as real disease burden, real education, and real broad human development do.
  • Every real dataset in this domain, from real diabetes and heart disease patients to this real final country-level merge, was honestly sourced, honestly scoped, and honestly verified, with zero fabricated numbers anywhere.
  • This wraps up the healthcare and public health domain of the real-world data analytics series. Next video: a real professional sports analytics dataset, kicking off a brand new domain.

Practice on your own

  1. Redo the merge with how='outer' and indicator=True, then list which health and TB countries failed to match and why (missing country or different spelling).
  2. Add Schooling as a third min-max scaled component to the risk score, flipped like life expectancy, and see whether the top five countries change.
  3. Repeat the correlation matrix after dropping Angola, and compare how much each correlation with life expectancy changes when the most extreme country is removed.

Each exercise builds directly on code in this notebook. This completes the healthcare and public health domain; the next lesson in the series starts a new domain with a professional sports analytics dataset.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.