Mojangcam

카테고리: 파이썬 (Python)

파이썬 pandas 실전 - 엑셀 파일 분석하기! 결측치·피벗테이블·여러 시트 📊

글쓴이 Mojangcam발행일 읽는 데 11분
목차14개 항목

안녕하세요! Mojangcam입니다 😎

지난 편에서는 pandas(판다스)로 DataFrame 만들기 → 필터링 → groupby 요약 → 엑셀 저장까지 기본기를 훑어봤어요. 오늘은 예고했던 대로 실전편이에요! 회사에서 받은 것 같은 엑셀 파일을 직접 불러와서, 비어 있는 칸(결측치)을 정리하고, 피벗 테이블로 지점별·월별 매출을 뽑고, 결과를 시트 3개짜리 보고서 엑셀로 저장하는 것까지 한 번에 해볼게요.

엑셀에서 피벗 만들고, 시트 나누고, 다음 달에 또 똑같이 반복하는 작업 있으시죠? 이 코드 하나 만들어두면 다음 달엔 파일만 바꿔서 실행 한 번이면 끝나요 🙌

📚 이 글은 pandas 2편이에요. 기초가 아직이라면 「파이썬 pandas(판다스) 입문 - 표 데이터 다루기! 설치부터 엑셀 저장까지」 를 먼저 보고 오시면 훨씬 수월해요.

🧠 오늘 배울 것 3가지

실무 엑셀 파일에는 거의 항상 이 세 가지 문제가 있어요.

  • 빈 칸(결측치) : 담당자가 안 적은 칸. 그냥 계산하면 결과가 통째로 깨져요.
  • 요약이 필요함 : "지점별로, 메뉴별로 얼마 팔았는지" → 엑셀 피벗 테이블의 역할이죠.
  • 시트가 여러 개 : 원본 / 요약 / 월별… 보고용으로 나눠 담아야 해요.

오늘은 이 셋을 각각 isna()·fillna(), pivot_table(), ExcelWriter로 해결해요.

📌 준비물

  • Python 3.11 이상 — pandas 3.x는 3.11+에서만 설치돼요.
  • pandas 3.x — 이 글 작성 기준 최신은 3.0.5 (2026-07-22 릴리스)
  • openpyxl — .xlsx 파일을 읽고 쓸 때 pandas가 내부적으로 쓰는 라이브러리예요.
cmake
pip install pandas openpyxl
coffeescript
import pandas as pd
print(pd.__version__)   # 3.0.5 처럼 나오면 준비 완료!

🛠️ 실습용 엑셀 파일 만들기

따라 할 파일이 없으신 분을 위해 예제 파일부터 만들어요. 아래 코드를 한 번만 실행하면 sales_raw.xlsx 가 생겨요. (이미 분석할 엑셀이 있다면 이 단계는 건너뛰고 파일명만 바꾸세요!)

코드
import pandas as pd

rows = [
    ("2026-07-01", "강남점", "아메리카노", 4500, 12),
    ("2026-07-01", "강남점", "라떼", 5000, 7),
    ("2026-07-01", "홍대점", "아메리카노", 4500, 9),
    ("2026-07-02", "홍대점", "케이크", 6500, None),   # 빈 칸(결측치)
    ("2026-07-02", "강남점", "케이크", 6500, 3),
    ("2026-07-03", "홍대점", "라떼", 5000, 11),
    ("2026-08-01", "강남점", "아메리카노", 4500, 15),
    ("2026-08-01", "홍대점", "라떼", 5000, None),     # 빈 칸(결측치)
    ("2026-08-02", "강남점", "케이크", 6500, 4),
    ("2026-08-02", "홍대점", "아메리카노", 4500, 13),
]

df = pd.DataFrame(rows, columns=["날짜", "지점", "메뉴", "가격", "수량"])
df.to_excel("sales_raw.xlsx", index=False, sheet_name="판매기록")
print("sales_raw.xlsx 생성 완료!")

일부러 수량이 비어 있는 행 2개를 넣어놨어요. 실무 데이터의 현실이니까요 😅

📌 1단계 - 엑셀 불러오고 상태 파악하기

coffeescript
import pandas as pd

df = pd.read_excel("sales_raw.xlsx", sheet_name="판매기록")

