记一次Python操作Excel——从入门到上手

本文链接：https://blog.csdn.net/linghuanxu/article/details/126111735

前言

由于一直从事软件开发，所以，几乎没有处理过复杂的excel，用用公式和函数就不得了了。导致我对使用excel操作数据的能力几乎为零。由于平常处理数据几乎都是通过数据的sql语句进行的，所以没有什么动力去深入excel。直到最近。
最近我遇见了一个需要我分析的数据。这是公司的性能数据。我有每个接口的延迟和调用次数。但是，我需要对一段时间内它的表现进行分析和评估，并对每个服务及产品线聚合出一份有价值的参考数据。第一个版本我是找公司的PMO帮我处理的。虽然效果也有了，但是，我想做什么尝试，就得先定义清楚，再跟他解释，再等他有空帮我处理。每个月定期出数据也得等他帮我做出来，很是麻烦。所以，我下定决心，攻克使用python处理excel这一关——这几年晚上听得很多，说python处理excel好用又好学，况且编程我可是专业的。

聊聊Python处理Excel

最开始我买的一本书叫《超简单用Python让Excel飞起来》——本人学新东西喜欢搞本书系统得学。书中内容确实很容易上手，对于使用Python操作Excel以及对数据进行处理和简单的分析也都好上手。要说缺点吧，就是对分析数据的工具NumPy讲解得不够系统，更多的还是以典型的场景带动出来的。使用这本书学出来的，很难自己思考进行更深入的操作。因为你学的时候就是用啥学习啥，而不是先搞明白工具的逻辑，在搞明白为啥这么用工具。好处也有，就是不用先搞明白工具的逻辑以及为啥这么用工具，就能解决很多实际中遇到的问题。所以，这本书适合上手，但是还需要后面的深入。
后面搞了本书较《NumPy数据处理详解》蛮厚的一本小书。对NumPy的使用讲解得很系统。由于有了之前那本书的基础，这本书我基本就是在翻阅我需要的功能，顺便也把这本书顺序的过了以便，搞清楚了用NumPy处理数据由浅入深的逻辑在哪里。总体来说，还是受益匪浅的。

聊聊这次的案例

先说表头，我原始表的表头是：

中心名称：若干个服务，构成了后台服务中一个完整的概念，我们称之为中心，如产品中心、订单中心
服务名：一个后台服务我们称之为一个服务，它的名字就是服务名。不同的服务，服务名不同。
接口URL：具体的url
95%响应时间（ms）：需要说明的是我的原始数据分为响应时间和调用次数两张表。这个在响应时间的表里。
调用次数：这个在负载的表里

原始数据总共是四张表，分别是6月延迟表，6月负载表，7月延迟表和7月负载表。我们这次分析的目标是提供一个现阶段性能的表现以及对6、7月的环比分析。

总体目标

结果分为两个：

6月、7月的性能情况。
6月、7月的环比情况。

关于接口性能的比较我是这样设计指标的

我们使用P95作为接口性能指标的评估。超过1s的即为性能不达标。
众多接口性能不达标的话，我们综合根据接口的使用频次和P95延迟判断优化接口优先级。为了进行这个比较，我设计了一个指标，我称之为性能指数=调用次数*P95/1000/1000。这个指标最后除以两个1000是为了让它变得小一些。从绝对值上来说，这个指标不说明任何问题。但是，对于性能超标接口来说，它们提供了需要优化的紧迫程度的参考值。
关于服务和产品线的综合值，我才用了以调用次数为权重的加权平均数。

关于接口性能环比，我们需要除了性指标以外的环比变化量以及环比变化率。
综上所述，结果会是三张表。但是，由于我在环比数据中保留了原始数据，所以，结果就变成了环比数据一张表。

关键步骤

单月性能数据

首先，我需要把每个月的数据弄出来，作为后续环比的基础数据。这一步的数据处理是比较简单的，但是也会涉及全流程的基础操作，算是一个基础版的学习吧。

引用文件

是的，这里我先讲下怎么引用文件。python肯定是可以引用自己封装好的重用组件的。其实也很简单。就是import语法。

import pandas as pd
from sqlalchemy import null
import xlwings as xw

'''
加载指定文件workbook
'''
def loadWorkbook(path):
    app=xw.App(visible=False,add_book=False)
    workbook=app.books.open(path)
    return workbook

上面是我写的一个重用的python文件，里面是加载一个工作簿的方法，文件名叫：nature_excel_tools。
同文件夹的引用如下：

import nature_excel_tools as nt

子文件夹的引用也是一样的。这个其实蛮奇怪的，不过也蛮好用的。

读取excel

import pandas as pd
def loadDataFrame(path,sheetIndex):
    sheetData=pd.read_excel(path,sheet_name=sheetIndex)
    return sheetData

我们这里是使用的pandas加载制定的sheet页。这个序号是从0开始的。加载的结果就是一个DataFrame。很好用。
有我原始数据的数据集是将所有的数据按照不同的系统分割开来了，所以需要一次加载所有sheet页并合并成一个DataFrame。源码如下：

