How to Build a Pivot Table From Our World in Data Demographics

Demographic datasets from Our World in Data are excellent for learning data analysis because they contain real-world statistics on population, life expectancy, fertility rates, migration, and age distribution across countries.



One of the fastest ways to summarize this data is with a pivot table. In this tutorial, you will use Python and Pandas in Google Colab to create a demographic pivot table from an Our World in Data dataset.

A pivot table is a data summarization tool used to reorganize, group, and analyze large datasets quickly.

It allows you to turn raw rows of data into meaningful summaries without changing the original dataset.

Why It’s Called a “Pivot” Table

The word “pivot” means you can rotate or rearrange the structure of the data:

  • Rows can become columns
  • Columns can become grouped categories
  • Values can be summarized differently

You are essentially “pivoting” the dataset around specific fields.

Think of a pivot table as:

“A fast way to turn messy rows into organized insights.”

Instead of manually filtering and calculating data, the pivot table does the summarization automatically.


Step 1: Install and Import Pandas

Start by importing Pandas.

import pandas as pd


Step 2: Upload the Demographic Dataset

Download a CSV dataset from Our World in Data Demographics Datasets. Here we use Population Data.

For example, you can use datasets containing:

  • Population

  • Life expectancy

  • Fertility rates

  • Median age

Upload the file into Google Colab.

from google.colab import files

uploaded = files.upload()





Step 3: Load the CSV File

Load the uploaded dataset into a DataFrame.

file_name = list(uploaded.keys())[0]

df = pd.read_csv(file_name)

df.head()

Typical columns may include:

  • country

  • year

  • population

  • life_expectancy



Step 4: Inspect the Dataset

Check the available columns.

print(df.columns)

Example output:

Index(['country', 'year', 'population'], dtype='object')


Step 5: Create the Pivot Table

Now build a pivot table that summarizes population by year and country.

pivot_table = pd.pivot_table(
    df,
    values='population',
    index='year',
    columns='country',
    aggfunc='sum'
)

pivot_table.head()

This creates:

  • Rows = years

  • Columns = countries

  • Values = total population



Step 6: Create a More Advanced Pivot Table

You can also calculate averages instead of totals.

For example, average life expectancy by continent.

pivot_life = pd.pivot_table(
    df,
    values='Population',
    index='year',
    columns='Entity',
    aggfunc='mean'
)

pivot_life.head()


Note: Review other lessons and clean up the Nan Columns.

Step 7: Export the Pivot Table

Save the pivot table as a CSV file.

pivot_table.to_csv("population_pivot.csv")

Download it:

files.download("population_pivot.csv")


Why Pivot Tables Matter

Pivot tables help analysts quickly:

  • Compare demographic trends

  • Summarize millions of rows

  • Analyze population growth

  • Study regional patterns

  • Prepare dashboards for BI tools

They are widely used in data engineering, business intelligence, economics, healthcare analytics, and public policy reporting.

Final Thoughts

Using Pandas pivot tables with datasets from Our World in Data allows you to transform raw demographic data into structured insights in minutes.

For aspiring data analysts and data engineers, mastering pivot tables is one of the most practical skills you can develop for real-world reporting and analytics workflows.



Advance Your Career With 16 Python Projects in Data & ML — All for $288.

Comments

Post a Comment

Popular posts from this blog

How to Filter Rows Using Boolean Indexing in Pandas (Afrobarometer Kenya Dataset)

Image
Learn how to filter rows in Pandas using boolean indexing with real Afrobarometer Kenya survey data. Build clean, ML-ready features with practical examples. Why Boolean Indexing Matters in Feature Engineering In Pandas, boolean indexing is how you select the exact slices of data that become features .  In Module 04 of the course, you’ll no longer exploring—you’ll be deciding what the model sees. In this case, with a dataset like Afrobarometer Kenya Round 10 , filtering is how you: Remove invalid survey responses Isolate specific demographic groups Prepare clean subsets for encoding Load and Inspect the Dataset This assumes that you’ve already converted the .sav file and loaded it: import pandas as pd df = pd.read_csv("afrobarometer_kenya_r10.csv") df.head() 1. Filter Valid Survey Responses Survey datasets contain codes like -1 , 8 , 9 for missing or “Don’t know”. df = df[df["trust_president"] >= 0] This keeps only valid numeric responses for trust in the pres...
Read more

How to Decide Whether to Drop or Fill Missing Value

Image
Learn how to decide whether to drop or fill missing values in datasets using practical, production-ready strategies. Understand trade-offs, bias risks, and data engineering best practices. Missing values are not just a data cleaning nuisance—they’re a signal . The decision to drop or fill them has direct consequences on model performance, statistical validity, and downstream business decisions. If you treat missing data casually, you risk introducing bias, noise, or false confidence into your analysis. This guide gives you a structured, decision-oriented framework you can use in real-world data workflows. 1. First Principle: Understand Why Data Is Missing Before deciding what to do , you need to understand why it’s missing . In data science, this is formalized into three categories: 1. Missing Completely at Random (MCAR) Missingness has no relationship to any variable. Example: A sensor randomly fails. Implication: Safe to drop rows without introducing bias. 2. Missing at Random (MAR...
Read more

How to create your first line chart with World Bank Kenya GDP data

Image
Learn how to pull Kenya GDP data directly from the World Bank API and create your first line chart in Python using pandas and matplotlib—clean, fast, and reproducible. 1. Install Required Libraries !pip install pandas matplotlib wbdata 2. Import Dependencies import pandas as pd import matplotlib.pyplot as plt import wbdata import datetime 3. Define the Data You Need World Bank uses indicators . For GDP (current US$), the indicator is: NY.GDP.MKTP.CD Set the country to Kenya ( KEN ) and define a time range: indicator = {'NY.GDP.MKTP.CD': 'gdp'} data_date = (datetime.datetime(2000, 1, 1), datetime.datetime(2023, 1, 1)) 4. Fetch Data from World Bank df = wbdata.get_dataframe(indicator, country='KEN', data_date=data_date) 5. Clean and Prepare Data df = df.reset_index() df['date'] = pd.to_datetime(df['date']) df = df.sort_values('date') print (df) Explanation df = df.reset_index() → Converts the index into a normal column and resets ro...
Read more