Python数据处理：基于日期对行列值进行复制与均值计算

Got it, let's walk through this problem step by step. First, let's break down what you're working with: you've got a DataFrame with date components (Year/Month/Day) and three subject columns where most entries are empty strings, with a few numerical values stored as text. You need to handle value replication based on dates and calculate mean values. Here's how to approach this:

First, we need to get your data into a format pandas can work with for calculations. We'll turn empty strings into NaN (pandas' standard missing value marker), convert the subject columns to numeric types, and create a proper date column for easier date-based operations.

import pandas as pd
import numpy as np

# Your original dataset
d1 = {'Year': ['2008','2008','2008','2008','2008','2008','2008','2008','2008','2008'], 
      'Month':['1','1','2','6','7','8','8','11','12','12'], 
      'Day':['6','22','6','18','3','10','14','6','16','24'], 
      'Subject_A':['','30','','','','35','','','',''], 
      'Subject_B':['','','','','','','','40','',''], 
      'Subject_C': ['','','','','','65','','50','','']}
d1 = pd.DataFrame(d1)

# Replace empty strings with NaN so pandas recognizes missing values
d1.replace('', np.nan, inplace=True)

# Convert subject columns to numeric types (required for calculations)
subject_cols = ['Subject_A', 'Subject_B', 'Subject_C']
d1[subject_cols] = d1[subject_cols].apply(pd.to_numeric)

# Create a single Date column from Year/Month/Day for easier date logic
d1['Date'] = pd.to_datetime(d1[['Year', 'Month', 'Day']])

I'm assuming you want to fill in those missing values using date-related logic—two common, practical approaches here are filling with period averages (like monthly means) or propagating existing values to adjacent dates. Let's cover both:

Option 1: Fill missing values with monthly means #

If you expect values to be consistent within a month, replace missing entries with the average value of that subject for the same month/year:

# Calculate the mean for each subject per month/year
monthly_subject_means = d1.groupby(['Year', 'Month'])[subject_cols].transform('mean')

# Fill the NaNs with these precomputed monthly means
d1_filled_with_monthly_means = d1.copy()
d1_filled_with_monthly_means[subject_cols] = d1_filled_with_monthly_means[subject_cols].fillna(monthly_subject_means)

Option 2: Propagate existing values to adjacent dates #

If values change gradually over time, you can "carry forward" the last known value (or fill backward from the next known value) along the sorted date timeline:

# First, sort the DataFrame by date to ensure proper value propagation
d1_sorted = d1.sort_values('Date')

# Forward-fill: use the last available value for missing entries
d1_forward_filled = d1_sorted.copy()
d1_forward_filled[subject_cols] = d1_forward_filled[subject_cols].ffill()

# Backward-fill: use the next available value for missing entries
d1_backward_filled = d1_sorted.copy()
d1_backward_filled[subject_cols] = d1_backward_filled[subject_cols].bfill()

Once your data is cleaned (or filled), calculating means is straightforward. Here are a few common scenarios:

Overall mean for each subject #

Get the average value across all dates for each subject:

overall_subject_means = d1[subject_cols].mean()
print("Overall Subject Means:\n", overall_subject_means)

Monthly mean for each subject #

Get the average value per month/year for each subject (this is the same calculation we used for filling missing values, but formatted as a summary):

monthly_means_summary = d1.groupby(['Year', 'Month'])[subject_cols].mean()
print("Monthly Subject Means:\n", monthly_means_summary)

• The data cleaning step is non-negotiable—pandas can't calculate means on empty strings, so converting those to NaN and switching to numeric types is essential.
• Pick the value replication method that fits your use case: monthly means work well if values are stable within a month, while forward/backward filling is better for trends that change over time.

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