<jupyter_start><jupyter_text>MBTdelay -- build_datasets.ipynb© <NAME> 2019 <EMAIL>Build composite database for MBTdelay using headway gap (delays), events, and weather<jupyter_code># GENERAL INCLUSIONS
import numpy as np
import glob,os
import matplotlib.pyplot as plt
import csv
import pandas as pd

# FOR DATES AND TIMES #
import time
import datetime
from dateutil import tz
from datetime import timedelta
import arrow
STATIONS=["Lechmere", "Science Park", "North Station",
              "Haymarket", "Government Center", "Park Street", "Boylston",
             "Arlington", "Copley","Hynes", "Kenmore","Fenway","Longwood",
             "Brookline Village", "Brookline Hills", "Beaconsfield", "Reservoir",
             "Chestnut Hill", "Newton Centre", "Newton Highlands", "Eliot",
             "Waban","Woodland", "Riverside"]

# GENERAL PURPOSE DATE-TIME FUNCTION
from date_time_functions import *
## SPECIFIC DATE-TIME FUNCTIONS

# CONVERTS AB:CD TO AB:00
def rounddown_hms(x):
    return x[:2]+":00"

def rounddown_h(x):
    return x[:2]

# GET HOUR FROM YYYY-MM-DD HH:MM:SS STRING
def get_hour(dt_str):
    return dt_str[11:13]

# GET MONTH FROM YYYY-MM-DD HH:MM:SS STRING
def get_month_num(dt_str):
    return dt_str[5:7]


# WEATHER DATA AND SAVE TO ARRAY
# GET FILENAMES 
weather_data=[]
station_weather_files=glob.glob("WEATHER_DATA/*.csv")
for file in station_weather_files:
    sw_raw=pd.read_csv(file)
    station_name,station_lat,station_lon=(file.replace(".csv","").replace("WEATHER_DATA/","")).split("_")

    weather_data.append([station_name,station_lat,station_lon,sw_raw])
    
weather_data=np.array(weather_data)

# EVENT DATA 
# ALL FENWAY EVENTS
fenway_df=pd.read_csv("EVENT_SCHED/all_events.csv")

# GET RAW HEADWAY DATA
# GET FILENAMES 
headway_data=[]
headway_data_files=glob.glob("HEADWAY_DATA/Headway*.csv")
for file in headway_data_files:
    station_name,station_id=file.replace("HEADWAY_DATA/Headway_","").replace(".csv","").split("_")
    dt_raw=pd.read_csv(file)
    headway_data.append([station_name,station_id,dt_raw])
headway_data=np.array(headway_data)

# FOR STATION DATA WE WANT TO BIN BY THE HOUR
## NOTE THAT THIS ISNT NEEDED EVERY TIMES
# pd.options.mode.chained_assignment = None  # default='warn'

# GET HEADWAY DATA 
# BINNING BY THE HOUR
def get_average_headway(station_num):

    station_name=headway_data[station_num][0]
    station_id=headway_data[station_num][1]
    headway_data_loc=headway_data[station_num][2].copy() # GET DATAFRAME

    # MAKE DATA
    headway_data_loc['HEAD_GAP']=headway_data_loc['HDW_T']-headway_data_loc['BNCH_HDW_T']
    headway_data_loc['CURR_ARR_DT']=headway_data_loc['CURR_ARR_DT'].astype(int)
    
    # LOCAL BOSTON TIME #
    headway_data_loc['CURR_ARR_DATE_TIME']=headway_data_loc['CURR_ARR_DT'].apply(lambda x: conv_unixts_to_east_hms(x))
    
    new_date_time=headway_data_loc['CURR_ARR_DATE_TIME'].str.split(" ", n = 1, expand = True)
    headway_data_loc['CURR_ARR_DATE']=new_date_time[0]
    headway_data_loc['CURR_ARR_TIME']=new_date_time[1]

