从 Excel 到 Python
云栖号资讯:【点击查看更多行业资讯】
在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来!
在 2016 年的 Build 大会上,微软宣布全球有 12 亿人在使用 Excel,而在同一年,全球的人口为 74 亿。也就是说,使用 Excel 的人占全球人口的 16.2%。
2019 年的一份报告( https://slashdata-website-cms.s3.amazonaws.com/sample_reports/ZAamt00SbUZKwB9j.pdf )显示,Python 拥有 820 万活跃开发者,占全球人口的 0.001%。
从这些数据可以看出,增强 Excel 和 Python 之间的交互性对我们是有好处的,这为更多人打开了一扇使用 Python 工具的大门。
Python 在 Excel 前端方面的机会是巨大的。在本文中,我们将分享如何实现一个“典型的”财务 Excel 表格。
先工具,后 Excel
在几乎所有我能想到的场景中,通常是先写 Python 代码,不过必须要保持数据“输入”格式的灵活性。
改变输入数据集格式不应该影响到代码
假设我们使用 Pandas 读取一个或两个 CSV/Excel 表格,可能会依赖一组给定的列名。
如果有数千行这样的代码,我们就依赖了很多硬编码的列名,当我们试图使用 Excel 动态输入列名时,就会遇到问题。
因此,在最初的原型设计阶段,在还没有使用 Excel 工作表时,可以在代码里将列名和内部标签名映射起来:
mappings = {'loan identifier': 'loan_id', 'amt': 'amount', ... 'init fees': 'initial_fees'} data.rename(mappings, axis=1, inplace=True)
稍后,这种映射将被 Excel 工作表取代。
Excel 前端
等到 Python 初具模型,就可以开始构建 Excel 前端了。首先,我们要确定哪些变量可以放在 Excel 工作表中。
在开发这类工具时,一般都是要假设输入数据的格式是会变的。
这点要么很重要,要么不那么重要,具体取决于你所在的工作环境以及你要开发什么样的工具。有些工作流程定义得比较好,数据格式不太可能会发生变化。
但是,我总是会倾向于保持谨慎,希望通过 Excel 来增加灵活性,但要注意不要将事情复杂化。
使用 Excel 将 Python 内部列名与外部 CSV/Excel 列名映射起来
使用内部命名系统并允许 Excel 用户指定列映射,这是保持灵活性的一个很好的例子。现在,Excel 用户不再依赖于硬编码的列名,他们可以在不修改 Python 代码的情况下调整列映射。
映射
mappings 是集成的核心部分,它的内容来自 Excel 中的一张表(我通常会叫它 Mapping)。
要得到 mappings,我们需要一个函数来读取 Excel。为此,我们使用了 openpyxl。
我们可以这样读取 Excel 中的单元格:
import openpyxl # 加载工作簿 wb = openpyxl.load_workbook("sheet.xlsx", data_only=True) # 创建工作簿对象 ws = wb.active # 获取单元格 E4 的值 value = ws['E4'].value
我们可以通过这种方式得到 mappings。我们将代码稍作调整,添加 Excel 工作簿“tool_setup.xlsx”本地路径。
我们还要假设 Excel 的当前工作表可能不是我们想要的那个,而且可能会新增、被删除或被移动,所以我们需要通过遍历找到目标工作表的索引位置:
# 首先,我们设置 Excel 文件的路径 path = r".\documents\tool_setup.xlsx" # 加载文件,创建工作簿对象 wb = openpyxl.load_workbook(path, data_only=True) # 找到目标工作表的索引 idx = [i for i, name in wb.sheetnames if name == 'Mapping'][0] # 将目标工作表设置为当前工作表 wb.active = idx ws = wb.active
现在,我们可以填充 mappings 内容了 :
mappings = {} mappings['Amount'] = ws["E4"].value mappings['Term'] = ws["E5"].value
保持灵活性
如果工作表里添加了新行或者把旧行删除,有可能会得到一个不正确的 mappings。为了避免这种情况,我们需要 search_col 函数,它会遍历查找每个单元格,直到找到包含我们想要的值(或超过 limit 限制)的单元格。
# 定义一个函数,用于查找 openpyxl 工作簿对象中的给定列 def search_col(sheet, column, value, limit=100): # 从 1 开始,逐行查找,直到达到 limit 限制 for row in range(1, limit+1): if sheet[f"column{row}"].value == value: # 找到想要的单元格,返回单元格的列和行 return (col, row)
search_col 返回我们想要的数据的列和行。
如果没处理好,哪怕是在工作表里添加一个注释也会让工具不可用。左边的“Internal”在第 12 行,而右边是第 14 行。
我们可以像下面这样找到“Internal”的单元格位置:
search_col(ws, 'B', 'Internal') [Out]: ('B', 12)
接下来,我们通过循环往 mappings 添加其他列映射。在遇到两个或者更多个空的单元格后,我们就知道映射内容已经全部读取完毕,就可以结束循环了:
empty = 0 # 初始化空单元格数量 while empty < 2: # 增加行计数 row += 1 # 赋值 internal = ws[f'B{row}'].value if internal is None: empty += 1 # 遇到空单元格就增加空单元格计数 else: # 加入 mappings mappings[internal] = ws[f'D{row}'].value empty = 0 # also re-initialize the empty counter
运行上面的代码,就可以得到像下面这样的 mappings:
{ 'Loan ID': 'loan identifier', 'Product': 'product type', ... 'Initial Fees': 'init fees' }
如果要引入其他变量,比如文件路径(filepath),我们只需要找到包含“Filepath”的单元格,并把它的值赋给“filepath”:
row, _ = search_col(ws, 'C', 'Filepath') mappings['filepath'] = ws[f'D{row}].value
集成
最后一步,也是最容易的一步——在 Python 脚本中使用列名。
我们使用上面得到的 mappings,将输入列名转成内部标签。
data = pd.read_csv(mappings['Filepath'])
在将输入列名转成内部标签之前,我们必须翻转键值对,即把键 - 值转成值 - 键。
# 翻转 inv_mappings = {mappings[key]: key for key in mappings}
对于这个简单的例子,或许在构建 mappings 时就进行翻转会更方便些。对于复杂一点的工具,我发现使用内部到外部的映射格式会更好。但不管怎样,这一切取决于你自己。
最后,将输入列名转成内部标签:
data.rename(inv_mappings, axis=1, inplace=True)
我们可以做得更灵活一些。为了处理不必要的空格或大小写拼写错误,我们重写了一小部分代码:
data = pd.read_csv(mappings['Filepath']) # 转成小写,剔除不必要的空格 data.rename({col: col.strip().lower() for col in data.columns}, axis=1, inplace=True) # inv_mappings 也是一样 # 内部标签使用蛇形命名方式 (不是必需的) inv_mappings = { mappings[key].strip().lower(): key.strip().lower().replace(' ', '_') for key in mappings } # 现在安全了 data.rename(inv_mappings, axis=1, inplace=True)
另外,我们在 Excel 中显示内部标签时通常会使用首字母大写和正常空格,而在内部我个人还是选择蛇形命名格式。
"Loan ID" -> "loan_id" "Initial Rate" -> "initial_rate"
结论
我曾见过无数家重度使用 Excel 的公司,这么做可以节省数百个小时用于检查单元格、输入值或等待 Excel 模型处理数据的时间。
尽管自动化和机器学习时代正在迅速地将 Excel 的很多领域自动化,但 Excel 不会很快就消失掉。
目前,世界上发展最快的编程语言(Python)和世界上使用最为广泛的软件(Excel)之间的紧密集成可以给很多行业带来巨大收益。
【云栖号在线课堂】每天都有产品技术专家分享!
课程地址:https://yqh.aliyun.com/zhibo立即加入社群,与专家面对面,及时了解课程最新动态!
【云栖号在线课堂 社群】https://c.tb.cn/F3.Z8gvnK
原文发布时间:2020-08-04
本文作者:James Briggs
本文来自:“InfoQ”,了解相关信息可以关注“InfoQ”

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
一文讲透“进程,线程和协程”
云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! 本文从操作系统原理出发结合代码实践讲解了以下内容: 什么是进程,线程和协程? 它们之间的关系是什么? 为什么说Python中的多线程是伪多线程? 不同的应用场景该如何选择技术方案?... 什么是进程 进程-操作系统提供的抽象概念,是系统进行资源分配和调度的基本单位,是操作系统结构的基础。程序是指令、数据及其组织形式的描述,进程是程序的实体。程序本身是没有生命周期的,它只是存在磁盘上的一些指令,程序一旦运行就是进程。 当程序需要运行时,操作系统将代码和所有静态数据记载到内存和进程的地址空间(每个进程都拥有唯一的地址空间,见下图所示)中,通过创建和初始化栈(局部变量,函数参数和返回地址)、分配堆内存以及与IO相关的任务,当前期准备工作完成,启动程序,OS将CPU的控制权转移到新创建的进程,进程开始运行。 操作系统对进程的控制和管理通过PCB(Processing Control Block),PCB通常是系统内存占用区中的一个连续存区,它存放着操作系统用于描述进程情况及控制进程运行所需的全部...
- 下一篇
Java 新特性前瞻:封印类
云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! 本文要点 即将于 2020 年 9 月发布的 Java SE 15 将引入“封印类(sealed class)”(JEP 360),并将其作为预览特性。封印类是一种类或接口,对哪些类或接口可以扩展它们进行了限制。封印类就像枚举一样,可以捕获领域模型中的可选项,让程序员和编译器可以控制穷举。通过解耦可访问性和可扩展性,封印类有助于创建安全的继承结构,让程序库开发人员既可以公开接口,又能够控制所有的实现。封印类与记录类和模式匹配一起,为以数据为中心的编程模式提供支持。Java SE 15(即将于 2020 年 9 月发布)引入 封印类作为预览特性。封印类和接口对可扩展它们的子类型具有更多的控制权, 这对于一般的领域建模和构建更安全的平台库来说都是很有用的。我们可以用sealed 来声明一个类或接口,这意味着只有一组特定的类或接口可以直接对其进行扩展: sealed interface Shape permits Circle, Rectangle { ... } 这段代码声明了一个叫作 Sh...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS8编译安装MySQL8.0.19
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- Hadoop3单机部署,实现最简伪集群
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- CentOS7设置SWAP分区,小内存服务器的救世主
- SpringBoot2全家桶,快速入门学习开发网站教程
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- Windows10,CentOS7,CentOS8安装Nodejs环境
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长