엑셀·CSV 여러 파일 합치고 정리하기 — 파이썬 판다스 입문

5장에서는 파일의 이름을 바꿨습니다. 이번에는 파일 안으로 들어가 내용을 합칩니다. 지점마다 형식이 조금씩 다른 매출 파일을 받아서 하나의 표로 모으는 일인데, 손으로 하면 복사·붙여넣기 몇 번으로 끝날 것 같다가 열 개가 넘어가는 순간 실수가 생기는 작업입니다.

판다스는 엑셀 표를 코드로 다루는 도구입니다

판다스(pandas)는 파이썬에서 표 형태의 데이터를 읽고 합치고 요약하는 라이브러리(남이 만들어 둔 기능 묶음)입니다. 표 하나는 DataFrame(데이터프레임, 행과 열이 있는 표 객체)이라고 부릅니다. 엑셀 시트 한 장이 DataFrame 하나라고 생각하면 됩니다.

설치와 버전

터미널(윈도우는 명령 프롬프트)에서 한 줄이면 설치됩니다.

pip install pandas openpyxl

openpyxl은 판다스가 .xlsx 파일을 읽고 쓸 때 쓰는 보조 도구입니다. 판다스만 설치하고 이걸 빼먹는 경우가 많은데, 아래 오류 수정 대화에서 바로 그 상황을 다룹니다. 윈도우에서 pip가 인식되지 않으면 py -m pip install pandas openpyxl로 바꿔 입력하세요.

판다스 최신 버전은 3.0.6(2026-09-17 릴리스)이고, 3.0.0은 2026-01-21에 나왔습니다. 3.x는 Python 3.11 이상이 필요합니다(PyPI, pandas 공식 릴리스 노트).

이 장의 코드는 read_excel, read_csv, concat, pivot_table 같은 기본 기능만 써서 2.x와 3.x 어느 쪽에서도 같게 동작하도록 짠 것입니다. 파이썬이 3.10 이하라면 최신 판다스가 설치되지 않을 수 있으니, 파이썬을 올리거나 아래의 Colab 방법을 쓰세요.

합치기까지의 세 동작

판다스 공식 문서는 반복문 안에서 concat을 하나씩 호출하지 말고, 리스트에 모았다가 마지막에 한 번 합치라고 권합니다. 아래 코드가 정확히 그 방식입니다.

지점별 매출 파일 합치기 실습

상황은 이렇습니다. 지점별_매출 폴더에 파일이 세 개 있습니다. 파일 형식과 저장 방식이 제각각입니다.

모든 파일에는 월과 매출액 열이 있습니다. 실제 업무 파일에서 이 정도 불일치는 흔합니다.

AI에게 보낼 요청 프롬프트

파이썬 판다스로 폴더 안의 엑셀(xlsx)과 CSV를 하나로 합치는 코드를 써 줘.

• 폴더: 코드와 같은 위치의 "지점별_매출" 폴더
• 각 파일에는 "월", "매출액" 열이 있어
• 합칠 때 "지점" 열을 새로 만들어 파일 이름(확장자 제외)을 넣어 줘
• 합친 전체 표 시트와, 지점(행)×월(열)로 매출액 합계를 낸 요약 시트를 "매출_통합.xlsx" 한 파일에 저장해 줘
• 요약표에서 빈칸은 0으로
• 초보자가 읽을 수 있게 주석을 달아 줘

완성된 코드

처음 받은 코드는 아래 오류를 몇 번 거친 뒤 이 형태가 됩니다. 오류 수정 전에 최종본을 먼저 봅니다.

from pathlib import Path
import pandas as pd

FOLDER = Path("지점별_매출")          # 엑셀·CSV가 들어 있는 폴더
frames = []

for path in sorted(FOLDER.iterdir()):
    if path.suffix == ".xlsx":
        df = pd.read_excel(path)
    elif path.suffix == ".csv":
        try:
            df = pd.read_csv(path, encoding="utf-8")
        except UnicodeDecodeError:
            df = pd.read_csv(path, encoding="cp949")   # 한국 엑셀이 저장한 CSV
    else:
        continue
    df.columns = df.columns.str.strip()                # '매출액 ' 같은 공백 제거
    df["지점"] = path.stem
    frames.append(df)

total = pd.concat(frames, ignore_index=True)
summary = total.pivot_table(index="지점", columns="월", values="매출액",
                            aggfunc="sum", fill_value=0)

