什么是分组汇总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.
提示:汇总前先用 365Lab 在线 CSV 工具 规范类型、去重、修剪空格,结果更可靠;大文件也能本地流式处理。
Tip: Before summarizing, use the 365Lab toolkit to normalize types, dedupe and trim — results are more reliable, and big files stream locally.

想马上试试?打开在线工具,拖入 CSV 做类型规范与去重,再导出干净的汇总源数据。

Want to try now? Open the toolkit, drop in your CSV to normalize types and dedupe, then export clean source data for summarizing.

打开 CSV 清洗工具 →Open the CSV Cleaner →