Exploring the Saw Kill Field Data and Historical Records

Thursday, October 29, 2026 | DATA 121

How do Tuesday’s observations compare with earlier observations of the Saw Kill?

In Homework 3, you used a Saw Kill Monitoring Project dataset to investigate missing measurements, sampling coverage, distributions, and numerical relationships. In this activity you return to those questions and incorporate the data you helped collect.

You’ll build on the filter(), mutate(), count(), grouped summaries, and ggplot skills you’ve been developing.

This activity will also introduce three new functions:

• slice() to select or remove rows by their position

• case_when() to recode values

• pivot_wider() to organize sampling-record counts by site and year

The goal for today is to finish Steps 1–3 in class. Finish the summary table, histogram, and brief interpretations before Friday’s lab.

You can refer to the linked course resources throughout the activity.

NOTE: You will submit your work on this activity with Homework 4

Getting started

1. Open your data121 RStudio project. Create thursday_oct29_yourname.qmd in activities, add your name, and copy the five numbered headings below.
2. Load the tidyverse
3. Download the class field data and save it as data/field_data_skmp_site_final.csv in your project.
4. Download the historical data using the directions below.

Download historical observations

1. Open the Saw Kill Watershed Community Database and navigate to the DATA DOWNLOAD page.
2. Choose the Saw Kill Monitoring Program and leave Select Site(s) at All Sites.
3. Set the date range to 2016-01-01 through 2026-06-14. These are limits set on the data portal’s filter, note that the available observations may cover a shorter period.
4. Open View Column Descriptions and find water temperature and conductivity. Choose Conductivity, not the separately listed uncompensated conductivity column.
5. Leave Include all columns checked.
6. Select Preview data, check your choices, then Download CSV.
   • Save the file as data/saw_kill_historical.csv.

Import and use shorter column names

Import both files, then use the functions you practiced in Homework 3 to prepare their columns.

• This uses the rename() function where new_var_name = old_var_name
• We use backticks (not single quotes) to enclose the original column names containing spaces or punctuation.

field_data <- read_csv("data/field_data_skmp_site_final.csv") |>
  rename(
    submission_id = ObjectID,
    sampler_names = `Sampler Name(s)`,
    site = `Sampling Site Location`,
    sample_date = `Sample Date`,
    sample_time = `Sample time`,
    instrument_id = `YSI probe ID`,
    water_temp_c = `Water Temperature (°C)`,
    conductivity_us_cm = `Comp./Specific Conductivity (µS/cm)`
  )

Next, read in the historical data from the SKMP data portal:

historical_data <- read_csv("data/saw_kill_historical.csv") |>
  rename(
    site = Site,
    sample_date = `Sample Date`,
    year = Year,
    season = Season,
    water_temp_c = `Water Temperature`,
    conductivity_us_cm = Conductivity
  )

After running the code above, redefine the historical_data data table with these two changes:

1. Use mutate() and as.character() to convert site to character data.
2. Keep only site, sample_date, year, month, season, water_temp_c, and conductivity_us_cm, in that order.
3. Save the completed result as historical_data.

1. Identify the observations to keep for the site comparison

• Use glimpse(), head(), and count(site) to inspect both tables.

• For the field data, count submissions by site and probe. Which combinations have more than one entry? Why?

field_data |> count(site, instrument_id)

• Write down the dimensions of each table and identify the temperature and conductivity data types.

Write a brief response: In each data table, what does one row represent?

Remove test submissions and the entry labeled Other

Open field_data from the Environment pane and inspect the first 17 rows. Compare the written observations with the temperature and conductivity entries. Which rows look like tests of the form?

You were asked to enter landscape, water, and weather descriptions only with your first reading at each site. Later readings can therefore have NA, N/A, or blank written responses.

Row 1 is the setup test, and rows 3–17 contain the class practice entries. Row 2 is the only submission labeled Other. Remove these first 17 rows to keep the submissions from Sites 3 and 2.5:

field_data <- field_data |>
  slice(-(1:17))

nrow(field_data)

1:17 lists rows 1 through 17; the minus sign removes them. These are positions in the imported table, so run this once after importing, before rearranging or removing other rows. You should have 37 rows left. Keep repeated temperature and conductivity values; separate submissions can record the same measurements.

Reference: selecting and removing rows with slice().

2. Keep missing-value codes and entry errors out of your calculations

In Homework 3, missing measurements were already stored as NA. The field data collection form uses 999 for an unavailable measurement. R will then include that number in calculations until you recode it. In the following step you will clean the two variables you’ll analyze: temperature and compensated conductivity, measured in °C and µS/cm.

Before recoding, inspect the columns in field_data. Identify any columns where every entry is 999, then redefine field_data to remove those columns because they contain no recorded measurements.

• Reference: course slides: if/then with case_when(). Another explanation can be found in IDS Section 4.10.1.

First, count missing and coded values:

• is.na() returns TRUE for each missing value; sum() counts those TRUE entries.

field_data |>
  summarize(
    missing_temperature = sum(is.na(water_temp_c)),
    coded_temperature = sum(water_temp_c == 999, na.rm = TRUE),
    missing_conductivity = sum(is.na(conductivity_us_cm)),
    coded_conductivity = sum(conductivity_us_cm == 999, na.rm = TRUE)
  )

The remaining temperature entries contain no 999 values, but two conductivity entries do. Recode those conductivity values using case_when():

