14个Python处理Excel的常用操作分享

目录
  • 一、关联公式:Vlookup
  • 二、数据透视表
  • 三、对比两列差异
  • 四、去除重复值
  • 五、缺失值处理
  • 六、多条件筛选
  • 七、 模糊筛选数据
  • 八、分类汇总
  • 九、条件计算
  • 十、删除数据间的空格
  • 十一、数据分列
  • 十二、异常值替换
  • 十三、分组
  • 十四、根据业务逻辑定义标签

自从学了Python后就逼迫用Python来处理Excel,所有操作用Python实现。目的是巩固Python,与增强数据处理能力。

这也是我写这篇文章的初衷。废话不说了,直接进入正题。

数据是网上找到的销售数据,长这样:

一、关联公式:Vlookup

vlookup是excel几乎最常用的公式,一般用于两个表的关联查询等。所以我先把这张表分为两个表。

df1=sale[['订单明细号','单据日期','地区名称', '业务员名称','客户分类', '存货编码', '客户名称', '业务员编码', '存货名称', '订单号',
       '客户编码', '部门名称', '部门编码']]
df2=sale[['订单明细号','存货分类', '税费', '不含税金额', '订单金额', '利润', '单价','数量']]

需求:想知道df1的每一个订单对应的利润是多少。

利润一列存在于df2的表格中,所以想知道df1的每一个订单对应的利润是多少。用excel的话首先确认订单明细号是唯一值,然后在df1新增一列写:=vlookup(a2,df2!a:h,6,0) ,然后往下拉就ok了。(剩下13个我就不写excel啦)

那用python是如何实现的呢?

#查看订单明细号是否重复,结果是没。
df1["订单明细号"].duplicated().value_counts()
df2["订单明细号"].duplicated().value_counts()

​​​​​​​df_c=pd.merge(df1,df2,on="订单明细号",how="left")

二、数据透视表

需求:想知道每个地区的业务员分别赚取的利润总和与利润平均数。

pd.pivot_table(sale,index="地区名称",columns="业务员名称",values="利润",aggfunc=[np.sum,np.mean])

三、对比两列差异

因为这表每列数据维度都不一样,比较起来没啥意义,所以我先做了个订单明细号的差异再进行比较。

需求:比较订单明细号与订单明细号2的差异并显示出来。

sale["订单明细号2"]=sale["订单明细号"]

#在订单明细号2里前10个都+1.
sale["订单明细号2"][1:10]=sale["订单明细号2"][1:10]+1

#差异输出
result=sale.loc[sale["订单明细号"].isin(sale["订单明细号2"])==False]

四、去除重复值

需求:去除业务员编码的重复值

sale.drop_duplicates("业务员编码",inplace=True)

五、缺失值处理

先查看销售数据哪几列有缺失值。

#列的行数小于index的行数的说明有缺失值,这里客户名称329<335,说明有缺失值
sale.info()

需求:用0填充缺失值或则删除有客户编码缺失值的行。

实际上缺失值处理的办法是很复杂的,这里只介绍简单的处理方法,若是数值变量,最常用平均数或中位数或众数处理,比较复杂的可以用随机森林模型根据其他维度去预测结果填充。若是分类变量,根据业务逻辑去填充准确性比较高。

比如这里的需求填充客户名称缺失值: 就可以根据存货分类出现频率最大的存货所对应的客户名称去填充。

这里我们用简单的处理办法:用0填充缺失值或则删除有客户编码缺失值的行。

#用0填充缺失值
sale["客户名称"]=sale["客户名称"].fillna(0)
#删除有客户编码缺失值的行
sale.dropna(subset=["客户编码"])

六、多条件筛选

需求:想知道业务员张爱,在北京区域卖的商品订单金额大于6000的信息。

sale.loc[(sale["地区名称"]=="北京")&(sale["业务员名称"]=="张爱")&(sale["订单金额"]>5000)]

七、 模糊筛选数据

需求:筛选存货名称含有"三星"或则含有"索尼"的信息。

sale.loc[sale["存货名称"].str.contains("三星|索尼")]

八、分类汇总

需求:北京区域各业务员的利润总额。

