SAS多行合并为单行求助：转置summary变量生成新变量

Hey there! Let's figure out how to transpose your summary variable into new variables. I'll cover solutions for two widely used data processing tools—R (with tidyr/dplyr) and Python (Pandas)—since you didn't specify which one you're using.

First, let's set a realistic example of your original data #

Let's assume your dataset looks something like this (adjust the structure if your actual data has different separators or formats):

Original dataset sample:

id	summary
1	"A: 5, B: 3, C: 2"
2	"A: 4, B: 1, C: 6"

Solution 1: Using R (tidyr + dplyr) #

We'll split the summary column into key-value pairs, then pivot it into wide format to create your new variables:

# Load required libraries
library(tidyr)
library(dplyr)

# Sample original data (replace with your actual dataset)
df <- tibble(
  id = c(1, 2),
  summary = c("A: 5, B: 3, C: 2", "A: 4, B: 1, C: 6")
)

# Step 1: Split each summary entry into separate rows
# Step 2: Split each row into variable name and its value
# Step 3: Convert values to numeric (optional but recommended)
# Step 4: Pivot to wide format to create new variables
df_processed <- df %>%
  separate_rows(summary, sep = ", ") %>%
  separate(summary, into = c("new_variable", "value"), sep = ": ") %>%
  mutate(value = as.numeric(value)) %>%
  pivot_wider(names_from = new_variable, values_from = value)

# Check the result
print(df_processed)

This will give you a dataset where each unique entry in summary becomes a new column, with the corresponding values matched to each id.

Solution 2: Using Python (Pandas) #

Similar logic—split, reshape, and pivot:

import pandas as pd

# Sample original data (replace with your actual data)
df = pd.DataFrame({
    "id": [1, 2],
    "summary": ["A: 5, B: 3, C: 2", "A: 4, B: 1, C: 6"]
})

# Step 1: Split summary into a list of entries, then explode into separate rows
df_split = df.assign(summary=df['summary'].str.split(', ')).explode('summary')

# Step 2: Split each entry into variable name and value
df_split[['new_variable', 'value']] = df_split['summary'].str.split(': ', expand=True)
df_split['value'] = df_split['value'].astype(int)  # Convert to numeric

# Step 3: Pivot to wide format to create new variables
df_processed = df_split.pivot(index='id', columns='new_variable', values='value').reset_index()

# Clean up the column name index
df_processed.columns.name = None

# Check the result
print(df_processed)

Quick note for custom formats #

If your summary uses different separators (like newlines instead of commas, or colons with extra spaces), just adjust the sep argument in separate_rows (R) or str.split (Python) to match your data.

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