HW 03: Data wrangling

Real data rarely shows up ready to analyze.

Published

October 9, 2026

Purpose

Real data rarely shows up ready to analyze. It comes in different file types, spread across more than one table, or shaped in a way that doesn’t match the plot you want to make. In this assignment you’ll import, recode, join, and reshape four small datasets.

  • Import data from CSV and Excel files
  • Create new variables with case_when()
  • Join two data frames and reorder factor levels for a plot
  • Reshape data with pivot_wider() and pivot_longer()

Grading Rubric

  • (2 pt) Commit history: on time and at least one commit per part I-IV.
  • (4-5 pts ea.) Two parts will be graded for correctness, two for completion.

While many of these steps could be combined into a single step using chaining methodology, please do the work clearly under the assigned question number. For example, don’t answer Question 8 as part of Q7, or Q5 as part of Q4.

Getting started

Load the tidyverse and readxl packages. All of the data for this assignment is in your data folder.

Part I. Age gaps in movies

The age-gaps.xlsx file lists couples in Hollywood movies and the ages of the two actors. The data is on the sheet called Hollywood Age Gaps, below 2 rows of notes.

1. Import from Excel

Read in age-gaps.xlsx with the appropriate read_ function and save it as a data frame named age_gaps. Starter code has been provided.

_______ <- _______(
  here::here("_______"),
  sheet = "_______",
  skip = 2
)

2. Who is older?

Use filter to keep rows where the gender of character 1 and character 2 are not the same. Then use case_when() to create a new variable to identify which gender is older (regardless if the older person is character 1 or 2). Call this variable older and create values of "man older", "woman older", or "same age". Save the result as age_gaps_mw.

3. Plot who is older

Make a bar plot of older. What do you notice?

Part II. Sales taxes by region

This part uses the following two data sets:

  • sales-taxes-25.csv has 2025 state and local sales tax rates for each state.
  • us-regions.csv lists the Census region for each state.

4. Import two files

Read in both data sets and save them as sales_taxes and us_regions.

5. Join

Use left_join() to add each state’s region to sales_taxes. The state name is called state in one data frame and state_name in the other, so use join_by(state == state_name). Save the result as taxes_regions.

6. Tax rates by region

6a. Reorder

Use fct_reorder() to order region by combined_tax_rate. Save the result back to taxes_regions.

6b. Boxplot

Make a boxplot of combined_tax_rate for each region. What do you notice? Why is there a NA in the plot?

Part III. Minimum wage and employment

On April 1, 1992, New Jersey raised its minimum wage from $4.25 to $5.05 per hour. Pennsylvania did not. Economists David Card and Alan Krueger surveyed fast-food restaurants on both sides of the border before and after the change to see whether the higher wage cost jobs. Here are some variable definitions.

  • id: restaurant ID
  • state: NJ or PA
  • time: before or after the wage increase
  • wage: starting wage in dollars
  • fte: full-time-equivalent employees (full-time workers plus half the part-time workers)

7. Import and pivot wider

Read in card-krueger.csv. Then use pivot_wider() to create card_krueger_wide with one row per restaurant and the columns id, state, wage_before, wage_after, fte_before, and fte_after.

8. Change in employment

Add a new variable called fte_change that equals fte_after - fte_before.

9. Compare states

Use group_by() and summarize() to find the mean fte_change in each state. Did employment fall in New Jersey after the wage increase?

Part IV. Income distribution

The acs5_income.csv file was downloaded directly from the U.S. Census Bureau. It shows the percent of households in each income bracket for every county in California.

U.S. Census Bureau, U.S. Department of Commerce. “Income in the Past 12 Months (in 2023 Inflation-Adjusted Dollars).” American Community Survey, ACS 5-Year Estimates Subject Tables, Table S1901, https://data.census.gov/table/ACSST5Y2023.S1901?t=Income+and+Poverty&g=040XX00US06$0500000. Accessed on 27 Sep 2026.

Each row is an income bracket. Each column is one county, for one household type (households, families, married-couple families, or nonfamily households), for either the estimate or its margin of error. That’s 465 columns!

10. Import

Read in acs5_income.csv. Keep only the first column and the estimate columns for all households, and save the result as acs_income. Variables can be renamed on the fly in the select statement using select(newname = oldname), and the helper function contains("!!Households!!Estimate") will select every column with that literal text in its name.

11. Pivot longer

Filter out the total row, then use pivot_longer() on every column except income, with the column names in county and the values in percent. Save the result as income_long.

12. Clean up

percent was read in as text because the values have % signs, and every county name ends in the same long piece of text. Use parse_number() to drop everything that isn’t part of a number, and str_remove() to delete a piece of text. Lastly filter to only keep 4 counties of your choice to compare to Butte (that is, choose Butte county and 4 others.)

Starter code has been provided for you.

income_long <- income_long |>
  mutate(
    percent = parse_number(_______),
    county = str_remove(_______,
                        " County, California!!Households!!Estimate")) |>
  filter(county %in% c(_______))

13. Income by county

Make a bar plot with one bar per county, filled by income bracket. Refer back to wk6-d03 (Recoding data) and use the income by religion plot at the end of it as a guide. Where does Butte County fall compared with your chosen 4 counties?

When you create the plot, confirm the income brackets are in the order you expect.

Note

This page uses data and ideas from STA 199, Fall 2025 by Mine Çetinkaya-Rundel (AE 06: sales taxes, AE 08: age gaps, HW 4: Card and Krueger minimum wage data). Modified: new questions combining four datasets for data import, recoding, joins, factors, and pivoting.