sale.groupby(["地区名称","业务员名称"])["利润"].sum()

九、条件计算

需求:存货名称含“三星字眼”并且税费高于1000的订单有几个?这些订单的利润总和和平均利润是多少?(或者最小值,最大值,四分位数,标注差)

sale.loc[sale["存货名称"].str.contains("三星")&(sale["税费"]>=1000)][["订单明细号","利润"]].describe()

十、删除数据间的空格

需求:删除存货名称两边的空格。

sale["存货名称"].map(lambda s :s.strip(""))

十一、数据分列

需求:将日期与时间分列。

sale=pd.merge(sale,pd.DataFrame(sale["单据日期"].str.split(" ",expand=True)),how="inner",left_index=True,right_index=True)

十二、异常值替换

首先用describe()函数简单查看一下数据有无异常值。

#可看到销项税有负数,一般不会有这种情况,视它为异常值。
sale.describe()

需求:用0代替异常值。

sale["订单金额"]=sale["订单金额"].replace(min(sale["订单金额"]),0)

十三、分组

需求:根据利润数据分布把地区分组为:“较差”,“中等”,“较好”,“非常好”

首先,当然是查看利润的数据分布呀,这里我们采用四分位数去判断。

sale.groupby("地区名称")["利润"].sum().describe()

根据四分位数把地区总利润为[-9,7091]区间的分组为“较差”,(7091,10952]区间的分组为"中等" (10952,17656]分组为较好,(17656,37556]分组为非常好。

#先建立一个Dataframe
sale_area=pd.DataFrame(sale.groupby("地区名称")["利润"].sum()).reset_index()

#设置bins,和分组名称
bins=[-10,7091,10952,17656,37556]
groups=["较差","中等","较好","非常好"]

#使用cut分组
#sale_area["分组"]=pd.cut(sale_area["利润"],bins,labels=groups)

十四、根据业务逻辑定义标签

需求:销售利润率(即利润/订单金额)大于30%的商品信息并标记它为优质商品,小于5%为一般商品。

sale.loc[(sale["利润"]/sale["订单金额"])>0.3,"label"]="优质商品"
sale.loc[(sale["利润"]/sale["订单金额"])<0.05,"label"]="一般商品"

其实excel常用的操作还有很多,我就列举了14个自己比较常用的,若还想实现哪些操作可以评论一起交流讨论,另外我自身也知道我写python不够精简,惯性使用loc。(其实query会比较精简)。若大家对这几个操作有更好的写法请务必评论告知我,感谢!

最后想说说,我觉得最好不要拿excel和python做对比,去研究哪个好用,其实都是工具,excel作为最为广泛的数据处理工具,垄断这么多年必定在数据处理方便也是相当优秀的,有些操作确实python会比较简单,但也有不少excel操作起来比python简单的。

比如一个很简单的操作:对各列求和并在最下一行显示出来,excel就是对一列总一个sum()函数,然后往左一拉就解决,而python则要定义一个函数(因为python要判断格式,若非数值型数据直接报错。)

总结一下就是:无论用哪个工具,能解决问题就是好数据分析师!

以上就是14个Python处理Excel的常用操作分享的详细内容,更多关于Python处理Excel的资料请关注我们其它相关文章!

(0)

