<jupyter_start><jupyter_text>Preprocessing of Train Station Datasets<jupyter_code>import pandas as pd
import geopandas
import json, math, os, re, requests, time
import datetime
pd.set_option('display.max_columns', 100)
pd.set_option('display.max_rows', 200)
# Navigate to the directory containing final_dataset.csv
os.chdir('C:/Users/Mindy/Documents/BT4222/Group Project/Primary Datasets/')<jupyter_output><empty_output><jupyter_text>Section 1: MRT Opening Dates<jupyter_code># Dataset of MRT Opening
mrt_opening = pd.read_excel('mrt_openingDates.xlsx', header = None)
mrt_opening.columns = ['STN_NO', 'STN_PREFIX', 'OPEN_DATE']
mrt_opening.index = mrt_opening.index + 1
mrt_opening.head()
mrt_opening.shape
mrt_opening.dtypes
# Cleanin STN_NO so that only the first value is taken
mrt_opening['cleaned_STN_NO'] = mrt_opening['STN_NO'].apply(lambda x: str(x).strip().split('\xa0')[0])

# Dropping duplicated instances (ie. interchanges)
mrt_opening = mrt_opening[mrt_opening['cleaned_STN_NO'].duplicated(keep='last') == False]
mrt_opening.head()

# Editing instances where STN_NAME = 'Botanic Gardens • Kebun Bunga'
mrt_opening.loc[mrt_opening['STN_PREFIX'].str.contains('Botanic Gardens'), 'STN_PREFIX'] = 'Botanic Gardens'
len(mrt_opening['cleaned_STN_NO'].unique())
mrt_opening.head()<jupyter_output><empty_output><jupyter_text>Section 2: LRT Opening Dates<jupyter_code>lrt_opening = pd.read_excel('lrt_openingDates.xlsx', header = None)
lrt_opening.columns = ['STN_NO', 'STN_PREFIX', 'OPEN_DATE', 'CLOSE_DATE']
lrt_opening.index = lrt_opening.index + 1
lrt_opening.head()
lrt_opening.dtypes
# Cleanin STN_NO so that only the first value is taken
lrt_opening['cleaned_STN_NO'] = lrt_opening['STN_NO'].apply(lambda x: str(x).strip().split('\xa0')[0])

# Correcting cleaned_STN_NO for Sengkang station
lrt_opening.loc[lrt_opening['cleaned_STN_NO'] == 'NE16','cleaned_STN_NO'] = 'STC'<jupyter_output><empty_output><jupyter_text>Section 3: Train Stations<jupyter_code>def prep_train_station_df(train_stations): 
    
    # Leaving out Bukit Brown Station CC18  and Teck Lee Station PW2 
    # since they are not yet open (ie. opening dates are TBA)
    train_stations = train_stations[train_stations['STN_NO'] != 'CC18'] 
    train_stations = train_stations[train_stations['STN_NO'] != 'PW2']

    # Altering COLOR column for all LRT stations from 'OTHERS' to 'LRT' 
    train_stations.loc[train_stations['STN_NAME'].str.contains('LRT'), 'COLOR'] = 'LRT'

    # Altering COLOR columns for all stations along the Thompson East Coast line from 'OTHERS' to 'BROWN'
    train_stations.loc[train_stations['STN_NO'].str.contains('TE'), 'COLOR'] = 'BROWN'

    # Altering COLOR columns for all CE stations from 'OTHERS' to 'YELLOW'
    train_stations.loc[train_stations['STN_NO'].str.contains('CE'), 'COLOR'] = 'YELLOW'

    # Altering COLOR columns for all CG stations from 'OTHERS' to 'GREEN'
    train_stations.loc[train_stations['STN_NO'].str.contains('CG'), 'COLOR'] = 'GREEN'

    """
    # Converting train_stations from DataFrame object in GeoDataFrame
    train_station_gdf = geopandas.GeoDataFrame(train_stations, 
                                               geometry=geopandas.points_from_xy(train_stations['Longitude'], train_stations['Latitude']),
                                               crs={"init":"EPSG:4326"})

    # Converting CRS so that we can calculate distances in meters instead of degrees
    train_station_gdf = train_station_gdf.to_crs({"init": "EPSG:3857"})
    """
    return train_stations
train_stations = pd.read_csv('mrtsg.csv')
train_stations = prep_train_station_df(train_stations)

