如何用readr/tidy读取多份CSV并添加ID键？处理固定格式地下水TXT数据

Got it, let's tackle your two questions one by one—both using the tidyverse ecosystem (since you mentioned readr/tidy tools) to keep things consistent and readable.

When working with multiple CSVs, the purrr package (part of tidyverse) makes batch reading and combining straightforward. We'll add an ID column to track which file each row came from, either by extracting it from filenames or using a custom mapping.

First, load the required packages:

library(tidyverse) # Includes readr, purrr, dplyr, stringr, etc.

Option 1: Extract ID from filenames #

If your CSV filenames include well IDs (e.g., well_01.csv, well_02.csv), we can pull that ID directly:

# Get paths to all CSV files in your target folder
csv_files <- list.files(path = "your_data_folder", pattern = "\\.csv$", full.names = TRUE)

# Read all CSVs, add a `well_id` column, and bind rows automatically
combined_csv <- map_dfr(csv_files, function(file) {
  read_csv(file) %>%
    # Extract well ID from the filename (adjust regex to match your naming pattern)
    mutate(well_id = str_extract(basename(file), "well_\\d+"))
})

• map_dfr() reads each file and binds all results into a single data frame.
• basename(file) removes the full path, leaving just the filename.
• str_extract() uses regex to pull the ID—tweak the pattern if your filenames are formatted differently (e.g., Well-17.csv would use Well-\\d+).

Option 2: Custom ID mapping #

If filenames don't include IDs, create a manual mapping to link files to your IDs:

# Create a table linking file paths to your custom well IDs
file_id_map <- tibble(
  file_path = csv_files,
  well_id = c("Well 02", "Well 17", "Well 23") # Match the order of csv_files
)

# Read files, combine, and attach IDs
combined_csv <- file_id_map %>%
  mutate(data = map(file_path, read_csv)) %>% # Read each file into a nested column
  unnest(data) %>% # Expand nested data into rows
  select(-file_path) # Remove the file path column if you don't need it

That TXT format is tricky—each well's data is a single continuous string with a header followed by packed entries. We'll split the file into well blocks, parse each block, and clean up the data into a tidy format.

Step 1: Load and split the raw text #

# Read the entire file as a single string
raw_text <- read_file("groundwater_logger.txt")

# Split the text into individual well blocks (starts with "well" or "Well")
well_blocks <- str_split(raw_text, "(?<=Serial Number)(?=\\s*(well|Well))")[[1]]

The regex here splits the text right after Serial Number and just before the next well's header, ensuring each block contains one well's full data.

Step 2: Write a function to process each well block #

We'll create a helper function to extract the well ID, split the data entries, and convert them into a tidy tibble:

process_well_block <- function(block) {
  # Extract and standardize the well ID (e.g., "well 02" → "Well 02")
  well_id <- str_extract(block, "(well|Well)\\s+\\d+") %>% str_to_title()
  
  # Split the block into header and raw data parts
  header_data_split <- str_split(block, "Serial Number\\s*")[[1]]
  raw_data_str <- str_trim(header_data_split[2]) # Get the packed data string
  
  # Split the data string into individual entries (each starts with a number)
  data_entries <- str_split(raw_data_str, "(?<=\\d)\\s+(?=\\d)")[[1]]
  
  # Parse each entry into columns and combine into a tibble
  map_dfr(data_entries, function(entry) {
    # Split entry by commas and trim whitespace from each value
    entry_values <- str_split(entry, ",")[[1]] %>% str_trim()
    
    # Map values to columns (handle cases where Serial Number might be missing)
    tibble(
      well_id = well_id,
      entry_index = entry_values[1],
      time = entry_values[2],
      current_feet = as.numeric(entry_values[3]),
      serial_number = ifelse(length(entry_values) >= 4, entry_values[4], NA)
    )
  })
}

Step 3: Process all blocks and clean the final data #

# Process all well blocks and combine into one dataset
combined_groundwater <- map_dfr(well_blocks, process_well_block)

# Optional: Convert time to datetime format and clean up
combined_groundwater <- combined_groundwater %>%
  mutate(time = parse_datetime(time)) %>% # Auto-detect datetime format
  select(well_id, time, current_feet, serial_number, entry_index) # Reorder columns

Quick adjustments if needed: #

• If some entries don't have a Serial Number, the ifelse will fill those with NA—adjust this logic if your data has different missing patterns.
• If the entry split regex doesn't work (e.g., entries are separated differently), tweak str_split(raw_data_str, "(?<=\\d)\\s+(?=\\d)") to match your actual data separators.

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