엑셀 파일 합치기 [파이썬 openpyxl, pandas DataFrame]

엑셀 파일 합치기 연습 - 엑셀 필드(컬럼) 항목이 같은 여러 개의 파일을 하나의 엑셀 파일로 합치는 작업을 파이썬으로 만들어봤다. 3가지 방법으로 테스트를 해보니 pandas DataFrame으로 만든 코드가 가장 빨랐다.

 

 

엑셀 필드 항목이 같은 여러 개의 파일을 하나의 파일로 만들기

 

아래에 3가지 파이썬 코드가 있다.

1. 한 셀씩 추출하여 붙여넣기
2. 한 row(행) 단위로 추출하여 붙여 넣기
3. 판다스로 DataFrame(df) 만들고 여러 엑셀 파일의 데이터를 읽은 후 df를 다른 엑셀 파일에 저장하기

많은 row(행)를 가진 여러 엑셀 파일로 테스트를 해본 결과, 판다스로 구현한 코드가 가장 빨리 처리가 됐다. 

 

합칠 엑셀 파일

 

1. 한 셀씩 추출하여 붙여 넣기

import pathlib
import openpyxl

def file_merge():
    wb = openpyxl.Workbook()
    ws = wb['Sheet']  # wb.active
    xl_filename = 'File_Merge_test' + '.xlsx'
    theme = ['단지 코드', '법정동 코드', '단지명', '사용 승인일', '동수', '호수', '세대수', '도로명 주소']
    ws.append(theme)
    wb.save('./' + xl_filename)

    path = pathlib.Path(r'D:\pythonDev\test')
    # print(path)
    files = path.glob('*')
    # print(files)  # <generator object Path.glob at 0x000001B634162BA0>
    rows = 2  # 붙여넣을 엑셀의 row count

    for file in list(files):
        print(file)  # 폴더\파일명 형태
        if file.is_file():
            # print(file.name)
            read_wb = openpyxl.load_workbook(file, data_only=True)
            read_ws = read_wb[read_wb.sheetnames[0]]  # sheetnames List

            for row_idx in range(1, read_ws.max_row):
                col_cnt = 1
                for col in read_ws.iter_cols(min_col=1, max_col=8):  # col= A, B, C,...
                    print(col[row_idx].value, end=', ')
                    ws.cell(rows, col_cnt).value = col[row_idx].value
                    col_cnt += 1
                print(f'\nrows: {rows}')
                rows += 1
            wb.save('./' + xl_filename)
            read_wb.close()
        # break

    wb.close()

file_merge()

• 합칠 엑셀 파일을 생성하고, 컬럼명을 append
• 파일 리스트를 불러오는 generator object인 path.glob('*')를 리스트로 변환하고 각각의 엑셀 파일을 load 후 iter_cols()로 읽어서 합칠 엑셀 파일의 셀에 대입함

 

 

[터미널 실행 결과]

