<jupyter_start><jupyter_text>Step 1: Import required libraries and packages<jupyter_code># import required libraries
import pandas as pd
import numpy as np
import datetime
from geopy.distance import distance
import time
pd.set_option('mode.chained_assignment', None)

# set directories
resource_directory = "00_Ntbk_Resources\\00_DataCollectionProcessingCleaning\\PMPML_BusRoutes_July2019\\"
result_directory = "00_Ntbk_Resources\\01_DataAnalysis\\00_ProcessedData\\PMPML_BusRoutes_July2019\\"<jupyter_output><empty_output><jupyter_text>Step 2: Data Collection source: http://opendata.punecorporation.org/Citizen/CitizenDatasets/Indextags: PMPMLtitle: PMPML Bus Routes - July 2019file type: .zipsize: 8.81 MB  Step 3: Data Processing and Cleaning<jupyter_code># import trip/service calendar information into the dataframe
df_TripCalendar = pd.read_csv(f"{resource_directory}calendar.txt")
df_TripCalendar
# import bus stop information into the dataframe
df_BusStops = pd.read_csv(f"{resource_directory}stops.txt")
df_BusStops = df_BusStops.drop(columns=["zone_id","stop_url","stop_desc","stop_code","location_type","parent_station","stop_timezone","wheelchair_boarding"])
df_BusStops
# check dataframe for NaN values
df_BusStops[df_BusStops.isna().any(axis=1)]
# import bus route information into the dataframe
df_BusRouteShapes = pd.read_csv(f"{resource_directory}shapes.txt")
df_BusRouteShapes
# calculate the distance for each route and add it to separate column of dataframe
df_temp = df_BusRouteShapes.groupby("shape_id").transform(lambda x : x.shift(1))[["shape_pt_lat", "shape_pt_lon"]]

list_dist_traveled = []
for a,b,c,d in zip(df_BusRouteShapes["shape_pt_lat"], df_BusRouteShapes["shape_pt_lon"], df_temp["shape_pt_lat"], df_temp["shape_pt_lon"]):
    if not np.isnan(c) :
        list_dist_traveled.append(distance([a,b],[c,d]).km)
    else :
        list_dist_traveled.append(0)
        
df_BusRouteShapes ["shape_dist_traveled"] = list_dist_traveled
df_BusRouteShapes["trip_distance"] = df_BusRouteShapes.groupby("shape_id")["shape_dist_traveled"].transform(sum)
df_BusRouteShapes
# import bus trip information into the dataframe and add trip distance information in a new column of dataframe
df_BusTrips = pd.read_csv(f"{resource_directory}trips.txt")
df_BusTrips = df_BusTrips.drop(columns=["trip_short_name","block_id","wheelchair_accessible","bikes_allowed","duty","duty_sequence_number","run_sequence_number"])
df_BusTrips = df_BusTrips.join(df_BusRouteShapes.set_index("shape_id").groupby("shape_id")[["trip_distance"]].max().reset_index("shape_id").set_index("shape_id"), on="shape_id")
df_BusTrips
# import bus stop times information into the dataframe
df_BusStopTimes = pd.read_csv(f"{resource_directory}stop_times.txt")
df_BusStopTimes = df_BusStopTimes.drop(columns=["stop_headsign","pickup_type","drop_off_type","shape_dist_traveled","timepoint"])
df_BusStopTimes
# information from this dataframe needs to verify because this dataframe has a lot of information and chances of human error are also high
df_BusStopTimes[df_BusStopTimes.isnull().any(axis=1)]<jupyter_output><empty_output><jupyter_text>To verify the information in dataframe we are going to run some filters and check whether wrong information is present or not.\We are also going to fix the wrong information with the average value of logically correct information, while doing this we also need to take care that already correct information is not getting changed.\1) Check whether arrival_time or departure time are correctly entered i.e. they are in 24hr format.\2) Check duration between arrival and departure time for each stop, it should be realistic\3) Check duration between stop to stop arrival time, it should not be more than 2.5 to 3hrs considering traffic conditions<jupyter_code># check whether arrival_time or departure time is correctly entered i.e. they are in 24hr format.
df_temp1 = df_BusStopTimes[(df_BusStopTimes["arrival_time"] > '23:59:59') | (df_BusStopTimes["departure_time"] > '23:59:59')]
df_temp1["trip_id"].unique()
# there are some entries which have arrival or departure time greater than 23:59:59
# the wrong information is found where bus trips are started late at night and end on next day early in the morning
# so time mentioned beyond 23:59:59 is nothing but early morning on the next day
# let's check and replace such entries with correct time values

