如何合并元组与DataFrame数据并实现特定分组及Group_Id生成
2026-7-29
问题：处理DataFrame分组并合并空sGroup行
我正在学习Python，以下问题可能较为基础，若需更多信息请告知。
原始DataFrame
ID Model MVersion dId sGroup eName eValue 0 1 Main V15 40 GROUP 1 dNumber U220059090(C) 1 2 Main V15 40 GROUP 1 tDate 44901 2 3 Main V15 40 GROUP 2 dNumber U220059090(C) 3 4 Main V15 40 GROUP 2 tDate 44901 4 5 Main V15 40 None sCompany bp 5 6 Main V15 42 GROUP 1 dNumber U220059090(C) 6 7 Main V15 42 GROUP 1 tDate 44901 7 8 Main V15 42 GROUP 2 dNumber U220059090(C) 8 9 Main V15 42 GROUP 2 tDate 44901 9 10 Main V15 42 None sCompany bp 10 11 Main V15 44 None Sender sDummy 11 12 Main V15 44 None TradeDate Tdummy 12 13 Main V15 44 None Product Pdummy 13 14 Main V15 44 None seller seDummy
需求
- 将
sGroup为None的行并入对应dId的每个非None的sGroup分组中； - 若某个
dId的sGroup全为None，则单独成组； - 为每个分组添加递增的
Group_Id列。
预期结果示例
ID Model MVersion dId sGroup eName eValue Group_Id 0 1 Main V15 40 GROUP 1 dNumber U220059090(C) 1 1 2 Main V15 40 GROUP 1 tDate 44901 1 4 5 Main V15 40 None sCompany bp 1 ID Model MVersion dId sGroup eName eValue Group_Id 2 3 Main V15 40 GROUP 2 dNumber U220059090(C) 2 3 4 Main V15 40 GROUP 2 tDate 44901 2 4 5 Main V15 40 None sCompany bp 2 ID Model MVersion dId sGroup eName eValue Group_Id 5 6 Main V15 42 GROUP 1 dNumber U220059090(C) 3 6 7 Main V15 42 GROUP 1 tDate 44901 3 9 10 Main V15 42 None sCompany bp 3 ID Model MVersion dId sGroup eName eValue Group_Id 7 8 Main V15 42 GROUP 2 dNumber U220059090(C) 4 8 9 Main V15 42 GROUP 2 tDate 44901 4 9 10 Main V15 42 None sCompany bp 4 ID Model MVersion dId sGroup eName eValue Group_Id 10 11 Main V15 44 None Sender sDummy 5 11 12 Main V15 44 None TradeDate Tdummy 5 12 13 Main V15 44 None Product Pdummy 5 13 14 Main V15 44 None seller seDummy 5
我的尝试代码
import pandas as pd import numpy as np data = [ [1,'Main','V15', 40,'GROUP 1','dNumber','U220059090(C)'], [2,'Main','V15', 40,'GROUP 1','tDate','44901'], [3,'Main','V15', 40,'GROUP 2','dNumber','U220059090(C)'], [4,'Main','V15', 40,'GROUP 2','tDate','44901'], [5,'Main','V15', 40,None, 'sCompany','bp'], [6,'Main','V15', 42,'GROUP 1','dNumber','U220059090(C)'], [7,'Main','V15', 42,'GROUP 1','tDate','44901'], [8,'Main','V15', 42,'GROUP 2','dNumber','U220059090(C)'], [9,'Main','V15', 42,'GROUP 2','tDate','44901'], [10,'Main','V15', 42,None,'sCompany','bp'], [11,'Main','V15', 44,None,'Sender','sDummy'], [12,'Main','V15', 44,None,'TradeDate','Tdummy'], [13,'Main','V15', 44,None,'Product','Pdummy'], [14,'Main','V15', 44,None,'seller','seDummy'], [15,'Delivery','V15', 40,None,'delIncoTerm','FIP'], [16,'Delivery','V15', 40,None,'delWindow','44562'], ] df = pd.DataFrame(data, columns=['ID','Model','MVersion','dId','sGroup','eName','eValue']) print(df) print('\n') nullSectionGroup = df[df['sGroup'].isnull()] print('null sGroup') print('----------------') print(nullSectionGroup) print('\n') grpModel = df.groupby('Model') # 1) group by Model for model in grpModel: grpModelVersion = model[1].groupby('MVersion') # 2) group by MVersion for modelVersion in grpModelVersion: grpDocId = modelVersion[1].groupby('dId') # 3) group by dId for docId in grpDocId: #print('docId', docId) grpSG = docId[1].groupby('sGroup') # 4) group by sGroup for x in grpSG: #variable declarition model = x[1].Model.iloc[0] modelVersion = x[1].MVersion.iloc[0] docId = x[1].dId.iloc[0] sectionGroup = x[1].sGroup.iloc[0] #filtering dataframe of null section group based on x[1] values #print('****model :', model, '**mVersion :', mVersion, '**Doc_Id :', dId, '**sGroup :', sGroup) filtered_value = nullSectionGroup.loc[(nullSectionGroup['Model']==model)&(nullSectionGroup['MVersion']==modelVersion)&(nullSectionGroup['dId']==docId)] print('filtered_value => pandas.core.frame.DataFrame') print(filtered_value) print('grouped values => tuple') print(x) print('\n')
解决方案
可以通过以下步骤高效实现需求：
- 拆分原始数据为非空sGroup行和空sGroup行两个DataFrame；
- 按
Model、MVersion、dId三层分组，对每个子组判断是否存在非空sGroup：- 存在非空sGroup：为每个非空sGroup生成单独分组，并将同组的空sGroup行复制后并入；
- 不存在非空sGroup：直接将该组作为单独分组；
- 为所有分组分配递增的
Group_Id并合并为最终结果。
完整代码
import pandas as pd # 构造数据 data = [ [1,'Main','V15', 40,'GROUP 1','dNumber','U220059090(C)'], [2,'Main','V15', 40,'GROUP 1','tDate','44901'], [3,'Main','V15', 40,'GROUP 2','dNumber','U220059090(C)'], [4,'Main','V15', 40,'GROUP 2','tDate','44901'], [5,'Main','V15', 40,None, 'sCompany','bp'], [6,'Main','V15', 42,'GROUP 1','dNumber','U220059090(C)'], [7,'Main','V15', 42,'GROUP 1','tDate','44901'], [8,'Main','V15', 42,'GROUP 2','dNumber','U220059090(C)'], [9,'Main','V15', 42,'GROUP 2','tDate','44901'], [10,'Main','V15', 42,None,'sCompany','bp'], [11,'Main','V15', 44,None,'Sender','sDummy'], [12,'Main','V15', 44,None,'TradeDate','Tdummy'], [13,'Main','V15', 44,None,'Product','Pdummy'], [14,'Main','V15', 44,None,'seller','seDummy'], [15,'Delivery','V15', 40,None,'delIncoTerm','FIP'], [16,'Delivery','V15', 40,None,'delWindow','44562'], ] df = pd.DataFrame(data, columns=['ID','Model','MVersion','dId','sGroup','eName','eValue']) # 拆分非空和空sGroup数据 non_null_sg = df[df['sGroup'].notnull()] null_sg = df[df['sGroup'].isnull()] # 存储最终分组结果的列表 result_groups = [] # 按三层维度分组处理 for (model, mversion, did), group in df.groupby(['Model', 'MVersion', 'dId']): # 获取当前组的非空sGroup集合 unique_sg = non_null_sg[(non_null_sg['Model'] == model) & (non_null_sg['MVersion'] == mversion) & (non_null_sg['dId'] == did)]['sGroup'].unique() if len(unique_sg) > 0: # 存在非空sGroup，为每个sGroup生成分组 for sg in unique_sg: # 提取当前sGroup的非空行 sg_subgroup = non_null_sg[(non_null_sg['Model'] == model) & (non_null_sg['MVersion'] == mversion) & (non_null_sg['dId'] == did) & (non_null_sg['sGroup'] == sg)] # 提取当前组的空sGroup行 current_null_rows = null_sg[(null_sg['Model'] == model) & (null_sg['MVersion'] == mversion) & (null_sg['dId'] == did)] # 合并并加入结果列表 combined = pd.concat([sg_subgroup, current_null_rows], ignore_index=False) result_groups.append(combined) else: # 全为空sGroup，直接加入结果 result_groups.append(group) # 分配Group_Id并合并所有分组 final_df = pd.concat( [group.assign(Group_Id=i+1) for i, group in enumerate(result_groups)], ignore_index=False ) # 按Group_Id和ID排序，保持分组内的原始顺序 final_df = final_df.sort_values(['Group_Id', 'ID']) print(final_df)
代码说明
- 数据拆分：将原始数据拆分为非空和空sGroup两部分，简化后续分组逻辑；
- 分组处理：利用pandas的多层分组功能，对每个子组判断是否有非空sGroup，分别处理两种场景；
- ID分配：通过枚举结果列表为每个分组分配递增ID，最后合并排序得到符合预期的结果。
该方法避免了多层嵌套循环，利用pandas原生操作实现，逻辑清晰且效率更高。
内容的提问来源于stack exchange，提问作者tt0206
相关产品推荐
相关产品推荐
