Module 4: pandas and wrangling

PUBHLT 0411 · Python for Public Health Data Analysis

Soumik Purkayastha

Dept. of Biostatistics and Health Data Science, University of Pittsburgh

October 29, 2026

Twenty-three blanks.

Sometimes official statistics aren’t reported because they’re not trustworthy!

Sixty-seven counties, twenty-three blanks

Figure 1. All 67 Pennsylvania counties in alphabetical order, one tile each. Tiles with a published infant mortality rate are shaded blue, darker for higher rates, from Bucks at 3.4 to Mifflin at 10.2 per 1,000. Twenty-three tiles are white with a dashed gold border and a dash in place of a number: Bradford, Cameron, Carbon, Clinton, Columbia, Elk, Forest, Fulton, Greene, Jefferson, Juniata, McKean, Montour, Pike, Potter, Snyder, Sullivan, Susquehanna, Tioga, Union, Warren, Wayne, and Wyoming.

Give them ten seconds with this before saying anything. Then read the number and stop. Twenty-three of sixty-seven. More than a third of the state has no published infant mortality rate.

The instinct in the room will be that somebody is hiding something, and that instinct is worth naming and then dismantling. Nobody is hiding anything. The figure is unstable: a county with four hundred births a year and two infant deaths has a rate of 5.0 per 1,000; one more death makes it 7.5. The rate did not change because health changed, it changed because one family had the worst year of their lives. Publishing that number invites a reader to compare it to Allegheny’s, and the comparison is meaningless. The withholding is a statistical judgment, not an administrative one. That framing carries the whole module.

The question this session opens: which twenty-three, and what do they have in common? The tool for answering it is the DataFrame — a table whose columns keep their names and whose rows stay aligned through every operation. Module 3 ended with two arrays held in alignment by nothing but care. Today that stops being the student’s problem.

The order is alphabetical on purpose. Alphabetical order hides any pattern, so the blanks look scattered — and that is the honest starting point. Nothing on this picture says what the twenty-three share.

Ask the room: do any of these names sound alike? Someone from the northern tier will spot Potter, Tioga, Sullivan. Do not confirm it. Say that a hunch is not an answer, and that answering it needs a second table — county size and rural status are not in this file.

By the end of the session the same twenty-three come back on one figure, sorted by the thing they share.

A DataFrame is a table with named columns

pd.read_csv() reads an entire table in one call. .shape gives rows and columns, .head(n) the first rows, and .dtypes the type of each column.

Inspect before computing

A column read as text when it should be numeric produces no error at read time. The failure appears later, when arithmetic on it returns something wrong or raises TypeError.

Contrast this directly with Module 3 and be explicit about it. Reading two columns there took two genfromtxt calls, and keeping them aligned was a promise nothing enforced. Here one call reads eight columns, and the alignment is structural — it is what a DataFrame is.

.shape returns (67, 8), the same attribute name as NumPy, and that is deliberate: a DataFrame is built on arrays. Rows first, columns second.

import pandas as pd is a convention, like np. Departing from it makes code harder for others to read.

object in the dtype output means text, and it is pandas’ least helpful piece of naming. When a column that should be numeric shows up as object, something in it is not a number — a stray comma, a footnote marker, the word “suppressed.” A thousands separator alone is enough: "1,204" is text, and "712" + "8" gives "7128", not 720. That is a silent wrong answer, worse than an error.

Say the source out loud: County Health Rankings 2024, all 67 Pennsylvania counties. Every number in this module is real and every one of them describes somewhere a student in the room is from.

.info() and .describe() report what is missing

The table has 67 rows. infant_mortality_per_1000 has 44 non-null values.

The gap is the finding, not an obstacle

Twenty-three counties are missing a rate — the answer to the question this session opened, found in the second minute of inspection. In .describe(), count is the number of present values, and every other statistic is computed over it.

This is the moment to slow down. The cold open asked which twenty-three counties, and .info() just found the twenty-three without a single line of analysis. Point at the 44 and let them do the subtraction.

Two other columns have gaps worth naming: teen_births_per_1000 shows 66, and child_mortality_per_100k shows 60. Same reason, same suppression rule, different thresholds because the underlying counts differ.

The count row in .describe() is the one nobody reads and it is the most important one. A mean of 6.15 describes 44 counties, not 67, and the number of counties it describes belongs in any sentence that reports it. Infant mortality across the 44 runs 3.4 to 10.2, median 6.0. Child poverty runs to 28.3% in Philadelphia.

