1.excel數(shù)據(jù)合并、處理、導(dǎo)出、入庫 V1.0

#!/usr/bin/python
# coding: utf-8
import pandas as pd
import os
from sqlalchemy import create_engine
import sqlalchemy
import time

time_start=time.time()
#文件路徑
file_dir="D:/test/test數(shù)據(jù)/aa/"
#合并新表格名稱
new_filename=file_dir+'\\new_file.xlsx'

#返回文件路徑下所有表格的列表
file_list=os.listdir(file_dir)
new_list=[]

for file in file_list:
    file_path=os.path.join(file_dir,file)    #重構(gòu)文件路徑
    dataframe=pd.read_excel(file_path)       #將excel轉(zhuǎn)換成DataFrame
    new_list.append(dataframe)
df = pd.concat(new_list)                     #數(shù)據(jù)轉(zhuǎn)換

#數(shù)據(jù)清洗
df2=df
df2=df2.set_axis(df2.iloc[1],axis=1,inplace=False)  #設(shè)置第一行數(shù)據(jù)作為columns
#df=df.drop_duplicates()                              #去除重復(fù)行
df2=df2[~df2['業(yè)務(wù)時(shí)間'].isin(['業(yè)務(wù)時(shí)間'])]          #篩除指定行
df2.dropna(subset=['業(yè)務(wù)時(shí)間'],inplace=True)          #去除業(yè)務(wù)時(shí)間為NaN的行
df2=df2.reset_index(drop=True)

#導(dǎo)出excel表
#df2.to_excel(new_filename,index=False)       

#數(shù)據(jù)入庫
engine=create_engine('mysql+pymysql://root:root@localhost/test?charset=utf8')
df3=df2
#df3.to_sql(stored_card_change,)
df3.to_sql('stored_card_change', engine, index=False, if_exists='append')

#計(jì)時(shí)
time_end=time.time()
print('time cost',time_end-time_start,'s')