Combining multiple datasets - Data Analysis with Python and Pandas p.5

The core takeaway is that combining county-level unemployment data with state-level minimum wage data and then linking both to county-level 2016 presidential voting percentages (focused on Donald Trump) produces weak, inconsistent statistical relationships—suggesting no clear, simple connection between these economic indicators and voting behavior. The exercise is less about proving a political theory and more about learning how to stitch messy datasets together in pandas, then measure correlation and covariance.

First, the workflow loads “unemployment by county” from a CSV and inspects the unemployment rate column. Next comes the key data-integration step: minimum wage data exists by year and state, not by county. To bridge that mismatch, the transcript builds a function that looks up the minimum wage for a given (year, state) pair using pandas indexing (via `.loc` on the minimum-wage table’s year index). That function is then mapped across the unemployment-by-county rows to create a new “minimum wage” column. The mapping approach is intentionally basic and slow—using Python’s `map`—but it reliably handles functions with multiple parameters (year and state). During this step, the transcript also notes a structural limitation: because minimum wage is not truly county-specific, the county-level dataset inherits the same state minimum wage values for all counties in that state.

With the merged unemployment + minimum wage dataset in place, the analysis checks whether unemployment rate and minimum wage move together. Covariance and correlation are computed, and the results don’t show a strong, convincing relationship. The transcript then loads the “2016 presidential election vote by county” dataset, which contains vote counts and percentages by county and candidate. To simplify, it filters down to Donald Trump and keeps only the percent column.

The next challenge is aligning schemas so the datasets can be merged: the presidential dataset uses full state names (or a state field labeled differently), while the unemployment/minimum-wage pipeline uses postal abbreviations. A state-abbreviation mapping file is loaded, converted into a dictionary, and applied so the presidential data’s state field matches the unemployment dataset’s state format. After renaming columns (e.g., standardizing “County” and “state” casing) and setting a dual index on (county, state), the transcript merges the presidential percent-vote data with the unemployment+minimum-wage data.

Finally, correlation and covariance are computed between (1) minimum wage and Trump’s vote percentage and (2) unemployment rate and Trump’s vote percentage. Minimum wage shows a negative association with Trump’s vote share—counties with higher minimum wages tend to have lower Trump vote percentages—while unemployment rate appears to have little relationship. Even where a directional pattern appears, the transcript emphasizes that the results aren’t statistically significant and should not be treated as evidence of a causal link. The practical value is the pandas technique: mapping multi-parameter functions, filtering by candidate, standardizing keys, and merging on county/state to enable correlation-style comparisons.