1、背景
刚开始的时候,领导给了我一张excel表,她说每周都不定期更新,每次更新都是复制粘贴,不但容易出错,而且要花费2个多小时,然后她问我,你在下班时间可以考虑怎么用脚本如python,或VBA把这个东西做批量化处理,提高一下工作效率,我说好,然后就开始构思怎么去处理这样一种格式的数据,捡起我3个月都没写脚本的热情,就展开了。刚开始也没想着一下就能完全实现,一步一步来,快一点是一点,然后写了一个不太完美的初级版本,后面在别人的刺激下对脚本进行了改善,完全做到了自动化脚本,目前已经正常无误运转了3次,打算再测4次,后面就正式投入使用了,哈哈!!!!
2、需求实现
大致的需求如下图,就是如何把原始的数据格式变成目标格式(实际上就是把区域1,2,3根据字段名做一个纵向连接),大家可以先思考一下如何去做,下面一一为大家揭晓:
原始数据:
目标样式:
相信有很多小伙伴们都能想到,就是先把每一个汇报人所属区域的起始位置和区域间隔识别出来,然后结合for用concat做个纵向拼接,不就ok了么,能想到这一步,那就离成功不远了,哈哈,下面将逐步分析怎么去是实现这个功能,即如何实现这个功能呢????。
3、分析及代码实现
(1)废话不多说,先把文件读进来再说;
(2)获取区域的起始位置:大家有没有发现,1,2,3(第一行合并单元格的数据)在excel中的数据表现和python不一致,excel占据了多个单元格,python只占用了一个单元格。从python输出来看,第一行中不为nan的数据是不是就是每一个区域的开始位置对应的值,那么是不是可以使用argwhere函数来获取它所对应的索引,也就是位置呢,下面继续,这里注意不加astype将其转化成float类型会报错,因为isnan只适用于float类型;
(3)得到区域间隔:区域间隔,不就是后项减去前项吗,这么简单的操作,构造一个新列就行,哈哈;确定了每个区域的起始位置和间隔,用个循环是不是就搞定了呢???
(4)for循环遍历区域:这里有几个注意的小点,说明一下:
1、from numpy import * 是为了使用tile函数,方便对第一列进行填充;
2、注意iloc每次取数的位置
3、为什么要使用dropna函数???这个读者可以自己去探索,去掉和不去掉有什么区别
4、concat的时候记得吧df重置索引,也就是用reset_index函数,
(5)如果是普通需求,将empty_df的数据直接用to_csv、to_excel、to_sql写入就行,是不是就ok了,哈哈,大功告成!!!
附加想法
(1)如果要遍历所有sheet,怎么弄??
file_path = "D:\\test.xlsx" # 获取文件路径
file = pd.ExcelFile(file_path)
sh_names = file.sheet_names # 获取sheet名称
# 写个循环遍历所有sheet
for sh_name in sh_names:
raw_data = pd.read_excel(file_path, sheet_name = sh_name, header = None)
(2)如何将数据写入到原始的工作表中,并且不覆盖原来的数据,怎么操作(在实际工作中其实是有这种需求的,即原始表给我留着,过程表我也要,结果表我也要,统一放在一个工作簿中)??反正直接使用to_excel是行不通的,往下看,不加engine='openpyxl’参数就会覆盖原表,不知道为啥,有兴趣的可以去探索一下:
# 将数据写入到excel文件中,过程表,在写入的时候不覆盖原表
writer = pd.ExcelWriter(file_path,engine='openpyxl') # 方便存储原表
book = load_workbook(file_path)
writer.book = book
empty_df.to_excel(excel_writer = writer, sheet_name = '过程表')
writer.save()
writer.close()
源代码
# -*- coding: utf-8 -*-
"""
遍历整个excel工作簿,对所有sheet的每个区域进行同样操作,实际上就是并到一块,做自动化处理
This is a temporary script file.
"""
import pandas as pd
from numpy import *
import numpy as np
from openpyxl import load_workbook
'''遍历sheet,对不同区域数据进行拼接'''
def deal_data(file_path, sh_name, empty_df):
raw_data = pd.read_excel(file_path, sheet_name = sh_name, header = None)
No1_row = np.array(raw_data.iloc[0, :]).astype(float) # 获取第一行的数据,方便按照区域进行划分,不加astype会报错,isnan只适用于float类型
# 得到起始位置
No1_index = np.ravel(np.argwhere(~np.isnan(No1_row))) # 获取第一行的索引,方便进行区域划分
# 求出间隔
col_num = raw_data.shape[1]
No1_index_mk = np.append(No1_index[1:], col_num) # 构造列,实现错位相减
interval = No1_index_mk - No1_index # 每次取数的间隔,也就是步长
user_col_names= ['序号']
# 将每个区域的数据拼接
length = len(No1_index)
for i in range(0, length):
begin_position = No1_index[i]
begin_interval = interval[i]
# 获取每一块区域的数据
part_data = raw_data.iloc[:, begin_position:begin_position + begin_interval]
data = part_data.dropna(how="all") # 去重掉该区域的空值
# 获取列名
input_col_names = np.array(data.iloc[1, 0:begin_interval])
# 获取实际信息
input_info = data.iloc[2:, :].reset_index(drop=True)
input_info.columns = input_col_names
row_num = len(input_info) # 获取行数,方便进行填充
# 获取序号信息并填充
user_info = np.array(data.iloc[0:1, 0]) # 获取投放用户信息
user_info_fill = pd.DataFrame(tile(user_info,(row_num,1)), columns = user_col_names).reset_index(drop=True)
# 横向拼接
data_1 = pd.concat([user_info_fill, input_info], axis = 1)
# 纵向拼接
empty_df = pd.concat([empty_df, data_1], axis = 0, sort = False)
return empty_df
if __name__ == '__main__':
file_path = "D:\\test.xlsx" # 获取文件路径
file = pd.ExcelFile(file_path)
sh_names = file.sheet_names # 获取sheet名称
empty_df = pd.DataFrame() # 创建一个空df,用来存储数据
# 将多个sheet中的数据整合到一个sheet中
for sh_name in sh_names:
empty_df = deal_data(file_path, sh_name, empty_df)
# 将数据写入到excel文件中,过程表,在写入的时候不覆盖原表
writer = pd.ExcelWriter(file_path,engine='openpyxl') # 方便存储原表
book = load_workbook(file_path)
writer.book = book
empty_df.to_excel(excel_writer = writer, sheet_name = '过程表')
writer.save()
writer.close()