对于昨天写的用python实现自动化办公------Excel操作进入了全站热榜，真的很让我意外，但是对于Excel的自动化操作还远远没有结束。python自动化办公之Excel还有很长一段路要走，读者朋友们可以关注我，对于Excel的操作我会进行不定时的更新，也会进一步改善我的表达方式，从而给读者一个美好的阅读体验。

文章图片

来看看今天学习的内容：

用python实现自动化办公------Excel操作

• • 摘要
  • 1. 多表联合
  • 2. 数据校验
  • 3. 数据分割
  • 4. 统计
  • 5. 删除重复数据
  • 6. 数据表转置
  • 7. CSV、TSK、TXT文件操作
  • 8. 透视表
  • 9. 线性回归、数据预测

摘要 考虑到Excel操作需要真实数据作为支撑，读者，尤其是根据本文进行学习的读者无法直接使用Excel数据表进行学习，所以本文会在列出的python文件中前段代码为生成的xlsx文件的代码，具体操作如下所示,。对于代码的解释，重要的部分已经在代码中进行解释。

文章图片

1. 多表联合 Excel功能：实现多表联合，列出学生对应的成绩，如果没有找到相应数据，则将Score设置为0，相关公式：IFNA(VLOOKUP(),0)

文章图片


文章图片

python实现方法：
learn_14.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt#create random data# id=[i for i in range(1,40,2)] # name=[] # for i in id: #before_name='Student_' #before_name+=str(i) #name.append(before_name) # # df1=pd.DataFrame({ #'Id':pd.Series(id), #'name':pd.Series(name) #}) # df1.set_index('Id',inplace=True) # print(df1) # df1.to_excel('./dataframe/learn_14.xlsx')#vlookup Student=pd.read_excel('./dataframe/learn_14.xlsx',sheet_name='Student') Score=pd.read_excel('./dataframe/learn_14.xlsx',sheet_name='Score') # Student.set_index('Id',inplace=True) # Score.set_index('Id',inplace=True) # print(Student) # print(Score) #相当于Excel中的vlookup查询Student和Score两张表Id列所对应的分数 # 合并成一张表,how为左边中的表无论如何，都要保持完整 #当数据为NA时，填充为0 #left_on=列名，right_on=列名 table=Student.merge(Score,how='left',on='Id').fillna(0) table.Score=table.Score.astype(int)#将Score列转换为整数 print(table) Student['Score']=table['Score'] print(Student) Student.set_index('Id',inplace=True) Student.to_excel('./dataframe/learn_14_last.xlsx')

实现效果：

文章图片

2. 数据校验 Excel功能：对数据进行校验，对不合理的数据进行圈定。

文章图片

python实现方法：
learn_15.py

