pandas处理Excel数据的应用

一、安装环境：

 1：pandas依赖处理Excel的xlrd模块，所以我们需要提前安装这个，安装命令是：pip install xlrd

  2:安装pandas模块还需要一定的编码环境，所以我们自己在安装的时候，确保你的电脑有这些环境：Net.4 、VC-Compiler以及winsdk_web，如果大家没有这些软件~可以咨询我们的辅导员索要相关安装工具。

  3:步骤1和2 准备好了之后，我们就可以开始安装pandas了，安装命令是：pip install pandas

一切准备就绪，就可以开始愉快的玩耍咯！
ps:在这个过程中，可能会遇到安装不顺利的情况，万能的度娘有N种解决方案，你这么大应该要学着自己解决问题。

二、pandas操作Excel表单

数据准备，有一个Excel文件：lemon.xlsx有两个表单，表单名分别为：Python 以及student，

Python的表单数据如下所示：

student的表单数据如下所示：

1：在利用pandas模块进行操作前，可以先引入这个模块，如下：

import  pandas  as pd

当一个excel文件的sheet比较多时候, 这时候需要获取所有的sheet的名字. 

xl = pd.ExcelFile('foo.xls')

xl.sheet_names  # see all sheet names

xl.parse(sheet_name)  # read a specific sheet to DataFrame

2:读取Excel文件的两种方式：