.round(2) is there because the raw output carries six decimal places of false precision. Formatting is graded in this course, so model it.

A column is a Series; .loc and .iloc select rows

A Series is one column: values plus an index that labels them. .count() counts present values; .size counts rows. .loc[row, column] selects by label; .iloc[row, column] by position.

KeyError names a label that is not there

births["infant_mortality"] raises KeyError: 'infant_mortality' — the column is infant_mortality_per_1000. Resolution: print df.columns.tolist() and copy the name.

Run the KeyError broken on the projector. Thirty seconds, and the traceback is ugly enough to be memorable. It is the single most common pandas error, and once they know it means “that name is not in this table,” it stops costing them time. Tell them to copy column names rather than type them.

The index is what makes a Series more than an array, and it is what keeps rows aligned through a sort or a filter. Module 3’s parallel arrays had no index, which is exactly why sorting one and not the other broke them silently.

.mean() on a Series skips missing values by default — the opposite of NumPy, which returns nan. That bites everyone who learned NumPy first, which is everyone in this room. pandas assumes skipping is what was wanted. The cost is that the denominator changes without saying so.

.set_index("county") is what makes .loc worth using. Allegheny is 6.2 per 1,000.

One trap to mention: .loc["Adams":"Beaver"] includes Beaver, while .iloc[0:3] stops at position 2. Label slicing includes the endpoint; position slicing follows Python’s usual rule. Both are defensible; together they are a trap.

A boolean mask filters rows

The mask is Module 3’s boolean array. Passing it to [] keeps the rows where it is True, and every column comes along.

This is what the DataFrame buys

Module 3’s mask filtered one array and left the names behind. Here the county names, the rates, and the incomes all move together, because they are rows of one object.

Eleven counties have child poverty above 20%. Philadelphia is the highest at 28.3%.

Make the Module 3 comparison explicit, because this is the payoff for the complaint that module ended on. There, filtering pm25 gave 22 numbers and no way to know which counties they were without applying the same mask to a second array and trusting the alignment. Here the answer arrives labelled.

The mask itself is worth printing once. births["children_in_poverty_pct"] > 20 is a Series of 67 True/False values with the same index, and it is the identical construct from last week wearing a label.

If someone asks how to match a list of names: births["county"].isin(["Allegheny", "Centre"]) builds the mask in one call, in place of a chain of == joined by |.

Combining conditions requires &, |, and parentheses

The parentheses are mandatory

& binds more tightly than >. Without parentheses, Python evaluates 20 & births[...] first and raises TypeError. This is the most common pandas error after KeyError.

Resolution: parenthesise every comparison. (a > 1) & (b < 2), never a > 1 & b < 2.

This is a check-in. Put the unparenthesised version on the screen, ask what it does, and run it. The error is loud, which is a mercy, but the message — Cannot perform 'rand_' with a dtyped [float64] array and scalar of type [bool] — points at nothing useful. Writing and instead of & gives a different error: “the truth value of a Series is ambiguous.”

The quiet case is worse, and worth thirty seconds if the room is with you. Parenthesise only the first comparison — (a > 6) & b > 15 — and no error is raised at all. The filter returns zero rows. A missing pair of parentheses can produce a plausible empty answer.

Four counties clear both thresholds: Fayette, Mifflin, Philadelphia, and Venango. Mifflin has the highest infant mortality in the state at 10.2 per 1,000 with child poverty at 20.6%.

Handle the substance carefully. Four counties appearing in both lists is a structural observation about where poverty and infant death occur together, and it is not a statement about any family. Keep it at the county level and keep it structural — §5 is explicit about this and the room will follow the framing that gets modelled.

.assign(), .rename(), and .drop() change the columns

.assign() adds a derived column. .rename(columns={old: new}) renames. .drop(columns=[...]) removes. All three return a new DataFrame and leave the original unchanged.

.drop() needs columns=

.drop("year") alone raises KeyError, because .drop() removes a row by default.

Recomputing a column the file already contains looks pointless and it is the opposite. This is a data check: rate_check should match the published rate to rounding, and if it does not, either the population column is the wrong year or the published rate uses a different denominator. They match here. Say so — a check that passes is still a check.

The lambda d: is the part to explain slowly. It means “the table as it exists at this point,” which here is the 2023 rows. The underscore in 100_000 is Python’s digit separator, for reading only.

