多站点陷阱有效工作天数计算（含故障排除规则）及鳗鱼捕获量统计技术需求

Alright, let's work through how to calculate your effective trap working days and total eel catches per monitoring site in R. We'll use the tidyverse ecosystem (dplyr + lubridate) since it's perfect for handling grouped date calculations with tbl_df/tbl objects.

Step 1: Load Required Packages #

First, make sure you have these packages installed (run install.packages(c("tidyverse", "lubridate")) if not):

library(tidyverse)
library(lubridate)

Step 2: Full Calculation Code #

Replace df with your actual data frame name, then run this code:

# Process data and calculate metrics
site_summary <- df %>%
  # Convert string dates to proper date format (day/month/year)
  mutate(Date.of.Survey = dmy(Date.of.Survey)) %>%
  # Sort by site and survey date to ensure chronological order
  arrange(Location, Date.of.Survey) %>%
  # Group calculations by each monitoring site
  group_by(Location) %>%
  # Calculate days between current and previous survey
  mutate(interval_days = as.numeric(Date.of.Survey - lag(Date.of.Survey))) %>%
  # Determine which intervals count as effective working days
  mutate(valid_days = case_when(
    # First survey has no prior date, so 0 effective days
    is.na(interval_days) ~ 0,
    # If trap was working when checked, the interval is valid
    Was.the.trap.working.when.you.checked.it. == "Yes" ~ interval_days,
    # If trap was broken, the interval is invalid (matches your rule)
    Was.the.trap.working.when.you.checked.it. == "No" ~ 0,
    # Catch-all for edge cases
    TRUE ~ 0
  )) %>%
  # Sum up the metrics for each site
  summarise(
    total_effective_working_days = sum(valid_days, na.rm = TRUE),
    total_eels_caught = sum(Number.of.eels, na.rm = TRUE)
  )

# View the final summary table
print(site_summary)

How This Works #

Let's break down the logic to match your requirements:

1. Date Conversion: dmy() converts your day-first date strings into R's date format, which is necessary for calculating day differences.
2. Group & Sort: We group by Location and sort by date to ensure we're calculating intervals in the correct chronological order.
3. Interval Calculation: lag(Date.of.Survey) grabs the previous survey date for each site, and subtracting gives us the number of days between surveys.
4. Valid Days Logic:
   • If a survey finds the trap working (Yes), the days since the last survey count as effective (this includes intervals where the prior survey was broken—we assume the trap was repaired between surveys).
   • If a survey finds the trap broken (No), the days since the last survey are marked as invalid, which aligns with your rule that days from the last working date to the broken date don't count.
5. Summary: We sum up all valid days and total eels for each site to get your final metrics.

Example Output #

You'll get a tidy summary table like this (values are placeholders for your data):

内容的提问来源于stack exchange，提问作者Sam