R语言语法需求：将同一被试多行数据合并为单行

Hey there! Since you're comfortable with SPSS but new to R, I'll walk you through this task with straightforward, commented code that you can adapt to your data. The goal is to take your multi-row per subject Excel files and convert them to one row per subject with all their data.

Step 1: Set Up R with Required Tools #

First, we'll install and load the packages we need—these are like R's version of SPSS procedures, but more flexible.

# Install packages (run this ONCE, then you can skip it later)
install.packages(c("readxl", "dplyr", "tidyr", "purrr", "writexl"))

# Load the packages every time you run this script
library(readxl)   # Reads Excel files
library(dplyr)    # For data grouping/cleaning
library(tidyr)    # Converts "long" data to "wide" (your key step!)
library(purrr)    # Handles batch processing of multiple files
library(writexl)  # Saves processed data back to Excel

Step 2: Batch Process All Your Excel Files #

We'll create a function to handle one file at a time, then apply it to all your Excel files automatically.

Key Notes Before You Run: #

• Replace "subject_id" in the code below with the actual name of your subject ID column (e.g., "ID", "Participant_Number").
• Make sure all your Excel files are in the same folder as your R script (or adjust the file path if needed).

# Get a list of all Excel files in your current folder
excel_files <- list.files(pattern = "\\.xlsx$", full.names = TRUE)

# Define a function to process a single Excel file
process_single_file <- function(file_path) {
  # 1. Read the Excel file (uses the first worksheet by default)
  raw_data <- read_excel(file_path)
  
  # 2. Define your subject ID column (UPDATE THIS TO MATCH YOUR DATA!)
  subject_id_column <- "subject_id"
  
  # 3. Convert multi-row per subject to single-row (wide format)
  # We add a row number to each subject's rows to avoid duplicate column names
  cleaned_data <- raw_data %>%
    group_by(!!sym(subject_id_column)) %>%  # Group data by subject ID
    mutate(row_number = row_number()) %>%  # Assign 1,2,3... to each row per subject
    pivot_wider(
      id_cols = all_of(subject_id_column),  # Keep subject ID as the main identifier
      names_from = row_number,              # Use row numbers to name new columns
      values_from = everything(),           # Move all other columns to wide format
      names_sep = "_"                       # Separate original column name and row number (e.g., "score_1", "score_2")
    ) %>%
    ungroup()  # Remove grouping after processing
  
  # 4. Save the processed file (adds "_processed" to the original filename)
  output_filename <- gsub("\\.xlsx$", "_processed.xlsx", file_path)
  write_xlsx(cleaned_data, output_filename)
  
  # Optional: Return the processed data for checking
  return(cleaned_data)
}

# 5. Run the function on ALL your Excel files
all_processed_data <- map(excel_files, process_single_file)

How This Works (SPSS Analogy) #

• group_by() is like splitting your data by subject ID in SPSS.
• pivot_wider() does the same thing as SPSS's "Restructure" wizard when converting from long to wide format—just way faster for multiple files!
• The batch processing with map() replaces manually opening and processing each file one by one.

Quick Troubleshooting Tips #

• If you get an error about missing columns, double-check that subject_id_column matches exactly the column name in your Excel files (capitalization matters!).
• If you have missing values in your data, R will keep them as NA—just like SPSS's system-missing values.
• If your Excel files have multiple worksheets, add sheet = "SheetName" inside read_excel() to specify which sheet to use.

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