field_clean <- field_data |>  # Start with field_data; save the result as field_clean
  mutate(  # Use mutate to redefine a column
    conductivity_us_cm = case_when( # Check conditions in order and use the first match
      conductivity_us_cm == 999 ~ NA, # Replace 999 with an NA representing a missing value
      TRUE ~ conductivity_us_cm  # Otherwise, keep the existing conductivity
    )
  )

• case_when() checks conditions in order and uses the first match.
• ~ separates the condition from its replacement value.
• The last line means “otherwise, keep the existing conductivity.”
• mutate() overwrites the previous version of the column
• <- saves the result as field_clean.

Run the counting code again using field_clean. Both coded counts should be zero, the missing conductivity count should increase by two, and the number of rows should stay the same.

Write a brief response: Why would na.rm = TRUE fail to solve the 999 missing data problem? Why would replacing an unavailable temperature with zero change its meaning?

Use case_when() to handle specific entry errors

Removing 999 does not catch every error. Inspect the unusually high temperatures and their associated entries:

field_clean |>
  filter(water_temp_c > 40) |>
  select(submission_id, site, instrument_id,
         water_temp_c, conductivity_us_cm)

• Which temperatures are implausible for the Saw Kill in October? Record their submission IDs and explain how leaving them in would affect the mean. Hint: Compare both measurement columns in submission 43.

The temperatures in submissions 35, 43, and 44 are entry errors. Without verified replacement temperatures, mark them as unavailable. The value 533 in submission 43 is a confirmed conductivity reading, so restore it to conductivity_us_cm.

Next:

• Redefine field_clean using mutate() and case_when().

• Start with the rule submission_id == 35 ~ NA, add a rule for each of the other two submissions, and finish with TRUE ~ water_temp_c.

• In the same mutate(), use a second case_when() to correct conductivity for submission 43 while keeping the other conductivity values.

Check your targeted recode

field_clean <- field_clean |>
  mutate(
    water_temp_c = case_when(
      submission_id == 35 ~ NA,
      submission_id == 43 ~ NA,
      submission_id == 44 ~ NA,
      TRUE ~ water_temp_c
    ),
    conductivity_us_cm = case_when(
      submission_id == 43 ~ 533,
      TRUE ~ conductivity_us_cm
    )
  )

The conditions identify submissions by ID, so they still work after removing the test rows.

3. Compare how often each site was sampled across years

How many historical sampling records are available for each site in each year? Start with the familiar count() function:

site_year_counts <- historical_data |>
  count(site, year)

site_year_counts

Each row gives the number of records for one site–year combination. Use pivot_wider() to put the years in separate columns so you can compare them across one row for each site:

coverage_wide <- site_year_counts |>
  pivot_wider(
    names_from = year,
    values_from = n,
    values_fill = 0
  )

coverage_wide

• names_from = year uses the years as the new column names.
• values_from = n fills those columns with the counts you calculated.
• values_fill = 0 puts zero where a site–year combination has no sampling records.
• site identifies the rows because it is the remaining column.

Here, zero means no sampling records for that site and year.

Next:

• Open coverage_wide from the Environment pane to see all year columns.

• Which sites have records in the most years?

Reference: course slides: pivot_wider().

4. Compare average temperature and conductivity across sites

Use field_clean from Step 2 to create a table named field_summary with one row per site. For each site, report:

• The number of available temperatures and their mean in °C.
• The number of available conductivity measurements and their mean in µS/cm.

Use group_by() and summarize() to:

• Count available (non-missing) measurements with sum(!is.na(...)) and

• Calculate each mean with na.rm = TRUE.

• Replace ... with the relevant column name.

Display your table.

For one site, write one sentence reporting both means and how many measurements contributed to each.

5. Compare Tuesday’s Site 3 temperature with earlier Octobers

Use Site 3 for this comparison: it is labeled "Site 3" in field_summary and "3" in historical_data. The historical download has no records for Site 2.5.

1. Create a new October-only data frame:
   • Use filter() to keep historical records for that site and October (month == 10).
   • Save the result as historical_october.
2. Use summarize() to calculate and display the number of available (non-missing) temperatures and their median.
3. Check the years represented with count(year).
4. Finally, make a histogram of water_temp_c using:
   • The historical_october table
   • geom_histogram()
   • A binwidth of 2
   • Labels:
     • Include the site and years in the title or subtitle,
     • label the axes with °C and the number of monitoring records.

Write a sentence or two in your document describing the distribution and comparing Tuesday’s mean from field_summary with the historical median.

Refer to Homework 3, the Introduction to ggplot slides, or the example below for code examples.

Example: October temperatures at Site 3

Use this example to check your Site 3 analysis.

example_october <- historical_data |>
  filter(site == "3", month == 10)

example_october |>
  summarize(n_available = sum(!is.na(water_temp_c)),
            median_temperature_c = median(water_temp_c, na.rm = TRUE))

# A tibble: 1 × 2
  n_available median_temperature_c
        <int>                <dbl>
1          10                 13.2

Site 3 has 10 recorded October temperatures, with a median of 13.2°C.

ggplot(example_october, aes(x = water_temp_c)) +
  geom_histogram(binwidth = 2, 
                 fill = "orange", 
                 color = "white") +
  labs(title = "October water temperatures at Saw Kill Site 3",
       subtitle = "10 recorded temperatures, 2016–2025",
       x = "Water temperature (°C)", y = "Number of monitoring records",
       caption = "Source: Saw Kill Watershed Community Database")

Warning: Removed 5 rows containing non-finite outside the scale range
(`stat_bin()`).

Each bar counts records in a 2°C interval.

Bring to Friday lab

Render your document. Bring your completed QMD, rendered HTML, and the two CSV files to continue working with them in Friday lab activity.