基于DataFrame同年份a、b行求和生成id为ab的新行

我有如下DataFrame，需新增2000-2002年的3条行：

• 新行id为ab
• val列取同年份下id为a和b的val值之和
• 其余列（如othernulcol）设为np.nan

原DataFrame生成代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'year':[2000,2001,2002]*3,
    'id':['a']*3+['b']*3+['c']*3,
    'othernulcol': ['xyz']*3+[np.nan]*4+['tyu']*2,
    'val':[np.nan,2,3,4,5,6,7,8,9]
})

原数据结构 #

year id othernulcol  val
0  2000  a         xyz  NaN
1  2001  a         xyz  2.0
2  2002  a         xyz  3.0
3  2000  b         NaN  4.0
4  2001  b         NaN  5.0
5  2002  b         NaN  6.0
6  2000  c         NaN  7.0
7  2001  c         tyu  8.0
8  2002  c         tyu  9.0

实现代码 #

# 筛选id为a和b的数据，按年份分组求和val
ab_data = df[df['id'].isin(['a', 'b'])].groupby('year')['val'].sum().reset_index()
# 设置新行的id和其他列值
ab_data['id'] = 'ab'
ab_data['othernulcol'] = np.nan
# 合并原数据与新生成的ab行
result_df = pd.concat([df, ab_data], ignore_index=True)

预期输出 #

year  id othernulcol   val
0   2000   a         xyz   NaN
1   2001   a         xyz   2.0
2   2002   a         xyz   3.0
3   2000   b         NaN   4.0
4   2001   b         NaN   5.0
5   2002   b         NaN   6.0
6   2000   c         NaN   7.0
7   2001   c         tyu   8.0
8   2002   c         tyu   9.0
9   2000  ab         NaN   NaN
10  2001  ab         NaN  10.0
11  2002  ab         NaN  12.0

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