파이썬 pandas로 엑셀 데이터 자동 정리하기

작성자:

매주 월요일 아침 3시간짜리 엑셀 노가다, 코드 한 방에 끝냈다

월요일 출근 첫 업무가 두려웠다. 지난주 판매 데이터 파일 5개를 열고, 시트를 복사하고, 중복을 지우고, 합계 열을 만들고, 팀장에게 보낼 요약 시트까지 따로 만드는 작업이 매주 반복됐다. 빠르면 2시간, 파일이 좀 꼬이면 3시간도 훌쩍 넘겼다. “이걸 내가 손으로 하고 있어야 하나” 싶었다.

파이썬 pandas 라이브러리를 배우고 나서 그 작업이 10초로 줄었다. 엑셀 파일을 읽고, 정리하고, 다시 저장하는 전 과정을 코드가 대신한다. 설치부터 실전 자동화 완성 코드까지, 엑셀 반복 업무에 지친 분들을 위해 내가 쓰는 방식 그대로 공개한다.

1. pandas가 뭔지, 왜 엑셀 자동화에 쓰는지

pandas는 파이썬에서 표 형태 데이터를 다루는 표준 라이브러리다. 엑셀의 모든 기능 — 필터, 정렬, 피벗, 중복 제거, 합계 — 을 코드 한 줄로 처리한다. 엑셀과 다른 점은 단 하나. 마우스를 한 번도 안 쓴다는 것이다.

핵심 개념은 DataFrame이다. 엑셀 시트를 파이썬 안으로 불러들인 표라고 생각하면 된다. 파일을 읽는 순간 DataFrame이 되고, 작업이 끝나면 다시 엑셀 파일로 내보낸다. 그 사이에 일어나는 모든 정리 작업이 코드로 기록되기 때문에, 다음 주에 같은 작업을 하려면 실행 버튼 한 번만 누르면 된다.

2. 개발 환경 준비 및 패키지 설치

  • Python 버전: 3.10 이상
  • 편집기: VS Code (Jupyter 확장 설치 권장)
  • 핵심 패키지: pandas, openpyxl, glob (표준 라이브러리, 별도 설치 불필요)
pip install pandas openpyxl

openpyxl은 pandas가 .xlsx 파일을 읽고 쓸 때 내부적으로 사용하는 엔진이다. 이걸 빠뜨리면 ModuleNotFoundError가 뜨는데, 처음 환경을 셋업할 때 한 번은 꼭 만나는 에러다. 처음부터 같이 설치해두면 그 삽질을 건너뛸 수 있다.

3. 실전 자동화 코드: 다중 파일 병합부터 요약 시트 생성까지

1단계: 폴더 안 엑셀 파일 전체 자동 병합

같은 형식의 엑셀 파일이 여러 개 쌓여 있을 때, 하나하나 열어서 복사할 필요가 없다. glob으로 파일 목록을 긁어오고 반복문으로 전부 읽어 하나의 DataFrame으로 합친다.

import pandas as pd
import glob
import os

# 병합할 엑셀 파일이 있는 폴더 경로
folder_path = "./data/*.xlsx"
file_list = glob.glob(folder_path)

print(f"발견된 파일 수: {len(file_list)}개")

df_list = []
for file in file_list:
    df = pd.read_excel(file)
    df["출처파일"] = os.path.basename(file)  # 어느 파일에서 왔는지 추적
    df_list.append(df)

# 전체 파일을 하나의 DataFrame으로 합치기
merged_df = pd.concat(df_list, ignore_index=True)
print(f"병합 완료: 총 {len(merged_df)}행")
print(merged_df.head())

2단계: 중복 제거, 결측값 처리, 정렬

데이터를 합치고 나면 반드시 중복 행과 빈 값이 섞여 있다. 손으로 찾으면 몇 시간이지만, pandas는 세 줄이면 끝난다.

# 완전히 동일한 중복 행 제거
merged_df = merged_df.drop_duplicates()

