Mathew K Analytics

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…

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

Pandas 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)
(26232, 18)
currency_other                     99.4
additional_context_on_income       89.1
additional_context_on_job_title    74.2
other_monetary_comp                25.9
state                              17.9
dtype: float64

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)
age salary currency years_field
0 25-34 55000 USD 5-7 years
1 25-34 54600 GBP 5-7 years
2 25-34 34000 USD 2 - 4 years

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)
gender
Woman                            77.6
Man                              18.1
Non-binary                        2.7
Other or prefer not to answer     1.0
NaN                               0.6
Prefer not to answer              0.0
Name: proportion, dtype: float64
fix = {"Prefer not to answer": "Other or prefer not to answer"}
df["gender"] = df["gender"].replace(fix).fillna("No answer")
df["gender"].value_counts()
gender
Woman                            20359
Man                               4743
Non-binary                         713
Other or prefer not to answer      269
No answer                          148
Name: count, dtype: int64
df["industry"].value_counts(normalize=True).head(5).round(3)
industry
Computing or Tech                       0.167
Education (Higher Education)            0.090
Nonprofits                              0.089
Government and Public Administration    0.070
Health care                             0.066
Name: proportion, dtype: float64
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)
2021-04-27 11:02:10 2021-05-17 19:48:52
timestamp
2021-04-27    11257
2021-04-28     7107
2021-04-29     2899
Name: count, dtype: int64
print(df["country"].nunique())
df["country"].value_counts().head(4)
294
country
United States    9010
USA              7918
US               2485
Canada           1543
Name: count, dtype: int64

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")
83.3% 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()
age
18-24          1015
25-34         11748
35-44          9398
45-54          3042
55-64           931
65 or over       88
under 18         10
Name: count, dtype: int64
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()
age
under 18         10
18-24          1015
25-34         11748
35-44          9398
45-54          3042
55-64           931
65 or over       88
Name: count, dtype: int64
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()
No description has been provided for this image

Quick check: spot the problem in these experience labels.

df["years_field"].unique()[:5].tolist()
['5-7 years',
 '2 - 4 years',
 '21 - 30 years',
 '11 - 20 years',
 '1 year or less']
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())
0
senior = df["years_field"] >= "11-20 years"
print(senior.mean().round(3))
0.321

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()
education
High School                             568
Some college                           1880
College degree                        12584
Master's degree                        8387
Professional degree (MD, JD, etc.)     1261
PhD                                    1356
Name: count, dtype: int64

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()
1677 26086
['Hispanic, Latino, or Spanish origin, White',
 'Hispanic, Latino, or Spanish origin, White',
 'Asian or Asian American, White']

Common mistake: calling unique on split lists

choices = race.str.split(", ")
choices.unique()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[18], line 2
      1 choices = race.str.split(", ")
----> 2 choices.unique()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:2228, in Series.unique(self)
   2163 def unique(self) -> ArrayLike:
   2164     """
   2165     Return unique values of Series object.
   2166 
   (...)
   2226     Categories (3, str): ['a' < 'b' < 'c']
   2227     """
-> 2228     return super().unique()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\base.py:1159, in IndexOpsMixin.unique(self)
   1157     result = values.unique()
   1158 else:
-> 1159     result = algorithms.unique1d(values)  # type: ignore[assignment]
   1160 return result

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\algorithms.py:433, in unique(values)
    327 @set_module("pandas")
    328 def unique(values):
    329     """
    330     Return unique values based on a hash table.
    331 
   (...)
    431     Length: 3, dtype: complex128
    432     """
--> 433     return unique_with_mask(values)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\algorithms.py:476, in unique_with_mask(values, mask)
    474 table = hashtable(len(values))
    475 if mask is None:
--> 476     uniques = table.unique(values)
    477     uniques = _reconstruct_data(uniques, original.dtype, original)
    478     return uniques

File pandas/_libs/hashtable_class_helper.pxi:7840, in pandas._libs.hashtable.PyObjectHashTable.unique()

File pandas/_libs/hashtable_class_helper.pxi:7783, in pandas._libs.hashtable.PyObjectHashTable._unique()

TypeError: unhashable type: 'list'
one_per_row = choices.explode()
print(one_per_row.nunique())
one_per_row.value_counts()
9
race
White                                                     22975
Asian or Asian American                                    1610
Hispanic                                                    968
Latino                                                      968
or Spanish origin                                           968
Black or African American                                   806
Another option not listed here or prefer not to answer      660
Middle Eastern or Northern African                          157
Native American or Alaska Native                            142
Name: count, dtype: int64

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)
race
White                        22975
Asian or Asian American       1610
Hispanic or Latino             968
Black or African American      806
Name: count, dtype: int64
ticks = race.str.get_dummies(sep=", ")
print(ticks.shape)
ticks.mean().sort_values(ascending=False).round(3)
(26086, 7)
White                                                     0.881
Asian or Asian American                                   0.062
Hispanic or Latino                                        0.037
Black or African American                                 0.031
Another option not listed here or prefer not to answer    0.025
Middle Eastern or Northern African                        0.006
Native American or Alaska Native                          0.005
dtype: float64
print(round(ticks.mean().sum(), 3))
ticks.sum(axis=1).value_counts()
1.047
1    24930
2     1086
3       64
4        6
Name: count, dtype: int64

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")
4.4% 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)
currency
USD        21852
CAD         1564
GBP         1521
EUR          585
AUD/NZD      469
Other        133
Name: count, dtype: int64

Quick check: do people paid in yen really earn the most?

raw_median = df.groupby("currency")["salary"].median()
raw_median.sort_values().tail(3)
currency
SEK     481800.0
ZAR     690000.0
JPY    4700000.0
Name: salary, dtype: float64

