如何高效填充Pandas DataFrame中缺失年份的number值为0？

First, let's recap your problem with the sample data:

import pandas as pd

df = pd.DataFrame([ 
    ['A', 2017, 1], ['A', 2019, 1], 
    ['B', 2017, 1], ['B', 2018, 1], 
    ['C', 2016, 1], ['C', 2019, 1]
], columns=['ID', 'year', 'number'])

Your current loop-based approach works, but since you're dealing with a large DataFrame, we can optimize this by leaning into Pandas' vectorized operations (implemented in C, way faster than Python-level loops). Here are two more efficient solutions:

Solution 1: Use unstack() + stack() (Most Efficient) #

This method uses Pandas' built-in vectorized tools—no Python loops at all, making it ideal for big datasets:

# Set multi-index, pivot years into columns (fill gaps with 0), then convert back to long format
df_full = (
    df.set_index(['ID', 'year'])['number']
      .unstack(fill_value=0)
      .stack()
      .reset_index(name='number')
)

Why this beats your original code: #

• All operations run on Pandas' optimized C backend, which is drastically faster than Python loops for large volumes of data.
• The syntax is concise and readable, cutting out manual index construction steps.

Solution 2: Generate Full MultiIndex with from_tuples #

If you want explicit control over year ranges per ID, this approach still avoids inefficient list appends:

# Calculate min and max year for each ID
id_year_bounds = df.groupby('ID')['year'].agg(['min', 'max'])

# Build a complete MultiIndex of (ID, year) pairs
full_index = pd.MultiIndex.from_tuples(
    [(id_, year) 
     for id_, (min_year, max_year) in id_year_bounds.iterrows() 
     for year in range(min_year, max_year + 1)],
    names=['ID', 'year']
)

# Reindex the original DataFrame to fill missing values with 0
df_full = df.set_index(['ID', 'year']).reindex(full_index, fill_value=0).reset_index()

Why this is better than your original approach: #

• We skip manual list appends (which have high memory overhead for large datasets) by constructing the MultiIndex directly.
• The loop here is streamlined and leverages Pandas' efficient index handling under the hood.

Both solutions will produce your desired output:

ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

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