11. Pandas :操作Excel文件(Excel报表的案例研究)

从一个装有各种 Excel 文件的文件夹开始,这些文件需要被整合到 Excel 报表中。

它们包含了虚构的电信运营商在全美各营业厅的套餐(金、银、铜)销售情况。每个月有两个文件,子文件夹 new 中的是新用户,子文件夹 existing 中的是老用户。

由于这些报表来自不同的系统,因而它们的格式也不相同:新用户的数据以 xlsx 文件格式

交付,老用户的数据则以旧的 xls 格式交付。每个文件最多包含了 10000 次交易。


一 目标

生成一张 Excel 报表,在报表中展示每个营业厅每月的总体销售情况。

文件夹 new 中的 January.xlsx 文件:


二 方法:写一个脚本文件

脚本文件会从两个目录中读取 Excel 文件、汇总数据,最后将总结表写入一个新的 Excel 文件。将脚本文件和 sales_data 文件夹放到一起。

读取所有Excel → 合并 → 按日期+店铺汇总 → 按月统计 → 导出报表。

from pathlib import Pathimport pandas as pd

Path:用于处理文件路径(跨平台兼容)。

通过标准库 pathlib 模块中的 Path 类,你可以使用多种强大的工具:路径对象可以让你轻松地通过斜杠连接路径的分量来构造路径,就像在 this_dir / "sales_data" 及其下面 4 行代码中所展示的那样。这些路径对象是可以跨平台工作的。也可以使用 rglob 之类的过滤器。

补充:Path 对象支持用 / 拼接路径分量。

from pathlib import Path# 构造路径:当前目录下的 sales_data 子目录
this_dir = Path.cwd()  # 获取当前工作目录
data_path = this_dir / "sales_data"  # 拼接路径
print(data_path)  

输出:

/home/user/project/sales_data(Linux)

或 C:\project\sales_data(Windows)

/ 操作符会自动处理不同操作系统的路径分隔符(如 \ 或 /),无需手动调整。

支持多级拼接,例如 data_path / "2025" / "report.csv"

补充:rglob 过滤器在下面说明。

# 文件的目录
this_dir = Path(__file__).resolve().parent

获取当前脚本所在目录。

Path(__file__):获取当前脚本文件路径。__file__ 表示源代码文件运行时所在路径。

parent:返回上级目录路径(即脚本所在文件夹)。

若脚本路径为 /home/user/project/scripts/analyze.py,则 this_dir/home/user/project/scripts/

resolve():解析符号链接,获取绝对路径。在 parent 前面调用的 resolve 方法会将 parent路径转换为绝对路径。

# 从sales_data的所有子文件夹中读取Excel文件
parts = []
for path in (this_dir / "sales_data").rglob("*.xls*"):print(f'Reading {path.name}')part = pd.read_excel(path, index_col="transaction_id")parts.append(part)

合并所有Excel文件。

(this_dir / "sales_data"):定位到 sales_data 子目录。

rglob("*.xls*"):递归搜索所有 .xls 或 .xlsx 文件。读取某个目录中所有 Excel 文件。通过通配符来展开路径名。? 通配符表示某单个字符,而 * 表示任意多个字符(包括 0 个)。将 *.xls* 作为 globbing 表达式可以确保新旧两种格式的 Excel 文件都能被发现。也可以写成 [!~$]*.xls*。这样就可以忽略临时的 Excel 文件(文件名以~ $ 开头)。

补充:表达式 [!~$]*.xls*

[!...] 是排除模式,表示不匹配方括号内指定的字符。

[!a] 表示排除以 a 开头的文件名。

[!~$] 表示排除以 ~ 或 $ 开头的文件名。

pd.read_excel:读取 Excel 文件,并将 transaction_id 列设为 DataFrame 的索引。

parts:存储所有子文件的DataFrame,后续合并。

parts.append(part)

将所有子 DataFrame 存储到 parts 列表中,后续可通过 pd.concat(parts) 合并为单一 DataFrame。

注意:

要求每个Excel文件都有 transaction_id 列,否则报错。

文件需有相同列名才能正确合并。

补充:rglob 过滤器

# 递归查找所有 .csv 文件
csv_files = list(data_path.rglob("*.csv"))  
# 输出类似:[PosixPath('/sales_data/2025/report.csv'), ...]# 查找所有以 "temp" 开头的文件(含子目录)
temp_files = list(data_path.rglob("temp*.*"))  

