如何在R中将22个特定命名的Excel文件数据重排为目标格式？

Hey there! Let's tackle this data reshaping task step by step. Since you mentioned a "specified format" but didn't share the exact structure, I'll cover common practical scenarios and flexible approaches you can adapt to your needs.

Assuming you've loaded all CSV files into a list (e.g., using list.files() + lapply(read.csv, ...)), start by confirming the structure of your data and extracting metadata from the filenames (since they follow a consistent pattern):

# Example: Load files into a list if you haven't already
csv_files <- list.files(pattern = "*.CSV", full.names = TRUE)
csv_list <- lapply(csv_files, read.csv, header = FALSE) # Adjust header based on your files
names(csv_list) <- basename(csv_files)

# Check structure of the first file to confirm 850 rows × 2 columns
str(csv_list[[1]])

# Extract Treatment (T1) and Week (W1/W10) from filenames
metadata <- tibble(
  file_name = names(csv_list),
  Treatment = stringr::str_extract(file_name, "^T\\d+"),
  Week = stringr::str_extract(file_name, "W\\d+")
)

Below are three typical target formats with corresponding R code using dplyr and tidyr (install them first if you haven't: install.packages(c("dplyr", "tidyr", "stringr"))):

Scenario 1: Wide Format (One Row per Feature) #

If you want each of the 850 features as a row, with columns for each Treatment-Week combination's value:

library(dplyr)
library(tidyr)

combined_wide <- pmap(list(csv_list, metadata$Treatment, metadata$Week), function(df, trt, wk) {
  df %>%
    rename(Feature = V1, Value = V2) %>% # Adjust V1/V2 if your columns have headers
    mutate(Treatment = trt, Week = wk)
}) %>%
  bind_rows() %>%
  pivot_wider(
    names_from = c(Treatment, Week),
    values_from = Value,
    names_sep = "_"
  )

# Verify: 850 rows (features) + 22 value columns + 1 Feature column
dim(combined_wide)

Scenario 2: Long Format (One Row per Observation) #

This format is ideal for statistical analysis or visualization, where each row represents a single feature-Treatment-Week value:

combined_long <- pmap(list(csv_list, metadata$Treatment, metadata$Week), function(df, trt, wk) {
  df %>%
    rename(Feature = V1, Value = V2) %>%
    mutate(Treatment = trt, Week = wk)
}) %>%
  bind_rows()

# Verify: 850 × 22 = 18700 rows, 4 columns (Feature, Value, Treatment, Week)
dim(combined_long)

Scenario 3: Feature-as-Columns Format (One Row per File) #

If you want each file (Treatment-Week combination) as a row, with 850 features as columns:

combined_feature_cols <- pmap(list(csv_list, metadata$Treatment, metadata$Week), function(df, trt, wk) {
  df %>%
    t() %>% # Transpose to turn features into columns
    as.data.frame() %>%
    slice(-1) %>% # Remove the first row (original feature names, now column headers)
    mutate(Treatment = trt, Week = wk) %>%
    rename_with(~ df[[1]], 1:850) # Rename columns to match feature names
}) %>%
  bind_rows()

# Verify: 22 rows (files) + 850 feature columns + 2 metadata columns
dim(combined_feature_cols)

Always double-check the output to ensure it matches your expectations:

# Inspect the first few rows
head(combined_wide) # Or combined_long/combined_feature_cols

# Check for missing values (if any)
sum(is.na(combined_wide))

If your target format differs from these scenarios, share more details about the exact structure you need, and I can tweak the code accordingly!

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