金牌厨师Pandas：听说Excel处理数据分行快得很？

HeoiJin 凹凸数据

大家好，我是HeoiJin

作者简介：立志透过数据看清世界的产品策划，专注爬虫、数据分析、产品策划领域。
万物皆营销 | 资本永不眠 | 数据恒真理
CSDN：https://me.csdn.net/weixin_40679090

一、前言

最近收到一个小朋友（无中生友）发过来的需求，要将多列数据转化成多行数据，并提取指定列的数据。

但因为数据结构有点丑，而且数据量大，不好通过excel公式进行清洗，希望我利用python帮他清洗下。

那么这位小朋友碰上了什么幺蛾子，接下来一起一探究竟吧。

二、项目准备

语言：Python 3.7
IDE：Pycharm
相关库：Pandas、re、xlrd、xlwt

三、项目特色

利用ExcelFile方法读取单个Excel文件中的多个sheets

利用explode方法为dataframe中嵌套的列表解嵌套为多行数据

四、项目需求

拿到样本数据和最终效果图的那一刻,有句话想跟设计表格的大佬说，不知当讲不当讲...各位先品一品这堆数据。

吐槽归吐槽，金牌厨师胖大师表示：“这波问题不大，接下来我要展示我王者水平的操作！”

（点击可放大本图）

五、核心厨具介绍

pandas.DateFrame.explode能将dataframe的嵌套列表拆分成多行，并会复制同一行的其他元素。

六、烹饪开始

6.1 确认烹饪思路

遍历所有sheet
筛选出核心的房号、姓名、电话信息
将信息拼接成为列表
将列表拆分为多行数据
输出为csv

6.2 筛选核心食材（获取特定的columns）

观察可知，三个sheets的表头顺序都不一样，如果只利用简单粗暴的切片方式，并不能精准地获取到需要的表头。但庆幸表头名称都相同，派出正则表达式这把利刃便能轻松地完成任务。

def get_new_columns(df):    '''    >> 利用正则匹配出目标columns    >> df.columns=['栋数', '户型', '姓名', '性别', '电话', '房号', '姓名.1', '性别.1', '电话.1', '房号.1','姓名.2', '性别.2', '电话.2', '房号.2']    >> 当我们的columns重复的时候，pandas会自动帮我们在重复的columns后面加上.编号。    :param df:    :return:    '''    # 提取columns对应的字段    pattern_name=r',(姓名.?\d?),'    pattern_room=r',(房号.?\d?),'    pattern_phoone=r',(电话.?\d?),'    # 通过前后增加逗号，确保正则的精准匹配    columns_str=','+','.join(df.columns.to_list())+','    columns_name=re.findall(pattern_name,columns_str)    columns_room=re.findall(pattern_room,columns_str)    columns_phone=re.findall(pattern_phoone,columns_str)    target_columns=[]    # 将每一行的数据变为一个一层嵌套的列表    for i in range(len(columns_name)):        target_columns+=[columns_room[i],columns_name[i],columns_phone[i]]    return target_columns

6.3 调制酱料（多列数据合并为一列）

这里定义一个函数，用于将每行的元素合并成一个有一层嵌套列表的series。

# 用于rebuild_df的applydef merge_cols(Series):    # 获取非空项    Series=Series[Series.notna()]    # 获取当行所有数据    value=Series.values    result=[]    # 将每一行的数据变为一个一层嵌套的列表    for idx in range(0,len(value),3):        result.append([value[idx],value[idx+1],value[idx+2]])    return result

6.4 大火爆炒，加料调味（拆分嵌套为多行，拼接其他数据）

食材和配料我们都准备好了，下面就可以开火下锅制作佳肴

烹饪流程：

获取楼层、户型信息
利用apply方法，拼接每行数据
利用explode方法展开数据
拆分每组数据

def rebuild_df(df,merge_columns):    # 获取表格头部通用信息    # merge_columns=['房号', '姓名', '电话', '房号.1', '姓名.1', '电话.1', '房号.2', '姓名.2', '电话.2']    df_new=df.iloc[:,:2]    # 调用merge_cols函数对数据进行合并    # 注意：使用apply调用函数时不用加括号    df_new['merge']=df.loc[:,merge_columns].apply(merge_cols,axis=1)    # 通过explode变成多行    df=df_new.explode('merge')    # 拆分merge列的列表    df['房号']=df['merge'].str[0]    df['姓名']=df['merge'].str[1]    df['电话']=df['merge'].str[2]    df.drop('merge',axis=1,inplace=True)    return df

6.5 跑堂的，上菜！

我们的食材有多个sheets，通过ExcelFile方法读取所有sheets名，并在输出的文件名中添加sheets名来区分文件。

def open_file(sheets_name):    df=pd.read_excel('数据集/多行转多列数据集.xlsx',sheet_name=sheets_name)    return dfif __name__ == '__main__':    sheets_name_list=pd.ExcelFile('数据集/多行转多列数据集.xlsx').sheet_names    for sheets_name in sheets_name_list:        df=open_file(sheets_name)        merge_columns=get_new_columns(df)        df=rebuild_df(df,merge_columns)        df.to_excel(f'数据集/{sheets_name}.xls', index=False，sheet_name=sheets_name,encoding='utf-8')

ok，数据非常快就清洗完了，打开其中一个表格检查下效果，小老板搞得不丑~

七、后记

本文核心思路：

利用ExcelFile读取多个sheet
通过explode方法，将横版的数据转换为竖版

完整代码及数据集请移步至文末github地址或阅读原文

(PS.数据集为脚本随机生成的虚拟数据)

后续将推出一篇食材来自”垃圾场“的清洗实战，究竟都多脏？

先看看群友们对这份数据的看法吧~

欲知后事如何，请您下回分解~
github地址：https://github.com/heoijin/Date-Clean

参考资料：
pandas原文档：
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.explode.html

金牌厨师Pandas：听说Excel处理数据分行快得很？