프로젝트 개요

• 팀 구성 : 3명
• 일정 : 10/17 ~ 10/27
• 활용 데이터 셋 : Brazilian E-Commerce Public Dataset by Olist
  캐글-Olist Dataset
• 활용 언어 : SQL, Python, 시각화 툴 : Python, Excel, Power BI

데이터 셋 소개

• 활용 데이터 셋은 Kaggle에 있는 Olist E-Commerce 데이터 셋으로 브라질 전자상거래 데이터다. 2016년부터 2018년까지 마켓플레이스에서 이루어진 약 10만 건의 주문 정보가 포함되어 있으며, 각각의 데이터 셋는 다음과 같다.

  

데이터 전처리

▶ olist_orders_dataset 테이블
1-1. order_status = 'canceled' 행 삭제
1-2. 판매 사이클에 맞지 않는 데이터 삭제

SELECT * FROM olist_orders_dataset WHERE order_status = 'canceled';
DELECT FROM olist_orders_dataset WHERE order_status = 'canceled';

▶ olist_sellers_dataset 테이블
2. seller_city가 seller_state에 포함되지 않은 이슈 발생

SELECT SD.seller_id, SD.seller_zip_code_prefix, GD.geolocation_state
FROM olist_sellers_dataset SD
JOIN 
(SELECT 
	DISTINCT geolocation_zip_code_prefix AS zip_code, 
    geolocation_state FROM olist_geolocation_dataset) GD 
ON SD.seller_zip_code_prefix = GD.zip_code

-> 이슈 해결

▶ 모든 데이터 조인

# 데이터 합치기
olist_df = pd.merge(orders_df, order_payments_df, on = 'order_id')
olist_df = olist_df.merge(customer_df, on = 'customer_id')
olist_df = olist_df.merge(order_items_df, on = 'order_id')
olist_df = olist_df.merge(products_df, on = 'product_id')
olist_df = olist_df.merge(category_name_df, on = 'product_category_name')
olist_df = olist_df.merge(order_reviews_df, on = 'order_id')
olist_df = olist_df.merge(sellers_df, on = 'seller_id')

olist_df.info()

# 중복 행 제거
olist_df.duplicated(subset = 'order_id').value_counts()
olist_df = olist_df[~olist_df.duplicated(['order_id'])]

# 인덱스 초기화
olist_df = olist_df.reset_index()
olist_df = olist_df.drop(columns = {'index'})

▶ 날짜 컬럼 형변환후 연, 월, 요일별로 분해

# datetime 형식으로 구매 일자 변경
date_time = olist_df['order_purchase_timestamp'].str.split()

date_list = []
time_list = []
for x in range(date_time.shape[0]) :
    date_list.append(date_time[x][0])
    time_list.append(date_time[x][1])
    
olist_df['purchase_date'], olist_df['purchase_time'] = date_list, time_list
olist_df = olist_df.drop(columns = {'order_purchase_timestamp'})

olist_df['purchase_date'] = pd.to_datetime(olist_df['purchase_date'])

# 구매 일자를 연, 월, 요일별로 분해
olist_df['year'] = olist_df['purchase_date'].dt.year
olist_df['month'] = olist_df['purchase_date'].dt.month
olist_df['day_of_week'] = olist_df['purchase_date'].dt.day_name()

# olist_df['purchase_time']에 시간 값만 저장
olist_df['purchase_time'] = olist_df['purchase_time'].str.slice(0, 2)

▶ 브라질의 주 이름의 한글 명을 컬럼으로 따로 저장(시각화에 용이하기 위해)

# 주 이름을 한글로 바꾸는 함수
def get_kor_state(state) :
    if state == 'AC' :
        state_kor_name = '아크리주'
    elif state == 'AL' :
        state_kor_name = '알라고아스주'
    elif state == 'AP' :
        state_kor_name = '아마파주'
    elif state == 'AM' :
        state_kor_name = '아마조나스주'
    elif state == 'BA' :
        state_kor_name = '바이아주'
    elif state == 'CE' :
        state_kor_name = '세아라주'
    elif state == 'DF' :
        state_kor_name = '연방구'
    elif state == 'ES' :
        state_kor_name = '이스피리투산투주'
    elif state == 'GO' :
        state_kor_name = '고이아스주'
    elif state == 'MA' :
        state_kor_name = '마라냥주'
    elif state == 'MT' :
        state_kor_name = '마투그로수주'
    elif state == 'MG' :
        state_kor_name = '미나스제라이스주'
    elif state == 'PA' :
        state_kor_name = '파라주'
    elif state == 'PB' :
        state_kor_name = '파라이바주'
    elif state == 'PR' :
        state_kor_name = '파라나주'
    elif state == 'PE' :
        state_kor_name = '페르남부쿠주'
    elif state == 'PI' :
        state_kor_name = '피아우이주'
    elif state == 'RJ' :
        state_kor_name = '리우데자네이루주'
    elif state == 'RN' :
        state_kor_name = '히우그란지두노르치주'
    elif state == 'RS' :
        state_kor_name = '히우그란지두술주'
    elif state == 'RO' :
        state_kor_name = '혼도니아주'
    elif state == 'RR' :
        state_kor_name = '호라이마주'
    elif state == 'SC' :
        state_kor_name = '산타카타리나주'
    elif state == 'SP' :
        state_kor_name = '상파울루주'
    elif state == 'SE' :
        state_kor_name = '세르지피주'
    elif state == 'MS' :
        state_kor_name = '마투그로수두술'
    else :
        state_kor_name = '토칸칭스주'
    return state_kor_name
    