glob("*.csv"):仅搜索当前目录下的 CSV 文件。glob 会忽略子目录。

rglob("*.csv"):等同于 glob("**/*.csv"),递归所有子目录。对所有子目录也进行匹配。

# 将从Excel文件生成的DataFrame结合成单个DataFrame
# pandas会负责对列进行对齐
df = pd.concat(parts)

合并DataFrame。

pd.concat(parts):将多个DataFrame按行拼接。

合并逻辑:默认按行合并(axis=0),要求所有 Excel 文件的列结构一致。

自动对齐列名:不同文件的列名若不一致,合并后会产生NaN。若文件1有列 [A, B],文件2有列 [A, C],合并后列名为 [A, B, C],缺失值填充 NaN

# 对每个营业厅进行数据透视,将同一天产生的交易全部加起来
pivot = pd.pivot_table(df,index="transaction_date", columns="store",values="amount", aggfunc="sum")

创建数据透视表:通过 pivot_table 函数,将原始数据按以下规则重组。

index:行索引 → 交易日期。以 transaction_date(交易日期)为行索引,自动去重并排序所有日期。指定行索引为交易日期,实现按日期分组。

columns:列分类 → 店铺。以 store(营业厅名称)为列索引,自动去重并生成列标题。

单元格值:对同一日期、同一营业厅的 amount(交易金额)进行求和操作(sum)。

values="amount":指定需要聚合的数值列。amount是列名。若 values 未指定,默认聚合所有数值型列;若指定非数值列,会引发错误。

aggfunc="sum":定义聚合函数为求和。

# 按月重采样,并赋予一个索引名称
summary = pivot.resample("M").sum()
summary.index.name = "Month" 

resample("M"):按月末日期分组(如 2023-01-31)。

.sum():每月各店铺销售额求和。

index.name:重命名索引为 Month

summary.to_excel(this_dir / "sales_report_pandas.xlsx")

导出到Excel。

生成文件路径:this_dir/sales_report_pandas.xlsx

文件内容:月度各店铺销售总额报表。


三 实践记录

python版本:3.12.4

pandas版本:2.2.2

报错原因:sales_data 文件夹中包含 .xls 文件(旧版 Excel 格式)。从 Pandas 1.2.0 开始,默认引擎(openpyxl)只支持 .xlsx 文件,无法读取 .xls 文件。这会导致在尝试读取 .xls 文件时报错。

修改:需要安装 xlrd 库(支持 .xls 格式),并在读取 .xls 文件时显式指定引擎。

第1步:pip install --upgrade xlrd

xlrd 新版本只支持 .xls 文件。

在 pandas 1.2.0 及以后的版本中,默认情况下不再支持直接使用 xlrd 来读取 .xls 文件,因为 xlrd 从 2.0.0 版本开始已移除对 Excel .xlsx 文件的支持,并且只支持 .xls 文件。

第2步:更新代码。

结果: 

警告信息是一个 FutureWarning,这是 pandas 提前通知用户某些功能或参数将在未来版本中被移除或更改。具体来说,这条警告是关于时间序列重采样频率字符串 'M' 的使用。在 pandas 中,'M' 一直用于表示“按月”重采样(即每个月的最后一天)。然而,为了更清晰和一致,pandas 团队引入了新的频率字符串 'ME',表示“按月末”(Month End)。在未来的 pandas 版本中,'M' 将被移除,建议使用 'ME' 来避免潜在的兼容性问题。

需要将代码中的 'M' 替换为 'ME'

summary = pivot.resample("ME").sum() 

生成了sales_report_pandas.xlsx文件。

文件内容:

完整代码:

from pathlib import Pathimport pandas as pd# 文件的目录
this_dir = Path(__file__).resolve().parent# 从sales_data的所有子文件夹中读取Excel文件
parts = []
for path in (this_dir / "sales_data").rglob("*.xls*"):print(f'Reading {path.name}')if path.suffix == ".xls":  # 如果是 .xls 文件part = pd.read_excel(path, index_col="transaction_id", engine="xlrd")else:  # 如果是 .xlsx 文件part = pd.read_excel(path, index_col="transaction_id")parts.append(part)# 将从Excel文件生成的DataFrame结合成单个DataFrame
# pandas会负责对列进行对齐
df = pd.concat(parts)# 对每个营业厅进行数据透视,将同一天产生的交易全部加起来
pivot = pd.pivot_table(df,index="transaction_date", columns="store",values="amount", aggfunc="sum")# 按月重采样,并赋予一个索引名称
summary = pivot.resample("M").sum()
summary.index.name = "Month"# 将总结报表写入Excel文件
summary.to_excel(this_dir / "sales_report_pandas.xlsx")

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/35008.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