# Separating MRT stations from LRT stations
lrt = train_stations[train_stations['COLOR'] == 'LRT']
mrt = train_stations[train_stations['COLOR'] != 'LRT']
mrt.head()<jupyter_output><empty_output><jupyter_text>Section 4: Combining MRT Datasets<jupyter_code># Checking that these dataframes are of the same length
len(mrt['STN_NO'].values) == len(mrt_opening['STN_NO'].values)
# Check that the stations within these 2 dataframes are a 1-to-1 match
# There should be no output if it is indeed a 1-to-1 match
for station in mrt['STN_NO'].values: 
    if (station not in mrt_opening['cleaned_STN_NO'].values): 
            print(station)
            
print('---------------')
            
for station in mrt_opening['cleaned_STN_NO'].values: 
    if (station not in mrt['STN_NO'].values): 
            print(station)
mrt = mrt.join(mrt_opening.set_index('cleaned_STN_NO'), on = 'STN_NO', lsuffix='_station', rsuffix='_opening')
mrt = mrt.drop('STN_NO_opening', axis = 1)
mrt.rename(columns = {'STN_NO_station': 'STN_NO'}, inplace = True)
# Double verifying correctness of join
mrt['NAME_VERIFIED'] = mrt.apply(lambda x: x['STN_PREFIX'].upper() in x['STN_NAME'], axis = 1)
mrt['NAME_VERIFIED'].value_counts()<jupyter_output><empty_output><jupyter_text>Section 5: Combining LRT Datasets<jupyter_code># Checking that these dataframes are of the same length
len(lrt['STN_NO'].values) == len(lrt_opening['STN_NO'].values)
# Check that the stations within these 2 dataframes are a 1-to-1 match
# There should be no output if it is indeed a 1-to-1 match
for station in lrt['STN_NO'].values: 
    if (station not in lrt_opening['cleaned_STN_NO'].values): 
            print(station)
            
print('---------------')
            
for station in lrt_opening['cleaned_STN_NO'].values: 
    if (station not in lrt['STN_NO'].values): 
            print(station)
lrt = lrt.join(lrt_opening.set_index('cleaned_STN_NO'), on = 'STN_NO', lsuffix='_station', rsuffix='_opening')
lrt = lrt.drop('STN_NO_opening', axis = 1)
lrt.rename(columns = {'STN_NO_station': 'STN_NO'}, inplace = True)
# Double verifying correctness of join
lrt['NAME_VERIFIED'] = lrt.apply(lambda x: x['STN_PREFIX'].upper() in x['STN_NAME'], axis = 1)
lrt['NAME_VERIFIED'].value_counts()<jupyter_output><empty_output><jupyter_text>Section 6: Final Train Stations Dataset<jupyter_code>mrt['CLOSE_DATE'] = datetime.datetime(2100, 12, 31)
lrt.loc[lrt['CLOSE_DATE'].isnull() == True, 'CLOSE_DATE'] = datetime.datetime(2100, 3, 31)
mrt = mrt.append(lrt, ignore_index=True)
# Checking for duplicates
mrt[mrt.duplicated(keep = False)]
# Dropping unneccesary columns
mrt = mrt.drop(['STN_PREFIX', 'NAME_VERIFIED'], axis = 1)
mrt
# Checking for null values
mrt.isnull().any()
mrt['COLOR'].value_counts()

# ___ LRT Stations ___
# Historically, there has been a totoal of 41 LRT stations opened. 
# Ten Mile Junction is the only Lrt station that has ceased operations

# ___ BROWN Line ___
# The Thomson East Coast Line cuurently only has 3 stations in operation when verifying with the mrt map published

# ___ Other Lines ___
# The number of train stations for the rest of the lines were verified through a quick google search<jupyter_output><empty_output><jupyter_text>Saving Dataset<jupyter_code>mrt.to_csv('train_stations_data.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1281940233867475, 'fraction_numerical': 0.01024974736538184, 'mean_word_length': 5.23021582733813, 'pattern_counts': {'":': 2, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '89906', 'license': 'MIT', 'n_tokens_mistral': 2562, 'n_tokens_neox': 2407, 'n_words': 554, 'path': 'Feature Engineering/01A Train Data Compilation.ipynb', 'repo_name': 'SG-Property-Valuation-Model/Modelling', 'size': '6927'}