# 주의 한글 이름 컬럼 추가
for index, row in olist_df.iterrows():
    state = row['customer_state']
    kor_name = get_kor_state(state)
    olist_df.at[index,'kor_state'] = kor_name

▶ 주의 위치에 따라 지역별로 나누어 새로운 컬럼에 저장

# state를 입력받으면 지역을 return하는 함수
def get_region(state) :
    if (state == 'SP' or state == 'MG' or state == 'ES' or state == 'RJ') : 
        region = '남동부'
    elif (state == 'PR' or state == 'SC' or state == 'RS') : 
        region = '남부'
    elif (state == 'BA' or state == 'PE' or state == 'CE' or state == 'RN' or state == 'PI' or state == 'MA' or state == 'SE' or state == 'AL' or state == 'PB') : 
        region = '북동부'
    elif (state == 'GO' or state == 'MT' or state == 'MS' or state == 'DF') : 
        region = '중서부'
    else : 
        region = '북부'
    return region
    
# 판매자 거주 지역 컬럼 추가
for index, row in olist_df.iterrows():
    state = row['seller_state']
    region = get_region(state)
    olist_df.at[index,'seller_region'] = region

# 구매자 거주 지역 컬럼 추가
for index, row in olist_df.iterrows():
    state = row['customer_state']
    region = get_region(state)
    olist_df.at[index,'customer_region'] = region

EDA

주별 고객 분포

customer_state_df = olist_df['customer_state']
customer_state_df = pd.DataFrame(customer_state_df.value_counts())
customer_state_df = customer_state_df.reset_index().rename(columns = {'index' : 'state'})

px.pie(customer_state_df, values='customer_state', names='state', 
       color_discrete_sequence=px.colors.qualitative.Pastel1 + px.colors.qualitative.Pastel2)

• 고객이 가장 많이 거주하고 있는 주로는 상파울루주로 고객의 42%가 상파울루주에 거주하고 있는 것을 알 수 있다. 다음으로는 리우데자네이루주, 미나스제라이스주, 히우그란지두술주 순으로 이어지며 이는 주별 인구수와 비슷한 결과를 나타내고 있다.

카테고리 별 주문 건수

category_df = pd.DataFrame(olist_df['product_category_name_english'].value_counts())
category_df = category_df.reset_index().rename(columns = {'index' : 'category', 'product_category_name_english' : 'count'})
category_df

px.bar(category_df, x="category", y='count',
       labels={"category":"카테고리","count":"총 주문건수"}, 
       title='카테고리별 주문 건수', color = px.colors.qualitative.Pastel1 + px.colors.qualitative.Pastel2 + px.colors.qualitative.Pastel + px.colors.qualitative.Light24 + px.colors.qualitative.Safe + px.colors.qualitative.Set2, 
       color_discrete_map='identity')

• 다음 그래프에서는 카테고리별 주문 건수를 확인할 수 있다. 주문 건수가 가장 많은 상위 5개의 카테고리로는 'bed_bath_table', 'health_beauty', 'sports_leisure', 'computers_accessories', 'furniture_decor' 순이고, 주문 건수가 가장 적은 하위 5개의 카테고리는 'home_comfort_2', 'la_cuisine', 'cds_dvds_musicals', 'fashion_childrens_clothes', 'security_and_services' 이다.

매출액 추이

date_purchase_df = olist_df[['payment_value', 'purchase_date', 'year', 'month', 'day_of_week']]
date_purchase_df['purchase_date'].describe()

• 취소된 주문 건수를 제외하면 2016년 9월 4일부터 2018년 9월 3일 까지의 데이터인것을 알 수 있다.

date_purchase_pt = pd.pivot_table(data = date_purchase_df, 
                                   index = 'purchase_date', 
                                   values = 'payment_value', 
                                   aggfunc = 'sum').reset_index()
date_purchase_pt