D:\pythonDev\test\강원.xlsx
A21010109, 4215011000, 부영2차아파트, 1999-09-09, 5, 917, 917, 강원도 강릉시 가작로 71,
rows: 2
A21010002, 4215011000, 교동1주공아파트, 1999-12-01, 14, 1019, 1019, 강원도 강릉시 가작로 78,       
rows: 3
A21000301, 4215011000, 교동2주공아파트, 2002-05-13, 8, 688, 688, 강원도 강릉시 가작로 85,
rows: 4
A21011201, 4215011100, 대인4차아파트, 1995-06-30, 1, 174, 174, 강원도 강릉시 강릉대로469번길 14-1, 
rows: 5
A21092705, 4215011600, 내곡한라, 1997-11-10, 11, 724, 724, 강원도 강릉시 강변로 114,
rows: 6
A21093205, 4215012100, 신화아파트, 1998-03-18, 7, 493, 493, 강원도 강릉시 강변로 270,
rows: 7
A21070808, 4215012100, 양우내안애아파트, 2009-09-01, 10, 456, 456, 강원도 강릉시 강변로 328-8,     
rows: 8
D:\pythonDev\test\경기.xlsx
A48208006, 4163010700, 양주고읍휴먼시아5단지, 2010-04-23, 7, 531, 531, 경기도 양주시 고읍북로 15, 
rows: 9
A48208002, 4163010700, 은빛마을유승한내들, 2009-10-13, 6, 412, 412, 경기도 양주시 고읍북로 28-8,  
rows: 10
A48208005, 4163010700, 고읍휴먼시아7단지, 2010-04-20, 8, 773, 773, 경기도 양주시 고읍북로 78,     
rows: 11
A48284102, 4163033022, 희망아파트, 1996-12-21, 4, 436, 436, 경기도 양주시 광적로 85-18,
rows: 12
A48283514, 4163025022, 백석한승아파트, 2001-08-25, 6, 499, 499, 경기도 양주시 권율로 1455-15,     
rows: 13
A48281202, 4163034025, 양주푸른옥마을, 2001-12-22, 7, 262, 262, 경기도 양주시 권율로 51,
rows: 14
D:\pythonDev\test\경남.xlsx
A65691804, 4831010800, 일성한빛타운, 1996-04-19, 1, 182, 182, 경상남도 거제시 장평3로3길 41,
rows: 15
A65691801, 4831010800, 제니스타운, 2000-05-16, 5, 606, 606, 경상남도 거제시 장평3로3길 7,
rows: 16
A65691808, 4831010800, 대한2차아파트, 1994-12-19, 5, 411, 411, 경상남도 거제시 장평로6길 20,
rows: 17
A65691810, 4831010800, 대한1차아파트, 1993-05-17, 4, 312, 312, 경상남도 거제시 장평로6길 23,
rows: 18
A65679003, 4831010800, 성원아파트, 1995-10-27, 8, 795, 795, 경상남도 거제시 장평로6길 30,
rows: 19
A65691807, 4831010800, 광우무지개맨션, 1994-03-29, 3, 312, 312, 경상남도 거제시 장평로6길 7,
rows: 20
A65680603, 4831011300, 삼성쉐르빌, 2007-03-07, 5, 420, 420, 경상남도 거제시 제산로 2-5,
rows: 21
A65691401, 4831011300, 거제수월힐스테이트, 2012-04-19, 8, 715, 715, 경상남도 거제시 제산로 51,        
rows: 22
A65680604, 4831011300, 거제더샵아파트, 2009-03-11, 5, 473, 473, 경상남도 거제시 제산로 86,
rows: 23
A65681201, 4831037024, 신우희가로, 2006-09-01, 1, 210, 210, 경상남도 거제시 죽토로 2,
rows: 24
A65672004, 4831010900, 거제2차덕산베스트타운, 2002-04-11, 17, 1566, 1566, 경상남도 거제시 중곡2로 89, 
rows: 25
D:\pythonDev\test\부산.xlsx
A61782901, 2653010800, 엄궁한신1차아파트, 1998-06-02, 2, 200, 200, 부산광역시 사상구 낙동대로 712-17, 
rows: 26
A61776003, 2653010400, 괘법2차한신아파트, 1998-07-13, 9, 1094, 1094, 부산광역시 사상구 낙동대로1210번길 33,
rows: 27
A61780802, 2653010400, 사상강변동원아파트, 1999-12-03, 6, 600, 600, 부산광역시 사상구 낙동대로1210번길 34,
rows: 28
A61775901, 2653010400, 괘법한신아파트, 1994-12-14, 4, 434, 434, 부산광역시 사상구 낙동대로1210번길 53,
rows: 29
A61770101, 2653010600, 주례경동윈츠빌아파트, 2004-09-14, 1, 312, 312, 부산광역시 사상구 냉정로 120,
rows: 30

 

 

2. 한 row(행) 단위로 추출하여 붙여 넣기

import pathlib
import openpyxl

