집에서하자 _ 등록 차량 데이터 정제 2

#python 3.0
import pandas as pd
import openpyxl
from datetime import datetime

#파일명 
filename = 'D:/data/자동차_등록_현황/1차가공/202101.xlsx'

#파일명 변수화 
directory = 'D:/data/자동차_등록_현황/1차가공/'

all_data = pd.DataFrame(columns =['year','month','type','city','전기','총계'])

#

 

#연도별 파일 호출. 
for year in range(2021,2023) :
    for month in range(1,13):
        m = format(month,'02')
        #print(directory + str(year) +'년_'+ m + postfile)
        filename = directory + str(year)+ m +'.xlsx'
        total = set_df_2(year,month,filename) 
        all_data = all_data.append(total, ignore_index = True)

#

 

def set_df_2(year,month,filename):
    df = pd.read_excel(filename,
                               engine = 'openpyxl',
                               header = 1)
    removeidx = df[df['연료별']=='시도별'].index
    df.drop(removeidx,inplace=True)
    df.rename(columns={'연료별':'city'},inplace=True)
    newdf = df[['city','전기','총계']]
    newdf['type'] = '승용'
    newdf

    newdf1 = df[['city','전기.1','총계.1']]
    newdf1['type'] = '승합'
    newdf1.rename(columns={'전기.1':'전기'},inplace=True)
    newdf1.rename(columns={'총계.1':'총계'},inplace=True)
    newdf1

    newdf2 = df[['city','전기.2','총계.2']]
    newdf2['type'] = '화물'
    newdf2.rename(columns={'전기.2':'전기'},inplace=True)
    newdf2.rename(columns={'총계.2':'총계'},inplace=True)
    newdf2

    newdf3 = df[['city','전기.3','총계.3']]
    newdf3['type'] = '특수'
    newdf3.rename(columns={'전기.3':'전기'},inplace=True)
    newdf3.rename(columns={'총계.3':'총계'},inplace=True)
    newdf3

    #최초 1번 
    total = newdf
    total = total.append(newdf1, ignore_index = True)
    total = total.append(newdf2, ignore_index = True)
    total = total.append(newdf3, ignore_index = True)
    
   
    
    total['year'] = year
    total['month'] = month
    
    return total 
    
    
  #  total.to_excel(excel_writer ='D:/data/자동차_등록_현황/2차가공/'+str(year)+month+'.xlsx')