많은 데이터 애널리스트에게 Excel은 간편함과 유연성 덕분에 여전히 필수적인 도구입니다. 하지만 대용량 데이터 처리, 반복 작업, 복잡한 분석에는 Python이 속도, 자동화, 고급 분석 면에서 훨씬 뛰어난 성능을 발휘합니다. Excel과 Python을 통합하면 두 도구의 장점을 동시에 활용할 수 있습니다.
이 튜토리얼에서는 Excel과 Python을 결합해 강력한 데이터 과학 워크플로를 구축하는 방법을 단계별로 소개합니다.
필수 도구 및 환경 설정
Excel과 Python을 연동하기 전에 먼저 환경을 설정해야 합니다. 이렇게 하면 첫 단계부터 원활하고 생산적인 워크플로를 유지할 수 있습니다.
사전 준비물:
- Microsoft Excel: 초기 데이터 검토 및 보고서 작성용
- Python 3.x: 데이터 과학 워크플로의 엔진 역할
- Python 라이브러리:
- pandas: 데이터 분석
- matplotlib: 차트 생성
- openpyxl(선택 사항): Excel 파일 쓰기
- numpy: 수치 계산
- matplotlib/seaborn: 데이터 시각화
Python 라이브러리 설치:
pip install pandas matplotlib openpyxl
1. Python으로 데이터 불러오기
pandas를 사용하면 Excel 파일의 데이터를 손쉽게 불러와 조작하고 분석할 수 있습니다.
import pandas as pd
# Excel 파일에서 데이터 읽기
df = pd.read_excel('SalesData.xlsx')
# 데이터 미리 보기
print(df.head()) # 데이터의 첫 5행 출력
print(df.info()) # 열 정보, 데이터 타입, 결측치 현황 출력
- pd.read_excel()은 Excel 파일을 pandas DataFrame으로 불러옵니다.
- df.head()는 첫 5개 행을 표시하며 빠른 데이터 확인에 유용합니다.
- df.info()는 행·열 개수와 데이터 타입 등 데이터셋의 전체 구조를 보여줍니다.
실행하면 매출 데이터의 앞부분과 아래 같은 요약 정보를 확인할 수 있습니다:
TransactionID Date CustomerID ProductID ProductName Category Quantity UnitPrice Region Channel SalesRep 0 100001 2024-01-02 C-100 P-101 Laptop Electronics 2.0 800.0 East Online Smith 1 100002 2024-01-02 C-101 P-102 Printer Electronics 1.0 200.0 West Retail Johnson 2 100003 2024-01-03 C-102 P-103 Mouse Electronics 5.0 25.0 North Online Lee 3 100004 2024-01-04 C-103 P-104 Desk Furniture 1.0 150.0 South Retail Brown 4 100005 2024-01-05 C-104 P-105 Monitor Electronics 3.0 175.0 NaN Online Davis <class 'pandas.core.frame.DataFrame'> RangeIndex: 63 entries, 0 to 62 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 TransactionID 63 non-null int64 1 Date 62 non-null datetime64[ns] 2 CustomerID 62 non-null object 3 ProductID 61 non-null object 4 ProductName 63 non-null object 5 Category 61 non-null object 6 Quantity 61 non-null float64 7 UnitPrice 62 non-null float64 8 Region 62 non-null object 9 Channel 62 non-null object 10 SalesRep 62 non-null object dtypes: datetime64[ns](1), float64(2), int64(1), object(7) memory usage: 5.5+ KB None
2. 데이터 정제 및 변환
원본 데이터는 분석에 바로 쓸 수 있는 경우가 드뭅니다. 이 단계에서는 결측치를 처리하고, 열의 데이터 타입을 올바르게 변환하며, 새로운 계산 필드를 추가합니다.
중복 제거:
# 중복 행 제거 df = df.drop_duplicates()
- 중복된 값을 삭제합니다.
결측치 확인:
# 열별 결측치 개수 출력 print(df.isnull().sum())
- 각 열에 몇 개의 결측값(NaN)이 있는지 보여줍니다. 결측치가 있다면 삭제할지, 대체할지 판단할 수 있습니다.
#출력: TransactionID 0 Date 1 CustomerID 1 ProductID 2 ProductName 0 Category 2 Quantity 2 UnitPrice 1 Region 1 Channel 1 SalesRep 1 dtype: int64
데이터 타입 변환:
# 필터링과 그룹화를 쉽게 하기 위해 'Date' 열을 pandas datetime 타입으로 변환 df['Date'] = pd.to_datetime(df['Date'])
- Date 열을 텍스트에서 pandas datetime 형식으로 변환해 필터링과 그룹화를 쉽게 만듭니다.
'TotalSales' 열 생성:
# 새 열 추가: 거래별 총 매출액 df['TotalSales'] = df['Quantity'] * df['UnitPrice']
- 거래 건별 총 매출액을 나타내는 새 열을 추가합니다.
시계열 분석을 위한 월(Month) 추출:
df['Month'] = df['Date'].dt.to_period('M')
- 월별로 매출을 그룹화하고 분석할 수 있는 Month 열을 생성합니다.
- 이후 print(df.head())로 정제된 데이터를 미리 확인하세요.
#출력: TransactionID Date CustomerID ProductID ProductName Category ... UnitPrice Region Channel SalesRep TotalSales Month 0 100001 2024-01-02 C-100 P-101 Laptop Electronics ... 800.0 East Online Smith 1600.0 2024-01 1 100002 2024-01-02 C-101 P-102 Printer Electronics ... 200.0 West Retail Johnson 200.0 2024-01 2 100003 2024-01-03 C-102 P-103 Mouse Electronics ... 25.0 North Online Lee 125.0 2024-01 3 100004 2024-01-04 C-103 P-104 Desk Furniture ... 150.0 South Retail Brown 150.0 2024-01 4 100005 2024-01-05 C-104 P-105 Monitor Electronics ... 175.0 NaN Online Davis 525.0 2024-01
3. 데이터 분석하기
깨끗하게 정제된 데이터셋이 준비되면, 비즈니스 가치를 창출하는 인사이트를 도출할 수 있습니다. 월별, 제품별, 지역별 매출 집계가 대표적인 예입니다.
월별 총 매출:
# 월별로 그룹화하여 각 달의 총 매출 합산
monthly_sales = df.groupby('Month')['TotalSales'].sum()
print(monthly_sales)
- 데이터를 Month 기준으로 그룹화하고 각 월의 TotalSales를 합산합니다.
#출력: Month 2024-01 9075.0 2024-02 9800.0 2024-03 9075.0 Freq: M, Name: TotalSales, dtype: float64
인기 판매 제품:
# 제품별로 그룹화해 총 매출을 합산한 뒤 높은 순으로 정렬
product_sales = df.groupby('ProductName')['TotalSales'].sum().sort_values(ascending=False)
print(product_sales)
- 제품별 매출을 합산한 후 판매량이 많은 순서대로 정렬합니다.
#출력: ProductName Laptop 15200.0 Monitor 3850.0 Printer 3200.0 Desk 2550.0 Chair 2325.0 Mouse 1125.0 Name: TotalSales, dtype: float64
지역별 매출:
# 지역별로 그룹화해 지역별 총 매출 합산
region_sales = df.groupby('Region')['TotalSales'].sum()
print(region_sales)
- 지역별 총 매출을 집계합니다.
#출력: Region East 6075.0 North 5925.0 South 8225.0 West 7500.0
4. 핵심 인사이트 시각화
데이터는 시각화될 때 더 큰 힘을 발휘합니다. 간단한 차트를 만들어 핵심 트렌드를 한눈에 파악할 수 있도록 해보겠습니다.
4.1. 월별 매출 추이
import matplotlib.pyplot as plt # 시각화를 위한 임포트
# 월별 매출 막대그래프 생성
monthly_sales.plot(
kind='bar',
title='Total Sales by Month',
ylabel='Sales ($)',
xlabel='Month'
)
plt.tight_layout() # 라벨 겹침 방지
plt.savefig('monthly_sales.png') # PNG 파일로 저장
plt.show() # 차트 화면에 표시
- 월별 매출을 막대그래프로 표현합니다.
- plt.savefig는 보고서용으로 차트를 저장합니다.
- 막대그래프를 통해 매월 매출 변화를 확인할 수 있습니다.
4.2. 지역별 매출
# 지역별 매출 원그래프
region_sales.plot(
kind='pie',
autopct='%1.1f%%',
title='Sales Distribution by Region'
)
plt.ylabel('') # 기본 y축 라벨 제거
plt.tight_layout()
plt.savefig('region_sales.png')
plt.show()
- 지역별 매출 비중을 원그래프로 보여주며, 경영진이나 마케팅팀 보고에 적합합니다.
5. 고급 분석 및 모델링
기본적인 그룹화와 요약을 넘어, Python은 몇 줄의 코드만으로 고급 통계 분석, 피벗 테이블, 심지어 머신러닝까지 가능하게 해줍니다. 데이터를 더 깊이 파헤쳐 추가 인사이트를 찾아보겠습니다.
5.1. 기술통계
기술통계는 데이터셋의 빠른 요약을 제공하며, 수치형 열의 평균, 표준편차, 사분위수 등을 보여줍니다.
# 수치형 열의 요약 통계 출력 (평균, 표준편차, 최솟값, 최댓값, 사분위수 등) print(df.describe())
- df.describe()는 모든 수치형 열(Quantity, UnitPrice, TotalSales 등)을 빠르게 요약합니다.
#출력: TransactionID Quantity UnitPrice TotalSales count 61.000000 59.000000 60.000000 59.000000 mean 100030.180328 2.542373 262.083333 478.813559 std 17.497150 1.534905 277.339497 527.085627 min 100001.000000 1.000000 25.000000 75.000000 25% 100015.000000 1.000000 75.000000 162.500000 50% 100030.000000 2.000000 175.000000 300.000000 75% 100045.000000 3.000000 200.000000 525.000000 max 100060.000000 7.000000 800.000000 2400.000000
5.2. pandas 피벗 테이블
피벗 테이블은 Excel에서 대화형 보고서를 만들 때 강력한 기능인데, pandas로도 동일하게 구현할 수 있습니다.
# 피벗 테이블 생성: 지역별 TotalSales 합계 pivot = df.pivot_table(index='Region', values='TotalSales', aggfunc='sum') print(pivot)
- pivot_table()은 Excel의 피벗 테이블처럼 지역별 TotalSales를 요약합니다.
#출력 TotalSales Region East 6075.0 North 5925.0 South 8225.0 West 7500.0
5.3. 간단한 머신러닝 예제
판매 수량만으로 총 매출을 예측할 수 있는지 간단한 선형 회귀(머신러닝) 모델을 통해 확인해 보겠습니다.
from sklearn.linear_model import LinearRegression # scikit-learn에서 선형 회귀 임포트
# 특징 변수와 타깃 변수 준비
X = df[['Quantity']] # 특징: 판매 수량
y = df['TotalSales'] # 타깃: 총 매출액
# 회귀 모델 생성 및 학습
model = LinearRegression()
model.fit(X, y)
# 회귀 계수(기울기) 출력
print('Coefficient:', model.coef_)
# 절편(수량이 0일 때의 기본값) 출력
print('Intercept:', model.intercept_)
- scikit-learn에서 LinearRegression을 임포트합니다.
- Quantity를 사용해 TotalSales를 예측합니다.
- 모델을 학습시키고 계수(판매 수량이 1개 늘어날 때 매출 증가분)를 출력합니다.
#출력: Coefficient: [-37.65294772] Intercept: 596.8483500185391
6. 정제·분석된 데이터를 Excel로 내보내기
데이터의 정제, 분석, 모델링이 끝나면 요약 테이블과 인사이트를 여러 시트로 구성된 Excel 파일로 내보낼 수 있습니다. 핵심 결과물을 한곳에 모아 Excel에서 바로 검토할 수 있습니다.
# 요약 및 고급 분석 테이블을 다중 시트 Excel 파일로 내보내기
with pd.ExcelWriter('sales_summary.xlsx') as writer:
# 월별 요약
monthly_sales.to_frame().to_excel(writer, sheet_name='Monthly Sales')
# 제품별 요약
product_sales.to_frame().to_excel(writer, sheet_name='Product Sales')
# 지역별 요약
region_sales.to_frame().to_excel(writer, sheet_name='Region Sales')
# 피벗 테이블 (지역별 총 매출)
pivot.to_excel(writer, sheet_name='Pivot Table')
# 선택 사항: 기술통계 내보내기
df.describe().to_excel(writer, sheet_name='Descriptive Stats')
- 컨텍스트 매니저(with … as writer): 작성 후 Excel 파일이 올바르게 저장되고 닫히도록 보장합니다.
- 테이블별 .to_excel(): 각 DataFrame 또는 요약을 별도 시트에 저장해 접근성을 높입니다.
- 커스텀 시트 이름: 분석 단계에 맞게 각 시트에 명확한 이름을 부여합니다.
- Excel에서 sales_summary.xlsx 파일을 엽니다.
- Monthly Sales, Product Sales, Region Sales, Pivot Table, Descriptive Statistics 시트가 각각 나뉘어 저장된 것을 확인할 수 있습니다.
7. 워크플로 자동화 및 확장
Python을 활용하면 반복되는 보고서나 분석 작업을 자동화할 수 있습니다. 다음에 새 Excel 파일을 받으면 파일만 교체하고 스크립트를 다시 실행하면 됩니다. 모든 분석과 보고서가 즉시 갱신됩니다.
- 모든 분석 코드를 하나의 Python 파일에 유지합니다.
- 보고서를 업데이트하려면 CSV 파일을 교체하고 다음 명령을 실행합니다:
python Excel_to_Python.py
- 더 나아가 이 작업을 주간 또는 월간 스케줄로 예약 실행할 수도 있습니다.
결론
Excel의 직관적인 데이터 입력 및 보고 기능과 Python의 강력한 데이터 과학 역량을 결합하면, 방대하고 지저분한 데이터셋도 효율적으로 처리·분석할 수 있습니다. 반복적인 보고 작업이 자동화되고, 머신러닝과 고급 시각화의 문도 열립니다. Python 전문가가 되기 위해 밤새 공부할 필요는 없습니다. 간단한 작업 하나부터 시작하고, 성공하면 한 단계씩 추가하세요. 어느새 복잡한 보고서까지 자동화하고 있는 자신을 발견하게 될 것입니다.
무료 Excel 고급 연습 문제와 해설을 받아보세요!