# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python [conda env:tensorflow]
#     language: python
#     name: conda-env-tensorflow-py
# ---

# +
# 추가되는 패키지는 여기에서 import 해주세요
import warnings
warnings.filterwarnings('ignore')

import missingno
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
import geopy.distance
pd.set_option('display.max_colwidth', -1)
pd.set_option('display.max_columns', 500)

import numpy as np
import datetime
import statsmodels.api as sm

# +
# 데이터 load
train = pd.read_csv('train.csv', date_parser='date')
# 분석을 위해 컬럼명 수정
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)


# 시작 날짜와, 끝 날짜 확인
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']]

#평일과 휴일을 나누기, 금토일 =1, 나머지는 0
train.loc[train['date_dayofweek'] == 0, 'holiday' ] = 0
train.loc[train['date_dayofweek'] == 1, 'holiday' ] = 0
train.loc[train['date_dayofweek'] == 2, 'holiday' ] = 0
train.loc[train['date_dayofweek'] == 3, 'holiday' ] = 0
train.loc[train['date_dayofweek'] == 4, 'holiday' ] = 1
train.loc[train['date_dayofweek'] == 5, 'holiday' ] = 1
train.loc[train['date_dayofweek'] == 6, 'holiday' ] = 1
# 추석 추가도 공휴일로 처리
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


# 시내와 시외를 int형으로 원핫인코딩
train.loc[train['in_out'] == '시외','in_out'] = 1
train.loc[train['in_out'] == '시내','in_out'] = 0

train.tail()
# -

total = train.groupby(['station_name', 'station_code']).count()
total

# +
station = {}
for idx in total.index:
    if idx[0] not in station.keys():
        station[idx[0]] = 1
    else:
        station[idx[0]] += 1

df = pd.DataFrame()
df['station_name'] = station.keys()
df['counts'] = station.values()
df = df.sort_values(['counts','station_name'], ascending = [False, True])
df.head()
# -

total.tail()

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[0:500] ## 데이터 확인용 
    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'])

        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(['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
# -

new_train = get_station_across()
# new_train.to_csv('newtrain.csv')
new_train[['date_x','station_code_x','station_name','date_y','station_code_y']]

new_train.to_csv('newtrain.csv')

train.query('date == "2019-09-01" & station_name == "정존마을"')

train.head()

train.query('date == "2019-09-01" & station_name == "제주썬호텔"')


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))]

# +
d2 = train.loc[0:500] ## 데이터 확인용 
blank = pd.DataFrame()

test = d2.loc[27]
test_date = d2.loc[27, 'date']
test_st_name = d2.loc[27, 'station_name']
test_dis = d2.loc[27, 'dis_jejusi']       


right = d2.query('date == @test_date & station_name == @test_st_name').reset_index()
right['code'] = abs(right['station_code'] - d2.loc[27, 'station_code'])
right['standard'] = abs(right['dis_jejusi'] - d2.loc[27, 'dis_jejusi'])
A = pd.DataFrame(test).T.merge(pd.DataFrame(right.sort_values(['code','standard']).reset_index(drop = True).loc[1]).T, on = 'station_name')
# -

tmp = pd.DataFrame(right.sort_values(['code','standard']).reset_index(drop = True))
tmp

pd.DataFrame(right.sort_values(['code','standard']).reset_index(drop = True).loc[1]).T

right

total.reset_index()

total.query('station_name == "서귀포시외버스터미널"')

# +
d2 = train.query('date == "2019-09-01"') ## 데이터 확인용 
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'])
    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
# -

d2.tail()

right







firsts.to_csv('even.csv')

firsts = even_station()

d2 = train.copy()
d2.

test = d2.loc[3]
test_date = d2.loc[3, 'date']
test_st_name = d2.loc[3, 'station_name']
test_dis = d2.loc[3, 'dis_jejusi']

test_date, test_st_name, test_dis

# +
blank = pd.DataFrame()
bag = pd.DataFrame(np.zeros(26)).T
bag.columns = right.columns
bag.station_name = right.station_name

right = d2.query('date == @test_date & station_name == @test_st_name').reset_index()
right['code'] = abs(right['station_code'] - d2.loc[3, 'station_code'])
right['standard'] = abs(right['dis_jejusi'] - d2.loc[3, 'dis_jejusi'])
try:
    A = pd.DataFrame(test).T.merge(pd.DataFrame(right.sort_values(['code','standard']).loc[1]).T, on = 'station_name')
except:
    A = pd.DataFrame(test).T.merge(bag, on = 'station_name')
blank = pd.concat([blank, A])
# -

test

blank

right

bag = pd.DataFrame(np.zeros(26)).T
bag.columns = right.columns
bag.station_name = right.station_name
c

right

right.station_name

blank[['station_name','station_code_x','station_code_y']]

total.query('station_name == "제주국제공항(600번)"')

#하나면 어떡할래?
total

hard

StarcoderdataJupyterScriptsDedupFiltered