识别关联记录并分配唯一ID/数据清理：混乱数据集处理问询

Got it, let's work through cleaning this dataset step by step. You've got a few common data quality issues here: inconsistent casing in the id column, comma-separated multiple values in code, and invalid placeholder values like .... Below's a straightforward workflow using the tidyverse toolchain (the go-to for R data wrangling):

First, let's start with your sample data (formatted properly for R):

df <- data.frame(stringsAsFactors=FALSE, 
                 id = c("C01182", "C00966", "C00130", "d34567", "c34567", "C01142", "C00241", "C00232", "C01094", "C00979", "C00144"), 
                 code = c("13762", "13762", "13762, 13886,13850", "55653", "65247", "13698", "13698", "13698", "13880", "..."))

Step 1: Load Required Packages #

First, make sure you have the tidyverse installed and loaded—it includes dplyr (for data manipulation) and tidyr (for reshaping data):

install.packages("tidyverse")
library(tidyverse)

Step 2: Standardize id Casing #

Notice the id column has mixed uppercase/lowercase entries like d34567 and c34567—this is almost certainly a typo. Let's standardize everything to uppercase (switch to str_to_lower() if you prefer lowercase):

df_clean <- df %>%
  mutate(id = str_to_upper(id))

Step 3: Split Multi-Value code Entries into Rows #

The code column has entries with multiple values separated by commas (with inconsistent spacing). We'll split these into individual rows so each id + code pair is a single record:

df_clean <- df_clean %>%
  separate_rows(code, sep = "\\s*,\\s*")  # The regex handles commas with or without surrounding spaces

Step 4: Remove/Replace Invalid code Values #

The ... placeholder is useless for analysis—you can either replace it with NA (if you want to keep the row for reference) or filter those rows out entirely:

Option A: Replace with NA #

df_clean <- df_clean %>%
  mutate(code = if_else(code == "...", NA_character_, code))

Option B: Filter Out Invalid Rows #

df_clean <- df_clean %>%
  filter(code != "...")

Step 5: Optional: Validate code Format #

If code should be strictly numeric, you can strip any non-numeric characters (just in case there are hidden typos):

df_clean <- df_clean %>%
  mutate(code = str_remove_all(code, "\\D"))  # Removes all non-digit characters

Final Clean Dataset Preview #

After running these steps, you'll have a tidy dataset where each row is a single id + code pair. Here's what the first few rows look like:

head(df_clean)
#>        id  code
#> 1 C01182 13762
#> 2 C00966 13762
#> 3 C00130 13762
#> 4 C00130 13886
#> 5 C00130 13850
#> 6 D34567 55653

From here, you can easily do things like count how many ids are associated with each code, or join this dataset with other tables using code as a key.

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