print(df.head())        # 앞 5행 미리보기
print(df.shape)         # (행 수, 열 수)
print(df.dtypes)        # 열별 데이터 타입

하나씩 뜯어볼게요 💡

  • read_excel("파일명", sheet_name="시트명") : 엑셀을 DataFrame으로 읽어요. sheet_name을 생략하면 첫 번째 시트를 읽고, 숫자(sheet_name=0)로 순서를 지정할 수도 있어요.
  • df.dtypes : 열마다 어떤 타입인지 보여줘요. 여기서 수량이 float64로 나오는데, 빈 칸(NaN) 때문에 정수가 실수로 바뀐 거예요. 이게 결측치가 있다는 첫 번째 신호랍니다.
  • 헤더가 3번째 줄부터 시작하는 지저분한 파일이라면 header=2, 위쪽 안내문 몇 줄을 버리려면 skiprows=2 를 쓰면 돼요.

📌 2단계 - 결측치 확인하고 처리하기

코드
# 열별로 빈 칸이 몇 개인지 세기
print(df.isna().sum())

# 빈 칸이 있는 행만 눈으로 확인
print(df[df["수량"].isna()])

# 수량 결측치는 0으로 채우고, 정수로 되돌리기
df["수량"] = df["수량"].fillna(0).astype(int)

하나씩 뜯어볼게요 💡

  • df.isna() : 각 칸이 비었는지를 True/False로 바꿔줘요. 여기에 .sum() 을 붙이면 열별 결측치 개수가 나와요. 데이터를 받으면 제일 먼저 하는 습관이에요!
  • fillna(0) : 빈 칸을 0으로 채워요. 상황에 따라 평균값(df["수량"].mean())이나 바로 위 값(ffill())으로 채우기도 해요.
  • astype(int) : 결측치를 없앴으니 다시 정수형으로. 안 하면 매출이 54000.0처럼 소수점으로 출력돼요.
  • 결측 행을 버려야 할 땐 df = df.dropna(subset=["수량"]) 를 쓰세요. "0으로 채울지, 버릴지"는 데이터의 의미에 따라 정하는 거예요. 판매 수량이라면 0이 자연스럽죠!

⚠️ 주의: 예전 블로그 글에서 자주 보이는 df.fillna(method="ffill") 문법은 pandas 2.1부터 경고가 뜨고 지금은 쓰지 않아요. 그냥 df.ffill() 을 쓰세요. df["수량"].fillna(0, inplace=True) 같은 inplace 연쇄 대입도 pandas 3.0의 Copy-on-Write 때문에 원본에 반영이 안 될 수 있으니, 위 코드처럼 왼쪽에 다시 대입하는 방식이 안전해요.

📌 3단계 - 날짜 변환 + 파생 열 만들기

prolog
df["날짜"] = pd.to_datetime(df["날짜"])   # 문자열 → 날짜 타입
df["월"] = df["날짜"].dt.month            # 월만 뽑아 새 열로
df["매출"] = df["가격"] * df["수량"]       # 매출 계산

print(df.head())

하나씩 뜯어볼게요 💡

  • pd.to_datetime() : 날짜처럼 생긴 문자열을 진짜 날짜 타입으로 바꿔줘요. 이걸 해야 월별·요일별 집계가 가능해져요.
  • .dt.month : 날짜 열 전용 도구상자예요. .dt.year(연), .dt.day(일), .dt.dayofweek(요일, 0=월요일)도 자주 써요.
  • df["매출"] = ... : 열끼리 곱하면 행마다 자동 계산돼요. 엑셀에서 수식 걸고 아래로 드래그하던 그 작업이 한 줄이에요!

📌 4단계 - 피벗 테이블로 요약하기

오늘의 하이라이트예요. 엑셀 피벗 테이블과 완전히 같은 개념이에요.

코드
pivot = df.pivot_table(
    index="메뉴",
    columns="지점",
    values="매출",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="합계",
)
print(pivot)

# 월별 x 지점 매출
monthly = df.pivot_table(index="월", columns="지점", values="매출", aggfunc="sum", fill_value=0)
print(monthly)

