python遍历sheet并将处理结果存储到当前工作表中

python数据分析 专栏收录该内容
8 篇文章 0 订阅

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()

  • 4
    点赞
  • 0
    评论
  • 30
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:书香水墨 设计师:CSDN官方博客 返回首页

打赏作者

python_new

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

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

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

打赏作者

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

抵扣说明:

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

余额充值