如何修复ValueError: Data must be 1-dimensional错误并高效计算DataFrame的Overall Total列

Let's break down what's going wrong with your code and fix it step by step. The core issues are mismatched dimensions between your rate series and date-based calculations, plus incorrect column name handling for the Total columns.

Here's the Corrected Code: #

import pandas as pd

# Your original DataFrame setup
data = [['30-06-2021', 3.4, 43578, '31-01-2022', 5000, '28-02-2022', 78564, '31-03-2022', 52353, '30-04-2022'], ['14-06-2021', 8.9, 4475, '14-01-2022', 2546, '05-02-2022', 5757, '28-03-2022', 2352, '01-04-2022']]
ds = pd.DataFrame(data, columns = ['Start', 'Rate', 'Jan-22Total', 'Jan-22', 'Feb-22Total', 'Feb-22', 'Mar-22Total', 'Mar-22', 'Apr-22Total', 'Apr-22'])

# Step 1: Identify monthly date columns (e.g., Jan-22, Feb-22)
m = pd.to_datetime(ds.columns.str.extract(r'([A-Z][a-z]{2}-\d+\Z)', expand=False), format='%b-%y', errors='coerce').notna()
date_cols = ds.columns[m]

# Step 2: Generate corresponding Total column names (e.g., Jan-22Total, Feb-22Total)
total_cols = [col + 'Total' for col in date_cols]

# Step 3: Convert dates to datetime objects
start_dates = pd.to_datetime(ds['Start'], dayfirst=True)
month_dates = ds[date_cols].apply(pd.to_datetime, dayfirst=True)

# Step 4: Calculate time difference in years (days / 365)
years_diff = (month_dates.sub(start_dates, axis=0).dt.days) / 365

# Step 5: Prepare rate values with correct dimension for broadcasting
rate = ds['Rate'] / 100
rate_factor = (1 + rate)[:, None]  # Convert to 2D array to match years_diff's shape

# Step 6: Compute the denominator term (1+rate)^(years_diff)
denominator = rate_factor.pow(years_diff)

# Step 7: Calculate each term and sum for Overall Total
terms = ds[total_cols].pow(1 / denominator)
ds['Overall Total'] = terms.sum(axis=1)

print(ds['Overall Total'])

Key Fixes & Explanations: #

• Correct Total Column Name Generation:
  Your original cols + 'Total' tried to concatenate an Index object with a string, which doesn't generate the right column names. Instead, we use a list comprehension to explicitly create matching Total column names for each monthly date column.

• Fix Dimension Mismatch for Broadcasting:
  The ValueError came from mismatched dimensions: years.values is a 2D array (rows × monthly columns), while your original rate2 was a 1D Series. By converting rate_factor to a 2D array with [:, None], we let Pandas broadcast each row's rate across all monthly columns correctly.

• Simplify Time Difference Calculation:
  We use .dt.days to extract the number of days from timedeltas, which is more straightforward than the original astype('timedelta64[D]') approach for datetime DataFrames.

• Cleaner Term Calculation:
  We compute 1 / denominator directly and apply it to the Total columns with pow(), then sum across rows to get the final Overall Total value.

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