<jupyter_start><jupyter_text>제주도 버스승차 인원 예측-  data 415423 rows × 49 columns Columns- id : 해당 데이터에서의 고유한 id- data : 날짜- bus_route_id : 노선 ID- in_out : 시내버스, 시외버스 구분- station_code : 해당 승하차 정류소의 id- station_name : 해당 승하차 정류소의 이름- latitude : 해당 버스정류장의 위도- longitude : 해당 버스정류장의 경도- X-Y_ride : X:00:00 ~ x:59:59까지 승차한 인원 수- X-Y_takeoff : X:00:00 ~ x:59:59까지 하차한 인원 수- 18-20_ride : 18:00:00부터 19:59:59까지 승차한 인원 수- 지번주소 : EDA 및 제주 날씨 merge용 column - si :EDA 및 제주 날씨 merge용 column - city : EDA 및 제주 날씨 merge용 column  - dong : EDA 및 제주 날씨 merge용 column  - road_addr : EDA 및 제주 날씨 merge용 column  - weather_addr : EDA 및 제주 날씨 merge용 column - temperature : 온도- precipitation : 강수량- bus_interval : 노선 배차 간격- date_day : 일 (1일 ~ 30일)- date_dayofweek : 요일 (0 ~ 6)- date_dayofname : 요일 (월 ~ 일)- weekday : 평일 여부- weekend : 주말 여부- holiday : 공휴일 여부- typhoon : 태풍이 온날- in_ : 시내 버스 구분- out : 시외 버스 구분- ridexx : ride(탑승)의 2시간 간격 (ex 6시부터 ~7시 까지), 예측하는 y값이 18시 ~ 20시 까지이므로 생성함- offxx : off(하차)의 2시간 간격 (ex 6시부터 ~7시 까지), 예측하는 y값이 18시 ~ 20시 까지이므로 생성함- ride_sum : 승차인원의 합계- off_sum : 하차인원의 합계- bus_route_id_sum : 정류소, 일별 운행한 버스노선의 갯수- bus_route_id_all_sum : 정류소별 9월 전체 운행한 버스노선의 갯수  0. 공통<jupyter_code># 추가되는 패키지는 여기에서 import 해주세요
import warnings
warnings.filterwarnings('ignore')
import missingno
import pandas as pd
import numpy as np
import datetime
import statsmodels.api as sm
import matplotlib.pyplot as plt
import seaborn as sns
import matplotlib as mpl
import pickle
from sklearn.externals import joblib
from statsmodels.stats.outliers_influence import variance_inflation_factor
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.preprocessing import StandardScaler
from itertools import count
import plotly.express as px

import geopy.distance

pd.options.mode.chained_assignment = None
pd.set_option('display.max_columns', 500)
# matplotlib 설정
mpl.use('Agg')
mpl.rcParams['axes.unicode_minus'] = False
%matplotlib inline
# seaborn 설정
sns.set()
sns.set_style("whitegrid")
sns.set_color_codes()
from matplotlib import font_manager, rc
font_name = font_manager.FontProperties(fname="c:/Windows/Fonts/malgun.ttf").get_name()
rc('font', family=font_name)
pd.set_option('max_columns', 700)
pd.set_option('max_rows', 500)<jupyter_output><empty_output><jupyter_text>01. 데이터 전처리 부분 (컬럼 추가, 삭제 등)<jupyter_code># 데이터 load
train = pd.read_csv('train.csv',date_parser='date')
train.tail(2)
# 분석을 위해 컬럼명 수정
train.rename(columns={'6~7_ride': 'ride6', '7~8_ride': 'ride7', '8~9_ride': 'ride8',\
                      '9~10_ride': 'ride9', '10~11_ride': 'ride10', '11~12_ride': 'ride11', 
                      '6~7_takeoff': 'off6', '7~8_takeoff': 'off7', '8~9_takeoff': 'off8', \
                      '9~10_takeoff': 'off9', '10~11_takeoff': 'off10', '11~12_takeoff': 'off11',\
                      '18~20_ride': 'ride18'},inplace=True)
train.tail(2)
coords_jejusi = (33.500770, 126.522761) #제주시의 위도 경도
coords_seoquipo = (33.259429, 126.558217) #서귀포시의 위도 경도
train['dis_jejusi'] = [geopy.distance.vincenty((train['latitude'].iloc[i],train['longitude'].iloc[i]), coords_jejusi).km for i in range(len(train))]
train['dis_seoquipo'] = [geopy.distance.vincenty((train['latitude'].iloc[i],train['longitude'].iloc[i]), coords_seoquipo).km for i in range(len(train))]

def get_station_across():
    
    d2 = train
