在R中合并同名列：处理mongolite导出的MongoDB数据集

Got it, let's tackle this problem step by step. You're dealing with a wide-format data frame from mongolite where columns A and B repeat, plus trailing NA columns that don’t get proper names. The goal is to reshape this into a long-format where each pair of A/B (or NA/NA) rows is stacked under the original _id. Here's how to do it:

Step 1: Simulate Your Dataset #

First, let's replicate the data structure you described (mongolite might return a data frame with duplicate column names, so we use check.names = FALSE to preserve them):

# Simulate the raw data from mongolite
df <- data.frame(
  _id = c(1, 2),
  A = c("a", "k"),
  B = c(1, 4),
  A = c("b", "l"),
  B = c(2, 3),
  A = c("e", "c"),
  B = c(5, 3),
  NA = c(NA, "d"),
  NA = c(NA, 4),
  check.names = FALSE
)

Step 2: Clean Up Column Names #

R automatically appends suffixes to duplicate column names, but we'll explicitly rename them to make grouping easier. We'll also name the trailing NA columns for clarity:

col_names <- colnames(df)

# Rename repeated A/B columns with sequential numbers
col_names[col_names == "A"] <- paste0("A", seq_along(col_names[col_names == "A"]))
col_names[col_names == "B"] <- paste0("B", seq_along(col_names[col_names == "B"]))

# Rename unnamed NA columns
na_col_indices <- which(is.na(col_names))
col_names[na_col_indices] <- paste0("NA", seq_along(na_col_indices))

colnames(df) <- col_names

Step 3: Dynamically Reshape to Long Format #

We'll create groups of columns (each group is either A_n + B_n or NA_x + NA_y) and stack them vertically. This approach adapts automatically if your future data has more columns:

Using dplyr & purrr (tidyverse) #

library(dplyr)
library(purrr)

# Calculate the number of groups (based on the maximum count of A or B columns)
group_count <- max(sum(grepl("^A", col_names)), sum(grepl("^B", col_names)))

# Iterate over each group, extract the columns, rename to A/B, and bind rows
result <- map_dfr(1:group_count, function(i) {
  # Determine which columns to pick for this group
  a_col <- ifelse(paste0("A", i) %in% col_names, paste0("A", i), paste0("NA", (i*2)-1))
  b_col <- ifelse(paste0("B", i) %in% col_names, paste0("B", i), paste0("NA", i*2))
  
  df %>%
    select(_id, all_of(a_col), all_of(b_col)) %>%
    rename(A = all_of(a_col), B = all_of(b_col))
})

print(result)

Using Base R (No External Packages) #

If you prefer not to use tidyverse packages, here's a base R alternative:

final_df <- data.frame()
group_count <- max(sum(grepl("^A", col_names)), sum(grepl("^B", col_names)))

for(i in 1:group_count) {
  a_col <- ifelse(paste0("A", i) %in% col_names, paste0("A", i), paste0("NA", (i*2)-1))
  b_col <- ifelse(paste0("B", i) %in% col_names, paste0("B", i), paste0("NA", i*2))
  
  temp_df <- df[, c("_id", a_col, b_col)]
  colnames(temp_df)[2:3] <- c("A", "B")
  final_df <- rbind(final_df, temp_df)
}

print(final_df)

Output #

Both approaches will give you the desired long-format data:

_id    A    B
1    1    a    1
2    2    k    4
3    1    b    2
4    2    l    3
5    1    e    5
6    2    c    3
7    1 <NA> <NA>
8    2    d    4

This solution handles variable column counts (so if future data has more A/B pairs, it will automatically include them) and properly maps trailing unnamed columns to the final A/B structure.

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