srcmini本文概述
Excel, 我的老朋友。
我是那种只因为我能就可以操作电子表格中数据的人。上周, 我的团队负责人发出了尚未更新项目清单的员工清单, 我立即打开它, 开始制作表格并获取员工的摘要统计信息, 然后对他进行汇总, 然后按员工细分。它可能使他有些发疯, 但我无能为力。我喜欢数据分析, 也喜欢Excel。自2001年以来, 我一直在使用Excel, 直到几个月前, 它还是我用于数据组织, 分析和可视化的首选工具。它非常适合组织数据, 执行计算和分析, 甚至可以用作简单的数据库。从跟踪我的个人预算, 费用和锻炼情况到陆军中多页人员实力分析报告, 我都使用了它。在大多数情况下, Excel都可以很好地工作, 这是唯一的选择。出于安全原因, 我无法访问VBA, Python或任何其他类型的自动化。数据集也足够小以至于无法处理。
大约一年前的今天, 我开始作为软件性能测试的系统工程师在Cerner的能力实验室工作。我要做的简短版本是软件性能测试。我们先测试新的和更新的软件, 然后再发布给客户, 然后测试所有内容：Windows和Unix CPU和内存, Java虚拟机性能, 后端服务和用户界面的响应时间, 数据库性能, 对当前软件的影响, 网络性能以及你能想到的几乎所有其他功能。没有测试仅限于一台机器, 大多数情况下, 它是Windows和Unix设备(物理和虚拟), 最终用户系统和服务器的组合。在我参与的一个项目中, 我们有20多个必须从中收集数据的系统。
根据项目运行的时间长短, 最多可能需要审查, 分析和报告50多个测试。所有这些数据都必须汇总到测试结果的最终报告中, 该报告将发布到内部Jira项目页面中, 以供软件开发团队使用。我们确实有一些内部自定义工具可以汇总大多数数据, 但不是全部数据, 而且这些工具中生成的报告无法针对特定类型的测试进行自定义, 做。我选择编写和提供这些报告的工具是Excel, 为什么不呢？我来自Excel的深厚背景, 当时我相信我可以使它做任何事情。
Excel, 我们有问题…
我不会进入将数据从内部工具获取到Excel的繁琐过程, 但这对我来说是一个数小时的过程, 而且我非常精通Excel。数据必须被复制, 清理, 分类和过滤。无论我如何编写公式, 几乎都必须对其进行调整, 然后必须根据测试次数, 节点数和项目期间记录的各种响应时间来调整表格和图表。之后, 这是一个查找和替换过程, 以使测试名称正确无误, 并确保每页每一行的数字格式和突出显示正确无误, 并清除常规数据。我尝试编写模板, 宏和VBA脚本。如果没有对每个测试进行认真的清理, 我所做的任何事情都无法适应各种测试, 设备和性能指标。我能够提出一个模板, 该模板确实可以加快流程, 但仍然需要半天的时间才能解决问题。
我想到了该模板中包含的一些荒谬复杂的公式和VBA脚本, 我有些笑。当我们更新Java版本并且内部工具无法正确处理日志时, 情况变得更加糟糕。我们切换到了名为GC Easy的外部工具, 该工具在解析注销到可以加载到Excel的JSON文件方面做得很好。可以使用PowerQuery加载JSON文件, 但这是一个缓慢而乏味的过程, 当你尝试解析50多个JSON文件(每个文件包含超过一百万行)时, 你经常会中断Excel。另外, 我需要一种使流程自动化的方法, 而我只是找不到一种很好的方法来做到这一点。即使当我可以将数据加载到Excel中时, 可视化效果(表示随时间变化的堆大小的堆积面积图)也将冻结并导致更多的崩溃。即使我可以解决该问题, JSON文件也只是开始。
手动复制, 清理和分析数据将导致错误。我们都是人类, 我们错过事物, 我们分心, 我们误点击。我的过程无法正常进行, 除非所有人都接受了Excel的广泛培训, 否则它对所有人都将无效。也许这是我的军事背景, 也许这只是个个性怪癖, 但我一直在寻找一种改进流程并提高效率的方法, 因此我开始尝试找出如何简化整个流程的方法。
当时, 我们的内部工具没有API, 也没有有效的导出功能。没有复制/粘贴, 我无法直接从中提取数据。我的下一个想法是直接找到源：测试中每个设备的日志文件。这意味着搜索测试目录及其中的许多子目录, 从不同格式的多种文件类型中提取数据, 进行所有计算, 然后以我需要的格式将其写入Excel, 并附带可视化内容和条件生成格式化。
我需要解析的文件类型是.txt, .csv, .json(各种结构)、. xml(各种结构)、. dat, .html, 并且某些数据必须从Oracle数据库中提取。我必须考虑到变化的目录结构, 变化的文件数量和结构, 不带结束标记的.xml文件以及文件大小超过10GB。该过程还必须易于遵循, 最大程度地实现自动化并处理数百万行数据。 Excel绝对不能满足我的所有需求, 因此我转向了一些流行的BI工具：PowerBI和Tableau。我不希望这是对BI工具的产品评论, 所以我只想说那些绝对不能满足我的需求。
那么, 什么工具可以提供Excel可以做的所有事情, 可以在Excel中编写报告并且可以满足我列出的所有要求？
认识我的新朋友Python和Pandas
如果你完全熟悉Excel, 我想你一定会喜欢Python的熊猫。 pandas将Excel的功能与Python语言的功能结合在一起。 Excel列变成pandas Series, 表变成DataFrame, 复杂公式变成Python函数。熊猫可以做Excel可以做的所有事情：
读取数据
Excel在读取平面文件方面做得非常好, 并且使用PowerQuery来查询数据库以及读取某些.xml和.json文件的能力有限。如果你阅读该文档, 它看起来就像是阅读几乎所有内容的理想工具, 但实际上并非如此。界面笨拙, 需要很多时间来适应, 没有很好的方法来使流程自动化, 并且数据清理几乎和看牙医一样有趣。另一方面, 熊猫使读取数据变得轻而易举。
这个Python数据操作库可以从多个源中本地读取数据。查看文档页面以获取完整列表。真正伟大的事情是, 如果熊猫没有本地阅读它, 那么很有可能找到一个可以读取它的Python包, 或者编写几行代码来解决该问题。查看srcmini的Python导入数据课程系列以及其他课程, 以深入讨论该主题。
我能够使用熊猫读取我之前讨论的每种文件类型, 包括那些没有关闭标签的.xml文件。通过一些创造性的自然语言处理, 我还可以从原始.txt文件中获取所有测试元数据, 这是我无法使用Excel进行的操作, 因为它们未格式化为数据。我现在使用的脚本版本不仅可以读取需要读取的每种文件类型, 而且还可以使用几行Python代码, 通过点击API, 自动从GC Easy中获取Java Garbage Collection .json日志。我以前用Excel无法做到的事情。与Excel相比如何？使用熊猫, 我可以搜索整个测试目录, 并在短短几分钟之内就将所有数据恢复到漂亮, 整洁的DataFrame中。它甚至可以轻松处理那些超过10GB的日志文件。看起来文件大小似乎不大, 但是祝你好运, 用Excel读取任何大小的文件。
我什至开始从读取这些原始平面文件切换到从多TB的Oracle数据库中提取数据。我无法将该过程与Excel进行比较；除了进行简短的实验外, 我还避免了在Excel中进行数据库查询。根据我的记忆, 这不是我想重复的经历。但是, 通过SQLAlchemy轻松进行Python连接。连接很容易, 并且查询速度围绕Excel运行。查阅Python中的关系数据库简介, 以获取对该主题的出色介绍。
清洁数据：
除了可视化之外, 我坚信数据应该放在干净, 整洁, 格式化和标记的表中。熊猫有一些不错的选择, 可以用来清理数据和显示格式良好的表格。熊猫官方网站上的样式页面是我最喜欢的格式化DataFrame的指南。使用几行简短的代码, 你可以设置小数位, 格式编号, 添加行和条件格式, 并且该过程可在无限多个文档中重复进行。无论数据大小如何, 自动执行数据清理某些部分的能力为我节省了无数小时。
公平地说, 你当然可以在Excel中清除数据。它具有大量用于此目的的内置工具, 而这正是我(最终)每次将需要的所有数据加载到其中时都要做的事情。这是一个很大的手动过程, 涉及到太多的鼠标单击, 并且再次非常容易出错且耗时。即使你精通Excel必须提供的所有工具, 错误也一定会发生。有多种方法可以使用公式, 宏和VBA脚本来加快速度(我将在稍后介绍), 但这远非完美的解决方案。
分析数据：
在这里, 我会变得非常直率：在分析数据时, pandas击败了Excel。比较这两者没有公平的方法。你可以编写公式, 并使用Excel中的一些内置函数来分析数据。我做了很多年, 就使它起作用。我教过一些有关Excel中数据分析的课程, 并提倡多次使用它, 并参加了统计课, 其中Excel是首选工具。我并不是说它不能用于分析。但是, 将其与熊猫进行比较就像将铅笔和纸与计算器进行比较一样。他们不在同一个联赛中。
去年8月, Kaggle报告说Python在数据科学领域已经超过R, 而在12月, Quartz将pandas称为”数据科学中最重要的工具”。我认为我不需要在这个话题上说更多。
宏和VBA：
2012年, 我仍处于现役状态, 不得不去得克萨斯州埃尔帕索的幸福堡(Fort Bliss)进行例行牙医预约。当我坐在椅子上时, 牙医提到我的智齿需要去除, 而且由于他有时间, 他只用局部麻醉剂就地进行了手术。那是一次可怕的经历, 使我痛苦了好几天。如果我不得不在再次执行操作还是使用Excel Macros或Visual Basic之间进行选择, 我想我会更乐于接受。
在这些领域有很多高技能的人, 我看到了用聪明的VBA脚本所做的一些令人惊奇的事情, 我对此表示赞赏。我显然不喜欢它, 也不建议任何人使用它, 尤其是当你可以使用Python时。这是VBA的一种流行替代方案, Microsoft打算将Python作为Excel中的脚本语言。
但是我仍然需要使用Excel：
尽管我想用Python做所有事情, 但就我而言这并不实际。目前, 我的分析结果仍必须在Excel中提供。幸运的是, 有一些软件包如OpenPyXl和XlsxWriter专门用于此目的。我每天使用XlsxWriter在Excel中将DataFrames转换为非常专业的报表, 这些报表没有任何公式, 宏或VBA脚本。它甚至可以进行可视化。
谈到可视化数据…
没有更多的无聊图
我从来都不是数据可视化的狂热者。我总是更喜欢在行和列中查看数据, 并突出显示感兴趣的区域, 也许是在此处和那里的条形图。 Excel通过提供大量的可视化数据选项提供了不错的工作, 但据我所知, 我制作的是堆积面积图, 条形图和折线图。对于其中的每一个, 我都选择数据, 点击”插入图表”, 然后找到我需要的数据。从那里, 我可能会做一些过滤并添加标题, 但这只是它的范围。
为了透明起见, 在Excel中有大量用于自定义图形的选项, 但是此过程可能非常困难, 而且学习曲线也很陡。我仍然不喜欢在Excel中制作图形, 但是使用Python则是另一回事了。
我现在坚信数据可视化, 并投入了大量精力来提出出色的可视化分析。当我开始在srcmini上学习课程并被引入Matplotlib和Bokeh之类的软件包时, 我开始了解可视化数据的真正功能和目的。使用Matplotlib, 可以轻松地从熊猫内部或导入pyplot进行可视化。至少可以说, pyplot中可能的可视化列表非常广泛, 并且实际上可以自定义图的所有方面。使用Python进行数据可视化入门介绍了如何使用Matplotlib进行一些出色的可视化。
和Matplotlib一样, 我发现自己在可视化中需要更多的交互性。我希望能够在给定的可视化中选择, 缩放, 悬停, 排序和过滤数据, 而我当然不想学习react.js或D3。直到今天, 学习Python仍然是一种乐趣, 但是JavaScript不是我的专长。幸运的是, Python使用Bokeh和Dash之类的软件包再次拯救了人们。
Anaconda支持的Bokeh结合了D3.js和Python来制作完全交互式的图形, 这些图形可以作为独立的html文档导出, 嵌入到网页中或在Bokeh Server上运行。尽管它的学习曲线比Matplotlib陡峭, 但我认为你会同意它所产生的可视化值得付出努力；只需看看画廊, 然后自己看看。我不打算成为Bokeh的熟练者, 但自从” Bokeh交互式数据可视化”的第一堂课以来, 它一直是我最喜欢的可视化软件包之一。如果Bokeh不能满足你的需求, 那么Plotly的好伙伴会提供Dash。
Dash是另一个适用于Python的免费开源可视化工具, 它结合了React.js而不是D3。我是最近才发现Dash的, 它还可以产生一些真正奇妙的交互式可视化效果, 而无需学习Java语言。很难将Dash与Bokeh进行比较, 因为它们都能产生出色的可视化效果。到目前为止, 我对Dash的唯一困扰是, 我只能使可视化效果在Dash服务器上运行。我还无法将它们嵌入独立的文档, html页面或Jupyter笔记本中。不过, 正如你在他们的画廊中看到的那样, 它仍然显示出巨大的希望。
通过计划和任务自动化节省时间
在2014年夏天, 我们当时所在的组织遇到了问题。陆军预备役由一系列统称为”准备就绪”的数字驱动。是分配给给定组织的人员比例, 可以调派人员进行部署。该数字由几类数据确定, 包括医疗, 牙科, 教育, 身体健康以及其他各种数字。我无法在此处给出数字, 但我们的数字远未达到所需的水平。
我的解决方案是Excel中的多页报告, 其中包含从各种人员数据库中汇编的几张表格和信息图表。这些信息包括有关该组织的摘要数据, 每个度量类别中每个人的信息, 以及基于该周采取的当前行动对未来三个月该数字的预测。实际上, 这是一个巨大的成功, 我们的”就绪状态”确实有了很大的提高, 并且保持了很长时间。
尽管它确实起作用了, 但却是一团糟。我会在星期一清晨到来, 并从所有不同来源手动获取信息, 然后清理数据并将其复制到该周的报告中。然后, 我将花费至少一个小时来检查报告是否有错误, 并进行更多的数据清理。之后, 必须将其通过电子邮件发送给管理人员, 以便他们可以验证数据并添加数据库中还没有的任何更新。平均而言, 花了三天时间才能发送此报告。工时成本过高且不必要。
使用少量的Python脚本, 我可以自动从各种来源中提取所有数据, 将所有内容编译为pandas DataFrames, 将整个报告写到Excel中, 然后自动将其通过电子邮件发送给管理人员。最好的部分是, 本可以安排在星期一早晨午夜后一分钟运行, 这样可以节省大量工作时间。在Linux系统上, 通过将Python脚本添加到crontab中来安排在特定时间运行的脚本太容易了。在我目前的工作中, 我们使用的系统是如此之多, 以至于crontab是工作的重要组成部分, 而调度任务是一项必不可少的技能。因此, 如果确实需要每天晚上在午夜提取数据并将其加载到报表中, 那么crontab将是在Linux系统上运行的方法。
我是Linux迷, 但是大部分工作都在Windows上完成, 这是我的首选操作系统。使用它, 你可以通过Windows Task Scheduler, Schtask.exe或Powershell计划任务的各种选项。使用这些选项中的任何一个, 你都可以安排脚本在系统资源最多的最不繁忙的时段内运行, 也可以将脚本设置为在知道数据源已更新后立即运行。我只能希望几年前有这种选择。
我最喜欢的工具：Jupyter笔记本
一直以来, 我一直使用Excel, 学习Python就像用一把钳子过着我的生活, 有一天有一天发现了一个装满闪亮新工具的完整工具箱。它们中的每一个都很棒, 并且有其用途, 但是如果我使用一种工具比其他所有工具都多, 那就是Jupyter笔记本。我用Python编写的所有内容都已写在Jupyter笔记本中, 可以直接使用, 也可以在以后用作独立的Python脚本。 Jupyter是编写功能, 测试代码, 进行探索性数据分析甚至展示最终产品的绝佳工具。它甚至可以用于撰写博客文章(如此类文章)或在具有Pages的Github上。如果浏览srcmini上的教程, 你会发现其中许多教程是用相同的方式编写的。
为什么在讨论Excel时提及它？
Jupyter Notebooks可以代替Excel用作分析产品。你可以在一页上编写代码, 显示pandas DataFrames, 可视化效果和结论, 然后单击一下即可将其转换为HTML。我的个人目标之一是完全摆脱Excel并使用Jupyter Notebooks来发送完整的报告。自JupyterLab发布以来, 这成为一个更可行的选择。在这和小部件之间, 你可以将Jupyter Notebook变成交互式数据分析应用程序。
最后的想法
我仍然每天都在使用Excel, 它是许多组织中根深蒂固的出色工具, 包括我所服务的组织。几乎每个人都熟悉它, 可以使用它来消费信息并进行简单的数据分析。我不认为很多人知道, 使用Pandas和Python进行相同的分析不仅效率更高, 而且更容易。
我的目标是在年底之前完全摆脱Excel的束缚, 我将能够使用本文中提到的工具来做到这一点。你不会在一夜之间成为专家, 但是对于Excel来说也是如此。我希望阅读此书能激发你脱离它, 并尝试我提到的一些新工具。这将使你效率更高, 生产率更高, 并最终加快工作流程。
评论前必须登录！
注册