如何在for循环中对DF1至DF621执行spread函数并按GEOID左连接？

Got it, let's tackle this problem efficiently—handling 621 data frames one by one would be a nightmare, so we'll use list-based workflows to make this clean and scalable.

Step 1: Collect all your data frames into a single list #

Instead of referencing DF1 to DF621 individually, we can pull all of them into a list in one go using mget(), which grabs objects from your environment by their names:

# Create a list containing every DF1 through DF621
df_list <- mget(paste0("DF", 1:621))

Step 2: Spread each data frame in the list #

We’ll use purrr::map() (or imap() for extra context) to apply the spread() function to every data frame in the list. A critical detail: if different DF[i] share the same variable names, spreading them directly will create duplicate column names during merging. To avoid this, we can add a unique suffix to each spread dataset’s columns. Here are two options:

Option 1: No suffix (only if all variable names are unique across all DF[i]) #

Use this only if you’re 100% sure no two data frames have overlapping variable values:

library(tidyr)
library(dplyr)
library(purrr)

# Spread each data frame, retain GEOID and Name columns
spread_list <- map(df_list, ~ spread(.x, key = variable, value = normalized) %>%
                     select(GEOID, Name, everything()))

Option 2: Add a suffix (recommended for safety) #

This appends _DF1, _DF2, etc., to the spread columns so you can track which original data frame each value came from:

library(tidyr)
library(dplyr)
library(purrr)
library(stringr)

spread_list <- imap(df_list, function(df, df_name) {
  # Extract the number from the data frame name (e.g., "DF21" becomes "21")
  df_number <- str_extract(df_name, "\\d+")
  # Spread the data frame
  spread_df <- spread(df, key = variable, value = normalized)
  # Rename all columns except GEOID and Name with the suffix
  spread_df <- spread_df %>%
    rename_with(~ paste0(.x, "_DF", df_number), !c(GEOID, Name))
  return(spread_df)
})

Step 3: Merge all spread data frames with left join #

Use Reduce() to perform a cumulative left join on the entire list of spread data frames. This avoids writing 620 separate left_join() calls by hand:

# Merge all spread data frames, joining on GEOID and Name (to ensure consistency)
final_merged_df <- Reduce(function(x, y) left_join(x, y, by = c("GEOID", "Name")), spread_list)

# If you don't need to validate Name matches, just join on GEOID:
# final_merged_df <- Reduce(function(x, y) left_join(x, y, by = "GEOID"), spread_list)

Key Notes #

• Name Column Consistency: Including Name in the by argument ensures you don’t end up with mismatched names for the same GEOID across different data frames.
• Missing Values: Left joins will keep all rows from the first data frame in your list and fill in NA where other data frames lack a matching GEOID. If you only want GEOIDs present in every data frame, swap left_join() for inner_join().
• Performance: 621 data frames will take a minute to process, but list-based operations are way faster than manual joins!

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