• 피벗테이블의 aggfunc을 합계로 설정하여 일별 매출액을 알아보고자 한다.

px.line(date_purchase_pt, x = 'purchase_date', y = 'payment_value',
        labels={"purchase_date":"날짜","payment_value":"매출액"},
        title='매출액 추이')

• 매출액 추이를 나타낸 그래프로, 서비스가 오픈한 후부터 2017년 초까지는 매출액이 저조하다가 2017년 2~3월 부터 매출액이 증가하기 시작하는 것을 확인할 수 있다. 다음 그래프에서 유독 매출액이 비정상적으로 상승한 날짜를 살펴볼 수 있는데 이는 2017년 11월 24일로 이 날은 블랙프라이데이라고 한다.

연도별 매출액

# 연도별 매출액의 합계 피벗테이블 생성
year_purchase_pt = pd.pivot_table(data = date_purchase_df, 
                                  index = 'year',
                                  values = 'payment_value',
                                  aggfunc = 'sum').reset_index()
# 연도별 주문 건수 피벗테이블 생성
year_purchase_pt2 = pd.pivot_table(data = date_purchase_df, 
                                  index = 'year',
                                  values = 'payment_value',
                                  aggfunc = 'count').reset_index()
year_purchase_pt = pd.merge(year_purchase_pt, year_purchase_pt2, on = 'year')
year_purchase_pt = year_purchase_pt.rename(columns = {'payment_value_x' : 'value_sum', 'payment_value_y' : 'value_count'})
year_purchase_pt

# 막대그래프 = 연도별 주문 건수, 선그래프 = 연도별 매출액
matplotlib.rc_file_defaults()
ax1 = sns.set_style(style=None, rc=None )

fig, ax1 = plt.subplots(figsize=(12,6))

sns.lineplot(data = year_purchase_pt['value_sum'], marker='o', sort = False, ax=ax1)
ax2 = ax1.twinx()

sns.barplot(year_purchase_pt, x='year', y='value_count', alpha=0.5, ax=ax2)

• 해당 그래프는 연도별 매출액과 주문건수를 시각화한 그래프로 2016년부터 2018년까지 시간이 지날수록 매출액과 주문건수가 증가했음을 확인할 수 있다.

월별 매출액

# 월별 매출액의 합계 피벗테이블 생성
month_purchase_pt = pd.pivot_table(data = date_purchase_df,
                                   index = 'month',
                                   values = 'payment_value',
                                   aggfunc = 'sum').reset_index()
# 월별 주문 건수 피벗테이블 생성
month_purchase_pt2 = pd.pivot_table(data = date_purchase_df, 
                                  index = 'month',
                                  values = 'payment_value',
                                  aggfunc = 'count').reset_index()
month_purchase_pt = pd.merge(month_purchase_pt, month_purchase_pt2, on = 'month')
month_purchase_pt = month_purchase_pt.rename(columns = {'payment_value_x' : 'value_sum', 'payment_value_y' : 'value_count'})
month_purchase_pt

# 막대그래프 = 월별 주문 건수, 선그래프 = 월별 매출액
matplotlib.rc_file_defaults()
ax1 = sns.set_style(style=None, rc=None )

fig, ax1 = plt.subplots(figsize=(12,6))

sns.lineplot(data = month_purchase_pt['value_sum'], marker='o', sort = False, ax=ax1)
ax2 = ax1.twinx()

sns.barplot(month_purchase_pt, x='month', y='value_count', alpha=0.5, ax=ax2)

• 해당 그래프는 월별 매출액과 주문 건수를 시각화한 그래프로 9 ~ 12월 데이터와 1 ~ 2월 데이터가 서비스를 오픈한지 얼마 되지않았던 2016년 데이터와 2017년 데이터 밖에 존재하지 않아 다른 달보다 매출액과 주문건수가 적게 표현된 것을 확인할 수 있다. 이를 제외하고 5월과 8월에 매출액과 주문 건수가 다른 달에 비해 높은 것을 확인할 수 있다.

요일별 매출액

# 요일별 매출액의 합계 피벗테이블 생성
dayofweek_purchase_pt = pd.pivot_table(data = date_purchase_df,
                                       index = 'day_of_week',
                                       values = 'payment_value',
                                       aggfunc = 'sum').reset_index()
# 요일별 주문 건수 피벗테이블 생성
dayofweek_purchase_pt2 = pd.pivot_table(data = date_purchase_df, 
                                  index = 'day_of_week',
                                  values = 'payment_value',
                                  aggfunc = 'count').reset_index()
dayofweek_purchase_pt = pd.merge(dayofweek_purchase_pt, dayofweek_purchase_pt2, on = 'day_of_week')
dayofweek_purchase_pt = dayofweek_purchase_pt.rename(columns = {'payment_value_x' : 'value_sum', 'payment_value_y' : 'value_count'})

