用 pandas 做多维度数据透视分析（pivot_table 实战，附完整代码）

{"type":"doc","content":[{"type":"paragraph","attrs":{"id":"6fc82831-68ee-43e6-9cb4-c3cc3abed7e4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"拿到一张扁平的交易明细表，想从不同角度汇总看数：按地区看销售额、按地区乘产品看交叉、加上合计行——手工做透视又慢又错。本文用 pandas 的 pivot_table 一行搞定多维度聚合，支持两层行索引、列分组和总计。"}]},{"type":"paragraph","attrs":{"id":"b95000f4-35a8-4ad6-8928-e98dd32916a8","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"一、数据假设"}]},{"type":"paragraph","attrs":{"id":"d1be0aab-cdbf-43b0-9d86-0ebac1f27f15","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"data.xlsx 至少包含这些列（代码中用英文列名，便于直接运行）："}]},{"type":"bulletList","attrs":{"id":"9da5b5b7-7acf-40f0-86d5-1a265032c8fa","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"662b719b-dc06-4ad1-9ab6-7d7a71383663"},"content":[{"type":"paragraph","attrs":{"id":"d92ef3ab-3eb2-4c5f-b6ef-8b929a8d3a9f","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"region 地区"}]}]},{"type":"listItem","attrs":{"id":"3c19061d-7503-43d8-8a89-cd669a6b1ef8"},"content":[{"type":"paragraph","attrs":{"id":"4f9d75f5-e5f8-4a64-9c25-241a2afbc62c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"product 产品"}]}]},{"type":"listItem","attrs":{"id":"7b451015-f7f9-4ddd-ae99-4e44ecd03192"},"content":[{"type":"paragraph","attrs":{"id":"24c885d1-3d4c-476e-8bb1-2c98a04264ef","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"month 月份"}]}]},{"type":"listItem","attrs":{"id":"9bb79332-4be1-4df4-ba07-027e47c832e8"},"content":[{"type":"paragraph","attrs":{"id":"f49b1cba-59a4-402a-9f92-10f7ab04de2e","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"amount 金额"}]}]}]},{"type":"paragraph","attrs":{"id":"20550956-ef9e-4773-96c9-57e28928fc3f","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"（实战把路径和列名换成你自己的即可）"}]},{"type":"paragraph","attrs":{"id":"9bc0bd00-8b1a-4d4a-aa1f-652f0b91ab95","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"二、核心思路"}]},{"type":"orderedList","attrs":{"id":"e6c19b30-45c9-4f49-84aa-6e192d499caa","start":1,"isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"457d9dea-e066-4838-a3cd-de340f9d2353"},"content":[{"type":"paragraph","attrs":{"id":"307e37e6-0bb3-4e29-ab20-843fc750a3e6","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"index 设定行分组维度，columns 设定列分组维度；"}]}]},{"type":"listItem","attrs":{"id":"36c7cef5-2059-4a51-a18c-f846a8c2f2fa"},"content":[{"type":"paragraph","attrs":{"id":"adca2c3a-cc51-4a84-a141-0b96cd2078a9","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"values 指定聚合字段，aggfunc 指定聚合方式（sum / mean / count）；"}]}]},{"type":"listItem","attrs":{"id":"c999ac42-dbdd-4c8a-94d6-9ced5fdd988b"},"content":[{"type":"paragraph","attrs":{"id":"260b51d5-3321-46bf-883f-52737629b1f5","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"margins 一键加总计行与总计列；"}]}]},{"type":"listItem","attrs":{"id":"56d41d59-156c-4211-ac9e-87653867ec93"},"content":[{"type":"paragraph","attrs":{"id":"c19c7626-f9f2-4dc0-a827-e026fdb818ad","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"fill_value 把缺失组合补成 0，避免 NaN 干扰后续计算。"}]}]}]},{"type":"paragraph","attrs":{"id":"a2f91308-ae74-43ce-a26e-d235e61dfb55","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"三、完整代码（自包含，可直接跑）"}]},{"type":"codeBlock","attrs":{"id":"d37d724d-44e2-41cb-924a-91c12eaec58c","language":"javascript","theme":"atom-one-dark","runtimes":0,"isHoverDragHandle":false,"key":"","languageByAi":"javascript"},"content":[{"type":"text","text":"import pandas as pd\n\n# read your data, e.g. df = pd.read_excel(\"data.xlsx\")\ndf = pd.DataFrame({\n \"region\": [\"East\", \"East\", \"West\", \"West\", \"North\"],\n \"product\": [\"A\", \"B\", \"A\", \"B\", \"A\"],\n \"month\": [\"2026-01\", \"2026-01\", \"2026-02\", \"2026-02\", \"2026-02\"],\n \"amount\": [1000, 1500, 800, 1200, 900],\n})\n\n# 1. single dimension: region x product\npt = pd.pivot_table(df, index=\"region\", columns=\"product\",\n values=\"amount\", aggfunc=\"sum\", fill_value=0)\nprint(\"region x product:\\n\", pt)\n\n# 2. two-level row index: region + month\npt2 = pd.pivot_table(df, index=[\"region\", \"month\"], values=\"amount\",\n aggfunc=\"sum\", fill_value=0)\nprint(\"\\nregion-month:\\n\", pt2)\n\n# 3. with margin totals\npt3 = pd.pivot_table(df, index=\"region\", columns=\"product\",\n values=\"amount\", aggfunc=\"sum\",\n margins=True, margins_name=\"Total\", fill_value=0)\nprint(\"\\nwith totals:\\n\", pt3)\n"}]},{"type":"paragraph","attrs":{"id":"1e0db9f3-b7ab-4361-b63c-22dedf77a51c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"四、几个实战坑"}]},{"type":"bulletList","attrs":{"id":"97817e08-94da-4668-8563-c7f384634821","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"7081174d-853b-46f2-aa16-1a65985fd7da"},"content":[{"type":"paragraph","attrs":{"id":"2bf3fba9-804a-4351-a4d5-dd37d8d2b525","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"组合缺失会出 NaN，fill_value=0 比 dropna 更稳妥，但要注意真为零和缺失的区别；"}]}]},{"type":"listItem","attrs":{"id":"5fb226ae-d7a3-4292-b5ef-acee9e6c9f52"},"content":[{"type":"paragraph","attrs":{"id":"4cadf3d5-a74d-440c-8a62-2e9b0a86d94a","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"多值聚合用 aggfunc=[sum, mean] 会生成多层列名，导出前用 .columns 扁平化；"}]}]},{"type":"listItem","attrs":{"id":"8bf20e6e-c8b3-4d1c-9620-d14a6eea3e0e"},"content":[{"type":"paragraph","attrs":{"id":"ee39dd1c-cf8c-47ad-a0a3-0002ee1b04dd","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"月份排序若变成字符串会乱序，先转 datetime 再排；"}]}]},{"type":"listItem","attrs":{"id":"4197dce9-8c67-43d3-862e-eb2178eaae4c"},"content":[{"type":"paragraph","attrs":{"id":"8b269548-1ae7-48ef-a97a-3e73e82fc22b","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"数据量大时 pivot_table 比 groupby 后再 unstack 可读性更高，性能接近；"}]}]},{"type":"listItem","attrs":{"id":"c5a90b4f-6321-40c7-a493-4e51dae64506"},"content":[{"type":"paragraph","attrs":{"id":"2b7ef66f-5bee-4bde-b31a-95b89119af58","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"读 csv 注意指定编码为 gbk（Windows 中文环境），xlsx 一般无此问题。"}]}]}]},{"type":"paragraph","attrs":{"id":"d330bc03-65cf-47e0-914a-24bb229179d8","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"五、边界"}]},{"type":"paragraph","attrs":{"id":"f94f28e7-0567-4166-9050-cad1685253a4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"脚本只负责把数据按维度汇总，口径定义（哪些算收入、怎么分摊）由你定。客户数据本地跑，别贴通用云端大模型。"}]},{"type":"paragraph","attrs":{"id":"dea2ff8d-e8c5-45f1-a2fd-6e439414972e","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"我是会计师事务所的一名注册会计师，做过财报审计、经济责任审计、专项审计、尽职调查、内控评价各类项目，从手写底稿到 AI 辅助都经历过。如果你也在财务 / 审计岗位想提效，欢迎关注、交流实战。"}]},{"type":"paragraph","attrs":{"id":"b84622c3-fa3b-4892-be55-bcf7d3c0b33a","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"感谢你看到这里。如果这段代码对你有用，欢迎点赞 + 收藏 + 关注——我是会计师事务所的一名注册会计师，会继续分享审计提效与 Python 实战，咱们下篇见。"}]},{"type":"horizontalRule","attrs":{"id":"2c3870c0-e71f-4338-94a0-20246ab6e3cc","isHoverDragHandle":false}},{"type":"heading","attrs":{"id":"fb523372-9de5-438a-8847-c44104b1e0d1","textAlign":"inherit","indent":0,"level":3,"isHoverDragHandle":false},"content":[{"type":"text","text":"📄 技术文11｜用 pdfplumber 从 PDF 提取表格数据（附完整代码）"}]},{"type":"paragraph","attrs":{"id":"dce686ec-d706-42d9-aa85-e21d5d0562f4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"很多报表、对账单、回单都是 PDF 格式，没法直接用 pandas 读。本文用 pdfplumber 把 PDF 里的表格提取成 Python 列表 / 字典，再转 DataFrame 做后续分析。比复制粘贴稳得多，尤其适合批量处理几十份 PDF。"}]},{"type":"paragraph","attrs":{"id":"f16fa604-71df-4d57-867a-f4cd8102b7d9","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"一、环境准备"}]},{"type":"codeBlock","attrs":{"id":"23eb660c-b842-4bcc-9678-da205191726b","language":"javascript","theme":"atom-one-dark","runtimes":0,"isHoverDragHandle":false,"key":"","languageByAi":"javascript"},"content":[{"type":"text","text":"pip install pdfplumber pandas\n"}]},{"type":"paragraph","attrs":{"id":"ede79712-de69-42a1-9f4e-7b9da1da9d31","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"二、核心思路"}]},{"type":"orderedList","attrs":{"id":"1197cefa-4550-40de-abb6-7796c6bcd3d7","start":1,"isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"04a54181-911a-45e1-b2aa-a63bea857d38"},"content":[{"type":"paragraph","attrs":{"id":"fddaab7e-47a1-4e2a-bdff-b4c3f6abc458","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"pdfplumber.open 打开 PDF，逐页读取；"}]}]},{"type":"listItem","attrs":{"id":"7e9e1252-56db-42be-a6df-9cecc78b0cab"},"content":[{"type":"paragraph","attrs":{"id":"21b853f9-9204-4937-9797-78af4506e8fe","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"page.extract_tables() 返回每页所有表格（嵌套列表）；"}]}]},{"type":"listItem","attrs":{"id":"5a6f6b1e-d177-42e6-861e-0fe15e36bc53"},"content":[{"type":"paragraph","attrs":{"id":"764779fd-c6fc-46b1-88d7-7bd4f3babaa5","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"第一行为表头，其余行 zip 成字典，方便入库；"}]}]},{"type":"listItem","attrs":{"id":"823da8aa-ff88-41b4-8c85-7bb70c539c73"},"content":[{"type":"paragraph","attrs":{"id":"7ee4a538-b9e1-40e9-bb7a-fee661f801c2","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"多个 PDF 循环提取，拼成一个总 DataFrame。"}]}]}]},{"type":"paragraph","attrs":{"id":"bf88f807-136b-4b04-84ea-ff6e3965dae7","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"三、完整代码（自包含，可直接跑）"}]},{"type":"codeBlock","attrs":{"id":"c41cdc67-a8d7-4b0f-bb3b-74f94c61a8df","language":"javascript","theme":"atom-one-dark","runtimes":0,"isHoverDragHandle":false,"key":"","languageByAi":"javascript"},"content":[{"type":"text","text":"import pdfplumber\nimport pandas as pd\n\npdf_path = \"report.pdf\"\nrows = []\n\nwith pdfplumber.open(pdf_path) as pdf:\n for page in pdf.pages:\n tables = page.extract_tables()\n for table in tables:\n if not table:\n continue\n header = table[0]\n for r in table[1:]:\n rows.append(dict(zip(header, r)))\n\ndf = pd.DataFrame(rows)\nprint(df.head())\nprint(\"total rows:\", len(df))\n"}]},{"type":"paragraph","attrs":{"id":"6a8f60f2-7ba5-48ef-a733-fd30aa7ec605","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"四、几个实战坑"}]},{"type":"bulletList","attrs":{"id":"cb927de3-e0ac-499f-8f3b-f52a5ff50f2e","isHoverDragHandle":false},"content":[{"type":"listItem","attrs":{"id":"e4201f34-0318-4463-92bd-0f6cdd9edd9b"},"content":[{"type":"paragraph","attrs":{"id":"a66b578b-4bd3-4c68-aceb-bb9651f13430","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"扫描件 PDF（图片型）extract_tables 会返回空，必须先 OCR 转成文本型 PDF；"}]}]},{"type":"listItem","attrs":{"id":"35cc20db-c243-4f4d-a711-102a40e0f779"},"content":[{"type":"paragraph","attrs":{"id":"2f925857-3786-433d-a0c3-bda04502461f","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"表格线缺失时提取会错位，可用 page.crop 先框定区域再 extract_table；"}]}]},{"type":"listItem","attrs":{"id":"42cf19aa-2e3d-498e-98b9-3911232c7e8b"},"content":[{"type":"paragraph","attrs":{"id":"12e4bf06-c907-4f35-8dba-5c70bf00e79c","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"数字被提成字符串带千分位逗号，要 df[col].str.replace(\",\", \"\").astype(float) 清洗；"}]}]},{"type":"listItem","attrs":{"id":"b2c72abe-0a61-4d37-97cd-ef1bb3fb637f"},"content":[{"type":"paragraph","attrs":{"id":"af013e82-8cbd-43e2-9332-c18e597f6840","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"合并单元格会被拆成多行，需补前向值 fillna(method=\"ffill\")；"}]}]},{"type":"listItem","attrs":{"id":"749eff87-74ac-4aca-a67b-a0baefc7d0b6"},"content":[{"type":"paragraph","attrs":{"id":"fe0cf25a-8760-4c9e-b6b0-023010808c6f","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"大 PDF 逐页处理内存友好，不必一次性 load 全部。"}]}]}]},{"type":"paragraph","attrs":{"id":"03f69f40-f3fc-47d1-af49-2316d7d3a711","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"五、边界"}]},{"type":"paragraph","attrs":{"id":"58323d4e-c871-49a1-ab2a-e13a00b4d3e5","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"脚本只负责把 PDF 文本结构化，数据真实性、勾稽关系由你复核。客户资料本地跑，别贴通用云端大模型。"}]},{"type":"paragraph","attrs":{"id":"3f1a39ce-24f8-486f-9e73-1ce448fdb7db","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"我是会计师事务所的一名注册会计师，做过财报审计、经济责任审计、专项审计、尽职调查、内控评价各类项目，从手写底稿到 AI 辅助都经历过。如果你也在财务 / 审计岗位想提效，欢迎关注、交流实战。"}]},{"type":"paragraph","attrs":{"id":"92ac7543-714b-40a6-a903-9209bd4563e4","textAlign":"inherit","indent":0,"color":null,"background":null,"isHoverDragHandle":false},"content":[{"type":"text","text":"感谢你看到这里。如果这段代码对你有用，欢迎点赞 + 收藏 + 关注——我是会计师事务所的一名注册会计师，会继续分享审计提效与 Python 实战，咱们下篇见。"}]}]}