R语言拆分多值列后分组计数的数据清洗问题求助

Hey there! Let's work through this data cleaning problem together and get you the exact result you need.

Why You're Seeing That Error #

You’ve nailed the root cause: when you use unlist(strsplit(A, "\\:+")), you end up with a vector of length 4 (split values: "A", "A", "B", "B") but your original B vector only has 3 elements. Aggregate needs these vectors to be the same length to pair each split value with its corresponding B entry, hence the "variable lengths differ" error.

Solution 1: Using Tidyverse (Clean & Intuitive) #

Tidyverse tools make this unnest-and-count task super straightforward. Here’s how to implement it:

library(tidyverse)

# Your original data (using data.frame() avoids unwanted factor conversion from cbind())
A <- c("A","A:::B","B")
B <- c("x","x","y")
df <- data.frame(A, B, stringsAsFactors = FALSE)

# Process to get your desired output
df %>%
  # Split the A column into a list of values per row
  mutate(A = str_split(A, ":::")) %>%
  # Unnest the list column to create a row for each split value
  unnest(A) %>%
  # Count occurrences of each (A, B) pair
  count(A, B, name = "Count") %>%
  # Rename columns to match your target format
  rename(Var1 = A, Var2 = B)

This will output exactly what you’re looking for:

# A tibble: 3 × 3
  Var1  Var2 Count
  <chr> <chr> <int>
1 A     x         2
2 B     x         1
3 B     y         1

Solution 2: Base R (No External Packages) #

If you prefer to stick with base R, you can manually create a matched B vector that aligns perfectly with the split A values:

# Original data
A <- c("A","A:::B","B")
B <- c("x","x","y")
df <- data.frame(A, B, stringsAsFactors = FALSE)

# Split the A column into a list of values per row
split_A <- strsplit(df$A, ":::")
# Create a B vector where each value repeats as many times as its corresponding A row was split
matched_B <- rep(df$B, sapply(split_A, length))
# Combine split A values and matched B into a new data frame
expanded_df <- data.frame(Var1 = unlist(split_A), Var2 = matched_B)
# Count occurrences of each (Var1, Var2) pair
result <- aggregate(. ~ Var1 + Var2, data = expanded_df, FUN = length)
# Rename the count column to match your desired output
colnames(result)[3] <- "Count"

# View the final result
result

Running this code will produce:

Var1 Var2 Count
1    A    x     2
2    B    x     1
3    B    y     1

The core fix here is using rep() to ensure every split A value has a corresponding B value, making their lengths match perfectly for aggregation.

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