读取Excel数据后缺失值填充与字符替换的R语言技术问题

Hey there! Let's get your Animal ID data cleaned up properly, and fix that odd behavior you're seeing with tibbles. Here's a clean, robust approach using the tidyverse that works seamlessly with both data frames and tibbles:

Step-by-Step Cleanup (and a Better Way to Do It) #

First, let's recap your core goals clearly:

• Strip the -L/-R suffixes from IDs
• Convert the cleaned values to numeric type
• Fill missing values with the nearest non-NA value directly above them

Instead of relying on str_replace_all (which might have been tripping you up with tibbles), we can use parse_number() from the readr package (included in the tidyverse) to handle both suffix removal and numeric conversion in one go—it automatically extracts numeric values from strings, ignoring any non-digit characters like -L or -R. Then, we'll use fill() from tidyr to easily propagate the last non-NA value downward to fill gaps.

Here's the full code, using a simulated version of your input data:

# Load the tidyverse (includes dplyr, tidyr, readr, etc.)
library(tidyverse)

# Simulate your input tibble
animal_data <- tibble(
  Animal_ID = c("38-L", NA, "39-L", NA, "40-L", NA)
)

# Clean and fill the data in one pipeline
cleaned_animals <- animal_data %>%
  mutate(
    # Extract numeric values (automatically drops -L/-R suffixes)
    Animal_ID_clean = parse_number(Animal_ID)
  ) %>%
  # Fill NA values with the previous non-NA entry
  fill(Animal_ID_clean, .direction = "down")

# View the final result
cleaned_animals

Why str_replace_all Might Have Failed with Tibbles #

Chances are, the issue wasn't with tibbles themselves—it was one of two small oversights:

1. You forgot to load the stringr package (where str_replace_all lives) before using it
2. Your column was accidentally stored as a factor (though tibbles default to character columns, so this is less likely)

Using parse_number() avoids this entirely, since it's part of the core tidyverse toolset you're already using with tibbles.

If You Prefer a Base R Solution (Less Recommended for Readability) #

If you want a base R alternative (though it's less concise and readable), you can use this approach (requires the zoo package for filling NAs):

library(zoo)

# Simulate your input data frame
animal_df <- data.frame(
  Animal_ID = c("38-L", NA, "39-L", NA, "40-L", NA),
  stringsAsFactors = FALSE
)

# Remove suffix and convert to numeric
animal_df$Animal_ID_clean <- as.numeric(sub("-.*", "", animal_df$Animal_ID))
# Fill NAs with the last non-NA value
animal_df$Animal_ID_clean <- na.locf(animal_df$Animal_ID_clean)

But the tidyverse pipeline is far more intuitive and maintainable for this kind of data wrangling.

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