如何在Python3的Pandas DataFrame中合并重复行并汇总成本

我尝试合并生成的.csv中的重复行，但未得到预期结果。现有Python3代码可正常运行，但经df.map函数映射后，输出CSV出现重复的team行（如sre-infra-and-release），希望将重复的team行合并为单行并汇总cost列。尝试使用df.groupby(['team']).sum()但未生效，重复行仍存在。

现有代码 #

def inputCsv():
    r = requests.get(endpoint, headers=headers, params=params)
    with open("input.csv", "w") as f:
        f.writelines(r.text.splitlines(True))
    df = pd.read_csv("input.csv")
    return df

def outputCsv():

    with open("secret.json", "w") as file:
        auth = ssm.get_parameter(Name="/something/something/creds", WithDecryption=True)
        file.write(str(auth['Parameter']['Value']))
        os.environ["creds"] = "secret.json"


    rows = []
    with open(rb'output.csv', 'w', newline='') as out_file:
        timestamp = datetime.now()        
        df = getCsv()
        if 'Name' in df.columns:
            df.rename(columns = {"Name": "team", "Total": "cost"}, inplace = True)
        df.insert(0, 'date',timestamp)
        df.insert(1, 'resource_type', "pod")
        df.insert(2, 'resource_name', "kubernetes")
        df.insert(3, 'cluster_name', "eks-cluster")
        df.drop(["CPU", "GPU", "RAM", "PV", "Network", "LoadBalancer", "External", "Shared", "Efficiency"], axis=1, inplace=True)
        df['team'] = df['team'].map(squads).fillna(df['team'])

        df.to_csv(out_file, index=False)

输出CSV示例 #

date,resource_type,resource_name,cluster_name,team,cost
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,billing,0.201
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,sre-infra-and-release,0.238
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,sre-infra-and-release,0.008
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,sre-infra-and-release,0.836
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,growth,0.513
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,sre-observability,3.633
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,order-platform,1.963
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,menu,0.46
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,ncr,3.291
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,order-platform,4.846
2022-12-30 14:56:08.383080,pod,kubernetes,eks-cluster,grocery-affordability,0.171

解决方案 #

问题出在groupby的使用逻辑——仅按team分组时，其他非数值列（如date、resource_type）的存在会导致分组无法合并重复行，因为这些列也会被视为分组判断的一部分。正确的做法是将所有非求和列都纳入分组键，确保同一team的行在这些列上值一致，再对cost求和。

修改后的核心代码片段：

# 原team映射逻辑保留
df['team'] = df['team'].map(squads).fillna(df['team'])

# 新增分组聚合步骤
group_cols = ['date', 'resource_type', 'resource_name', 'cluster_name', 'team']
df = df.groupby(group_cols, as_index=False)['cost'].sum()

# 原输出逻辑保留
df.to_csv(out_file, index=False)

关键说明 #

• group_cols包含所有不需要求和的列：这些列在所有行中值相同，确保同一team的行会被归为一组。
• as_index=False：保证分组结果仍为标准DataFrame格式，不会将分组键设为索引，直接适配后续的CSV输出。
• 仅对cost列求和：其他列保持分组后的唯一值，避免数据丢失或变形。

修改后，输出CSV中每个team只会显示一行，cost列对应该team所有重复行的总和。

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