相关推荐

  • python pandas处理excel表格数据的常用方法总结

    目录 前言 1.读取xlsx表格:pd.read_excel() 2.获取表格的数据大小:shape 3.索引数据的方法:[ ] / loc[] / iloc[] 4.判断数据为空:np.isnan() / pd.isnull() 5.查找符合条件的数据 6.修改元素值:replace() 7.增加数据:[ ] 8.删除数据:del() / drop() 9.保存到excel文件:to_excel() 总结 前言 最近助教改作业导出的成绩表格跟老师给的名单顺序不一致,脑壳一亮就用pandas写了

  • Python办公自动化Word转Excel文件批量处理

    目录 前言 首先使用Python将Word文件导入 row和cell解析所需内容 内层解析循环 前言 大家好,今天有一个公务员的小伙伴委托我给他帮个忙,大概是有这样一份Word(由于涉及文件私密所以文中的具体内容已做修改) 一共有近2600条类似格式的表格细栏,每个栏目包括的信息有: 日期 发文单位 文号 标题 签收栏 需要提取其中加粗的这三项内容到Excel表格中存储,表格样式如下: 也就是需要将收文时间.文件标题.文号填到指定位置,同时需要将时间修改为标准格式,如果是完全手动复制和修改时间,

  • Python Excel处理库openpyxl详解

    Python中的penpyxl是一个第三方库,可以处理xlsx格式的Excel文件.pip install openpyxl安装. penpyxl读取Excel文件 需要导入相关函数 from openpyxl import load_workbook # 默认可读写,若有需要可以指定write_only和read_only为True wb = load_workbook('pythontab.xlsx') 默认打开的文件为可读写,若有需要可以指定参数read_only为True. penpyx

  • Python 自动化处理Excel和Word实现自动办公

    今天我来分享一些Python办公自动化的方法,欢迎收藏学习,喜欢点赞支持,欢迎畅聊. Openpyxl Openpyxl 可以说是 Python 中最通用的工具模块了,它使与 Excel 交互pip install openpyxl pip install python-docx简直就像在公园里漫步一样. 有了它,你就可以读写所有当前和传统的 excel 格式,即 xlsx 和 xls. Openpyxl 允许填充行和列.执行公式.创建 2D 和 3D 图表.标记轴和标题,以及大量可以派上用场的

  • python实现处理Excel表格超详细系列

    目录 前言 xls和xlsx excel后缀.xls和.xlsx有什么区别 基本操作 1:用openpyxl模块打开Excel文档,查看所有sheet表 1.2:通过sheet名称获取表格 1.2:获取活动表 2:获取表格的尺寸 2.1:获取单元格中的数据 2.2:获取单元格的行.列.坐标 3:获取区间内的数据 操作 创建新的excel 修改单元格.excel另存为 添加数据 插入有效数据 插入空行空列 删除行.列 移动指定区间的单元格(move_range) 字母列号与数字列号之间的转换 字体

  • 教你用Python实现Excel表格处理

    一.文件 一个测试有两个sheet页的Excel测试文件 test.xlsx 二.代码 import pandas as pd file1 = pd.ExcelFile('D:\\data\\py\\test.xlsx') file2 = pd.read_excel('D:\\data\\py\\test.xlsx') print(file) <pandas.io.excel._base.ExcelFile object at 0x0000021DE525DF88> -------------

  • Python使用openpyxl模块处理Excel文件

    首先贴出四种方法适用范围比较: 注释:Excel 2003 即XLS文件有大小限制即65536行256列,所以不支持大文件.而Excel 2007以上即XLSX文件的限制则为1048576行16384列 一.xlutils & xlrd & xlwt 最原始的莫过于两位老牌黄金搭档xlrd xlwt了,针对二者的封装有如下模块: xlutils:https://pypi.org/project/xlutils/ xlrd:https://pypi.org/project/xlrd/ xlw

  • 14个Python处理Excel的常用操作分享

    目录 一.关联公式:Vlookup 二.数据透视表 三.对比两列差异 四.去除重复值 五.缺失值处理 六.多条件筛选 七. 模糊筛选数据 八.分类汇总 九.条件计算 十.删除数据间的空格 十一.数据分列 十二.异常值替换 十三.分组 十四.根据业务逻辑定义标签 自从学了Python后就逼迫用Python来处理Excel,所有操作用Python实现.目的是巩固Python,与增强数据处理能力. 这也是我写这篇文章的初衷.废话不说了,直接进入正题. 数据是网上找到的销售数据,长这样: 一.关联公式:

  • 使用Python对Excel进行读写操作

    学习Python的过程中,我们会遇到Excel的读写问题.这时,我们可以使用xlwt模块将数据写入Excel表格中,使用xlrd模块从Excel中读取数据.下面我们介绍如何实现使用Python对Excel进行读写操作. Python版:3.5.2 通过pip安装xlwt,xlrd这两个模块,如果没有安装的话: pip install xlwt pip install xlrd 一.对Excel文件进行写入操作: # -*- conding:utf-8 -*- __author__ = 'mayi

  • python数据类型_字符串常用操作(详解)

    这次主要介绍字符串常用操作方法及例子 1.python字符串 在python中声明一个字符串,通常有三种方法:在它的两边加上单引号.双引号或者三引号,如下: name = 'hello' name1 = "hello bei jing " name2 = '''hello shang hai haha''' python中的字符串一旦声明,是不能进行更改的,如下: #字符串为不可变变量,即不能通过对某一位置重新赋值改变内容 name = 'hello' name[0] = 'k' #通

  • python pymysql库的常用操作

    批量插入 import pymysql def insert_to_mysql(to_db_list): mysql_db = pymysql.connect(host="HOST_IP", port=3306, user="username", password="password", database="db", charset="utf8") cursor = mysql_db.cursor() sq

  • Python处理EXCEL表格导入操作分步讲解

    目录 一.前期准备 二.编写代码基本思路 三.编写代码读取数据 四.结语 一.前期准备 此篇使用两种导入excel数据的方式,形式上有差别,但两者的根本方法实际上是一样的. 首先需要安装两个模块,一个是pandas,另一个是xlrd. 在顶部菜单栏中点击文件,再点击设置,然后在设置中找到以下界面,并点击“+”号. 然后会出现以下界面,在搜索框中分别搜索以上两个模块:pandas/xlrd. 选中搜索出来的模块,并点击左下角的的安装按钮,便可将模块安装到自己电脑中. 需要注意的是,xlrd的新版本

  • Python学习之字符串常用操作详解

    目录 1.查找字符串 2.分割字符串 3.连接字符串 4.替换字符串 5.移除字符串的首尾字符 6.转换字符串的大小写 7.检测字符串(后续还会更新) 1.查找字符串 除了使用index()方法在字符串中查找指定元素,还可以使用find()方法在一个较长的字符串中查找子串.如果找到子串,返回子串所在位置的最左端索引,否则返回-1. 语法格式: str.find(sub[,start[,end]]) 其中,str表示被查找的字符串.sub表示查找的子串.start表示开始索引,缺省时为0.end表

  • Python批量修改文件名实例操作分享

    此处以修改图片文件名为例,将图片复制到新的目录中再修改名称~ 原始文件名如下: 修改后的效果如下: 代码: 实现将one文件夹中的每一个图片复制到two文件夹并修改图片名为按照a.b.c.d递增排序 思路:重命名为新建一个文件将原来文件内容复制过去 import os   def alterFileName(source,target):     print("当前文件夹下有以下几个文件>>>")     file_list=os.listdir(source)  

  • Python中列表的常用操作详解

    目录 打印出列表的数据 一.列表的循环遍历 1.for循环遍历 2.while循环遍历 二.列表的添加操作 1.append() 2.extend() 3.insert() 三.列表的修改操作 四.列表的查找 1.in 2.not in 3.index 4.count 五.列表中的删除 1.del 2.pop 3.remove 六.列表的排序操作 1.排序sort() 2.reverse方法是将内容顺序反转 总结 列表的格式:变量A的类型为列表 namesList = ['xiaoWang','

  • MongoDB使用小结 一些常用操作分享

    MongoDB的使用之前也分享过一篇,稍微高阶点:见这里:<MongoDB使用小结> 1.shell登陆和显示 假设在本机上有一个端口为17380的MongoDB服务,假设已经把mongo bin文件加入到系统PATH下. 登陆:mongo --port 17380 显示DB:show dbs 进入某DB:use test_cswuyg 显示集合:show tables 2.简单查找 查找文档:db.test_mac_id.find({'a': 'b'}) 删除文档:db.test_mac_i

  • Python中快速掌握Data Frame的常用操作

    掌握Data Frame的常用操作 一. 查看DataFrame的常用属性 DataFrame基础属性有:values(元素).index(索引).columns(列名) .dtypes(类型).size(元素个数).ndim(维度数)和 shape(形状大小尺寸),还有使用T属性 进行转置 import pandas as pd detail=pd.read_excel('E:\data\meal_order_detail.xlsx') #读取数据,使用read_excel 函数调用 # pr

随机推荐