파이썬(Python) 공부 12편 — 엑셀·CSV 자동화 완전 정복 | openpyxl·pandas 읽기·쓰기·필터·피벗·차트까지

12 / 15 Python 공부 시리즈 — 엑셀·CSV 자동화

← 11편: 모듈·패키지·pip 완전 정복 보러 가기

Python 공부 시리즈 · 12편 | openpyxl · pandas · 엑셀 · CSV 자동화

파이썬(Python) 공부 12편
엑셀·CSV 자동화 완전 정복

매달 반복하는 엑셀 작업, 파이썬 한 번만 배우면 자동화할 수 있습니다. 표준 라이브러리 csv 모듈부터 openpyxl로 엑셀 직접 제어, pandas로 데이터를 분석·가공하는 방법까지 — 실무에서 바로 쓸 수 있는 코드를 위주로 정리합니다.

csv 표준 모듈 openpyxl 엑셀 제어 pandas 데이터프레임 필터·정렬·집계 groupby / pivot_table 엑셀 차트 생성

---

🔧 어떤 도구를 골라야 할까 — openpyxl vs pandas

파이썬으로 엑셀·CSV를 다루는 방법은 크게 세 가지입니다. 상황에 따라 어떤 도구를 쓸지 결정하는 것이 먼저입니다.

openpyxl 엑셀 파일을 직접 제어

• 셀 스타일·색상·폰트 적용
• 수식 삽입, 차트 생성
• 시트 이름 변경·추가·삭제
• 기존 파일 구조 유지하며 편집
• 데이터 분석보다는 보고서 생성에 적합

pandas 데이터 분석·가공 최강자

• 필터·정렬·집계·피벗 한 줄로
• CSV, 엑셀, JSON, SQL 모두 읽기
• 수백만 행 데이터도 빠르게 처리
• 결측값 처리, 데이터 정제 편리
• 데이터 분석·자동화에 적합

언제 어떤 도구를?

• csv 표준 모듈: 단순 CSV 읽기·쓰기 / 외부 라이브러리 설치 없이 가볍게
• openpyxl: 셀 스타일·수식·차트가 필요한 엑셀 보고서 생성
• pandas: 데이터 필터·정렬·집계·피벗 등 분석 작업 전반
• pandas + openpyxl 조합: 데이터는 pandas로 처리 → 결과를 openpyxl로 꾸며서 저장 (가장 강력)

▲ 파이썬으로 엑셀 업무를 자동화하면 반복 작업에서 벗어날 수 있다. openpyxl과 pandas는 엑셀·CSV 자동화의 양대 도구다. (출처: Unsplash / 참고 이미지)

---

📄 csv 표준 모듈 — 설치 없이 바로 사용

파이썬 표준 라이브러리의 csv 모듈은 pip 없이 바로 import 해서 쓸 수 있습니다. 복잡한 분석 없이 CSV를 읽거나 쓸 때는 이걸로 충분합니다.

 

csv_basic.py — csv 모듈 읽기·쓰기

import csv

# ─── CSV 쓰기 ───────────────────────────────────
rows = [
    ["이름", "부서", "매출"],
    ["김철수", "영업1팀", 1500000],
    ["이영희", "영업2팀", 2300000],
    ["박민준", "마케팅",  980000],
]

with open("sales.csv", "w", newline="", encoding="utf-8-sig") as f:
    # utf-8-sig → 엑셀에서 한글 깨짐 방지
    writer = csv.writer(f)
    writer.writerows(rows)

# ─── CSV 읽기 (리스트) ─────────────────────────
with open("sales.csv", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    for row in reader:
        print(row)
# ['이름', '부서', '매출']
# ['김철수', '영업1팀', '1500000']  ...

# ─── CSV 읽기 (딕셔너리) ───────────────────────
with open("sales.csv", encoding="utf-8-sig") as f:
    reader = csv.DictReader(f)   # 첫 줄을 헤더로 자동 인식
    for row in reader:
        print(row["이름"], row["매출"])
# 김철수 1500000
# 이영희 2300000  ...

---

📊 openpyxl — 엑셀 파일 직접 제어

openpyxl은. xlsx 파일을 읽고 쓰는 전용 라이브러리입니다. 셀에 값을 넣는 것은 물론 스타일·수식·차트까지 넣을 수 있어 자동화된 보고서를 만드는 데 자주 쓰입니다.

설치

pip install openpyxl

 

openpyxl_basic.py — 엑셀 읽기·쓰기·스타일

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

# ─── 새 워크북 만들기 ─────────────────────────
wb = Workbook()
ws = wb.active
ws.title = "월별매출"

# 헤더 작성 + 스타일
headers = ["이름", "부서", "1월", "2월", "3월", "합계"]
for col, h in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=h)
    cell.font    = Font(bold=True, color="FFFFFF")
    cell.fill    = PatternFill("solid", fgColor="217346")  # 엑셀 초록
    cell.alignment = Alignment(horizontal="center")