def file_merge():
    wb = openpyxl.Workbook()
    ws = wb['Sheet']  # wb.active
    xl_filename = 'File_Merge_test' + '.xlsx'
    theme = ['단지 코드', '법정동 코드', '단지명', '사용 승인일', '동수', '호수', '세대수', '도로명 주소']
    ws.append(theme)
    wb.save('./' + xl_filename)

    path = pathlib.Path(r'D:\pythonDev\test')
    # print(path)
    files = path.glob('*')
    rows = 2  # 붙여넣sms 엑셀의 row count

    for file in list(files):
        print(file)  # 폴더\파일명 형태
        if file.is_file():
            # print(file.name)
            read_wb = openpyxl.load_workbook(file, data_only=True)
            read_ws = read_wb[read_wb.sheetnames[0]]  # sheetnames List

            # print(read_ws[2:read_ws.max_row])

            for rs in read_ws[2:read_ws.max_row]:
                # print(read_ws.max_row, read_ws.max_column)
                col_cnt = 1
                for cell in rs:
                    print(cell.value, end=', ')
                    ws.cell(rows, col_cnt).value = cell.value
                    col_cnt += 1                    
                rows += 1
                print(f'\n복사붙여넣기 rows: {rows}\n')
            wb.save('./' + xl_filename)
            read_wb.close()
        # break
        
    wb.close()

file_merge()

• 위 1.번과 동일하나 대상 엑셀 파일의 rows를 전체를 추출(read_ws[2:ws.max_row])하는 부분이 다름

 

 

[코드 실행으로 생성된 엑셀 파일]

 

 

 

3. 판다스(pandas) DataFrame 활용하기

import pandas as pd
import pathlib
import openpyxl
import os


def file_merge():
    xl_filename = 'File_Merge_test' + '.xlsx'
    path = pathlib.Path(r'D:\pythonDev\test')
    # print(path)
    files = path.glob('*')

    all_data = pd.DataFrame()

    for file in list(files):
        print(file)  # 폴더\파일명 형태
        if file.is_file():
            # print(file.name)
            df = pd.read_excel(file)
            print(f'{file} 파일 읽는중...')
            # all_data = all_data.append(df, ignore_index=True)
            all_data = pd.concat([all_data, df], ignore_index=True)
    
    print(all_data.shape)
    if all_data.shape[0] > 1048575:
        print(f'데이터 전체 열(rows)이 {all_data.shape[0]:,}개로 엑셀 Sheets 저장 범위를 초과했습니다.\n')
        return False
    else:
        if os.path.exists('./' + xl_filename):  # 파일 삭제
            os.remove('./' + xl_filename)
            print('기존 파일 삭제!!')

        if not os.path.exists('./' + xl_filename):  # 파일 생성 후 df 작업
            print(f'{xl_filename} 파일에 저장중...')
            with pd.ExcelWriter('./' + xl_filename, mode='w', engine='openpyxl') as writer:
                all_data.to_excel(writer, index=False, sheet_name='Sheet1', startcol=0, startrow=0)
            print('작업 완료!!')


file_merge()

• 판다스로 DataFrame을 하나(all_data)를 만들고 엑셀 파일의 데이터를 pd.read_excel()로 읽은 후 만든 all_data에 append 함. (FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead. 메시지를 확인하고 concat()으로 대체)
• 합칠 전체 엑셀 데이터의 크기를 계산하기 위해 all_data.shape 활용 (all_data.shape는 튜플인 (row, colume) 리턴)
• 기존 엑셀 파일이 있을 경우, 삭제하고 실행하도록 os.path.exists() 함수와 os.remove() 함수 사용함
• pd.ExcelWriter() 객체 생성 후 DataFrame의 to_excel() 함수를 사용하여 생성된 엑셀 파일에 데이터 추가

 

[터미널 실행 결과]

D:\pythonDev\test\강원.xlsx
D:\pythonDev\test\강원.xlsx 파일 읽는중...
D:\pythonDev\test\경기.xlsx
D:\pythonDev\test\경기.xlsx 파일 읽는중...
D:\pythonDev\test\경남.xlsx
D:\pythonDev\test\경남.xlsx 파일 읽는중...
D:\pythonDev\test\부산.xlsx
D:\pythonDev\test\부산.xlsx 파일 읽는중...
D:\pythonDev\test\해오라기
(29, 8)
File_Merge_test.xlsx 파일에 저장중...     
작업 완료!!

 

 

▷ 관련 포스팅 더보기

 

엑셀 파일 여러 개를 하나의 Excel 파일로 합치는 프로그램