파이썬으로 폴더 안의 모든 엑셀 파일을 하나로 병합하는 방법

개요

여러 명이 각각 작성한 엑셀 파일이나 월별·부서별로 나뉜 매출 데이터를 하나의 파일로 합쳐야 하는 경우가 자주 있습니다. 이럴 때 파이썬의 glob 모듈로 폴더 안의 엑셀 파일 목록을 자동으로 가져오고, pandas의 read_excel(), concat(), append() 메서드를 조합하면 클릭 몇 번 없이도 손쉽게 병합할 수 있습니다.

예를 들어 바탕화면에 아래와 같이 두 개의 엑셀 파일이 있다고 가정해 보겠습니다.

Sales1.xlsx

Sales2.xlsx

참고: 사용 환경에 따라 엑셀 파일을 읽기 위해 openpyxl(.xlsx 전용) 또는 xlrd(.xls 전용) 패키지를 미리 설치해야 할 수 있습니다.

pip install openpyxl xlrd

1단계: 폴더 경로 지정 후 엑셀 파일 목록 가져오기

먼저 병합하고자 하는 엑셀 파일들이 모여 있는 폴더 경로를 변수에 저장합니다. 이후 glob.glob()에 *.xlsx 와일드카드 패턴을 넘겨 해당 폴더의 모든 엑셀 파일 이름을 한꺼번에 읽어옵니다.

path = "C:\\Users\\amit_\\Desktop\\"

filenames = glob.glob(path + "*.xlsx")
print('File names:', filenames)

2단계: 병합 결과를 담을 빈 데이터프레임 생성

여러 엑셀 파일의 데이터를 차곡차곡 쌓아갈 비어 있는 데이터프레임을 준비합니다. 이 데이터프레임이 최종적으로 병합 결과물이 됩니다.

outputxlsx = pd.DataFrame()

3단계: 반복문으로 파일을 읽고 병합하기

이제 실제 병합 작업입니다. for 반복문으로 앞에서 구한 파일 목록을 순회하면서 각 엑셀 파일을 read_excel()로 읽고, concat()으로 여러 시트를 하나로 묶은 뒤 append()로 결과 데이터프레임에 추가합니다. sheet_name=None을 지정하면 파일 안의 모든 시트까지 한 번에 읽어올 수 있다는 점이 포인트입니다.

for file in filenames:
    # read_excel()로 파일을 읽은 뒤 concat()으로 시트들을 결합
    df = pd.concat(pd.read_excel(file, sheet_name=None), ignore_index=True, sort=False)

    # 읽어온 데이터를 결과 데이터프레임에 추가
    outputxlsx = outputxlsx.append(df, ignore_index=True)

전체 예제 코드

지금까지의 과정을 하나로 합친 전체 코드는 다음과 같습니다.

import pandas as pd
import glob

# 바탕화면에 있는 병합 대상 엑셀 파일의 경로
path = "C:\\Users\\amit_\\Desktop\\"

# 확장자가 .xlsx인 모든 파일(엑셀 파일) 읽어오기
filenames = glob.glob(path + "*.xlsx")
print('File names:', filenames)

# 병합 결과를 담을 새 출력용 엑셀 파일을 위한 빈 데이터프레임
outputxlsx = pd.DataFrame()

# for 반복문으로 모든 엑셀 파일 순회
for file in filenames:
    # read_excel()로 파일을 읽은 뒤 concat()으로 시트 결합
    df = pd.concat(pd.read_excel(file, sheet_name=None), ignore_index=True, sort=False)

    # 읽어온 엑셀 데이터를 outputxlsx에 추가
    outputxlsx = outputxlsx.append(df, ignore_index=True)

print('병합된 엑셀 파일이 같은 위치에 생성되었습니다:')
outputxlsx.to_excel("C:/Users/amit_/Desktop/Output.xlsx", index=False)

실행 결과

코드를 실행하면 두 파일의 데이터가 모두 합쳐진 Output.xlsx 파일이 원본 파일들과 같은 폴더(바탕화면)에 자동으로 생성됩니다.

참고: 최신 pandas에서는 append() 대신 concat() 사용 권장

DataFrame.append()는 pandas 1.4부터 지원 중단(deprecated)되었고, pandas 2.0 이상에서는 완전히 제거되었습니다. 최신 버전의 pandas를 사용한다면 아래처럼 각 파일을 리스트에 담은 뒤 pd.concat()으로 한 번에 병합하는 방식을 사용하는 것이 좋습니다. 실행 속도 면에서도 훨씬 효율적입니다.

import pandas as pd
import glob

path = "C:\\Users\\amit_\\Desktop\\"
filenames = glob.glob(path + "*.xlsx")

# 각 파일을 데이터프레임으로 읽어 리스트에 저장
df_list = [pd.read_excel(file, sheet_name=None) for file in filenames]

# 리스트 안의 모든 데이터프레임을 한 번에 병합
merged_df = pd.concat([pd.concat(d.values()) for d in df_list], ignore_index=True)

merged_df.to_excel("C:/Users/amit_/Desktop/Output.xlsx", index=False)

이 방식을 활용하면 파일이 수십 개로 늘어나도 코드 수정 없이 동일하게 병합할 수 있으므로, 반복적인 엑셀 데이터 통합 업무를 자동화하는 데 매우 유용합니다.