id: de25408526ed462b8be8e40d50e1c194
parent_id: 
item_type: 1
item_id: b27fe1862ecf43b89e73859f7660353a
item_updated_time: 1764345591503
title_diff: "[{\"diffs\":[[1,\"3步搞定：Python报表自动化\"]],\"start1\":0,\"start2\":0,\"length1\":0,\"length2\":16}]"
body_diff: "[{\"diffs\":[[1,\"<html>\\\r\\\n<head>\\\r\\\n  <title>3步搞定：Python报表自动化</title>\\\r\\\n  <basefont face=\\\"微软雅黑\\\" size=\\\"2\\\" />\\\r\\\n  <meta http-equiv=\\\"Content-Type\\\" content=\\\"text/html;charset=utf-8\\\" />\\\r\\\n  <meta name=\\\"exporter-version\\\" content=\\\"YXBJ Windows/608924 (zh-CN, DDL); Windows/10.0.0 (Win64); EDAMVersion=V2;\\\"/>\\\r\\\n  <style>\\\r\\\n\\tbody, td {\\\r\\\n\\t  font-family: 微软雅黑;\\\r\\\n\\t  font-size: 12pt;\\\r\\\n\\t}\\\r\\\n  </style>\\\r\\\n</head>\\\r\\\n<body>\\\r\\\n<a name=\\\"21011\\\"></a>\\\r\\\n<h1>3步搞定：Python报表自动化</h1>\\\r\\\n\\\r\\\n<div>\\\n<span><div><br/></div><div style=\\\"min-width: 100%;\\\"><div style=\\\"background-color: transparent; vertical-align: baseline;\\\"><div style=\\\"background-color: transparent; vertical-align: baseline; text-align: left;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 0px auto; padding: 0px 2em; border: 0px; vertical-align: baseline; z-index: 200; position: relative; width: 36em;\\\"><div style=\\\"margin: 0px 25px 0px -25px; padding: 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"margin: 0px; padding: 0px 0px 9em; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><h1 style=\\\"font-size: 35px; border: 0px; vertical-align: baseline; text-align: left; padding: 0px; margin: 0px;\\\"></h1><h2 style=\\\"margin: 0px 0px 14px; padding: 0px; line-height: 1.4; color: rgb(51, 51, 51); font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: 0.612px; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);\\\"><span style=\\\"line-height: 1.4; color: rgb(51, 51, 51); font-family: -apple-system-font, BlinkMacSystemFont, &quot;Helvetica Neue&quot;, &quot;PingFang SC&quot;, &quot;Hiragino Sans GB&quot;, &quot;Microsoft YaHei UI&quot;, &quot;Microsoft YaHei&quot;, Arial, sans-serif; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: 0.612px; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;\\\"><font style=\\\"font-size: 24pt;\\\">3步搞定：Python报表自动化</font></span></h2><div style=\\\"padding: 1.5em 0px; border: 0px; vertical-align: baseline; position: relative; height: 1em; text-align: center; margin: 0px;\\\"><div style=\\\"padding: 0px 4em; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline; position: absolute; left: -4em; top: 50%; z-index: 10; width: 100%; height: 0.1em; opacity: 0.5; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%); margin: 0px;\\\"></div></div></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640\\\" type=\\\"image/gif\\\" data-filename=\\\"640\\\" height=\\\"150\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"400\\\"/></span></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><h1 style=\\\"font-size: 35px; margin: 0px 0px 20px; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">01 案例场景</span></h1><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">作为企业的数据统计岗，每天都需要做很多报表，日报、周报、季报、月报。如果我们能利用Python的数据分析功能把这些常规的流程标准化的报表自动化，那么我们将能有更多的时间集中于</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">数据背后的业务发展及逻辑的分析上</span><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">，这样才能被称为是企业的数据分析师，而不是简单的数据搬运工。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">下面我们通过一个简单的案例来看看Python报表自动化的建模过程。某银行贷款业务部门数据分析员每天需要根据系统生成的个人贷款客户信息表统计管辖区域内各经营单位的不同贷款产品今年的投放情况。源数据表格式及字段如下图所示；</span></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [1]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"103\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">需要统计的数据报表如下图所示：</span></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [2]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"125\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div></div><h1 style=\\\"font-size: 35px; margin: 0px 0px 20px; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">02 Excel制作过程</span></h1><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">结合以上两张图，我们知道利用Excel的数据透视表功能就制作该报表：</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">选中数据表中任意一个单元格，点击插入数据透视表</span><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">，然后按以下步骤执行：</span></div><ul style=\\\"font-size: inherit; padding: 0px; border: 0px; vertical-align: baseline; margin: -1em 0px 1.5em 1.5em; list-style: disc;\\\"><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">将合同生效日字段放在页区域（筛选今年）</span></div></li><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">将用途字段放在列区域。</span></div></li><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">将单位字段放在透视表的行区域。</span></div></li></ul><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [3]\\\" type=\\\"image/jpeg\\\" data-filename=\\\"640\\\" height=\\\"322\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">当处理到单位字段时我们会发现，表中每一笔贷款都有三家网点进行业绩分成。我们需要将分成比例也考虑进去。所以透视表中的行区域及值区域不能简单的放入单位1和贷款金额。此时大部分人都会想到先在数据源表格中添加三列按分成比例分成以后的贷款金额。</span></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [4]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"115\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);\\\" width=\\\"249\\\"/></span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">三个数值的计算方法分别为：</span></div><blockquote style=\\\"font-size: inherit; margin: 0px 0px 1.5em 2em; padding: 0px 0px 0px 1em; border-width: 0px 0px 0px 5px; border-top-style: initial; border-right-style: initial; border-bottom-style: initial; border-color: initial; border-image: initial; vertical-align: baseline; quotes: none; border-left-style: solid;\\\"><div style=\\\"margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-style: italic; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">分成贷款金额1=贷款金额*分成比例1</span></div><div style=\\\"margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-style: italic; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">分成贷款金额2=贷款金额*分成比例2</span></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-style: italic; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">分成贷款金额3=贷款金额*分成比例3</span></div></blockquote><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">然后将单位1及分成贷款金额1拖放到透视表的行区域及值区域。求出每个网点在分成金额1上的贷款投放，用同样的方法将各网点在分成贷款金额2及3的和。于是就会得到结构如下的三个数据透视表：</span></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [5]\\\" type=\\\"image/jpeg\\\" data-filename=\\\"640\\\" height=\\\"327\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">最后一步就是运用VlOOKUP将同一家网点的同种贷款金额整合相加到日报相对应的单元格里，实现最后的报表输出。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">以上流程每天都需要进行重复：插入列、编写公式、做数据透视表、VLOOKUP，相信就算是熟悉Excel的人也需要花20到25分钟，而在操作过程中很容易</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">因为疏忽而造成错误。</span><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">如此循环往复，效率低下并且出错率高。而从操作上来讲，整个流程都是标准化的，因此我们可以考虑使用Python进行自动化设计。</span></div><h1 style=\\\"font-size: 35px; margin: 0px 0px 20px; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">03 Python优化报表制作过程</span></h1><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">通过以上分析，我们知道问题的难点在于处理分成比例。存在多个分成比例产生了很多重复性的工作。由于每笔贷款的三个分成比例都是对同一个贷款金额进行分成，我们可以将贷款金额分别与分成单位1、2、3及分成比例1、2、3组成三张分表，然后将分表纵向追加。这样计算分成贷款金额时就只需要将新表的贷款金额及合并成一列的分成比例进行相乘。得出每个分成比例对于的分成贷款金额，最后将分成贷款金额按照单位及用途进行数据透视。</span></div><h2 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.1加载数据表</span></h2><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">数据加载过程比较简单，使用read_excel()进行设置即可，这里不在赘述。仅提出以下建议，供大家参考：</span></div><ul style=\\\"font-size: inherit; padding: 0px; border: 0px; vertical-align: baseline; margin: -1em 0px 1.5em 1.5em; list-style: disc;\\\"><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">利用read_excel()的usecols参数对表列进行指定，排除不必要的干扰列。</span></div></li><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">养成数据加载以后，使用head()进行预览的习惯。</span></div></li><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">养成使用shape及info()了解表格的基本情况的习惯。</span></div></li></ul><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">import pandas as pd</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">from datetime import datetime   # 因为后面需要处理到日期筛选，所以需要将datetime类从datetime模块中加载进来</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data=pd.read_excel(r&quot;E:\\\\个人贷款客户信息表.xlsx&quot;,usecols=[1,4,6,7,8,9,10,11,12])  # 假设个人贷款客户信息表放在本地E盘</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data.shape</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">---</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">(50585, 9)</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">以上为导入个人贷款信息表格代码，由于个人贷款客户信息表为工作簿第一张工作表，因此缺省sheet_name参数。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">通过指定导入例的方法将与计算无关的</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">“</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">协议编号”，“贷款余额”，“固定利率”，“合同到期日”去除。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">#查看data表的基本信息</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data.info()</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">---</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">&lt;class 'pandas.core.frame.DataFrame'&gt;</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">RangeIndex: 50585 entries, 0 to 50584</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">Data columns (total 9 columns):</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">贷款金额     50585 non-null int64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">合同生效日    50585 non-null datetime64[ns]</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">用途       50585 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">单位1      50585 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">分成比例1    50585 non-null int64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">单位2      16418 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">分成比例2    16418 non-null float64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">单位3      958 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">分成比例3    958 non-null float64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">dtypes: datetime64[ns](1), float64(2), int64(2), object(4)</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">memory usage: 3.5+ MB</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">接下来预览一下data表的数据，默认显示前5行</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data.head() </span></div></div></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [6]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"156\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><h2 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.2日期筛选</span></h2><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">个人贷款信息表包含该银行所有的历史数据，而我们每日的报表只需要统计当年的投放情况。所以计算投放金额前，我们需要将合同生效日期不符合要求的贷款记录排除掉。这里我们通过判断日期是否为2019年（大于2018-12-31）返回TRUE/FALSE进行选择判断。</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">这种利用判断条件来选择数据的方式叫布尔索引。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">这里解释一下import datetime和from datetime import datetime的区别。datetime 是模块，而datetime模块里面还包含一个datetime类。通过from datetime import datetime能从datetime模块直接导入datetime类。如果导入import datetime ，则在定义时间时，需要使用datetime.datetime()格式。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data=data[data[&quot;合同生效日&quot;]&gt;datetime(2018,12,31)]</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data.shape #经过对日期的过滤，输出了1673行，9列</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">---</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">(1673, 9)</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对日期列进行观察，可以看到合同生效日都是2019年的日期了。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data.head() </span></div></div></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [7]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"165\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><h2 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.3数据表拆分</span></h2><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">下一步，我们需要处理分成比例问题了。此案例的重点也是在这里。按照 1.3节 Python优化报表制作过程中的分析，我们需要先将贷款金额分别与分成单位1、2、3及分成比例1、2、3组成三张分表。数据表的拆分代码很简单。直接用</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">普</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">通索引将需要的列传导给分表就可以了。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data1=data[[&quot;用途&quot;,&quot;贷款金额&quot;,&quot;单位1&quot;,&quot;分成比例1&quot;]]</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data2=data[[&quot;用途&quot;,&quot;贷款金额&quot;,&quot;单位2&quot;,&quot;分成比例2&quot;]]</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data3=data[[&quot;用途&quot;,&quot;贷款金额&quot;,&quot;单位3&quot;,&quot;分成比例3&quot;]]</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对data1表进行预览</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data1.head()</span></div></div></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [8]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"170\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);\\\" width=\\\"284\\\"/></span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对data2表进行预览</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data2.head()</span></div></div></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [9]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"173\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);\\\" width=\\\"284\\\"/></span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对data3表进行预览</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data3.head()</span></div></div></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [10]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"170\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);\\\" width=\\\"287\\\"/></span></div><h2 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.4 数据追加合并</span></h2><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">接下来我们需求是将三个分离的表进行纵向的拼接。在我们的例子中，需要将三个表的单位及分成比例字段追加在同一列。但是目前三个新表中的单位及分成比例字段名字是不一致的，不能直接追加。所以我们需要先将分表的名字统一。</span></div><h3 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.4.1 重命名列索引</span></h3><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">在Python中重命名，使用rename()函数。并使用键值对的方式对columns参数进行赋值。将各分表的单位字段统一命名为单位，分成比例字段统一命名为分成比例。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data1=data1.rename(columns={&quot;单位1&quot;:&quot;单位&quot;,&quot;分成比例1&quot;:&quot;分成比例&quot;})</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data2=data2.rename(columns={&quot;单位2&quot;:&quot;单位&quot;,&quot;分成比例2&quot;:&quot;分成比例&quot;})</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data3=data3.rename(columns={&quot;单位3&quot;:&quot;单位&quot;,&quot;分成比例3&quot;:&quot;分成比例&quot;})</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">预览data3表</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data3.head()</span></div></div></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [11]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"178\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"281\\\"/></span></div></div><h3 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.4.2 纵向拼接分表</span></h3><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">通过以上重命名操作，三个分表列名已经一致，这时我们可以将三个表格纵向追加起来。纵向追加使用concat()函数，并使用参数ignore_index重置行索引。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4=pd.concat([data1,data2,data3],ignore_index=True)</span></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">预览合并后的表</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4.head()</span></div></div></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [12]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"175\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);\\\" width=\\\"267\\\"/></span></div><h2 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.5数据分组/透视</span></h2><h3 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.5.1空值处理</span></h3><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">此时利用info()返回的数据可以判断data4是否存在空值。从以下运行结果来看，data4数据表格共5019行，贷款金额及贷款用途都含有5019行非空值，说明者两列都没有空值出现。而单位及分成比例只有2041行数据为非空。其他行为空值。根据业务逻辑可知，如果单位列数据为空，则一定不存在分成比例，即：分成比例也为空。</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">那么该条记录就是无效的。</span><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">因此可以直接将其删除。使用dropna()函数进行空值处理。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4.info()</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">---</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">&lt;class 'pandas.core.frame.DataFrame'&gt;</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">RangeIndex: 5019 entries, 0 to 5018</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">Data columns (total 4 columns):</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">用途      5019 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">贷款金额    5019 non-null int64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">单位      2041 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">分成比例    2041 non-null float64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">dtypes: float64(1), int64(1), object(2)</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">memory usage: 156.9+ KB</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对空值进行删除</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4=data4.dropna() # 此处对不设置 how=&quot;all&quot;,因为此场景中只要出现空值，就将记录删除。从以下输出结果可知存在空值的记录已经被删除。</span></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">查看删除后表的信息</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4.info()</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">---</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">&lt;class 'pandas.core.frame.DataFrame'&gt;</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">Int64Index: 2041 entries, 0 to 3365</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">Data columns (total 4 columns):</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">用途      2041 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">贷款金额    2041 non-null int64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">单位      2041 non-null object</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">分成比例    2041 non-null float64</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">dtypes: float64(1), int64(1), object(2)</span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">memory usage: 79.7+ KB</span></div></div></div></div><h3 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.5.2插入新列</span></h3><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">接下来一步是计算分成贷款金额，即：我们需要插入一列，使其等于贷款金额列剩余分成比例。注意到分成比例并非百分比格式，我们需要将其转化为百分比（除以100）。插入新列可以使用insert()函数，也可以直接以索引的方式进行。为了演示，我们分别选择不同的方法插入百分比列及分成贷款金额列。</span></div><ul style=\\\"font-size: inherit; padding: 0px; border: 0px; vertical-align: baseline; margin: -1em 0px 1.5em 1.5em; list-style: disc;\\\"><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">使用insert()插入百分比列</span></div></li></ul><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4.insert(2,&quot;分成百分比&quot;,data4[&quot;分成比例&quot;]/100)</span></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对插入数据后的表进行预览</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4.head()</span></div></div></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [13]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"171\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"333\\\"/></span></div></div><ul style=\\\"font-size: inherit; padding: 0px; border: 0px; vertical-align: baseline; margin: 0px 0px 1.5em 1.5em; list-style: disc;\\\"><li style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; list-style-type: inherit; list-style-position: outside;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">使用普通索引方式插入分成贷款金额列</span></div></li></ul><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4[&quot;分成贷款金额&quot;]=data4[&quot;贷款金额&quot;]*data4[&quot;分成百分比&quot;]/10000 # 除以10000，将结果单位换算为万元</span></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">对插入数据后的表进行预览</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data4.head()</span></div></div></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit;\\\"><br/></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [14]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"173\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"417\\\"/></span></div></div><h3 style=\\\"font-size: 30px; margin: 35px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; text-align: left;\\\"><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 35px; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">3.5.3数据透视</span></h3><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">至此，数据清洗过程基本上已经完成了，接下来只需要对数据进行分组透视啦。这里还是遵循</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">排除干扰的原则</span><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">，先使用普通索引的方式提取需要用到的列，排除不必要的干扰。然后使用pivot_table()设置相关参数进行透视。</span></div><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: rgb(243, 242, 238); width: auto; font-size: 0.875em;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline;\\\"><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">data5=data4[[&quot;单位&quot;,&quot;用途&quot;,&quot;分成贷款金额&quot;]] </span></div><div><span style=\\\"color: rgb(31, 9, 9); font-family: Inconsolata; line-height: 1.71429em;\\\">pd.pivot_table(data5,values=&quot;分成贷款金额&quot;,columns=&quot;用途&quot;,index=&quot;单位&quot;,aggfunc='sum').fillna(0).reset_index() #将无投放数据的地方填充为0</span></div></div></div></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\"><div style=\\\"margin: 1em 0px 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [15]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"512\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"359\\\"/></span></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">至此，我们的任务就完成了。至于结果输出部分，我们可以选择直接复制黏贴到结果表上。当然也可以使用to_excel()将输出结果保存为excel文件。甚至我们还可以导入xlrd模块，直接对我们的日报表进行修改输出。这里就不多做演示了，请读者们自己动起手来。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">模型建立好以后，我们只需要将</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">最新的个人贷款客户信息表放置在E盘，覆盖旧的数据文件</span><span style=\\\"font-size: inherit; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em; font-weight: bold; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;\\\">。</span><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">然后按下图所示点击 Run All 执行以上代码就可以一键完成我们每天需要的日报了。</span></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [16]\\\" type=\\\"image/png\\\" data-filename=\\\"640\\\" height=\\\"369\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"457\\\"/></span></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">其实以上模型处理除了可以计算年累计投放数据以外，我们还可以通过修改日期筛选的范围。一键统计每日、每周或者每季度的报表。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">需要本篇数据集的可以去本书github里面随书数据集里面下载。</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">github地址：https://github.com/junhongzhang/Excel-Python-DA</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">End.</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">来源：张俊红</span></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">    </span></div><div style=\\\"font-size: inherit; margin: 1em 0px; padding: 0px; border: 0px; vertical-align: baseline; text-align: center;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\"><img src=\\\"3步搞定：Python报表自动化_files/640 [17]\\\" type=\\\"image/jpeg\\\" data-filename=\\\"640\\\" height=\\\"528\\\" style=\\\"margin:0px;padding:0px;border:0px;font:inherit;vertical-align:baseline;border-color:rgb(31, 9, 9);display:block;max-width:100%;\\\" width=\\\"576\\\"/></span></div><p style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;\\\"></p><div style=\\\"font-size: inherit; margin: 1.5em 0px 0px; padding: 1.5em 0px 0px; border: 0px; vertical-align: baseline; position: relative;\\\"><div style=\\\"font-size: inherit; position: absolute; left: 25%; top: 0px; z-index: 10; width: 50%; padding-left: 0px; padding-right: 0px; height: 0.1em; margin: 0px; opacity: 0.25; background: -webkit-linear-gradient(0deg, rgb(243, 242, 238) 1%, rgb(31, 9, 9) 50%, rgb(243, 242, 238) 99%);\\\"></div><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; position: relative;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; position: relative;\\\"><div style=\\\"margin: 0px; padding: 0px; border: 0px; font-size: inherit; vertical-align: baseline; position: relative;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">在看点这里</span></div></div></div></div></div></div></div><div style=\\\"font-size: inherit; margin: 0px 0px 1.5em; padding: 0px; border: 0px; vertical-align: baseline; position: absolute; top: -1000px; left: -1000px;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1.5em;\\\">Measure</span></div><div style=\\\"font-size: inherit; margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; position: absolute; top: -1000px; left: -1000px;\\\"><span style=\\\"font-size: 16px; color: rgb(31, 9, 9); font-family: &quot;PT Serif&quot;; line-height: 1;\\\">Measure</span></div></div></div></div></div></div></div></div><div><br/></div></span>\\\n</div></body></html>\\u0000\"]],\"start1\":0,\"start2\":0,\"length1\":0,\"length2\":66126}]"
metadata_diff: {"new":{"id":"b27fe1862ecf43b89e73859f7660353a","parent_id":"f67288c3a0be4374b5d532b5dcc3aabc","latitude":"0.00000000","longitude":"0.00000000","altitude":"0.0000","author":"","source_url":"","is_todo":0,"todo_due":0,"todo_completed":0,"source":"joplin-desktop","source_application":"net.cozic.joplin-desktop","application_data":"","order":1764348814997,"markup_language":2,"is_shared":0,"share_id":"","conflict_original_id":"","master_key_id":"","user_data":"","deleted_time":0},"deleted":[]}
encryption_cipher_text: 
encryption_applied: 0
updated_time: 2025-11-28T21:03:13.186Z
created_time: 2025-11-28T21:03:13.186Z
type_: 13