基于日期列条件拆分DataFrame并聚合数值的技术需求

Hey, let's tackle this DataFrame transformation step by step. Here's a practical implementation that handles both date aggregation (when dates match) and row splitting (when dates differ), plus it automatically skips missing dates like NaT:

import pandas as pd

# Your original data setup
data = [ [101, '1987-09-01', 1, 1, '1987-09-01', 2, 2], 
         [102, '1987-09-01', 1, 1, '1999-09-01', 2, 2], 
         [103, 'nan', 0, 0, '1999-09-01', 2, 2] ]
df = pd.DataFrame(data, columns=['ID', 'Date1', 'x1', 'y1', 'Date2', 'x2', 'y2'])
df['Date1'] = pd.to_datetime(df['Date1'])
df['Date2'] = pd.to_datetime(df['Date2'])

# Step 1: Map date columns to their corresponding x/y pairs
date_columns = [col for col in df.columns if 'Date' in col]
xy_column_pairs = [('x1', 'y1'), ('x2', 'y2')]  # Aligns with date columns order

# Step 2: Process each row to split or aggregate
temp_rows = []
for _, row in df.iterrows():
    date_xy_totals = {}
    # Loop through each date-x/y group
    for date_col, (x_col, y_col) in zip(date_columns, xy_column_pairs):
        current_date = row[date_col]
        # Skip missing dates (NaT)
        if pd.notna(current_date):
            # Initialize entry if date isn't tracked yet
            if current_date not in date_xy_totals:
                date_xy_totals[current_date] = {'x': 0, 'y': 0}
            # Add x/y values to the date's total
            date_xy_totals[current_date]['x'] += row[x_col]
            date_xy_totals[current_date]['y'] += row[y_col]
    # Convert the date totals into individual rows
    for date, totals in date_xy_totals.items():
        temp_rows.append([row['ID'], date, totals['x'], totals['y']])

# Step 3: Build the final DataFrame and format dates as strings (matching your example)
df_desired = pd.DataFrame(temp_rows, columns=['ID', 'Date', 'x', 'y'])
df_desired['Date'] = df_desired['Date'].dt.strftime('%Y-%m-%d')

print(df_desired)

Output: #

ID        Date  x  y
0  101  1987-09-01  3  3
1  102  1987-09-01  1  1
2  102  1999-09-01  2  2
3  103  1999-09-01  2  2

How this works: #

• Pairing Dates with X/Y: We first link each date column to its matching x and y columns—this makes it easy to scale if you add more date columns later (just add the new (xn, yn) pair to xy_column_pairs).
• Row-by-Row Processing: For each row, we use a dictionary to track the sum of x and y values per unique date. If a date repeats across columns, we add the values together; if it's new, we start a new entry.
• Handling Missing Dates: Any NaT (missing) date values are skipped entirely, so they don't contribute to the final output.
• Flexible for N Dates: This code works for any number of date columns—if you have 3 or more, just update the date and xy lists to match, and it'll split/aggregate accordingly.

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