DataFrame按年份拆分多日期行并均分Price列的技术需求

Hey there! Let's tackle this problem step by step using pandas. The goal is to split rows whenever the Date column contains dates from different years, keep same-year dates grouped together in a single row, and divide the original Price evenly across each of these new rows. Here's a straightforward solution:

First, let's recreate your original dataset #

We'll start by building the sample DataFrame you provided so we can test our code:

import pandas as pd

# Original dataset
data = {
    'Price': [500, 400, 1000, 25, 304, 238],
    'Date': [
        '2016-01-01',
        '2016-01-03;2016-01-09',
        '2016-01-04;2017-09-01;2017-08-10;2018-01-01',
        '2016-01-04;2017-09-01',
        '2015-01-02',
        '2018-01-02;2018-02-02'
    ]
}
df = pd.DataFrame(data)

Step 1: Define a helper function to process each row #

This function will handle splitting dates into year groups, calculating the split price, and returning the formatted rows for each group:

def process_single_row(row):
    # Split the date string into individual dates
    individual_dates = row['Date'].split(';')
    
    # Group dates by their year (extract the first 4 characters of each date)
    year_to_dates = {}
    for date in individual_dates:
        year = date[:4]
        if year not in year_to_dates:
            year_to_dates[year] = []
        year_to_dates[year].append(date)
    
    # Convert each year's date list back to a semicolon-separated string
    grouped_date_strings = [';'.join(dates) for dates in year_to_dates.values()]
    
    # Calculate the price per group (original price divided by number of year groups)
    price_per_group = row['Price'] / len(grouped_date_strings)
    
    # Return a list of tuples (formatted price, grouped dates)
    # We'll round to 2 decimal places to match your target output
    return [(round(price_per_group, 2), date_group) for date_group in grouped_date_strings]

Step 2: Apply the function and reshape the DataFrame #

We'll apply our helper function to every row, then expand the resulting lists into separate rows:

# Apply the function to each row (returns a list of tuples per row)
processed_rows = df.apply(process_single_row, axis=1)

# Explode the lists into individual rows
exploded = processed_rows.explode()

# Convert the tuples back into a proper DataFrame
target_df = pd.DataFrame(exploded.tolist(), columns=['Price', 'Date'])

Step 3: Check the final result #

If you print target_df, you'll get exactly the output you're looking for:

print(target_df)

Output:

Price                     Date
0   500.00              2016-01-01
1   400.00  2016-01-03;2016-01-09
2   333.33              2016-01-04
3   333.33  2017-09-01;2017-08-10
4   333.33              2018-01-01
5    12.50              2016-01-04
6    12.50              2017-09-01
7   304.00              2015-01-02
8   238.00  2018-01-02;2018-02-02

Quick breakdown of what's happening: #

• Grouping by year: We split each date string, extract the year from each date, and group all dates from the same year together.
• Splitting the price: The original price is divided by how many unique year groups exist in the row (e.g., the row with 4 dates across 3 years gets split into 3 equal price parts).
• Expanding rows: The explode() function takes the list of results from each row and turns them into separate rows in the final DataFrame.

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