# 인덱스 재배열
dayofweek_purchase_pt = dayofweek_purchase_pt.reindex([3, 1, 5, 6, 4, 0, 2])
dayofweek_purchase_pt = dayofweek_purchase_pt.reset_index()
dayofweek_purchase_pt = dayofweek_purchase_pt.drop(columns = {'index'})
dayofweek_purchase_pt

# 막대그래프 = 요일별 주문 건수, 선그래프 = 요일별 매출액
matplotlib.rc_file_defaults()
ax1 = sns.set_style(style=None, rc=None )

fig, ax1 = plt.subplots(figsize=(12,6))

sns.lineplot(data = dayofweek_purchase_pt['value_sum'], marker='o', sort = False, ax=ax1)
ax2 = ax1.twinx()

sns.barplot(dayofweek_purchase_pt, x='day_of_week', y='value_count', alpha=0.5, ax=ax2)

시간대별 매출액

time_purchase_df = olist_df[['payment_value', 'purchase_time']]

# 시간별 매출액의 합계 피벗테이블 생성
time_purchase_pt = pd.pivot_table(data = time_purchase_df, 
                                   index = 'purchase_time', 
                                   values = 'payment_value', 
                                   aggfunc = 'sum').reset_index()
# 시간별 주문 건수 피벗테이블 생성
time_purchase_pt2 = pd.pivot_table(data = time_purchase_df, 
                                  index = 'purchase_time',
                                  values = 'payment_value',
                                  aggfunc = 'count').reset_index()
time_purchase_pt = pd.merge(time_purchase_pt, time_purchase_pt2, on = 'purchase_time')
time_purchase_pt = time_purchase_pt.rename(columns = {'payment_value_x' : 'value_sum', 'payment_value_y' : 'value_count'})
time_purchase_pt

# 막대그래프 = 시간대별 주문 건수, 선그래프 = 시간대별 매출액
matplotlib.rc_file_defaults()
ax1 = sns.set_style(style=None, rc=None )

fig, ax1 = plt.subplots(figsize=(12,6))

sns.lineplot(data = time_purchase_pt['value_sum'], marker='o', sort = False, ax=ax1)
ax2 = ax1.twinx()

sns.barplot(time_purchase_pt, x='purchase_time', y='value_count', alpha=0.5, ax=ax2)

• 다음은 시간대별 매출액과 주문건수에 대한 그래프로 사람들이 잠에 드는 오후 11시 ~ 오전 8시까지는 매출액과 주문 건수가 적은 것을 확인할 수 있고, 오전 10시 ~ 오후 4시 시간대에 가장 쇼핑을 많이 하는 것을 확인할 수 있다.

카테고리별 평균 배송 시간

category_delivery_df = olist_df[['product_category_name_english', 'purchase_date', 'order_delivered_customer_date']].dropna(axis = 0).reset_index().drop(columns = {'index'})

# 배송된 날짜만 추출
delivered_date = category_delivery_df['order_delivered_customer_date'].str.split()

delivered_date_list = []
for x in range(delivered_date.shape[0]) :
    delivered_date_list.append(delivered_date[x][0])

# 컬럼에 저장   
category_delivery_df['order_delivered_customer_date'] = pd.to_datetime(delivered_date_list)

# 주문 날짜와 배송 도착 날짜의 차이를 계산하여 컬럼에 저장 
category_delivery_df['delivered_date'] = category_delivery_df['order_delivered_customer_date'] - category_delivery_df['purchase_date']
category_delivery_df = category_delivery_df.drop(columns = {'order_delivered_customer_date', 'purchase_date'})

# 카테고리별 배송까지 걸린 날짜의 평균 피벗테이블 생성
category_delivery_pt = pd.pivot_table(data = category_delivery_df, 
                                   index = 'product_category_name_english', 
                                   values = 'delivered_date', 
                                   aggfunc = 'mean').reset_index()
                                   
# 배송까지 걸린 날짜를 기준으로 오름차순으로 정렬                                   
category_delivery_pt = category_delivery_pt.sort_values(by = 'delivered_date', ascending=True)
category_delivery_pt['delivered_date'] = category_delivery_pt['delivered_date'].dt.days

px.line(category_delivery_pt, 
        x = 'product_category_name_english', 
        y = 'delivered_date',
        labels={'product_category_name_english' : '카테고리 명', 'delivered_date' : '평균 배송 시간'}, 
        title = '카테고리별 평균 배송 시간')