.drop() defaulting to rows surprises everyone, because dropping a column is the far more common operation. Always pass columns=.

Renaming has a cost worth naming: rate_per_100k says what the number is. Shorten a name and something else has to carry the units.

There is an inplace=True argument on most pandas methods and it is going away — tell them to ignore it when they find it in older answers online.

.groupby() splits, applies, and combines

.groupby(column) splits the rows into groups, applies the aggregation to each, and combines the results into a table indexed by the grouping value. .sort_values() orders rows, and every column travels with its row.

count is the denominator

.agg() takes several functions at once. count reports how many rows carried a value in each group — the number the mean and median were computed over.

This is Module 3’s axis= generalized. There the collapse ran along a fixed dimension of a rectangle. Here the groups are defined by the contents of a column, so there can be six of them or sixty-seven, and pandas works it out.

Read the count column across the years: 55, 59, 59, 59, 60, 53. It is not constant, because suppression depends on that year’s counts. The mean of a different set of counties each year is not a time series, and comparing 2019 to 2023 as if it were is a mistake the table invites.

ascending=False sorts high to low. Missing values sort to the end regardless of direction. The sort moving whole rows is the guarantee Module 3’s parallel arrays could not make.

Split-apply-combine is the name for this pattern. It is the same idea as R’s group_by() plus summarise(), which half of them have already seen.

.merge() attaches a second table’s columns

on= names the key column. The result carries the columns of both tables.

The grouping variables live in a different file

Neither region nor metro appears in any outcome file. Every grouped comparison by place is possible only after a join. Compare the row count before and after every join.

This is the motivating slide for the rest of the session, so do not rush it.

.value_counts() is the categorical counterpart to .describe(): thirty-seven metro counties against thirty nonmetro, using the NCHS urban–rural classification. Six regions, unevenly sized: South Central and North Central have 14 counties each, Northeast has 8.

Eight columns plus seven, minus the shared key, gives fourteen. Sixty-seven rows in, sixty-seven rows out, which means every county matched.

how= decides what happens when a key does not match. The default, inner, deletes unmatched rows silently; left keeps every row of the left table and fills the borrowed columns with NaN. Recommend left. A NaN in a borrowed column then means a real gap or a broken key — a county written “Mc Kean” in one file and “McKean” in the other does not match. The lab returns to this.

The design point: keeping county attributes in one file and joining on demand is good practice. Storing region alongside every outcome means updating it in six places when a definition changes.

For the R half: this is left_join() and friends, same concept, different spelling.

Missing is not zero

.isna().sum() counts the gaps in every column. Filling with zero asserts that no infant died in those twenty-three counties. The state withheld the figure precisely because it is not zero.

.fillna() writes a claim into the data

Every fill value is an assertion about the world. Zero asserts absence. The mean asserts the county is typical. Neither is neutral, and both belong in the writeup.

.isna() gives a grid of True and False; .sum() counts the Trues per column. Twenty-three, seven, and one — three measures, three suppression thresholds, the same 67 counties. Run this after every read and every join.

6.15 against 4.04. A third of the state filled with a false zero drags the state figure down by more than two points, and the result is a claim that twenty-three counties had no infant deaths.

The instinct to fill with zero comes from spreadsheets, where a blank and a zero look alike. Name that and correct it once, firmly. This is the third module in a row with the same rule: Module 2’s .get(key, 0), Module 3’s np.nanmean(), and now this. It is the most important non-syntactic idea in the course.

.dropna() is the honest default and it is what .mean() does silently. Dropping is also a decision — the mean is over the counties that reported, and the count is what makes that visible. The right move here is to report the 44 and say which counties are absent.

Grouped rates are biased downward by suppression

.sum() skips NaN, but the denominator counts every county

A suppressed county contributes its population and none of its deaths. The rate is therefore too low, and no error is raised.

This is the most important slide in the module and it is a silent error, so give it the time.

Look at where the bias lands. Southwest and Southeast are unaffected — 42.9 and 41.3 either way — because no county in them is suppressed. Northwest is off by 2.0 and North Central by 1.8. The bias is largest in rural regions, which is exactly where suppression concentrates, and exactly where an underestimate does the most harm.

Say what that means outside the code. A published regional rate computed the naive way makes rural Pennsylvania look better than it is, in a dataset used to allocate resources. Nobody had to be careless for that to happen. .sum() skipping NaN is a reasonable default that becomes a systematic error the moment it appears in a ratio.