with pd.ExcelWriter("매출_통합.xlsx") as writer:
    total.to_excel(writer, sheet_name="전체", index=False)
    summary.to_excel(writer, sheet_name="요약")
print(f"{len(frames)}개 파일, {len(total)}행 합침 → 매출_통합.xlsx")

aggfunc="sum"을 빼면 판다스는 합계가 아니라 평균을 계산합니다. 기본값이 평균이라서 초보자가 숫자가 이상하다고 당황하는 지점입니다. index=False는 엑셀에 0, 1, 2 같은 행 번호가 첫 열로 따라 들어가는 것을 막습니다.

실행 결과

위 세 파일로 돌린 결과입니다.

3개 파일, 5행 합침 → 매출_통합.xlsx

같은 폴더에 생긴 매출_통합.xlsx의 요약 시트는 이렇게 나옵니다.

 지점  10월  9월
강남점 200 100
대전점   0  30
부산점  70  50

대전점의 10월이 0인 것은 파일에 10월 행이 없어서 fill_value=0이 채운 값입니다. 열 순서가 10월, 9월로 나온 점도 눈여겨보세요. 월 열이 숫자가 아니라 "9월", "10월" 같은 글자라서 문자 순서로 정렬된 결과입니다. 순서가 신경 쓰이면 AI에게 "월 열을 숫자로 바꿔서 정렬해 줘"라고 추가 요청하면 됩니다.

오류 수정 대화 세 번: 막히는 곳과 고치는 법

대화 1: ModuleNotFoundError

코드를 처음 돌리면 가장 먼저 만나는 오류입니다. 판다스만 설치한 상태에서 .xlsx를 읽으려 하면 나옵니다.

오류 메시지를 그대로 붙여 다시 묻기

코드를 실행했더니 아래 오류가 나. 어떻게 해결해?

ModuleNotFoundError: No module named 'openpyxl'

원인과 해결

