[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화(3) : 데이터 클리닝 & 전처리 + 분석(총 매출, 평균 매출, 월별 매출추이) + 시각화(매출 트렌드 예측, 고객 세그먼트 분석)

 

이번에는 지난 포스팅에 이어서 조금 더 심화된 버전의 파이썬을 활용한 엑셀 자동화 입니다.

 

[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화 (1) : 데이터 생성 + 필터링 + 보고서 생성

[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화 (1) : 데이터 생성 + 필터링 + 보고서 생성

 

 

[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화(2) : 엑셀 데이터 가공 + 보고서 자동 생성 + 피벗 테이블 + (히트맵 + 다중차트)

[업무자동화 with Python] 파이썬을 활용한 엑셀 자동화(2) : 엑셀 데이터 가공 + 보고서 자동 생성 +

 

 

실습 환경은 구글 코랩입니다.

 

1. 파이썬을 활용한 엑셀 자동화: 데이터 클리닝 & 전처리 + 분석(총 매출, 평균 매출, 월별 매출추이) + 시각화(매출 트렌드 예측, 고객 세그먼트 분석)

 

1.1 데이터 생성 

실제 비지니스 데이터와 유사한 예제 데이터를 생성해줍니다.

import pandas as pd

import numpy as np

import random

# 날짜 범위 설정

dates = pd.date_range(start="2023-01-01", periods=365)

# 랜덤 고객 ID 생성

customer_ids = [f"C{str(i).zfill(4)}" for i in range(1, 501)]

# 지역 및 카테고리 설정

regions = ["서울", "부산", "대구", "대전", "광주"]

categories = ["전자제품", "의류", "식품", "화장품", "가구"]

customer_levels = ["일반", "프리미엄", "VIP"]

# 데이터 생성

sales_data = []

for date in dates:

    for _ in range(random.randint(50, 100)):  # 하루 50~100건 거래 발생

        customer = random.choice(customer_ids)

        region = random.choice(regions)

        category = random.choice(categories)

        sales = random.randint(30000, 500000)  # 매출 3만~50만

        quantity = random.randint(1, 10)  # 수량 1~10개

        discount = round(random.uniform(0.05, 0.3), 2)  # 할인율 5~30%

        level = random.choice(customer_levels)

        sales_data.append([date, customer, region, category, sales, quantity, discount, level])

df = pd.DataFrame(sales_data, columns=["날짜", "고객 ID", "지역", "상품 카테고리", "매출", "수량", "할인율", "고객 등급"])

df.to_excel("business_sales_data.xlsx", index=False)

print("📂 business_sales_data.xlsx 생성 완료!")

 

 

 

 

1.2 데이터 클리닝

생성된 데이터에서 매출이 0 이하이거나 할인율이 50%이상인 경우의 데이터를 이상치로 가정하고 이를 제거해보겠습니다.

 

# 엑셀 데이터 로드

df = pd.read_excel("business_sales_data.xlsx")

# 이상치 제거 (매출이 0 이하이거나 할인율이 50% 이상인 경우)

df = df[(df["매출"] > 0) & (df["할인율"] <= 0.5)]

# 고객 ID 및 지역 정리

df.dropna(subset=["고객 ID", "지역"], inplace=True)

# 데이터 타입 변환

df["날짜"] = pd.to_datetime(df["날짜"])

# 결과 저장

df.to_excel("cleaned_business_sales.xlsx", index=False)

print("📂 cleaned_business_sales.xlsx 저장 완료!")

 

 

1.3 분석(총 매출, 평균 매출, 월별 매출추이)

 

이제 분석을 해보겠습니다.

 

고객별 총 매출과 평균 매출을 알아보겠습니다. 그리고 VIP인 고객(가중 매출이 높은 고객) 10명에 대한 결과를 출력해보겠습니다.

 

# 고객별 총매출 & 평균 매출

customer_sales = df.groupby("고객 ID").agg(

    총매출=("매출", "sum"),

    평균매출=("매출", "mean"),

    구매횟수=("고객 ID", "count")

).sort_values("총매출", ascending=False)

# VIP 고객 10명 추출

top_customers = customer_sales.head(10)

top_customers

 

 

이제 월별 매출 추이를 분석해보겠습니다. 월별로 각 상품 카테고리별 매출 추이입니다.

 

# 월별 매출 추이 분석

df["월"] = df["날짜"].dt.to_period("M")

category_growth = df.groupby(["월", "상품 카테고리"])["매출"].sum().unstack()

category_growth

 

 

 

1.4 시각화(매출 트렌드 예측, 고객 세그먼트 분석)

 

이번에는 주어진 데이터를 가지고 고급 시각화를 해보고자 합니다.

 

1) 매출 트렌드 예측 

 

prophet 라이브러리를 활용해서 매출 트렌드를 예측해보겠습니다.

 

먼저 라이브러리를 설치해주고

!pip install prophet

 

코랩 환경에서 한글 폰트를 활용해 시각화하기 위해서 아래 라이브러리를 설치해줍니다. 폰트는 naum 체입니다.

!apt-get install -y fonts-nanum

!fc-cache -fv

!rm -rf ~/.cache/matplotlib

 

그리고 해당 폰트를 사용할 수 있게 설정해줍니다.

import matplotlib.pyplot as plt

from matplotlib import font_manager

# 나눔 폰트 경로 확인

font_path = "/usr/share/fonts/truetype/nanum/NanumGothic.ttf"

# 폰트 설정

font_manager.fontManager.addfont(font_path)

plt.rc('font', family='NanumGothic')  # 나눔고딕 설정

plt.rcParams['axes.unicode_minus'] = False  # 마이너스 기호 깨짐 방지

 

 

자 이제 Prophet 함수를 활용해서 데이터를 분석해보겠습니다.

 

간단하게 Prophet 라이브러리를 소개해드리면 

 

"Prophet"은 Facebook이 개발한 시계열 예측 라이브러리로, 트렌드, 계절성, 공휴일 효과를 반영한 예측 모델을 쉽게 만들 수 있습니다.

핵심 기능
✅ 트렌드(Trend) 모델링: 장기적인 성장 또는 감소 패턴을 학습
✅ 계절성(Seasonality) 자동 감지: 주간, 월간, 연간 패턴을 파악
✅ 이상치 및 변동성 처리: 급격한 변화(쇼크 이벤트)를 반영
✅ 공휴일 효과 포함 가능: 특정 이벤트(예: 블랙 프라이데이, 명절)가 매출에 미치는 영향 예측
✅ 불완전한 데이터 처리 가능: 결측값이나 불규칙한 시간 간격 데이터 지원

 

from prophet import Prophet

# Prophet용 데이터 변환

prophet_df = df.groupby("날짜")["매출"].sum().reset_index()

prophet_df.columns = ["ds", "y"]

# 모델 학습

model = Prophet()

model.fit(prophet_df)

# 미래 3개월 예측

future = model.make_future_dataframe(periods=90)

forecast = model.predict(future)

# 예측 결과 시각화

import matplotlib.pyplot as plt

fig = model.plot(forecast)

plt.title("3개월 매출 예측")

plt.show()

 

아래는 결과를 시각화 한것입니다.

 

데이터가 랜덤하게 생성되다보니 주기성이 너무 뚜렷했고, 3개월 후의 예측 (24년 1~3월)도 기존과 유사한 트렌드를 따라 갈 것으로 보고 있네요 

 

 

 

이번에는 K-mean 클러스터링을 통해 총 매출과 평균 매출을 기반으로 고객들을 클러스터링 해보겠습니다.

 

from sklearn.cluster import KMeans

import seaborn as sns

# 고객 클러스터링을 위한 데이터 준비

X = customer_sales[["총매출", "평균매출", "구매횟수"]]

kmeans = KMeans(n_clusters=3)

customer_sales["클러스터"] = kmeans.fit_predict(X)

# 클러스터 시각화

plt.figure(figsize=(8, 6))

sns.scatterplot(x="총매출", y="평균매출", hue="클러스터", data=customer_sales, palette="viridis")

plt.title("고객 세그먼트 분석 (K-Means)")

plt.show()

이때 활용한 클러스터의 개수는 3개 입니다.