用python做数据分析:pandas的excel应用初探

大毛 岂安科技业务风险分析师 多年订单业务反欺诈经验,负责岂安科技多款产品运营工作。

上回笔者分享过一些 pandas 的常用数据查询语法,但是数据查询对于 pandas 而言只是冰山一角,它还有着更多更有价值的的应用场景。今天要分享的是,用 pandas 来写 excel。

我的数据分析工作经常是在这样的场景下开展的:数据格式五花八门,有些存储在关系型数据库内,有些则是 csv 或者是 json,而最后老板想要的数据报告是 excel 版本的。

在没有使用 pandas 处理数据以前,我会周旋在各个数据源之间,将取完的数之后黏贴到 excel 中,最终在统一在 excel 内进行处理。

这个操作有一些缺陷:

✪ 各个数据源取数方法不统一。自己掌握可以通过反复操作熟练,一旦教授给新人需要花更多时间。 ✪ 数据处理环节效率低下,易受干扰。在频繁的复制黏贴中,很难确保不受外界干扰,一旦恍神了,很难想起刚刚的取数的一些细节。并且过多的复制黏贴可能导致excel崩溃。 ✪ 问题排查难以溯源。仔细想来,excel承担了打草稿的功能,在多次复制黏贴之后,没有人会清楚记得每一步为什么复制黏贴。一旦数据报告有误,想要定位问题所在,常常需要从头开始复盘。

➨如果把数据的获取到处理全部交给 pandas 呢?

这样一来 excel 只负责最终呈现层面的功能。为了实现这些,除了 pandas 本身强大的数据分析功能之外,还得益于两点:

  1. pandas 良好的数据读取接口
  2. xlsxwriter

良好的数据读取接口

一旦 import pandas as pd 了之后,就可以任意的 pd.readjson / pd.readcsv / pd.read_sql 了,是不是很方便?

example.json
[{
      "teamName": "GoldenArch", 
      "distCode": 04, 
      "distArea": "Shanghai", 
      "month": 11, 
      "income": 16255, 
      "cost": 30250,
},
{
      "teamName": "OldFather", 
      "distCode": 02, 
      "distArea": "Beijing", 
      "month": 11, 
      "income": 135300, 
      "cost": 27200,
}]

结果

bigsec

csv,相对省力,甚至可以用 excel 直接打开处理,不过这种方式影响到了这个数据处理方案的一致性。举例略。

data_csv = pd.read_csv('example.csv')

结果和上面json结果结构是一致的。

sql,最复杂的一项,在 readsql 之前,你还需要关心数据库连接问题,和要处理的 sql 语句问题。 这里我遭遇的坑在后者,如果你打算用 readsql 打入 dataframe 的是一张大表,那么可以暂时放弃这个念头,因为在 read_sql 的过程中,虽然读表很快,但是写入 dataframe 的速度却受制于数据规模,个人建议是,如果人类没有耐心把这些数据一一读完,那么就不要打给 dataframe,至少在目前的 0.20 版本是这样。不过好在我们可以在语句上做处理,在 where 之后按需做一些 group 或者 limit。

#数据库连接部分
import pymysql
def getConn():
    connect_config = {
        'host':'0.1.0.1',
        'port':8888,
        'user':'myname',
        'password':'mypassword',
        'db':'mydb',
                'charset':'utf8'
    }
    conn = pymysql.connect(**connect_config)
    return conn
#实例化连接对象
conn = getConn()
#语句
sql = "select company, sum(totaAmount) from myTable where ... and ... group by company"
#最熟悉的语法
data_sql = pd.read_sql(sql,con=conn)

结果和上面 json 结果结构是一致的

这个环节最大的收益就是将所有来源的数据 dataframe 或者 series 化了,然后就可以统一用 pandas 功能来进行下一步数据处理工作。数据处理环节环节太庞大,本文不做描述。这里我们跳过了处理环节,直奔 excel。

xlsxwriter

这个包的作用就是用 python 语法来写 excel 文件,在把所有关心的数据都裁剪完成后,下一步就是把它们按需塞进 excel 中。 import xlsxwriter 之后,用三行代码就能用 python 创建一个 excel 文件。

workbook = xlsxwriter.Workbook('helloworld.xlsx')
worksheet = workbook.add_worksheet(‘made by xlsxwriter’)
workbook.close()

简单吧,看字面意思就能理解——先创建文件,再创建表单,最后关闭。这是官网的文档,但是却不完全适用目前的场景。因为,我们需要借助于 pandas 来写,而不是直接写。

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
writer = pd.ExcelWriter('example2.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

这也是官网的文档,这两种方法处理 excel 都可以,区别是前者是可以方便地指定打在哪些格子里,而后者是将数据作为一个整体的打入 excel,这里更推荐后者。如果是多个不同数据源或者不同意义的数据,可以在 toexcel 的时候,新增一些 Sheet 来分类数据,sheetname 参数,使得你能自如的掌控数据的内容。

bigsec

所谓的塞数据也就是把你最终处理完的 dataframe 或者 series 交给了 excel,用 sheet_name 来管理不同意义的数据。 取数工作一般是周期性的,在数据需求没有产品化之前,需要利用更高效的工具来压缩取数环节所耗费的时间,将更多的精力留给数据分析之后结论以及建议上,毕竟分析才是数据价值。 上述方案比 excel+ 复制黏贴来的高效且可靠,既统一了数据采集方式,又使得取数过程可视化且易于维护。成本则是你需要花一些时间阅读 pandas 和 xlsxwriter 的文档,而 python 环境的搭建几乎是零成本的。

  • xlsxwriter文档:http://xlsxwriter.readthedocs.io
  • pandas文档:http://pandas.pydata.org/pandas-docs/stable/api.html