<jupyter_start><jupyter_text>Preprocessing data for Task 1 Import section<jupyter_code>import pandas as pd
import numpy as np
from copy import copy<jupyter_output><empty_output><jupyter_text>Load all tables onto dataframes<jupyter_code>train_t_1 = pd.read_csv("data/phase1_training/20min_avg_travel_time_training_phase1.csv")
train_t_2 = pd.read_csv("data/phase1_training/20min_avg_volume_training_phase1.csv")
train_t_3 = pd.read_csv("data/road/links_table3.csv")
train_t_4 = pd.read_csv("data/road/routes_table 4.csv")
train_t_5 = pd.read_csv("data/phase1_training/trajectories_training_phase1_table5.csv")
train_t_6 = pd.read_csv("data/phase1_training/volume_training_phase1_table6.csv")
train_t_7 = pd.read_csv("data/weather/weather_July_01_Oct_17_table7.csv")
sample_data_final = pd.read_csv("data/submission_sample/submission_sample_travelTime.csv")
test_t_1 = pd.read_csv("data/phase1_test/20min_avg_travel_time_test_phase1.csv")
test_t_2 = pd.read_csv("data/phase1_test/20min_avg_volume_test_phase1.csv")
test_t_5 = pd.read_csv("data/phase1_test/trajectories_test_phase1_table5.csv")
test_t_6 = pd.read_csv("data/phase1_test/volume_test_phase1_table6.csv")
test_t_7 = pd.read_csv("data/weather/weather_Oct_18_Oct_24_table7.csv")<jupyter_output><empty_output><jupyter_text>Removing outliers from table 5 -- trajectory data<jupyter_code>for k, row in train_t_5.iterrows():
    if row['travel_time'] > 600:
        last_value = train_t_5.loc[k-1,'travel_time']
        next_value = train_t_5.loc[k+1,'travel_time']
        if last_value < 600:
            train_t_5.loc[k, 'travel_time'] = (last_value + next_value)/2.0
        else:
            train_t_5.loc[k, 'travel_time'] = last_value
for k, row in test_t_5.iterrows():
    if row['travel_time'] > 600:
        last_value = test_t_5.loc[k-1,'travel_time']
        next_value = test_t_5.loc[k+1,'travel_time']
        if last_value < 600:
            test_t_5.loc[k, 'travel_time'] = (last_value + next_value)/2.0
        else:
            test_t_5.loc[k, 'travel_time'] = last_value<jupyter_output><empty_output><jupyter_text>Grouping the travel time of individual into average travel time for every 20 mins time window<jupyter_code># processing training data -- table 5

train_t_5['starting_time'] = pd.to_datetime(train_t_5['starting_time'], format='%Y-%m-%d %H:%M:%S')

train_t_5 = train_t_5.set_index(['starting_time'])

train_t_5 = train_t_5.groupby([pd.Grouper(freq='20Min'), 
                               'intersection_id', 
                               'tollgate_id']).travel_time.mean().reset_index().rename(
    columns={'travel_time':'average_travl_time'})

# processing test data -- table 5

test_t_5['starting_time'] = pd.to_datetime(test_t_5['starting_time'], format="%Y-%m-%d %H:%M:%S")

test_t_5 = test_t_5.set_index(['starting_time'])

test_t_5 = test_t_5.groupby([pd.Grouper(freq='20Min'), 
                             'intersection_id', 
                             'tollgate_id']).travel_time.mean().reset_index().rename(
    columns={'travel_time':'average_travl_time'})

print(train_t_5.shape, test_t_5.shape)<jupyter_output>(25144, 4) (448, 4)<jupyter_text>create unique pairs of all intersection and toll<jupyter_code>all_toll_intersections = []

for j in range(sample_data_final.shape[0]):    
    intersection=sample_data_final.loc[j]['intersection_id']
    tollgate=sample_data_final.loc[j]['tollgate_id']
    token = (intersection,tollgate)
    if token not in all_toll_intersections:
        all_toll_intersections.append(token)
        
