在RStudio中将含双因子的复杂长格式DataFrame转换为宽格式

Hey there! I totally get how frustrating it can be to spend hours stuck on data reshaping—let's fix this for you. You want to collapse your dataframe so each CLIENT_CODE is a single row, with each TIME value expanding into its own set of DATE and question columns. Here are step-by-step solutions in both R and Python, since those are the most common tools for this task:

Solution 1: Using R (tidyverse) #

The tidyr package's pivot_wider() function is perfect for this job. It lets you easily pivot long data into wide format with custom column names.

# Load the tidyverse package (includes tidyr and dplyr)
library(tidyverse)

# Assume your original dataframe is named 'df'
wide_df <- df %>%
  # Create a suffix for each TIME value (e.g., "_1" for TIME=1)
  mutate(time_suffix = paste0("_", TIME)) %>%
  # Pivot to wide format:
  # - Keep CLIENT_CODE as the row identifier
  # - Use time_suffix to name the columns for each TIME group
  # - Include DATE and all question fields as values to pivot
  pivot_wider(
    id_cols = CLIENT_CODE,
    names_from = time_suffix,
    values_from = c(DATE, starts_with("question"))  # Auto-select all question columns
  )

# View the final wide dataframe
print(wide_df)

What this does: #

• Automatically generates column names like DATE_1, DATE_2, question1_1, question1_2 (matching your desired format)
• Handles NA values just like in your example (e.g., CC1234's question1 for TIME=1 stays as NA)
• The starts_with("question") lets you include all question columns without typing each one individually—super handy if you have lots of them!

Solution 2: Using Python (pandas) #

Pandas has powerful pivot functionality to achieve the same result. We'll pivot the data first, then clean up the column names to match your target format.

import pandas as pd

# Assume your original dataframe is named 'df'
# Pivot the data: CLIENT_CODE as rows, TIME as columns, with DATE and questions as values
pivoted_df = df.pivot(
    index="CLIENT_CODE",
    columns="TIME",
    values=["DATE"] + [col for col in df.columns if col.startswith("question")]
)

# Rearrange column levels so the TIME suffix comes last, then sort columns
pivoted_df = pivoted_df.swaplevel(0, 1, axis=1).sort_index(axis=1)

# Rename columns to match your desired format (e.g., question1_1 → q1_1, DATE_1 stays)
pivoted_df.columns = [
    f"{col[0].replace('question', 'q')}_{col[1]}" 
    for col in pivoted_df.columns
]

# Reset index to move CLIENT_CODE back to a regular column
wide_df = pivoted_df.reset_index()

# Print the result
print(wide_df)

What this does: #

• Creates the same wide structure as the R solution
• Renames question columns to the shorter q1_1 format you showed in your example
• Preserves NA values and keeps each client's data in a single row

Optional: Filter specific TIME values #

If you only want to include certain TIME values (like 1 and 2, as in your target example), just add a filter step before pivoting:

• In R: Add filter(TIME %in% c(1, 2)) right after the df %>%
• In Python: Add df = df[df["TIME"].isin([1, 2])] before pivoting

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