매달 마감일만 되면 수십 개로 쪼개진 엑셀 파일을 일일이 열어서 VLOOKUP 함수를 걸고, 빈칸을 찾아서 채우느라 야근해 본 적 있으신가요? 엑셀 창이 하얗게 변하면서 ‘응답 없음’이 뜰 때의 그 답답함은 직장인이라면 누구나 한 번쯤 겪어보셨을 텐데요. 저 역시 몇 년 전 실무에서 수만 행짜리 판매 실적 엑셀을 다루다가 컴퓨터가 다운되어 눈물을 머금고 밤을 새웠던 뼈아픈 기억이 있어요. 😅
하지만 파이썬의 대표적인 데이터 분석 라이브러리인 판다스(Pandas)를 접한 이후로는 제 업무 환경이 180도 완전히 바뀌었답니다. 마우스로 수없이 클릭하고 복사하며 2~3시간씩 걸리던 데이터 정제 작업이 단 5초 만에 깔끔하게 자동화되니까요! 오늘은 파이썬 코딩이 낯선 초보자분들도 오늘 당장 실무에 써먹을 수 있는 엑셀 불러오기 및 데이터 전처리 핵심 기법을 친절하게 알려드릴게요.
1. 판다스 환경 준비와 엑셀 불러오기 기본기 📂
판다스로 엑셀 파일을 다루기 위해서는 먼저 파이썬 환경에 판다스와 함께 최신 엑셀 확장자(.xlsx)를 해석해 주는 파서 엔진 라이브러리를 설치해야 해요. 터미널이나 명령 프롬프트(CMD) 창을 열고 아래 명령어를 입력해 주세요.
설치가 완료되었다면 가장 기본적인 데이터 로드 함수인 pd.read_excel()을 사용합니다. 파일 경로만 지정해 주면 스프레드시트의 표가 판다스의 핵심 구조인 데이터프레임(DataFrame) 객체로 순식간에 변환됩니다.
# 기본 파일 읽기
df = pd.read_excel(‘sales_data.xlsx’)
# 특정 시트 및 필요한 열(Column)만 골라서 불러오기
df_target = pd.read_excel(
‘sales_data.xlsx’,
sheet_name=‘2024년매출’,
usecols=[‘주문번호’, ‘고객명’, ‘주문금액’, ‘주문일자’],
skiprows=2 # 상단 불필요한 제목 행 건너뛰기
)
판다스는 기본적으로
openpyxl 엔진을 통해 .xlsx 파일을 처리합니다. 간혹 오래된 .xls 파일(Excel 97-2003)을 읽어야 한다면 pip install xlrd를 추가로 설치해 주셔야 오류 없이 읽을 수 있다는 점을 꼭 기억해 두세요!
2. 데이터 첫인상 확인하기: 탐색적 데이터 분석(EDA) 🔍
데이터를 성공적으로 불러왔다면, 본격적인 가공에 들어가기 전에 데이터가 어떤 상태인지 구조를 훑어봐야 해요. 판다스에서는 단 몇 개의 명령어로 데이터의 크기, 결측치, 데이터 타입(Dtype)을 파악할 수 있습니다.
df.head(): 데이터의 상단 5개 행을 미리 보기 형태로 출력합니다.df.shape: 데이터프레임의 전체 크기(행 개수, 열 개수)를 튜플 형태로 반환합니다.df.info(): 각 열의 이름, 결측치를 제외한 데이터 수, 데이터 타입(정수, 실수, 문자열 등)을 종합적으로 보여줍니다.df.isnull().sum(): 열별로 비어 있는 결측치(NaN)가 몇 개 존재하는지 카운트합니다.
엑셀 작업 방식과 판다스 탐색 방식 비교
| 확인 작업 | 기존 엑셀 방식 | 판다스 명령어 | 장점 및 특징 |
|---|---|---|---|
| 데이터 규모 파악 | 스크롤을 끝까지 내리거나 단축키 이동 | df.shape |
0.01초 만에 전체 행/열 확인 |
| 빈칸(결측치) 확인 | 필터 버튼 누르고 ‘(공백)’ 항목 체크 | df.isnull().sum() |
모든 열의 누락치를 한 번에 요약 |
| 데이터 형식 점검 | 셀 서식 창 열어서 텍스트/숫자 확인 | df.dtypes / df.info() |
문자열, 날짜, 수치형 오염 방지 |
| 기초 통계량 계산 | AVERAGE, MIN, MAX 함수 각각 입력 | df.describe() |
평균, 사분위수, 최댓값 즉시 산출 |
엑셀에서는 분명 숫자처럼 보이던 데이터가 판다스에서는
object(문자열) 타입으로 인식되는 경우가 흔합니다. 원인은 쉼표(,)가 포함되어 있거나 중간에 빈 문자열 또는 하이픈(-) 같은 기호가 끼어있기 때문인데요. 이 상태에서는 수치 연산이나 집계 함수가 동작하지 않으므로 데이터 타입을 반드시 확인해야 합니다.
3. 실무에서 가장 많이 쓰는 핵심 전처리 4단계 🧹
엑셀 데이터를 불러온 뒤 실제 분석이나 보고서에 사용할 수 있도록 정돈하는 4가지 핵심 전처리 공식을 정리해 드릴게요.
📝 판다스 전처리 파이프라인 공식
전처리 완료 데이터 = 결측치 정제 + 중복행 제거 + 타입 변환(날짜/숫자) + 조건 필터링
-
1단계: 결측치(누락된 빈칸) 처리하기
데이터가 비어 있는 행을 아예 삭제하고 싶다면df.dropna()를, 특정 기본값(예: 0이나 ‘미입력’)으로 채우고 싶다면df.fillna()를 사용합니다.# 주문금액 결측치는 0으로 채우고, 고객명 결측치는 해당 행 삭제
df[‘주문금액’] = df[‘주문금액’].fillna(0)
df = df.dropna(subset=[‘고객명’]) -
2단계: 중복 데이터 제거하기
실수로 두 번 입력되었거나 복사 붙여넣기 과정에서 생긴 중복 레코드는drop_duplicates()한 줄로 깔끔하게 정리됩니다.# 동일한 주문번호가 있다면 최신 항목 1개만 남기고 중복 제거
df = df.drop_duplicates(subset=[‘주문번호’], keep=’last’) -
3단계: 데이터 타입 변환하기 (날짜 및 숫자)
문자열로 읽힌 날짜 컬럼은pd.to_datetime()으로 변환하고, 금액 내 콤마(,)를 제거한 뒤 정수형(int)으로 변환합니다.df[‘주문일자’] = pd.to_datetime(df[‘주문일자’])
df[‘주문금액’] = df[‘주문금액’].astype(str).str.replace(‘,’, ”).astype(int) -
4단계: 조건부 필터링 및 파생 컬럼 생성
원하는 조건의 행만 쏙 골라내고 새로운 지표를 계산해 컬럼에 추가합니다.# 주문금액이 10만 원 이상인 우수 주문만 추출하고 부가세 컬럼 추가
df_vip = df[df[‘주문금액’] >= 100000].copy()
df_vip[‘부가세’] = df_vip[‘주문금액’] * 0.1
🔢 전처리 작업 시간 절약 계산기
실무에서 다루는 엑셀 데이터의 규모와 월간 반복 횟수를 입력해 보세요. 판다스 자동화로 얼마나 많은 시간을 아낄 수 있는지 즉시 계산해 드립니다!
4. 실전 예시: 온오프라인 판매 실적 엑셀 정제하기 📚
이론만 배우면 와닿지 않으니, 실제 회사 업무에서 빈번하게 일어나는 판매 보고서 정제 상황을 예시로 들어볼게요. 실무 데이터의 지저분한 특성을 그대로 담은 상황입니다.
사례 주인공의 상황: 영업팀 김 대리의 마감 고충
- 취합 데이터: 전국 50개 매장에서 올라온 3만 행 규모의 주간 매출 엑셀 파일
- 골치 아픈 문제점: 일부 매장의 수량 누락(결측치), 판매가에 붙은 원화 표시와 콤마, 취합 실수로 인한 주문번호 중복
판다스를 활용한 정제 과정
1) pd.read_excel('weekly_sales.xlsx')로 원본 파일 즉시 로드
2) df.drop_duplicates(subset=['주문번호'])로 중복 입력된 주문 120건 제거
3) 수량 컬럼의 결측치는 0으로 채우고, 판매단가의 콤마를 제거한 뒤 두 컬럼을 곱해 '총매출' 파생 컬럼 계산
4) df.to_excel('정제완료_주간매출.xlsx', index=False)로 새 엑셀 파일 생성
최종 결과 및 성과
- 수작업 처리 시: 파일 열기부터 함수 입력, 검증까지 매주 2시간 이상 소요
- 판다스 코드 실행 시: 단 3.2초 만에 오류 없는 정제 엑셀 파일 완성!
김 대리처럼 작성해 둔 파이썬 스크립트는 한번 만들어 두면 다음 주에도 파일 이름만 바꿔서 엔터 한 번만 누르면 끝납니다. 이것이 바로 파이썬 데이터 전처리가 주는 강력한 자동화의 힘이에요!
가공이 끝난 데이터프레임을 다시 엑셀로 저장할 때는
df.to_excel('파일명.xlsx', index=False) 옵션을 반드시 붙여주세요. index=False를 생략하면 0부터 시작하는 판다스 고유의 행 인덱스 번호가 엑셀의 맨 첫 번째 열에 추가되어 서식이 흐트러질 수 있습니다.
마무리: 핵심 내용 요약 📝
오늘 배운 판다스 엑셀 전처리의 핵심 내용을 요약해 드릴게요. 이 흐름만 머릿속에 기억해 두셔도 실무 데이터 가공의 90% 이상을 해결하실 수 있습니다.
- 환경 및 파일 로드:
openpyxl엔진을 기반으로pd.read_excel()을 사용하며, 필요한 시트와 열만 지정해 불러옵니다. - 데이터 상태 확인:
df.info()와df.isnull().sum()으로 데이터 타입과 결측치 여부를 사전에 파악합니다. - 데이터 정제 및 가공:
dropna()와fillna()로 빈칸을 다듬고, 문자열 치환 후 수치형 타입(int,float)으로 변환합니다. - 결과물 저장:
df.to_excel('결과.xlsx', index=False)로 인덱스 열 없이 깔끔하게 저장합니다.
판다스 엑셀 전처리 한눈에 보기
자주 묻는 질문 ❓
usecols 옵션으로 꼭 필요한 컬럼만 지정하여 불러오거나, 가능하면 엑셀 대신 처리 속도와 압축률이 훨씬 뛰어난 CSV 또는 Parquet 형식으로 변환하여 작업하시는 것을 추천합니다.pd.to_datetime(df['날짜컬럼'], unit='D', origin='1899-12-30') 코드를 적용하시면 정상적인 연-월-일 형식으로 깨끗하게 변환됩니다.pd.read_excel('파일명.xlsx', sheet_name=None)과 같이 sheet_name 파라미터에 None을 전달하면, 모든 시트를 {시트이름: 데이터프레임} 형태의 파이썬 딕셔너리로 한꺼번에 불러와 반복문으로 손쉽게 처리할 수 있습니다..str.replace()를 사용하면 간단합니다. 예를 들어 df['금액'] = df['금액'].astype(str).str.replace(r'[^\d.]', '', regex=True).astype(int)와 같이 정규식을 활용하면 숫자와 마침표를 제외한 모든 통화 기호 및 쉼표를 한 번에 제거할 수 있습니다.read_excel()은 엑셀 파일이 마지막으로 저장될 당시 계산되어 저장된 계산 결과값을 읽어옵니다. 수식 텍스트 자체를 읽어오는 것은 아니므로 안심하고 수치 연산 및 집계 작업에 활용하실 수 있습니다.
처음에는 파이썬 코드를 한 줄씩 타이핑하는 과정이 엑셀 마우스 클릭보다 낯설게 느껴질 수 있지만, 익숙해지면 반복되는 엑셀 지옥에서 완벽히 해방될 수 있습니다. 오늘 소개해 드린 전처리 4단계를 여러분의 실제 업무 엑셀 파일에 바로 적용해 보세요. 더 궁금한 점이나 막히는 코드가 있다면 편하게 댓글로 물어봐주세요~ 😊