4 Data cleaning

Author

Mike O’Brien

Published

November 3, 2026

library(arrow)
library(dplyr)
library(ggplot2)

5 Striped bass

sb <- open_dataset("../data/RAW/combined_striped_bass.parquet") |>
  filter(longitude != 0, !is.na(datetime)) |> # CHANGE THIS LATER!
  mutate(latitude = abs(latitude), wk = week(datetime)) |>
  group_by(transmitter, year, wk, group) |>
  summarize(
    med_lon = median(longitude, na.rm = TRUE),
    med_lat = median(latitude, na.rm = TRUE)
  ) |>
  collect()

First visualize the distribution of the median per-individual latitudes through time:

ggplot(sb) +
  geom_point(aes(x = wk, y = med_lat, color = group), alpha = 0.4) +
  facet_wrap(~year) +
  labs(x = "Week of year", y = "Weekly median latitude") +
  theme_minimal() +
  theme(axis.title = element_text(size = 12))

Weekly median latitude of striped bass. Colors represent putative populations; DE: Delaware, HR: Hudson River, PR: Potomac River.

It seems that we have resident striped bass from both the Delaware and Potomac River. Striped bass exhibit partial migration, where fish <80 cm remain resident in their natal estuary (Secor et al. 2020) (though our understanding of this is rapidly changing!). The projects providing the data for Delaware and Potomac fish purposefully tagged smaller, resident fish. As a first pass, we’ll remove any fish that didn’t move higher than 40.46 \(\degree\) latitude (the latitude of Trenton, NJ).

Great, now we definitely have some migratory striped bass.

open_dataset("data/RAW/combined_striped_bass.parquet") |>
  anti_join(drop) |>
  mutate(latitude = abs(latitude), wk = week(datetime)) |>
  write_parquet("data/derived/striped_bass.parquet")

6 Atlantic sturgeon

ats <- "../data/derived/sturgeon.parquet" |>
  open_dataset() |>
  mutate(wk = week(det_Date), yr = year(det_Date)) |>
  group_by(Transmitter, yr, wk) |>
  summarize(
    med_lat = median(r.lat, na.rm = T),
    med_lon = median(r.lon, na.rm = T)
  ) |>
  collect()

ggplot(ats) +
  geom_point(aes(x = wk, y = med_lat), alpha = 0.4) +
  facet_wrap(~yr) +
  labs(x = "Week of year", y = "Weekly median latitude") +
  theme(axis.title = element_text(size = 12))

Weekly median latitude of Atlantic sturgeon.

"../data/derived/sturgeon.parquet" |>
  open_dataset() |>
  mutate(yr = lubridate::year(det_Date)) |>
  # group_by(Transmitter, yr) |>
  # filter("Atlantic Ocean" %in% Waterbody) |>
  collect()

# A tibble: 423,937 × 25
   r.station        Transmitter Date       Fork.Length Total.Length Weight Sex  
   <chr>            <chr>       <date>           <dbl> <chr>         <dbl> <chr>
 1 st 37.305 -77.08 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 2 st 37.305 -77.08 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 3 st 37.305 -77.08 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 4 st 37.305 -77.08 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 5 st 37.313 -77.2… A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 6 st 37.32 -77.277 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 7 st 37.32 -77.277 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 8 st 37.32 -77.277 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
 9 st 37.32 -77.277 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
10 st 37.32 -77.277 A69-9001-2… 2013-09-07         154 <NA>             NA <NA> 
# ℹ 423,927 more rows
# ℹ 18 more variables: Tag.Owner <chr>, Detection.Owner <chr>, det_Date <date>,
#   Genetic.Sex <chr>, Concensus.Sex <chr>, First.Population.Assignment <chr>,
#   Percent_Assignment <dbl>, r.lat <dbl>, r.lon <dbl>, Waterbody <chr>,
#   State <chr>, DPS <chr>, Spawning <chr>, Salinity <chr>, Month <dbl>,
#   Yday <dbl>, Week <dbl>, yr <int>

pick the sturgeon or sturgeon-by-year that existed in the atlantic ocean at some point