index_bad1 = df_BusStopTimes[(df_BusStopTimes["trip_id"].str[-6:-2] >= str(2040)) & (df_BusStopTimes["arrival_time"].str.startswith("24:") | df_BusStopTimes["arrival_time"].str.startswith("25:"))].index
index_bad2 = df_BusStopTimes[(df_BusStopTimes["trip_id"].str[-6:-2] >= str(2040)) & (df_BusStopTimes["departure_time"].str.startswith("24:") | df_BusStopTimes["departure_time"].str.startswith("25:"))].index

df_BusStopTimes.loc[index_bad1,"arrival_time"] = df_BusStopTimes.iloc[index_bad1]["arrival_time"].str.replace('^24:','00:').str.replace('^25:','01:').to_list()
df_BusStopTimes.loc[index_bad2,"departure_time"] = df_BusStopTimes.iloc[index_bad2]["departure_time"].str.replace('^24:','00:').str.replace('^25:','01:').to_list()
# for most of the trips from Swargate to Pune Station arrival and departure times are wrong which results in longer trip duration
# so lets filter out those bad trip ids and replace their arrival and departure times with average values of good trip_ids

df_temp1 = df_temp1[df_temp1["trip_id"].str.contains("NORMAL_5_Swargate To Pune Station_Up")]

list_BadTripIds = df_temp1["trip_id"].unique().tolist()

df_temp2 = df_BusStopTimes[df_BusStopTimes["trip_id"].str.contains("NORMAL_5_Swargate To Pune Station_Up")]
df_temp2.reset_index(inplace=True, drop =True)

df_temp3 = df_temp2[~ df_temp2["trip_id"].isin(list_BadTripIds)]
df_temp3.reset_index(inplace=True, drop =True)

df_temp3["deparr_time_diff"] = pd.to_timedelta(df_temp3["departure_time"]) - pd.to_timedelta(df_temp3["arrival_time"])
df_temp3["arr_time_diff"] = df_temp3.groupby("trip_id")["arrival_time"].transform(lambda x : pd.to_timedelta(x) - pd.to_timedelta(x.shift()))
df_temp3["avg_deparr_time_diff"] = df_temp3.groupby("stop_sequence")["deparr_time_diff"].transform(lambda x : pd.to_timedelta(x).sum()/ len(x))
df_temp3["avg_arr_time_diff"] = df_temp3.groupby("stop_sequence")["arr_time_diff"].transform(lambda x : pd.to_timedelta(x).sum()/ len(x))
df_temp3["avg_arr_time_diff"] = df_temp3.groupby("trip_id")["avg_arr_time_diff"].transform(lambda x : x.cumsum())

list_avg_deparr_time_diff = df_temp3[df_temp3["trip_id"] == "NORMAL_5_Swargate To Pune Station_Up-1010_0"]["avg_deparr_time_diff"].astype(str).str[7:15].tolist()
list_avg_arr_time_diff = df_temp3[df_temp3["trip_id"] == "NORMAL_5_Swargate To Pune Station_Up-1010_0"]["avg_arr_time_diff"].astype(str).str[7:15].tolist()
# this function will replace the wrong arrival and departure times of trips considering average duration found in other trips with correct trip ids
def changeTimings(df) :
    df_temp1 = pd.DataFrame()
    df_temp1["avg_arr_time_diff"] = list_avg_arr_time_diff
    
    list_arrival_time = (pd.to_timedelta(df["arrival_time"].tolist()[1]) + pd.to_timedelta(df_temp1["avg_arr_time_diff"])).astype(str).str[7:15].tolist()
    df["arrival_time"][2:] = list_arrival_time[2:]
    
    df_temp1["avg_deparr_time_diff"] = list_avg_deparr_time_diff
    df["departure_time"] = ((pd.to_timedelta(df.reset_index(drop=True)["arrival_time"]) + pd.to_timedelta(df_temp1["avg_deparr_time_diff"])).astype(str).str[7:15].tolist())

    return df
