Python基础与办公自动化（微课版）（AI协同）-课件 单元7、8 数据处理、Python教你玩转Excel文件

单元7数据处理任务7.1导入Excel文件数据【任务目标】文件“data1.xlsx”中的工作表“Sheet1”保存了“唐宋八大家”的相关信息，使用pandas导入文件数据，并输出前3行数据。【任务实施】……7.1数据处理基础创建数据对象数据的基本运算数据处理相关的属性和方法数据的索引和切片数据的导入和导出安装pandas。在程序中，可使用以下命令导入pandas。pandas的Series对象和DataFrame对象是数据处理过程中两个重要的数据结构。Series对象也称“序列”，用于存储和处理一维数据，类似Excel二维表格中的一行或一列。Series对象由一组数据和与数据相关的索引组成，可以存储和处理整数、浮点数、字符串、Python对象等多种类型的数据。DataFrame对象也称“数据框”，用于存储和处理二维数据，类似Excel二维表格。DataFrame对象由一组数据以及与数据相关的行索引和列索引组成，可以存储和处理整数、浮点数、字符串、Python对象等多种类型的数据。7.1.1创建数据对象1．创建Series对象Series()方法用于创建Series对象，其基本语法格式如下。其中，相关参数说明如下。（1）data：数据，可以是标量、可迭代对象（如列表）、字典等。（2）index：索引，默认以0开始的整数作为索引。省略所有参数时，该方法将创建一个空Series对象。示例代码单击此处编辑母版文本样式第二级第三级第四级第五级2．创建DataFrame对象DataFrame()方法用于创建DataFrame对象，其基本语法格式如下。其中，相关参数说明如下。（1）data：数据，可以是字典、二维数组、Series对象、DataFrame对象或其他可转换为DataFrame对象的数据。省略此参数，则创建一个空DataFrame对象。（2）index：行索引，可以是列表、数组、索引对象等。省略此参数，则创建一个默认的整数索引。（3）columns：列索引，可以是列表、数组、索引对象等。省略此参数，则创建一个默认的整数索引。（4）dtype：指定数据的类型，可以是NumPy库中的数据类型，如64、np.float64等。省略此参数，则根据数据自动推断数据类型。（5）copy：指定是否复制数据，默认值为False，表示不复制数据。如果设置为True，则复制输入的数据。示例代码7.1.2数据的基本运算Series对象和DataFrame对象支持各种数据运算，示例代码如下。7.1.3数据处理相关的属性和方法1．Series对象的常用属性和方法2．DataFrame对象的常用属性和方法7.1.4数据的索引和切片Series对象和DataFrame对象支持索引和切片，通过索引可访问和修改元素。1．访问和修改Series对象元素2．访问和修改DataFrame对象元素7.1.5数据的导入和导出1．导入数据pandas常用的数据导入方法如下。（1）read_csv()：导入CSV文件。（2）read_table()：导入分隔符分隔的文件，默认分隔符为制表符“\t”。（3）read_excel()：导入Excel文件。（4）read_json()：读取JSON文件。（5）read_sql()：从SQL数据库读取数据。（6）read_html()：从HTML文件中读取表格数据。这些方法的用法类似，2．导出数据to_excel()方法可以将数据导出到Excel文件，示例代码如下。导出的“output1.xlsx”工作簿数据如图7-1所示。DataFrame对象的列索引作为Excel表格的列名，行索引作为第1列数据（该表格第1列第1行数据为空）。可以将数据导出到多个工作表，示例代码如下。to_csv()方法可以将数据导出到CSV文件，示例代码如下。任务7.2成绩排序【任务目标】文件“data1.xlsx”中的工作表“Sheet3”保存了成绩数据（包括学号、姓名、政治、数学、英语等列），使用pandas导入数据，计算成绩总分，并按总分从高到低排序。【任务实施】……7.2数据处理操作本节介绍pandas的数据处理功能，包括数据的清洗、筛选、排序、排名、合并、连接、分组统计、透视表创建以及绘图等操作。7.2.1数据清洗数据清洗指对数据中的无效值、重复值等进行处理。1．处理无效值在导入数据时，pandas将n/a、NA、空值当作无效数据，用NaN表示。文件“data2.xlsx”中包含多个无效值，如图7-3所示。导入文件“data2.xlsx”中的数据，示例代码如下。除了默认处理的无效值，上述导入数据中的8t、-也属于不符合要求的无效值。可以在导入时，使用na_values参数指定需要额外处理的无效值，示例代码如下。可以使用isnull()方法判断数据中的元素是否属于无效值，示例代码如下。dropna()方法可以删除包含无效值的行，示例代码如下。fillna()方法可以用指定值替换无效值，示例代码如下。2．处理重复值可以使用duplicated()方法和drop_duplicates()方法处理重复数据。duplicated()方法用于检测是否存在重复数据，若存在重复数据，则返回True，否则返回Falsedrop_duplicates()方法用于删除重复数据。示例代码如下。7.2.2数据筛选DataFrame对象可以按行（列）索引筛选数据，也可以按条件筛选数据。1．按行（列）索引筛选数据loc属性可按标签索引返回数据，其基本语法格式如下。行（列）标签可以是单个值，也可以是用列表表示的多个值。省略列标签时，包含所有列。使用数字索引时，数字也作为标签。前面介绍过使用数字索引访问和修改元素的方法，下面介绍使用标签索引访问元素的方法示例代码2．按条件筛选数据可以使用表达式、query()方法、isin()方法等多种方式按条件筛选数据示例代码如下。7.2.3数据排序和排名1．数据排序sort_values()方法用于数据排序，其基本语法格式如下。其中，相关参数说明如下。（1）by：排序列名，可以是单个字符串或字符串列表。（2）ascending：默认值为True，表示按升序排序；设置为False，表示按降序排序。指定多个排序列名时，可在ascending参数中用True/False列表设置各个列的升降序排序。（3）inplace：默认值为False，表示不修改原DataFrame对象；设置为True，表示修改原DataFrame对象。2．数据排名rank()方法用于数据排名，其基本语法格式如下。其中，相关参数说明如下。（1）method：各列的排名方式，默认为average（相同数据排名取平均值），还可以是min（相同数据排名取最小值）、max（相同数据排名取最大值）、first（相同数据排名按出现的顺序取值）、dense（类似min）。（2）ascending：默认值为True，表示按升序排名；设置为False，表示按降序排名。7.2.4数据合并和连接1．按行（列）合并数据concat()方法用于按行（列）合并数据，其基本语法格式如下。其中：参数data1和data2是两个DataFrame对象；参数axis默认值为0，表示按行合并，值为1表示按列合并。concat()方法返回包含合并数据的新DataFrame对象2．按索引或列连接数据merge()方法用于按索引或列连接数据，其基本语法格式如下。其中：参数left为连接中的左侧DataFrame对象（左表），参数right为连接中的右侧DataFrame对象（右表），参数how为连接方式，参数on为连接使用的索引或者列名（值为None时，自动按相同列名进行连接）。连接方式分为以下几种。（1）left：类似SQL中的“左外连接”，包含左表中的全部数据，右表中无对应值时用NaN代替。（2）right：类似SQL中的“右外连接”，包含右表中的全部数据，左表中无对应值时用NaN代替。（3）inner：默认方式，类似SQL中的“内部连接”，仅包含左表和右表在on指定的索引或列上取值相等的记录。（4）outer：全连接，包含左表和右表的全部数据，无对应值时用NaN代替。7.2.5数据分组统计groupby()方法和聚合函数用于实现数据分组统计。groupby()方法的基本语法格式如下。其中，参数by是作为数据分组依据的列名。常用的聚合函数如下。（1）sum()：求和。（2）count()：计数。（3）mean()：求平均值。（4）max()：求最大值。（5）min()：求最小值。7.2.6数据透视表创建pivot_table()方法用于创建数据透视表，其基本语法格式如下。其中，相关参数说明如下。（1）data：DataFrame对象。（2）values：单个的列名或者列名列表，指定执行统计的列。（3）index：单个的列名或者列名列表，指定行索引。（4）columns：单个的列名或者列名列表，指定列索引。（5）aggfunc：单个函数或者用字典格式指定的多个函数，指定统计类型，默认为mean（求平均值）。7.2.7数据绘图pandas绘图依赖于Matplotlib的pyplot模块，可以简单地绘制出常用的统计图形。DataFrame对象的plot()方法可以实现常用的绘图操作，其基本语法格式如下。其中，相关参数说明如下。（1）kind：图表类型，默认为line（折线图），还可以是bar（垂直柱状图）、barh（水平柱状图）、hist（直方图）、box（箱线图）、kde（kerneldensityestimation，核密度估计图）、area（面积图）、pie（饼图）、scatter（散点图）、hexbin（六边形箱图）等。（2）x：x轴数据列名或索引，默认值为None。（3）y：y轴数据列名或索引，可以是单个的列名或列名列表，默认值为None。（4）title：图表标题，默认值为None。（5）grid：是否显示网格，默认值为None。【综合实例】Excel文件数据合并文件夹“datas”中有若干个保存了“年级”“层次”“专业名称”“人数”“录取线”等数据的Excel文件，编写一个程序将所有文件合并到一个新文件中。THANKYOU单元8Python教你玩转Excel文件

