SQL 프로젝트를 Python으로 다시 해보기
- SQL 프로젝트와 똑같이 기술 담당자로 프로젝트 진행
- 서울시 공공자전거(따릉이) 데이터를 이용
- CSV 파일 불러오기
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
plt.rcParams.update({'font.family':'Malgun Gothic',
'figure.dpi':'150',
'figure.figsize':[8,6],
'font.size':'10'})
place_list=pd.read_csv("공공자전거 대여소 정보(23.06월 기준).csv", encoding='cp949')
trouble_list=pd.read_csv("서울시 공공자전거 고장신고 내역_23.1-6.csv",encoding='cp949')
usage_detail=pd.read_csv("서울시 공공자전거 이용내역.csv",encoding='cp949')
rent_detail=pd.read_csv('자전거대여내역_250만_kr (1).csv',encoding='cp949')
place_list = place_list.rename(columns = {'대여소\n번호' : 'place_number',
'대여소명' : 'place_name',
'소재지(자치구)' : 'address_district',
'소재지(상세주소)' : 'address_all',
'소재지(위도)' : 'address_lat',
'소재지(경도)' : 'address_long',
'설치\n시기' : 'placed_at',
'설치형태 (LCD 거치 개수)' : 'install_lcd',
'설치형태 (QR 거치 개수)' : 'install_qr',
'운영\n방식' : 'install_type'})
usage_detail = usage_detail.rename(columns = {'대여일자' : 'rent_date',
'대여소번호' : 'place_number',
'대여소' : 'place_name',
'대여구분코드' : 'rent_code',
'성별' : 'sex',
'연령대' : 'age',
' 이용건수' : 'use_count',
'운동량' : 'momentum',
'탄소량' : 'carbon',
'이동거리(M)' : 'use_distance',
'이용시간(분)' : 'use_time_min' })
trouble_list = trouble_list.rename(columns = {'자전거번호' : 'bike_id',
'등록일시' : 'created_at',
'구분' : 'trouble_type'})
rent_detail = rent_detail.rename(columns = {'자전거번호' : 'rent_date',
'대여일시' : 'place_number',
'대여 대여소번호' : 'place_name',
'대여 대여소명' : 'rent_code',
'대여거치대' : 'sex',
'연령대' : 'age',
'반납일시' : 'use_count',
'반납대여소번호' : 'momentum',
'반납대여소명' : 'carbon',
'반납거치대' : 'use_distance',
'이용시간(분)' : 'use_time_min',
'이용거리(M)' : 'use_distance',
'생년' : 'birth',
'성별' : 'sex',
'이용자종류' : 'user_type',
'대여대여소ID' : 'rent_place_id',
'반납대여소ID' : 'return_place_id'})
#[Rent_detail] : return_place_num, return_place_name, return_rests, birth, sex, return_place_id 컬럼에 있는 공백과 ‘N’값 모두 null로 처리
rent_detail['return_place_num']=np.where(rent_detail['return_place_num']=='\\N',np.nan,rent_detail['return_place_num']) # 'N' 9888개 null로
rent_detail['return_place_name']=np.where(rent_detail['return_place_name']=='\\N',np.nan,rent_detail['return_place_name']) #'N' 9888개 null로
rent_detail['return_rests']=np.where(rent_detail['return_rests']=='\\N',np.nan,rent_detail['return_rests']) #'N' 9888개 null로
rent_detail['birth']=np.where(rent_detail['birth']=='\\N',np.nan,rent_detail['birth']) #'N' 192518개 변환
rent_detail['birth']=rent_detail['birth'].dropna().astype('int64') #null 제외 데이터타입 int64로 변환
rent_detail['birth']=np.where(rent_detail['birth']>2023,np.nan,np.where(rent_detail['birth']<1933,np.nan,rent_detail['birth'])) # 이용자 범위 1933~2023 빼고 나머지 NULL로
rent_detail['sex']=np.where(rent_detail['sex']=="\\N",np.nan,rent_detail['sex']) # 'N' 689200개 변환
rent_detail['sex']=rent_detail['sex'].replace('m','M').replace('f','F') #대소문자를 대문자로 통일
rent_detail['return_place_id']=np.where(rent_detail['return_place_id']=='\\N',np.nan,rent_detail['return_place_id']) #'N' 9888개 변환
#주중,주말 분류
usage_detail['weekday']=usage_detail['rent_date'].dt.day_name()
usage_detail['weekday']=usage_detail['weekday'] \
.replace('Monday','weekday')\
.replace('Tuesday','weekday')\
.replace('Wednesday','weekday')\
.replace('Thursday','weekday')\
.replace('Friday','weekday')\
.replace('Saturday','weekend')\
.replace('Sunday','weekend')
usage_detail['weekday']=np.where(usage_detail['rent_date']=="2023-06-06","weekend",usage_detail['weekday'])
#평균 이용시간
usage_mean_time=usage_detail.dropna(subset=['use_time_min'])\
.groupby('weekday',as_index=False)\
.agg(mean_usetime=('use_time_min','mean')
#평균 이동거리
usage_mean_distance=usage_detail.dropna(subset=['use_distance'])\
.groupby('weekday',as_index=False)\
.agg(mean_usedistance=('use_distance','mean'))
#막대그래프 시각화
fig, ax=plt.subplots(nrows=2);
sns.barplot(data=usage_mean_time,x='weekday',y='mean_usetime',ax=ax[0]);
sns.barplot(data=usage_mean_distance,x='weekday',y='mean_usedistance',ax=ax[1]);
- 자치구별 평균 이동거리 및 이용시간(상위 6개구)
#테이블 합치기
total_usage=pd.merge(usage_detail,place_list,on='place_number')
#자치구별 평균 이용시간
mean_time_district=total_usage.dropna(subset=['use_time_min']) \
.groupby(['address_district','weekday'],as_index=False)\
.agg(mean_time_district=('use_time_min','mean'))
#자치구별 평균 이동거리
mean_distance_district=total_usage.dropna(subset=['use_distance']) \
.groupby(['address_district','weekday'],as_index=False)\
.agg(mean_distance_district=('use_distance','mean'))
#막대 그래프 시각화
fig, ax=plt.subplots(nrows=2);
sns.barplot(data=mean_time_district,x='address_district',y='mean_time_district',hue='weekday',order=['성동구','영등포구','마포구','광진구','동대문구','관악구'],ax=ax[0]);
sns.barplot(data=mean_distance_district,x='address_district',y='mean_distance_district',hue='weekday',order=['성동구','영등포구','마포구','광진구','동대문구','관악구'],ax=ax[1]);
#연령별 평균 이용시간
age_meantime=usage_detail.dropna(subset=['use_time_min'])\
.groupby(['weekday','age'],as_index=False)\
.agg(mean_usetime=('use_time_min','mean')).sort_values('age')
#연령별 평균 이동거리
age_mean_distance=usage_detail.dropna(subset=['use_distance'])\
.groupby(['weekday','age'],as_index=False)\
.agg(mean_distance=('use_distance','mean')).sort_values('age')
#막대 그래프 그리기
fig, ax=plt.subplots(nrows=2);
sns.barplot(data=age_meantime,x='age',y='mean_usetime',hue='weekday',order=['~10대','20대','30대','40대','50대','60대','70대이상','기타'],ax=ax[0]);
sns.barplot(data=age_mean_distance,x='age',y='mean_distance',hue='weekday',order=['~10대','20대','30대','40대','50대','60대','70대이상','기타'],ax=ax[1]);
#대여권별 평균 이용시간
type_time_mean=total_usage.dropna(subset=['use_time_min'])\
.groupby(['rent_code','weekday'],as_index=False)\
.agg(time_mean=('use_time_min','mean'))
#대여권별 평균 이동거리
type_distance_mean=total_usage.dropna(subset=['use_distance'])\
.groupby(['rent_code','weekday'],as_index=False)\
.agg(distance_mean=('use_distance','mean'))
#막대그래프 시각화
fig, ax=plt.subplots(nrows=2);
sns.barplot(data=type_time_mean,x='rent_code',y='time_mean',hue='weekday',ax=ax[0]);
sns.barplot(data=type_distance_mean,x='rent_code',y='distance_mean',hue='weekday',ax=ax[1]);