```python import pandas as pd import numpy as np import random import matplotlib.pyplot as plt#create random data# id=[i for i in range(1,21)] # name=[] # for i in id: #before_name='Student_' #before_name+=str(i) #name.append(before_name) # # df1=pd.DataFrame({ #'Id':pd.Series(id), #'name':pd.Series(name), #'score':pd.Series(range(-40,160,10)) #}) # df1.set_index('Id',inplace=True) # print(df1) # df1.to_excel('./dataframe/learn_15.xlsx')def score_validation(row): #方法一 # try: #assert 0<=row.score<=100 # except: #print(f"#{row.Id}\t student {row.name} has invalid score{row.score}")#方法二 if not 0<=row.score<=100: print(f"#{row.Id}\t student {row.name} has invalid score{row.score}")#数据校验 df=pd.read_excel('./dataframe/learn_15.xlsx') # print(df) df.apply(score_validation,axis=1)#校验从上到下进行校验

实现效果：

文章图片

3. 数据分割 Excel功能：将full_name列以空格为分隔符分割成两列。

文章图片

python实现：
learn_16.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt#create random data# id=[i for i in range(1,21)] # name_select=[chr(i) for i in range(97,123)] # name=[] # for i in range(20): #name_before='' #for i in range(5): #name_before+=random.choice(name_select) #name_before+=' ' #for i in range(5): #name_before+=random.choice(name_select) #name.append(name_before.title()) # # # df1=pd.DataFrame({ #'id':pd.Series(id), #'full_name':pd.Series(name) #}) # df1.set_index('id',inplace=True) # print(df1) # df1.to_excel('./dataframe/learn_16.xlsx')#拆分单元格 employees=pd.read_excel('./dataframe/learn_16.xlsx') # print(df) df=employees['full_name'].str.split(n=3,expand=True)#默认空格，seres str split employees['first_name']=df[0].str.upper()#大写 employees['last_name']=df[1] print(employees) employees.set_index('id',inplace=True) employees.to_excel('./dataframe/learn_16_last.xlsx')

实现效果：

文章图片

4. 统计 Excel功能：实现总分、平均分汇总

文章图片

python实现：
learn_17.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt#create random data# id=[i for i in range(1,21)] # name_select=[chr(i) for i in range(97,123)] # name=[] # for i in range(20): #name_before='' #for i in range(5): #name_before+=random.choice(name_select) #name_before+=' ' #for i in range(5): #name_before+=random.choice(name_select) #name.append(name_before.title()) # # # df1=pd.DataFrame({ #'id':pd.Series(id), #'full_name':pd.Series(name), #'test1':pd.Series([random.choice(range(40,100)) for i in range(20)]), #'test2':pd.Series([random.choice(range(40,100)) for i in range(20)]), #'test3':pd.Series([random.choice(range(40,100)) for i in range(20)]) #}) # df1.set_index('id',inplace=True) # print(df1) # df1.to_excel('./dataframe/learn_17.xlsx')#统计分析 students=pd.read_excel('./dataframe/learn_17.xlsx',index_col='id') # print(students) temp=students[['test1','test2','test3']] print(temp) result=temp.sum() print(result) row_sum=temp.sum(axis=1)#从左到右为1，从上到下为0 row_mean=temp.mean(axis=1) students['total']=row_sum students['average']=row_mean print(students) col_mean=students[['test1','test2','test3','total','average']].mean() print(col_mean) col_mean['full_name']='summary' print(col_mean) students=students.append(col_mean,ignore_index=True) print(students) students.to_excel('./dataframe/learn_17_last.xlsx')

实现效果：

文章图片

5. 删除重复数据 Excel功能：删除重复的数据（id[1:5]和id[21:25]重复）。

文章图片

python实现：
learn_18.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt#create random data# id=[i for i in range(1,26)] # name_select=[chr(i) for i in range(97,123)] # name=[] # for i in range(20): #name_before='' #for i in range(5): #name_before+=random.choice(name_select) #name_before+=' ' #for i in range(5): #name_before+=random.choice(name_select) #name.append(name_before.title()) # df1=pd.DataFrame({ #'id':pd.Series(id), #'full_name':pd.Series(name), #'test1':pd.Series([random.choice(range(40,100)) for i in range(20)]), #'test2':pd.Series([random.choice(range(40,100)) for i in range(20)]), #'test3':pd.Series([random.choice(range(40,100)) for i in range(20)]) #})# print(df1) # print(df1['full_name'].at[6]) # for i in range(20,25): #df1['full_name'].at[i]=df1['full_name'].at[i-20] #df1['test1'].at[i]=df1['test1'].at[i-20] #df1['test2'].at[i]=df1['test2'].at[i-20] #df1['test3'].at[i]=df1['test3'].at[i-20] # df1.set_index('id',inplace=True) # print(df1) # # df1.to_excel('./dataframe/learn_18.xlsx')#清除重复数据 df=pd.read_excel('./dataframe/learn_18.xlsx') df.drop_duplicates(subset='full_name',inplace=True,keep='first')#subset设置列名keep为如果有重复的数据保留第一个出现的last/first print(df) #查询重复数据 # dupe=df.duplicated(subset='full_name') # print(dupe) # print(dupe.any()) # dupe=dupe[dupe] # print(dupe.index) # print(df.iloc[dupe.index]) df.set_index('id',inplace=True)df.to_excel('./dataframe/learn_18_last.xlsx') # df.to_excel('./dataframe/learn_18_last.xlsx',sheet_name='最终数据')

实现效果：

文章图片

6. 数据表转置 Excel功能：实现列和行的转置

文章图片

python实现：
learn_19.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt#create random data# id=[i for i in range(1,26)] # name_select=[chr(i) for i in range(97,123)] # name=[] # for i in range(20): #name_before='' #for i in range(5): #name_before+=random.choice(name_select) #name_before+=' ' #for i in range(5): #name_before+=random.choice(name_select) #name.append(name_before.title()) # df1=pd.DataFrame({ #'Month':pd.Series(pd.date_range('20210101',periods=30)), #'Active_user':pd.Series([random.choice(range(400,1000)) for i in range(30)]), #'Page_view':pd.Series([random.choice(range(100,3000)) for i in range(30)]), #}) # df1.set_index('Month',inplace=True) # df1.to_excel('./dataframe/learn_19.xlsx')#旋转表格 pd.options.display.max_columns=999 df=pd.read_excel('./dataframe/learn_19.xlsx',index_col='Month') table=df.transpose()#旋转 print(table) table.to_excel('./dataframe/learn_19_last.xlsx') df.to_csv('./dataframe/learn_19_last.csv') df.to_csv('./dataframe/learn_19_last.txt')

实现效果：

文章图片

7. CSV、TSK、TXT文件操作 【python自动化办公|用python实现自动化办公------Excel操作(2)】Excel功能：实现从Excel中获取csv、tsk、txt文件的数据。
python实现：
本代码中需要用的csv和txt文件已经在learn_18中生成。
learn_20.py

student1=pd.read_csv('./dataframe/learn_19_last.csv')#CSV文件和TXT文件都可以通过read_csv读取 print(student1) student2=pd.read_csv('./dataframe/learn_19_last.txt',sep=',',) print(student2)

8. 透视表 Excel功能：实现Excel的透视表功能（excel文件可以私聊我）

文章图片


文章图片

python实现：
learn_21.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt import numpy as np pd.options.display.max_columns=999 orders=pd.read_excel('./dataframe/Orders.xlsx')orders['Year']=pd.DatetimeIndex(orders['Date']).year print(orders.head(10))#数据透视表方法一 # pt1=orders.pivot_table(index='Category',columns='Year',values='Total',aggfunc=np.sum) # print(pt1) # pt1.to_excel('./dataframe/Orders_toushibiao_1.xlsx')#数据透视表方法二groups=orders.groupby(['Category', 'Year']) sum=groups['Total'].sum() count=groups['ID'].count() pt2=pd.DataFrame({ 'sum':sum, 'count':count })pt2.to_excel('./dataframe/Orders_toushibiao_2.xlsx')

实现效果：
方法一：

文章图片

方法二：

文章图片

9. 线性回归、数据预测 Excel功能：实现数据的线性回归方程，数据预测

文章图片

python实现：
learn_22.py

import pandas as pd import numpy as np import random import matplotlib.pyplot as plt from scipy.stats import linregress#create random data# df1=pd.DataFrame({ #'Month':pd.Series(pd.date_range('20210101',periods=30)), #'Active_user':pd.Series([random.choice(range(400,1000)) for i in range(30)]), #}) # df1.set_index('Month',inplace=True) # df1.to_excel('./dataframe/learn_22.xlsx')#线性回归，数据预测 sales=pd.read_excel('./dataframe/learn_22.xlsx',dtype={'Month':str}) print(sales.index)slope,intercept,r,p,std_err=linregress(sales.index,sales.Active_user) exp=sales.index*slope+intercept plt.plot(sales.index,exp,color='orange') plt.scatter(sales.index,sales.Active_user) plt.xticks(sales.index,sales.Month,rotation=90) plt.title(f'y={slope}*x+{intercept}') plt.tight_layout() plt.show()

实现效果：

文章图片

今天的《用python实现自动化办公------Excel》操作就分享到这儿了，欢迎各位读者能够进行点评和分享。
如果对我的文章感兴趣，请为我点一个赞，如果有python的知识需要了解或探讨,可以加本人微信cuiliang1666457052

推荐阅读

• 

  你们认为诗仙李白最好的一首诗是哪一首？
• 

  一天喝多少牛奶合适？
• 

  青芒的热量高吗
• 

  花胶有什么营养价值
• 

  土大黄的功效与作用 土大黄的副作用
• 

  美国冲突升级!武装民兵冲进议会大厦,议员吓的穿防弹衣上班,州长命令被废,大乱了吗？
• 

  最新热门的12个最佳博客平台合集（以及如何选择一个）
• 

  买笔记本电脑,处理器是选择i5还是i7？
• 

  妒组词语 妒有哪些组词呢
• 

  熊童子简笔画
• 

  Photoshop制作沧桑颓废的城市海报
• 

  手机停机怎么连接网络，手机停机怎么连接网络电视
• 

  176升冰箱购买指南及故障维修解决方案
• 

  早晨八点半
• 

  升级换代，让您的打印更高效 epsonL3153变成2710
• 

  海带有味道了还能吃吗
• 

  照门是什么意思 照门的含义
• 

  获嘉天气30天 获嘉天气预报
• 

  核酸检测假阳性怎么处理
• 

  分享一首小诗（相信）

• python自动化办公|用python实现自动化办公------定时发送邮件
• python自动化办公|用python实现自动化办公------定时发送微信消息
• 如何在Python 3中从SRT文件中重新同步电影的字幕(移位)
• Deep|深度学习实战案例（预测房价）
• opencv|Mean Shift算法查找物体(Python)
• 机器学习|机器学习:1、线性回归--波士顿房价预测
• py|第十三届蓝桥杯大赛软件赛省赛 Python 大学 B 组
• python-pygame实现飞机大战-5-屏幕渲染绘制分数生命数超级炸弹数以及暂停功能
• #导入MD文档图片# 推荐一款阿里最新 Python 自动化开源工具！