任务8.1将数据存入Excel文件【任务目标】文件“data1.txt”中保存了“唐宋八大家”的相关信息（包括姓名、朝代、代表作），编写一个程序，使用xlwings库，将这些数据存入一个Excel文件。【任务实施】……8.2管理工作簿创建工作簿打开工作簿管理工作簿8.2.1创建工作簿创建工作簿包含下列几个步骤：打开Excel程序、新建工作簿、保存工作簿、关闭工作簿、退出Excel程序等。上述代码中，第3行创建一个Excel程序实例，并新建一个工作簿，也可以调用app.books.add()方法新建工作簿（见知识点8.1.2中的示例代码）。第5行调用了工作簿对象（单个Excel文件对应的对象，代表一个具体的工作簿）wb的save()方法完成文件保存操作。在调用save()方法执行保存操作时需注意下列情况。（1）使用参数指定文件名时，按指定文件名保存工作簿。（2）省略参数时，按当前文件名保存工作簿。如果新建工作簿还没保存到文件，则在Python程序所在目录中使用默认文件名（如“工作簿1.xlsx”）完成保存操作。（3）工作簿保存到文件时，目录中的同名文件直接被覆盖，没有提示。还可以调用xw.Book()方法创建工作簿，示例代码如下。8.2.2打开工作簿工作簿集合对象（当前所有已打开工作簿的集合，是工作簿对象的容器）的open()方法可用于打开工作簿，示例代码如下。使用app.books.open()方法打开指定的工作簿时，如果该工作簿已经被打开，则以只读方式打开工作簿副本。也可以使用xw.Book()方法打开工作簿，示例代码如下。xw.Book()方法首先在已打开的Excel程序实例中查找指定工作簿，如果工作簿已经打开，则直接返回对应的工作簿对象，否则就创建Excel程序实例并打开工作簿。8.2.3管理工作表1．添加工作表一个工作簿中通常包含多个工作表，工作簿对象的sheets属性用于返回工作表集合对象（单个工作簿中所有工作表的集合，是工作表对象的容器），工作表集合对象的add()方法可用于添加工作表，示例代码如下。wb.sheets.add()方法默认将添加的工作表放在活动工作表之前（添加的工作表成为活动工作表），可使用after或before参数指定添加的工作表的位置，示例代码如下。2．选择工作表工作表集合对象可使用工作表的名称或者位置来选择工作表，示例代码如下。3．复制工作表工作表集合对象的copy()方法可用于复制工作表，示例代码如下。未指定参数时，copy()方法在当前工作簿中复制工作表，并将复制的工作表放在所有工作表之后，复制的工作表的默认名称为“原工作表名称(2)”。参数name用于指定复制的工作表的名称，参数after用于指定复制的工作表的位置，参数after应该是一个工作表集合对象，可以是当前工作簿或另一个工作簿中的工作表。4．删除工作表工作表集合对象的delete()方法用于删除工作表，示例代码如下。5．修改工作表名称工作表集合对象的name属性可用于获取或者修改工作表名称，示例代码如下。任务8.3合并学生名单【任务目标】文件夹“test8_17”中有多个学生名单Excel文件，每个文件中都只有一个工作表，工作表包含“年级”“层次”“专业名称”“录取人数”“录取线”等列，编写程序，将所有学生数据合并到一个工作表中。【任务实施】……8.3读写工作表读工作表写工作表删除数据导出数据8.3.1读工作表xlwings库的range对象表示单元格区域，通过它可完成各种数据读取操作。1．读取单个单元格数据读取单个单元格数据的基本语法格式如下。示例代码如下。2．读取单元格区域数据读取单元格区域数据的基本语法格式如下。示例代码如下。3．以扩展方式读取数据range对象的expand()方法可指定以扩展方式读取工作表数据，其基本语法格式如下。上述语法表示从指定单元格开始，按参数mode指定的方式读取数据，并将其存入变量var。参数mode可以为right（向右扩展读取行）、down（向下扩展读取列）或table（默认值，向右和向下同时扩展读取行和列）。示例代码4．读取已使用单元格区域数据工作表集合对象的used_range用于返回已使用的单元格区域，使用它可获取工作表中的全部数据示例代码如下。8.3.2写工作表1．按行写入数据按行写入数据的基本语法格式如下。上述语法表示从指定单元格开始，按行将data中的数据写入工作表。data可以是单个值或者列表表示的多个值。data是单个值时，只在指定单元格中写入该值；data是列表时，依次将数据写入从指定单元格开始的多个相邻单元格中。如果data是嵌套的列表，则写入多行数据。示例代码2．按列写入数据按列写入数据的基本语法格式如下。参数transpose设置为True，表示按列将data中的数据写入工作表，示例代码如下。8.3.3删除数据xlwings库的sheet或range对象的clear()方法（删除数据和格式）和clear_contents()方法（仅删除数据）用于删除数据。1．删除单个单元格数据删除单个单元格数据的基本语法格式如下。示例代码如下。2．删除单元格区域数据删除单元格区域数据的基本语法格式如下。示例代码如下。3．以扩展方式删除数据与读取工作表数据类似，range对象的expand()方法可指定以扩展方式删除工作表数据，其基本语法格式如下。示例代码如下。4．删除工作表中的全部数据调用工作表集合对象的clear()方法和clear_contents()方法可删除工作表中的全部数据，示例代码如下。8.3.4导出数据工作表集合对象的to_html()方法和to_pdf()方法可分别将工作表数据导出到HTML文件和PDF文件，示例代码如下。任务8.4设置成绩表格式【任务目标】文件“data3.xlsx”中保存了学生成绩数据，使用xlwings库设置数据区域格式：奇数行背景颜色设置为浅灰，标题行文字设置为黑体、16号、加粗，其他行文字设置为仿宋、14号，自动调整行高和列宽、文字居中对齐，下边界设置为直线。【任务实施】……8.4设置工作表格式设置背景颜色设置行高和列宽设置边框设置对齐方式设置文字格式8.4.1设置背景颜色单元格对象range的color属性用于获取或者设置单元格背景颜色，其基本语法格式如下。其中:range_a1_color为保存颜色值的变量，range_a1为单元格对象new_color为颜色值。颜色值可以用RGB颜色值元组(r,g,b)表示，r、g、b取值范围为0～255，也可以用"#FF00FF"格式的字符串表示。示例代码8.4.2设置行高和列宽单元格对象

range

的

row_height

属性用于获取或者设置单元格行高，column_width属性用于获取或者设置单