하나씩 뜯어볼게요 💡

  • index : 세로(행)에 놓을 기준. 엑셀 피벗의 "행" 영역이에요.
  • columns : 가로(열)에 놓을 기준. 엑셀 피벗의 "열" 영역이에요.
  • values : 실제로 집계할 숫자 열.
  • aggfunc : 집계 방법. "sum"(합계) 외에 "mean"(평균), "count"(개수), "max"도 되고, aggfunc=["sum", "mean"] 처럼 여러 개를 한 번에 볼 수도 있어요.
  • fill_value=0 : 해당 조합의 판매가 아예 없어서 비는 칸을 0으로 채워요. (안 넣으면 NaN으로 보여요)
  • margins=True : 마지막 행·열에 총합계를 붙여줘요. margins_name으로 이름도 바꿀 수 있어요.

📌 5단계 - 결과를 여러 시트로 저장하기

코드
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="정제데이터", index=False)
    pivot.to_excel(writer, sheet_name="지점별피벗")
    monthly.to_excel(writer, sheet_name="월별매출")

print("sales_report.xlsx 저장 완료!")

하나씩 뜯어볼게요 💡

  • pd.ExcelWriter(...) : 한 엑셀 파일에 여러 시트를 쓰기 위한 도구예요. with 블록을 나가는 순간 파일이 저장·정리돼요. (with 없이 쓰면 파일이 안 닫혀서 깨질 수 있어요!)
  • to_excel(writer, sheet_name=...) : 첫 인자로 파일명 대신 writer를 넘기는 게 포인트예요.
  • index=False : 원본 데이터는 0,1,2… 행 번호가 필요 없으니 빼요. 반대로 피벗 결과는 index가 곧 메뉴명이라 index를 살려둬야 해요. 두 줄의 차이를 눈여겨봐 주세요!

반대로 여러 시트를 한 번에 읽어올 때는 이렇게 해요.

코드
sheets = pd.read_excel("sales_report.xlsx", sheet_name=None)  # None = 전체 시트
print(list(sheets.keys()))          # ['정제데이터', '지점별피벗', '월별매출']
print(sheets["월별매출"])            # 시트 이름을 키로 꺼내 쓰기

sheet_name=None 을 주면 {시트이름: DataFrame} 형태의 딕셔너리로 돌려줘요. 시트가 12개짜리 월별 파일이라면 pd.concat(sheets.values()) 로 한 방에 합칠 수도 있어요 💡

📌 전체 코드 (복붙 OK)

코드
# pandas 3.x / Python 3.11+ / openpyxl 필요
import pandas as pd

# 1) 엑셀 불러오기
df = pd.read_excel("sales_raw.xlsx", sheet_name="판매기록")
print("=== 원본 미리보기 ===")
print(df.head())
print("행/열:", df.shape)

# 2) 결측치 확인 & 처리
print("\n=== 열별 결측치 개수 ===")
print(df.isna().sum())
df["수량"] = df["수량"].fillna(0).astype(int)

# 3) 날짜 변환 + 파생 열
df["날짜"] = pd.to_datetime(df["날짜"])
df["월"] = df["날짜"].dt.month
df["매출"] = df["가격"] * df["수량"]

# 4) 피벗 테이블
pivot = df.pivot_table(
    index="메뉴", columns="지점", values="매출",
    aggfunc="sum", fill_value=0, margins=True, margins_name="합계",
)
print("\n=== 지점 x 메뉴 매출 피벗 ===")
print(pivot)

monthly = df.pivot_table(index="월", columns="지점", values="매출", aggfunc="sum", fill_value=0)
print("\n=== 월별 지점 매출 ===")
print(monthly)

# 5) 시트 3개짜리 보고서로 저장
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="정제데이터", index=False)
    pivot.to_excel(writer, sheet_name="지점별피벗")
    monthly.to_excel(writer, sheet_name="월별매출")

print("\nsales_report.xlsx 저장 완료!")

📌 실행 결과

asciidoc
=== 열별 결측치 개수 ===
날짜    0
지점    0
메뉴    0
가격    0
수량    2
dtype: int64

=== 지점 x 메뉴 매출 피벗 ===
지점        강남점     홍대점      합계
메뉴
라떼      35000   55000   90000
아메리카노  121500   99000  220500
케이크     45500       0   45500
합계     202000  154000  356000

