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

约定不等于承诺〃 2023-05-28 14:20 18阅读 0赞

↑ 关注 + 星标 ~ 有趣的不像个技术号

每晚九点，我们准时相约  ![format_png][]

![format_png 1][]

大家好，我是HeoiJin![format_png 2][]

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

## 一、前言 ##

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

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

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

## 二、项目准备 ##

语言：Python 3.7

IDE：Pycharm

相关库：Pandas、re、xlrd、xlwt

## 三、项目特色 ##

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

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

## 四、项目需求 ##

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

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

![format_png 3][]

（点击可放大本图）

## 五、核心厨具介绍 ##

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

<table> 
 <thead> 
  <tr> 
   <th>方法</th> 
   <th>参数详解</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DataFrame.explode(self, column: Union[str, Tuple]) → 'DataFrame'</td> 
   <td><p>column：要进行操作的列名，可以传入字符串或者元组；</p><p>注意列名应该是<strong>唯一</strong>的，否则会引发<strong>ValueError</strong>；</p><p>返回dataframe（如果整个dataframe仅有一列，会自动在转换为series）</p></td> 
  </tr> 
 </tbody> 
</table>

## 六、烹饪开始 ##

**6.1 确认烹饪思路**

1.  遍历所有sheet
2.  筛选出核心的房号、姓名、电话信息
3.  将信息拼接成为列表
4.  将列表拆分为多行数据
5.  输出为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 result

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

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

烹饪流程：

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

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

![format_png 4][]

**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，数据非常快就清洗完了，打开其中一个表格检查下效果，小老板搞得不丑~

![format_png 5][]

## 七、后记 ##

**本文核心思路：**

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

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

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

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

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

![format_png 6][]

欲知后事如何，请您下回分解~

github地址：https://github.com/heoijin/Date-Clean

参考资料：

1.  pandas原文档：
    
    https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.explode.html

![format_png 7][]

**近期文章，点击图片即可查看**

[![format_png 8][]][format_png 8 1]

[![format_png 9][]][format_png 9 1]

[![format_png 10][]][format_png 10 1]

[![format_png 11][]][format_png 11 1]

**后台回复关键词「****进群****」，即刻加入读者交流群~**

![format_png 12][]

**五**

**送大家一个小福利**

**留言****点赞前三名****中，小五****随机抽取一名****送书**

**时间截止到明天3.22日晚9点**

![format_png 13][]

朱小五

[format_png]: /images/20230528/95e78354a17841c5a13b216a24fb2422.png
[format_png 1]: /images/20230528/00c0a013383b415a9de525077bbcee22.png
[format_png 2]: /images/20230528/20c1872e1ae2474fa54a2162acb26c3e.png
[format_png 3]: /images/20230528/e63211c2637543fa8436c8d0d78d0526.png
[format_png 4]: /images/20230528/619c7e167eff473bace5ca81d2d017f7.png
[format_png 5]: /images/20230528/d19f5e3cfc0a4ea7a873f70b1a17d21e.png
[format_png 6]: /images/20230528/c287c4bff4a3463b87bf95f4e99f1015.png
[format_png 7]: /images/20230528/fac98125e0cb4a228df1fafa06bc1c24.png
[format_png 8]: /images/20230528/a6172463822c43ba8abf81c64a8006cb.png
[format_png 8 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d22c5c93aabd319ae29eddb317e70b213f5e4f99ccd6f3f06113da094786adf9f9a26f347&idx=1&mid=2247485249&scene=21&sn=1edddc4cd39d15da613dc281fce804a8#wechat_redirect
[format_png 9]: /images/20230528/b7f4050e835a44569a8cf7d5423a91bd.png
[format_png 9 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d22bfc93aaba9570ddb3573567b05b7b92d5a43e3857f74ee875e260ce678c90c232d0d5b&idx=2&mid=2247485243&scene=21&sn=9abe92ca1b7edc2e7dd289bfa0afeee5#wechat_redirect
[format_png 10]: /images/20230528/346694a0db9943fcb9a92625ebde57e4.png
[format_png 10 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d22bfc93aaba9bd906b43ccef454f9d94eed943a51d3c6901eb188a3cca3f8d8036ece866&idx=1&mid=2247485243&scene=21&sn=ed9387be66ef9b9711b57e79ac8a0baf#wechat_redirect
[format_png 11]: /images/20230528/c111b4caef5a4d20b9bf6864894bf64c.png
[format_png 11 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4d22c5c93aabd375f0416df3533030485c7b50704b5b1c0a5f5435444133e2f7dfbbedf2c4&idx=3&mid=2247485249&scene=21&sn=70fd5fdead0de3b5003a5f7d9f559886#wechat_redirect
[format_png 12]: /images/20230528/290358147a7a437497b8d58f1999d7d3.png
[format_png 13]: /images/20230528/9ad822314db8408cad8694f02b4b00b5.png