Module 3: wrangling whale survey data

Read, clean, split, and summarise survey data with tidyverse packages

Learning objectives — After this module you will be able to:

Read a wide, multi-table CSV into R with readr
Clean messy column names with janitor
Understand the structure of a Mysticetus export
Split one wide data frame into three tidy sub-tables (tracks, sightings, environmental)
Parse timestamps and EDT time strings with lubridate
Filter, select, mutate, and summarise with dplyr
Save cleaned sub-tables to CSV for use in later modules

3.1 The Tidyverse

The tidyverse is a collection of R packages that share a consistent design philosophy. The core packages used in this module are:

Package	Role
`readr`	Fast CSV import
`dplyr`	Filter, select, mutate, summarise, join
`tidyr`	Reshape between wide and long formats
`lubridate`	Parse and manipulate dates and datetimes
`janitor`	Clean messy column names
`stringr`	String manipulation

library(tidyverse)
library(janitor)
library(lubridate)

3.2 Project setup

Let’s define and create some of these folders. It’s nice to do this in code so you can refer to these folders programmatically later on.

# define directories
processed_dir <- 'data/processed/'  # processed data
figure_dir <- 'figures/'            # figures

# create them using R if they do not exist
if(!dir.exists(processed_dir)){
  dir.create(processed_dir, recursive = T)
}
if(!dir.exists(figure_dir)){
  dir.create(figure_dir, recursive = T)
}

3.3 Example dataset

⬇ Download 2026-04-09-NEA-N_Whalemap.csv

Place the CSV in the raw data folder (data/raw/) before running this code. The data file (2026-04-09-NEA-N_Whalemap.csv) is an export from the Mysticetus software used to record data during a right whale by NEAq aboard the R/V Nereid in Cape Cod Bay on 9 April 2026.

We had Mysticetus set up to export data in one wide CSV with 57 columns that actually represent three distinct sub-tables embedded side by side:

Sub-table	Key columns
Track	`TrkTime`, `TrkLatitude`, `TrkLongitude`, `PlatformSpeed`, `TrkDist`
Sightings	`SIGHTNO`, `SPECCODE`, `LATITUDE`, `LONGITUDE`, `NUMBER`
Environmental	`BEAUFORT`, `WX`, `CLOUD`, `VISIBLTY`

Rows that belong only to the track have NA in the sightings columns, and vice versa — so the first job is to read the file and then split it into three clean, tidy tables.

Tip

You can see how WhaleMap processes these data on the WhaleMap github here

Note

Note that Macs and Linux OS use forward slashes / while Windows OS uses back slashes \ when creating file paths. You may need to convert depending on what platform you are using.

3.4 Reading the file in R

# read in csv
raw <- read.csv(                              # read.csv() is the base function for reading csv files
  "data/raw/2026-04-09-NEA-N_Whalemap.csv",   # path to our data file
  stringsAsFactors = F) |>                    # keep strings as characters
  as_tibble()                                 # immediately convert to tibble (fancy data frame)

# the above uses the pip operator for readability, but the same results could be achieved as follows:
# raw <- as_tibble(read.csv("data/raw/2026-04-09-NEA-N_Whalemap.csv", stringsAsFactors = F)

# view the data in R
raw             # print first few columns and first 10 rows to console
summary(raw)    # quick summary of data in each column
glimpse(raw)    # compact overview of every column

Tip

read_csv() (from readr) always returns a tibble — a modern data frame that prints cleanly and never silently converts strings to factors. It is often prefer it over base R read.csv(), but can have some unexpected behavior.

Tip

Click on the small table icon next to the raw object in the environment pane to show the detailed table.

3.5 Cleaning Column Names with `janitor`

The raw column names contain spaces, parentheses, dashes, and numbered suffixes from Whalemap (e.g. "date - 1SIGHTINGS", "TrkAltitude (m)"). clean_names() converts everything to lowercase_with_underscores so you can type them without backticks.

raw <- raw |>
  clean_names()

names(raw)
# "trk_time_edt"   "trk_latitude"   "trk_longitude"  "trk_altitude_m"
# "heading_plat_magnetic_m"  ...  "speccode"  "latitude"  ...

Note

clean_names() is safe to call on any data frame — it never loses data, it only renames columns. It’s not necessary but can be very handy.

