如何按商家汇总信用卡CSV交易的年度累计支出？

已将信用卡明细导出为CSV文件并通过「文本分列」转换为可读表格，需统计年度内各商家的累计消费金额（同一商家交易金额求和），最终生成汇总表格。

示例原始数据 #

Date (YYYY-MM-DD as UTC),Merchant,Txn Amount (Funding Card),Txn Currency (Funding Card),Txn Amount (Foreign Spend),Txn Currency (Foreign Spend),Card Name,Card Last 4 Digits,Type,Category,Notes
2023-01-29,T:mi Fahri Lati,-24.00,EUR,,,Visa Credit,0000,,Eating Out,
2023-01-29,K-citymarket Porvoo,-94.06,EUR,,,Visa Credit,000,,Groceries,
2023-01-29,Subway,-7.10,EUR,,,Visa Credit,0000,,Eating Out,
2023-01-28,K-market Haikko,-9.08,EUR,,,Visa Credit,0000,,Groceries,
2023-01-28,Sadama Turg,-24.26,EUR,,,Visa Credit,0000,,Eating Out,
2023-01-28,Bolt.eu/o/2301281350,-5.20,EUR,,,Visa Credit,0000,,Travel,
2023-01-28,Sports Direct,-9.00,EUR,,,Visa Credit,0000,,Shopping,
2023-01-28,R-kiosk Ulemiste Kesku,-1.55,EUR,,,Mastercard Credit,0000,,Shopping,
2023-01-28,House Ulemiste Keskus,-21.23,EUR,,,Visa Credit,0000,,Shopping,
2023-01-28,Bolt.eu,-5.00,EUR,,,Visa Credit,0000,,Travel,
2023-01-28,"Viking Line, Viking Xp",-57.20,EUR,,,Visa Credit,0000,,Travel,
2023-01-28,K-market Haikko,-6.00,EUR,,,Visa Credit,0000,,Groceries,
2023-01-27,Vikingline.fi,-356.40,EUR,,,Visa Credit,0000,,Travel,
2023-01-27,S-market Nasi,-19.52,EUR,,,Visa Credit,0000,,Groceries,
2023-01-26,Foodora Finland,-15.50,EUR,,,Visa Credit,0000,,Eating Out,
2023-01-25,Aliexpress,-5.86,EUR,,,Visa Credit,0000,,Shopping,
2023-01-25,K-market Haikko,-15.92,EUR,,,Visa Credit,0000,,Groceries,
2023-01-25,Lounasravintola,-11.00,EUR,,,Mastercard Credit,0000,,Eating Out,
2023-01-24,K-rauta.fi,-107.60,EUR,,,Visa Credit,0000,,Groceries,
2023-01-23,Sale Gammelbacka,-16.47,EUR,,,Visa Credit,0000,,Groceries,
2023-01-23,Motonet Porvoo,-16.98,EUR,,,Visa Credit,0000,,Shopping,
2023-01-22,Cmore.fi,-19.95,EUR,,,Visa Credit,0000,,Bills,
2023-01-22,Easypark Oy,-1.09,EUR,,,Mastercard Credit,0000,,Travel,
2023-01-21,Restaurant Roka,-26.00,EUR,,,Mastercard Credit,0000,,Eating Out,
2023-01-21,Sale Gammelbacka,-8.43,EUR,,,Mastercard Credit,0000,,Groceries,
2023-01-21,St1 Porvoo Eestinmaki,-77.08,EUR,,,Visa Credit,0000,,Travel,

方法一：Excel/Google Sheets 数据透视表（适合非编程用户） #

1. 准备数据：确保表格表头完整，无合并单元格，数据区域连续。
2. 插入数据透视表：
   • 选中整个数据区域（包含表头）
   • Excel：点击「插入」选项卡 → 选择「数据透视表」，指定透视表放置位置（新工作表或当前工作表）
   • Google Sheets：点击「数据」→「数据透视表」，设置放置位置
3. 配置透视表字段：
   • 将「Merchant（商家）」拖入「行」区域
   • 将「Txn Amount (Funding Card)（付款卡交易金额）」拖入「值」区域，默认会自动求和；若未设置，右键值字段 → 选择「值字段设置」→ 勾选「求和」
   • 可选：添加「Date（日期）」到「筛选器」区域，筛选指定年度的交易
4. 整理格式：调整列宽，设置金额为货币格式，删除无关字段，得到最终汇总表。

方法二：Python 脚本处理（适合有编程基础用户） #

使用pandas库快速分组求和：

import pandas as pd

# 读取CSV文件（替换为你的文件路径）
df = pd.read_csv("信用卡明细.csv")

# 按商家分组，对付款卡交易金额求和
summary = df.groupby("Merchant")["Txn Amount (Funding Card)"].sum().reset_index()

# 添加币种列（示例数据均为EUR，可根据实际情况调整）
summary["Currency"] = "EUR"

# 保存汇总结果到新CSV
summary.to_csv("商家消费汇总.csv", index=False)

# 打印查看结果
print(summary)

示例汇总结果 #

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