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

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

摘要

考虑到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文件操作

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

  • 11
    点赞
  • 79
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 11
    评论
评论 11
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

穆穆Max

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值