The fix is on the slide: drop the suppressed rows from the numerator and the denominator, so the rate describes the counties that actually reported. Then say how many were dropped.

The general rule, and write it down: whenever a ratio is computed over a group, confirm that the numerator and the denominator cover the same rows. That sentence catches this whole class of bug.

.melt() and .pivot() reshape a table

.melt(): wide → long. One row per county-year, with the year as data. .pivot(): long → wide, the reverse.

Long form is what .groupby() and plotting expect

Published tables arrive wide because wide is readable; analysis wants long because long is groupable.

Three rows become nine, and nine become three again. id_vars names the columns that stay as identifiers; everything else folds into a name column and a value column.

Why it matters: grouping by year is impossible in the wide table, because the year is not a variable there — it is spread across three column names. In the long table it is a column of data and .groupby("year") works. Same numbers, different shape, and only one shape can answer the question.

The year comes out of .melt() as text, because it was a column name. .astype() converts it. That is a good reason to look at .dtypes after any reshape.

.pivot() puts the county in the index and sorts the rows alphabetically. .reset_index() turns the index back into a column.

The overdose file already arrives long, which is why nothing so far needed this. Files built from an agency’s published tables usually arrive wide, and this is the first thing that happens to them. Module 5’s plots expect long form.

pd.to_datetime() makes a date column behave like a date

A text column sorts alphabetically. A datetime column sorts chronologically and exposes .dt.year, .dt.month, and .dt.day_name().

Forty-nine months, March 2020 through March 2024. The peak is January 2022 at 4,294 deaths statewide — the Omicron wave, and it is the largest single month in the file.

The yearly totals tell the shape of the pandemic in five lines: 975 in a partial 2020, then 10,875 and 11,109 in the two full years, then 3,647, then 916. Let that sit for a second without commentary. This is Module 2’s COVID story returning with a real date column underneath it.

Why to_datetime() matters beyond convenience: as text, "2020-03-01" sorts before "2020-12-01" correctly but "3/1/2020" does not, and nothing warns. Converting makes the ordering a property of the type rather than a property of the formatting.

.idxmax() returns the index label of the largest value — here a month. It is the pandas version of Module 3’s .argmax(). .dt is the accessor for datetime methods, the same way .str is the accessor for text methods.

The twenty-three.

Which counties, and what they share.

Exercise: who is missing, and what do they share?

# The blank is left. Every county in the births table should survive the
# join, whether or not it has a published rate — dropping rows here would
# delete the very counties the question is about.
#
# inner returns the same 67 rows in this case, because the keys match
# perfectly. left states the intent, and it is the join that stays correct
# if a key ever stops matching.

import pandas as pd births = pd.read_csv("data/pa_maternal_infant.csv") counties = pd.read_csv("data/pa_counties.csv") joined = births.merge(counties, on="county", how="left") joined["suppressed"] = joined["infant_mortality_per_1000"].isna() print(f"Suppressed counties: {joined['suppressed'].sum()} of {len(joined)}") print() print(joined.groupby("suppressed")["population_2023"].median()) print() print(pd.crosstab(joined["metro"], joined["suppressed"]))

import pandas as pd

births = pd.read_csv("data/pa_maternal_infant.csv")
counties = pd.read_csv("data/pa_counties.csv")

joined = births.merge(counties, on="county", how="left")

joined["suppressed"] = joined["infant_mortality_per_1000"].isna()

print(f"Suppressed counties: {joined['suppressed'].sum()} of {len(joined)}")
print()
print(joined.groupby("suppressed")["population_2023"].median())
print()
print(pd.crosstab(joined["metro"], joined["suppressed"]))

pd.crosstab(a, b) counts one categorical against another.

Fill this in with the room. It is the whole module in fifteen lines: two reads, a join, a derived flag, a grouped summary, and a cross-tab.

Ask for the median population comparison before running it. The suppressed counties come in at 37,607 against 157,825 — a factor of four. That is the mechanism made numeric: fewer people, fewer births, fewer events, no publishable rate.

Then the cross-tab. Five of thirty-seven metro counties are suppressed; eighteen of thirty nonmetro counties are. Thirteen and a half percent against sixty. Note what had to happen to see this: the suppression flag came from the outcome file, the metro classification from the reference file. Neither file can produce this table alone. That is the argument for .merge() in one sentence. normalize="index" gives row proportions — mention it.