# 데이터 입력
data = [
    ("김철수", "영업1팀", 1200, 1500, 1800),
    ("이영희", "영업2팀", 900,  1100, 1300),
    ("박민준", "마케팅",  750,  800,  950),
]
for r, row in enumerate(data, 2):
    for c, val in enumerate(row, 1):
        ws.cell(row=r, column=c, value=val)
    # 합계 수식 (C~E열 합계)
    ws.cell(row=r, column=6, value=f"=SUM(C{r}:E{r})")

# 열 너비 자동 조정
for col in ws.columns:
    max_len = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_len + 4

wb.save("monthly_sales.xlsx")
print("엑셀 저장 완료")

# ─── 기존 파일 읽기 ──────────────────────────
wb2 = load_workbook("monthly_sales.xlsx")
ws2 = wb2["월별매출"]
for row in ws2.iter_rows(min_row=2, values_only=True):
    print(row)   # ('김철수', '영업1팀', 1200, 1500, 1800, '=SUM(C2:E2)')

📈

openpyxl 차트 생성 엑셀 파일 안에 막대 차트 삽입하기

openpyxl은 엑셀의 차트 기능도 코드로 제어할 수 있습니다. BarChart, LineChart, PieChart 등 다양한 차트 타입을 지원합니다.

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title  = "월별 매출 현황"
chart.style  = 10
chart.y_axis.title = "매출 (만원)"
chart.x_axis.title = "담당자"

# 데이터 범위: C2:E4 (1~3월 데이터)
data_ref = Reference(ws, min_col=3, max_col=5, min_row=1, max_row=4)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
chart.shape = 4
ws.add_chart(chart, "H2")   # H2 셀 위치에 삽입
wb.save("monthly_sales.xlsx")

---

📉 pandas — 데이터프레임으로 엑셀·CSV 분석

pandas의 핵심은 DataFrame(데이터프레임)입니다. 엑셀의 시트처럼 행과 열로 이루어진 2차원 데이터 구조입니다. 필터·정렬·집계·피벗 같은 작업을 단 한두 줄로 처리할 수 있어서 데이터 분석 업무의 필수 도구입니다.

설치

pip install pandas openpyxl

엑셀 파일 읽기에는 openpyxl 엔진이 필요하므로 함께 설치합니다.

 

pandas_basic.py — 읽기·기본 조작

import pandas as pd

# ─── 파일 읽기 ────────────────────────────────
df  = pd.read_csv("sales.csv", encoding="utf-8-sig")
df2 = pd.read_excel("sales.xlsx", sheet_name="Sheet1")

# ─── 데이터 확인 ──────────────────────────────
print(df.head(3))       # 상위 3행
print(df.tail(2))       # 하위 2행
print(df.shape)          # (행수, 열수)  예: (100, 6)
print(df.dtypes)         # 각 열의 데이터 타입
print(df.info())         # 전체 구조 요약
print(df.describe())     # 수치 열 통계 요약 (평균·표준편차 등)
print(df.isnull().sum()) # 결측값 개수

# ─── DataFrame 직접 생성 ─────────────────────
df3 = pd.DataFrame({
    "이름": ["김철수", "이영희", "박민준"],
    "부서": ["영업1", "영업2", "마케팅"],
    "매출": [1500000, 2300000, 980000],
})

---

🔍 pandas 핵심 — 필터·정렬·집계·결측값

 

pandas_filter_sort.py

import pandas as pd

df = pd.DataFrame({
    "이름": ["김철수", "이영희", "박민준", "최지우", "정현수"],
    "부서": ["영업", "영업", "마케팅", "마케팅", "영업"],
    "매출": [1500, 2300, 980, 1750, None],
    "나이": [32, 28, 35, 29, 41],
})

# ─── 필터링 ───────────────────────────────────
high    = df[df["매출"] > 1500]               # 매출 1500 초과
sales   = df[df["부서"] == "영업"]             # 영업 부서만
multi   = df[((df["매출"] > 1000) &
              (df["부서"] == "영업"))]        # AND 조건
isin    = df[df["부서"].isin(["영업", "마케팅"])]  # isin 필터
name_f  = df[df["이름"].str.startswith("김")]  # 문자열 필터

# ─── 정렬 ─────────────────────────────────────
asc     = df.sort_values("매출")                   # 오름차순
desc    = df.sort_values("매출", ascending=False)  # 내림차순
multi_s = df.sort_values(["부서", "매출"],
                           ascending=[True, False])  # 다중 정렬

