使用Python处理Excel/csv格式重复数据

1,538 阅读14分钟

之前写过一篇文章使用Perl处理Excel重复数据,想要达成的目标是通过Perl脚本处理Excel文件里的数据,遍历数据,取一条数据的id跟另外一条数据的name对比,如果id跟name相同,则把重复数据放在一个新的Excel表格1里,如果没有重复,则把对比的该数据放在另外一个新的Excel表格2,依次遍历。结果遇到以下问题,让我最终放弃Perl处理Excel:

  1. 使用Perl模块Spreadsheet::ParseExcel读取目标Excel文件时,由于Perl机制,程序在写入读取文件内容时直接将所有文件内容读入,而不是一行一行读取遍历,故在处理大文本文件时,会出现内存溢出的情况(博主处理的Excel文件大概5万多行,就报内存溢出)
  2. 在网上查找相关资料(吐槽一下,Perl资料真的是少),大概有三种思路解决问题1的情况:
  • windows下的话,建议用win32::OLE,效率会高很多
  • 用Spreadsheet::ParseExcel模块时,获取行数,同时通过while遍历,不能用foreach,也不要grep和for
  • 转成csv格式(Excel另存为,可以选择csv格式),然后采用逐行读取处理,while()循环处理数据 博主的操作环境是windows平台,但由于博主的小可爱使用的是mac,所以方式1明显会被无情淘汰,方式2使用while能够遍历,但没思路使用while对比去重,所以也被淘汰,博主尝试了一下方式3,是能够遍历每一行的数据,但由于使用while遍历的,故使用while对比去重还是没有思路,没办法也被淘汰(在此不得不说,同样的数据,同样是处理csv格式的数据,Perl还是有自己的优点的,处理数据只需要十几秒,但Python处理需要好几分钟,Perl还是有自己的优势的,只是博主接触Perl有限,暂时对于对比去重没有自己的思路,还有就是博主的女友小可爱想要使用python处理数据,说它整理数据方便,图表漂亮,故直接放弃Perl尝试使用python

言归正传,接下来这篇文章会记录从python环境安装到数据处理目标达成一整套步骤(本篇文章的主要目的是博主要记录下来,给自己傻傻的女票使用,博主也要教女朋友敲代码了,开心❤)

1. python环境安装

1.1 windows环境下python的安装

进入python官网,点击Downloads,选择不同的平台,选择对应平台的python版本安装(注意python2.x与python3.x是有区别的,python2和python3的区别在于核心类的差异,废弃类的差异,修改类的差异,第三方工具包差异,以及一些内置函数的语法修改变动差异。所以可以同时安装两个版本的python)

windows版本的python2的版本是msi安装包,python3是exe安装包,直接下载安装就可以。 博主安装的版本分别是:

  • Python 2.7.18
  • Python 3.6.8 博主已经安装过python,关于下面的四张图片引用自百度经验,但与原安装步骤一模一样,请谅解(注意安装时,可以点击安装包右键,以管理员身份运行):

20161201152434302.png

先勾选最下方的选项,然后点击Customize installation,注意一定要勾选最下面一个选项,它可以自动帮你添加windows环境变量。点击完成后,进入下一个步骤:

20161201152650272.png

默认全选就好,继续点击下一步:

20161201152739258.png

记得勾选第一项,并选择自己想要安装的路径。然后点击Install后,等几分钟就好了。

安装完成后,是不是着急要测一测自己安装的python是否好用呢?

我们按Ctrl+R,输入cmd,打开命令提示符窗口,输入python,如果你出现下面的情况,说明你的python安装成功了!

1618627655(1).png

如果没有出现下面的情况,不要着急,可能是你的环境变量没有修改成功,我们执行一下步骤就好

右键点击"我的电脑"->属性->高级系统设置->环境变量

在系统变量栏目中,找到PATH,双击编辑后,添加你刚才安装python的路径就可以啦!

这里注意,可能系统版本不一样,有时候PATH是在一行显示的,那样的话需要在原来的PATH后面加上; 然后在后面加上刚才的python安装路径即可。

python3的安装跟python2是一样的,但在命令提示符窗口,输入python3时,有的会调出会自动弹出MicrosoftStore,解决办法如下:

  • 删除环境变量,删除WindowsApps的环境变量,环境变量可能在用户变量Path里面,也有可能在系统Path里面,我的是在用户变量里面。
  • 关闭管理应用执行别名,按下win键,输入管理应用,然后打开“管理应用执行别名”,关闭即可

1618628846(1).png 我这两种方式都做了,还是会自动弹出MicrosoftStore,就关闭终端(命令提示符窗口),重新打开,就不会 自动弹出MicrosoftStore,但会报'python3' 不是内部或外部命令,也不是可运行的程序或批处理文件。 解决办法如下: 打开python3的安装所在文件夹,复制一份python.exe,并改名为python3.exe

1618629239(1).jpg 再次在终端输入python3,出现下面的情况,说明你的python3安装成功了!

1618629498(1).png python2.x输入 Ctrl + C 即可退出命令行模式。

python3.x输入 exit() 即可退出命令行模式。

1.2 Mac OS X环境下python的安装

1.2.1 Mac默认安装了Python2.7

Python有两个发行版,一个是Python2,一个是Python3。有很多老的软件使用的是Python2,而有很多新的软件使用的是Python3,所以最好是同时安装两个版本,使用起来比较方便。

查看python版本:

打开终端(即terminal)

打开终端方法:通过command + space打开搜索,然后输入terminal就能打开终端。 终端内输入命令如下:

python -V

aHR0cHM6Ly9vc2NpbWcub3NjaGluYS5uZXQvb3NjbmV0LzNiYTZjMDNiODgyNWFiNmQ5M2I1Njg1ZDJjYjk1NmIxN2VlLmpwZw.png

注意:命令python -V之前的$符号之前的显示根据个人电脑机器的不同(个人设置的名字什么的),显示会不相同(针对萌新小盆友解释)。

查看python可执行文件路径:

aHR0cHM6Ly9vc2NpbWcub3NjaGluYS5uZXQvb3NjbmV0LzY5ZDM4NWMzZjBkMTAyNjYzMzk5NDBjYTQxOTkyNDNlMzIyLmpwZw.png

python的安装:

不同的安装方式,安装的目录也是不一样的(实际上就算是相同的安装方式不同的版本也会通过版本号区分开):

来源Python安装路径
mac系统默认自带/System/Library/Frameworks/Python.framework/Versions/2.7/
通过命令行工具homebrew安装/usr/local/Cellar/
python官网下载.pkg格式安装/Library/Frameworks/Python.framework/Versions/2.7/

所以可以通过修改环境变量从而切换不同的Python版本

1.2.2 安装Python3:

Python2系统默认已经有了,就不再安装了,Python3为了方便,本次采用brew安装(通过命令行工具homebrew安装):

命令行工具homebrew安装可以借鉴该篇文章使用Perl对TCGA数据库下载后的文件合并与转换中的目录2部分中关于mac的部分有介绍。

  • 在终端输入brew安装命令如下:
brew install python3

安装完成后的路径如下:

/usr/local/Cellar/python/......

一行命令搞定,但是它会安装最新版本对python3,对最新的版本并不支持。

官网下载python3 指定版本.pkg格式的安装包安装 www.python.org/downloads/m…

安装完全按照指示下一步就行。

  • 查看是否安装成功,在终端输入命令如下:
python3

15494112-e2f8df0874acaa51.webp

(上图来源网络)如此python3已经安装成功。

  • 查看python3安装位置,在终端输入命令如下:
which python3

15494112-014dd0805542a3c9.webp

安装路径为:/Library/Frameworks/Python.framework/Versions/3.6/bin/python3

(上图来源网络)至此python3在mac上安装完成。

  • 配置环境变量

目的:一般来说,我们mac命令行输入python,默认调用的python2版本,目前很多库python2已经不再支持维护。重点还是使用python3,下面我们开始配置。

由于/etc/profile;/etc/bashrc 是针对系统所有用户的全局变量,只有root用户才能修改这两个文件,对一般用户来说是他们是只读的。一般用户要想修改它们,可以在命令前加sudo,意思是以Root身份执行,比如:sudo vi /etc/profile ,然后按照提示输入密码即可。

因此,对于一般用户而言,通常我们建议去修改~/.bash_profile来设置环境变量,它是用户级的设置,只对当前用户有效。

在终端输入如下命令:

vi ~/.bash_profile 

是用 vim 打开文件bash_profile

在insert模式(按i)将python3 路径写入:

# Setting PATH for Python 3.9
PATH="/Library/Frameworks/Python.framework/Versions/3.6/bin:${PATH}"
export PATH
alias python="/Library/Frameworks/Python.framework/Versions/3.9/bin/python3"

(注意:填入的路径根据版本不同,安装方式不同,会不同。)

然后按esc键, 然后‘:’底线命令模式, 输入 'wq!'

想要修改后立即生效,在终端输入命令如下:

source ~/.bash_profile

上面输入命令更新配置的方式也可以重启电脑代替。

再次输入 python, 启动的应该就是 python3, 退出或者重启后都不会有改变。

查看环境变量的值,在终端输入命令如下:

echo $PATH

注意:

  1. ~/.bash_profile中有个点,输入命令时别忘记了;
  2. 如果是新增环境变量或者是修改环境变量的值,都需要source ~/.bash_profile一下才能立即生效。如果是删除一个环境变量,必须输入exit以logout当前shell,然后再重新打开一个新的shell并login才能生效。
  • 设置别名 如果是分别使用python2或者python3作为命令,是不需要设置别名的,但是很多场景是需要使用python作为命令,所以需要设置一下别名进行切换(注意:如果配置了上一步的环境变量,使用python调用的是python3)

在终端输入命令如下:

vi ~/.bashrc

添加一下内容如下:

alias python2='/System/Library/Frameworks/Python.framework/Versions/2.7/bin/python2.7'
alias python3='/Library/Frameworks/Python.framework/Versions/3.9/bin/python3'
#通过这两行进行切换
# alias python=python3  #设置python为python3
# alias python=python2  #设置python为python2

即当需要输入python作为命令,而不能输入python3作为命令,但又想通过命令调用的环境是python3的环境时,可以在终端输入命令:

alias python=python3

注意:以上所有涉及到安装路径的命令或者内容的参数都要以自己电脑上安装的路径或者版本为依据,例如我举的例子是3.9版本,有可能童鞋你安装的是3.10版本或者3.6版本,所以涉及到路径的地方不能完全复制我的(手动艾特某个小可爱

查看不同版本的命令如下:

python2版本:

which python2

python3版本:

which python3

2. python数据处理

书归正传,前面嘟嘟嘟那么多,主要是给某个小可爱科普一些基础的语言环境安装,接下来才是重头戏,进入最终的目的地: 使用python处理数据阶段。

可能某些童鞋对于python比较陌生(我也一样),所以一起慢慢摸索,一起学习,共同进步吧,一起加油!

u=2210207296,2380129719&fm=26&gp=0 (1).jpg

每种语言都有自己的语言特性、编码风格,所以推荐一位童鞋写的一篇比较干练的文章:十分钟快速入门 Python,对python提炼总结,化繁为简,可以帮助我们快速入门python。

翻看本博文的童鞋,博主都是站在假如你有一些些编码基础,特别是有一丢丢python编码基础,知道我们的代码为什么这么写的童鞋,所以,直接翻到Python数据处理这一章节吧,咱们调快速度。 针对Python数据处理这块,我们站在巨人的肩膀上,参考这篇博文:Python数据处理(一):处理 JSON、XML、CSV 三种格式数据 先科普一下文件格式:

以易于机器理解的方式来存储数据的文件格式,通常被称作机器可读的 (machine readable)。常见的机器可读格式包括:

  • 逗号分隔值(Comma-Separated Values,CSV):CSV
  • JavaScript 对象符号(JavaScript Object Notation,JSON):JSON
  • 可扩展标记语言(eXtensible Markup Language,XML):XML

2.1 处理CSV数据

CSV 文件(简称为 CSV)是指将数据列用逗号分隔的文件。文件的扩展名是 .csv。

另一种数据类型,叫作制表符分隔值(tab-separated values,TSV)数据,有时也与 CSV归为一类。TSV 与 CSV 唯一的不同之处在于,数据列之间的分隔符是制表符(tab),而不是逗号。文件的扩展名通常是 .tsv,但有时也用 .csv 作为扩展名。从本质上来看,.tsv 文件与 .csv 文件在Python 中的作用是相同的。

CSV文件可以直接使用Excel打开,我采用的示例数据如下:

QQ图片20210418160615.png 目标:去重数据,根据一条数据A的id跟另外一条数据B的name对比,如果另外一条数据B重复,则把重复的这条数据B放在新的Excel表格1,如果没有找到重复的数据,则把数据A放在另外一个Excel表格2。大概有五万多行,90多M的数据量。

2.1.1 遍历查看csv文件数据结构

我们先读取CSV数据,通过循环遍历数据看一下数据结构,代码如下: 文件名为mRNA.py(注意:Python文件是以.py结尾,Perl文件是以.pl结尾),文件内容如下:

注意代码里#符号后面是注释

# coding=utf-8
# 如果注释里有中文,就需要加这行,而且要放在文件的第一行,指定utf-8编码,
# 目的是让python解释器按你指定的编码方式去保存文件,读取的时候就不会出项上述错误。

# 导入 Python 自带的 csv 模块
import csv

# 以只读的形式打开数据文件并存储到变量 csvfile 中
# mRNA.csv是CSV文件的名字,./是mRNA.cs文件相对于本Python文件的路径
csvfile = open('./mRNA.csv', 'r')
# 调用 csv 的 reader() 方法将输出保存在 reader 变量中
reader = csv.reader(csvfile)

# 用 for 循环将数据输出
for row in reader:
    # print是打印输出的意思,不会有额外的逻辑,就是想看输出结果
    # 或者某一个变量里面存的什么,就可以使用print打印出来查看
    print(row)

上面代码也有第二种书写方式,如下(比较优雅,建议这种写法):

# coding=utf-8

import csv

with open('./mRNA.csv', 'r') as f:
    reader = csv.reader(f)
    print(type(reader))
    
    for row in reader:
        print(row)

输出结果如下:

~3CZ}RBPTG17U4HNGWSAN.png 上图可知,每一行里的列组成了列表。

上图是我在编辑器里面执行的,跟在操作系统的终端执行时一个道理,注意,我们要在.py文件(python文件)所在文件夹里执行python文件。从上图可以看到,由于数据有256列,所以打印的每一行数据都很长,而且我们把第一行的数据也打印了出来,读取Excel表格或者CSV文件时,如何跳过第一行读取表格数据呢?第一想法是在循环读取时进行判断,如果行数为第一行,则跳过当前循环,代码如下:

# coding=utf-8
# 导入 Python 自带的 csv 模块
import csv

csvfile = open('./mRNA.csv', 'r')
reader = csv.reader(csvfile)

# 定义一个参数
line_num = 0
# 用 for 循环将数据输出
for row in reader:
    line_num += 1
    if (line_num != 1):
        print(row)

以上逻辑是:定义一个参数line_num = 0,通过line_num += 1,在每次循环结束后把line_num大小加1,而第一次循环里,此时line_num还是为0,证明循环的是第一行数据,此时if判断,由于line_num = 0,不符合条件,不会进入if内部执行代码内容,跳过开始执行第二次循环,由于前面line_num += 1,在第一次循环结束后,line_num = 1,再次进入循环(第二次循环),if判断line_num = 1,符合if条件,进入if逻辑内部执行代码,所以会打印第二次循环的数据,也就是第二行,以此类推,这样子就会打印除了第一行的所有数据。

这样是可以实现的,但是效率较低,因此经过某度,发现了islice()函数(后续文章中会介绍如何跳过第一行读取表格文件,本文只介绍islice()方法的使用)。

迭代器切片:islice()

  • 使用islice()函数需要先导入islice包,代码如下:
from itertools import islice

逻辑代码如下:

# coding=utf-8
# 导入islice包
from itertools import islice

csvfile = open('./mRNA.csv', 'r')
# 构造迭代器对象
reader = islice(csvfile, 1, None)

#执行for循环
for row in reader:
    print("**************************")
    print(row)

或如下代码(比较优雅,建议这种写法):

# coding=utf-8
# 导入islice包
from itertools import islice

with open('./mRNA.csv', 'r') as f:
    reader = islice(f, 1, None)
    for row in reader:
        print("**************************")
        print(row)

每一行打印如下:

QQ图片20210418175305.png 从上图可以看出,我们打印出来的数据跟前面打印出来的数据还是有区别的,一行数据里的列通过“,”组成了一串字符串类型的数据。

其实我们要取的是每一行的第一列也就是id对应的值,或者第二列name对应的值,所以,通过islice()这种方式跳过列表的第一行后,可以通过split()方法字符串截取的方式取得每行的第一列或者第二列。

截取方式写法如下:

# 获取每行数据的第一列
print(row.split(',', 1)[0]) 
# 获取每行数据的第二列
print(row.split(',', 2)[1]) 

2.1.2 循环遍历的方式分离数据

我的处理思路如下:

创建三个列表,分别准备用于存储:

  • 筛选出来的重复数据
  • 用于储存重复数据之外剩余的数据
  • 用于储存要对比的所有数据的索引(即name),其中剔除为空的name 代码如下:
# coding=utf-8

# 跳过列表表头的引入依赖
from itertools import islice

import csv

# 用于储存重复的数据
re_l = []
# 用于储存重复数据之外剩余的数据
n_l = []

# 用于储存要对比的所有数据的索引(即name),其中剔除为空的name
values = []

# 获取所有数据中name值不为空数据的name
with open('./mRNA.csv', 'r') as f:
    # 跳过列表表头
    values_reader = islice(f, 1, None)
    for value in values_reader:
        if len(value.split(',', 2)[1]) != 0:
            values.append(value.split(',', 2)[1])

# 把数据分类
with open('./mRNA.csv', 'r') as f1:
    reader = islice(f1, 1, None)
    for row in reader:
        if not row.split(',', 1)[0] in values:
            n_l.append(row)
        else:
            # 重复的数据
            re_l.append(row)

# 把重复的数据写入remRNA.csv
with open('./remRNA.csv', 'w') as f2:
    re_cw = csv.writer(f2)
    for re_item in re_l:
        re_cw.writerow(re_item.split(','))

# 把重复的数据写入nmRNA.csv
with open('./nmRNA.csv', 'w') as f3:
    n_cw = csv.writer(f3)
    for n_item in n_l:
        n_cw.writerow(n_item.split(','))

注意:以上代码有两个细节需要注意

  • 获取的数据在写入文件时,由于for循环的是一串由“,”连接的字符串,需要通过.split(',')处理成列表才能使用.writerow()写入
  • 是写入文件的操作系统的兼容问题,Mac系统不管是python2还是python3在运行以上代码的写入文件的代码时不会出问题,但windows系统下python2的运行环境下写入文件操作、python3的运行环境下写入文件操作需要不同的写法,如下:
  1. python2的运行环境下写入文件操作代码:
with open('./remRNA.csv', 'wb') as f2:
  1. python3的运行环境下写入文件操作代码:
with open('./remRNA.csv', 'wt', newline='') as f2:

with open('./remRNA.csv', 'w', newline='') as f2:

以上代码运行要处理的数据如下图:

1618823958(1).png 处理后重复的数据列表如下图:

1618824056(1).png 处理后剩余数据列表如下图:

1618824145(1).png 故达到了想要的目的。

u=1332918485,3690834365&fm=26&gp=0.jpg

2.1.3 使用Pandas分离数据