3.6 Basic data wrangling with `dplyr`

Keep, remove or rename columns with `select()`

# Select a subset of columns
raw |>
  select(trk_time_edt, speccode, number, latitude, longitude)

# Drop one column
raw |>
  select(-sight_notes)

# Rename while selecting
raw |>
  select(datetime_edt = trk_time_edt, species = speccode, n = number)

Filter rows matching a condition with `filter()`

# Only definite sightings
definite_sightings <- raw |>
  filter(idrel == 3)

# Definite right whale sightings only
right_whales <- raw |>
  filter(speccode == "RIWH" & idrel == 3)

# Mother-calf pairs
mc_pairs <- raw |>
  filter(!is.na(numcalf))

# Sightings of groups
raw |>
  filter(speccode != "" & number > 1)

Create or modify columns with `mutate()`

raw <- raw |>
  mutate(
    lat_rnd = round(latitude,  3), # round lat to 3 decimal places
    lon_rnd = round(longitude, 3), # round lon to 3 decimal places
    datetime_edt = ymd_hms(trk_time_edt, tz = "America/New_York") # convert timestamp to R format
  )

NA handling with `drop_na()`


raw |>
  drop_na() # removes all rows in any column with NAs

raw |>
  drop_na(number) # removes all rows with NAs in `number` column

Sort rows with `arrange()`

raw |>
  arrange(datetime_edt)          # chronological

raw |>
  arrange(desc(number))          # largest groups first

3.6 Process survey data

3.6.1 Track data

The track data table is every row, but we only keep the GPS/navigation columns. We also parse the timestamp and calculate the cumulative distance traveled.

track <- raw |>
  select(
    datetime_edt    = trk_time_edt,
    latitude        = trk_latitude,
    longitude       = trk_longitude,
    heading_true    = heading_plat_true_m,
    speed_kts       = platform_speed_kts,
    dist_nm         = trk_dist_nm
  ) |>
  drop_na() |>
  mutate(
    datetime_edt = ymd_hms(datetime_edt, tz = "America/New_York"), # convert timestamp
    cum_dist_nm  = cumsum(dist_nm) # cumulative distance transited
  )

track

3.6.2 Sightings table

The sightings table is rows where a SPECCODE was recorded.

sightings <- raw |>
  filter(speccode != "") |> # keep rows that have speccodes
  select( # select and rename columns to include
    datetime_edt  = trk_time_edt,
    sight_no = sightno,
    latitude,
    longitude,
    speccode,
    id_reliability = idrel,
    number,
    numcalf,
    eg_letter     = eg_letter_1sightings,
    sight_notes
  ) |>
  mutate(
    datetime_edt = ymd_hms(datetime_edt, tz = "America/New_York") # convert timestamp
  )

sightings

3.6.3 Environmental conditions table

The environmental table includes our data on weather and sea state throughout the survey.

environmental <- raw |>
  filter(!is.na(beaufort)) |>
  select(
    datetime_edt  = trk_time_edt,
    latitude      = lat_enviro,
    longitude     = long_enviro,
    weather       = wx,
    cloud_cover   = cloud,
    visibility    = visiblty,
    beaufort,
    enviro_notes  = enviro_notes
  ) |>
  mutate(
    datetime_edt = ymd_hms(datetime_edt, tz = "America/New_York") # convert timestamp
  )
  
environmental

3.6.3 Groupwise calculations with `summarise()` + `group_by()`

This is a very powerful technique to quickly summarizing datasets. Here are a few examples that are especially useful for our purposes:

Summarize number of sightings

sightings |>
  group_by(speccode) |>
  summarise(
    n_sightings    = n(),
    n_total        = sum(number, na.rm = TRUE),
    n_calves       = sum(numcalf, na.rm = TRUE),
    .groups = "drop"
  ) |>
  arrange(desc(n_sightings))

Summarize survey effort

track |>
  summarise(
    lat_min = min(latitude),
    lat_max = max(latitude),
    lon_min = min(longitude),
    lon_max = max(longitude),
    duration_hrs = difftime(max(datetime_edt), min(datetime_edt), units = "hours") |>
      as.numeric() |> round(2),
    mean_speed_kts  = mean(speed_kts, na.rm = TRUE) |> round(1),
    total_dist_nm = max(cum_dist_nm) |> round(2)
  )