#方法一：默认读取第一个表单
df=pd.read_excel('lemon.xlsx')#这个会直接默认读取到这个Excel的第一个表单
data=df.head()#默认读取前5行的数据
print("获取到所有的值:
{0}".format(data))#格式化输出

得到的结果是一个二维矩阵，如下所示：

#方法二：通过指定表单名的方式来读取
df=pd.read_excel('lemon.xlsx',sheet_name='student')#可以通过sheet_name来指定读取的表单
data=df.head()#默认读取前5行的数据
print("获取到所有的值:
{0}".format(data))#格式化输出

#方法三：通过index的方式来读取，默认为0第一张表，一次往后加一
df=pd.read_excel('lemon.xlsx',sheet_name=1)#可以通过sheet_name来指定读取的表单
data=df.head()#默认读取前5行的数据
print("获取到所有的值:
{0}".format(data))#格式化输出

得到的结果如下所示，也是一个二维矩阵：

#方法三：通过表单索引来指定要访问的表单，0表示第一个表单
#也可以采用表单名和索引的双重方式来定位表单
#也可以同时定位多个表单，方式都罗列如下所示
df=pd.read_excel('lemon.xlsx',sheet_name=['python','student'])#可以通过表单名同时指定多个
# df=pd.read_excel('lemon.xlsx',sheet_name=0)#可以通过表单索引来指定读取的表单
# df=pd.read_excel('lemon.xlsx',sheet_name=['python',1])#可以混合的方式来指定
# df=pd.read_excel('lemon.xlsx',sheet_name=[1,2])#可以通过索引 同时指定多个
data=df.values#获取所有的数据，注意这里不能用head()方法哦~
print("获取到所有的值:
{0}".format(data))#格式化输出

具体结果是怎样的，同学们可以自己一个一个的去尝试，这个结果是非常有意思的，但是同时同学们也发现了，这个数据是一个二维矩阵，对于我们去做自动化测试，并不能很顺利的处理，所以接下来，我们就会详细的讲解，如何来读取行号和列号以及每一行的内容以及制定行列的内容。

三、pandas操作Excel的行列

1：读取指定的单行，数据会存在列表里面

#1：读取指定行
df=pd.read_excel('lemon.xlsx')#这个会直接默认读取到这个Excel的第一个表单
data=df.ix[0].values#0表示第一行 这里读取数据并不包含表头，要注意哦！
# if ix raise Exception, change ix to loc it will be work.
data=df.loc[0].values#0表示第一行 这里读取数据并不包含表头，要注意哦!
print("读取指定行的数据：
{0}".format(data))

2：读取指定的多行，数据会存在嵌套的列表里面：

df=pd.read_excel('lemon.xlsx')
data=df.ix[[1,2]].values#读取指定多行的话，就要在ix[]里面嵌套列表指定行数
print("读取指定行的数据：
{0}".format(data))

3：读取指定的行列：

df=pd.read_excel('lemon.xlsx')
data=df.ix[1,2]#读取第一行第二列的值，这里不需要嵌套列表
print("读取指定行的数据：
{0}".format(data))

4：读取指定的多行多列值：

df=pd.read_excel('lemon.xlsx')
data=df.ix[[1,2],['title','data']].values#读取第一行第二行的title以及data列的值，这里需要嵌套列表
print("读取指定行的数据：
{0}".format(data))

5：获取所有行的指定列

df=pd.read_excel('lemon.xlsx')
data=df.ix[:,['title','data']].values#读所有行的title以及data列的值，这里需要嵌套列表
print("读取指定行的数据：
{0}".format(data))

6：获取行号并打印输出

df=pd.read_excel('lemon.xlsx')
print("输出行号列表",df.index.values)

输出结果是：
输出行号列表 [0 1 2 3]

7：获取列名并打印输出

df=pd.read_excel('lemon.xlsx')
print("输出列标题",df.columns.values)

运行结果如下所示：
输出列标题 ['case_id' 'title' 'data']

8：获取指定行数的值：

df=pd.read_excel('lemon.xlsx')
print("输出值",df.sample(3).values)#这个方法类似于head()方法以及df.values方法

输出值
 [[2 '输入错误的密码' '{"mobilephone":"18688773467","pwd":"12345678"}']
 [3 '正常充值' '{"mobilephone":"18688773467","amount":"1000"}']
 [1 '正常登录' '{"mobilephone":"18688773467","pwd":"123456"}']]

9：获取指定列的值：

df=pd.read_excel('lemon.xlsx')
print("输出值
",df['data'].values)

四：pandas处理Excel数据成为字典

我们有这样的数据，，处理成列表嵌套字典，且字典的key为表头名。

实现的代码如下所示：

df=pd.read_excel('lemon.xlsx')
test_data=[]
for i in df.index.values:#获取行号的索引，并对其进行遍历：
    # 根据i来获取每一行指定的数据 并利用to_dict转成字典
    # col = df.columns.value
    # row_data=df.ix[i,col].to_dict()
    row_data=df.ix[i,['case_id','module','title','http_method','url','data','expected']].to_dict()
    test_data.append(row_data)
print("最终获取到的数据是：{0}".format(test_data))

最后得到的结果是：

最终获取到的数据是：
[{'title': '正常登录', 'case_id': 1, 'data': '{"mobilephone":"18688773467","pwd":"123456"}'}, 
{'title': '输入错误的密码', 'case_id': 2, 'data': '{"mobilephone":"18688773467","pwd":"12345678"}'}, 
{'title': '正常充值', 'case_id': 3, 'data': '{"mobilephone":"18688773467","amount":"1000"}'}, 
{'title': '充值输入负数', 'case_id': 4, 'data': '{"mobilephone":"18688773467","amount":"-100"}'}]

sheetname：默认是sheetname为0，返回多表使用sheetname=[0,1]，若sheetname=None是返回全表。注意：int/string返回的是dataframe，而none和list返回的是dict of dataframe。

df = pd.read_excel(xls, sheet_name=[0], keep_default_na='')

df -> (0, df)
dict(df) -> {0:df}

使用pandas将数据写入csv或xlsx

import pandas as pd
result_list = [['1', 1, 1], ['2', 2, 2], ['3', 3, 3]]
columns = ["URL", "predict", "score"]
dt = pd.DataFrame(result_list, columns=columns)
dt.to_excel("result_xlsx.xlsx", index=0)
dt.to_csv("result_csv.csv", index=0)

Pandas 读取 Excel 文件的引擎是 xlrd ， xlrd 虽然同时支持 .xlsx 和 .xls 两种文件格式，但是在源码文件 xlrd/sheet.py 中限制了读取的 Excel 文件行数必须小于 65536，列数必须小于 256。
源码 >>>:
if self.biff_version >= 80:
    self.utter_max_rows = 65536
else:
    self.utter_max_rows = 16384
self.utter_max_cols = 256


这就导致，即使是 .xlsx 格式的文件， xlrd 依然不支持读取 65536 行以上的 Excel 文件（源码中还有一个行数限制是 16384，这是因为 Excel 95 时代， xls 文件所支持的最大行数是 16384）。

解决办法
openpyxl 是一个专门用来操作 .xlsx 格式文件的 Python 库，和 xlrd 相比它对于最大行列数的支持和 .xlsx 文件所定义的最大行列数一致。
首先安装 openpyxl ：

pip install openpyxl
Pandas 的 read_excel 方法中，有 engine 字段，可以指定所使用的处理 Excel 文件的引擎，填入 openpyxl ，再读取文件就可以了。


import os
import pandas as pd

# 将文件读取出来放一个列表里面

pwd = '1'  # 获取文件目录

# 新建列表，存放文件名
file_list = []

# 新建列表存放每个文件数据(依次读取多个相同结构的Excel文件并创建DataFrame)
dfs = []

for root,dirs,files in os.walk(pwd):  # 第一个为起始路径，第二个为起始路径下的文件夹，第三个是起始路径下的文件。
    for file in files:
        file_path = os.path.join(root, file)
        file_list.append(file_path)  # 使用os.path.join(dirpath, name)得到全路径
        df = pd.read_excel(file_path)  # 导入xlsx文件，将excel转换成DataFrame
        dfs.append(df)
        


# 将多个DataFrame合并为一个
df = pd.concat(dfs)

# 数据输出，写入excel文件，不包含索引数据
# 数据写入 Excel，需要首先安装一个 engine，由 engine 负责将数据写入 Excel，pandas 使用 openpyx 或 xlsxwriter 作为写入引擎。
df.to_excel('test\1.xlsx', index=False,engine='openpyxl') # 导出 Excel，一般不需要索引，将index设置为False, 如果没有包直接pip3 install openpyxl

openpyxl.utils.exceptions.IllegalCharacterError

在使用pandas写excel的时候，可能会出现openpyxl.utils.exceptions.IllegalCharacterError的提示错误 。根据提示可以知道是openpyxl模块中的错误。因此需要到相应的代码查看原因，进入python命令行模式，输入如下：

>>> import sys
>>> help('openpyxl')

可得openpyxl模块的路径如下/usr/local/lib/python2.7/site-packages/openpyxl，查看该目录下的cell子目录中的cell.py文件，定位到具体错误代码为：

def check_string(self, value):
    """Check string coding, length, and line break character"""
    if value is None:
        return
    # convert to unicode string
    if not isinstance(value, unicode):
        value = unicode(value, self.encoding)
    value = unicode(value)
    # string must never be longer than 32,767 characters
    # truncate if necessary
    value = value[:32767]
    if next(ILLEGAL_CHARACTERS_RE.finditer(value), None):
        raise IllegalCharacterError
    return value

其中ILLEGAL_CHARACTERS_RE的定义在文件的开头，如下：

ILLEGAL_CHARACTERS_RE = re.compile(r'[00-10]|[13-14]|[16-37]')

这里面的非法字符都是八进制，可以到对应的ASCII表中查看，的确都是不常见的不可显示字符，例如退格，响铃等，在此处被定义为excel中的非法字符。
解决上述错误有两种方法，如下：
1，既然检测到excel中存在[00-10]|[13-14]|[16-37]这些非法的字符，因此可以将字符串中的非法字符替换掉即可，在重新写入excel即可。如下：

text= ILLEGAL_CHARACTERS_RE.sub(r'', text)

2，使用xlsxwriter

import xlsxwriter
outputData.to_excel(outputExcelFilePath, engine='xlsxwriter')
# outputData是一个DataFrame，在 xlsxwriter应该也是替换掉非法字符，省去了我们手工操作。