엑셀 파일을 읽어 주는 보조 도구 openpyxl이 설치되지 않았다는 뜻입니다. 터미널에서 pip install openpyxl을 실행하면 해결됩니다. 판다스 버전에 따라 메시지가 ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl. 형태로 나오기도 하는데(pandas GitHub 이슈 #41715), 처방은 같습니다.

설치했는데도 같은 오류가 나면 pip가 설치한 파이썬과 편집기(VS Code 등)가 실행하는 파이썬이 서로 다른 경우가 흔합니다. 이때는 python -m pip install openpyxl처럼 python -m을 붙여 설치하세요.

오류 수정 대화 2: UnicodeDecodeError

CSV를 읽는 순간 한글이 들어 있는 파일에서 나는 오류입니다. 실무에서 가장 자주 막히는 지점입니다.

오류 메시지를 그대로 붙여 다시 묻기

부산점.csv를 읽는 줄에서 아래 오류가 나. 파일은 한국 엑셀에서 CSV로 저장한 거야. 고쳐 줘.

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xbf in position 0: invalid start byte

원인과 해결

read_csv는 파일을 utf-8로 읽는 것이 기본값입니다(pandas 공식 문서). 그런데 한국어 윈도우의 엑셀이 만든 CSV는 cp949 같은 다른 방식으로 저장된 경우가 많습니다. utf-8로 읽다가 해석할 수 없는 바이트를 만나면 이 오류가 납니다. 바이트 값(0xbf)과 위치(position 0)는 파일마다 다르게 나오니, 숫자가 달라도 같은 문제로 보면 됩니다.

고치는 방법은 encoding="cp949"를 지정하는 것입니다.

df = pd.read_csv("부산점.csv", encoding="cp949")

최종 코드에서는 파일마다 인코딩이 다를 수 있으니, 먼저 utf-8로 읽어 보고 UnicodeDecodeError가 나면 cp949로 다시 읽는 try/except를 썼습니다. 이 방식이면 대전점.csv(utf-8)와 부산점.csv(cp949)가 한 번에 처리됩니다. euc-kr을 쓰라는 글도 있지만, 한글 범위가 더 넓은 cp949가 안전합니다.

반대 상황: 엑셀에서 CSV가 깨져 보일 때

반대로 판다스로 저장한 CSV를 엑셀에서 열었더니 한글이 깨지는 일도 있습니다. 엑셀은 파일 맨 앞의 BOM(utf-8임을 알리는 표식)이 있어야 utf-8을 알아봅니다. 저장할 때 아래처럼 utf-8-sig를 지정하세요.

total.to_csv("합계.csv", index=False, encoding="utf-8-sig")

오류 수정 대화 3: KeyError

세 번째 오류는 파일 하나가 코드를 통째로 멈추게 만드는 경우입니다. 이번 사례의 주인공은 눈에 보이지 않는 공백입니다.

오류 메시지를 그대로 붙여 다시 묻기

강남점.xlsx를 합치다가 아래 오류가 나. 엑셀에서 보면 "매출액" 열이 분명히 있는데 왜 못 찾아?

KeyError: '매출액'

원인과 해결

엑셀에서 열 제목 매출액 뒤에 공백이 하나 붙어 있었습니다. 판다스에게 매출액 과 매출액은 다른 이름이라서, 피벗을 만드는 줄에서 해당 열이 없다는 KeyError가 납니다. 눈으로는 절대 보이지 않는 문제라서 오류의 원인을 찾기 어렵습니다.

의심될 때는 열 이름 목록을 직접 찍어 보세요.

print(df.columns.tolist())

['월', '매출액 ']처럼 따옴표 안에서 공백이 드러납니다. 해결은 열 이름 앞뒤 공백을 지우는 한 줄입니다.

df.columns = df.columns.str.strip()

최종 코드에서 이 줄이 파일을 읽은 직후, concat 이전에 있는 이유가 있습니다. 파일마다 열 이름 표기가 다르면 합칠 때 빈칸(NaN)이 생기기 때문에, 합치기 전에 열 이름부터 통일해야 합니다. 열 이름이 공백이 아니라 아예 다르다면(예: 매출과 매출액) df.rename(columns={"매출": "매출액"})로 맞추라고 AI에게 요청하세요.

규모가 커질 때와 다른 실행 환경

판다스는 엑셀보다 훨씬 큰 표도 다루지만, 결과를 엑셀로 저장할 때는 엑셀의 한계가 적용됩니다.

알아 둘 한도

엑셀 한 시트는 최대 1,048,576행, 16,384열까지이며 셀 하나에는 32,767자까지 넣을 수 있습니다(Microsoft 엑셀 사양 문서). 구글 시트는 문서당 2,000만 셀 또는 100MB가 한도입니다(Google Drive 도움말).

행이 100만을 넘는 파일은 엑셀로 열기 전에 판다스로 먼저 줄여야 합니다. 구글 시트 한도는 과거 자료에 1천만 셀로 적힌 곳도 있으니, 정확한 값은 공식 도움말에서 확인하세요.

파이썬 설치 없이 Colab에서 돌리기

회사 PC에 파이썬을 깔 수 없다면 Google Colab을 쓰세요. 브라우저에서 파이썬을 실행하는 서비스입니다. 왼쪽 파일 아이콘으로 열리는 창에 지점별_매출 폴더를 끌어다 올리고, 위 코드를 셀에 붙여 실행하면 됩니다. 결과 매출_통합.xlsx는 같은 파일 창에서 내려받습니다. 업로드한 파일은 세션이 끝나면 사라질 수 있으니 결과물은 바로 저장해 두세요.

합치기보다 엑셀 함수가 나은 경우

파일 몇 개를 한 번만 합치고 끝이라면 엑셀의 함수와 피벗만으로 충분합니다. 함수로 처리할 일은 AI 엑셀·스프레드시트 마스터 시리즈에서 다룹니다. 이 장의 방법은 파일이 매달 새로 오고, 열 개 넘는 파일을 반복해서 합칠 때 빛을 냅니다.

정리

판다스로 합치기는 읽고, 열 이름을 정리하고, concat으로 잇고, pivot_table로 요약하는 네 걸음입니다. 오류는 대부분 세 가지입니다. openpyxl 미설치는 pip install, 한글 CSV 인코딩은 encoding="cp949", 열 이름 공백은 df.columns.str.strip()으로 풉니다.

다음 챕터에서는 '정기 보고서 자동 생성 — 매주 요약해 PDF·메일로 보내기'를 다루겠습니다. 앱스 스크립트로 시트를 요약해 PDF로 만들고, 시간 기반 트리거로 매주 자동 발송하는 방법을 익힙니다.