如何用Python批量导入Excel并用Pandas整合

最新推荐文章于 2024-08-08 14:14:43 发布

北辰Rick

最新推荐文章于 2024-08-08 14:14:43 发布

阅读量6.6k

点赞数 12

本文链接：https://blog.csdn.net/weixin_42339449/article/details/87067642

版权

本文介绍了如何利用Python的Pandas库批量导入Excel文件，并进行数据整合。首先，导入所需模块，查看工作路径确保能访问到文件。接着，获取指定文件夹中的.xlsx文件名，将每个文件读取为DataFrame。然后，合并所有DataFrame，根据特定列删除重复项。最终，处理后的数据被导出到新的Excel文件，得到272行35列的数据结构。

摘要由CSDN通过智能技术生成

Step 1 : 导入模块

import pandas as pd
import os

Step 2 : 查看工作路径
该工作路径应为想读取文件的文件夹，若python的工作路径不是该文件夹，则应用os.chdir来进行切换

os.getcwd()

'/Users/chenyuwang/Desktop/IAQF/data/dataList'

Step 3 : 获取文件夹中文件名为.xlsx的文件名，并存在一个list中

path = os.getcwd()
##os.chdir(path) 若想改变工作路径可以用chdir函数
filelist = []
for root,dirs,files in os.walk(path):
    for file in files:
        if os.path.splitext(file)[1] == '.xlsx':
            filelist.append(file)
            
filelist

['201708.xlsx',
 '200908.xlsx',
 '201509.xlsx',
 '201704.xlsx',
 '201211.xlsx',
 '201303.xlsx',
 '200904.xlsx',
 '200912.xlsx',
 '201712.xlsx',
 '201207.xlsx',
 '201010.xlsx',
 '201505.xlsx',
 '201102.xlsx',
 '201801.xlsx',
 '201401.xlsx',
 '201006.xlsx',
 '201007.xlsx',
 '201512.xlsx',
 '201103.xlsx',
 '201011.xlsx',
 '201504.xlsx',
 '201206.xlsx',
 '201601.xlsx',
 '201302.xlsx',
 '200905.xlsx',
 '201705.xlsx',
 '201210.xlsx',
 '201508.xlsx',
 '200909.xlsx',
 '201709.xlsx',
 '201702.xlsx',
 '201305.xlsx',
 '200902.xlsx',
 '201610.xlsx',
 '201606.xlsx',
 '201201.xlsx',
 '201503.xlsx',
 '201411.xlsx',
 '201807.xlsx',
 '201104.xlsx',
 '201407.xlsx',
 '201112.xlsx',
 '201811.xlsx',
 '201309.xlsx',
 '201108.xlsx',
 '201109.xlsx',
 '201308.xlsx',
 '201001.xlsx',
 '201902.xlsx',
 '201406.xlsx',
 '201810.xlsx',
 '201410.xlsx',
 '201806.xlsx',
 '201105.xlsx',
 '201502.xlsx',
 '201312.xlsx',
 '201607.xlsx',
 '201304.xlsx',
 '200903.xlsx',
 '201611.xlsx',
 '201703.xlsx',
 '201901.xlsx',
 '201002.xlsx',
 '201110.xlsx',
 '201405.xlsx',
 '201106.xlsx',
 '201805.xlsx',
 '201501.xlsx',
 '201203.xlsx',
 '201604.xlsx',
 '201311.xlsx',
 '201612.xlsx',
 '201307.xlsx',
 '201409.xlsx',
 '201809.xlsx',
 '201608.xlsx',
 '201609.xlsx',
 '201808.xlsx',
 '201408.xlsx',
 '201701.xlsx',
 '200901.xlsx',
 '201306.xlsx',
 '201605.xlsx',
 '201310.xlsx',
 '201202.xlsx',
 '201107.xlsx',
 '201804.xlsx',
 '201412.xlsx',
 '201812.xlsx',
 '201111.xlsx',
 '201404.xlsx',
 '201003.xlsx',
 '201008.xlsx',
 '201209.xlsx',
 '201511.xlsx',
 '201004.xlsx',
 '201403.xlsx',
 '201803.xlsx',
 '201507.xlsx',
 '201012.xlsx',
 '201205.xlsx',
 '201710.xlsx',
 '200910.xlsx',
 '201602.xlsx',
 '200906.xlsx',
 '201301.xlsx',
 '201706.xlsx',
 '201212.xlsx',
 '201707.xlsx',
 '200907.xlsx',
 '200911.xlsx',
 '201603.xlsx',
 '201204.xlsx',
 '201711.xlsx',
 '201506.xlsx',
 '201802.xlsx',
 '201101.xlsx',
 '201402.xlsx',
 '201510.xlsx',
 '201005.xlsx',
 '201208.xlsx',
 '201009.xlsx']

Step 4 : 读取Excel文件内容，并把每个表格都存成DataFrame结构