df_BusStopTimes[df_BusStopTimes["trip_id"].isin(list_BadTripIds)] = df_BusStopTimes[df_BusStopTimes["trip_id"].isin(list_BadTripIds)].groupby("trip_id").apply(lambda x: changeTimings(x))
df_BusStopTimes
# still, some entries need to be corrected individually
df_temp1 = df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_181C_Na Ta Wadi To Kondhwa Bk_Up-1450_0"]
df_temp1["deparr_time_diff"] = pd.to_timedelta(df_temp1["departure_time"]) - pd.to_timedelta(df_temp1["arrival_time"])
df_temp1["arr_time_diff"] = pd.to_timedelta(df_temp1["arrival_time"]) - pd.to_timedelta(df_temp1["arrival_time"].shift())
df_temp1.iloc[0,6] = pd.to_timedelta("00:00:00")
df_temp1.iloc[1,6] = pd.to_timedelta("00:01:15")
df_temp1["arr_time_diff"] = df_temp1.groupby("trip_id")["arr_time_diff"].transform(lambda x: x.cumsum())
df_temp1["arrival_time"] = (pd.to_timedelta(df_temp1.iloc[0,1]) + pd.to_timedelta(df_temp1["arr_time_diff"])).astype(str).str[7:15].tolist()
df_temp1["departure_time"] = (pd.to_timedelta(df_temp1["arrival_time"]) + pd.to_timedelta(df_temp1["deparr_time_diff"])).astype(str).str[7:15].tolist()
df_temp1= df_temp1.drop(columns=["deparr_time_diff","arr_time_diff"])
df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_181C_Na Ta Wadi To Kondhwa Bk_Up-1450_0"] = df_temp1
print(df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_181C_Na Ta Wadi To Kondhwa Bk_Up-1450_0"])

df_temp1 = df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_181C_Na Ta Wadi To Kondhwa Bk_Up-1450_0"]
df_temp1["deparr_time_diff"] = pd.to_timedelta(df_temp1["departure_time"]) - pd.to_timedelta(df_temp1["arrival_time"])
df_temp1["arr_time_diff"] = pd.to_timedelta(df_temp1["arrival_time"]) - pd.to_timedelta(df_temp1["arrival_time"].shift())
df_temp1.iloc[0,6] = pd.to_timedelta("00:00:00")
df_temp1.iloc[1,6] =  pd.to_timedelta("00:01:15")
df_temp1["arr_time_diff"] = df_temp1.groupby("trip_id")["arr_time_diff"].transform(lambda x: x.cumsum())
df_temp1["arrival_time"] = (pd.to_timedelta(df_temp1.iloc[0,1]) + pd.to_timedelta(df_temp1["arr_time_diff"])).astype(str).str[7:15].tolist()
df_temp1["departure_time"] = (pd.to_timedelta(df_temp1["arrival_time"]) + pd.to_timedelta(df_temp1["deparr_time_diff"])).astype(str).str[7:15].tolist()
df_temp1= df_temp1.drop(columns=["deparr_time_diff","arr_time_diff"])
df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_181C_Na Ta Wadi To Kondhwa Bk_Up-1450_0"] = df_temp1
print(df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_181C_Na Ta Wadi To Kondhwa Bk_Up-1450_0"])

df_temp1 = df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_256_Someshwarwadi To PMC_Down-1945_0"]
df_temp1["deparr_time_diff"] = pd.to_timedelta(df_temp1["departure_time"]) - pd.to_timedelta(df_temp1["arrival_time"])
df_temp1["arr_time_diff"] = pd.to_timedelta(df_temp1["arrival_time"]) - pd.to_timedelta(df_temp1["arrival_time"].shift())
df_temp1.iloc[0,6] = pd.to_timedelta("00:00:00")
df_temp1.iloc[1,6] =  pd.to_timedelta("00:01:15")
df_temp1.iloc[2,6] =  pd.to_timedelta("00:01:15")
df_temp1.iloc[1,5] =  pd.to_timedelta("00:00:18")
df_temp1["arr_time_diff"] = df_temp1.groupby("trip_id")["arr_time_diff"].transform(lambda x: x.cumsum())
df_temp1["arrival_time"] = (pd.to_timedelta(df_temp1.iloc[0,1]) + pd.to_timedelta(df_temp1["arr_time_diff"])).astype(str).str[7:15].tolist()
df_temp1["departure_time"] = (pd.to_timedelta(df_temp1["arrival_time"]) + pd.to_timedelta(df_temp1["deparr_time_diff"])).astype(str).str[7:15].tolist()
df_temp1= df_temp1.drop(columns=["deparr_time_diff","arr_time_diff"])
df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_256_Someshwarwadi To PMC_Down-1945_0"] = df_temp1
print(df_BusStopTimes[df_BusStopTimes["trip_id"]=="NORMAL_256_Someshwarwadi To PMC_Down-1945_0"])
# verify once again before proceeding to next filter
df_temp1 = df_BusStopTimes[(df_BusStopTimes["arrival_time"] > '23:59:59') | (df_BusStopTimes["departure_time"] > '23:59:59')]
df_temp1
# as arrival and departure values are within range we can change the data type of both columns to datetime
df_BusStopTimes["arrival_time"] = df_BusStopTimes["arrival_time"].astype('datetime64[ns]')
df_BusStopTimes["departure_time"] = df_BusStopTimes["departure_time"].astype('datetime64[ns]')
df_BusStopTimes
# this function will update the date of entry to next day if arrival time is less than its previous arrival time
def changeDate(df):    
    changeIndex = df[df["arrival_time"].shift(-1) < df["arrival_time"]].index + 1
    if changeIndex.any() :
        df.loc[changeIndex[0] : df.tail(1).index[0],"arrival_time"] = df.loc[changeIndex[0] : df.tail(1).index[0],"arrival_time"] + datetime.timedelta(days=1)
    
    changeIndex = df[df["departure_time"].shift(-1) < df["departure_time"]].index + 1
    if changeIndex.any() :
        df.loc[changeIndex[0] : df.tail(1).index[0],"departure_time"] = df.loc[changeIndex[0] : df.tail(1).index[0],"departure_time"] + datetime.timedelta(days=1)
    
    return df
df_BusStopTimes = df_BusStopTimes.groupby("trip_id").apply(lambda x: changeDate(x))
df_BusStopTimes
# let's add a column to the dataframe which will show arrival time difference between two consecutive stops
# also, add column showing duration between arrival and departure time for each stop
df_BusStopTimes["stp2stp_arrival_time"] = df_BusStopTimes.groupby("trip_id")["arrival_time"].transform(lambda x : x.shift(-1) - x)
df_BusStopTimes["deparr_time_diff"] = df_BusStopTimes["departure_time"] - df_BusStopTimes["arrival_time"]
df_BusStopTimes
# take a look at the maximum and 75% values of the stop to stop arrival duration as well as departure and arrival time difference
df_BusStopTimes.describe()
# there are entries where the time difference between arrival and departure time is greater than 6hrs, i.e. not possible 
# though 75% value is 30 seconds, for the safer side we'll assume duration as 3minutes and replace those with 75% value
# a similar technique can be applied for a stop to stop arrival time as well

df_BusStopTimes.loc[df_BusStopTimes[df_BusStopTimes["deparr_time_diff"] > datetime.timedelta(minutes = 3)].index,"deparr_time_diff"] =  df_BusStopTimes.describe().loc["75%", "deparr_time_diff"]
df_BusStopTimes.loc[df_BusStopTimes[df_BusStopTimes["stp2stp_arrival_time"] > datetime.timedelta(minutes = 30)].index,"stp2stp_arrival_time"]=  df_BusStopTimes.describe().loc["75%", "stp2stp_arrival_time"]
df_BusStopTimes
# now we have to update arrival and departure times according to updated/modified durations
df_BusStopTimes["stp2stp_arrival_time"] = df_BusStopTimes.groupby("trip_id")["stp2stp_arrival_time"].transform(lambda x : x.cumsum().shift().fillna(datetime.timedelta(minutes = 0)))
df_BusStopTimes
# this function will update the arrival time with the help of first entry and stop to stop arrival time duration value
def updateArrivalTime(df):
    df.loc[:,"arrival_time"] = df.iloc[0,1] +  df["stp2stp_arrival_time"]
    return df
df_BusStopTimes = df_BusStopTimes.groupby("trip_id").apply(lambda x: updateArrivalTime(x))
df_BusStopTimes
# similarly, update departure time as well
df_BusStopTimes.loc[:,"departure_time"] = df_BusStopTimes["arrival_time"]  +  df_BusStopTimes["deparr_time_diff"]
df_BusStopTimes
df_BusStopTimes["trip_bgn_time"] = df_BusStopTimes.groupby("trip_id")["arrival_time"].transform("first")
df_BusStopTimes["trip_end_time"] = df_BusStopTimes.groupby("trip_id")["arrival_time"].transform("last")
df_BusStopTimes["trip_duration"] = df_BusStopTimes["trip_end_time"] - df_BusStopTimes["trip_bgn_time"]
df_BusStopTimes.sort_values("trip_duration", ascending = True)
# let's drop this trip_id as it has only one stop_sequence
df_BusStopTimes = df_BusStopTimes.drop(df_BusStopTimes[df_BusStopTimes["trip_id"] == "NORMAL_107_Gharkul Vasahat Warje To Pimplegurav_Down-0530_0"].index[0])
df_BusStopTimes.reset_index(drop=True, inplace=True)
df_BusStopTimes
# let's verify the dataframe once again (check max and 75% values)
df_BusStopTimes["stp2stp_arrival_time"] = df_BusStopTimes.groupby("trip_id")["arrival_time"].transform(lambda x : x.shift(-1) - x)
df_BusStopTimes["deparr_time_diff"] = df_BusStopTimes["departure_time"] - df_BusStopTimes["arrival_time"]
df_BusStopTimes.describe()
# drop the duration columns as they are not required anymore 
df_BusStopTimes.drop(["stp2stp_arrival_time", "deparr_time_diff", "trip_bgn_time", "trip_end_time", "trip_duration"], axis =1, inplace =True)
df_BusStopTimes
# now that we've repaired the dataframes we should save it to resources folder for further analysis
df_TripCalendar.to_csv(f"{result_directory}calendar.txt", index=False)
df_BusStops.to_csv(f"{result_directory}stops.txt", index=False)
df_BusRouteShapes.to_csv(f"{result_directory}shapes.txt", index=False)
df_BusTrips.to_csv(f"{result_directory}trips.txt", index=False)
df_BusStopTimes.to_csv(f"{result_directory}stop_times.txt", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1339455157636976, 'fraction_numerical': 0.028772574227119683, 'mean_word_length': 8.481137550783517, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 17, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '493962', 'license': 'MIT', 'n_tokens_mistral': 6263, 'n_tokens_neox': 5935, 'n_words': 1043, 'path': '00_Ntbk_PuneBusRoutes_DataCollection,Processing,Cleaning.ipynb', 'repo_name': '2020mt93213/Pune_BusRoutes', 'size': '16335'}