sample_time = []
sample_times = sample_data_final[(sample_data_final['tollgate_id']==1)&
                                 (sample_data_final['intersection_id']=='B')]['time_window']

for st in sample_times:
    sample_time.append(pd.to_datetime(st.split(',')[0][1:], format="%Y-%m-%d %H:%M:%S") - pd.DateOffset(hours=2))

sample_time = pd.Series(sample_time).values<jupyter_output><empty_output><jupyter_text>method to replace missing time<jupyter_code>def replace_missing_time(test, tollgate, intersection, iteration, sample_time):
    while iteration > 0:
        try:
            missing_time = test[(test['tollgate_id']==tollgate)& 
                                (test['starting_time'] == sample_time[iteration - 1])& 
                                (test['intersection_id']==intersection)]['average_travl_time']
            
            return missing_time.values[0]
        except Exception as e:
            iteration = iteration - 1
            continue<jupyter_output><empty_output><jupyter_text>replace the missing time in table 5<jupyter_code>for intersection, tollgate in all_toll_intersections:
    test_toll_intersections = copy(test_t_5[(test_t_5['tollgate_id']==tollgate) & 
                                                       (test_t_5['intersection_id']==intersection) 
                                                     ].reset_index()) 
    
    test_time= test_t_5[(test_t_5['tollgate_id']==tollgate) & 
                                   (test_t_5['intersection_id']==intersection)
                                  ]['starting_time'].values
    
    test_toll_intersections.drop('index',axis=1,inplace=True)
    test_toll_intersections = test_toll_intersections.loc[0]
    
    for k in range(len(sample_time)):
        if sample_time[k] not in test_time: 
            test_toll_intersections['starting_time'] = sample_time[k]
            test_toll_intersections['average_travl_time'] = replace_missing_time(test_t_5, 
                                                                                 tollgate, 
                                                                                 intersection,
                                                                                 k, 
                                                                                 sample_time)
            
            test_t_5 = test_t_5.append(test_toll_intersections)
            
test_t_5 = test_t_5.reset_index()
test_t_5.drop('index', axis=1, inplace=True)
train_t_5 = train_t_5.append(test_t_5)
train_t_5['lag1'] = train_t_5['average_travl_time'].shift(1)
train_t_5['lag2'] = train_t_5['average_travl_time'].shift(2)
train_t_5['lag3'] = train_t_5['average_travl_time'].shift(3)
train_t_5['lag4'] = train_t_5['average_travl_time'].shift(4)
train_t_5['lag5'] = train_t_5['average_travl_time'].shift(5)
train_t_5['lag6'] = train_t_5['average_travl_time'].shift(6)
train_t_5['lag7'] = train_t_5['average_travl_time'].shift(7)<jupyter_output><empty_output><jupyter_text>create a heat map of the table 5<jupyter_code>import seaborn as sns
sns.heatmap(train_t_5.corr(), annot = True, fmt = ".2f")
display()<jupyter_output><empty_output><jupyter_text>create another dataframe from table 5 with a date offset of 2 hours<jupyter_code>test_t_5['starting_time'] = test_t_5['starting_time'] + pd.DateOffset(hours=2)
test_t_5_dup = test_t_5
test_t_5_dup.drop('average_travl_time', axis=1, inplace=True)
test_t_5_dup.head()
print(test_t_5_dup.shape)
print(train_t_5.shape)<jupyter_output>(504, 3)
(25648, 11)<jupyter_text>Additional info -- adding festival day's data<jupyter_code># Chinese festival days

from datetime import datetime

start_date = datetime(2016, 9, 15)
end_date = datetime(2016, 9, 17)
holiday_range = pd.date_range(start_date, end_date)
start_date2 = datetime(2016, 10, 1)
end_date2 = datetime(2016, 10, 7)
holiday_range= holiday_range.append(pd.date_range(start_date2, end_date2))
# Adding Extra column with the name ch_holidays. If the date exists in between Holiday range,the value of 
#the column will be 1 or else 0