• 다음 그래프는 카테고리별 평균 배송시간을 시각화한 것으로 가장 배송시간이 빠른 카테고리는 'arts_and_craftmanship' 이며, 'la_cuisine', 'books_imported', 'party_supplies', 'fashion_childrens_clothes' 가 뒤를 잇는 것을 확인할 수 있다. 반대로 가장 배송시간이 느린 카테고리는 'office_furniture'이며 평균 배송시간은 20일이다. 뒤를 이어 'fashion_shoes', 'home_comfort_2', 'christmas_supplies', 'security_and_services' 등이 있다.

배송시간별 평균 평점

delivery_score_df = olist_df[['order_delivered_customer_date', 'purchase_date', 'review_score']].dropna(axis = 0).reset_index()
delivery_score_df = delivery_score_df.drop(columns = {'index'})

# 배송된 날짜만 추출
delivered_date = delivery_score_df['order_delivered_customer_date'].str.split()

# 리스트에 저장
delivered_date_list = []
for x in range(delivered_date.shape[0]) :
    delivered_date_list.append(delivered_date[x][0])

# datetime 형식으로 컬럼에 저장
delivery_score_df['order_delivered_customer_date'] = pd.to_datetime(delivered_date_list)

# 주문 시간과 배송 도착 날짜의 차이를 컬럼에 저장하고 나머지 컬럼 삭제
delivery_score_df['delivered_date'] = delivery_score_df['order_delivered_customer_date'] - delivery_score_df['purchase_date']
delivery_score_df = delivery_score_df.drop(columns = {'order_delivered_customer_date', 'purchase_date'})
delivery_score_df['delivered_date'] = delivery_score_df['delivered_date'].dt.days
delivery_score_df

# 배송까지 걸린 날짜 범주화
delivery_dic = {1 : '0~5days', 2 : '5~10days', 3 : '10~15days', 4 : '15~20days', 5 : '20days~'}

delivery_legend_list = []
for date in delivery_score_df['delivered_date'] :
    if 0 <= date < 5 :
        delivery_legend_list.append(1)
    elif 5 <= date < 10 :
        delivery_legend_list.append(2)
    elif 10 <= date < 15 :
        delivery_legend_list.append(3)
    elif 15 <= date < 20 :
        delivery_legend_list.append(4)
    else :
        delivery_legend_list.append(5)
        
delivery_score_df['delivery_legend'] = delivery_legend_list

# 날짜 범주화당 평균 리뷰 점수를 구하는 피벗테이블 생성
delivery_score_pt = pd.pivot_table(data = delivery_score_df, 
                                   index = 'delivery_legend', 
                                   values = 'review_score', 
                                   aggfunc = 'mean').reset_index()

# 데이터를 delivery_dic의 value 값으로 변경
delivery_score_pt['delivery_legend'] = delivery_dic.values()
delivery_score_pt

px.bar(delivery_score_pt, x = 'delivery_legend', y = 'review_score', 
       labels = {'delivery_legend' : '배송시간', 'review_score' : '평균 평점'}, 
       title='배송시간별 평균 평점', color=["rgb(239,209,159)", "rgb(255,185,144)", "rgb(255,190,159)", "rgb(255,163,139)", "rgb(255,179,171)"], 
       color_discrete_map="identity")

• 다음 그래프는 배송시간에 따른 평점을 알아본 그래프로 배송 시간이 길수록 고객의 서비스 불만족이 높아지고 부정적인 리뷰를 받을 가능성이 높아진다고 말할 수 있다.

분석

• EDA를 통해 배송 시간이 늘어날 수록 평점이 줄어드는 것을 확인했다. 따라서 '배송 시간을 줄일려면 어떻게 해야될까' 라는 의문점을 시작으로 이번 분석을 시작했다.
• 먼저 배송 시간과 관련된 여러 요인들을 알아보고자 했다.

배송시간이 20일 이상 걸린 구매자와 판매자 사이의 평균 거리 / 배송시간이 20일 미만 걸린 구매자와 판매자 사이의 평균 거리 비교

# 위도와 경도를 통해 거리(단위 : km) 구하는 함수
def haversine_distance(lat1, lon1, lat2, lon2):
    # 지구의 반지름 (평균 반지름은 약 6,371 km)
    R = 6371.0

    # 위도와 경도를 라디안으로 변환
    lat1 = math.radians(lat1)
    lon1 = math.radians(lon1)
    lat2 = math.radians(lat2)
    lon2 = math.radians(lon2)

    # 위도와 경도 간의 차이 계산
    dlat = lat2 - lat1
    dlon = lon2 - lon1

    # Haversine 공식을 사용하여 거리 계산
    a = math.sin(dlat/2)**2 + math.cos(lat1) * math.cos(lat2) * math.sin(dlon/2)**2
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    distance = R * c

    return distance

다음과 같은 함수를 만들어 구매자의 위도, 경도와 판매자의 위도, 경도를 통한 거리를 알아보았다.