import pandas as pd
from sqlalchemy import null
import xlwings as xw
'''
加载指定文件workbook
'''
def loadWorkbook(path):
    app=xw.App(visible=False,add_book=False)
    workbook=app.books.open(path)
    return workbook
'''
读取文件指定sheet页到DataFrame
'''
def loadDataFrame(path,sheetIndex):
    sheetData=pd.read_excel(path,sheet_name=sheetIndex)
    return sheetData

'''
读取指定文件，并合并所有sheet页到一个DataFrame
需要所有sheet页的列相同
'''
def loadAllSheetToDataFrame(path,columns = null):
    workbook=loadWorkbook(path)
    workbookData=[]
    for item in workbook.sheets:
        sheetData=loadDataFrame(path,item.index-1)
        # print(sheetData)
        workbookData.append(sheetData)
    print('加载Sheet成功，数量：'+str(workbook.sheets.count))
    finalData=pd.concat(workbookData)
    # print(columns)
    # print(finalData)
    if columns==null:
        return finalData
    else:
        return finalData[columns]

这里除了pandas以外我还引用了xlwings。主要原因是我需要先预先读取所有的sheet然后再依次加载所有的sheet页。我记得曾经尝试过其它的方法数据有问题，就保留成了这个样子。后面好像看到pandas也可以直接读取所有sheet，后面我研究通了再回来替换。

数据拼接

DataFrame的数据拼接也分好几种情况。这里以我们的需要来进行。我这里读取了两个excel的数据集，分别是负载和延迟的。我需要将它们根据相同的url合并起来。在数据库，其实就是join。而因为有些数据是对不上的，所以我直接用innerjoin去除数据不全的部分。具体到代码上就是：

result= pd.merge(loadData,responseTimeData,on='接口URL',how='inner')

merge是一个pandas的操作，前面两个参数是需要连接的数据集，on是连接的列，how是指明的join的类型。
完了之后，我们把无效的数据，也就是数据补全的剔除掉

result=result.dropna(axis=0,how='any')

计算每行性能指数

性能指数这个指标是：调用次数*延迟毫秒数/1000/1000。最后除以两个一千是为了让它们不会太大。所以，对性能指数的计算，本质上是使用行数据中的不同列计算出一个新的值，而每个值都需要与相关的行对应。先上代码：

loadCount=result['调用次数']
responseTime=result['95%响应时间(ms)']
performanceIndex=loadCount*responseTime/1000000  # 性能指数

这三行代码其实很好理解，我们直接取了调用次数和响应时间的两列。然后直接按照公式进行计算。得到的结果就是与原序号相同的结果值，就是我们的性能指数。接下来，就是如何将它们合并会原表格了：

performanceIndex.name='性能指数'
result=pd.concat([result,performanceIndex],axis=1)

可以看到也很简单，就两步。第一步，给这列设置个名字；第二步，使用concat方法合并到原数据集中。axis=1是特指这是一列，这个参数默认是0，指待的是行。

数据分组统计

每行的性能指数搞定后，我还需要按照系统以及按照产品线对数据进行统计。
这个其实也很简单，主要有两个关键点：

数据要分组
分组后的数据要被遍历并统计。
talk is cheap,我们上代码：

grouped=result.groupby('服务名')

serverData=[]
for name,group in grouped:
    row=[name,group['中心名称'].max(),group['性能指数'].sum()/group['调用次数'].sum()*1000]
    serverData.append(row)

serverDataTable=pd.DataFrame(serverData,columns=['服务名','中心名称','服务综合指数'])

第一句，就是分组，非常简单，分组后的数据，北航都会有个数据集。
我们定义的serverData是一个数组，用来临时存放计算的结果。基础的数据类型用起来要简单点。
然后我们遍历整个分组后的数据，for里面的name，就是你分组的那个内容，group是分组后的数据。我们循环遍历添加行，就弄出来了基础的数据。
最后，就用这个数组初始化一个DataFrame，就结束了。
产品线的与此类似，就不再写了。

分sheet写excel

刚开始我用的是xlwings写excel，不是很方便。pandas提供了很好的写excel的方法，代码如下：

with pd.ExcelWriter(destFileName) as writer:
    result.to_excel(writer, sheet_name='url数据',index=False)
    serverDataTable.to_excel(writer, sheet_name='中心数据',index=False)
    pass

上面的代码一次写了两个sheet页，sheet页的顺序是依次从上到下的。中间设置index=False是不将index输出为数据，否则第一列就会是序号了，没啥用。

清洗数据

其实后面还有个计算环比数据的内容打算写。但是，我缕了一下，其实和上面的过程没有多大差别，也就没什么意思了。只是计算环比数据的过程，有个之前没有的环节，叫清洗数据。这个其实蛮重要的，先看代码：

def cleanData(df):
    # 对缺失值补0
    df=df.fillna(value=0)
    # 删除前后负载均为0的数据
    df=df.drop(df[(df[loadColName+beforeTag]==0)&(df[loadColName+afterTag]==0)].index)
    # 延迟为0的数据调整为1
    df.loc[df[performColName+beforeTag]==0,performColName+beforeTag]=1
    df.loc[df[performColName+afterTag]==0,performColName+afterTag]=1

    return df