Lesson 58 · Pandas Full Course
Clean and Analyse Survey Data in Pandas (Real Salary Survey) | Pandas Course #58
In this lesson you'll be able to: Check who answered a survey before trusting its results Put answer bands like ages and experience in the right order Split…
- CoursePandas Full Course
- Lesson58 of 65
- FormatJupyter notebook · 47 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.
- salary_survey.csv preview4.9 MB
📓 Full notebook
Download .ipynbPandas 58: Cleaning and Analysing Survey Data#
In this lesson you'll be able to:
- Check who answered a survey before trusting its results
- Put answer bands like ages and experience in the right order
- Split tick-all-that-apply answers into countable choices
- Convert salaries to one currency and read shares with crosstab
- Compare pay fairly by holding experience fixed Data: Ask a Manager Salary Survey 2021, 26,232 real answers
Warm-up: an EDA first look#
Lesson 57 began every project with an audit. Same routine here.
import pandas as pd
raw = pd.read_csv("data/pandas/salary_survey.csv")
print(raw.shape)
gaps = raw.isna().mean().mul(100).round(1)
gaps.sort_values(ascending=False).head(5)
Step 1: Check who answered#
A survey speaks only for the people who chose to fill it in.
names = {"how_old_are_you": "age", "annual_salary": "salary",
"other_monetary_comp": "bonus",
"years_of_experience_in_field": "years_field",
"highest_level_of_education_completed": "education"}
df = raw.rename(columns=names)
df[["age", "salary", "currency", "years_field"]].head(3)
Quick check: what share of respondents do you think are women?
shares = df["gender"].value_counts(normalize=True, dropna=False)
shares.mul(100).round(1)
fix = {"Prefer not to answer": "Other or prefer not to answer"}
df["gender"] = df["gender"].replace(fix).fillna("No answer")
df["gender"].value_counts()
df["industry"].value_counts(normalize=True).head(5).round(3)
fmt = "%m/%d/%Y %H:%M:%S"
when = pd.to_datetime(df["timestamp"], format=fmt)
print(when.min(), when.max())
when.dt.date.value_counts().sort_index().head(3)
print(df["country"].nunique())
df["country"].value_counts().head(4)
Your turn: what share of answers were paid in US dollars?
usd_share = (df["currency"] == "USD").mean()
print(f"{usd_share:.1%} of answers are in US dollars")
Step 2: Put answer bands in order#
Bands like 25-34 sort as text unless pandas knows their order.
df["age"].value_counts().sort_index()
ages = ["under 18", "18-24", "25-34", "35-44", "45-54", "55-64",
"65 or over"]
df["age"] = pd.Categorical(df["age"], categories=ages, ordered=True)
df["age"].value_counts().sort_index()
import matplotlib.pyplot as plt
counts = df["age"].value_counts().sort_index()
ax = counts.plot(kind="bar", rot=0, figsize=(8, 4))
ax.set_title("Survey respondents by age band")
ax.set_xlabel("Age band")
ax.set_ylabel("Respondents")
plt.show()
Quick check: spot the problem in these experience labels.
df["years_field"].unique()[:5].tolist()
tidy = df["years_field"].str.replace(" - ", "-")
bands = ["1 year or less", "2-4 years", "5-7 years", "8-10 years",
"11-20 years", "21-30 years", "31-40 years",
"41 years or more"]
df["years_field"] = pd.Categorical(tidy, categories=bands,
ordered=True)
print(df["years_field"].isna().sum())
senior = df["years_field"] >= "11-20 years"
print(senior.mean().round(3))
Your turn: put education in order, from High School to PhD, and count it.
levels = ["High School", "Some college", "College degree",
"Master's degree", "Professional degree (MD, JD, etc.)",
"PhD"]
df["education"] = pd.Categorical(df["education"], categories=levels,
ordered=True)
df["education"].value_counts().sort_index()
Step 3: Split tick-all-that-apply answers#
One cell can hold several choices, joined by commas.
race = df["race"].dropna()
multi = race.str.contains(", ")
print(multi.sum(), len(race))
race[multi].head(3).tolist()
Common mistake: calling unique on split lists
choices = race.str.split(", ")
choices.unique()
one_per_row = choices.explode()
print(one_per_row.nunique())
one_per_row.value_counts()
Quick check: three of those choices appear exactly the same number of times. Why?
long = "Hispanic, Latino, or Spanish origin"
race = race.str.replace(long, "Hispanic or Latino", regex=False)
race.str.split(", ").explode().value_counts().head(4)
ticks = race.str.get_dummies(sep=", ")
print(ticks.shape)
ticks.mean().sort_values(ascending=False).round(3)
print(round(ticks.mean().sum(), 3))
ticks.sum(axis=1).value_counts()
Your turn: what share of people ticked more than one box?
several = ticks.sum(axis=1) > 1
print(f"{several.mean():.1%} ticked more than one box")
Step 4: Make salaries comparable#
Salaries arrive in many currencies. Convert before you compare.
df["currency"].value_counts().head(6)
Quick check: do people paid in yen really earn the most?
raw_median = df.groupby("currency")["salary"].median()
raw_median.sort_values().tail(3)
Common mistake: sorting a Series with by=
raw_median.sort_values(by="salary")
raw_median.sort_values(ascending=False).head(3)
rates = {"USD": 1.0, "CAD": 0.80, "GBP": 1.38, "EUR": 1.18,
"AUD/NZD": 0.74}
df["salary_usd"] = df["salary"] * df["currency"].map(rates)
print(df["salary_usd"].notna().sum())
usd = df.groupby("currency")["salary_usd"].median().dropna()
usd.sort_values(ascending=False).round(0)
ax = usd.sort_values().plot(kind="barh", figsize=(8, 3))
ax.set_title("Median salary in US dollars, by pay currency")
ax.set_xlabel("Median salary (USD, approximate 2021 rates)")
ax.set_ylabel("Currency")
plt.show()
tiny = (df["salary_usd"] < 10_000).sum()
huge = (df["salary_usd"] > 1_000_000).sum()
print(tiny, huge)
ok = df["salary_usd"].between(10_000, 1_000_000)
clean = df[ok].copy()
clean["bonus"] = clean["bonus"].fillna(0)
clean["bonus_usd"] = clean["bonus"] * clean["currency"].map(rates)
print(len(clean))
has_bonus = clean["bonus_usd"] > 0
print(round(has_bonus.mean(), 3))
print(clean.loc[has_bonus, "bonus_usd"].median().round(0))
Step 5: Read shares with crosstab#
Survey questions are compared as percentages, not raw counts.
pair = clean[clean["gender"].isin(["Woman", "Man"])]
pd.crosstab(pair["gender"], pair["years_field"])
high = pair["salary_usd"] >= 100_000
pd.crosstab(pair["gender"], high, margins=True)
med = pd.crosstab(pair["gender"], pair["age"],
values=pair["salary_usd"], aggfunc="median")
med.loc[:, "25-34":"55-64"].round(-3)
Common mistake: guessing the normalize keyword
pd.crosstab(pair["gender"], pair["age"], normalize="rows")
by_age = pd.crosstab(pair["gender"], pair["age"], normalize="index")
by_age.mul(100).round(1)
tech = pair["industry"] == "Computing or Tech"
pd.crosstab(pair["gender"], tech, normalize="index").mul(100).round(1)
Your turn: what percentage of women and of men hold a PhD?
edu = pd.crosstab(pair["gender"], pair["education"],
normalize="index")
edu[["Master's degree", "PhD"]].mul(100).round(1)
Mini-project: same experience, same pay?#
US-dollar salaries, women and men, compared band by band.
us = pair[pair["currency"] == "USD"]
overall = us.groupby("gender")["salary"].median()
print(overall)
print(round(overall["Woman"] / overall["Man"], 2))
gap = us.pivot_table(index="years_field", columns="gender",
values="salary", aggfunc="median",
observed=True)
gap["ratio"] = gap["Woman"] / gap["Man"]
gap.round(2)
us.groupby(["years_field", "gender"], observed=True).size().unstack()
main = gap.loc[:"31-40 years", "ratio"]
ax = main.plot(kind="bar", rot=30, figsize=(8, 4))
ax.axhline(1, color="grey", linestyle="--")
ax.set_title("Women's median pay as a share of men's")
ax.set_xlabel("Years of experience in the field")
ax.set_ylabel("Ratio (women / men)")
plt.show()
in_tech = us[us["industry"] == "Computing or Tech"]
t = in_tech.groupby("gender")["salary"].agg(["median", "count"])
print(t)
print(round(t.loc["Woman", "median"] / t.loc["Man", "median"], 2))
top4 = us["industry"].value_counts().head(4).index
ind = us[us["industry"].isin(top4)].pivot_table(
index="industry", columns="gender", values="salary",
aggfunc="median")
ind["ratio"] = ind["Woman"] / ind["Man"]
ind.round(2)
lo, hi = gap.loc[:"31-40 years", "ratio"].agg(["min", "max"])
print(f"Same experience: women earn {lo:.0%} to {hi:.0%} of men,")
print("and the gap grows through the first 30 years.")
import os
os.makedirs("output/pandas", exist_ok=True)
gap.to_csv("output/pandas/l58_pay_gap.csv")
back = pd.read_csv("output/pandas/l58_pay_gap.csv")
back.round(2).head(3)
Recap#
- Check who answered: a survey speaks only for its respondents
- Ordered categoricals put bands like 25-34 in the right order
- Split multi-choice cells with explode or get_dummies; watch commas
- Convert money to one currency and validate it before comparing
- Compare shares with crosstab, and hold factors fixed
Challenges#
- Repeat the pay-gap table for GBP answers, in pounds.
- Find the median USD salary for each education level.
- Which race choice is most often ticked alongside another?
What's next#
Lesson 59: Analysing Financial and Stock Market Data Daily prices, returns, moving averages and risk.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