# ─── 열 선택·추가·삭제 ───────────────────────
names   = df["이름"]                              # Series 반환
sub     = df[["이름", "매출"]]                    # DataFrame 반환
df["등급"] = df["매출"].apply(
    lambda x: "A" if x and x >= 2000 else "B")  # 열 추가
df = df.drop(columns=["나이"])                  # 열 삭제

# ─── 결측값 처리 ──────────────────────────────
filled  = df.fillna(0)                          # NaN → 0 대체
dropped = df.dropna()                            # NaN 행 제거
mean_f  = df["매출"].fillna(df["매출"].mean())  # 평균으로 대체

# ─── 기본 집계 ────────────────────────────────
print(df["매출"].sum())    # 합계
print(df["매출"].mean())   # 평균
print(df["매출"].max())    # 최대
print(df["부서"].value_counts())  # 부서별 인원 수

---

📊 groupby와 pivot_table — 부서별·조건별 집계

 

pandas_groupby_pivot.py

import pandas as pd

df = pd.DataFrame({
    "이름": ["김철수", "이영희", "박민준", "최지우", "정현수", "한수진"],
    "부서": ["영업", "영업", "마케팅", "마케팅", "영업", "마케팅"],
    "분기": ["Q1", "Q2", "Q1", "Q2", "Q1", "Q2"],
    "매출": [1500, 2300, 980, 1750, 1200, 2100],
})

# ─── groupby ─────────────────────────────────
dept_sum = df.groupby("부서")["매출"].sum()
print(dept_sum)
# 부서
# 마케팅    4830
# 영업      5000

dept_stats = df.groupby("부서")["매출"].agg(["sum", "mean", "count"])
print(dept_stats)
#         sum    mean  count
# 마케팅  4830  1610.0      3
# 영업    5000  1666.7      3

# 다중 그룹
multi_g = df.groupby(["부서", "분기"])["매출"].sum().reset_index()
print(multi_g)

# ─── pivot_table ─────────────────────────────
pivot = df.pivot_table(
    values="매출",
    index="부서",
    columns="분기",
    aggfunc="sum",
    fill_value=0
)
print(pivot)
# 분기      Q1    Q2
# 부서
# 마케팅   980  3850
# 영업    2700  2300

# ─── 파일 저장 ────────────────────────────────
df.to_csv("result.csv", index=False, encoding="utf-8-sig")
df.to_excel("result.xlsx", index=False, sheet_name="집계결과")

# 여러 시트에 저장
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="원본", index=False)
    pivot.to_excel(writer, sheet_name="피벗")
    dept_stats.to_excel(writer, sheet_name="부서별집계")

---

📚 pandas 주요 메서드 한눈에

메서드 / 속성	용도	예시
read_csv() / to_csv()	CSV 읽기·저장	pd.read_csv("f.csv")
read_excel() / to_excel()	엑셀 읽기·저장	df.to_excel("f.xlsx")
head(n) / tail(n)	상위·하위 n행 확인	df.head(5)
shape / dtypes / info()	구조 확인	df.shape → (100, 6)
describe()	수치 통계 요약	평균·표준편차·사분위수
isnull() / fillna() / dropna()	결측값 확인·처리	df.fillna(0)
sort_values()	정렬	df.sort_values("매출", ascending=False)
filter (Boolean indexing)	조건 필터	df[df["매출"] > 1000]
groupby().agg()	그룹별 집계	df.groupby("부서")["매출"].sum()
pivot_table()	피벗 테이블	엑셀 피벗과 동일
apply() / map()	함수·값 적용	df["열"].apply(lambda x: x*2)
merge() / concat()	데이터프레임 합치기	SQL JOIN / 세로 합치기
rename() / drop()	열 이름 변경·삭제	df.rename(columns={"a":"b"})
value_counts()	값 빈도 집계	df["부서"].value_counts()

---

🌟 실전 자동화 패턴 — 폴더 내 CSV 파일 일괄 처리

 

batch_process.py — 폴더 내 CSV 합치기 + 엑셀 보고서 생성

import pandas as pd
from pathlib import Path

# 1. 폴더 안의 모든 CSV 파일 읽어서 하나로 합치기
data_dir = Path("monthly_data")
all_frames = []

for csv_file in data_dir.glob("*.csv"):
    df_tmp = pd.read_csv(csv_file, encoding="utf-8-sig")
    df_tmp["파일명"] = csv_file.stem   # 파일명 컬럼 추가
    all_frames.append(df_tmp)

combined = pd.concat(all_frames, ignore_index=True)
print(f"총 {len(combined)}행 합산 완료")

# 2. 결측값 처리
combined["매출"] = combined["매출"].fillna(0)

