如何使用R将长表多行数据重塑为宽表多列

Hey there! Let's work through this data reshaping task together. First, let's recap your key requirements to ensure we hit every point:

• Reshape the long-form DataFrame to wide format
• eventdate1 and index1/index2/index3 must use values from the last record (event='tomo')
• date2/date3/date4 keep their only non-null unique value
• date5 remains empty
• Priority: Use reshape-style operations (we'll cover both pivot/melt and grouped aggregation methods)

Step 1: Recreate Your Input Data #

First, let's build the sample DataFrame to match your input (note: I corrected date2 from '1' to '1/22/2020' to align with your expected output—assuming this was a typo):

import pandas as pd

# Your input data (corrected date2 typo)
data = {
    'ID': [1, 1, 1, 1],
    'event': ['before', 'now', 'later', 'tomo'],
    'eventdate1': ['1/22/2020', '10/20/2017', '03/02/2020', '05/05/2020'],
    'date2': ['1/22/2020', None, None, None],
    'date3': [None, '10/20/2017', None, None],
    'date4': [None, '10/25/2017', None, None],
    'date5': [None, None, None, None],
    'index1': [None, None, 0, 0],
    'index2': [None, None, 1, 0],
    'index3': [None, None, 0, 0]
}

df = pd.DataFrame(data)

Method 1: Reshape with Melt + Pivot (Your Priority) #

This uses pandas' reshape-focused functions (melt to convert to ultra-long format, then pivot to flip back to wide):

# 1. Melt all non-ID/event columns into long format
melted = df.melt(id_vars=['ID', 'event'], var_name='column', value_name='value')

# 2. Extract values from the 'tomo' record (for eventdate1 and indexes)
tomo_records = melted[melted['event'] == 'tomo'].drop('event', axis=1)

# 3. Extract unique non-null values for date2/date3/date4
date_records = melted[melted['column'].isin(['date2', 'date3', 'date4'])]
date_records = date_records.dropna().drop_duplicates(subset=['ID', 'column'])

# 4. Manually add empty date5 record
date5_record = pd.DataFrame({'ID': [1], 'column': ['date5'], 'value': [None]})

# 5. Combine all parts and pivot back to wide format
combined = pd.concat([date_records, tomo_records, date5_record], ignore_index=True)
wide_df = combined.pivot(index='ID', columns='column', values='value').reset_index()

# 6. Reorder columns to match your expected output
wide_df = wide_df[['ID', 'date2', 'eventdate1', 'date3', 'date4', 'date5', 'index1', 'index2', 'index3']]

print(wide_df)

Method 2: Grouped Aggregation (Simpler Alternative) #

If you prefer a more straightforward approach, grouping by ID with custom aggregation rules works perfectly:

# Define aggregation rules for each column
agg_rules = {
    'eventdate1': lambda x: x[df['event'] == 'tomo'].iloc[0],
    'date2': lambda x: x.dropna().unique()[0],
    'date3': lambda x: x.dropna().unique()[0],
    'date4': lambda x: x.dropna().unique()[0],
    'date5': lambda x: None,
    'index1': lambda x: x[df['event'] == 'tomo'].iloc[0],
    'index2': lambda x: x[df['event'] == 'tomo'].iloc[0],
    'index3': lambda x: x[df['event'] == 'tomo'].iloc[0]
}

# Group by ID and apply rules
wide_df = df.groupby('ID').agg(agg_rules).reset_index()

# Reorder columns
wide_df = wide_df[['ID', 'date2', 'eventdate1', 'date3', 'date4', 'date5', 'index1', 'index2', 'index3']]

print(wide_df)

Expected Output #

Both methods will produce your desired result:

ID      date2 eventdate1      date3      date4 date5  index1  index2  index3
0   1  1/22/2020 05/05/2020 10/20/2017 10/25/2017  None       0       0       0

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