=== 월별 지점 매출 ===
지점     강남점    홍대점
월
7   108500  95500
8    93500  58500

sales_report.xlsx 저장 완료!

총매출 356,000원 중 아메리카노가 220,500원으로 압도적 1위네요! 홍대점 케이크는 판매 기록이 아예 없어서 fill_value=0 덕분에 0으로 깔끔하게 표시됐고요. 폴더를 열어보면 sales_report.xlsx 안에 시트 3개가 그대로 들어가 있어요 📊

📌 자주 나는 에러

  1. FileNotFoundError: [Errno 2] No such file or directory: 'sales_raw.xlsx'
    • 원인 : 실행 위치(작업 디렉터리)와 엑셀 파일이 있는 폴더가 달라요. VS Code에서 흔히 나요.
    • 해결 : 파일을 파이썬 코드와 같은 폴더에 두거나, r"C:\Users\내이름\Desktop\sales_raw.xlsx" 처럼 절대경로로 쓰세요. import os; print(os.getcwd()) 로 현재 위치를 찍어보면 바로 보여요.
  2. ValueError: Worksheet named '판매기록' not found
    • 원인 : sheet_name에 적은 시트 이름이 실제와 달라요. 앞뒤 공백도 범인이 될 수 있어요.
    • 해결 : pd.ExcelFile("sales_raw.xlsx").sheet_names 로 실제 시트 이름 목록을 확인하고 그대로 복사해 쓰세요.
  3. PermissionError: [Errno 13] Permission denied: 'sales_report.xlsx'
    • 원인 : 저장하려는 엑셀 파일을 엑셀 프로그램에서 열어둔 상태예요. 가장 흔한 실수예요!
    • 해결 : 엑셀 창을 닫고 다시 실행하세요.
  4. TypeError: unsupported operand type(s) / 매출이 NaN으로 나옴
    • 원인 : 결측치를 처리하지 않고 바로 곱셈을 했어요. NaN은 뭘 곱해도 NaN이에요.
    • 해결 : 2단계처럼 fillna() 또는 dropna() 를 계산 전에 먼저 하세요.

💡 보너스 - 다음 달엔 파일명만 바꾸면 끝

전체 코드의 맨 위 파일명을 변수로 빼두면 재사용이 훨씬 편해요.

INI
SRC = "sales_raw.xlsx"        # 이번 달 원본
OUT = "sales_report_08.xlsx"  # 저장할 보고서

여기에 이전 글에서 다룬 이메일 자동 발송이나 디스코드 웹훅 알림을 붙이면, "엑셀 분석 → 보고서 저장 → 팀에 자동 발송"까지 완전 자동화가 돼요 🚀

📌 마무리

오늘은 pandas로 엑셀 읽기 → 결측치 처리 → 날짜/파생 열 → 피벗 테이블 → 여러 시트 저장까지 실전 분석 흐름을 통째로 돌려봤어요. 이 다섯 단계가 사실상 엑셀 업무 자동화의 뼈대라, 여기까지 오셨으면 웬만한 반복 보고서는 스크립트 하나로 끝낼 수 있어요!

다음 pandas 편에서는 여러 엑셀 파일을 하나로 합치기(concat·merge) 와 그래프로 시각화하기(matplotlib) 를 다뤄볼게요. 기대해주세요 🙌

📚 pandas 시리즈

  1. 파이썬 pandas(판다스) 입문 - 표 데이터 다루기! 설치부터 엑셀 저장까지
  2. 파이썬 pandas 실전 - 엑셀 파일 분석하기! 결측치·피벗테이블·여러 시트 ← 지금 이 글!
  3. (예정) pandas 여러 파일 합치기 + 그래프 시각화

💬 함께 보면 좋은 글

  • 파이썬 업무 자동화 #1 - 엑셀 자동화 입문 (openpyxl)
  • 파이썬 엑셀 라이브러리 openpyxl 완전정복 - 모듈·클래스·함수 총정리
  • 파이썬 가상환경(venv) 완벽 정리

글쓴이

Mojangcam

직접 코드를 짜고, 서버를 돌리고, 막힌 에러를 해결하며 배운 것들을 기록하는 개발·IT 실전 기록 블로그

소개RSS 구독문의