#     d2 = train.loc[300000:len(train)] ## 데이터 확인용 
    blank = pd.DataFrame()
    
    for i in d2.index:
#         print(str(i)+"/"+ str(len(d2)))
        test = d2.loc[i]
        test_date = d2.loc[i, 'date']
        test_st_name = d2.loc[i, 'station_name']
        test_dis = d2.loc[i, 'dis_jejusi']       
        

        right = d2.query('date == @test_date & station_name == @test_st_name').reset_index()
        right['code'] = abs(right['station_code'] - d2.loc[i, 'station_code'])
        right['standard'] = abs(right['dis_jejusi'] - d2.loc[i, 'dis_jejusi'])
        right['route_id'] = abs(right['bus_route_id'] - d2.loc[i, 'bus_route_id'])

        bag = pd.DataFrame(np.zeros(len(right.columns))).T #26
        bag.columns = right.columns
        bag.station_name = right.station_name
        bag.station_code = right.station_code
        bag.date = right.date
        bag.in_out = right.in_out
        bag.bus_route_id = right.bus_route_id

        
        try:
            A = pd.DataFrame(test).T.merge(pd.DataFrame(right.sort_values(['route_id','code','standard']).reset_index(drop = True).loc[1]).T, on = 'station_name')
        except:
            A = pd.DataFrame(test).T.merge(bag, on = 'station_name')
        
        blank = pd.concat([blank, A])
    blank.drop(['code','standard','index','id_y'], axis = 1, inplace = True)
    blank.reset_index(inplace = True)
    return blank
# train = get_station_across()
train = pd.read_csv('trainx2.csv')
train.drop(train.columns[0], axis = 1, inplace = True)
train.rename(columns={'bus_route_id_x' : 'bus_route_id','id_x':'id','date_x' : 'date', 'station_code_x' : 'station_code', 'station_name_x' : 'station_name'\
,'date_day_x' : 'date_day', 'date_dayofname_x': 'date_dateofname', 'date_dayofweek_x':'date_dayofweek', 'in_out_x':'in_out'}, inplace = True)
train.tail(2)
# 주소 데이터 가져오기
jeju_addr=pd.read_csv('jeju_addr.csv')
train = pd.merge(train, jeju_addr, on= 'station_code')
train.tail(2)
# 날씨 추가

# date 타입 변경
train['date'] = train['date'].astype('datetime64')

# 날씨 가져오기
#weather 데이터를 추가하기 전 실행해주세요!
jeju=['제주시','애월읍','조천읍']
seogipo=['서귀포시','남원읍','안덕면']
sungsan=['성산읍','구좌읍','우도면','표선면']
gosan=['한경면','한림읍','대정읍']
chuga=['추자면']
weather_addr=[]
for city in train['city']:
    if city in jeju: weather_addr.append('제주')
    elif city in seogipo: weather_addr.append('서귀포')
    elif city in gosan : weather_addr.append('고산')
    elif city in sungsan : weather_addr.append('성산')
    elif city in chuga : weather_addr.append('추자')    
#weather_addr 컬럼 생성
train['weather_addr']=weather_addr
weather=pd.read_excel('all_weather.xlsx')
train = pd.merge(train, weather, on= ['weather_addr','date'])
train.tail(2)
# 배차간격 추가
bus_interval = pd.read_csv('bus_interval_final.csv')
train = pd.merge(train, bus_interval, how='left', on='bus_route_id')
train.tail(2)
# Nan data 확인
missingno.matrix(train)
plt.show()
# 배차간격에 Nan값 확인
train.info()
# 배차간격이 없는것은 너무오래 안다니는것으로 999로 변경
train['bus_interval'].fillna(999, inplace = True)
train['precipitation'].fillna(0, inplace = True)
# Nan data 확인
missingno.matrix(train)
plt.show()
# 정확히 Nan값이 없는것을 확인
train.info()
# 시작 날짜와, 끝 날짜 확인
print(train['date'].min())
print(train['date'].max())
# date 타입 변경
# train['date'] = train['date'].astype('datetime64')
# 9월 1일 ~ 9월 30일 데이터이니까, day만 따로 가져옴
train['date_day'] = train['date'].dt.day
# 월(0) ~ 일(6) 데이터로 변환
train['date_dayofweek'] = train['date'].dt.dayofweek
train['date_dayofname']= train['date'].dt.day_name()
train[['date','date_dayofweek','date_day','date_dayofname']]

