本文介绍了Python在办公自动化中的应用，包括创建和操作Series与DataFrame，读取Excel文件，设置数据类型，处理缺失值，以及数据输出到Excel。此外，还探讨了如何对DataFrame进行操作，如插入、删除列，去重，拼接，字符串处理等。通过实例展示了如何利用pandas读取、处理和输出Excel数据，以及如何进行数据清洗和预处理。
主要用于利用python实现一定程度的办公自动化。
注意python对格式和大小写要求严格，不能像SQL一样。
1、创建Series&Dataframe
#创建一列基本的Series
s1=pd.Series([1,2,3])
s2=pd.Series([1,2,3.0])
s3=pd.Series(['第一','第二','第三'])
print(s1)
print(s2)
s3
#结果如下
0 1
1 2
2 3
dtype: int64 #dtype就是数据类型 第一列是默认的数字索引（默认从0开始）
#创建指定索引和数据类型的Series
#方法1：直接设定
s = pd.Series([1,2,3],
index=list('abc'),#设定行索引
dtype='int64', #设定数据类型
name='num') #设定列名
s
#结果如下
a 1
b 2
c 3
Name: num, dtype: int64
#方法2：利用字典 这样就不用再去设定索引了
d = pd.Series(
{'a':1,'b':2,'c':3} #abc是字典的key(键),可以作为索引
)
d
#结果如下
a 1
b 2
c 3
dtype: int64
#直接创建DataFrame
df=pd.DataFrame([ [1,2,4],
[3,6,9],
[7,5,6] ],
columns=['A','B','C'], #columns是列索引
index=['x','y','z'] # index是行索引
# 也可以写成 index=list('xyz')
)
df
#同样可以利用字典省去设定索引 这里不做演示了
2、读取excel
路径
df = pd.read_excel(C:/Users/Administrator/Desktop/原表.xlsx,
sheet_name='水果' #加入参数可以读取特定Sheet
sheet_name=1 ) #注意从0开始，即1代表Sheet2
sheet_name=[0,1] #提取多个sheet写成数组形式
)
#以上为绝对路径推荐使用，一般我们复制属性中的路径，是\而非/,所以保险可以再最前面加r，意思是告诉python别把\当转义符
即：pd.read_excel(r'C:\Users\Administrator\Desktop/原表.xlsx'
后面的内容都是加入各种参数达到各种目的
读取的参数
df = pd.read_excel('原表.xlsx',
1特定sheet sheet_name='水果',
2表头 header=[0,1], #设定表头 此处代表让第一行第二行都成为表头
3列索引 index_col=(0) #设定第一列为你的列索引，否则是默认的01234
)
#######################################################################
###如果是原表没有表头的情况，就要自己设置一个表头
df = pd.read_excel('python_read_test.xlsx',
sheet_name='水果',
header=None, #一定要=None 否则命名后会替换原来的的第一行数据
names=['第一列','第二列','第三列']
####################################################################
4读取特定列 usecols='A,B:D'
usecols=[0,1:3]
usecols=['科目','小明','小刚']
5跳过特定行 skiprows=[1,2,3,4,5,6,7,8]
6设定数据类型 dtype={'小明':str}
8定义缺失值（不常用） na_values=['a',' '] #这里设定了a 空格 为缺失值
na_values={'第一列':['a',' ']} #也可以设定特定列
如果字段名太多且复杂，手敲就比较累，因此介绍split函数
复制你表格的第一行并后缀加上.split()
‘科目 小明 小红 小刚 小孙’.split()
返回结果：
[‘科目’, ‘小明’, ‘小红’, ‘小刚’, ‘小孙’]
——————数字类型列表
时间的处理（暂不学）
3、输出excel
所谓浮点就是小数点，保留几位
1.输出一个sheet
df.to_excel('test.xlsx',
1表名字 sheet_name='output',
2是否要默认索引 index=False,
3标题 header=['及格','不及格'], # 换抬头也可以
6数字保留小数点几位 float_format='%.2f' #保留两位小数点
)
2.同时输出多个Sheet
with pd.ExcelWriter('cj.xlsx'
) as writer: #这里的Writer只是定义的名字，可以随意换
df1.to_excel(writer, sheet_name='小明的成绩') # Sheet1
df2.to_excel(writer, sheet_name='小刚的成绩') # Sheet2
3.将一份大Excel 等量拆分
将一份有N行的大excel（总表）平均拆分成若干excel（小表），每份小表有limit行
以下代码以总表9136行为例，每份1000行，拆成10个小文件，最后一个文件136行
#确定操作目录 自动创建文件夹
work_dir = 'C:/Users/Administrator/Desktop' #操作目录
split_dir = f'{work_dir}/splits' #加f表示后面{}内的是变量
import os
import pandas as pd
if not os.path.exists(split_dir): #如果没有目录就自动创建，已经有就无事发生
os.mkdir(split_dir)
import pandas as pd
df_total=pd.read_excel('simplify.xlsx')
#加入一个特征列，这个列的作用是对所有数据进行分组，每组1000个
df_total.insert(0, "split_reason", 'res', True)
total_row_count = df_total.shape[0] #总行数
print('总行数： ',total_row_count,'条数据')
#（自定义）每份需要的数据数量
limit=1000
print('每份文件:',limit,'条数据')
#确定均等拆分的文件个数
split_size = total_row_count // limit # //是向下取整 split_size=拆分后的文件个数
if total_row_count % limit != 0: #如果总行数/1000 的余数不等于0则
split_size +=1
print('拆分成: ',split_size,'份')
print('第',split_size,'份:',total_row_count-(split_size-1)*limit,'条数据')
# 循环选取每1000列对split_reason进行赋值
for i in range(1,split_size):
#start, end = 1+limit*(i-1), limit*i+1
df_total.loc[limit*(i-1):limit*i-1,'split_reason']=i
# 可以根据split_reason进行分组从而输出不同
for j in df_total['split_reason'].unique():
filename = split_dir + "/" + str(j) + ".xlsx" #字符串的暴力拼接，这两行如果看不懂也可以用最后一行代替
df_total[df_total['split_reason']==j].drop('split_reason',axis=1).to_excel(filename,index=False)
#df_total[df_total['split_reason']==j].drop('split_reason',axis=1).to_excel(r'C:\Users\Administrator\Desktop/splits/{excelname}.xlsx'.format(excelname=j),index=False)
print('第',j,'份','拆分完成！见：',split_dir,'/',j)
最后一步可以改成TXT格式（先转成CSV 直接改名字 成TXT）
filename = split_dir + "/" + str(j) + ".txt" #这里命名直接以TXT为后缀，因为CSV可以直接向txt转化
df_total[df_total['split_reason']==j].drop('split_reason',axis=1).to_csv(filename,index=False,sep='\t')# ,sep='\t' 识别回车键进行换行，否则会挤在一起
#df_total[df_total['split_reason']==j].drop('split_reason',axis=1).to_excel(r'C:\Users\Administrator\Desktop/splits/{excelname}.xlsx'.format(excelname=j),index=False)
print('第',j,'份','拆分完成！见：',split_dir,'/',j)
4、对Df的操作 （直接形成新Df）
不懂的查pandas官方文档 →pandas 文档
意思是已有一个df1，对其简单做操作变成df2，而不需要傻傻用字典重新搞一次
（1）插入、去重、挑选、去缺失
1、选部分整列
df2=pd.DataFrame(df1,columns=['rule', 'Tzone'])
df2=df1[['A','C']]
2、选部分整行（对某列进行筛选）
df2=df1.query(" Country in ['ES', 'FI', 'FR'] ")
#（df2=df1.query("A in [7]")如果是数字不要加引号）
df2=df1.query('A'>'B') #如果是等于是 ==
df['x':'y']
df[0:2] #返回0和1行(无2行)
旺旺：
DF1[DF1['date'].apply(lambda x: x not in list(DF2['date']))]
3、对Df1切片，切出df2
切一个点 df.loc['x','A']
切区域： df.loc[['x','z'],['A','B']]
:代表选择全部 df.loc[:,['A']]
df.loc[0:2,['A']]
::2代表步长为2 df.loc[::2,'A']
::-1是倒序 df.loc[::-1,'A']
切特定行（且） df[ (df['B']>2) & (df['B']<6) ]
切特定行（或） df[ (df['B']==2) | (df['C']==9) ]
切剩余行（取反） df[~(df['B']==6)]
3、插入一列（只能原地替换） #因此不能df2=df1.insert
df1.insert(1, "Country", CC_z9cs, True)
#1是插入位置
#列值可以是已有的列，也可以自己输入' '
#True是是否允许与其他列名重复
4、删除一列
df1.drop('short_id',1,inplace=True)
#1是删除列 0是删除行
5、去重
df1.drop_duplicates(subset=['A','B'],keep='first',inplace=True)
#subset是根据哪一列去判断重复，这里是指AB同时都是重复才行
#keep 是保留第几条数据
6、拼接多列
df['date'] = df['year'].map(str)+"/"+df['month'].map(str)+"/"+df['day'].map(str)
7、查询缺失值情况
df1['字段名'].isnull() #依次查询该字段每个值
df1['字段名'].isnull().any() #查询该字段是否全是缺失值
df1.isnull().sum()#查询整个表格的缺失值
8、去除缺失值
dropna(axis=0, subset=['姓名'],how='any', inplace=False)
#axis：0按行删；1按列删
#how：筛选方式。‘any’，该行/列只要有一个na，就删除该行/列；‘all’，表示该行/列全都为na，才删除该行/列。
#subset：条件判断区域,默认不填就是全表范围
#inplace：是否原地替换
#注意，这样无法去除只有若干空格的字符，所以如果需要去除有空格的字符需要将空格也定义成NA（需要numpy）
df1.replace(to_replace=r'^\s*$', value=np.nan, regex=True, inplace=True)
#然后dropna
9、是否是空表（返回布尔）
df1.empty #可以not df1.empty
（2）拼接两表
1、merge拼接（vlookup）
dflink = pd.merge(df1, df2, how='left', on=['主键'])
#这里建议左连接，其中df1应该是大表，主键相当于vlookup时的第一列
#df1 df2都应该要有相同名字的列作为主键
2、上下、左右直接拼接
res = pd.concat([df1, df2, df3], axis=0,ignore_index=True)
axis=0上下;1左右
ignore_index:是否重置index
#要保证每个表格的列名一致
5、对字符串处理(str.)
首先要将df中的某列进行提取List ↓
如果没有这一步直接df[‘A’]，返回的是一个一列的Dataframe的表格，无法进行字符串的处理。
cols = df['A'].tolist()
1、修改类
1、去除两边空格 df['A'] = df['A'].str.strip()
2、大小写 df["英文名"].str.upper()
df["英文名"].str.lower()
3、左填充 df["住址"].str.pad(10,fillchar="a")
右填充 df["住址"].str.pad(10,side="right",fillchar="*")
两边填充 df["家庭住址"].str.center(10,fillchar="*")
4、重复3遍 df["姓名"].str.repeat(3)
5、替换 df["身高"].str.replace(":","-") # :替换为-
指定位置替换 df["电话"].str.slice_replace(4,8,"*"*4)
#将位置（4,8]的字符 替换成给定的字符*乘4
6、拼接 df['AB']=df['A'].str.cat(df['B'] , sep='|')
#sep添加分隔符,可省略
2、判断类
df['字段名'].str.startswith('x') # 判断是否以x开头
df['字段名'].str.endswith('x') # 判断是否以x结尾
df['A'].str.contains('a|测') #判断A列中是否 包含a 或者 包含测
df['A'].str.contains('a','测') #找到A列中是否 既包含a又包含测的
PS：在判断类的语句外包上df[ ] 就可以展示出 符合条件的字符串 所在的整个行
df[df['姓名'].str.startswith('黄') ]
3、计算类
df['字段名'].str.count('3') #计算特定字符在字符串中出现的次数
df["姓名"].str.len() #计算字符长度空格也算一个字符 在任何操作后直接.str.len()也可
4、查询类
df['姓名'].str.get(0) #查询该字段中所有字符串的第一个字符，同理-1是最后一个字符
df["身高"].str.split('_') #将所有字符串按规定的字符为界限，分成两个[ , ]
df["身高"].str.split(":",expand=True) #加expand=True 会把这个字段分成两列(两个字段) [ ] [ ]
df[["身高描述","final身高"]] = df["身高"].str.split(":",expand=True) #可以通过赋值增加字段名
df["身高"].str.split(":").str.join("a"*2) #将分隔字符替换成aa
对查询后的字段再查询
注意get和str.get的区别
df["身高"].str.split('_').get(0) #这返回的是该字段下被split分好的第一个字符串 [' ',' ']
df["身高"].str.split('_').str.get(0)#这返回的是该字段下的所有字符串的前半部分（以_为界限）是一个竖着的List
6、For循环 变量赋值
1、基础语法
- 赋值数组
for i in range(0,9): #意思是设定一个临时变量i，对他依次赋值[0,9)
print(i)
- 赋值列、多个字符
for i in ['asd','qwe','zxc']:
print(i)
3、依次赋值字母
for char in 'woodman木头人':
#结果是w o o d m a n 木 头 人
2.连接所有值
for i in ['asd','qwe','zxc']:
print(i, end='|')
#结果 asd|qwe|zxc|
更多可参考https://zhuanlan.zhihu.com/p/29277658
7、def 自定义方法函数
1、语法介绍
例子1： 找到指定表中的指定字段最大长度 （并进行提醒）
兼顾异常处理
def catch_length(sheet_Name,mastername):
try: #try直接运行
print(mastername , '最大字段字符串长度:', sheet_Name[mastername].str.len().max(),'（超过35清理原表）')
except AttributeError: #如果try的结果是这个错误则
print(mastername , '全空')
for n in range(3):
catch_length(wb,"MasterName" + str(n+1))
2、如何调用自定义中的变量
方法中生成的变量是专属于该方法的变量，并不是公有变量
下例中的KNVP_Z001_AG非公有变量
目前的思路是：（如下代码）
1、利用return 让方法导出变量结果
2、在方法外进行赋值
def KNVP_001(AG):
KNVP_Z001_AG= pd.DataFrame({
'SOURCE_ID':Z001_serial_create,
'PARTNER': Z001_serial_create,
'VKORG': Z001_salesorg,
'VTWEG':'10',
'SPART':'00',
'KNVP--ACTION_CODE':'I',
'PARVW':AG})
return KNVP_Z001_AG
KNVP_Z001_AG = KNVP_001('AG')
正则表达式
2、中文字符集
u"[\u4e00-\u9fa5]”
3、阿拉伯数字集
r'(\d+)'
3、re模块常用方法
最终：数据清理的一般流程
观察数据
import pandas as pd
import numpy as np
- 初步探查
查看数据中有无缺失值,了解数据给维度格式的分布
data.info()
Column Non-Null Count Dtype
0 排名 2918 non-null int64
1 财富 2918 non-null int64
2 排名变化 2918 non-null object
3 姓名 2918 non-null object
4 性别 2918 non-null object
5 企业 2918 non-null object
6 行业 2918 non-null object
dtypes: int64(2), object(5)
memory usage: 159.7+ KB
- 属性初步探查
查看给数据的具体属性,确定具体的数据清洗方案
data.head()
- 清洗前的深度探查
我要清洗的数据进行详细的研究和探查
- 查找同类问题
data['姓名'].str.split('、',expand=True).info()
- 提出问题:是否存在,兄弟党、父子档、姐妹档等明显影响性别分割的组合
筛选出非单人登榜的组合
a=data['姓名'].str.split('、',expand=True)
data.loc[a[a[1].notnull()].index][20:]
- 结论
数据杂乱: 在姓名、性别,行业出现了多内容包含的数据,可以进一步挖掘
数据缺失: 在性别中可以明显看到年龄未知的情况
清洗思路
将问题划分为两类,即单人入榜,和多人进行清洗
- 数据拆分
data_many=data.loc[a[a[1].notnull()].index]
data_single=data.loc[a[a[1].isnull()].index]
- 单人上榜数据清洗
对年龄数据进行拆分
data_single['年龄']=data_single['性别'].str.split('\xa0\xa0',expand=True)[1]
data_single['性别']=data_single['性别'].str.split('\xa0\xa0',expand=True)[0]
- 将未知的年龄转为空
data_single['年龄']=data_single['年龄'].replace('未知',np.nan).astype('float')
- 对行业数据进行拆分
data_single['行业']=data_single['行业'].str.split('：',expand=True)[1]
data_single['行业']=[i.split('、') for i in data_single['行业']]
- 给上榜打上标签
data_single['是否单人上榜']=1
data_single.head()
- 多人上榜数据清洗
初步清洗: 将数据拆分
- 年龄拆分
a=data_many['性别'].str.split('、',expand=True)
- 姓名拆分
b=data_many['姓名'].str.split('、',expand=True)
- 将姓名与年龄做对应
data_many_1=b[[0]]
data_many_1['性别_0']=a[0].str.split('\xa0\xa0',expand=True)[0]
data_many_1['年龄_0']=a[0].str.split('\xa0\xa0',expand=True)[1]
data_many_1[1]=b[1]
data_many_1['性别_1']=a[1].str.split('\xa0\xa0',expand=True)[0]
data_many_1['年龄_1']=a[1].str.split('\xa0\xa0',expand=True)[1]
- 查看初步提取结果
data_many_1.head()
扫一扫
214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