Common mistake: sorting a Series with by=

raw_median.sort_values(by="salary")
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[26], line 1
----> 1 raw_median.sort_values(by="salary")

TypeError: Series.sort_values() got an unexpected keyword argument 'by'
raw_median.sort_values(ascending=False).head(3)
currency
JPY    4700000.0
ZAR     690000.0
SEK     481800.0
Name: salary, dtype: float64
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())
25991
usd = df.groupby("currency")["salary_usd"].median().dropna()
usd.sort_values(ascending=False).round(0)
currency
USD        79000.0
AUD/NZD    70300.0
EUR        64900.0
CAD        60800.0
GBP        55200.0
Name: salary_usd, dtype: float64
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()
No description has been provided for this image
tiny = (df["salary_usd"] < 10_000).sum()
huge = (df["salary_usd"] > 1_000_000).sum()
print(tiny, huge)
113 14
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))
25864
has_bonus = clean["bonus_usd"] > 0
print(round(has_bonus.mean(), 3))
print(clean.loc[has_bonus, "bonus_usd"].median().round(0))
0.456
6900.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"])
years_field 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
gender
Man 150 807 981 770 1339 494 87 17
Woman 986 4479 4784 3699 4668 1211 263 21
high = pair["salary_usd"] >= 100_000
pd.crosstab(pair["gender"], high, margins=True)
salary_usd False True All
gender
Man 2388 2257 4645
Woman 14919 5192 20111
All 17307 7449 24756
med = pd.crosstab(pair["gender"], pair["age"],
                  values=pair["salary_usd"], aggfunc="median")
med.loc[:, "25-34":"55-64"].round(-3)
age 25-34 35-44 45-54 55-64
gender
Man 83000.0 106000.0 120000.0 115000.0
Woman 68000.0 80000.0 80000.0 78000.0

Common mistake: guessing the normalize keyword

pd.crosstab(pair["gender"], pair["age"], normalize="rows")
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:1154, in _normalize(table, normalize, margins, margins_name)
   1153 try:
-> 1154     f = normalizers[normalize]
   1155 except KeyError as err:

KeyError: 'rows'

The above exception was the direct cause of the following exception:

ValueError                                Traceback (most recent call last)
Cell In[37], line 1
----> 1 pd.crosstab(pair["gender"], pair["age"], normalize="rows")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:1123, in crosstab(index, columns, values, rownames, colnames, aggfunc, margins, margins_name, dropna, normalize)
   1121 # Post-process
   1122 if normalize is not False:
-> 1123     table = _normalize(
   1124         table, normalize=normalize, margins=margins, margins_name=margins_name
   1125     )
   1127 table = table.rename_axis(index=rownames_mapper, axis=0)
   1128 table = table.rename_axis(columns=colnames_mapper, axis=1)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\pivot.py:1156, in _normalize(table, normalize, margins, margins_name)
   1154     f = normalizers[normalize]
   1155 except KeyError as err:
-> 1156     raise ValueError("Not a valid normalize argument") from err
   1158 table = f(table)
   1159 table = table.fillna(0)

ValueError: Not a valid normalize argument
by_age = pd.crosstab(pair["gender"], pair["age"], normalize="index")
by_age.mul(100).round(1)
age under 18 18-24 25-34 35-44 45-54 55-64 65 or over
gender
Man 0.0 3.2 39.3 40.8 13.1 3.2 0.4
Woman 0.0 3.8 45.4 35.3 11.5 3.7 0.3
tech = pair["industry"] == "Computing or Tech"
pd.crosstab(pair["gender"], tech, normalize="index").mul(100).round(1)
industry False True
gender
Man 61.4 38.6
Woman 88.8 11.2

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)
education Master's degree PhD
gender
Man 26.0 5.3
Woman 33.7 5.2

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))
gender
Man      104000.0
Woman     75000.0
Name: salary, dtype: float64
0.72
gap = us.pivot_table(index="years_field", columns="gender",
                     values="salary", aggfunc="median",
                     observed=True)
gap["ratio"] = gap["Woman"] / gap["Man"]
gap.round(2)
gender Man Woman ratio
years_field
1 year or less 59000.0 56000.0 0.95
2-4 years 73000.0 62500.0 0.86
5-7 years 92000.0 72000.0 0.78
8-10 years 105000.0 80673.0 0.77
11-20 years 126509.0 89000.0 0.70
21-30 years 143000.0 94000.0 0.66
31-40 years 131000.0 91000.0 0.69
41 years or more 120500.0 95000.0 0.79
us.groupby(["years_field", "gender"], observed=True).size().unstack()
gender Man Woman
years_field
1 year or less 122 796
2-4 years 639 3701
5-7 years 775 4088
8-10 years 633 3197
11-20 years 1060 4052
21-30 years 392 1055
31-40 years 71 235
41 years or more 14 19
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()
No description has been provided for this image
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))
          median  count
gender                 
Man     140000.0   1410
Woman   111000.0   1909
0.79
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)
gender Man Woman ratio
industry
Computing or Tech 140000.0 111000.0 0.79
Education (Higher Education) 70000.0 62000.0 0.89
Health care 89000.0 80000.0 0.90
Nonprofits 71500.0 65000.0 0.91
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.")
Same experience: women earn 66% to 95% of men,
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)
years_field Man Woman ratio
0 1 year or less 59000.0 56000.0 0.95
1 2-4 years 73000.0 62500.0 0.86
2 5-7 years 92000.0 72000.0 0.78

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#

  1. Repeat the pay-gap table for GBP answers, in pounds.
  2. Find the median USD salary for each education level.
  3. 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.