基于Pandas实现相对时间透视：将客户个性化近8月数据转宽表

Got it, let's work through this problem to turn your row-based monthly data into a wide table where each customer has columns for their last 8 months of Amount and Penalties, labeled with relative months like Month-8 (oldest) to Month-1 (most recent). We'll use Python's pandas library since it's perfect for this kind of data reshaping.

Step 1: Prepare and Clean the Data #

First, make sure your Month column is properly formatted as datetime, then group by each customer to isolate their last 8 months of records.

import pandas as pd
import numpy as np

# Example input data (replace with your actual dataset)
df = pd.DataFrame({
    'Customer': ['123']*8 + ['456']*8,
    'Amount': np.random.randint(100, 500, 16),
    'Penalties': np.random.randint(0, 100, 16),
    'Month': pd.date_range(start='2017-01-01', periods=8, freq='MS').tolist()*2
})

# Convert Month to datetime (critical for sorting)
df['Month'] = pd.to_datetime(df['Month'])

# Sort by customer and month, then keep only the last 8 months per customer
customer_last_8 = df.sort_values(['Customer', 'Month']).groupby('Customer').tail(8)

Step 2: Add Relative Month Labels #

We need to label each record with its relative position (e.g., the most recent month is Month-1, the one before that Month-2, ..., the oldest in the 8-month window is Month-8):

# Assign relative month ranks (descending so newest gets rank 1)
customer_last_8['Relative_Month'] = customer_last_8.groupby('Customer')['Month'] \
    .rank(ascending=False, method='dense') \
    .apply(lambda x: f'Month-{int(x)}')

Step 3: Reshape to Wide Format #

Use pandas' pivot function to turn rows into columns, combining Amount/Penalties with their relative month labels:

# Pivot the data to wide format
wide_table = customer_last_8.pivot(
    index='Customer',
    columns='Relative_Month',
    values=['Amount', 'Penalties']
)

# Flatten the multi-level column names into something readable
wide_table.columns = [f'{metric}_{month}' for metric, month in wide_table.columns]

# Optional: Reset index if you want Customer as a regular column instead of the index
wide_table = wide_table.reset_index()

Example Output #

Your final table will look like this (values will vary based on your data):

Notes for Edge Cases #

• If some customers have fewer than 8 months of data, tail(8) will keep all their available records, and missing relative month columns will show as NaN. You can fill these with 0 using wide_table.fillna(0, inplace=True) if needed.
• Ensure your Month values are unique per customer (no duplicate months for the same customer) to avoid pivot errors. If duplicates exist, you may need to aggregate first (e.g., sum Amount/Penalties for duplicate months).

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