px.bar(distance_dif_df, x = '배송 시간', y = '평균 거리(km)', color = '배송 시간')

• 배송 시간이 20일 이상 걸린 집단과 20일 미만 걸린 집단의 평균 거리 차이는 약 500km로 두 배 가까이 차이가 나는 것을 알 수 있다.

지역별 평균 배송비

# 구매자 주와 배송비 컬럼만 추출
state_freight_value_df = olist_df[['customer_state', 'freight_value']]

# 구매자 주별로 평균 배송비 피벗테이블 생성
state_freight_value_pt = pd.pivot_table(data = state_freight_value_df, 
                                  values = 'freight_value', index = 'customer_state', aggfunc='mean')
state_freight_value_pt = state_freight_value_pt.reset_index()

map_freight_value = folium.Map(location=[-15.7801, -47.9292], tiles="cartodbpositron", zoom_start=4)

folium.Choropleth(
        geo_data=geo,
        data=state_freight_value_pt,
        columns=['customer_state', 'freight_value'],
        key_on='id',
        fill_color='PuRd',
        fill_opacity=0.7,
        line_opacity=0.5,
).add_to(map_freight_value)

map_freight_value

• 평균 배송비는 남부와 남동부 주변 지역에서는 적게 나타나며 북부와 북동부, 중서부에서는 높게 나타나는데 이는 판매자의 대부분이 남부와 남동부에 거주하고 있기 때문이라고 생각했다.

세분화(판매자의 지역별로 주별 평균 배송비 시각화)

• 다음 그래프는 판매자의 위치에 따라 각 주별 평균 배송비를 시각화한 자료로 판매자로 부터 멀어질 수록 배송비가 올라간다는 것을 확인할 수 있다.

# 판매자의 주와 구매자의 주가 같은 것만 추출하기 위해 mask 생성
mask_same = (olist_df['seller_state'] == olist_df['customer_state'])

# 판매자의 주와 구매자의 주가 같은 데이터
cus_sell_same = olist_df[mask_same].reset_index()
# 판매자의 주와 구매자의 주가 다른 데이터
cus_sell_dif = olist_df[~mask_same].reset_index()
cus_sell_same['state'] = '같은 주로 배송'
cus_sell_dif['state'] = '다른 주로 배송'

cus_sell_df = pd.concat([cus_sell_same, cus_sell_dif])
cus_sell_df = cus_sell_df.reset_index().drop(columns = 'index')

px.histogram(cus_sell_df, x = 'freight_value', color = 'state')

• 다음은 같은 주로의 배송비와 다른 주로의 배송비의 분포 차이를 알아본 그래프이다. 판매자와 구매자가 같은 주일때의 배송비는 평균 13.4헤알이고, 다른 주일때의 평균 배송비는 23.6 헤알이다. 대략 2배 가까이 차이가 나는 것을 알 수 있다.

따라서 동일 지역에 판매자가 없을 경우 배송비가 증가하며 그에 따른 고객의 지출 금액이 증가하고 배송 시간이 증가하여 구매자의 서비스 만족도가 낮아질 것이다.

이를 해결하기 위해 지역별 선호 카테고리를 조사하여 선호 카테고리의 판매자가 부족한 경우 판매자를 유입시킬 수 있는 마케팅을 진행할 수 있도록 지역과 선호 카테고리를 분석하고자 했으며, 최적의 물류센터 위치를 분석해 배송 시간을 최소화할 수 있는 방법을 고민했다.

지역별 선호 카테고리

• 남부와 남동부 지역에서는 'bed_bath_table' 카테고리 상품을 모든 주에서 선호하는 것으로 보여지며 'health_beauty', 'sports_leisure' 카테고리 상품 또한 선호하는 것을 알 수 있다.
• 중서부 지역에 경우, 남부 그리고 남동부와 비슷하게 'bed_bath_table’, 'health_beauty', 'sports_leisure' 카테고리 상품을 가장 선호한다
• 북부에 경우에는 'health_beauty', 'computers_accessories', 'sports_leisure' 등의 카테고리 상품을 가장 선호한다.
• 마지막으로 북동부에서도 'bed_bath_table' , 'health_beauty', 'sports_leisure' 의 카테고리 상품을 선호하며 'watches_gifts', 'telephony' 카테고리 상품도 일부 주에 한하여 선호한다.

판매자가 많은 남부와 남동부를 제외하고 나머지 지역에 대한 선호 카테고리의 판매자 수를 같이 알아보고자 했다.

대체로 주문 건수에 비해 판매자수는 낮게 나타나는 것을 확인할 수 있다.

