Py) 전처리 - ML 군집분석모델용 - 01

df = pd.read_csv("Korea_Busan_metro_stn_hour_gio_2023.csv", 
                 encoding = "CP949")
df.to_csv("Korea_Busan_metro_stn_hour_gio_2023_utf8.csv", 
          index = False, encoding = "utf-8")

df = pd.read_csv("Korea_Busan_metro_stn_info_210226.csv", 
                 sep = "\t", encoding = "utf-16")
df.to_csv("Korea_Busan_metro_stn_info_210226_utf8.csv",
          index = False, encoding = "utf-8")

import pandas as pd

df = pd.read_csv("data/Korea_Busan_metro_stn_hour_gio_2023_utf8.csv")
df.iloc[:3, :10]

df.columns
## Index(['역번호', '역명', '년월일', '요일', '구분', '합계', '01시-02시', '02시-03시', '03시-04시',
##        '04시-05시', '05시-06시', '06시-07시', '07시-08시', '08시-09시', '09시-10시',
##        '10시-11시', '11시-12시', '12시-13시', '13시-14시', '14시-15시', '15시-16시',
##        '16시-17시', '17시-18시', '18시-19시', '19시-20시', '20시-21시', '21시-22시',
##        '22시-23시', '23시-24시', '24시-01시'],
##       dtype='object')

ls_col_hrs = ["h" + str(h).zfill(2) for h in range(1, 25)]
df.columns = ["stn_no", "stn_nm", "date", "wday", "type", "cnt"] + ls_col_hrs
df.iloc[:3, :10]

df_stn = pd.read_csv("data/Korea_Busan_metro_stn_info_210226_utf8.csv")
df_stn.iloc[:2, :8]

df_stn_sub = df_stn[["역번호", "환승역구분"]].copy()
df_stn_sub.head()

df_stn_sub["환승역구분"].value_counts()
## 환승역구분
## 일반역    102
## 환승역     12
## Name: count, dtype: int64

df_stn_sub["is_trans"] = (df_stn_sub["환승역구분"] == "환승역") + 0
df_stn_sub = df_stn_sub.drop(columns = "환승역구분")

df_join = pd.merge(df, df_stn_sub, left_on = "stn_no", right_on = "역번호")
df_join.iloc[:3, list(range(8)) + [df_join.shape[1] - 1]]

df_join_sub = df_join.loc[~df_join["wday"].isin(["토", "일"]), ]
df_join_sub = df_join_sub.loc[df_join_sub["is_trans"] == 0, ]
df_join_sub = df_join_sub.loc[df_join_sub["type"] == "승차", ]
df_join_sub = df_join_sub.drop(columns = ["stn_nm", "is_trans", "type"])
df_join_sub.iloc[:3, :8]

df_join_sub = df_join_sub.reset_index(drop = True)
df_join_sub["is_Mon"] = (df_join_sub["wday"] == "월") + 0
df_join_sub["week_cnt"] = df_join_sub["is_Mon"].cumsum()
df_join_sub.iloc[:3, list(range(8)) + [df_join_sub.shape[1] - 1]]

df_join_sub = df_join_sub.drop(columns = ["date", "wday", "is_Mon"])
df_join_sub.iloc[:3, list(range(8)) + [df_join_sub.shape[1] - 1]]

df_g = df_join_sub.groupby(["stn_no", "week_cnt"]).sum().reset_index()
df_g.iloc[:3, :10]

df_g2 = df_g.drop(columns = "week_cnt").groupby("stn_no").agg(["min", "max", "mean"])
df_g2 = df_g2.reset_index()
df_g2.columns = ["stn_no"] + ['_'.join(map(str, t)) for t in df_g2.columns.to_list()][1:]
df_g2.iloc[:3, :8]

from sklearn.preprocessing import StandardScaler

arr_g2_nor = StandardScaler().fit_transform(df_g2.iloc[:, 1:])
df_g2_nor = pd.DataFrame(arr_g2_nor, columns = df_g2.columns[1:])
df_g2_nor.iloc[:3, :8]

df_model_nor = pd.concat([df_g2[["stn_no"]], df_g2_nor], axis = 1)
df_model_nor.iloc[:3, :8]

df_model_nor.to_csv("model_clustering_busan_metro.csv", index = False)