金牌厨师Pandas：听说Excel处理数据分行快得很？
大家好，我是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的嵌套列表拆分成多行，并会复制同一行的其他元素。
| 方法 | 参数详解 |
|---|---|
| DataFrame.explode(self, column: Union[str, Tuple]) → 'DataFrame' | column：要进行操作的列名，可以传入字符串或者元组； 注意列名应该是唯一的，否则会引发ValueError； 返回dataframe（如果整个dataframe仅有一列，会自动在转换为series） |
六、烹饪开始
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的apply
def 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 result6.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 df
if __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
近期文章，点击图片即可查看
后台回复关键词「进群」，即刻加入读者交流群~
五
送大家一个小福利
留言点赞前三名中，小五随机抽取一名送书
时间截止到明天3.22日晚9点
朱小五