使用计算机语言玩数理统计
专题——将Excel数据导入Python
专题——将Excel数据导入Python

专题——将Excel数据导入Python

本文涉及到openpyxl模块的内容在以下书目中有详细记载与说明:

《Python编程快速上手——让繁琐工作自动化》(ISBN:9787115422699)


实际情况中,我们会先把数据输入Excel中,然后再通过祖传复制粘贴的方法导入统计软件中进行分析,SPSS需要将所有的数据搞到1列,并定义组别编号;而Prism处理数据则以列为单位,因此若收集数据的模板为每列为1组则直接复制粘贴进工作表就可以了。当数据量大的时候,Prism可能还好,但SPSS就有些难受了。当我们数据量大的时候,自己一列一列将数据转置后再粘贴到Python中也是让人崩溃的事情。即便有人有耐心这样做,这种行为严重背离了计算机语言的设计初衷。

Python有些模块提供了将Excel中的内容导入Python的功能,常用的有openpyxl, xlrd, pandas。openpyxl是一个专门用来读取、编辑Excel的软件,主要功能侧重于Excel本身,可以很简便地精确到对某个单元格的读取,但对具体的行或列的读取则稍逊风骚;pandas模块则侧重于数据的灵活提取。二者通常相辅相成。xlrd通常用于读取.xls格式的文件,多少有些过时了。

我们仍然通过一段材料开启我们的实操:(废话开始)前面建立数据框的专题中我们提到(内链),国家授时中心捐给某班级一台原子钟,结合教务处的学风整顿活动,同学们迟到的情况大为缓解。今天教务处某职员在练习Python处理数据时苦于没有现成的样本,于是突发奇想,决定调取今日A1班同学们的迟到情况并将其作为自己练手的材料。已知A1班一共45名学生,分成了5组,每组9名学生。该职员决定分析各个组迟到情况有无统计学差异。该班级迟到的情况如下:

文件下载链接:github 百度网盘(提取码:1234)

我们一起帮这位职员将这个Excel表格中的数据导入Python中试一下。

一、通过openpyxl模块读取Excel数据

这篇文章对openpyxl处理Excel表格时常用的功能总结的很到位:参考链接

openpyxl中有个方法load_workbook()适用于读取Excel文件,传入的参数为该Excel存放的路径。但通常Excel可以包含多个工作表(sheet),可通过用加上用中括号框起来工作表名称的方法来读取具体的工作表。

若该Excel文件包含多个工作表,当不确定选取哪个工作表时,可以使用.sheetnames方法来实现查阅该文件所有工作表名字的目的。

假设以上学生迟到时间的数据直接保存在D盘根目录,我们通过以下代码可以读取该工作表:

import openpyxl
work_book = openpyxl.load_workbook(r"D:\ruizoneteach.xlsx")
print(work_book.sheetnames)
>['Sheet1']
sheet_data = work_book["Sheet1"]
print(sheet_data)
><Worksheet "Sheet1">
print(type(sheet_data))
><class 'openpyxl.worksheet.worksheet.Worksheet'>

可以看到,虽然我们读取了该工作表,但数据并非为我们所用,因为openpyxl读取的数据类型并非数据框。前面我们提到,openpyxl对单个单元格的支持很好,但对整行或整列数据的调取则显得不那么友好。首先我们学习一下openpyxl如何读取单元格:

cell_value = sheet_data.cell(row=r, column=c).value  # r,c分别代表该单元格处于工作表的第几行、第几列。

在本案例中,我们需要读取第1行从第二个单元格开始到最后一个单元格并转换为列表,作为分组名称;读取包含每一列从第2个单元格到第10个单元格的数据并转换为列表,作为每组的样本。然后将五个组迟到的数据全部装进一个字典中。

在开始组装大数据之前,我们先确定以下该表格的规格(数据有多少行,多少列):

row_number = sheet_data.max_row
column_number = sheet_data.max_column

接下来我们开始组装迟到数据大字典。Python实现代码如下(前方高能):

group_name = [sheet_data.cell(row=1, column=i).value for i in range(2, column_number+1)]
late_data_dict = {}
for name in group_name:
    late_data_dict[name] = [sheet_data.cell(row=i, column=2).value for i in range(2, row_number+1)]

于是我们得到了包含每一组每一位同学迟到数据的字典,运行一下print(late_data_dict)大概是这样的:

{'第一组': [-0.73052207, 1.72710918, -0.55085448, 0.66957831, 0.72203906, -0.40541355, -1.78305816, 0.70825785, 0.25776012], '第二组': [-0.73052207, 1.72710918, -0.55085448, 0.66957831, 0.72203906, -0.40541355, -1.78305816, 0.70825785, 0.25776012], '第三组': [-0.73052207, 1.72710918, -0.55085448, 0.66957831, 0.72203906, -0.40541355, -1.78305816, 0.70825785, 0.25776012], '第四组': [-0.73052207, 1.72710918, -0.55085448, 0.66957831, 0.72203906, -0.40541355, -1.78305816, 0.70825785, 0.25776012], '第五组': [-0.73052207, 1.72710918, -0.55085448, 0.66957831, 0.72203906, -0.40541355, -1.78305816, 0.70825785, 0.25776012]}

这个字典的形式有没有很眼熟呢?接下来就可以用上一章《使用Python创建可供统计分析的数据框》的方法创建一个可以用于统计分析的数据框就可以了。

二、通过pandas模块读取Excel数据

从上一部分来看,使用openpyxl模块读取Excel数据的工程量蔚为壮观。主要是因为openpyxl模块没有专门读取行或列的方法。相比之下,pandas模块在这一方面做的要好得多(参考链接)。

目前我还没找到一个很好的能把Excel中的数据直接变成数据框的方法,但能够逐行或逐列读取已经好太多了。以我们这次提供的数据为例,首先我们使用pandas模块读取我们的Excel表格:

import pandas as pd
df = pd.read_excel(file)  # file为Excel文件保存的目录。可以通过os模块的chdir()方法直接定义工作目录

通过pandas读取的Excel表格df直接就是数据框的形式。但这个数据框还不能直接用于统计分析,需要对其中的内容进行编辑。需要注意的是,pd.read_excel()方法默认读取的Excel是不包含首行的(即默认将首行当成表头)。表头的读取可以使用df.keys().values的方法。

读取具体的列,可以直接在read_excel()方法后面跟上[列名],若想获取列表,则在[列名]后面加.values即可。我们试着读取本案例中第一组的迟到数据:

print(df["第一组"].values)
>[-0.73052207 1.72710918 -0.55085448 0.66957831 0.72203906 -0.40541355 -1.78305816 0.70825785 0.25776012]
print(type(df["第一组"].values))
><class 'numpy.ndarray'>
print(list(df["第一组"].values)
>[-0.73052207, 1.72710918, -0.55085448, 0.66957831, 0.72203906, -0.40541355, -1.78305816, 0.70825785, 0.25776012]

可以发现,读取的每一列数据类型直接就是数组,该数组可以用list()转换成列表。

接下来的任务就比较简单了,我们可以用循环将每一列的数据读出,然后按照 《使用Python创建可供统计分析的数据框》 的方法转换成数据框。

读好的数组可以直接通过某种思路转换成可供统计分析使用的数据框吗?目前我还不太清楚,回头是要好好研究一下。

2条评论

发表评论

您的电子邮箱地址不会被公开。