一周学会Flask3 Python Web开发-SQLAlchemy添加数据操作-班级模块

锋哥原创的Flask3 Python Web开发 Flask3视频教程: 2025版 Flask3 Python web开发 视频教程(无废话版) 玩命更新中~_哔哩哔哩_bilibili SQLAlchemy提供session.add()方法添加model实体数据,以及提供session.commit()提交事务。 首先list.html加一个添…

大型语言模型与强化学习的融合:迈向通用人工智能的新范式——基于基础复现的实验平台构建

1. 引言 大型语言模型(LLM)在自然语言处理领域的突破,展现了强大的知识存储、推理和生成能力,为人工智能带来了新的可能性。强化学习(RL)作为一种通过与环境交互学习最优策略的方法,在智能体训…

Axure大屏可视化原型模板及素材:数据可视化的高效解决方案

数据可视化已成为企业决策、运营分析、市场洞察的重要工具。数据可视化大屏,作为数据展示和交互的直观平台,能够实时呈现关键数据,帮助企业快速做出决策。Axure作为原型设计领域的领先工具,以其丰富的组件库、强大的交互设计能力和…

图片填充容器,如何描述

【图片需要完全填充/拉伸以适应容器尺寸&#xff0c;不保持原始比例&#xff0c;使用 object-fit: fill 属性实现】 效果&#xff1a; 代码案例&#xff1a; <!DOCTYPE html> <html lang"zh-CN"> <head><meta charset"UTF-8">&l…

缓存和客户端数据存储体系(Ark Data Kit)--- 应用数据持久化(首选项持久化、K-V、关系型数据库)持续更新中...

Core File Kit做怎删改查操作不便&#xff0c;用Ark Data Kit。 功能介绍 ArkData &#xff08;方舟数据管理&#xff09;为开发者提供数据存储、数据管理和数据同步能力&#xff0c;比如联系人应用数据可以保存到数据库中&#xff0c;提供数据库的安全、可靠以及共享访问等管…

RUOYI框架在实际项目中的应用三:Ruoyi微服务版本-RuoYi-Cloud

如需观看Ruoyi框架的整体介绍&#xff0c;请移步&#xff1a;RUOYI框架在实际项目中的应用一&#xff1a;ruoyi简介 一、Ruoyi微服务版本-Ruoyi微服务版本 1、官方资料 1&#xff1a;代码地址&#xff1a;https://gitee.com/y_project/RuoYi-Cloud.git 2&#xff1a;文档介绍…

windbg集成python环境(pykd)

背景: 调试FPU指令过程时&#xff0c;需要一直跟踪FPU Status寄存器TOP字段(ST寄存器对应的BC寄存器)&#xff0c;TOP寄存器位于FPU Status[13:11]&#xff0c;这种转换过程并非一目了然(如下图)&#xff1a; [Disassembly窗口fld指令执行后&#xff0c;Registers窗口中fpsw的…

微信小程序threejs三维开发

微信小程序threejs开发 import * as THREE from three; const { performance, document, window, HTMLCanvasElement, requestAnimationFrame, cancelAnimationFrame, core, Event, Event0 } THREE .DHTML import Stats from three/examples/jsm/libs/stats.module.js; im…

【算法】双指针、递归与回溯、排序、查找

⭐️个人主页&#xff1a;小羊 ⭐️所属专栏&#xff1a;Linux 很荣幸您能阅读我的文章&#xff0c;诚请评论指点&#xff0c;欢迎欢迎 ~ 目录 持续更新中...1、双指针移动零复写零快乐数长度最小的子数组dd爱框框 2、递归与回溯3、排序算法4、查找算法 持续更新中… 1、双指…

How to install cangjie on Linux mint 22.1

概述 仓颉编程语言是一款面向全场景智能的新一代编程语言&#xff0c;主打原生智能化、天生全场景、高性能、强安全。主要应用于鸿蒙原生应用及服务应用等场景中&#xff0c;为开发者提供良好的编程体验。 今天&#xff0c;我们介绍一下仓颉语言在Linux mint 22.1上的安装。 …

