CtrlEnter,1秒批量合并数据并导入数据库的办法,你学会了吗?
在日常数据分析的业务场景下,我们经常会遇到以下难题: 如何批量导入不同工作簿不同Sheet表下的数据? 如何将这些不同Sheet表下的数据合并为一个数据? 如何将合并的数据导入数据库?
本文构造一组数据,批量合并后导入数据库保存,下面一起来学习。 本文使用工具:Python 3.7.0
本文使用的模块:pandas、os、pymysql
适用范围:数据批量导入与合并,数据库留存批量数据导入并合并
首先构建10个工作簿,每个工作簿下面有三个Sheet表。
每一个分别Sheet表包含ID、number、sale三个字段。
导入相关的库,在数据批量导入及合并的时候,使用到pandas和os两个库。 #导入相关的库 import pandas as pd import os
这里介绍一下os.listdir命令,他可以将一个数据文件内的所有文件名都读取出来,如下图就是读取的文件名,包含那十个工作簿。 os.listdir(r"C:Users尚天强Desktop测试数据")
使用read_excel命令读取一个Excel文件。 df = pd.read_excel(r"C:Users尚天强Desktop测试数据测试数据1.xlsx") df.head()
首先构建一个空的列表,里面没有任何数值,同时,在开始部分就定义数据的导入路径、导出路径、以及保存的文件名。 import pandas as pd import os #构建一个空的列表 dfs = [] read_path=r"C:Users尚天强Desktop测试数据" save_path=r"C:Users尚天强Desktop" save_name="out_table.xlsx"
接下来是循环遍历每一个文件的名称,并且遍历的文件名不能为保存的文件名,sheet_name=None表示读取每一个Sheet表下的数据,skiprows=1,header=None取消标题,跳过第一行,只保留数据内容,使用extend命令将遍历的这些数据内容上下拼接起来。 for fname in os.listdir(read_path): if fname.endswith(".xlsx") and fname !=save_name: df = pd.read_excel(read_path+""+fname,skiprows=1,header=None,sheet_name=None) dfs.extend(df.values())
使用concat命令将每一个工作簿的数据拼接起来。 #合并 result = pd.concat(dfs)
在导出数据的时候,使用header命令给表格加一个标题,批量导入数据并合并的结果如下所示。 #导出数据 result.to_excel(save_path+""+save_name,index=False,header=["id","number","sale"])
使用第一种方法合并数据,发现有重复值,可以使用drop_duplicates参数去除重复值,使用sort_values参数进行排序,去除重复值并且排序的数据结果如下所示。 import pandas as pd import os dfs = [] read_path=r"C:Users尚天强Desktop测试数据" save_path=r"C:Users尚天强Desktop" save_name="out_table.xlsx" for fname in os.listdir(read_path): if fname.endswith(".xlsx") and fname !=save_name: df = pd.read_excel(read_path+""+fname,skiprows=1,header=None,sheet_name=None,names=["id","number","sale"]) #这里需要用names函数命名,不然没法去重和排序 dfs.extend(df.values()) #合并 result = pd.concat(dfs) #根据ID去除重复值 result.drop_duplicates(subset="id",keep="first",inplace=True) #降序排列 result.sort_values(by="sale",ascending=False,inplace=True) #导出数据 result.to_excel(save_path+""+save_name,index=False,header=["id","number","sale"])
数据导入数据库
将数据导入数据库,这里还是使用上面的数据文件,连接数据库使用的模块为pymysql。 #读入数据 data = pd.read_csv(r"C:Users尚天强Desktopout_table.csv",engine="python") data.head()#导入需要使用到的数据模块 import pandas as pd import pymysql
数据库连接,host为数据库地址、user为用户名、password为密码、db为数据库的名字、port为端口,默认为3306。 # 建立数据库连接 con = pymysql.connect(host="127.0.0.1", user="root", password="123456", db="demo", port=3306)
获取游标对象。 # 获取游标对象 cursor = con.cursor()
用数据库demo,"USE demo" 也是数据库内的SQL语言。 #使用数据库demo cursor.execute("USE demo")
构造一个test表,包含ID、number、sale三个字段。 cursor.execute("create table if not exists test ( ID char(4) primary key, number int, sale float)" )
向表test表内插入数据。 #插入数据语句 query = "insert into test(ID,number,sale)values (%s,%s,%s)"
迭代读取每行数据,转化数据类型,将其保存在values内。 #迭代读取每行数据,values中元素有个类型的强制转换,否则会出错 for r in range(0, len(data)): ID = data.iloc[r,0] number = data.iloc[r,1] sale = data.iloc[r,2] values = (str(ID),int(number),float(sale)) cursor.execute(query, values)
关闭游标,提交,关闭数据库连接。 #关闭游标,提交,关闭数据库连接,如果没有这些关闭操作,执行后在数据库中查看不到数据 cursor.close() con.commit() con.close()打印数据# 建立数据库连接 con = pymysql.connect(host="127.0.0.1", user="root", password="123456", db="demo", port=3306)# 获取游标对象 cursor = con.cursor()
首先执行SQL命令,"select * from test"表示查询test表内的所有数据,循环遍历数值,将它打印出来。 #查询数据库并打印内容 cursor.execute("select * from test") result = cursor.fetchall() for values in result: print(values)
打印出来的结果可以看到,第一列是字符串,第二列是整型,第三列是浮点型。 #关闭 cursor.close() con.commit() con.close()
导入数据库后的结果如下所示: