基于年份区间对Pandas DataFrame行求平均值的技术实现

To calculate the yearly average fuel consumption based on each car's active year range, follow these straightforward steps:

Step 1: Expand each car's year range into individual rows #

First, we generate a list of all years between the start and end year (inclusive) for each car, then "explode" this list into separate rows—one for every year the car was in circulation.

import pandas as pd

# Ensure year columns are integer types (skip if already formatted correctly)
df_cars['Start year'] = df_cars['Start year'].astype(int)
df_cars['End year'] = df_cars['End year'].astype(int)

# Create a list of years for each car's active period
df_cars['Year'] = df_cars.apply(lambda row: list(range(row['Start year'], row['End year'] + 1)), axis=1)

# Explode the Year column to get one row per year per car
df_expanded = df_cars.explode('Year').reset_index(drop=True)

Step 2: Calculate yearly average fuel consumption #

Group the expanded DataFrame by the Year column and compute the mean of the Average fuel consumption values to get the yearly average.

# Compute average fuel consumption per year
yearly_avg_fuel = df_expanded.groupby('Year')['Average fuel consumption'].mean().reset_index()

# Rename column for clearer readability (optional)
yearly_avg_fuel.rename(columns={'Average fuel consumption': 'Yearly Average Fuel Consumption'}, inplace=True)

Example Output #

Using your sample data plus an extra car for demonstration:

Original df_cars:

The expanded DataFrame will have rows like:

The final yearly_avg_fuel will look like:

Optimization for Large Datasets #

If you're working with tens of thousands of rows, using numpy.arange instead of list(range) can speed up year list generation by leveraging vectorized operations:

import numpy as np

df_cars['Year'] = df_cars.apply(lambda row: np.arange(row['Start year'], row['End year'] +1).tolist(), axis=1)

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