따라서 주문 건수와 판매자 수를 고려했을 때
1. 북동부에서는 health_beauty 카테고리 상품을 취급하는 판매자 유입 마케팅을 진행하는 것이 가장 효과적일 것으로 예상된다.
2. 중서부는 health_beauty 와 sports_leisure 카테고리 상품에 대한 판매자 유입 마케팅을 진행하는 것이 가장 효과적일 것으로 예상된다.
3. 북부에서는 health_beauty 카테고리 상품을 취급하는 판매자 유입 마케팅을 진행하는 것이 가장 효과적일 것으로 예상된다.

물류 센터 위치 분석

• 아이디어 : '배송이 오래 걸린(20일 이상) 구매자와 판매자의 위치 중앙값이 많이 분포해 있는 곳에 물류 센터를 둔다면 배송 시간이 줄어들지 않을까?'

# 특정 컬럼만 추출
geo_seller_customer_df = olist_df[['customer_id', 'seller_id', 'order_approved_at', 'order_delivered_customer_date', 'delivery_date', 'customer_zip_code_prefix', 'seller_zip_code_prefix']]

geo_df = geolocation_df[['geolocation_zip_code_prefix', 'geolocation_lat', 'geolocation_lng']]
geo_df = geo_df.rename(columns = {'geolocation_zip_code_prefix' : 'zip_code_prefix'})

# 중복행 제거
geo_df = geo_df[~geo_df.duplicated(['zip_code_prefix'])]
# 인덱스 초기화
geo_df = geo_df.reset_index().drop(columns = {'index'})

# 배송일이 20일 이상 걸린 값만 추출하기 위해 필터 생성
mask_delivery = (geo_seller_customer_df['delivery_date'] >= 20) == True

# 배송일이 20일 이상 걸린 데이터와 그렇지 않은 데이터 분리
geo_seller_customer_df_20 = geo_seller_customer_df[mask_delivery]
geo_seller_customer_df_0 = geo_seller_customer_df[~mask_delivery]

• 다음과 같이 배송이 20일 이상 걸린 구매자와 판매자 집단과 20일 미만 걸린 집단을 분리했다.

# geo 데이터셋과 join하여 구매자의 위도, 경도 값 불러오기 
geo_seller_customer_df_20 = geo_seller_customer_df_20.merge(geo_df, how = 'inner', on = 'customer_zip_code_prefix')

# geo 데이터셋과 join하여 판매자의 위도, 경도 값 불러오기 
geo_seller_customer_df_20 = geo_seller_customer_df_20.merge(geo_df, how = 'inner', on = 'seller_zip_code_prefix')

# 컬럼명 변경
geo_seller_customer_df_20 = geo_seller_customer_df_20.rename(columns = {'geolocation_lat_x' : 'customer_lat', 'geolocation_lng_x' : 'customer_lng', 
                                                                  'geolocation_lat_y' : 'seller_lat', 'geolocation_lng_y' : 'seller_lng'})

# 구매자와 판매자의 위치 중앙값 계산
geo_seller_customer_df_20['middle_location_lat'] = (geo_seller_customer_df_20['customer_lat'] + geo_seller_customer_df_20['seller_lat']) / 2
geo_seller_customer_df_20['middle_location_lng'] = (geo_seller_customer_df_20['customer_lng'] + geo_seller_customer_df_20['seller_lng']) / 2

• 좌표평면 상의 두 점 (x1, y1), (x2, y2)의 중앙값은 ((x1 + x2) / 2 , (y1 + y2) / 2)이므로 이 점을 고려해 구매자와 판매자의 중앙 위도, 경도 값을 구했다.

import folium
import json

# 지도 생성
map = folium.Map(location=[-15.7801, -47.9292], tiles="cartodbpositron", zoom_start=4)

geo = json.load(open('./brazil_geo.json'))

# FeatureGroups 생성
customer_group = folium.FeatureGroup(name='Customer Locations')
seller_group = folium.FeatureGroup(name='Seller Locations')
middle_location_group = folium.FeatureGroup(name='Middle Locations')
pax_group = folium.FeatureGroup(name='Pax Hubs')

# 구매자 위치 표시
for index, row in geo_seller_customer_df_20.iterrows():
    customer_lat = row['customer_lat']
    customer_lng = row['customer_lng']
    folium.Circle(location=[customer_lat, customer_lng], radius=1, color = 'rgb(6, 224, 208)').add_to(customer_group)

# 판매자 위치 표시
for index, row in geo_seller_customer_df_20.iterrows():
    seller_lat = row['seller_lat']
    seller_lng = row['seller_lng']
    folium.Circle(location=[seller_lat, seller_lng], radius=1, color='rgb(255, 0, 255)').add_to(seller_group)

