按列分组拼接DataFrame时如何避免新增列

我希望通过以下代码仅对指定行进行拼接操作，尝试修改concat方法的索引和轴参数但未解决问题。

原代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame(({'C1':['a','b','c','a','b','c'], 'C2':[1,2,3,4,5,6], 'C3':[1,2,3,4,5,6]}))
dfNew = pd.DataFrame()
for name, group in df.groupby('C1', sort=False):
    dfNew = pd.concat([dfNew, group, pd.Series(np.nan)])

当前实际输出 #

C1  C2  C3  0
0   a   1.0 1.0 NaN
1   a   4.0 4.0 NaN
0   NaN NaN NaN NaN
0   b   2.0 2.0 NaN
1   b   5.0 5.0 NaN
0   NaN NaN NaN NaN
0   c   3.0 3.0 NaN
1   c   6.0 6.0 NaN
0   NaN NaN NaN NaN

期望输出 #

C1  C2  C3  
0   a   1.0 1.0 
1   a   4.0 4.0 
0   NaN NaN NaN 
0   b   2.0 2.0 
1   b   5.0 5.0 
0   NaN NaN NaN 
0   c   3.0 3.0 
1   c   6.0 6.0 
0   NaN NaN NaN 

问题根源是你拼接的pd.Series(np.nan)会生成新列（输出里的0列），而非和原DataFrame结构匹配的空行。需要创建与原表列名一致的空行数据，再进行拼接。

匹配期望输出索引的修改代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame(({'C1':['a','b','c','a','b','c'], 'C2':[1,2,3,4,5,6], 'C3':[1,2,3,4,5,6]}))
dfNew = pd.DataFrame()
for name, group in df.groupby('C1', sort=False):
    # 创建与原表列名一致的空行，指定索引为0
    empty_row = pd.DataFrame([[np.nan]*len(df.columns)], columns=df.columns, index=[0])
    dfNew = pd.concat([dfNew, group, empty_row])

说明 #

• 用pd.DataFrame生成空行，保证列名和原表完全匹配，避免新增无关列
• 指定空行索引为[0]，和你期望输出的索引格式一致
• 如果不需要重复索引，可在concat中添加ignore_index=True实现连续索引

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