[SQL 입문반 33기] 4주차 복습(Python ver)

데이터리안 SQL 데이터 분석 캠프 입문반 4주차 복습

4주차 학습 내용을 Python으로 복습해봤습니다.

 

0. 데이터 설명

캐글의 타이타닉 데이터 + 수제작한 job 데이터

타이타닉 데이터

변수명	정의
PassengerId	승객 번호
Survived	생존여부
Pclass	티켓 등급
Name	이름
Sex	성별
Age	연령
SibSp	승선 중인 형제나 배우자의 수
Parch	승선 중인 부모나 자녀의 수
Ticket	티켓 번호
Fare	티켓 요금
Cabin	방 번호
Embarked	승선한 항구의 이름

 

job 데이터

변수명	정의
id	승객 번호 중 일부
job	직업(개발자, 경찰관, 소방관 등)
parents_id	부모의 승객 번호

데이터: https://drive.google.com/file/d/1fZwxfJxF_TXJrA7ilV9sTwc4OPB8hAKp/view?usp=sharing

job.csv

 

 

데이콘의 이커머스 데이터

onlinesales 데이터

변수명	정의
customer_id	고객 고유 ID
order_id	거래 고유 ID
date	거래가 이루어진 날짜
product_id	제품 고유 ID
category	제품이 포함된 카테고리
quantity	주문한 품목 수
avg_cost	수량 1개당 가격 (단위: 달러) 동일 상품이어도 세부 옵션에 따라 가격이 다를 수 있음
shipping_fee	배송비용 (단위: 달러)
coupon_status	할인쿠폰 적용 상태

 

customers 데이터

변수명	정의
customer_id	고객 고유 ID
sex	고객 성별
local	고객지역
period	가입기간 (단위: 월)

 

데이터 출처: https://dacon.io/competitions/official/236222/data

이커머스 고객 세분화 분석 아이디어 경진대회 - DACON

 

해당 복습에서는 그래프를 그리기 위해서 아래 코드를 실행하고 다시 처음부터 코드를 실행해야 합니다.

# 한글 폰트 깨지지 않도록 하기
!sudo apt-get install -y fonts-nanum
!sudo fc-cache -fv
!rm ~/.cache/matplotlib -rf

# 이 코드 실행하고 '세션 다시 시작' 실행하기
import matplotlib.pyplot as plt
plt.rc("font", family="NanumGothic") # 라이브러리 불러오기와 함께 한번만 실행
plt.rcParams['axes.unicode_minus'] =False

 

1. 데이터 연결하기2(LEFT JOIN, RIGHT JOIN)

• LEFT JOIN

job 데이터 상위 5개를 추출하세요.

job.head()

 

 

titanic_train 데이터의 상위 5개 를 추출하세요.

titanic_train.head()

 

 

Q. 승객과 관련된 모든 데이터를 추출하세요.

combined_df = pd.merge(titanic_train, job, left_on = 'PassengerId', right_on = 'id', how = 'left')
combined_df.head()

 

 

Q. 직업이 기재되어 있는 사람들만 추출하세요.

df_job = combined_df[combined_df['id'].isnull() == False ].reset_index(drop = True)
df_job.head()

 

 

Q. 성인(18세이상)이면서 직업이 기재되어 있는 사람들의 id, Age, job 컬럼만 추출하세요.

df_adult = df_job[df_job['Age'] >= 18].reset_index(drop = True)
df_adult.loc[:4, ['id', 'Age', 'job']]

 

 

• RIGHT JOIN

Q.  승객과 관련된 모든 데이터를 추출하세요.

combined_df1 = pd.merge(job, titanic_train, left_on = 'id', right_on = 'PassengerId', how = 'right')
combined_df1.head()

 

• SELF JOIN

Q. job 테이블에서 자식과 부모가 같은 직업인 데이터만 추출하세요.

job_self_joined = pd.merge(job, job, left_on = 'parents_id', right_on = 'id', how = 'inner').reset_index(drop = True)
job_self_joined.head()

 

 

2. 매출 분석 

Q. onlinesales 테이블의 일별 매출액 계산하세요.

# sales라는 매출 컬럼 생성
onlinesales['sales'] = onlinesales['quantity'] * onlinesales['avg_cost']

# 일별 매출액 계산
revenue_daily = onlinesales.groupby('date')['sales'].agg('sum').reset_index()
revenue_daily = revenue_daily.rename(columns = {'sales':'revenue_daily'})
revenue_daily.head()

 

 

Q. onlinesales 테이블의 일별 매출액, PU, ARPPU 계산하세요.

ARPPU = revenue_daily.copy()
ARPPU['PU'] = onlinesales.groupby('date')['customer_id'].agg('nunique').reset_index()['customer_id']
ARPPU

 

 

ARPPU['ARPPU'] = round(ARPPU['revenue_daily'] / ARPPU['PU'], 2)
ARPPU.head()

 

이 ARPPU 데이터프레임을 파이썬 시각화 라이브러리로 선 그래프를 그려겠습니다.

# date 컬럼 날짜 형식으로 변환
ARPPU['date'] = pd.to_datetime(ARPPU['date'])