# 평일과 휴일의 원핫 인코딩
train.loc[train['date_dayofweek'] == 0, 'weekday' ] = 1
train.loc[train['date_dayofweek'] == 1, 'weekday' ] = 1
train.loc[train['date_dayofweek'] == 2, 'weekday' ] = 1
train.loc[train['date_dayofweek'] == 3, 'weekday' ] = 1
train.loc[train['date_dayofweek'] == 4, 'weekday' ] = 0
train.loc[train['date_dayofweek'] == 5, 'weekday' ] = 0
train.loc[train['date_dayofweek'] == 6, 'weekday' ] = 0

#Weekend 주말
train.loc[train['date_dayofweek'] == 0, 'weekend' ] = 0
train.loc[train['date_dayofweek'] == 1, 'weekend' ] = 0
train.loc[train['date_dayofweek'] == 2, 'weekend' ] = 0
train.loc[train['date_dayofweek'] == 3, 'weekend' ] = 0
train.loc[train['date_dayofweek'] == 4, 'weekend' ] = 1
train.loc[train['date_dayofweek'] == 5, 'weekend' ] = 1
train.loc[train['date_dayofweek'] == 6, 'weekend' ] = 1

# 추석 추가도 공휴일로 처리
train['holiday'] = 0
train.loc[train['date'] == '2019-09-14', 'holiday'] = 1
train.loc[train['date'] == '2019-09-13', 'holiday'] = 1
train.loc[train['date'] == '2019-09-12', 'holiday'] = 1

# 태풍의 컬럼 추가
train['typhoon'] = 0
train.loc[train['date'] == '2019-09-06', 'typhoon'] = 1
train.loc[train['date'] == '2019-09-07', 'typhoon'] = 1
train.loc[train['date'] == '2019-09-21', 'typhoon'] = 1
train.loc[train['date'] == '2019-09-22', 'typhoon'] = 1

train.tail(2)
# 시내, 시외 원핫 인코딩
in_out = pd.get_dummies(train['in_out'])
in_out.columns = ['in_', 'out']
train = pd.concat([train, in_out], axis = 1)
train.tail(2)
# 2시간 노선 만들기
train['ride67_x'] = train['ride6_x'] + train['ride7_x']
train['ride89_x'] = train['ride8_x'] + train['ride9_x']
train['ride1011_x'] = train['ride10_x'] + train['ride11_x']
train['off67_x'] = train['off6_x'] + train['off7_x']
train['off89_x'] = train['off8_x'] + train['off9_x']
train['off1011_x'] = train['off10_x'] + train['off11_x']

train['ride67_y'] = train['ride6_y'] + train['ride7_y']
train['ride89_y'] = train['ride8_y'] + train['ride9_y']
train['ride1011_y'] = train['ride10_y'] + train['ride11_y']
train['off67_y'] = train['off6_y'] + train['off7_y']
train['off89_y'] = train['off8_y'] + train['off9_y']
train['off1011_y'] = train['off10_y'] + train['off11_y']


train.tail(2)
# 탑승의 총합 만들기
train['ride_sum'] = train['ride6_x'] + train['ride7_x'] + train['ride8_x'] + train['ride9_x'] + train['ride10_x'] + train['ride11_x']
# 하차의 총합만들기
train['off_sum'] = train['off6_x'] + train['off7_x'] + train['off8_x'] + train['off9_x'] + train['off10_x'] + train['off11_x']
# 일별, 정류장별 버스노선 갯수
station_table = pd.pivot_table(index=['station_code', 'date'],values='bus_route_id', data=train, aggfunc=len).reset_index()
station_table.columns = ['station_code', 'date', 'bus_route_id_sum']
train = pd.merge(train, station_table, how='left', on=['station_code', 'date'])

# 정류장별 버스노선의 갯수
station_table = pd.pivot_table(index=['station_code'],values='bus_route_id', data=train, aggfunc=len).reset_index()
station_table.columns = ['station_code', 'bus_route_id_all_sum']
train = pd.merge(train, station_table, how='left', on=['station_code'])

train.tail(10)
# 컬럼별 값들의 갯수는?
for i in train.columns:
    print('{}의 개수는 {}'.format(i, len(train[i].unique())))
# 415423 rows × 86 columns
train = train.sort_values('id_x').reset_index(drop = True)
train.shape
# # 데이터 저장
# 저장된 데이터를 드렸으니, 해당 행은 실행할 필요없습니다.
# train.to_csv('raw_train.csv',encoding='utf-8',index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17834394904458598, 'fraction_numerical': 0.039196472317491425, 'mean_word_length': 4.875647668393782, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '152632', 'license': 'MIT', 'n_tokens_mistral': 4851, 'n_tokens_neox': 5119, 'n_words': 849, 'path': 'hss/Final01_Data Preprocessing_ver_0-1.0.2.ipynb', 'repo_name': 'hmkim312/Jeju_bus_ride_prediction', 'size': '10205'}