<jupyter_start><jupyter_text>Import libraries<jupyter_code>from urllib.request import urlretrieve<jupyter_output><empty_output><jupyter_text>Read Files<jupyter_code>def get_file(url,file_name):
    
    return urlretrieve(url,file_name)

subway_urls = ['https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/6664420f-316f-4f94-9ba4-d4b4677aeea9',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/d1159888-0035-45a0-b238-86b546555ac0',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/240d8e8c-d300-4f91-b94f-cbb3d136d25e',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/98d4ac77-aa9f-40a3-97ee-6fc9070da252',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/92e7649a-cf2f-4ac7-9802-b7f92f00b384',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/61412f10-656b-4992-9a1a-a156dcf2f6c7',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/69a6db37-7982-49c7-8dbc-56919a92afca',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/10080217-8022-41c0-a8ba-2a455b8d9d6e',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/a731c4bb-630a-4530-b590-b325f9b4ef9b',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/5dd04139-6dd0-46c4-99a8-45e7b7607e5c',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/84d2cc1f-296d-4593-a6eb-504a7a5c769a',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/83e7a4e7-fa62-4932-afc2-8c8ca2235835',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/d9bf907b-413c-4028-b9bc-0b72a66bf11e',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/6767e18c-cbb3-461a-b4ae-ba202c9b73e4',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/e91ccb8b-9b0a-4479-adcd-447bfb298ab5',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/718061ee-8351-4cbb-818a-691f03e92041',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/b96a8119-9241-4392-92c7-f2fb6f4eff5f',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/5e22688c-e841-4249-8a18-243dc70307c1',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/9d4cae40-1a5e-4dea-b3c2-95818ae0b521',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/5e3067a3-ff0a-4ecf-a25b-5cf59e7adc25',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/b4e5c8d0-36b3-4e24-8f24-30bf59fded2d',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/9a824dba-20cc-40b1-8f26-778a34a0f3a8',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/e6bac74e-2da2-4429-a76f-202eba3d9193',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/0511879f-3233-4a42-8c28-93b432132c8b',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/447b4a5a-f696-4f05-86c0-9602f56922e5',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/a302fcab-81a1-4142-b0ec-031b0666c1df',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/655a138c-d381-4fe7-b3b3-a6620825161f',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/34d9619f-0239-4dad-a598-b6bc71ce1071',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/b6557580-a0f4-4c96-9ce2-82657b62e88a',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/e2a5e386-ddf7-4416-8e84-c3508c4f9a4f',
               'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/fd837bd2-85ed-485e-ba02-46e29af52024']
file_names=['sub_01_18','sub_02_18','sub_03_18','sub_04_18','sub_05_18','sub_06_18','sub_07_18',
           'sub_08_18','sub_09_18','sub_10_18','sub_11_18','sub_12_18','sub_01_19','sub_02_19',
            'sub_03_19','sub_04_19','sub_05_19']


all_sub_df = []
for url, file in zip(subway_urls, file_names):
    data = pd.read_excel(get_file(url,file)[0],ignore_index=True)
    # store DataFrame in list
    all_sub_df.append(data)   
all_sub_df = pd.concat(all_sub_df)
all_sub_df.head()<jupyter_output><empty_output><jupyter_text>Data Cleansing<jupyter_code>sub_delay_code = 'https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/fece136b-224a-412a-b191-8d31eb00491e'
delay_code_df1 = pd.read_excel(get_file(sub_delay_code,'delay_code')[0], ignore_index=True,skiprows=1)
delay_code_df1=delay_code_df1[['SUB RMENU CODE','CODE DESCRIPTION']]
delay_code_df1.head()
# Put codes and description in a dictionary 
#and replace the codes in the incident column with it's corresponding description.

d = dict(zip(delay_code_df1['SUB RMENU CODE'], delay_code_df1['CODE DESCRIPTION']))

all_sub_df['Incident'] = [d.get(x,'Unknown') for x in all_sub_df['Code']]

all_sub_df.Line.unique()
all_sub_df.isnull().sum()
all_sub_df.info()