# 핵심 컬럼(예: '주문번호')이 비어 있는 행 제거
merged_df = merged_df.dropna(subset=["주문번호"])

# 날짜 컬럼 기준 최신순 정렬
merged_df = merged_df.sort_values(by="주문일자", ascending=False)

print(f"정리 후 행 수: {len(merged_df)}")

3단계: 팀장 보고용 요약 시트와 원본 시트를 한 파일에 저장

정리된 전체 데이터와 카테고리별 합계 요약을 별도 시트로 나눠 하나의 엑셀 파일에 담는다. 매주 팀장에게 보내던 그 파일을 코드가 자동으로 만들어준다.

# 카테고리별 매출 합계 요약 생성
summary_df = merged_df.groupby("카테고리")["매출금액"].sum().reset_index()
summary_df.columns = ["카테고리", "총매출"]
summary_df = summary_df.sort_values("총매출", ascending=False)

# 원본 시트 + 요약 시트를 하나의 엑셀 파일로 저장
output_path = "주간_판매_리포트.xlsx"
with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
    merged_df.to_excel(writer, sheet_name="전체데이터", index=False)
    summary_df.to_excel(writer, sheet_name="카테고리요약", index=False)

print(f"완료! '{output_path}' 파일 생성됨")

4. 직접 맞은 에러 2가지와 해결법

에러 ① PermissionError — 엑셀 파일이 열려 있을 때

코드를 실행했는데 PermissionError: [Errno 13] Permission denied가 떴다. 저장하려는 엑셀 파일을 내가 직접 열어둔 채로 파이썬이 덮어쓰려 해서 생긴 충돌이었다. 해결책은 단순하다. 코드를 실행하기 전에 해당 엑셀 파일을 닫으면 된다. 아니면 출력 파일명에 타임스탬프를 붙여서 매번 새 파일로 저장하는 방식도 좋다.

from datetime import datetime
timestamp = datetime.now().strftime("%Y%m%d_%H%M")
output_path = f"주간_판매_리포트_{timestamp}.xlsx"

에러 ② 날짜 컬럼이 문자열로 읽혀 정렬이 엉키는 문제

날짜 기준으로 정렬했는데 순서가 뒤죽박죽이었다. 엑셀에서는 날짜처럼 보이지만 pandas가 읽을 때 object(문자열) 타입으로 인식한 것이었다. 문자열 정렬은 “2026-02-10″이 “2026-01-09″보다 앞에 오는 이상한 결과를 낸다. pd.to_datetime()으로 타입을 명시적으로 바꿔주면 해결된다.

merged_df["주문일자"] = pd.to_datetime(merged_df["주문일자"])
merged_df = merged_df.sort_values(by="주문일자", ascending=False)

5. 응용 확장: 이 코드로 더 할 수 있는 것들

오늘 만든 코드가 뼈대가 되면, 여기서 확장은 어렵지 않다.

  • 자동 이메일 발송: smtplib 모듈과 연결하면 리포트 파일을 만드는 즉시 지정된 메일 주소로 자동 발송할 수 있다. 팀장에게 매주 직접 첨부파일을 보내는 작업도 코드가 대신한다
  • 스케줄러 연동: APScheduler나 Windows 작업 스케줄러에 등록하면 매주 월요일 오전 8시에 스크립트가 자동 실행된다. 출근하기 전에 리포트가 이미 완성돼 있다
  • 조건부 하이라이트: openpyxl의 스타일 기능을 쓰면 매출이 목표치 이하인 행을 빨간색으로 자동 색칠할 수 있다. 엑셀에서 조건부 서식을 수동으로 거는 작업도 코드 한 줄로 대체된다

처음 pandas를 배울 때 “문법이 직관적이지 않다”는 느낌을 받을 수 있다. 그냥 일단 오늘 코드를 본인 파일에 맞게 컬럼명만 바꿔서 돌려보자. 10초 만에 리포트가 완성되는 그 순간, 매주 월요일이 달라진다.