#     print(headway_data_loc)
    
    # FIX FORMAT OF TIME TO HH:MM
    headway_data_loc['HOUR_BIN']=headway_data_loc['CURR_ARR_TIME'].apply(lambda x: rounddown_hms(x))
    headway_data_loc['HOUR_BIN']=headway_data_loc['HOUR_BIN'].astype(str)

    # BIN BY THE HOUR
    binned_headway=headway_data_loc.groupby(['CURR_ARR_DATE','HOUR_BIN']).agg({'HEAD_GAP':'mean','HDW_T':'mean','BNCH_HDW_T':'mean'}).reset_index()

    # FIX FORMAT TO DATETIME TO YYYY-MM-DD HH:MM
    binned_headway['CURR_ARR_DATE_TIME']=(binned_headway['CURR_ARR_DATE']).str.cat(binned_headway['HOUR_BIN'],sep=" ",join='left')
    # UNIX FORMAT 
    binned_headway['U_DATETIME']=binned_headway['CURR_ARR_DATE_TIME'].apply(lambda x: conv_east_to_unixts_hm(x))
    binned_headway=binned_headway.sort_values(['U_DATETIME'])

    header = ["U_DATETIME","HEAD_GAP","HDW_T","BNCH_HDW_T"]
    binned_headway.to_csv('DELAYS/'+station_name+'_'+station_id+'.csv',columns=header,index=False)


# GET AVERAGE HEADWAY TIME
for station_num in range(len(headway_data)):
    #print(station_num,headway_data[station_num][0],headway_data[station_num][1])
    get_average_headway(station_num)
# AVERAGED HEADWAY DATA
# GET FILENAMES 
f_head_data=[]
f_head_data_files=glob.glob("DELAYS/*.csv")
for file in f_head_data_files:
    station_name,station_id=file.replace("DELAYS/","").replace(".csv","").split("_")
    dt_raw=pd.read_csv(file)
    f_head_data.append([station_name,station_id,dt_raw])
f_head_data=np.array(f_head_data)

station_names=f_head_data[:,0]
station_ids=f_head_data[:,1]

# THE FINAL DATA -- COMBINE HEADWAY DELAY, WEATHER, AND FENWAY EVENTS
for i in range(len(f_head_data_files)):
    station_name=f_head_data[i,0]
    station_id=f_head_data[i,1]

    #dwell_df=f_dwell_data[i,2]
    head_df=f_head_data[i,2].copy()

    weather_df=(weather_data[weather_data[:,0]==station_name])[0,3].copy()

    merged_1=pd.merge(left=head_df,right=weather_df,     how='left', left_on='U_DATETIME', right_on='U_DATETIME')
    merged_final=pd.merge(left=merged_1,right=fenway_df, how='left', left_on='U_DATETIME', right_on='u_datetime')

    # GET HUMAN READABLE DATE, HOUR AND MONTH BINS
    merged_final['DATETIME'] = merged_final['U_DATETIME'].apply(lambda x:conv_unixts_to_east_hms(x))
    merged_final['HOUR_BIN'] = merged_final['DATETIME'].apply(lambda x:get_hour(x))
    merged_final['MONTH_BIN'] = merged_final['DATETIME'].apply(lambda x:get_month_num(x))
    merged_final['DOW'] = merged_final['U_DATETIME'].apply(lambda x:get_day_of_week_east_unix(x))
    # ENCODE PRECIPITATION TYP
    catenc=pd.factorize(merged_final['PRECIP_TYP'])
    merged_final['PRECIP_TYP_ENC'] = catenc[0]
    # FILL NaN FOR EVENT
    merged_final['event'] =  merged_final['event'].fillna(0)
    
    merged_final.to_csv('DS/DS_'+station_name+'_'+station_id+'.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1388888888888889, 'fraction_numerical': 0.006305385139740968, 'mean_word_length': 6.105326876513317, 'pattern_counts': {'":': 1, '<': 7, '<?xml version=': 0, '>': 7, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '648930', 'license': 'Unlicense', 'n_tokens_mistral': 2369, 'n_tokens_neox': 2273, 'n_words': 335, 'path': 'build_datasets.ipynb', 'repo_name': 'markfmace/MBTdelay', 'size': '5883'}