# 3. 부서별 집계
summary = combined.groupby("부서")["매출"].agg(
    합계="sum", 평균="mean", 건수="count"
).reset_index()

# 4. 엑셀 보고서로 저장 (여러 시트)
out_path = Path("output") / "monthly_report.xlsx"
out_path.parent.mkdir(exist_ok=True)

with pd.ExcelWriter(out_path, engine="openpyxl") as writer:
    combined.to_excel(writer, sheet_name="전체데이터", index=False)
    summary.to_excel(writer,  sheet_name="부서별집계",  index=False)

print(f"보고서 저장 완료: {out_path}")

---

📝 12편 실습 문제

실습 1 — openpyxl 성적표 만들기

• 학생 5명의 이름·국어·영어·수학 점수를 엑셀로 저장
• 합계·평균 열을 수식(=SUM(), =AVERAGE())으로 추가
• 헤더 셀에 배경색·굵은 폰트 스타일 적용
• 결과 파일 성적표.xlsx로 저장

실습 2 — pandas로 데이터 분석

• 실습 1에서 만든 엑셀 파일을 pandas로 읽기
• 평균 점수 70점 이상인 학생만 필터링
• 점수 합계 기준 내림차순 정렬
• 과목별(groupby) 평균 계산
• 결과를 분석결과.csv로 저장

 

practice_12.py — 예시 답안

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
import pandas as pd

# ── 실습 1: openpyxl 성적표 ───────────────────
wb = Workbook()
ws = wb.active
ws.title = "성적표"

headers = ["이름", "국어", "영어", "수학", "합계", "평균"]
students = [
    ("김민준", 85, 90, 78),
    ("이서연", 92, 88, 95),
    ("박지호", 65, 70, 60),
    ("최유진", 78, 82, 88),
    ("정다은", 55, 60, 50),
]

# 헤더 스타일
for c, h in enumerate(headers, 1):
    cell = ws.cell(1, c, h)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1F497D")
    cell.alignment = Alignment(horizontal="center")

# 데이터 + 수식
for r, s in enumerate(students, 2):
    for c, v in enumerate(s, 1): ws.cell(r, c, v)
    ws.cell(r, 5, f"=SUM(B{r}:D{r})")
    ws.cell(r, 6, f"=AVERAGE(B{r}:D{r})")
wb.save("성적표.xlsx")

# ── 실습 2: pandas 분석 ──────────────────────
df = pd.read_excel("성적표.xlsx", engine="openpyxl")
# 수식 셀은 읽히지 않으므로 합계·평균 재계산
df["합계"] = df["국어"] + df["영어"] + df["수학"]
df["평균"] = df[["국어", "영어", "수학"]].mean(axis=1)

pass_df  = df[df["평균"] >= 70]               # 평균 70 이상 필터
sorted_df = pass_df.sort_values("합계", ascending=False)  # 합계 내림차순
subj_avg  = df[["국어","영어","수학"]].mean()  # 과목별 평균

print(sorted_df[["이름","합계","평균"]])
print(subj_avg)
sorted_df.to_csv("분석결과.csv", index=False, encoding="utf-8-sig")

12편 핵심 요약

• csv 표준 모듈: 설치 없이 CSV 읽기·쓰기 / reader·writer·DictReader / utf-8-sig로 한글 깨짐 방지
• openpyxl: .xlsx 직접 제어 / 셀 스타일·수식·차트 / Workbook→Worksheet→cell
• openpyxl 스타일: Font·PatternFill·Alignment·Border / column_dimensions로 열 너비
• pandas 읽기: read_csv() / read_excel() / sheet_name 지정 가능
• pandas 핵심 조작: 조건 필터 / sort_values / drop / apply / value_counts
• 결측값: isnull() 확인 → fillna() 대체 / dropna() 제거
• groupby: groupby("열").agg()로 그룹별 다중 집계 / reset_index()로 일반 DataFrame 변환
• pivot_table: index·columns·values·aggfunc 지정 → 엑셀 피벗 테이블과 동일
• pandas 저장: to_csv() / to_excel() / ExcelWriter로 여러 시트에 저장
• 실전 패턴: pathlib.glob으로 CSV 일괄 수집 → concat 합치기 → groupby 집계 → ExcelWriter 저장

다음 편 예고 13편 — 웹 스크래핑 기초

requests / BeautifulSoup / 셀레늄 / 실전 크롤링 패턴

🐍

---

※ 본 포스팅은 openpyxl 공식 문서(openpyxl.readthedocs.io)와 pandas 공식 문서(pandas.pydata.org)를 기반으로 작성된 학습용 콘텐츠입니다. 코드 예시는 Python 3.10 이상, openpyxl 3.x, pandas 2.x 환경에서 테스트되었습니다.