제각각인 50개의 엑셀을 단 하나로 통합하다
거래처마다 "일자", "거래일자", "금액", "공급가액" 등 서식이 모두 다르다면 어떻게 할까요?
수십 개의 파일을 파이썬으로 쓸어 담아, 하나의 통일된 장부로 매끄럽게 정제(Cleaning)하는 실무 최고급 기술을 배웁니다.
11단계의 최종 목표와 업무 흐름
11단계는 단순한 코딩이 아닙니다. 실제 회계법인/세무법인에서 수많은 거래처로부터 데이터를 넘겨받았을 때 겪게 되는 "데이터 불일치"를 자동화로 해결하는 데이터 파이프라인(Data Pipeline)을 구축합니다.
폴더에 있는 50개의 엑셀 파일을 파이썬이 자동으로 읽어들입니다.
제각각인 열 이름("금액", "공급가액")을 하나로 통일하고, 콤마(,)와 공백을 제거합니다.
이상한 금액(음수), 결측값, 중복 기장 의심 데이터를 색출합니다.
통합 장부와 검증 결과를 엑셀 다중 시트 보고서로 출력합니다.
실습을 위해 아래와 같은 폴더 구조를 준비합니다.
├── 📁 input/ (거래처가 보낸 엑셀들)
│ ├── 📄 ABC상사_매출.xlsx
│ └── 📄 DEF기업_매출.xlsx
├── 📁 output/ (결과물 저장)
└── 📄 main.py
파일을 쓸어 담는 마법: glob
수십 개의 파일 이름을 일일이 적을 수 없습니다. glob 모듈을 사용하면 특정 폴더의 엑셀 파일 경로를 리스트로 한 번에 가져올 수 있습니다.
import glob # input 폴더 안의 확장자가 .xlsx 인 모든 파일 리스트 files = glob.glob("input/*.xlsx") print(files) # 출력예: ['input/ABC상사.xlsx', 'input/DEF기업.xlsx', ...]
반복문과 concat() 으로 데이터 합치기
files 리스트를 반복문으로 돌면서 pd.read_excel로 읽어 빈 리스트에 차곡차곡 쌓아둡니다. 마지막에 pd.concat()을 이용해 하나의 거대한 DataFrame으로 병합합니다.
import pandas as pd import os dataframes = [] for file in files: df = pd.read_excel(file) # 파일 이름에서 거래처명을 추출하여 데이터에 삽입! (매우 유용) # "ABC상사.xlsx" -> "ABC상사" customer = os.path.splitext(os.path.basename(file))[0] df["거래처"] = customer df["원본파일"] = os.path.basename(file) dataframes.append(df) # 리스트에 모인 데이터프레임 50개를 아래로 쭈욱 이어 붙이기 all_data = pd.concat(dataframes, ignore_index=True)
데이터 정제 1: 열 이름 통일 & 공백 제거
거래처 A는 "일자", "금액", 거래처 B는 "거래일", "공급가액" 이라고 적어 보냅니다. 합치기 전에 rename으로 통일해야 합니다.
rename_map = { "일자": "거래일자", "거래일": "거래일자", "금액": "공급가액", "공급금액": "공급가액", "품명": "품목" } # 사전에 매핑된 이름대로 컬럼명 갈아끼우기 all_data = all_data.rename(columns=rename_map) # 거래처명 앞뒤에 몰래 숨은 띄어쓰기(공백) 제거 # " ABC상사 " -> "ABC상사" all_data["거래처"] = all_data["거래처"].astype(str).str.strip()
데이터 정제 2: 금액, 날짜, 사업자번호 통일
텍스트로 입력된 금액("1,500,000")은 계산할 수 없습니다. 콤마를 떼고 숫자로 바꿔야 합니다.
# 1. 금액: 문자열 콤마(,) 떼고 숫자(numeric)로 변환 all_data["공급가액"] = all_data["공급가액"].astype(str).str.replace(",", "") all_data["공급가액"] = pd.to_numeric(all_data["공급가액"], errors="coerce") # 오류나면 NaN # 2. 날짜: 2026/01/01, 26.01.01 등 제각각인 날짜를 YYYY-MM-DD 형식으로 통일 all_data["거래일자"] = pd.to_datetime(all_data["거래일자"], errors="coerce") # 3. 사업자번호: 대시(-)나 띄어쓰기를 지우고 10자리 숫자만 남김 all_data["사업자번호"] = all_data["사업자번호"].astype(str).str.replace(r"\D", "", regex=True)
데이터 검증 (누락, 중복, 이상 거래)
파이썬의 진정한 위력은 바로 여기서 나옵니다. 사람이 눈알 빠지게 찾아야 하는 중복 건과 이상 거래를 코드가 색출합니다.
# 1. 누락 데이터: 거래일자나 금액이 비어있는 행 추출 missing_data = all_data[ all_data["거래일자"].isna() | all_data["공급가액"].isna() ] # 2. 이상 금액: 음수 금액이거나, 터무니없는 금액(10억 이상) 색출 abnormal_data = all_data[ (all_data["공급가액"] < 0) | (all_data["공급가액"] >= 1000000000) ] # 3. 중복 의심 기장: 완전히 똑같은 날짜, 거래처, 금액이 두 번 기장된 경우 # keep=False 옵션을 주면 원본과 중복본 모두 추출하여 눈으로 확인할 수 있게 합니다. duplicates = all_data[ all_data.duplicated(subset=["거래일자", "거래처", "공급가액"], keep=False) ]
종합 실무 솔루션: 파이프라인 마스터 코드
11단계 교육의 꽃입니다. 위 과정을 하나의 파이썬 스크립트로 구성하여, main.py를 실행하는 순간 수십 개의 엑셀이 하나의 완벽한 리포트로 변신합니다.
import pandas as pd import glob, os # 1. 수집 (Collection) & 정제(Rename) files = glob.glob("input/*.xlsx") dataframes = [] for file in files: df = pd.read_excel(file) df["거래처"] = os.path.splitext(os.path.basename(file))[0] df = df.rename(columns={"일자":"거래일자", "금액":"공급가액", "품명":"품목"}) dataframes.append(df) all_data = pd.concat(dataframes, ignore_index=True) # 2. 정제 (Cleaning Formatting) all_data["공급가액"] = pd.to_numeric(all_data["공급가액"].astype(str).str.replace(",", ""), errors="coerce") all_data["거래일자"] = pd.to_datetime(all_data["거래일자"], errors="coerce") all_data["월"] = all_data["거래일자"].dt.to_period("M").astype(str) # 3. 분석 (Analysis) - 집계 cust_summary = all_data.groupby("거래처")["공급가액"].sum().reset_index() # 4. 검증 (Validation) missing = all_data[all_data["거래일자"].isna()] duplicates = all_data[all_data.duplicated(subset=["거래일자", "거래처", "공급가액"], keep=False)] abnormal = all_data[all_data["공급가액"] < 0] # 5. 보고 (Reporting) - 다중 시트 저장 os.makedirs("output", exist_ok=True) with pd.ExcelWriter("output/final_report.xlsx") as w: all_data.to_excel(w, sheet_name="전체통합장부", index=False) cust_summary.to_excel(w, sheet_name="거래처별통계", index=False) missing.to_excel(w, sheet_name="누락_검토대상", index=False) duplicates.to_excel(w, sheet_name="중복의심_검토", index=False) abnormal.to_excel(w, sheet_name="이상거래_확인", index=False) print("✅ 완벽하게 통합 및 정제된 보고서가 output 폴더에 저장되었습니다.")
지금까지는 결과물을 엑셀로 뽑아냈습니다. 하지만 데이터가 수백만 건이 되면 엑셀이 버티지 못합니다.
다음 12단계부터는 MariaDB / MySQL 이라는 데이터베이스 시스템에 파이썬을 연결하여, 방대한 데이터를 SQL로 저장하고 조회하는 "진짜 엔터프라이즈 시스템"의 영역으로 들어갑니다.