Summarize environmental data

# Count hours at each Beaufort level
environmental |>
  mutate(next_time = lead(datetime_edt), # Use lead() to find when the next record starts
         duration_hour = difftime(next_time, datetime_edt, units = "hours") |>
                         as.numeric()
         )|>
  group_by(beaufort) |>
  summarise(total_hour = sum(duration_hour, na.rm = TRUE), .groups = "drop") |>
  arrange(beaufort)

3.8 Saving data

# Save as CSV (readable by any software)
write_csv(track,         paste0(processed_dir, "track.csv"))
write_csv(sightings,     paste0(processed_dir, "sightings.csv"))
write_csv(environmental, paste0(processed_dir, "environmental.csv"))

# Save as .rds (preserves POSIXct types, factors, and all column types exactly)
saveRDS(track,          paste0(processed_dir, "track.rds"))
saveRDS(sightings,      paste0(processed_dir, "sightings.rds"))
saveRDS(environmental,  paste0(processed_dir, "environmental.rds"))

# Reload (use .rds when reading into R)
track         <- readRDS(paste0(processed_dir, "track.rds"))
sightings     <- readRDS(paste0(processed_dir, "sightings.rds"))
environmental <- readRDS(paste0(processed_dir, "environmental.rds"))

Tip

Save both formats: .csv for interoperability with other tools, .rds for loading back into R (no type-guessing on re-import).

Note

Next: Module 4 — Plotting with ggplot2 →

