如何在R语言中基于列名自动合并拆分后的虚拟变量列并生成变量两两组合矩阵？

Got it, let's solve this problem efficiently—no more manual column selection for 1000+ columns! The core idea is to map each dummy column back to its original variable, then batch-calculate row sums for every pairwise combination of original variables. Here's a step-by-step solution using your example data:

First, Load Your Example Data #

Let's start by reproducing your sample setup to test the solution:

set.seed(100)
dfOG <- data.frame(
 day = sample(c('1', '2'), 3, replace = T),
 rain = sample(c('yes', 'no'), 3, replace = T),
 val1 = runif(3)
)

# Create the dummy pair matrix as in your example
name2 <- c('day.1', 'day.2', 'rain.yes', 'rain.no', 'val1')
nam2 <- expand.grid(name2, name2)
newName2 <- paste0(nam2$Var2, ":", nam2$Var1)
set.seed(100)
newMat2 <- matrix(rexp(75, rate=.1), nrow = 3, ncol = length(newName2))
colnames(newMat2) <- newName2

Step 1: Map Dummy Columns to Original Variables #

We'll use a regular expression to extract the original variable name from each dummy column. For columns like day.1, we strip everything after the dot to get day; for val1 (no dot), it stays as-is:

# Extract original variable names from dummy column headers
col_orig_var <- sub("\\..*$", "", colnames(newMat2))
# Quick check: this vector should list the original var for each column in newMat2
head(col_orig_var)

Step 2: Generate All Pairwise Combinations of Original Variables #

We need every ordered pair (including pairs where both variables are the same, like day.day):

# Get unique original variables
orig_vars <- unique(col_orig_var)
# Generate all ordered pairs (day.rain != rain.day, which matches your manual output)
var_pairs <- expand.grid(orig_vars, orig_vars, stringsAsFactors = FALSE)
# Create clean column names for the final output
var_pair_names <- paste0(var_pairs$Var1, ".", var_pairs$Var2)

Step 3: Batch-Calculate Row Sums for Each Pair #

We'll write a helper function to compute row sums for a given pair of original variables, then apply it to all pairs:

# Helper function to calculate row sums for a single original variable pair
calc_pair_sum <- function(pair) {
  # Identify columns where the first part of the dummy pair is pair[1], second is pair[2]
  cols_to_sum <- col_orig_var == pair[1] & sub("^.*:", "", colnames(newMat2)) == pair[2]
  # Sum rows for those columns (drop=FALSE ensures we handle single-column cases correctly)
  rowSums(newMat2[, cols_to_sum, drop = FALSE])
}

# Apply the function to all pairs and combine results into a matrix
final_matrix <- do.call(cbind, lapply(1:nrow(var_pairs), function(i) {
  calc_pair_sum(var_pairs[i, ])
}))

# Assign clean column names and convert to data frame if needed
colnames(final_matrix) <- var_pair_names
dfNew_auto <- as.data.frame(final_matrix)

Step 4: Verify It Matches Your Manual Result #

Let's confirm the automated output is identical to your manual dfNew:

# Create your manual dfNew for comparison
dfNew <- data.frame(
 day.day = apply(newMat2[,c(1,2,6,7)], 1, sum),
 day.rain = apply(newMat2[,c(3,4,8,9)], 1, sum),
 day.val1 = apply(newMat2[,c(5,10)], 1, sum),
 rain.day = apply(newMat2[,c(11,12,16,17)], 1, sum),
 rain.rain = apply(newMat2[,c(13,14,18,19)], 1, sum),
 rain.val1 = apply(newMat2[,c(15,20)], 1, sum),
 val1.day = apply(newMat2[,c(21,22)], 1, sum),
 val1.rain = apply(newMat2[,c(23,24)], 1, sum),
 val1.val1 = newMat2[,c(25)]
)

# Check if all columns match
all.equal(dfNew_auto, dfNew)
# Should return TRUE!

Tips for Large Datasets #

• Efficiency: This method uses vectorized operations (like rowSums and logical indexing) instead of slow loops, so it'll handle 1000+ columns smoothly.
• Custom Naming Patterns: If your dummy columns use underscores (e.g., day_1) instead of dots, adjust the regex: sub("_.*$", "", colnames(newMat2)).
• Single-Column Safety: The drop=FALSE argument ensures we don't accidentally convert a single column to a vector, which would break rowSums.

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