杰理可视化SDK-手机三方通话控制

杰理可视化SDK-手机三方通话控制 手机三方通话功能杰理SDK三方通话控制SDK三方通话状态获取SDK三方通话处理 手机三方通话功能是手机常用的功能之一。本篇文章简单介绍了杰理可视化SDK在蓝牙耳机应用中&#xff0c;当手机存在三方通话来电或正在进行三方通话时&#xff0c;蓝牙…

【二分算法】-- 寻找旋转排序数组中的最小值

文章目录 1. 题目2. 题目解析3. 代码 1. 题目 在线oj 2. 题目解析 解法一&#xff1a;暴力查找最小值 时间复杂度&#xff1a;0(N) 解法二&#xff1a;二分查找算法 【二段性】&#xff1a; A~B&#xff1a;nums[i] > nums[i 1] C~D&#xff1a;nums[i] < nums[i…

音视频入门基础:RTCP专题(1)——RTCP官方文档下载

一、引言 实时传输控制协议&#xff08;Real-time Transport Control Protocol或RTP Control Protocol或简写RTCP&#xff09;是实时传输协议&#xff08;RTP&#xff09;的一个姐妹协议。RTCP由《RFC 3550》定义&#xff08;取代废弃的《RFC 1889》&#xff09;。RTP使用一个…

OrioleDB: 新一代PostgreSQL存储引擎

PostgreSQL 12 引入了可插拔式的表存储方法接口&#xff0c;允许为不同的表选择不同的存储机制&#xff0c;例如用于 OLTP 操作的堆表&#xff08;HEAP、默认&#xff09;、用于 OLAP 操作的列式表&#xff08;Citus&#xff09;&#xff0c;以及用于超快速搜索处理的内存表。 …

1.5 Spring Boot项目打包和运行

本文介绍了如何使用Spring Boot进行项目打包和运行。首先&#xff0c;讲解了如何将Spring Boot项目打包为可执行的JAR包&#xff0c;并直接运行&#xff0c;无需部署到外部Web服务器。接着&#xff0c;介绍了如何将项目打包为WAR包&#xff0c;以便部署到Web容器中&#xff0c;…

2.7 滑动窗口专题:串联所有单词的子串

LeetCode 30. 串联所有单词的子串算法对比分析 1. 题目链接 LeetCode 30. 串联所有单词的子串 2. 题目描述 给定一个字符串 s 和一个字符串数组 words&#xff0c;words 中所有单词长度相同。要求找到 s 中所有起始索引&#xff0c;使得从该位置开始的连续子串包含 words 中所…

vue中,watch里,this为undefined的两种解决办法

提示&#xff1a;vue中&#xff0c;watch里&#xff0c;this为undefined的两种解决办法 文章目录 [TOC](文章目录) 前言一、问题二、方法1——使用function函数代替箭头函数()>{}三、方法2——使用that总结 前言 ‌‌‌‌‌尽量使用方法1——使用function函数代替箭头函数()…

uniapp移动端图片比较器组件,仿英伟达官网rtx光追图片比较器功能

组件下载地址&#xff1a;https://ext.dcloud.net.cn/plugin?id22609 已测试h5和微信小程序&#xff0c;理论支持全平台 亮点&#xff1a; 简单易用 使用js计算而不是resize属性&#xff0c;定制化程度更高 组件挂在后可播放指示线动画&#xff0c;提示用户可以拖拽比较图片…

SDL3 游戏开发 Windows 环境搭建

SDL3 游戏开发 Windows 环境搭建 一、准备工作1.1 必备工具与库安装1.1.1 CMake1.1.2 MinGW-w641.1.3 Ninja1.1.4 Git1.1.5 SDL3 及扩展库1.1.6 VSCode 及插件 二、配置VSCode项目并验证环境2.1 创建测试源文件2.2 编写CMakeLists.txt文件和CMakePresets.json2.2.1 使用VSCode的…

【sql靶场】第13、14、17关-post提交报错注入保姆级教程

目录 【sql靶场】第13、14、17关-post提交报错注入保姆级教程 1.知识回顾 1.报错注入深解 2.报错注入格式 3.使用的函数 4.URL 5.核心组成部分 6.数据编码规范 7.请求方法 2.第十三关 1.测试闭合 2.列数测试 3.测试回显 4.爆出数据库名 5.爆出表名 6.爆出字段 …