如何在Pandas DataFrame中插入缺失年份行并以前后行平均值填充人口数据

Hey there! Let's tackle this problem of filling in the missing 2012 row in your Fairfax County DataFrame. I've got two solid approaches for you, depending on whether you just need to fix this single missing year or might have more gaps to handle later.

方法一：手动构造缺失行（适合单条缺失） #

This is super straightforward when you only have one missing row to add:

1. First, grab the 2011 and 2013 rows to calculate the average county population. City population is the same in both years, so we can just reuse that value.
2. Build a new DataFrame row with all the required fields: set rank to 7.0, use the calculated average for county_population, and keep city_population as 60300.
3. Combine the new row with your original DataFrame, sort by year, and adjust the index to match your desired output.

代码实现 #

import pandas as pd

# 假设你的原始DataFrame名为'df'
# 提取2011和2013年的数据行
row_2011 = df[df['year'] == 2011.0].iloc[0]
row_2013 = df[df['year'] == 2013.0].iloc[0]

# 计算县人口的平均值
avg_county_pop = (row_2011['county_population'] + row_2013['county_population']) / 2

# 构造2012年的缺失行
new_row = pd.DataFrame({
    'county_name': ['Fairfax County'],
    'state': ['Virginia'],
    'year': [2012.0],
    'rank': [7.0],
    'county_population': [avg_county_pop],
    'city_population': [row_2011['city_population']]
})

# 合并数据、按年份排序并调整索引
df_updated = pd.concat([df, new_row], ignore_index=True)
df_updated = df_updated.sort_values('year').reset_index(drop=True)

# 设置索引从31开始，匹配你期望的输出格式
df_updated.index = range(31, 31 + len(df_updated))

方法二：重采样+线性填充（适合批量缺失年份） #

If you ever need to fill multiple missing years down the line, this method is more scalable:

1. Convert the year column to datetime format and set it as the index.
2. Use resample('Y') to create a row for every year, then interpolate('linear') to automatically fill numerical fields with the average of adjacent years.
3. Manually set the rank value for 2012 to 7.0, then convert the year back to a numeric field and reset the index.

代码实现 #

import pandas as pd

# 假设你的原始DataFrame名为'df'
# 将year列转为datetime格式并设置为索引
df['year'] = pd.to_datetime(df['year'], format='%Y')
df = df.set_index('year')

# 按年重采样，并用线性插值填充数值字段
df_resampled = df.resample('Y').interpolate(method='linear')

# 手动设置2012年的rank值为7.0
df_resampled.loc['2012-12-31', 'rank'] = 7.0

# 将year转回数值格式并重置索引
df_resampled['year'] = df_resampled.index.year
df_resampled = df_resampled.reset_index(drop=True)

# 调整索引以匹配你期望的输出格式
df_resampled.index = range(31, 31 + len(df_resampled))

Both methods will give you the exact DataFrame you're looking for. The first is great for one-off fixes, while the second is better if you anticipate more missing years in your dataset.

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