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

작성자:

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

월요일 아침마다 출근하면 가장 먼저 마주하는 게 지난주 판매 데이터 파일 5개를 일일이 열고, 시트를 복사하고, 중복 데이터 정리에 합계 계산까지 하는 일이었다. 그 과정에서 팀장님께 보낼 요약 시트도 따로 만들어야 했는데, 제대로 꼬이기라도 하면 3시간은 기본이었다. 이런 반복 작업을 계속 손으로 하면서 ‘내가 이렇게까지 해야 하나’ 하는 회의감이 들곤 했다.

그런데 파이썬의 pandas 라이브러리를 알게 되면서 상황이 완전히 달라졌다. 이전에는 먼 길을 돌아가던 작업이, 이제는 스크립트 한 줄로 끝나버린다. 엑셀 파일을 읽고, 필요한 대로 손질한 뒤, 다시 저장하는 전 과정을 코드가 담당하는 덕분이다. 물론 처음에 설치부터 환경 세팅, 그리고 실전 코드를 손에 익히는 데 시간이 꽤 걸렸고, openpyxl 모듈을 깜빡했을 때는 ModuleNotFoundError 때문에 한참 고생했다. 특히 모바일 환경에서 작업하다 보면 메뉴가 꼬여서 제대로 작동하지 않을 때도 많아 불편했다.

그럼에도 불구하고 어느 순간부터는 이 과정을 반복하는 데 걸리는 시간이 10초 정도로 줄었다. 덕분에 월요일 아침이 덜 무겁고, 다른 중요한 업무에 집중할 여유가 생겼다. 지금부터 제가 실제로 쓰는 방법을 공유하려 한다. 설치부터 자동화 코드까지, 엑셀 반복 작업에 지친 분들께 조금이나마 도움이 되길 바란다.

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

pandas는 파이썬에서 표 형태 데이터를 다루는 대표 라이브러리다. 엑셀에서 직접 하던 필터, 정렬, 피벗 테이블 생성, 중복 제거, 합계 계산 같은 작업을 코드 한 줄로 처리할 수 있다. 하지만 단점이라면, 마우스를 클릭하는 대신 코드를 이해하고 작성해야 한다는 점이다. 그래서 익숙해지기까지 시간이 꽤 필요하다.

핵심은 DataFrame이라는 객체다. 엑셀 시트를 불러오면 pandas가 그걸 DataFrame으로 만든다. 이후 데이터 정리는 모두 이 DataFrame 안에서 이뤄지고, 다 끝나면 다시 엑셀 파일로 저장한다. 이 과정을 처음부터 다 기록해두면, 다음 주에 똑같은 작업을 할 때는 코드 실행만 하면 된다. 번거로움을 반복하지 않아도 된다는 게 가장 큰 이점이다.

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

  • Python 버전: 3.10 이상 사용을 권장한다. 낮은 버전에서는 호환 문제가 생길 수 있다.
  • 편집기: VS Code를 주로 사용하며, Jupyter 확장을 설치하면 코드 테스트가 한결 수월하다.
  • 필수 패키지: pandas, openpyxl, 그리고 표준 라이브러리인 glob를 쓴다. glob는 별도 설치 없이 바로 쓸 수 있다.
pip install pandas openpyxl

openpyxl은 pandas가 .xlsx 파일을 읽고 쓸 때 내부적으로 사용하는 엔진인데, 이걸 깜빡하고 설치하지 않으면 ModuleNotFoundError가 뜬다. 저도 처음 셋업할 때 이 에러 때문에 한참 시간을 썼는데, 설치만 제대로 해두면 앞으로 이런 문제는 피할 수 있다.

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

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

엑셀 파일이 여러 개 쌓여 있을 때마다 일일이 열어서 데이터를 복사하는 건 시간 낭비다. 저는 glob 모듈을 써서 폴더 내 모든 엑셀 파일 목록을 한꺼번에 가져온 다음, 반복문으로 전부 읽어 하나로 합치는 방식을 썼다. 이렇게 하면 파일이 몇 십 개여도 수 분 안에 끝난다.

다만 한 가지 주의할 점은, 파일마다 시트 구조가 조금이라도 다르면 에러가 발생한다는 것이다. 실제로 몇몇 파일에서 시트명이나 컬럼 순서가 달라서 한참 디버깅했는데, 이런 부분은 사전에 꼭 확인해야 한다고 생각한다.

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는 아주 간단하게 해낸다. 저는 drop_duplicates()로 완전 중복 행을 지우고, ‘주문번호’ 같은 핵심 컬럼이 비어 있는 행은 dropna()로 제거했다.

그런데 실제로는 중복이 완전히 같지 않고 아주 미세하게 달라서 중복 제거가 제대로 안 된 경우도 있었다. 이럴 때는 조건을 더 세밀하게 지정하거나, 추가적인 전처리 작업을 넣어야 하는데, 그 과정이 꽤 귀찮았다.

# 완전히 동일한 중복 행 제거
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단계: 팀장 보고용 요약 시트와 원본 시트를 한 파일에 저장

최종 데이터는 전체 내역과 카테고리별 매출 합계를 각각 다른 시트로 나눠서 한 파일에 담았다. 매주 팀장님께 보고하느라 수동으로 작업하던 걸 생각하면 천지차이다. 다만 openpyxl 엔진을 쓸 때 파일이 이미 열려 있으면 에러가 뜨는데, 이걸 체크하는 코드를 넣지 않으면 자동화가 중단된다.

이런 부분은 나중에 예외 처리나 파일 잠금 검사 코드를 추가해 안정성을 높여야 한다. 그래도 지금 단계만 해도 매주 몇 시간씩 쓰던 시간이 10분 안쪽으로 줄었다는 점은 분명히 만족스럽다.

# 카테고리별 매출 합계 요약 생성
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() 함수로 날짜 컬럼을 명확히 날짜 타입으로 변환해 주면 그제야 정상적으로 정렬된다. 이런 기본적인 타입 처리는 pandas 쓸 때 반드시 체크해야 할 부분이다.

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