---
title: "M04 Lab — Data Wrangling"
author: "Your Name"
date: today
format:
  html:
    toc: true
    toc-depth: 3
    theme: cosmo
    df-print: paged
    highlight-style: github
    embed-resources: true
execute:
  warning: false
  message: false
---

# Setup

Load the packages we'll use in this notebook.

```{r}
#| label: setup
#| warning: false
#| message: false

library(tidyverse)
library(here)
```

# Introduction

<!-- TODO: In one short paragraph, describe what you're going to do in this lab and the research question your main analysis will answer. -->

# Data

Load the opioid_counties dataset from your project's `data/` folder.

```{r}
#| label: import-data

opioid_counties <- read_rds(here("data", "opioid_counties.Rds"))
```

A quick structural look at what you imported. You should see about 26,980 rows and 6 columns.

```{r}
#| label: glimpse-data

opioid_counties |> glimpse()
```

# Warm-up · Practice the core verbs on one state

You'll write five small pipelines below — one per family of dplyr verbs, plus the two ways group_by() can be finished. Every task stays inside Colorado and builds on the one before it. Each chunk has a TODO comment with what to write.

## 2a · filter — keep just one state

```{r}
#| label: warmup-filter

# TODO (Task 2a): Keep only the Colorado rows, and save the result as co_only.
# Then print it. Expect about 526 rows.
```

## 2b · mutate — add pills_shipped_per_resident

```{r}
#| label: warmup-mutate

# TODO (Task 2b): Add a pills_shipped_per_resident column to co_only — pills
# divided by population — and save it back to co_only. Then print it and
# confirm the new column is there.
```

## 2c · group_by + summarize — Colorado, one row per year

```{r}
#| label: warmup-summarize

# TODO (Task 2c): Collapse co_only to one row per year. Count the counties that
# reported, total the pills, total the population, then derive the rate per
# resident from those two totals (not by averaging 2b's column). Save as
# co_by_year and print it. Expect 9 rows — and check whether the county count
# is the same in every year.
```

## 2d · group_by + mutate — each county vs its state-year

```{r}
#| label: warmup-grouped-mutate

# TODO (Task 2d): Run the SAME grouping as 2c, but finish with the verb that
# keeps every row instead of collapsing them. Attach each year's statewide rate
# to every Colorado county in that year, ungroup, then add a column giving each
# county's rate as a multiple of its state's. Save back to co_only.
# Expect 526 rows, not 9 — that contrast is the point of the task.
```

## 2e · arrange — sort by pills_shipped_per_resident

```{r}
#| label: warmup-arrange

# TODO (Task 2e): Sort co_only from the highest shipment rate to the lowest,
# keep the top ten rows, and show only county, year, and the rate.
```

# Main analysis · How do states compare?

The research question:

> *How did opioid pill shipments per resident compare across U.S. states between 2006 and 2014, and which states received the most?*

## Stage 1 · Aggregate to state-year level

```{r}
#| label: state-year-aggregation

# TODO (Stage 1): Collapse the county data to one row per state per year —
# the county count, total pills, total population, and the rate per resident.
# Save as state_year and print it. Expect 459 rows (51 jurisdictions x 9 years).
```

## Stage 2 · Identify the top 5 states

```{r}
#| label: top5-states

# TODO (Stage 2): Collapse state_year again, this time to one row per state —
# how many years it contributed, and the average of its yearly rates. Keep the
# five highest and save as top5. Expect WV, KY, TN, OK, NV.
```

## Stage 3 · Plot the top 5 states' trajectories

```{r}
#| label: fig-top5-trends
#| fig-cap: "Annual oxycodone and hydrocodone pills shipped per resident for the five states with the highest mean annual shipment rates, 2006–2014. Counts represent deliveries to covered buyers located in each state, not pills consumed by residents. Source: DEA ARCOS data prepared by The Washington Post."
#| fig-alt: "Line chart with year on the horizontal axis from 2006 to 2014 and oxycodone and hydrocodone pills shipped per resident on the vertical axis. Five colored lines, one per state — West Virginia, Kentucky, Tennessee, Oklahoma and Nevada. All five rise through the late 2000s, peak in 2011 or 2012, and decline afterwards; Kentucky and West Virginia sit highest throughout."

# TODO (Stage 3): Chart the five states in top5 — year across the bottom,
# shipment rate up the side, one colored line per state, with points on the
# lines. Give the x-axis a break for every year, and write the title as the
# finding rather than a description (the M03 rule).
```

## Stage 4 · Join the Appalachia county list

```{r}
#| label: import-appalachia

# TODO (Stage 4): Read appalachia.Rds from your data/ folder, the same way you
# read opioid_counties above. Then add a marker column named in_arc_list that
# flags every row as being in the list. The lab explains why that is sturdier
# than reading membership off the economic-status column.
```

```{r}
#| label: check-the-join

# TODO (Stage 4): Before trusting the join, check it in BOTH directions —
# ARC counties with no shipment data, and shipment counties not in the ARC
# list. The two answers should be very different sizes. Know which answer you
# expect before you look.
```

```{r}
#| label: appalachia-join

# TODO (Stage 4, step one): Join the ARC list onto opioid_counties, keeping
# every shipment row, then label each county Appalachian or not depending on
# whether it matched a row in the ARC list. Save as app_counties.
# This KEEPS the county-year grain — expect the same 26,980 rows you started
# with, now carrying a group label. Count the labels to confirm.
```

```{r}
#| label: appalachia-trend

# TODO (Stage 4, step two): Collapse app_counties to one row per group per
# year — the county count, total pills, total population, and the rate per
# resident. Save as app_trend. Expect 18 rows (2 groups x 9 years).
```

```{r}
#| label: fig-appalachia
#| fig-cap: "Oxycodone and hydrocodone pills shipped per resident, 2006–2014, for counties inside and outside the federally designated Appalachian Region. Counts represent deliveries to covered buyers, not pills consumed by residents. Sources: DEA ARCOS data prepared by The Washington Post; region definition from the Appalachian Regional Commission."
#| fig-alt: "Line chart with year on the horizontal axis from 2006 to 2014 and pills shipped per resident on the vertical axis. Two lines, one for Appalachian counties and one for all other counties. Both rise to a peak in the early 2010s and decline, but the Appalachian line sits well above the other throughout and the vertical distance between them grows across the period."

# TODO (Stage 4): Chart app_trend — year across the bottom, shipment rate up
# the side, one colored line per group, with points. Title-as-finding again:
# say what the comparison shows, not what it is.
```

## Stage 5 · Interpret

```{r}
#| label: number-for-prose

# TODO (Step 4): Pull ONE number out of app_trend and give it a name, so your
# paragraph can call it instead of you typing the digits. A good choice is the
# Appalachian-vs-other gap in a single year. Print it once so you can see what
# you got. The lab page explains why the number has to come from the code.
```

<!-- TODO (Step 4, at home): Write ONE paragraph, 3-4 sentences, doing four
things — the same four the lab page lists:

  1. Identify one pattern visible in the figure.
  2. Compare at least two of the state trajectories.
  3. Cite one specific number from your Appalachian comparison — and cite it
     with INLINE R, calling the object you just named rather than typing the
     digits. The lab page shows the syntax.
  4. State one limitation of what shipment data can establish.

Your paragraph must also reference BOTH figures by name: type @fig-top5-trends
and @fig-appalachia as plain text, no backticks. One of each, minimum. After
you render, click both links in the HTML to confirm they jump to the charts —
and check that your inline number printed a value, not an error.

Delete this comment block once your paragraph is written. -->