# 구매자와 판매자의 위치 중앙값 표시
for index, row in geo_seller_customer_df_20.iterrows():
    middle_lat = row['middle_location_lat']
    middle_lng = row['middle_location_lng']
    folium.Circle(location=[middle_lat, middle_lng], radius=1, color='rgb(0, 0, 128)').add_to(middle_location_group)

# Pax의 허브 위치 마커로 표시
for index, row in pax_hub_df.iterrows():
    pax_lat = row['pax_hub_lat']
    pax_lng = row['pax_hub_lng']
    folium.Marker(location=[pax_lat, pax_lng], icon=folium.Icon(color='beige')).add_to(pax_group)

# 각 주별 경계선 표시
folium.Choropleth(geo_data = geo, line_color='black', line_opacity = 1, fill_opacity=0).add_to(map)
    
customer_group.add_to(map)
seller_group.add_to(map)
middle_location_group.add_to(map)
pax_group.add_to(map)


# 레이어 컨트롤을 맵에 부착
folium.LayerControl().add_to(map)

map

배송이 20일 이상 걸린 구매자와 판매자의 위치 분포를 살펴보면 위와 같다.

그에 따른 위치 중앙값은 위와 같다.

각각을 남부와 남동부를 제외한 나머지 지역에서 확대해보면

해당 주에서 중앙값이 많이 분포해 있는 것을 확인할 수 있다.

• 북부, 북동부, 중서부에서 위치 중앙값이 많이 분포해 있는 도시를 알아보면 다음과 같다.
  -중서부-
  
  (시노프 - 시노프 공항 위치, 163도로를 통해 브라질리아, 북부, 북동부와 연결)
  -북부-
  
  (파라냐 - 242도로를 통해 마토그로수 주와 연결, 010도로를 통해 브라질리아, 파우마스 공항과 연결)
  -북동부-
  
  (비토리아다 콩기스타 - 116도로를 통해 살바도르 도시와 연결, BA262도로를 통해 투칸칭스 주와 연결, 415도로를 통해 미나스 제라이스 주와 연결)

검증

• 과연 중앙값으로 물류 센터의 위치를 정하는 것이 효과적일지 알아보기 위해 실제 Olist에서 운영하고 있는 Pax라는 물류 회사의 센터 위치를 같이 표시해 보았다.
  

주황색 마커는 실제 Pax 회사의 물류 센터의 위치이고 같은 지역에서 비교했을 때 빨간 원 부분에서 유사하다는 것을 확인했다.

결론

• 따라서 지금까지의 분석 결과를 종합하고 브라질의 주별 인구수와 주문 건수를 고려하여 우선 순위를 정했을 때
  

1. 북동부에서는 health_beauty 카테고리 상품을 취급하는 판매자 유입
북동부의 바이아 주에 있는 비토리아다 콩기스타에 허브 위치 제안
2. 중서부에서는 bed_bath_table, sports_leisure 카테고리 상품을 취급하는 판매자 유입
중서부의 마토그로수 주에 있는 시노프에 허브 위치 제안
3. 북부에서는 health_beauty 카테고리 상품을 취급하는 판매자 유입
북부의 토칸칭스 주에 있는 파라냐에 허브 위치 제안

프로젝트 회고

• 굉장히 의미있는 결과가 나와서 만족스러운 프로젝트였다.
  실제 EDA를 통해 데이터의 특징을 알아보고 그것을 깊이 분석해 실제로 제안할 수 있는 결과까지 도출했다는 점에서 프로젝트 진행 과정도 매끄럽게 흘러갔던 것 같다.
  많이 성장할 수 있는 기회였고, 또 이번 프로젝트를 하면서 나 자신을 성찰할 수 있는 기회도 된 것같다.

• 나 자신에 대해 성찰한 부분을 서술하자면

  1. 하나에 데이터도 보는 관점에 따라 다르게 해석될 수 있다. 시야를 넓혀서 확인해야된다. (이미 넓어도 더 넓혀야 된다.)
  2. 데이터 시각화는 보는 사람에 맞춰서 진행해야된다. 플롯을 선택하는 것 뿐만 아니라 세세한 단위까지 뭘 의미하는 지 명확하게 보여주어야 하기 때문에 꼼꼼하게 확인해야된다.
  3. 데이터 시각화는 여러 툴을 사용해 보는 것이 좋다. 각각의 툴마다 장점이 있고 전달력도 데이터마다 툴마다 달라진다.(이전에는 시각화를 할 때 python만 고집했다..)
  4. 정말 데이터는 내 입맛에 맞게 존재하지 않는다.
  5. 팀 활동에서는 협력이 제일 중요한 것 같다. 협력을 하게 되면 내가 보지 못했던 것을 팀원이 보여주고 팀원이 보지 못했던 것을 내가 알려줄 수 있다. 그에 따라 퀄리티도 높아지게 되어있다.