Excel固然功能强大,也有许多函数实现数据处理功能,但是Excel仍需大量人工操作,虽然能嵌入VB脚本宏,但也容易染上宏病毒。python作为解释性语言,在数据处理方面拥有强大的函数库以及第三方库,excel作为主要基础数据源之一,在利用数据进行分析前往往需要预先对数据进行整理。因此,本文就python处理excel数据进行了学习,主要分为python对excel数据处理的常用数据类型以及常用函数,用python语言实现excel基本操作。
首先,序列是是Python中最基本的数据结构。序列中的每个元素都分配一个数字 - 它的位置,或索引,第一个索引是0,第二个索引是1,依此类推。每个索引对应一个元素。Python包含 6 中内建的序列,包括列表、元组、字符串、Unicode字符串、buffer对象和xrange对象。
对于序列,都可以使用以下操作:
- 取序列中的最大、最小值
- 一组有序项目的集合。可变的数据类型【可进行增删改查】
- 列表是以方括号“[]”包围的数据集合,不同成员以“,”分隔。
- 列表中可以包含任何数据类型,也可包含另一个列表
- 列表可通过序号访问其中成员
Python列表脚本操作符
#列表操作补充--切片操作
#打印某一区间 左闭右开
- 元组是以圆括号“()”包围的数据集合,不同成员以“,”分隔
- 与列表不同:元组中数据一旦确立就不能改变
(元组中的元素值是不允许修改的,但我们可以对元组进行连接组合,如下实例)
Isnull是Python中检验空值的函数,返回的结果是逻辑值,包含空值返回True,不包含则返回False。可以对整个数据表进行检查,也可以单独对某一列进行空值检查。
查看唯一值 (删除重复项)
#查看city列中的唯一值
#删除数据表中含有空值的行
#使用数字0填充数据表中空值
#使用数字0填充数据表中空值
#使用price均值对NA进行填充
#清除city字段中的字符空格
#city列大小写转换
首先是对不同的数据表进行合并,我们这里创建一个新的数据表df1,并将df和df1两个数据表进行合并。在Excel中没有直接完成数据表合并的功能,可以通过VLOOKUP函数分步实现。在python中可以通过merge函数一次性实现。下面建立df1数据表,用于和df数据表进行合并。
#数据表匹配合并,inner模式
排序(按索引,按数值)
Excel中可以通过VLOOKUP函数进行近似匹配来完成对数值的分组,或者使用“数据透视表”来完成分组。相应的 python中使用where函数完成数据分组。
#对复合多个条件的数据进行分组标记
数据分列(对字段拆分)
#对category字段的值依次进行分列,并创建数据表,索引值为df_inner的索引列,列名称为category和size
#将完成分列后的数据表与原df_inner数据表进行匹配
#按索引提取单行的数值
使用冒号可以限定提取数据的范围,冒号前面为开始的标签值,后面为结束的标签值。下面提取了0到5的数据行。
#按索引提取区域行数值
Reset_index函数用于恢复索引,这里我们重新将date字段的日期设置为数据表的索引,并按日期进行数据提取。
使用冒号限定提取数据的范围,冒号前面为空表示从0开始。提取所有2013年1月4日以前的数据。
#提取4日之前的所有数据
使用iloc函数按位置对数据表中的数据进行提取,这里冒号前后的数字不再是索引的标签名称,而是数据所在的位置,从0开始。
#使用iloc按位置区域提取数据
iloc函数除了可以按区域提取数据,还可以按位置逐条提取,前面方括号中的0,2,5表示数据所在行的位置,后面方括号中的数表示所在列的位置。
#使用iloc按位置单独提取数据
按标签和位置提取(ix)
ix是loc和iloc的混合,既能按索引标签提取,也能按位置进行数据提取。下面代码中行的位置按索引日期设置,列按位置设置。
#使用ix按索引标签和位置混合提取数据
按条件提取(区域和条件值)
除了按标签和位置提起数据以外,还可以按具体的条件进行数据。下面使用loc和isin两个函数配合使用,按指定条件对数据进行提取。
使用isin函数对city中的值是否为beijing进行判断。
将isin函数嵌套到loc的数据提取函数中,将判断结果为Ture数据提取出来。这里我们把判断条件改为city值是否为beijing和 shanghai。如果是就把这条数据提取出来。
#先判断city列里是否包含beijing和shanghai,然后将复合条件的数据提取出来。
数值提取还可以完成类似数据分列的工作,从合并的数值中提取出制定的数值。
#提取前三个字符,并生成数据表使用与,或,非三个条件配合大于,小于和等于对数据进行筛选,并进行计数和求和。与excel中的筛选功能和countifs和sumifs功能相似。
按条件筛选(与,或,非)
#使用“与”条件进行筛选
在前面的代码后增加price字段以及sum函数,按筛选后的结果将price字段值进行求和,相当于excel中sumifs的功能。
#对筛选后的数据按price字段进行求和
#使用“非”条件进行筛选
在前面的代码后面增加city列,并使用count函数进行计数。相当于excel中的countifs函数的功能。
#对筛选后的数据按city列进行计数
还有一种筛选的方式是用query函数。下面是具体的代码和筛选结果。
#使用query函数进行筛选
在前面的代码后增加price字段和sum函数。对筛选后的price字段进行求和,相当于excel中的sumifs函数的功能。
#对筛选后的结果按price进行求和
Excel中使用分类汇总和数据透视可以按特定维度对数据进行汇总,python中使用的主要函数是groupby和pivot_table。下面分别介绍这两个函数的使用方法。
Groupby是进行分类汇总的函数,使用方法很简单,制定要分组的列名称就可以,也可以同时制定多个列名称,groupby按列名称出现的顺序进行分组。同时要制定分组后的汇总方式,常见的是计数和求和两种。
#对所有列进行计数汇总
#对特定的ID列进行计数汇总
#对两个字段进行汇总计数
除了计数和求和外,还可以对汇总后的数据同时按多个维度进行计算,下面的代码中按城市对price字段进行汇总,并分别计算price的数量,总金额和平均金额。
#对city字段进行汇总并计算price的合计和均值。
Excel中的插入目录下提供“数据透视表”功能对数据表按特定维度进行汇总。Python中也提供了数据透视表功能。通过pivot_table函数实现同样的效果。
数据透视表也是常用的一种数据分类汇总方式,并且功能上比groupby要强大一些。下面的代码中设定city为行字段,size为列字段,price为值字段。分别计算price的数量和金额并且按行与列进行汇总。
Weights参数是采样的权重,通过设置不同的权重可以更改采样的结果,权重高的数据将更有希望被选中。这里手动设置6条数据的权重值。将前面4个设置为0,后面两个分别设置为0.5。
Python中可以通过Describe对数据进行描述统计。
Describe函数是进行描述统计的函数,自动生成数据的数量,均值,标准差等数据。下面的代码中对数据表进行描述统计,并使用round函数设置结果显示的小数位。并对结果数据进行转置。
Python中的Std函数用来接算特定数据列的标准差。
Excel中的数据分析功能中提供协方差的计算,python中通过cov函数计算两个字段或数据表中各字段间的协方差。
Cov函数用来计算两个字段间的协方差,可以只对特定字段进行计算,也可以对整个数据表中各个列之间进行计算。
Excel的数据分析功能中提供了相关系数的计算功能,python中则通过corr函数完成相关分析的操作,并返回相关系数。
Corr函数用来计算数据间的相关系数,可以单独对特定数据进行计算,也可以对整个数据表中各个列进行计算。相关系数在-1到1之间,接近1为正相关,接近-1为负相关,0为不相关。
处理和分析完的数据可以输出为xlsx格式和csv格式。
在数据处理的过程中,大部分基础工作是重复和机械的,对于这部分基础工作,我们可以使用自定义函数进行自动化。以下简单介绍对数据表信息获取自动化处理。
#调用自定义函数获取df数据表信息并输出结果