def identify_holiday_dates(data, start_time):
    day_of_the_week = pd.get_dummies(data[start_time].dt.weekday_name)
    hr_of_the_day = pd.get_dummies(data[start_time].dt.hour, prefix='hour_')
    minute= pd.get_dummies(data[start_time].dt.minute)
    data = pd.concat([data, day_of_the_week, hr_of_the_day, minute], axis=1)
    data['date'] = data[start_time].dt.date
    data['date'] = data['date'].astype(str)
    data['date'] = pd.to_datetime(data['date'], format='%Y-%m-%d')
    data['hour'] = data[start_time].dt.hour.astype(int)
    start_time_date = data[start_time].dt.date
    
    for k, row in data.iterrows():
        data.loc[k,"ch_holidays"] = 0
        if start_time_date.loc[k] in holiday_range: data.loc[k, "ch_holidays"] = 1
    return data
train_t_5 = identify_holiday_dates(train_t_5, "starting_time")
test_t_5_dup = identify_holiday_dates(test_t_5_dup, "starting_time")
print(test_t_5_dup.shape)
train_t_5.head()<jupyter_output>(504, 20)<jupyter_text>Adding table 7 (weather data)<jupyter_code># loading and appending weather test data to weather train data

train_t_7 = train_t_7.append(test_t_7).reset_index()
train_t_7['date'] = pd.to_datetime(train_t_7['date'], format='%Y-%m-%d')<jupyter_output><empty_output><jupyter_text>remove outlier from weather table<jupyter_code># replacing the outlier value of 99017 in wind_direction of weatherData by avg of previous and next value
# 99017 is the only outlier

for i, row in train_t_7.iterrows():
    if row['wind_direction']== 999017.0:
        previous_value = train_t_7.loc[i-1,'wind_direction']
        next_value = train_t_7.loc[i+1,'wind_direction']
        if next_value != 999017.0:
            train_t_7.loc[i, 'wind_direction'] = (previous_value + next_value)/2.0
        else:
            train_t_7.loc[i, 'wind_direction'] = previous_value
train_t_7.head()
train_t_5.shape<jupyter_output><empty_output><jupyter_text>Combining table 5 with table 7 (trajectory data + weather data)<jupyter_code># Turn hour into 3 hour intervals and then combine with weather data

def addWeatherData(df):
    for i, row in df.iterrows():
        if row['hour'] in [23,0,1]: df.loc[i, "hour"] = 0
        elif row['hour'] in [2,3,4]: df.loc[i, "hour"] = 3 
        elif row['hour'] in [5,6,7]: df.loc[i, "hour"] = 6         
        elif row['hour'] in [8,9,10]: df.loc[i, "hour"] = 9         
        elif row['hour'] in [11,12,13]: df.loc[i, "hour"] = 12         
        elif row['hour'] in [14,15,16]: df.loc[i, "hour"] = 15         
        elif row['hour'] in [17,18,19]: df.loc[i, "hour"] = 18         
        elif row['hour'] in [20,21,22]: df.loc[i, "hour"] = 21
    return pd.merge(df, train_t_7, on =['date', 'hour'], how='left')
train_t_5 = addWeatherData(train_t_5)
test_t_5_dup = addWeatherData(test_t_5_dup)
train_t_5.head()
train_t_5 = train_t_5.drop(['hour','date'],axis=1)
test_t_5_dup = test_t_5_dup.drop(['hour','date'],axis=1)
train_t_5.shape<jupyter_output><empty_output><jupyter_text>Processing table 4<jupyter_code>divison_row = []

def divide(seq):
    return seq.split(',')

train_t_4.link_seq = train_t_4.link_seq.apply(divide)

