在Pandas中补全缺失日期并剔除周末数据的方法

Hey Jason, let's walk through solving your problem using Python's pandas library—it's ideal for handling time series tasks like this. Here's a step-by-step breakdown with code examples:

1. Import Required Libraries & Load Your Initial Data #

First, we'll set up your dataset correctly, making sure dates are parsed properly for the DD/MM/YY format:

import pandas as pd

# Your original dataset entries
raw_data = [
    ("31/03/14", -0.0123),
    ("30/04/14", 0.11168),
    ("30/06/14", 0.0997),
    ("31/07/14", 0.007),
    ("30/09/14", 0.886)
]

# Convert to a DataFrame and parse dates with day-first format
df = pd.DataFrame(raw_data, columns=["date", "value"])
df["date"] = pd.to_datetime(df["date"], dayfirst=True)

2. Generate the Full Daily Date Sequence #

We'll create a continuous date range from the start of your first month (2014-03-01) to the end of your last month (2014-09-30):

# Create a full daily date range
full_dates = pd.date_range(start="2014-03-01", end="2014-09-30", freq="D")

# Turn the range into a DataFrame to merge with your data
full_dates_df = pd.DataFrame(full_dates, columns=["date"])

3. Merge to Fill Missing Dates #

Combine your original data with the full date range to get every date in the period. Missing values will show as NaN by default (you can fill them with 0 or another value if needed):

# Merge the two datasets to fill in all dates
filled_df = pd.merge(full_dates_df, df, on="date", how="left")

# Optional: Uncomment below to fill missing values with 0 instead of NaN
# filled_df["value"] = filled_df["value"].fillna(0)

4. Filter Out Saturday and Sunday Entries #

Pandas makes it straightforward to exclude weekends using the weekday property (0 = Monday, 4 = Friday; 5 = Saturday, 6 = Sunday):

# Keep only weekdays (filter out Saturday and Sunday)
workday_only_df = filled_df[filled_df["date"].dt.weekday <= 4]

# Alternative: Use isoweekday (1 = Monday, 5 = Friday) for the same result
# workday_only_df = filled_df[filled_df["date"].dt.isoweekday <= 5]

Final Outcome #

The workday_only_df DataFrame now contains every weekday from 1/3/14 to 30/09/14, with your original values preserved and missing dates showing either NaN or your chosen fill value.

If you're working with a different tool (like Excel or R), feel free to ask and I can adapt the solution to fit that environment!

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