--- title: "Module 3: wrangling whale survey data" description: "Read, clean, split, and summarise survey data with tidyverse packages" --- ::: {.objectives} **Learning objectives** — After this module you will be able to: - Read a wide, multi-table CSV into R with `readr` - Clean messy column names with `janitor` - Understand the structure of a Mysticetus export - Split one wide data frame into three tidy sub-tables (tracks, sightings, environmental) - Parse timestamps and EDT time strings with `lubridate` - Filter, select, mutate, and summarise with `dplyr` - Save cleaned sub-tables to CSV for use in later modules ::: --- ## 3.1 The Tidyverse The **tidyverse** is a collection of R packages that share a consistent design philosophy. The core packages used in this module are: | Package | Role | |---|---| | `readr` | Fast CSV import | | `dplyr` | Filter, select, mutate, summarise, join | | `tidyr` | Reshape between wide and long formats | | `lubridate` | Parse and manipulate dates and datetimes | | `janitor` | Clean messy column names | | `stringr` | String manipulation | ```r library(tidyverse) library(janitor) library(lubridate) ``` --- ## 3.2 Project setup Let's define and create some of these folders. It's nice to do this in code so you can refer to these folders programmatically later on. ``` r # define directories processed_dir <- 'data/processed/' # processed data figure_dir <- 'figures/' # figures # create them using R if they do not exist if(!dir.exists(processed_dir)){ dir.create(processed_dir, recursive = T) } if(!dir.exists(figure_dir)){ dir.create(figure_dir, recursive = T) } ``` ## 3.3 Example dataset **[⬇ Download `2026-04-09-NEA-N_Whalemap.csv`](data/raw/2026-04-09-NEA-N_Whalemap.csv)** Place the CSV in the raw data folder (`data/raw/`) before running this code. The data file (`2026-04-09-NEA-N_Whalemap.csv`) is an export from the **Mysticetus** software used to record data during a right whale by NEAq aboard the *R/V Nereid* in Cape Cod Bay on 9 April 2026. We had Mysticetus set up to export data in **one wide CSV** with 57 columns that actually represent three distinct sub-tables embedded side by side: | Sub-table | Key columns | |---|---| | **Track** | `TrkTime`, `TrkLatitude`, `TrkLongitude`, `PlatformSpeed`, `TrkDist` | | **Sightings** | `SIGHTNO`, `SPECCODE`, `LATITUDE`, `LONGITUDE`, `NUMBER` | | **Environmental** | `BEAUFORT`, `WX`, `CLOUD`, `VISIBLTY` | Rows that belong only to the track have `NA` in the sightings columns, and vice versa — so the first job is to read the file and then split it into three clean, tidy tables. ::: {.callout-tip} You can see how WhaleMap processes these data on the WhaleMap github [here](https://github.com/hansenjohnson/WhaleMap/blob/master/R/proc_neaq_nereid_2026.R) ::: ::: {.callout-note} Note that Macs and Linux OS use forward slashes `/` while Windows OS uses back slashes `\` when creating file paths. You may need to convert depending on what platform you are using. ::: --- ## 3.4 Reading the file in R ```r # read in csv raw <- read.csv( # read.csv() is the base function for reading csv files "data/raw/2026-04-09-NEA-N_Whalemap.csv", # path to our data file stringsAsFactors = F) |> # keep strings as characters as_tibble() # immediately convert to tibble (fancy data frame) # the above uses the pip operator for readability, but the same results could be achieved as follows: # raw <- as_tibble(read.csv("data/raw/2026-04-09-NEA-N_Whalemap.csv", stringsAsFactors = F) # view the data in R raw # print first few columns and first 10 rows to console summary(raw) # quick summary of data in each column glimpse(raw) # compact overview of every column ``` ::: {.callout-tip} `read_csv()` (from `readr`) always returns a **tibble** — a modern data frame that prints cleanly and never silently converts strings to factors. It is often prefer it over base R `read.csv()`, but can have some unexpected behavior. ::: ::: {.callout-tip} Click on the small table icon next to the `raw` object in the environment pane to show the detailed table. ::: --- ## 3.5 Cleaning Column Names with `janitor` The raw column names contain spaces, parentheses, dashes, and numbered suffixes from Whalemap (e.g. `"date - 1SIGHTINGS"`, `"TrkAltitude (m)"`). `clean_names()` converts everything to `lowercase_with_underscores` so you can type them without backticks. ```r raw <- raw |> clean_names() names(raw) # "trk_time_edt" "trk_latitude" "trk_longitude" "trk_altitude_m" # "heading_plat_magnetic_m" ... "speccode" "latitude" ... ``` ::: {.callout-note} `clean_names()` is safe to call on any data frame — it never loses data, it only renames columns. It's not necessary but can be very handy. ::: --- ## 3.6 Basic data wrangling with `dplyr` ### Keep, remove or rename columns with `select()` ```r # Select a subset of columns raw |> select(trk_time_edt, speccode, number, latitude, longitude) # Drop one column raw |> select(-sight_notes) # Rename while selecting raw |> select(datetime_edt = trk_time_edt, species = speccode, n = number) ``` ### Filter rows matching a condition with `filter()` ```r # Only definite sightings definite_sightings <- raw |> filter(idrel == 3) # Definite right whale sightings only right_whales <- raw |> filter(speccode == "RIWH" & idrel == 3) # Mother-calf pairs mc_pairs <- raw |> filter(!is.na(numcalf)) # Sightings of groups raw |> filter(speccode != "" & number > 1) ``` ### Create or modify columns with `mutate()` ```r raw <- raw |> mutate( lat_rnd = round(latitude, 3), # round lat to 3 decimal places lon_rnd = round(longitude, 3), # round lon to 3 decimal places datetime_edt = ymd_hms(trk_time_edt, tz = "America/New_York") # convert timestamp to R format ) ``` ### NA handling with `drop_na()` ```r raw |> drop_na() # removes all rows in any column with NAs raw |> drop_na(number) # removes all rows with NAs in `number` column ``` ### Sort rows with `arrange()` ```r raw |> arrange(datetime_edt) # chronological raw |> arrange(desc(number)) # largest groups first ``` ## 3.6 Process survey data ### 3.6.1 Track data The track data table is every row, but we only keep the GPS/navigation columns. We also parse the timestamp and calculate the cumulative distance traveled. ```r track <- raw |> select( datetime_edt = trk_time_edt, latitude = trk_latitude, longitude = trk_longitude, heading_true = heading_plat_true_m, speed_kts = platform_speed_kts, dist_nm = trk_dist_nm ) |> drop_na() |> mutate( datetime_edt = ymd_hms(datetime_edt, tz = "America/New_York"), # convert timestamp cum_dist_nm = cumsum(dist_nm) # cumulative distance transited ) track ``` ### 3.6.2 Sightings table The sightings table is rows where a SPECCODE was recorded. ```r sightings <- raw |> filter(speccode != "") |> # keep rows that have speccodes select( # select and rename columns to include datetime_edt = trk_time_edt, sight_no = sightno, latitude, longitude, speccode, id_reliability = idrel, number, numcalf, eg_letter = eg_letter_1sightings, sight_notes ) |> mutate( datetime_edt = ymd_hms(datetime_edt, tz = "America/New_York") # convert timestamp ) sightings ``` ### 3.6.3 Environmental conditions table The environmental table includes our data on weather and sea state throughout the survey. ```r environmental <- raw |> filter(!is.na(beaufort)) |> select( datetime_edt = trk_time_edt, latitude = lat_enviro, longitude = long_enviro, weather = wx, cloud_cover = cloud, visibility = visiblty, beaufort, enviro_notes = enviro_notes ) |> mutate( datetime_edt = ymd_hms(datetime_edt, tz = "America/New_York") # convert timestamp ) environmental ``` --- ### 3.6.3 Groupwise calculations with `summarise()` + `group_by()` This is a very powerful technique to quickly summarizing datasets. Here are a few examples that are especially useful for our purposes: #### Summarize number of sightings ```r sightings |> group_by(speccode) |> summarise( n_sightings = n(), n_total = sum(number, na.rm = TRUE), n_calves = sum(numcalf, na.rm = TRUE), .groups = "drop" ) |> arrange(desc(n_sightings)) ``` #### Summarize survey effort ```r track |> summarise( lat_min = min(latitude), lat_max = max(latitude), lon_min = min(longitude), lon_max = max(longitude), duration_hrs = difftime(max(datetime_edt), min(datetime_edt), units = "hours") |> as.numeric() |> round(2), mean_speed_kts = mean(speed_kts, na.rm = TRUE) |> round(1), total_dist_nm = max(cum_dist_nm) |> round(2) ) ``` #### Summarize environmental data ```r # Count hours at each Beaufort level environmental |> mutate(next_time = lead(datetime_edt), # Use lead() to find when the next record starts duration_hour = difftime(next_time, datetime_edt, units = "hours") |> as.numeric() )|> group_by(beaufort) |> summarise(total_hour = sum(duration_hour, na.rm = TRUE), .groups = "drop") |> arrange(beaufort) ``` --- ## 3.8 Saving data ```r # Save as CSV (readable by any software) write_csv(track, paste0(processed_dir, "track.csv")) write_csv(sightings, paste0(processed_dir, "sightings.csv")) write_csv(environmental, paste0(processed_dir, "environmental.csv")) # Save as .rds (preserves POSIXct types, factors, and all column types exactly) saveRDS(track, paste0(processed_dir, "track.rds")) saveRDS(sightings, paste0(processed_dir, "sightings.rds")) saveRDS(environmental, paste0(processed_dir, "environmental.rds")) # Reload (use .rds when reading into R) track <- readRDS(paste0(processed_dir, "track.rds")) sightings <- readRDS(paste0(processed_dir, "sightings.rds")) environmental <- readRDS(paste0(processed_dir, "environmental.rds")) ``` ::: {.callout-tip} Save **both** formats: `.csv` for interoperability with other tools, `.rds` for loading back into R (no type-guessing on re-import). ::: --- ::: {.callout-note} **Next:** [Module 4 — Plotting with ggplot2 →](module4.qmd) :::

3.1 The Tidyverse

3.2 Project setup

3.3 Example dataset

3.4 Reading the file in R

3.5 Cleaning Column Names with janitor

3.6 Basic data wrangling with dplyr

Keep, remove or rename columns with select()

Filter rows matching a condition with filter()

Create or modify columns with mutate()

NA handling with drop_na()

Sort rows with arrange()

3.6 Process survey data

3.6.1 Track data

3.6.2 Sightings table

3.6.3 Environmental conditions table

3.6.3 Groupwise calculations with summarise() + group_by()

Summarize number of sightings

Summarize survey effort

Summarize environmental data

3.8 Saving data

3.5 Cleaning Column Names with `janitor`

3.6 Basic data wrangling with `dplyr`

Keep, remove or rename columns with `select()`

Filter rows matching a condition with `filter()`

Create or modify columns with `mutate()`

NA handling with `drop_na()`

Sort rows with `arrange()`

3.6.3 Groupwise calculations with `summarise()` + `group_by()`