如何在大型三文鱼养殖data.frame数据集中为各养殖场补全年度1-12月数据并去重

Hey there! Let's work through this problem together—this is a super common issue with panel time-series data, and we can use the tidyverse toolkit in R to fix it smoothly. Here's a step-by-step solution tailored to your salmon farming dataset:

Step 1: Remove duplicate month rows #

First, we need to clean up those duplicate entries where a farm has two rows for the same month in a year. We'll keep only unique combinations of farm, year, and month. If you have conflicting values in duplicates (e.g., two different production numbers for the same month), you can adjust this step to aggregate (like take the average or sum) instead of just dropping duplicates.

library(tidyverse)

# Replace "salmon_data" with your actual data frame name
cleaned_no_duplicates <- salmon_data %>%
  # Keep only unique farm-year-month rows, retain all other columns
  distinct(org_anonym, year, month, .keep_all = TRUE)

# If you need to aggregate duplicates instead (e.g., average numeric columns):
# cleaned_no_duplicates <- salmon_data %>%
#   group_by(org_anonym, year, month) %>%
#   summarise(across(where(is.numeric), mean, na.rm = TRUE), .groups = "drop")

Step 2: Build a complete reference frame #

Next, we'll create a "template" that includes every farm, every year (2005-2020), and every month (1-12). This ensures we have no gaps in our time series.

# Extract all unique farms from your data
all_farms <- unique(cleaned_no_duplicates$org_anonym)
# Define the full year and month ranges
all_years <- 2005:2020
all_months <- 1:12

# Generate every possible combination of farm, year, month
full_time_template <- expand_grid(
  org_anonym = all_farms,
  year = all_years,
  month = all_months
)

Step 3: Merge and fill missing values #

Now we'll combine our cleaned data with the template, then fill any missing data points with 0 or NA (whichever you prefer).

# Merge the template with your cleaned data
final_complete_data <- full_time_template %>%
  left_join(cleaned_no_duplicates, by = c("org_anonym", "year", "month")) %>%
  # Fill ALL numeric columns with 0 (replace with NA if you don't want to impute)
  mutate(across(where(is.numeric), ~replace_na(., 0)))

# If you only want to fill specific columns (e.g., "feed_usage" and "production"):
# final_complete_data <- final_complete_data %>%
#   mutate(
#     feed_usage = replace_na(feed_usage, 0),
#     production = replace_na(production, 0)
#   )

Quick notes for edge cases: #

• If your month column uses text (like "Jan" or "December") instead of numbers, convert it to numeric first with lubridate::month() (e.g., month = month(month_label, label = FALSE)).
• Double-check that your year column is formatted as integers (not characters) to avoid mismatches during the merge.

This approach will give you a dataset where every farm has 12 months of data for every year from 2005 to 2020—no duplicates, no missing months, and missing values filled as you specified.

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