# matplotlib 라이브러리 사용
import matplotlib.dates as mdates

# 컬럼 리스트 정의
columns_to_plot = ['revenue_daily', 'PU', 'ARPPU']

# 반복문을 통해 각각 그래프 그리기
for column in columns_to_plot:
    plt.figure(figsize=(12, 6))  # 그래프 크기 조정
    plt.plot(ARPPU['date'], ARPPU[column], label=column)
    
    # 추세선 추가
    z = np.polyfit(mdates.date2num(ARPPU['date']), ARPPU[column], 1)  # 1차 다항식 (선형 회귀)
    p = np.poly1d(z)
    plt.plot(ARPPU['date'], p(mdates.date2num(ARPPU['date'])), "r--", label='Trend Line')  # 추세선
    
    # 그래프 제목 및 레이블 설정
    plt.title(f'{column}')
    plt.xlabel('Date')

    # x축 날짜 포맷 설정
    plt.xticks(rotation=45)  # x축 레이블 회전

    # 원하는 월로 x축 설정
    plt.gca().xaxis.set_major_locator(mdates.MonthLocator(bymonthday=1, interval=1))  # 매달 1일
    plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))  # 연도와 월 포맷

    # x축 레이블 추가
    plt.xlim([ARPPU['date'].min(), ARPPU['date'].max()])  # x축 범위 설정

plt.show()

 

# seaborn 라이브러리 사용
import seaborn as sns

# 그래프 크기 조정
plt.figure(figsize=(12, 18))  # 전체 그래프 크기 설정

for i, column in enumerate(columns_to_plot, start=1):
    plt.subplot(3, 1, i)  # 3행 1열의 i번째 서브플롯 생성
    
    # Seaborn으로 선 그래프 그리기
    sns.lineplot(data=ARPPU, x='date', y=column, label=column)
    
    # 추세선 추가
    z = np.polyfit(mdates.date2num(ARPPU['date']), ARPPU[column], 1)  # 1차 다항식 (선형 회귀)
    p = np.poly1d(z)
    plt.plot(ARPPU['date'], p(mdates.date2num(ARPPU['date'])), "r--", label='Trend Line')  # 추세선

    # 그래프 제목 및 레이블 설정
    plt.title(f'{column}')
    plt.xlabel('Date')

    # x축 날짜 포맷 설정
    plt.xticks(rotation=45)  # x축 레이블 회전
    plt.gca().xaxis.set_major_locator(mdates.MonthLocator(bymonthday=1, interval=1))  # 매달 1일
    plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))  # 연도와 월 포맷

    # x축 레이블 추가
    plt.xlim([ARPPU['date'].min(), ARPPU['date'].max()])  # x축 범위 설정
    plt.legend()  # 범례 추가

# 그래프 보여주기
plt.tight_layout()  # 레이아웃 조정
plt.show()

 

 

Q. 일별, 고객별 주문 금액 및 주문수를 추출하세요.

daily_customer_sales = onlinesales.groupby(['date', 'customer_id']).agg(
    revenue_per_customer = ('sales', 'sum'),
    order_cnts = ('order_id', 'nunique')
).reset_index()

# 결과 확인
daily_customer_sales.head(10)

 

 

• 지역을 기준으로 고객 세분화 해보기

Q. 지역별 매출액, PU, 1인당 매출액을 1인당 매출액을 순위별로 추출하세요.

# 1단계: customer 데이터와 JOIN
local = pd.merge(onlinesales, customer, on = 'customer_id', how = 'inner')

# 2단계: 지역별로 매출, PU 구하기
df_l = local.groupby('local').agg(
    local_revenue = ('sales', 'sum'),
    PU = ('customer_id', 'nunique'),
).reset_index()
df_l

 

# 3단계: LRPPU(Local Revenue Per Paying User) 계산해서 추가하기
df_l['LRPPU'] = df_l['local_revenue'] / df_l['PU']
df_l.sort_values('LRPPU', ascending = False)

 

 

Q. Chicago 지역에서 제품 카테고리별 주문수, 매출액을 추출하세요.(매액 내림차순)

# 1단계: Chicago 지역 데이터만 따로 추출
df_chicago = local[local['local'] == 'Chicago'].reset_index(drop = True)
df_chicago

 

df_chicago = df_chicago.groupby('category').agg(
    order_cnts = ('order_id', 'nunique'),
    revenue_category = ('sales', 'sum')
).reset_index()
df_chicago.sort_values('revenue_category', ascending = False).reset_index()

이렇게 Python으로 4주차 학습내용을 복습해봤습니다. 

실전반 전까지 이커머스 데이터를 가지고 분석 프로젝트를 진행하려고 합니다.

다음 글은 RFM 기준 잡는 방법과 이커머스 데이터 EDA가 될 것 같습니다.

 

 

전체 소스코드가 궁금하다면 아래 깃허브 주소를 참고해주세요.

https://github.com/HSYhrae/TIL/tree/master/MySQL/Datarian

TIL/MySQL/Datarian at master · HSYhrae/TIL

 

 

 

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 입문반' 을 수강하며 작성한 내용입니다.