The how= blank is the one worth arguing about. inner gives an identical answer here and is still the wrong choice, because the intent is to keep every county and the keys matching today is luck rather than design.

Twenty-three rural counties

Figure 2. Each mark is one county, positioned by population on a logarithmic scale. The twenty-three counties with no published infant mortality rate, gold squares on the upper row, sit almost entirely below 70,000 residents with a median of 37,607. The forty-four counties that do publish a rate, blue circles on the lower row, run from about 45,000 to well over a million with a median of 157,825. The two groups barely overlap.

Twenty-three of Pennsylvania’s sixty-seven counties have no published infant mortality rate. Eighteen of them are nonmetro. Median population: 37,607, against 157,825 for the counties that report.

Land it and stop. These are the dashed tiles from the opening figure, now sorted by the thing they share.

Then make the second point, which is the one that outlasts the module. Suppression is not the absence of information. That two-thirds of rural Pennsylvania counties cannot support a stable infant mortality rate is itself a finding — it says the events are rare and the populations are small, and both of those are facts about rural Pennsylvania worth knowing.

Where this leaves an analyst. The rate cannot be published county by county, so the options are to aggregate counties into regions until the counts are stable, to pool several years, or to report the raw counts alongside an explicit statement that no rate is computable. All three are honest. Filling the blanks with zero is not, and neither is quietly analysing the forty-four and calling the result “Pennsylvania.”

Keep the framing structural. These are counties with small populations, long distances to obstetric care, and hospitals that have closed maternity wards. That is a statement about infrastructure. §5 is explicit that this story is never framed as individual choice, and the room takes its cue from how the instructor says it.

Challenge: what does the rural rate describe?

Group the joined table by metro status and report how many counties stand behind each mean.

# The blank is the aggregation that counts PRESENT values, the one that
# differs from "size" wherever data is missing.

by_metro = joined.groupby("metro")["infant_mortality_per_1000"].agg(["size", "count", "mean"]) print(by_metro.round(2))

by_metro = joined.groupby("metro")["infant_mortality_per_1000"].agg(["size", "count", "mean"])

print(by_metro.round(2))

Your turn!

Nonmetro infant mortality is 7.38 per 1,000 against 5.68 for metro. How many nonmetro counties does 7.38 describe, and which ones are left out? Is 7.38 the rate of rural Pennsylvania?

The answer: 7.38 describes 12 of 30 nonmetro counties. Metro is 32 of 37. The eighteen left out are the smallest rural counties — median population about 37,600 against about 74,600 for the twelve that report.

So 7.38 is the rate of the larger rural counties. Whether the smallest ones are better or worse cannot be read off this table, and that is the honest answer to give. The gap between 5.68 and 7.38 is real for the counties measured; extending it to all of rural Pennsylvania is a claim this data cannot support.

This is the denominator rule one last time: size against count, and the difference is the story. Module 5 opens with the scatter this earns, and the 44 goes in the caption.

Session outcomes

1. Reading a table with pd.read_csv(); Series, DataFrame, and Index

2. Inspecting with .head(), .info(), .describe(), .shape, and .dtypes

3. Selecting with [], .loc by label, and .iloc by position

4. Filtering with boolean masks and & and | with parentheses

5. Changing columns with .assign(), .rename(), and .drop()

6. Summarising with .groupby() and .agg(); ordering with .sort_values()

7. Joining with .merge() on a shared key, and checking the row count

8. Handling missing data with .isna(), .dropna(), and .fillna() — missing is not zero

9. Reshaping with .melt() and .pivot(); dates with pd.to_datetime() and .dt

Preparation for the lab, Thursday 11/5

The lab is six days after this lecture, across Election Day. It restates each construct before exercising it, so returning to it cold is expected and planned for.

Module 5 turns these tables into figures.

The single sentence for the whole module: a DataFrame keeps labelled columns aligned through every operation, so the question can be reshaped without the data coming apart.

The second sentence, which matters more: every missing value is a decision waiting to be made, and the decision belongs in the writeup rather than in a .fillna() nobody reads.

Flag the gap out loud. Six days and a holiday sit between this and the lab, and this is the densest module in the course. Tell them the lab restates everything and that they should come back to it without re-reading the slides first.

That’s all folks!

See you on Thursday — no class Tuesday, it’s Election Day.