什么是分组汇总What Group & Summarize Means
分组汇总就是"按某一列的值把行分成几组,再对各组的另一列做统计(求和、计数、平均值)"。最常见的例子:按"地区"汇总"销售额",或按"状态"统计"订单数"。这类需求用"透视表"思路最直观。
Group & summarize means "split rows by one column's values, then aggregate another column per group (sum, count, average)". The classic example: sum "sales" by "region", or count "orders" by "status". A pivot-table mindset fits best.
Excel / Google Sheets 透视表Pivot Tables in Excel / Sheets
- 选中数据 →「插入 → 透视表」。
- 把"分组列"拖到"行",把"数值列"拖到"值",并设置汇总方式(求和/计数)。
- Sheets 中路径为「数据 → 数据透视表」。
- Select data → Insert → PivotTable.
- Drag the group column to Rows, the value column to Values, and pick the aggregation (sum/count).
- In Sheets it's Data → Pivot table.
Python pandas 一行搞定One Line with pandas
import pandas as pd
df = pd.read_csv('sales.csv')
print(df.groupby('region')['amount'].sum())
print(df['status'].value_counts())
groupby 负责分组,sum()/value_counts() 负责汇总,适合批量与自动化。
groupby groups, sum()/value_counts() aggregates — ideal for batch and automation.
命令行(大文件友好)Command Line (Big-File Friendly)
awk -F, '{s[$1]+=$3} END {for (k in s) print k, s[k]}' sales.csv
用 awk 按第 1 列分组、累加第 3 列,内存占用低,适合超大 CSV。
awk groups by column 1 and sums column 3 with low memory — great for huge CSVs.
三个统计陷阱Three Pitfalls
- 数字被当文本:求和得到 0,先确保数值列是数字(见 日期数字格式)。
- 空值被计入:求和时会忽略空值,但计数时可能把空也数进去,需先决定处理方式。
- 分组列有空格/大小写:"北京" 和 " 北京 " 会被当成两组,先修剪与统一。
- Numbers as text: sum returns 0; ensure numeric columns are numbers (see date/number formats).
- Nulls counted: sum ignores nulls but count may include them — decide handling first.
- Spaces/case in group column: "Beijing" and " Beijing " become two groups — trim and unify first.
想马上试试?打开在线工具,拖入 CSV 做类型规范与去重,再导出干净的汇总源数据。
Want to try now? Open the toolkit, drop in your CSV to normalize types and dedupe, then export clean source data for summarizing.