Lesson 36 · Real-World Data Analytics
Pandas explode for Comma-Separated Lists: COVID Vaccine Data | Python Data Analytics #36
A real, country-by-country snapshot of which COVID vaccines every real country actually used, who reported it, and how current that reporting genuinely is.
- CourseReal-World Data Analytics
- Lesson36 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.
📓 Full notebook
Download .ipynbData Analytics 100, Video 36: Real Global Vaccination Snapshot#
- Video thirty-six of the hundred-video real-world data analytics series.
- A real, country-by-country snapshot of which COVID vaccines every real country actually used, who reported it, and how current that reporting genuinely is.
- Let's get into it.
This lesson is for learners who can load a CSV with pandas and want to practise answering a real public-health question with a small, tidy table. The question is simple to ask and surprisingly revealing: which COVID vaccines did each country actually use, who reported that information, and how up to date is it?
The dataset, vaccination_locations_raw.csv, has one row per country or territory. The columns that matter are location, iso_code (the three-letter country code), vaccines (a comma-separated list of brand names), last_observation_date, and source_name and source_website, which record the reporting body.
In this lesson you will:
- check a dataset for missing values and duplicate keys before trusting it
- turn a comma-separated text column into counts and a worldwide brand tally
- measure how stale each country's reporting is using date arithmetic
- flag non-country rows and build simple boolean filters for equity questions
You should be comfortable with basic pandas selection, value_counts() and describe(); see earlier lessons in the series if not.
Part 1: A Real Global Dataset#
Before any analysis you need to know how big the table is, because every share and count later depends on it. The code imports pandas, numpy, matplotlib and Counter from Python's collections module (used later to tally vaccine brands), then reads the CSV with pd.read_csv. The .shape attribute returns rows and columns as a pair. The output, (223, 6), tells you there are 223 locations and 6 columns. That is small enough to inspect by eye, which is a good habit to keep for sanity checks.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from collections import Counter
vax = pd.read_csv('vaccination_locations_raw.csv')
vax.shape
Part 2: Real Data Preview#
A quick preview tells you what each column actually contains and what names you must type later. The code calls vax.head(5) and then vax.columns.tolist(), but Jupyter only displays the last expression in a cell, so the stored output is just the list of column names: location, iso_code, vaccines, last_observation_date, source_name and source_website. To see the first rows as well, run head(5) on its own. Note that the vaccine brands sit inside a single text column, which shapes how the rest of the lesson works.
vax.head(5)
vax.columns.tolist()
Part 3: Real Missing Data Check#
Missing values silently break counts and averages, so checking for them early saves confusion later. vax.isna() returns True wherever a value is missing, and .sum() adds up the Trues column by column, giving a missing count per column. The output shows 0 for every one of the six columns. That is unusually clean for real data, and it means you can split and count the vaccines column without guarding against empty entries. Still, always run this check: a clean result is a finding, not an assumption.
vax.isna().sum()
Part 4: Real Duplicate Check#
Each country should appear exactly once, otherwise any per-country count would be inflated. .duplicated() marks every row whose value has already appeared earlier in the column, and .sum() counts those marks. The code checks both iso_code and location, but only the last line's result is displayed, so the output 0 refers to location. The iso_code check is repeated as a sanity test at the end of the notebook. Tip: if you want both numbers shown, wrap them in a tuple or use print().
vax['iso_code'].duplicated().sum()
vax['location'].duplicated().sum()
Part 5: Real Vaccine Count per Country#
The vaccines column holds a list as text, such as several brand names separated by commas. To compare countries you need a number. The code uses .apply() with a small lambda function that splits each string on ', ' (comma plus space) and takes len() of the resulting list, storing it in a new vaccine_count column. The preview shows Afghanistan with 10 brands, Albania and Algeria with 4, Andorra with 3 and Angola with just 1. Watch the separator: splitting on ',' alone would leave stray spaces in the names.
vax['vaccine_count'] = vax['vaccines'].apply(lambda s: len(s.split(', ')))
vax[['location', 'vaccine_count']].head(5)
Part 6: Real Vaccine Count Distribution#
Before ranking individual countries, it helps to know what a typical value looks like. .describe() gives the count, mean, standard deviation, minimum, quartiles and maximum of vaccine_count. The output shows 223 countries, a mean of about 4.3 brands and a median (the 50% line) of 4. A quarter of locations used 2 or fewer, a quarter used 5 or more, and the range runs from 1 to 12. The mean and median are close, so a handful of very diverse countries is not pulling the average far.
vax['vaccine_count'].describe()
Part 7: Real Most Vaccine-Diverse Countries#
Ranking the top end shows which countries drew on the widest mix of suppliers. nlargest(5, 'vaccine_count') sorts by that column and keeps the top five rows, and the double square brackets select just the location and vaccine_count columns. The output puts Iran first with 12 brands, followed by Afghanistan, Bahrain, Djibouti and Egypt, each with 10. Note that ties are common at this level, so when several countries share a value, nlargest keeps whichever comes first in the data, which here is alphabetical order.
most_diverse = vax.nlargest(5, 'vaccine_count')[['location', 'vaccine_count']]
most_diverse
Part 8: Real Single-Vaccine Countries#
Countries that relied on a single brand are the other end of the story, and often point to supply or equity constraints. The code filters with a boolean mask, vax['vaccine_count'] == 1, and keeps location and vaccines. It then calls len(single_vax) and single_vax.head(10), but only the last line is shown, so the output is the first ten rows rather than the count. Oxford/AstraZeneca appears for six of these ten, including Angola, Liberia and Mali, Chad used only Sinopharm/Beijing, and Greenland used only Moderna. Run len(single_vax) separately to see the total.
single_vax = vax[vax['vaccine_count'] == 1][['location', 'vaccines']]
len(single_vax)
single_vax.head(10)
Part 9: Real Most-Used Vaccine Brands Worldwide#
To count brands worldwide you first need each country's text turned into a real Python list. .str.split(', ') applies the split to every row at once, without a lambda, and returns a Series where each value is a list of brand names. The output shows the first three rows: Afghanistan's list starts with CanSino and Covaxin, and Albania's and Algeria's start with Oxford/AstraZeneca. The lists are cut off with ... in the display; that is only pandas shortening long values, not missing data.
vax['vaccines'].str.split(', ').head(3)
Part 10: Real Brand Tally#
With lists in hand, you can count how many countries used each brand. Calling .sum() on a Series of lists joins them into one long list, and Counter counts how often each name appears. The code wraps that in a pd.Series, sorts from largest to smallest and keeps the top 15. Oxford/AstraZeneca leads with 187 countries, then Pfizer/BioNTech with 167, Moderna with 115 and Johnson&Johnson with 106. After Sinopharm/Beijing on 96, the counts drop away, and brands like Sanofi/GSK, Soberana02 and Abdala appear in only 4 countries each.
all_brands = vax['vaccines'].str.split(', ').sum()
brand_counts = Counter(all_brands)
top_brands = pd.Series(brand_counts).sort_values(ascending=False).head(15)
top_brands
Part 11: Real Top Brands Chart#
A ranked table is precise, but a chart makes the gap between the leaders and the rest obvious at a glance. The code creates a figure with plt.subplots(figsize=(10, 6)), sorts the brands in ascending order so the largest bar ends up at the top, and draws a horizontal bar chart with kind='barh'. It labels the axis, adds a title, then saves the image with savefig and closes it, so nothing is displayed inline. When you open top_vaccine_brands.png, look for the steep step down after the first five or so brands.
fig, ax = plt.subplots(figsize=(10, 6))
top_brands.sort_values().plot(kind='barh', ax=ax, color='teal')
ax.set_xlabel('Real Number of Countries Using This Brand')
ax.set_title('Real Most-Used COVID Vaccine Brands Worldwide')
plt.tight_layout()
plt.savefig('top_vaccine_brands.png', dpi=100)
plt.close()
Part 12: Real Reporting Source Breakdown#
Knowing who reported the data tells you how much to trust it and how consistent it is between countries. value_counts() counts each distinct source_name, sorted from most to least common, and .head(10) keeps the top ten. The World Health Organization is the source for 87 locations, far ahead of generic Ministry of Health entries on 29 and the SPC Public Health Division (which covers Pacific islands) on 14. Notice that names like Ministry of Health are generic labels shared by different countries, so they are not one single body.
source_counts = vax['source_name'].value_counts().head(10)
source_counts
Part 13: Real WHO Reliance Share#
It is useful to express the WHO's role as a share of all locations rather than a raw count. Comparing source_name to 'World Health Organization' gives a True/False Series, and .mean() on booleans returns the share of Trues, because True counts as 1 and False as 0. Multiplying by 100 and rounding to one decimal place gives a percentage. The output is 39.0, so about two in five locations rely on the WHO for this information, which matches 87 out of 223.
who_share = round((vax['source_name'] == 'World Health Organization').mean() * 100, 1)
who_share
Part 14: Real Date Parsing#
Dates stored as text cannot be compared or subtracted, so they must be converted first. pd.to_datetime turns the last_observation_date column into proper datetime values, and the code then shows the earliest and latest with .min() and .max(). The output shows a range from 14 April 2021 to 14 August 2024. That is more than three years between the oldest and newest report, which already hints that some countries stopped updating long ago. Always check the min and max after parsing, as a strange year often reveals a parsing error.
vax['last_observation_date'] = pd.to_datetime(vax['last_observation_date'])
vax['last_observation_date'].min(), vax['last_observation_date'].max()
Part 15: Real Reporting Recency#
Recency matters because comparing a 2021 snapshot with a 2024 one is not a fair comparison. The code treats the newest date in the data as the reference point and subtracts each country's date from it. Subtracting datetimes gives a time difference, and .dt.days turns that into whole days, stored in days_since. The describe() output shows a median of 460 days and a mean of about 458, with the most current reporter at 0 days and the stalest at 1,218. Remember this is relative to the latest report in the file, not to today.
reference_date = vax['last_observation_date'].max()
vax['days_since'] = (reference_date - vax['last_observation_date']).dt.days
vax['days_since'].describe()
Part 16: Real Most Stale Reporters#
Listing the stalest reporters shows where the data is least reliable. nlargest(5, 'days_since') keeps the five rows with the most days since their last update. The Falkland Islands lead at 1,218 days (last report 14 April 2021), followed by Saint Helena at 1,197, Bonaire Sint Eustatius and Saba at 1,078, Monaco at 967 and Bosnia and Herzegovina at 928. Most of these are small territories, which is worth keeping in mind before drawing conclusions about them from this table.
stalest = vax.nlargest(5, 'days_since')[['location', 'last_observation_date', 'days_since']]
stalest
Part 17: Real Most Current Reporters#
The opposite view shows which countries kept reporting into the most recent period. nsmallest(5, 'days_since') returns the five rows with the fewest days since update. Lithuania and Malaysia report on 14 August 2024, the reference date, so both show 0 days. Estonia and India follow at 2 days and New Zealand at 3. These are the countries whose vaccine lists you can treat as most current. Tip: nsmallest and nlargest are quicker to read than a full sort_values followed by head.
freshest = vax.nsmallest(5, 'days_since')[['location', 'last_observation_date', 'days_since']]
freshest
Part 18: Real Share of Genuinely Stale Data#
A single headline figure for staleness is easier to communicate than a distribution. The comparison vax['days_since'] > 365 marks every location whose last report is more than a year older than the newest one. .sum() counts those Trues and .mean() gives their share, turned into a percentage. The output shows 148 locations, or 66.4%, are over a year stale. In other words, about two thirds of this snapshot describes the situation well before the latest date in the file.
stale_count = (vax['days_since'] > 365).sum()
stale_share = round((vax['days_since'] > 365).mean() * 100, 1)
stale_count, stale_share
Part 19: Real Diversity vs Recency Relationship#
It is natural to ask whether countries that used more brands also reported more recently. .corr() computes the Pearson correlation between vaccine_count and days_since, a number between -1 and 1 where 0 means no straight-line relationship. The output is -0.142: a slightly negative value, meaning more diverse countries tend to be a little more current, but the link is weak. Treat it as a hint rather than a finding, and remember correlation says nothing about cause.
diversity_recency_corr = round(vax['vaccine_count'].corr(vax['days_since']), 3)
diversity_recency_corr
Part 20: Real Subnational Entities Flag#
Not every row is a sovereign country. Our World in Data, the usual source of this table, gives non-standard places codes beginning with OWID. The code builds a mask with .str.startswith('OWID'), then lists the matching locations. Only the last line is displayed, so the output is the list: England, Kosovo, Northern Cyprus, Northern Ireland, Scotland and Wales. That is six rows, and four of them are parts of the United Kingdom, which can lead to double counting if the UK also appears.
is_subnational = vax['iso_code'].str.startswith('OWID')
is_subnational.sum()
vax[is_subnational]['location'].tolist()
Part 21: Real Country-Only View#
Removing the subnational rows gives a cleaner country-level base for averages. The ~ operator flips the mask, so vax[~is_subnational] keeps every row that does not start with OWID. .copy() makes an independent DataFrame, which avoids pandas warnings if you add columns to it later. The output shape is (217, 8): 223 rows minus the 6 flagged, and 8 columns because vaccine_count and days_since were added earlier. Always check the shape after a filter to confirm you removed what you expected.
countries_only = vax[~is_subnational].copy()
countries_only.shape
Part 22: Real Country-Only Average Diversity#
With the country-only view, you can check whether the subnational rows were distorting the average. The code takes .mean() of vaccine_count on countries_only and rounds to two decimals. The output is 4.34, almost identical to the 4.30 mean across all 223 locations shown earlier. So the six subnational rows make little difference here. That is still worth verifying: in other metrics, such as population totals, the same rows could cause real double counting.
country_avg_diversity = round(countries_only['vaccine_count'].mean(), 2)
country_avg_diversity
Part 23: Real Sputnik V Global Reach#
Counting a single brand's reach is a quick way to answer a targeted question. .str.contains('Sputnik V') returns True for every row whose vaccine list includes that text, and .sum() counts them. The output is 67 countries, which matches the Sputnik V figure in the brand tally. Be careful with str.contains: it matches substrings, so searching for a short name could also catch longer names that include it. Here 'Sputnik V' does not match Sputnik Light, so the count is correct.
sputnik_countries = vax['vaccines'].str.contains('Sputnik V').sum()
sputnik_countries
Part 24: Real mRNA Vaccine Reach#
mRNA vaccines (Pfizer/BioNTech and Moderna) used a newer technology and needed cold storage, so their reach is an interesting measure of access. str.contains treats its pattern as a regular expression by default, and the | symbol means OR, so this counts rows mentioning either brand. The output is 170 countries. That is more than Pfizer/BioNTech's 167 alone, showing only a few countries used Moderna without Pfizer. Note that the / in the names is fine, but characters like ( or + would need escaping.
mrna_countries = vax['vaccines'].str.contains('Pfizer/BioNTech|Moderna').sum()
mrna_countries
Part 25: Real Non-Western-Only Countries#
To find countries that used no Western-developed vaccines, you first need to define what counts as Western. This step simply creates a Python list, western_brands, holding Pfizer/BioNTech, Moderna, Oxford/AstraZeneca and Johnson&Johnson. There is no output because assigning a variable displays nothing. Keeping the definition in one named list makes the next step easier to read and lets you change the definition in a single place if you want to test a different grouping.
western_brands = ['Pfizer/BioNTech', 'Moderna', 'Oxford/AstraZeneca', 'Johnson&Johnson']
Part 26: Real Non-Western-Only Check#
This check answers a concrete equity question: which countries had none of the Western brands at all? The lambda splits each country's vaccine string and uses any() to test whether at least one brand is in western_brands, giving a True/False column has_western. Flipping it with ~ keeps the countries without any. The output shows 8 countries: Belarus, Chad, China, Cuba, Equatorial Guinea, Kyrgyzstan, Russia and Venezuela. Splitting first, rather than using str.contains, ensures exact brand names are compared.
has_western = vax['vaccines'].apply(lambda v: any(w in v.split(', ') for w in western_brands))
non_western_only = vax[~has_western]['location'].tolist()
len(non_western_only), non_western_only
Part 27: Saving the Real Enriched Dataset#
Saving the enriched table means later work can start from the cleaned, extended version instead of repeating every step. to_csv with index=False writes the file without the pandas row numbers as an extra column. The code then reads it back and compares shapes, and the output True confirms the same rows and columns came back. Bear in mind that a CSV does not store data types, so last_observation_date will reload as text unless you parse it again.
vax.to_csv('vaccination_enriched.csv', index=False)
reloaded = pd.read_csv('vaccination_enriched.csv')
reloaded.shape == vax.shape
Part 28: Real Sanity Check, Row Count#
Simple assertions at the end of a notebook protect you from silent changes. This line checks that the table still has exactly 223 rows, the number seen at the very start. The output is True, so no step accidentally dropped or duplicated rows. If you rerun the notebook on a newer version of the data, a False here is a useful early warning that something has changed and needs a look.
len(vax) == 223
Part 29: Real Sanity Check, No Duplicate Codes#
This repeats the key check that each ISO code appears only once, now as a yes/no test. duplicated().sum() == 0 returns True if there are no repeated codes. The output is np.True_, which is just numpy's way of displaying True. Because the earlier duplicate check only displayed the location result, this is the step that actually confirms iso_code is a clean unique key, which matters if you ever merge this table with another dataset.
vax['iso_code'].duplicated().sum() == 0
Part 30: Real Recap Print#
Ending with a one-sentence summary forces you to pick the numbers that matter. The code uses an f-string, a string starting with f where values in curly braces are filled in, to combine the row count, the rounded mean brand count, the WHO share and the stale share. The output reads: across 223 locations, the average country used 4.3 brands, 39.0% reported through the WHO, and 66.4% of figures are over a year stale. A summary like this is a good basis for a stakeholder update.
print(f'Across {len(vax)} real locations, the average country used {round(vax["vaccine_count"].mean(), 1)} real vaccine brands, {who_share}% reported through the WHO, and {stale_share}% of last-reported figures are genuinely over a year stale.')
Wrap-Up: What You Learned#
- This real global snapshot covers two hundred twenty-three real countries and territories, with genuinely zero missing values or duplicate codes.
- Oxford slash AstraZeneca and Pfizer slash BioNTech were the real two most widely used vaccine brands worldwide, by a real wide margin.
- Twenty-nine real countries relied on just a real single vaccine brand for their entire campaign, a real stark equity gap.
- Two thirds of countries in this dataset have last-reported figures genuinely over a year stale relative to the most current reporter, a real reminder to always check a dataset's real freshness.
- A real handful of countries reported zero doses of any Western-developed vaccine, a real concrete equity finding worth sitting with.
- Next video: a real global child mortality analysis, continuing this domain with another genuinely global public health snapshot.
Practice on your own
- Run
len(single_vax)on its own, then usevalue_counts()onsingle_vax['vaccines']to see which brand single-vaccine countries depended on most. - Group by
source_nameand take the median ofdays_sincefor the five most common sources. Do WHO-reported locations tend to be more or less current than those reported by a Ministry of Health? - Build a boolean column for whether each country used any Chinese-developed vaccine (Sinopharm/Beijing, Sinovac or CanSino), then count how many countries used both a Chinese brand and an mRNA brand.
Try each one, and check the answers make sense against the counts already shown in this notebook. The next lesson in the series moves on to a global child mortality analysis, another public-health snapshot built on the same habits of checking completeness and freshness first.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



