소개
세상은 Excel이 지배한다고 해도 과언이 아닙니다. 데이터 엔지니어링 업무를 하다 보면 동료들이 중요한 의사결정 도구로 Excel을 얼마나 많이 활용하는지 놀라게 되는 경우가 많습니다. MS Office와 Excel 스프레드시트의 열렬한 팬은 아니지만, 대용량 Excel 파일을 효과적으로 다루는 실용적인 방법을 소개해 드리겠습니다.
일반적으로 대용량 CSV 파일을 처리할 때는 Pandas의 chunksize 옵션을 사용해 청크 단위로 나누어 처리할 수 있습니다. 하지만 안타깝게도 Excel 스프레드시트의 경우 Pandas가 기본적으로 청크 처리 옵션을 제공하지 않습니다.
따라서 이 글에서는 Excel 파일을 직접 청크 단위로 읽고 처리할 수 있는 방법을 단계별로 살펴보겠습니다.
사전 준비 사항
본격적으로 코드를 작성하기 전에 Pandas로 Excel 파일을 다루기 위한 기본 사항부터 확인하겠습니다.
1. 라이브러리 설치
먼저 openpyxl과 xlsxwriter 라이브러리를 설치합니다. 설치 여부가 확실하지 않다면 Python 터미널에서 pip freeze 또는 pip list 명령으로 설치된 패키지 목록을 확인할 수 있습니다.
pip install openpyxl xlsxwriter
이번 예제에서 진행할 전체 흐름은 다음과 같습니다.
- 데이터를 담은 튜플을 사용해 Excel 스프레드시트를 생성합니다.
- 생성된 Excel 파일을 Pandas DataFrame으로 불러옵니다.
- DataFrame의 데이터를 새로운 워크북(Sheet2)에 기록합니다.
구현 방법
2. Excel 스프레드시트 생성하기
먼저 딕셔너리 형태의 데이터를 Excel 스프레드시트에 기록하는 함수를 만들겠습니다. 각 코드 단계마다 로직에 대한 설명이 포함되어 있습니다.
import xlsxwriter
import pandas as pd
# Function : write_data_to_files
def write_data_to_files(inp_data, inp_file_name):
"""
function : 전달받은 데이터를 Excel 파일로 생성
args : inp_data : 대상 파일에 기록할 튜플 데이터
inp_file_name : 데이터를 저장할 대상 파일명
return : none
assumption : 생성될 파일과 이 코드가 같은 디렉터리에 있어야 함.
"""
print(f" *** Writing the data to - {inp_file_name}")
# 워크북 생성
workbook = xlsxwriter.Workbook(inp_file_name)
# 워크시트 추가
worksheet = workbook.add_worksheet()
# 첫 번째 셀부터 시작. 행과 열 인덱스는 0부터 시작함.
row = 0
col = 0
# 입력 데이터를 읽어 행과 열에 기록
for player, titles in inp_data:
worksheet.write(row, col, player)
worksheet.write(row, col + 1, titles)
row += 1
# 워크북 닫기
workbook.close()
print(f" *** Completed writing the data to - {inp_file_name}")
3. Pandas로 Excel 파일 다루기
다음은 Pandas를 활용해 Excel 파일에 적용할 수 있는 주요 기능들을 빠르게 살펴보는 함수입니다.
# Function : excel_functions_with_pandas
def excel_functions_with_pandas(inp_file_name):
"""
function : pandas로 excel에 적용 가능한 함수 개요
args : inp_file_name : 입력 excel 스프레드시트
return : none
assumption : 입력 excel 스프레드시트와 이 코드가 같은 디렉터리에 있어야 함.
"""
data = pd.read_excel(inp_file_name)
# 상위 2개 행 출력
print(f" *** Displaying top 2 rows of - {inp_file_name} \n {data.head()} ")
# 데이터 타입 확인
print(f" *** Displaying info about {inp_file_name} - {data.info()}")
# 새 시트 "Sheet2"를 생성하고 데이터 기록
new_players_info = pd.DataFrame(data=[
{"players": "new Roger Federer", "titles": 20},
{"players": "new Rafael Nadal", "titles": 20},
{"players": "new Novak Djokovic", "titles": 17},
{"players": "new Andy Murray", "titles": 3}], columns=["players", "titles"])
new_data = pd.ExcelWriter(inp_file_name)
new_players_info.to_excel(new_data, sheet_name="Sheet2")
if __name__ == '__main__':
# 파일명과 데이터 정의
file_name = "temporary_file.xlsx"
# 저장할 튜플 데이터
file_data = (['player', 'titles'], ['Federer', 20], ['Nadal', 20],
['Djokovic', 17], ['Murray', 3])
# file_data를 file_name에 기록
write_data_to_files(file_data, file_name)
# excel 파일을 pandas로 읽어 함수 적용
excel_functions_with_pandas(file_name)
실행 결과
*** Writing the data to - temporary_file.xlsx *** Completed writing the data to - temporary_file.xlsx *** Displaying top 2 rows of - temporary_file.xlsx player titles 0 Federer 20 1 Nadal 20 2 Djokovic 17 3 Murray 3 <class 'pandas.core.frame.DataFrame'> RangeIndex: 4 entries, 0 to 3 Data columns (total 2 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 player 4 non-null object 1 titles 4 non-null int64 dtypes: int64(1), object(1) memory usage: 192.0+ bytes *** Displaying info about temporary_file.xlsx - None
대용량 Excel 파일 청크 단위로 처리하기
앞서 언급했듯이 CSV 파일은 chunksize 옵션으로 손쉽게 청크 처리할 수 있지만, Excel 스프레드시트에는 이러한 옵션이 기본 제공되지 않습니다. 아래 프로그램은 Excel 파일을 지정한 크기만큼 나누어 순차적으로 읽어오는 방식으로 이 문제를 해결합니다.
핵심 원리
nrows매개변수로 한 번에 읽을 행 수를 지정합니다.skiprows를 활용해 이미 읽은 행은 건너뛰며 반복적으로 데이터를 읽습니다.- 더 이상 읽을 데이터가 없으면(
df_chunk.shape[0]이 0이면) 루프를 종료합니다. - 읽어온 모든 청크를
pd.concat()으로 하나의 DataFrame으로 합칩니다. - 첫 번째 행은 헤더이므로 별도로 읽어 최종 결과와 결합합니다.
예제 코드
def global_excel_to_db_chunks(file_name, nrows):
"""
function : excel 스프레드시트를 청크 단위로 처리
args : file_name : 입력 excel 스프레드시트
nrows : 한 번에 읽을 행 수
return : none
assumption : 입력 excel 스프레드시트와 이 코드가 같은 디렉터리에 있어야 함.
"""
chunks = []
i_chunk = 0
# 첫 번째 행은 헤더이므로 이미 읽었기 때문에 건너뜀.
skiprows = 1
df_header = pd.read_excel(file_name, nrows=1)
while True:
df_chunk = pd.read_excel(
file_name, nrows=nrows, skiprows=skiprows, header=None)
skiprows += nrows
# 더 이상 데이터가 없으면 루프 종료
if not df_chunk.shape[0]:
break
else:
print(
f" ** Reading chunk number {i_chunk} with {df_chunk.shape[0]} Rows")
chunks.append(df_chunk)
i_chunk += 1
df_chunks = pd.concat(chunks)
# 헤더와 청크들을 연결하기 위해 열 이름 변경
columns = {i: col for i, col in enumerate(df_header.columns.tolist())}
df_chunks.rename(columns=columns, inplace=True)
df = pd.concat([df_header, df_chunks])
print(f' *** Reading is Completed in chunks...')
if __name__ == '__main__':
print(f" *** Gathering & Displaying Stats on the excel spreadsheet ***")
file_name = 'Sample-sales-data-excel.xls'
stats = pd.read_excel(file_name)
print(f" ** Total rows in the spreadsheet are - {len(stats.index)} Rows")
# 한 번에 1000행씩 청크 단위로 excel 파일 처리
global_excel_to_db_chunks(file_name, 1000)
실행 결과
*** Gathering & Displaying Stats on the excel spreadsheet *** ** Total rows in the spreadsheet are - 9994 Rows ** Reading chunk number 0 with 1000 Rows ** Reading chunk number 1 with 1000 Rows ** Reading chunk number 2 with 1000 Rows ** Reading chunk number 3 with 1000 Rows ** Reading chunk number 4 with 1000 Rows ** Reading chunk number 5 with 1000 Rows ** Reading chunk number 6 with 1000 Rows ** Reading chunk number 7 with 1000 Rows ** Reading chunk number 8 with 1000 Rows ** Reading chunk number 9 with 994 Rows *** Reading is Completed in chunks...
마치며
Pandas는 Excel 파일에 대해 기본적으로 청크 처리 옵션을 제공하지 않지만, nrows와 skiprows 파라미터를 조합하면 위 예제처럼 대용량 Excel 파일도 메모리 부담 없이 청크 단위로 효율적으로 처리할 수 있습니다. 이 방식은 수만~수십만 행 규모의 판매 데이터, 로그 데이터 등을 다룰 때 특히 유용하며, 각 청크를 읽는 시점에 필터링·집계·DB 저장 등의 작업을 함께 수행하면 성능을 더욱 향상시킬 수 있습니다.