如何程序化重组Pandas切片并整合多参与者心理测量数据？

Hey there! I’ve been in your shoes—manually slicing DataFrames for participant data gets old fast. Let’s build a reusable, automated workflow that will handle all your sheets in one go, no repetitive code required.

Core Approach #

The goal is to:

• Batch-read all Excel sheets (each tied to a participant)
• Write a reusable function to clean and reshape each participant’s data into a single row
• Combine all reshaped data into one master DataFrame with a client_code identifier

Step-by-Step Solution #

First, make sure you’ve got pandas installed (if not, run pip install pandas).

1. Define a Reusable Processing Function #

This function will take a single participant’s sheet DataFrame and their identifier, then reshape it into the wide format you need:

import pandas as pd

def process_participant_sheet(sheet_df, client_code):
    # Skip the first 2 rows (matches your manual iloc[2:] step)
    cleaned_data = sheet_df.iloc[2:].reset_index(drop=True)
    
    # Assume the first column contains timepoint labels (e.g., "Baseline", "Week 4")
    timepoint_col = cleaned_data.columns[0]
    # Set timepoints as the index to organize responses
    cleaned_data = cleaned_data.set_index(timepoint_col)
    
    # Reshape: turn timepoint + item combinations into individual columns
    # Stack items into rows, then unstack to create timepoint-item columns
    reshaped_data = cleaned_data.stack().unstack([0, 1])
    
    # Rename columns to be human-readable (e.g., "Baseline_Item1" instead of a tuple)
    reshaped_data.columns = ['_'.join(col_pair) for col_pair in reshaped_data.columns]
    
    # Add the client code identifier
    reshaped_data['client_code'] = client_code
    
    # Reset index to get a single row per participant
    return reshaped_data.reset_index(drop=True)

2. Batch Process All Sheets #

Now we’ll read all sheets from your Excel file, process each one, and combine the results:

# Read all sheets into a dictionary: {sheet_name: DataFrame}
all_participant_sheets = pd.read_excel("your_psychometric_data.xlsx", sheet_name=None)

# Process each sheet and collect results
processed_participants = []
for sheet_name, sheet_data in all_participant_sheets.items():
    # Use the sheet name as client code (adjust this if your client ID is in the sheet itself!)
    participant_df = process_participant_sheet(sheet_data, client_code=sheet_name)
    processed_participants.append(participant_df)

# Combine all participants into one master DataFrame
final_master_df = pd.concat(processed_participants, ignore_index=True)

3. Adjust for Your Exact Data Structure #

If your raw sheet layout differs from the assumption (e.g., timepoints are in columns instead of rows), tweak the reshaping step:

• If timepoints are columns and items are rows:

  cleaned_data = sheet_df.iloc[2:].reset_index(drop=True)
  item_col = cleaned_data.columns[0]
  cleaned_data = cleaned_data.set_index(item_col)
  reshaped_data = cleaned_data.stack().unstack([1, 0])
  reshaped_data.columns = ['_'.join(col_pair) for col_pair in reshaped_data.columns]
  

• If client codes are stored inside the sheet (not in the sheet name):
  Extract it directly from the sheet, e.g.:

  client_code = sheet_data.iloc[0, 1]  # Grab value from first row, second column
  

4. Cleanup & Final Touches #

• Handle missing values with final_master_df.fillna(method='ffill') or fillna(0) (adjust based on your data needs)
• Standardize column names with .str.replace() if needed:

  final_master_df.columns = final_master_df.columns.str.replace(" ", "_").str.lower()
  

Example Output #

Your final DataFrame will look like this:

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