all_sub_df.dropna(inplace=True)
# list of stations in each line.
line_1 = ['Vaughan Metropolitan''Highway 407','Pioneer Village','York University','Finch West'
'Downsview Park','Sheppard West','Wilson','Yorkdale','Lawrence West','Glencairn','Eglinton West',
'St Clair West','Dupont','Spadina','St George','Museum','Queen\'s Park','St Patrick','Osgoode',
'St Andrew','Union','King','Queen','Dundas','College','Wellesley','Bloor-Yonge','Rosedale',
'Summerhill','St Clair','Davisville''Eglinton','Lawrence','York Mills',
'Sheppard-Yonge','North York Centre','Finch']
line_2 = ['Kipling', 'Islington','Royal York','Old Mill','Jane','Runnymede','High Park',
'Keele','Dundas West','Lansdowne','Dufferin','Ossington','Christie',
'Bathurst','Spadina','St George','Bay','Bloor-Yonge','Sherbourne','Castle Frank',
'Broadview','Chester','Pape','Donlands','Greenwood','Coxwell','Woodbine','Main Street',
 'Victoria Park','Warden','Kennedy']
line_3 = ['Kennedy','Lawrence East','Ellesmere','Midland','Scarborough Centre','McCowan']
line_4 = ['Sheppard-Yonge','Bayview','Bessarion','Leslie','<NAME>']

# The four subway lines are 1,2,3 and 4. 
# there are entries with other names as well which need to be categoriezed into these four lines.
# lists of entries belonging to the same line are created.
# The stops near a particular station are included in the line going through that station.

BD_line = ['BD','BD LINE','BLOOR DANFORTH','45 KIPLING','31 GREENWOOD','126 CHRISTIE','B/D',
           '37 ISLINGTON','29 DUFFERIN','73 ROYAL YORK','BLOOR DANFORTH LINE','341 KEELE',
          '11 BAYVIEW', '63 OSSINGTON','BLOOR DANFORTH LINES']
YU_line = ['YU','95 YORK MILLS','104 FAYWOOD','60 STEELES WEST','36 FINCH WEST','504 KING','116 MORNINGSIDE',
          '32 EGLINTON WEST','YU LINE','YUS','YONGE UNIVERSITY SERVI','504',]
SRT_line = ['SRT','9 BELLAMY','129 MCCOWAN NORTH','16 MCCOWAN']
SHP_line = ['SHP','SHEPPARD','25 DON MILLS','YU/SHEP','85 SHEPPARD EAST']
YU_BD = ['YU/BD','YU / BD','YU/ BD','BD/YU','BD/YUS','YU - BD','YU-BD','510 SPADINA','YU - BD LINE','YU BD']


all_sub_df.Line=all_sub_df['Line'].apply(lambda x: 'BD'  if x in BD_line
                                           else ('YU' if(x in YU_line)
                                             else ('SRT' if(x in SRT_line)
                                             else ('SHP' if (x in SHP_line)
                                                   else ('YU_BD' if (x in YU_BD)
                                                         else('Unknown' if (x.isdigit())
                                                 else x
                                                        ))))))<jupyter_output><empty_output><jupyter_text>percentage of missing values.<jupyter_code>all_sub_df.Line.unique()<jupyter_output><empty_output><jupyter_text>The missing values in column "Line" is relatively easy to fill. The sation where the delay causing incident occured can reveal the line.<jupyter_code># to do
# avg delay on days of the week
# month, season 
# time of teh day - time slots
# resaons top 5
# top 5 stations
# train number with teh highest number of dealays
# cause categorised into passenger caused, mechanical, other

all_sub_df.Bound.unique()
# The unfamiliar entries in the Bound column are negligible number 
#and can be either rtemoived or put in a new actegory called "unknown". 
# on close inspection it was found that the strange entries in the Bound column has no correlation with any of the other columns.
all_sub_df[all_sub_df['Vehicle']==0].sum()
all_sub_df.groupby('Vehicle')['Min Delay'].average()
all_sub_df[all_sub_df['Min Delay']==0]<jupyter_output><empty_output><jupyter_text>Clean Direction data. Cleaning the values of column Directrion  Visualization * more than 5 minute delay distribution.* comparison of subway bus and streetcar.* location with highest number of delays.* biggest reason for delays.* time of the day with the most delays.* day of the week with the most delays.<jupyter_code>all_df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14571614019904802, 'fraction_numerical': 0.09108610990913024, 'mean_word_length': 3.853018372703412, 'pattern_counts': {'":': 0, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 32, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 46, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '506579', 'license': 'MIT', 'n_tokens_mistral': 3956, 'n_tokens_neox': 3514, 'n_words': 465, 'path': 'TTC_Subway_delay.ipynb', 'repo_name': 'Nisha-C/TTC-Subway-Delay-Analysis-with-Visualization', 'size': '9247'}