求助：用R语言按条件更新CSV文件并为数据框新增/更新列

Hey there! Let's tackle this problem step by step—since you're working with lamb data across 2017 and 2018, we can use matching on damtag to fill in those missing damsource2 values properly. Here's how to do it clearly, with both tidyverse/dplyr and base R options (pick whichever feels more comfortable for you):

First, Let's Replicate Your Sample Data #

First, let's create a small sample dataset that matches your scenario (so you can test the code directly):

# Simulate your sheep data
sheep_data <- data.frame(
  year = c(2017, 2017, 2018, 2018, 2018),
  damtag = c("D001", "D002", "D001", "D002", "D003"),
  damsource2 = c("Farm A", "Farm B", NA, NA, NA)
)

This includes:

• 2017 entries with valid damsource2 values
• 2018 entries where damsource2 is missing
• A dam (D003) that only appears in 2018 (should stay NA as requested)

Method 1: Using dplyr (Tidyverse, Recommended for Readability) #

If you're open to using the tidyverse, dplyr makes this process intuitive with pipe operators (%>%):

1. First, install/load the dplyr package if you haven't already:

install.packages("dplyr") # Only run once
library(dplyr)

2. Create a "lookup table" of 2017 dam sources (one entry per unique damtag):

# Extract unique 2017 dam-source pairs
dam_source_lookup <- sheep_data %>%
  filter(year == 2017) %>% # Keep only 2017 data
  distinct(damtag, .keep_all = TRUE) %>% # Ensure one row per damtag
  select(damtag, damsource_2017 = damsource2) # Rename for clarity

3. Merge this lookup table with your original data and fill missing values:

updated_sheep_data <- sheep_data %>%
  left_join(dam_source_lookup, by = "damtag") %>% # Match damtags across datasets
  # Replace NA damsource2 with 2017 values; keep existing non-NA values
  mutate(damsource2 = ifelse(is.na(damsource2), damsource_2017, damsource2)) %>%
  select(-damsource_2017) # Remove the temporary lookup column

Method 2: Using Base R (No Extra Packages) #

If you prefer to stick with base R, here's an equivalent approach:

1. Create the lookup table from 2017 data:

# Get unique 2017 dam-source pairs
dam_source_lookup <- unique(subset(sheep_data, year == 2017, select = c(damtag, damsource2)))
names(dam_source_lookup)[2] <- "damsource_2017" # Rename column for clarity

2. Merge and update the missing values:

# Merge original data with lookup table (keep all original rows)
updated_sheep_data <- merge(sheep_data, dam_source_lookup, by = "damtag", all.x = TRUE)

# Fill missing damsource2 values
updated_sheep_data$damsource2 <- ifelse(
  is.na(updated_sheep_data$damsource2),
  updated_sheep_data$damsource_2017,
  updated_sheep_data$damsource2
)

# Remove the temporary lookup column
updated_sheep_data <- updated_sheep_data[, !(names(updated_sheep_data) == "damsource_2017")]

Check the Result #

Run this to see your updated data:

print(updated_sheep_data)

You should get output like this:

year damtag damsource2
1 2017   D001      Farm A
2 2017   D002      Farm B
3 2018   D001      Farm A
4 2018   D002      Farm B
5 2018   D003       <NA>

Perfect! The 2018 entries now have the correct damsource2 values from 2017, and the dam that only appears in 2018 stays NA as requested.

Quick Tips for Your Real Data #

• When reading your CSV file, make sure damtag is treated as text (not a factor). Use:

  sheep_data <- read.csv("your_file.csv", stringsAsFactors = FALSE)
  

• If you have multiple entries for the same damtag in 2017, the distinct() (dplyr) or unique() (base R) steps ensure we only use one valid source value per dam.

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