R语言：当ID存在重复值时，如何以ID变量为最终值完成数据重塑

Hey there! Let's tackle your problem step by step. First, we'll fix that loop you were struggling with, then we'll look at a much cleaner and more efficient way to get your desired output—especially critical given your 120k+ rows of data.

1. Fixing Your Loop Code #

Your original loop had two key issues: you were checking if the column name (not the column values) was NA, and the assignment syntax was off. Here's the corrected version:

# First, identify columns that start with "Output_"
output_cols <- grep("^Output_", colnames(df2), value = TRUE)

# Loop through each target column
for(col in output_cols) {
  # Find rows where the column isn't NA
  non_na_rows <- !is.na(df2[[col]])
  # Replace those positions with the corresponding Input value
  df2[[col]][non_na_rows] <- df2$Input[non_na_rows]
}

# Clean up column names to match your target format
colnames(df2) <- gsub("Output_", "", colnames(df2))

This will correctly replace non-NA values with matching Input entries and rename columns to your desired 91/92 format.

2. A More Efficient Full Workflow #

Looping isn't the most optimal approach for large datasets. The core fix here is first removing duplicate Input-Output pairs (since they don't add new information), then reshaping directly to your target format. Here are two robust options:

Option 1: Tidyverse (Readable & Reliable)

Great for most use cases, with clear syntax:

library(tidyverse)

# Step 1: Remove duplicate Input-Output pairs to shrink data size
df_unique <- df %>%
  distinct(Input, Output, .keep_all = TRUE)

# Step 2: Reshape to wide format directly
df_wide <- df_unique %>%
  pivot_wider(
    names_from = Output,  # Use Output values as column names
    values_from = Input,  # Fill cells with matching Input values
    values_fill = list(Input = NA)  # Fill missing entries with NA
  ) %>%
  arrange(Input)  # Optional: Sort rows by Input to match your target

Option 2: data.table (Blazing Fast for Large Data)

If your 120k+ rows are causing performance lag, data.table is optimized for big data operations:

library(data.table)

# Convert to data.table format
setDT(df)

# Step 1: Remove duplicates
df_unique <- unique(df, by = c("Input", "Output"))

# Step 2: Reshape to wide format
df_wide <- dcast(
  df_unique,
  Input ~ Output,
  value.var = "Input",
  fill = NA
)

Both methods will directly produce your exact target format, no extra cleanup loops needed. Removing duplicates first is the key to speeding up operations and avoiding issues with repeated entries.

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