Lesson 37 · Real-World Data Analytics
Counts vs Rates per 100k in Pandas: Tuberculosis Data | Python Data Analytics #37
A real WHO-curated tuberculosis burden dataset, twenty-five real countries tracked across twenty-four real years, from 2000 through 2023. Let's get into it.
- CourseReal-World Data Analytics
- Lesson37 of 100
- Video17 min
- FormatJupyter notebook · 30 code cells
- Data1 dataset
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.
- tb_burden_raw.csv preview52.8 KB
📓 Full notebook
Download .ipynbData Analytics 100, Video 37: Real Global Tuberculosis Burden Analysis#
- Video thirty-seven of the hundred-video real-world data analytics series.
- A real WHO-curated tuberculosis burden dataset, twenty-five real countries tracked across twenty-four real years, from 2000 through 2023.
- Let's get into it.
This lesson is for learners who have handled a single-year table and are ready for panel data: the same countries measured every year. The business question is how the tuberculosis (TB) burden has changed since 2000, and whether falling deaths and rising case counts can both be true at once.
The dataset, tb_burden_raw.csv, is a WHO-curated extract with one row per country per year. The key columns are country, g_whoregion (WHO region), year, e_inc_num and e_inc_100k (estimated new cases, as a count and per 100,000 people), e_mort_num and e_mort_100k (estimated deaths), e_pop_num (population), c_cdr (case detection rate) and cfr (case fatality ratio). It covers a subset of countries, not the whole world, and the lesson is honest about that.
In this lesson you will:
- audit coverage and missing values in a country-year table
- compare raw counts with per-capita rates and see why they disagree
- calculate percentage change per country with a self-merge
- check regional sample sizes before trusting a regional average
You should be comfortable with pandas groupby and boolean filtering; see earlier lessons in the series if not.
Part 1: A Real WHO Dataset, Honestly Scoped#
Scoping comes first: before quoting any global-sounding figure you need to know how much of the world this file actually covers. The code imports pandas, numpy and matplotlib, reads the CSV with pd.read_csv, and shows .shape. The output, (600, 18), means 600 rows and 18 columns. With 25 countries and 24 years, 25 times 24 is exactly 600, so the table looks like one row per country per year. Keep that expected number in mind, as it is checked again at the end.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
tb = pd.read_csv('tb_burden_raw.csv')
tb.shape
Part 2: Real Data Preview#
WHO column names are compact codes, so you need the full list in front of you before writing any analysis. The code calls tb.head(5) and then tb.columns.tolist(), but only the last expression is displayed, so the output is the 18 column names. A useful pattern: e_ means a WHO estimate, c_ means a calculated or reported figure, _num is a raw count and _100k is a rate per 100,000 people. Columns like e_mort_tbhiv_num split deaths into those with and without HIV.
tb.head(5)
tb.columns.tolist()
Part 3: Real Coverage Check#
Coverage checks confirm the time span and the number of units before you build trends. The code asks for the minimum and maximum year and then counts distinct countries with nunique(). Only the last line is shown, so the output is 25 countries; run the year line on its own to see the span, which the notebook states is 2000 to 2023. Notice that the country names in later outputs all start with A or B, which is a strong hint this is a slice of the full WHO list rather than a representative sample.
tb['year'].min(), tb['year'].max()
tb['country'].nunique()
Part 4: Real Missing Data Audit#
Knowing exactly which columns have gaps tells you which calculations need care. isna().sum() counts missing values per column. All of the WHO estimate columns (e_) are complete, but c_cdr is missing 50 values, c_newinc_100k 38 and cfr 12. Because pandas skips missing values in mean() and corr() by default, later averages of these columns are based on fewer rows than the full 600. Tip: say so when you report them, rather than presenting them as covering every country-year.
tb.isna().sum()
Part 5: Real WHO Regions Represented#
Regional comparisons only make sense if each region has enough countries. The code groups by g_whoregion and counts distinct countries in each with ['country'].nunique(), sorted from most to least. The Americas have 9 countries and Europe 7, while Africa has 3 and the Eastern Mediterranean, South-East Asia and Western Pacific have just 2 each. So a region like South-East Asia is represented here by only two countries, which is far too few to speak for the whole region.
tb.groupby('g_whoregion')['country'].nunique().sort_values(ascending=False)
Part 6: Real Latest-Year Snapshot#
A single-year snapshot lets you compare countries on a like-for-like basis without mixing years. The code filters with tb['year'] == 2023 and uses .copy() so the new latest DataFrame is independent of tb. The output (25, 18) confirms one row per country for 2023, with all 18 columns. If the shape had been smaller, it would mean some countries had no 2023 data and you would need to decide how to handle them.
latest = tb[tb['year'] == 2023].copy()
latest.shape
Part 7: Real Highest 2023 Incidence Rates#
Incidence rate (new cases per 100,000 people) is the fair way to compare countries of very different sizes. nlargest(5, 'e_inc_100k') keeps the five highest 2023 rates, showing only country and e_inc_100k. Angola leads at 339 per 100,000, followed by Bangladesh at 221, Afghanistan at 180, Bhutan at 164 and Bolivia at 105. Angola's rate is more than three times Bolivia's, a big spread even within the top five.
highest_incidence = latest.nlargest(5, 'e_inc_100k')[['country', 'e_inc_100k']]
highest_incidence
Part 8: Real Lowest 2023 Incidence Rates#
The bottom of the ranking shows where TB is now rare. nsmallest(5, 'e_inc_100k') returns the five lowest 2023 rates. Barbados has just 0.81 cases per 100,000, then Antigua and Barbuda at 1.2, Aruba and Bermuda at 3.6 and American Samoa at 4.5. These are small island populations, where a handful of cases can move the rate noticeably from one year to the next. Bear that in mind before reading too much into small differences at this end.
lowest_incidence = latest.nsmallest(5, 'e_inc_100k')[['country', 'e_inc_100k']]
lowest_incidence
Part 9: Real Highest 2023 Mortality Rates#
Mortality rate shows the human cost, and it does not always follow incidence exactly. nlargest(5, 'e_mort_100k') lists the five highest 2023 death rates per 100,000. Angola is again first at 61, more than twice Bhutan's 28. Bangladesh has 26 and Afghanistan 24, and Benin appears at 11 even though it was not in the top five for incidence. Comparing the two rankings side by side is a quick way to spot countries where people are more likely to die once infected.
highest_mortality = latest.nlargest(5, 'e_mort_100k')[['country', 'e_mort_100k']]
highest_mortality
Part 10: Real Global Incidence Trend#
To see the overall trend, you add up cases across all countries for each year. groupby('year')['e_inc_num'].sum() totals the estimated new cases per year, giving one value per year. The code shows only the first three years with .head(3): 466,689 cases in 2000, 472,416 in 2001 and 482,331 in 2002. These are raw counts across these 25 countries, not the global total, so describe them that way.
incidence_by_year = tb.groupby('year')['e_inc_num'].sum()
incidence_by_year.head(3)
Part 11: Real Global Mortality Trend#
Deaths are summed the same way so you can set them against cases. groupby('year')['e_mort_num'].sum() gives total estimated deaths per year. The first three years show 135,295 deaths in 2000, 137,091 in 2001 and 133,964 in 2002. Already the pattern differs from cases: deaths dipped in 2002 while cases kept rising. Using the same groupby structure for both series keeps them aligned year by year, which makes the chart and the percentage changes straightforward.
mortality_by_year = tb.groupby('year')['e_mort_num'].sum()
mortality_by_year.head(3)
Part 12: Real Trend Chart#
Plotting both series on one chart makes the contrast between cases and deaths visible at a glance. The code calls ax.plot twice, once for incidence and once for mortality, using each Series' .index (years) and .values (totals), and gives each a label so ax.legend() can identify them. The figure is saved to tb_incidence_vs_mortality.png and closed, so nothing appears inline. When you open it, look for the cases line drifting upward while the deaths line drifts down.
fig, ax = plt.subplots(figsize=(10, 6))
ax.plot(incidence_by_year.index, incidence_by_year.values, label='Real Estimated Incident Cases', color='darkorange')
ax.plot(mortality_by_year.index, mortality_by_year.values, label='Real Estimated Deaths', color='crimson')
ax.set_xlabel('Real Year')
ax.set_ylabel('Real Estimated Count Across 25 Countries')
ax.set_title('Real Tuberculosis Incidence vs Mortality, 2000-2023')
ax.legend()
plt.tight_layout()
plt.savefig('tb_incidence_vs_mortality.png', dpi=100)
plt.close()
Part 13: Real Incidence Change, 2000 to 2023#
Percentage change turns a long series into a single, comparable number. The code pulls the 2000 and 2023 totals with .loc[2000] and .loc[2023], which look up values by index label (the year), then computes (new minus old) divided by old, times 100. The output is 39.9: estimated cases across these countries rose by about 40% over the period. On its own that sounds like failure, but it is a raw count, so population growth is part of the story.
inc_2000 = incidence_by_year.loc[2000]
inc_2023 = incidence_by_year.loc[2023]
inc_pct_change = round((inc_2023 - inc_2000) / inc_2000 * 100, 1)
inc_pct_change
Part 14: Real Mortality Change, 2000 to 2023#
The same calculation on deaths shows the other side. Using .loc to fetch the 2000 and 2023 totals and the same percentage-change formula, the output is -38.7. Estimated deaths fell by nearly 39% while cases rose by about 40%. That combination suggests a lower share of people dying once they have TB, for example through better detection and treatment, but this data alone cannot prove the cause.
mort_2000 = mortality_by_year.loc[2000]
mort_2023 = mortality_by_year.loc[2023]
mort_pct_change = round((mort_2023 - mort_2000) / mort_2000 * 100, 1)
mort_pct_change
Part 15: Real Population Effect on Case Counts#
If raw cases rose, you need to know how much of that is simply more people. .corr() measures the Pearson correlation between population (e_pop_num) and estimated cases (e_inc_num) across all 600 rows; 1 would be a perfect straight-line link. The output is 0.947, a very strong positive relationship. Bigger countries record more cases almost automatically. That is exactly why rates per 100,000, not raw counts, are the fair basis for comparing countries.
pop_case_corr = round(tb['e_pop_num'].corr(tb['e_inc_num']), 3)
pop_case_corr
Part 16: Real Per-Country Incidence Rate Change#
Per-country rate changes show who actually improved, independent of population size. The code builds two small tables, the 2000 and 2023 rates, renames the rate column in each with rename(columns=...), and joins them with merge(on='country'). It drops rows where the 2000 rate is 0 to avoid dividing by zero, then calculates pct_change. The output, 24, means one of the 25 countries was removed by that filter. Tip: always check the row count after a merge and filter.
early = tb[tb['year'] == 2000][['country', 'e_inc_100k']].rename(columns={'e_inc_100k': 'inc_2000'})
late = tb[tb['year'] == 2023][['country', 'e_inc_100k']].rename(columns={'e_inc_100k': 'inc_2023'})
rate_change = early.merge(late, on='country')
rate_change = rate_change[rate_change['inc_2000'] > 0]
rate_change['pct_change'] = round((rate_change['inc_2023'] - rate_change['inc_2000']) / rate_change['inc_2000'] * 100, 1)
len(rate_change)
Part 17: Real Most-Improved Countries#
Sorting by percentage change brings the biggest reductions to the top. sort_values('pct_change') sorts ascending, so the most negative changes come first, and .head(5) keeps five. Antigua and Barbuda cut its rate by 80.3%, from 6.1 to 1.2 per 100,000. The Bahamas fell 73.1%, Andorra 72.9%, Austria 69.4% and Belarus 68.6%. Belarus stands out because it started from a much higher rate of 86, so its drop to 27 is a large absolute improvement too.
most_improved = rate_change.sort_values('pct_change').head(5)
most_improved
Part 18: Real Countries That Got Worse#
The other end of the same sort shows where progress stalled. .tail(3) on the ascending sort returns the three largest changes. Australia barely moved (-1.6%), Bangladesh was flat at 221 in both years (0.0%), and Angola rose 14.1%, from 297 to 339 per 100,000. So among these 24 countries only Angola's rate actually went up. Remember percentage change hides the starting point: Bangladesh's 0% is far more serious than a small country's 0% at a low rate.
got_worse = rate_change.sort_values('pct_change').tail(3)
got_worse
Part 19: Real Case Fatality Ratio Distribution#
The case fatality ratio (CFR) is the estimated share of people with TB who die from it, here stored as a fraction between 0 and 1. describe() summarises it. The count is 588, matching the 12 missing values found earlier. The median is 0.11, meaning about 11% of cases die in a typical country-year, while the mean is higher at about 0.16 and the maximum is 1.0. A value of 1.0 would mean every case died, which is likely an estimate artefact for a small country and worth inspecting before you trust it.
tb['cfr'].describe()
Part 20: Real Average Incidence Rate by Region#
Regional averages give a high-level picture, but you must read them with care. The code groups by g_whoregion, takes the mean of e_inc_100k across all years and countries in each region, rounds to one decimal and sorts. South-East Asia is highest at 203.5 per 100,000 and Africa next at 163.5, while the Western Pacific is lowest at 6.5. These averages come from very few countries, as the next step makes explicit.
region_avg = tb.groupby('g_whoregion')['e_inc_100k'].mean().round(1).sort_values()
region_avg
Part 21: Real Regional Sample Size Honesty Check#
This step repeats the region count from earlier, now sorted ascending, specifically to put it next to the regional averages. The output shows the Eastern Mediterranean, South-East Asia and Western Pacific each rest on just 2 countries, Africa on 3, Europe on 7 and the Americas on 9. So South-East Asia's 203.5 average is really two countries, and the Western Pacific's 6.5 is two others. A regional average from two countries describes those two countries, not the region.
region_counts = tb.groupby('g_whoregion')['country'].nunique().sort_values()
region_counts
Part 22: Real HIV-Associated TB Death Share#
TB and HIV often occur together, and HIV-positive people are more likely to die from TB. The code divides the total of e_mort_tbhiv_num (TB deaths among people with HIV) by the total of all TB deaths in e_mort_num, across all rows, and expresses it as a percentage. The output is 5.82%. Because this is a ratio of sums, larger countries carry more weight than smaller ones, which is usually what you want for a share of all deaths.
hiv_death_share = round(tb['e_mort_tbhiv_num'].sum() / tb['e_mort_num'].sum() * 100, 2)
hiv_death_share
Part 23: Real Case Detection Rate by Region#
The case detection rate (c_cdr) estimates what percentage of new TB cases are actually found and reported. Undetected cases cannot be treated, so this matters a great deal. The code takes the mean per region, rounds and sorts. Africa is lowest at 62.7% and South-East Asia next at 64.8%, while Europe reaches 81.3% and the Western Pacific 87.0%. Remember that c_cdr had 50 missing values, which mean() skips, and that each region has only a few countries here.
cdr_by_region = tb.groupby('g_whoregion')['c_cdr'].mean().round(1).sort_values()
cdr_by_region
Part 24: Real Correlation, Detection vs Mortality Rate#
It is reasonable to expect that finding more cases would mean fewer deaths. .corr() between c_cdr and e_mort_100k tests this across all country-years with both values present. The output is -0.557, a moderate negative correlation: higher detection rates tend to go with lower death rates. That fits the idea that detected cases get treated, but correlation is not causation, and richer health systems likely drive both.
cdr_mort_corr = round(tb['c_cdr'].corr(tb['e_mort_100k']), 3)
cdr_mort_corr
Part 25: Saving the Real Enriched Dataset#
Saving the data keeps a clean copy for later lessons or tools. to_csv with index=False writes the table without pandas' row numbers, and the code reads it back and compares shapes. The output True confirms the same rows and columns survived the round trip. This table has not been changed much in this notebook, but saving at a known point is still a good habit for any analysis you might pick up later.
tb.to_csv('tb_burden_cleaned.csv', index=False)
reloaded = pd.read_csv('tb_burden_cleaned.csv')
reloaded.shape == tb.shape
Part 26: Real Sanity Check, Row Count#
A simple row-count assertion catches accidental drops or duplications. len(tb) == 600 checks the table still has the 600 rows seen at the start, which also equals 25 countries times 24 years. The output is True. If a later version of the file has more or fewer rows, this check will flag it straight away.
len(tb) == 600
Part 27: Real Sanity Check, No Duplicate Country-Years#
In panel data, the key is the combination of country and year, not either one alone. duplicated(subset=['country', 'year']) marks rows where that pair has already appeared, and .sum() == 0 checks there are none. The output np.True_ (numpy's version of True) confirms every country-year is unique. This matters because a duplicate pair would double-count cases and deaths in every yearly total.
tb.duplicated(subset=['country', 'year']).sum() == 0
Part 28: Real Sanity Check, Full Year Coverage#
Unique keys are not enough: you also want every country present in every year, or yearly totals will jump when a country drops out. The code groups by country, counts the years for each with .count(), compares each count with 24, and .all() returns True only if every country passes. The output np.True_ confirms a complete, balanced panel, so the trend in yearly totals is not caused by countries joining or leaving.
(tb.groupby('country')['year'].count() == 24).all()
Part 29: Real Top Finding Print#
Pulling the headline finding straight from your results avoids typing numbers by hand. .iloc[0] takes the first row of highest_incidence by position, and the code reads its country and e_inc_100k values into variables. An f-string then builds the sentence. The output reads that Angola had the highest incidence rate in this dataset in 2023, at 339.0 cases per 100,000 people. Note the careful wording: in this dataset, not in the world.
top_country = highest_incidence.iloc[0]['country']
top_rate = highest_incidence.iloc[0]['e_inc_100k']
print(f'{top_country} carried the real highest tuberculosis incidence rate in this dataset in 2023, at {top_rate} real cases per hundred thousand people.')
Part 30: Real Recap Print#
The recap sentence brings together the lesson's central contrast. The f-string uses nunique() for the country count and abs() to turn the negative mortality change into a positive number for a natural-sounding sentence. The output says that across 25 countries, estimated deaths fell 38.7% from 2000 to 2023 while raw case counts rose 39.9%. That single line is a good example of telling a non-technical audience why rates and raw counts can point in opposite directions.
print(f'Across these {tb["country"].nunique()} real countries, total estimated deaths fell {abs(mort_pct_change)}% from 2000 to 2023, even as raw case counts rose {inc_pct_change}%, a real reminder that rates and raw counts can tell genuinely different stories.')
Wrap-Up: What You Learned#
- This real dataset honestly covers twenty-five countries and twenty-four years, 2000 through 2023, not the full global WHO set, and that real honest scoping matters before drawing conclusions.
- Real estimated tuberculosis deaths fell by nearly forty percent across this window, even though real raw case counts actually rose, driven largely by real population growth in high-burden countries.
- Real population size correlates strongly with real raw case counts, which is exactly why real per-capita rates, not raw counts, are the real fair way to compare countries.
- A handful of real countries cut their real incidence rate by more than seventy percent since 2000, a real genuine public health success story.
- Checking real regional sample sizes before trusting a real regional average is a real easy, honest habit most analyses skip.
- Next video: a real global obesity and nutrition analysis, continuing this domain with another genuinely global public health snapshot.
Practice on your own
- Repeat the per-country percentage change for
e_mort_100kinstead ofe_inc_100k, and list the five countries whose death rate fell most. - Find which country was dropped by the
inc_2000 > 0filter by comparingearly['country']withrate_change['country'], then look at its rows to see why. - Plot
e_inc_100kover time for Angola, Bangladesh and Belarus on one line chart, and compare their paths with the 2000 and 2023 values shown in this notebook.
Each takes only a few lines built from code you already have. The next lesson in the series is a global obesity and nutrition analysis, another public-health snapshot where honest scoping and per-capita thinking will matter just as much.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



