如何使用Pandas按日期范围展开数据并生成每日新列？

Here's a straightforward, efficient way to expand your date ranges into individual daily rows while keeping all original columns intact:

Full Solution Code #

import pandas as pd

# Sample input DataFrame (match your actual data structure)
data = {
    'Start': ['1/1/2010', '2/1/2010'],
    'End': ['1/4/2010', '2/3/2010'],
    'Category': ['A', 'B']
}
df = pd.DataFrame(data)

# Convert date columns to datetime objects (critical for range generation)
df['Start'] = pd.to_datetime(df['Start'])
df['End'] = pd.to_datetime(df['End'])

# Create a column with the full daily date range for each row
df['Date'] = df.apply(lambda row: pd.date_range(start=row['Start'], end=row['End']), axis=1)

# Explode the date range list into separate rows
expanded_df = df.explode('Date')

# Optional: Convert Date back to string format matching your input
# Adjust the format string based on your needs:
# - %m/%d/%Y = 01/01/2010 (with leading zeros)
# - %-m/%-d/%Y = 1/1/2010 (no leading zeros, Unix/macOS)
# - %#m/%#d/%Y = 1/1/2010 (no leading zeros, Windows)
expanded_df['Date'] = expanded_df['Date'].dt.strftime('%-m/%-d/%Y')

# Clean up the index (optional but recommended)
expanded_df = expanded_df.reset_index(drop=True)

print(expanded_df)

Output #

Start        End Category      Date
0 2010-01-01 2010-01-04        A  1/1/2010
1 2010-01-01 2010-01-04        A  1/2/2010
2 2010-01-01 2010-01-04        A  1/3/2010
3 2010-01-01 2010-01-04        A  1/4/2010
4 2010-02-01 2010-02-03        B  2/1/2010
5 2010-02-01 2010-02-03        B  2/2/2010
6 2010-02-01 2010-02-03        B  2/3/2010

Key Breakdown: #

1. Date Conversion: We first turn the Start and End string columns into datetime objects. This is essential because we can't generate date ranges from plain text.
2. Generate Date Ranges: Using apply with pd.date_range, we create a new Date column that holds a list of every date between Start and End (inclusive) for each row.
3. Explode Rows: The explode method takes each date in the list and turns it into its own row, while preserving the original Start, End, and Category values for each expanded entry.
4. Formatting (Optional): If you need the Date column to match your input's string format (instead of datetime objects), use dt.strftime with the appropriate format string.

Pro Tips: #

• If your input dates have a non-standard format, specify it in pd.to_datetime (e.g., pd.to_datetime(df['Start'], format='%d/%m/%Y')) to avoid parsing errors.
• For very large datasets, apply might be slow. For better performance, you can use pd.MultiIndex.from_product, but the apply method is simpler for most use cases.

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