X = train_t_4.apply(lambda row: [divison_row.append([row['intersection_id'], row['tollgate_id'], link]) 
                         for link in row.link_seq], axis=1)

table_headers = ['intersection_id', 'tollgate_id', 'link_id']
train_t_4_new= pd.DataFrame(divison_row, columns=table_headers)
train_t_4_new['link_id'] = train_t_4_new['link_id'].astype(str)<jupyter_output><empty_output><jupyter_text>Processing table 3<jupyter_code>train_t_3['cross_in'] = 0
train_t_3['cross_out'] = 0

for k, row in train_t_3.iterrows():
    if ',' in str(row['out_top']):
        train_t_3.loc[k, 'cross_out'] = 1
    if ',' in str(row['in_top']):
        train_t_3.loc[k, 'cross_in'] = 1
        
train_t_3['link_id'] = train_t_3['link_id'].astype(str)  
train_t_4_new = pd.merge(train_t_4_new, train_t_3, on='link_id', how='left')
train_t_4_new.drop(['in_top', 'out_top'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Merging table 4 with table 5<jupyter_code>join_incount= train_t_4_new[['intersection_id', 'tollgate_id', 'cross_in']].groupby([
    'intersection_id', 'tollgate_id']).cross_in.sum().reset_index().rename(columns={
    'cross_in':'inlink_cross_count'})

join_outcount = train_t_4_new[['intersection_id', 'tollgate_id', 'cross_out']].groupby([
    'intersection_id', 'tollgate_id']).cross_out.sum().reset_index().rename(columns={
    'cross_out':'outlink_cross_count'})

final = pd.merge(join_incount, join_outcount, on=['intersection_id', 'tollgate_id'], how='left')

_len = train_t_4_new[['intersection_id', 'tollgate_id', 'length']].groupby([
    'intersection_id', 'tollgate_id']).length.sum().reset_index()

final = pd.merge(final, _len, on=['intersection_id', 'tollgate_id'], how='left')

link_count = train_t_4_new[['intersection_id', 'tollgate_id']].groupby([
    'intersection_id', 'tollgate_id']).size().reset_index().rename(columns={0:'link_count'})

final = pd.merge(final, link_count, on=['intersection_id', 'tollgate_id'], how='left')

lane1_length = train_t_4_new[train_t_4_new.lanes==1][['intersection_id', 'tollgate_id', 'length']].groupby([
    'intersection_id', 'tollgate_id']).length.sum().reset_index().rename(columns={'length':'lane1_length'})

final = pd.merge(final, lane1_length, on=['intersection_id', 'tollgate_id'], how='left')

lane1_count = train_t_4_new[train_t_4_new.lanes== 1][['intersection_id', 'tollgate_id']].groupby([
    'intersection_id', 'tollgate_id']).size().reset_index().rename(columns = {0:'lane1_count'})

final = pd.merge(final, lane1_count, on =['intersection_id', 'tollgate_id'], how='left')

lane2_length = train_t_4_new[train_t_4_new.lanes==2][['intersection_id', 'tollgate_id', 'length']].groupby([
    'intersection_id', 'tollgate_id']).length.sum().reset_index().rename(columns={'length':'lane2_length'})

final = pd.merge(final, lane2_length, on=['intersection_id', 'tollgate_id'], how='left')

lane2_count = train_t_4_new[train_t_4_new.lanes== 2][['intersection_id', 'tollgate_id']].groupby([
    'intersection_id', 'tollgate_id']).size().reset_index().rename(columns = {0:'lane2_count'})

final = pd.merge(final, lane2_count, on =['intersection_id', 'tollgate_id'], how='left')

lane3_length = train_t_4_new[train_t_4_new.lanes==3][['intersection_id', 'tollgate_id', 'length']].groupby([
    'intersection_id', 'tollgate_id']).length.sum().reset_index().rename(columns={'length':'lane3_length'})

final = pd.merge(final, lane3_length, on=['intersection_id', 'tollgate_id'], how='left')

lane3_count = train_t_4_new[train_t_4_new.lanes==3][['intersection_id', 'tollgate_id']].groupby([
    'intersection_id', 'tollgate_id']).size().reset_index().rename(columns = {0:'lane3_count'})

final = pd.merge(final,lane3_count,on =['intersection_id', 'tollgate_id'] ,how='left')

lane4_length = train_t_4_new[train_t_4_new.lanes==4][['intersection_id', 'tollgate_id', 'length']].groupby([
    'intersection_id', 'tollgate_id']).length.sum().reset_index().rename(columns={'length':'lane4_length'})

final = pd.merge(final, lane4_length, on=['intersection_id', 'tollgate_id'],how='left')

lane4_count = train_t_4_new[train_t_4_new.lanes==4][['intersection_id', 'tollgate_id']].groupby([
    'intersection_id', 'tollgate_id']).size().reset_index().rename(columns = {0:'lane4_count'})

final = pd.merge(final,lane4_count,on =['intersection_id', 'tollgate_id'] ,how='left')

final.fillna(0, inplace=True)

train_t_5 = pd.merge(train_t_5, final, on=['intersection_id', 'tollgate_id'], how='left')

test_t_5_dup = pd.merge(test_t_5_dup, final, on=['intersection_id', 'tollgate_id'], how='left')<jupyter_output><empty_output><jupyter_text>Formatting starting and end time<jupyter_code>def time_period(data, start_time, end_time):
    st = data[start_time].apply(lambda x: x.strftime("%Y-%m-%d %H:%M:%S"))
    et = data[end_time].apply(lambda x: x.strftime("%Y-%m-%d %H:%M:%S"))
    data['time_window'] = '[' + st + ',' + et + ')'
    
    return data.drop([start_time, end_time], axis=1)
test_t_5_dup['end'] = test_t_5_dup['starting_time'] + pd.DateOffset(minutes=20)
train_t_5['end'] = train_t_5['starting_time'] + pd.DateOffset(minutes=20)
test_t_5_dup = time_period(test_t_5_dup, 'starting_time', 'end')
train_t_5 = time_period(train_t_5, 'starting_time', 'end')
test_t_5_dup = test_t_5_dup.set_index(['intersection_id', 'tollgate_id', 'time_window'])
train_t_5 = train_t_5.set_index(['intersection_id','tollgate_id','time_window'])
train_t_5.shape
test_t_5_columns, train_t_5_columns = list(test_t_5_dup.columns.values), list(train_t_5.columns.values)
m_t5 =  [data for data in train_t_5_columns if data not in test_t_5_columns]
for label in m_t5:
    test_t_5_dup[label] = 0
test_t_5_dup = test_t_5_dup[train_t_5_columns]<jupyter_output><empty_output><jupyter_text>Handling the missing values with mean value in table 5<jupyter_code>def fill_nullvalues(data):
    return data.fillna(data.mean())

test_t_5_dup = fill_nullvalues(test_t_5_dup)

train_t_5 =fill_nullvalues(train_t_5)
print(test_t_5_dup.shape, train_t_5.shape)
test_t_5_dup.to_csv("data/preprocessing_data/task1_preprocess_test_data.csv")
train_t_5.to_csv("data/preprocessing_data/task1_preprocess_training_data.csv")
train_t_5.columns.values
test_t_5_dup.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1470501387822595, 'fraction_numerical': 0.02870135238882655, 'mean_word_length': 4.759863945578231, 'pattern_counts': {'":': 0, '<': 72, '<?xml version=': 0, '>': 73, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '497430', 'license': 'MIT', 'n_tokens_mistral': 6574, 'n_tokens_neox': 6337, 'n_words': 908, 'path': 'task1_data_preprocessing.ipynb', 'repo_name': 'agnivad/Highway-Tollgates-Traffic-Flow-Prediction', 'size': '16933'}