实用技巧！盘点Python和Excel交互的常用操作

在以前，商业分析对应的英文单词是Business Analysis，大家用的分析工具是Excel，后来数据量大了，Excel应付不过来了（Excel最大支持行数为1048576行），人们开始转向python和R这样的分析工具了，这时候商业分析对应的单词是Business Analytics。

其实python和Excel的使用准则一样，都是[We don't repeat ourselves]，都是尽可能用更方便的操作替代机械操作和纯体力劳动。

用python做数据分析，离不开著名的pandas包，经过了很多版本的迭代优化，pandas现在的生态圈已经相当完整了，官网还给出了它和其他分析工具的对比：

本文用的主要也是pandas，绘图用的库是plotly，实现的Excel的常用功能有：

• Python和Excel的交互

• vlookup函数

• 数据透视表

• 绘图

以后如果发掘了更多Excel的功能，会回来继续更新和补充。开始之前，首先按照惯例加载pandas包：

import numpy as npimport pandas as pdpd.set_option('max_columns', 10)pd.set_option('max_rows', 20)pd.set_option('display.float_format', lambda x: '%.2f' % x) # 禁用科学计数法

Python和Excel的交互

pandas里最常用的和Excel I/O有关的四个函数是read_csv/ read_excel/ to_csv/ to_excel，它们都有特定的参数设置，可以定制想要的读取和导出效果。

比如说想要读取这样一张表的左上部分：

可以用pd.read_excel("test.xlsx", header=1, nrows=17, usecols=3)，返回结果：​​​​​​​

dfOut[]:        工号   姓名 性别  部门0   A0001   张伟  男  工程1   A0002  王秀英  女  人事2   A0003   王芳  女  行政3   A0004   郑勇  男  市场4   A0005   张丽  女  研发5   A0006   王艳  女  后勤6   A0007   李勇  男  市场7   A0008   李娟  女  工程8   A0009   张静  女  人事9   A0010   王磊  男  行政10  A0011   李娜  女  市场11  A0012  刘诗雯  女  研发12  A0013   王刚  男  后勤13  A0014   叶倩  女  后勤14  A0015  金雯雯  女  市场15  A0016  王超杰  男  工程16  A0017   李军  男  人事

输出函数也同理，使用多少列，要不要index，标题怎么放，都可以控制。

vlookup函数

vlookup号称是Excel里的神器之一，用途很广泛，下面的例子来自豆瓣，VLOOKUP函数最常用的10种用法，你会几种?

案例一

问题：A3:B7单元格区域为字母等级查询表，表示60分以下为E级、60~69分为D级、70~79分为C级、80~89分为B级、90分以上为A级。D:G列为初二年级1班语文测验成绩表，如何根据语文成绩返回其字母等级？

方法：在H3:H13单元格区域中输入=VLOOKUP（G3, $A$3:$B$7, 2）

python实现：​​​​​​​

df = pd.read_excel("test.xlsx", sheet_name=0)def grade_to_point(x):    if x >= 90:        return 'A'    elif x >= 80:        return 'B'    elif x >= 70:        return 'C'    elif x >= 60:        return 'D'    else:        return 'E'
df['等级'] = df['语文'].apply(grade_to_point)df
Out[]:      学号   姓名 性别   语文 等级0   101  王小丽  女