宽表转长表：如何拆分DataFrame每行生成3条对应记录？

Got it, let's break down how to split each row into 3 rows while keeping your core identifiers (SEDOL, ISIN, INSTRUMENT) paired with their respective date and price sets. We'll use pandas' built-in functions to do this cleanly.

First, let's recap your original DataFrame for reference:

import pandas as pd

df = pd.DataFrame(
    [[2, 4, 'ABC', '01Jan2017', '01Jul2016', '01Jul2017', 0.5, 0.3, 0.7],
     [9, 2, 'DEF', '01Jan2017', '01Jul2016', '01Jul2017', 0.5, 0.3, 0.7]],
    columns=['SEDOL', 'ISIN', 'INSTRUMENT', 'DT_TRANSACTED', 'DT_SIX_MTHS_BEF', 
             'DT_SIX_MTHS_AFT', 'PX_TRANSACTED', 'PX_SIX_MONTHS_BEF', 'PX_SIX_MONTHS_AFT']
)

Method 1: Use pd.wide_to_long (Cleanest for Structured Columns) #

This function is designed exactly for converting wide-format DataFrames with repeated prefixes (like DT_ and PX_) into long format. Here's how to use it:

# First, standardize column name suffixes to match (aligns price column with date column naming)
df_renamed = df.rename(columns={
    'PX_SIX_MONTHS_BEF': 'PX_SIX_MTHS_BEF'
})

# Reshape the DataFrame
reshaped_df = pd.wide_to_long(
    df_renamed,
    stubnames=['DT', 'PX'],  # Prefixes of our date/price columns
    i=['SEDOL', 'ISIN', 'INSTRUMENT'],  # Columns to keep as identifiers
    j='PERIOD',  # New column to label the date/price group
    sep='_',  # Separator between prefix and suffix in column names
    suffix='.+',  # Regex to match any suffix after the separator
).reset_index()

Result Explanation #

The output reshaped_df will have 6 rows (2 original rows × 3 date-price pairs) with these columns:

• SEDOL, ISIN, INSTRUMENT: Your original identifiers, preserved for each row
• PERIOD: Labels like TRANSACTED, SIX_MTHS_BEF, SIX_MTHS_AFT to indicate which date/price set the row represents
• DT: The corresponding date value
• PX: The corresponding price value

Method 2: Use pd.melt (More Flexible for Custom Cases) #

If you prefer a more explicit approach, you can melt the date and price columns separately, then merge them back together:

# Melt date columns into long format
date_long = df.melt(
    id_vars=['SEDOL', 'ISIN', 'INSTRUMENT'],
    value_vars=['DT_TRANSACTED', 'DT_SIX_MTHS_BEF', 'DT_SIX_MTHS_AFT'],
    var_name='PERIOD',
    value_name='DT'
)

# Melt price columns into long format
price_long = df.melt(
    id_vars=['SEDOL', 'ISIN', 'INSTRUMENT'],
    value_vars=['PX_TRANSACTED', 'PX_SIX_MONTHS_BEF', 'PX_SIX_MONTHS_AFT'],
    var_name='PERIOD',
    value_name='PX'
)

# Clean up the PERIOD column by removing the DT_/PX_ prefixes
date_long['PERIOD'] = date_long['PERIOD'].str.replace('DT_', '')
price_long['PERIOD'] = price_long['PERIOD'].str.replace('PX_', '')

# Merge the two long DataFrames to pair dates with their prices
reshaped_df = pd.merge(date_long, price_long, on=['SEDOL', 'ISIN', 'INSTRUMENT', 'PERIOD'])

This gives you the exact same result as the first method, but lets you customize each step if you need to adjust formatting or add transformations along the way.

Bonus: Convert Date Column to Datetime #

While not part of your original question, it's a good practice to convert the DT column to datetime format for future analysis:

reshaped_df['DT'] = pd.to_datetime(reshaped_df['DT'], format='%d%b%Y')

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