<jupyter_start><jupyter_text>ZVV Transit Notebook The point of this notebook is to come up with a structured file, which includes the necessary stations and lines needed for the Mister X Zürich application.<jupyter_code># Imports
import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>At first we want to specify, which lines we want to look and and get their corresponding route ids and their type of transportation.<jupyter_code>routes = pd.read_csv(r'routes.txt')
routes = routes[["route_id", "route_short_name"]]
line_labels = np.array(["2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15",
                        "31", "32", "33","39", "40", "69", "72","80", "89"])
routes = routes[routes.route_short_name.isin(line_labels)]

tram_routes = routes[routes.route_id.str.startswith("1-")].assign(route_type="tram")
bus_routes_2 = routes[routes.route_id.str.startswith("2-")].assign(route_type="bus")
bus_routes_3 = routes[routes.route_id.str.startswith("3-")].assign(route_type="bus")
bus_routes_4 = routes[routes.route_id.str.startswith("4-")].assign(route_type="bus")

routes = pd.concat([tram_routes, bus_routes_2, bus_routes_3, bus_routes_4], sort=False)
routes
trips = pd.read_csv(r'trips.txt')
trips = trips[["route_id","trip_id"]]
trips = trips[trips.route_id.isin(routes.route_id)]
trips
trips_by_route_id = trips.groupby(["route_id"])["trip_id"].apply(list).reset_index(name='trip_ids')
trips_by_route_id
stop_times = pd.read_csv(r'stop_times.txt')
stop_times = stop_times[["trip_id","stop_id","stop_sequence"]]
stop_times = stop_times[stop_times.trip_id.isin(trips.trip_id)]
stop_times
ordered_stops_by_trip_id = stop_times.groupby(["trip_id"])["stop_id"].apply(list).reset_index(name='ordered_stop_ids')
ordered_stops_by_trip_id
ordered_stops_by_trip_id['length'] = ordered_stops_by_trip_id["ordered_stop_ids"].apply(len)
ordered_stops_by_trip_id
def get_trip_length(x):
    return ordered_stops_by_trip_id.loc[ordered_stops_by_trip_id["trip_id"] == x]["length"].to_numpy()[0]

def choose_element_with_max_length_list(x):
    max_trip = x[0]
    for trip in x:
        if get_trip_length(trip) > get_trip_length(max_trip):
            max_trip = trip
    return max_trip
trips_by_route_id["max_trip_id"] = trips_by_route_id["trip_ids"].apply(choose_element_with_max_length_list)
trips_by_route_id
def get_stops(route_id):
    max_trip_id = trips_by_route_id.loc[trips_by_route_id["route_id"] == route_id]["max_trip_id"].to_numpy()[0]
    ordered_stops = ordered_stops_by_trip_id.loc[ordered_stops_by_trip_id["trip_id"] == max_trip_id]["ordered_stop_ids"].to_numpy()[0]
    return ordered_stops
    
lines_tram_bus = routes
lines_tram_bus["ordered_stops"] = routes["route_id"].apply(get_stops)
lines_tram_bus = lines_tram_bus.reset_index(drop=True)
lines_tram_bus
# Adding simple Train lines
simple_train_lines = {'route_id':['3-S4-P-j21-1', '3-S6-P-j21-1', '3-S9-P-j21-1', '3-S10-P-j21-1', '3-S16-P-j21-1', '3-S24-P-j21-1'],
                     'route_short_name':['S4', 'S6', 'S9', 'S10', 'S16', 'S24'],
                     'route_type':['train', 'train', 'train', 'train', 'train', 'train'],
                     'ordered_stops':[['gen:23026:2550:1:0', 'gen:23026:2551:1:0'],
                                     ['gen:23026:2346:1:1', 'gen:23026:3034:1:1'],
                                     ['gen:23026:3039:1:0', 'gen:23026:2575:1:0'],
                                     ['gen:23026:2680:1:0','gen:23026:2551:1:0','gen:23026:412:1:0'],
                                     ['gen:23026:3041:1:0', 'gen:23026:3039:1:0', 'gen:23026:412:1:0', 'gen:23026:3029:1:11', 'gen:23026:3034:1:1', 'gen:23026:3027:1:50'],
                                     ['gen:23026:3044:1:0', 'gen:23026:3026:1:0','gen:23026:3042:1:0','gen:23026:412:1:0','gen:23026:3043:1:10']]}
simple_train_lines = [pd.Series(['3-S4-P-j21-1', 'S4', 'train', ['gen:23026:2550:1:0', 'gen:23026:2551:1:0']], index=lines_tram_bus.columns ) ,
                      pd.Series(['3-S6-P-j21-1', 'S6', 'train', ['gen:23026:2346:1:1', 'gen:23026:3034:1:1']], index=lines_tram_bus.columns ) ,
                      pd.Series(['3-S9-P-j21-1', 'S9', 'train', ['gen:23026:3039:1:0', 'gen:23026:2575:1:0']], index=lines_tram_bus.columns ) ,
                      pd.Series(['3-S10-P-j21-1', 'S10', 'train', ['gen:23026:2680:1:0','gen:23026:2551:1:0','gen:23026:412:1:0']], index=lines_tram_bus.columns ) ,
                      pd.Series(['3-S16-P-j21-1', 'S16', 'train', ['gen:23026:3041:1:0', 'gen:23026:3039:1:0', 'gen:23026:412:1:0', 'gen:23026:3029:1:11', 'gen:23026:3034:1:1', 'gen:23026:3027:1:50']], index=lines_tram_bus.columns ) ,
                      pd.Series(['3-S24-P-j21-1', 'S24', 'train', ['gen:23026:3044:1:0', 'gen:23026:3026:1:0','gen:23026:3042:1:0','gen:23026:412:1:0','gen:23026:3043:1:10']], index=lines_tram_bus.columns )]

lines_tram_bus_train = lines_tram_bus.append(simple_train_lines, ignore_index=True)
lines_tram_bus_train
stops = pd.read_csv(r'stops.txt')
stops = stops[["stop_id","stop_name","stop_lat","stop_lon"]]
stops
def get_station_names(ordered_stops):
    ordered_stop_names = []
    for stop_id in ordered_stops:
        stop_name = stops.loc[stops["stop_id"] == stop_id]["stop_name"].to_numpy()[0]
        ordered_stop_names.append(stop_name)
    return ordered_stop_names

lines_tram_bus_train["ordered_stop_names"] = lines_tram_bus_train["ordered_stops"].apply(get_station_names)
lines_tram_bus_train
lines_tram_bus_train_csv = lines_tram_bus_train.to_csv("lines_tram_bus_train", index=False)
list_of_stations = []
for station_list in lines_tram_bus_train["ordered_stop_names"]:
    list_of_stations += station_list
list_of_stations = list(set(list_of_stations))
list_of_stations
len(list_of_stations)
station_info = pd.DataFrame(columns = ["name", "stop_lat", "stop_lon", "reachable_by_tram", "reachable_by_bus", "reachable_by_train"])
station_info["name"] = list_of_stations

def get_stop_lat(stop_name):
    return stops.loc[stops["stop_name"] == stop_name]["stop_lat"].to_numpy()[0]

def get_stop_lon(stop_name):
    return stops.loc[stops["stop_name"] == stop_name]["stop_lon"].to_numpy()[0]

def get_stop_id(stop_name):
    return stops.loc[stops["stop_name"] == stop_name]["stop_id"].to_numpy()[0]

station_info["stop_lat"] = station_info["name"].apply(get_stop_lat)
station_info["stop_lon"] = station_info["name"].apply(get_stop_lon)
#station_info["stop_id"] = station_info["name"].apply(get_stop_id)

station_info["reachable_by_tram"] = np.empty((len(station_info), 0)).tolist()
station_info["reachable_by_bus"] = np.empty((len(station_info), 0)).tolist()
station_info["reachable_by_train"] = np.empty((len(station_info), 0)).tolist()

station_info = station_info.set_index(["name"])

station_info

for i in range(lines_tram_bus_train.shape[0]):
    ordered_stop_names = lines_tram_bus_train.loc[i]["ordered_stop_names"]
    if lines_tram_bus_train.loc[i]["route_type"]=="tram":
        station_info.loc[ordered_stop_names[0],"reachable_by_tram"].extend([ordered_stop_names[1]])
        for j in range(1,len(lines_tram_bus_train.loc[i]["ordered_stop_names"])-1):
            station_info.loc[ordered_stop_names[j],"reachable_by_tram"].extend([ordered_stop_names[j-1],ordered_stop_names[j+1]])
        station_info.loc[ordered_stop_names[-1],"reachable_by_tram"].extend([ordered_stop_names[-2]])
    elif lines_tram_bus_train.loc[i]["route_type"]=="bus":
        station_info.loc[ordered_stop_names[0],"reachable_by_bus"].extend([ordered_stop_names[1]])
        for j in range(1,len(lines_tram_bus_train.loc[i]["ordered_stop_names"])-1):
            station_info.loc[ordered_stop_names[j],"reachable_by_bus"].extend([ordered_stop_names[j-1],ordered_stop_names[j+1]])
        station_info.loc[ordered_stop_names[-1],"reachable_by_bus"].extend([ordered_stop_names[-2]])
    elif lines_tram_bus_train.loc[i]["route_type"]=="train":
        station_info.loc[ordered_stop_names[0],"reachable_by_train"].extend([ordered_stop_names[1]])
        for j in range(1,len(lines_tram_bus_train.loc[i]["ordered_stop_names"])-1):
            station_info.loc[ordered_stop_names[j],"reachable_by_train"].extend([ordered_stop_names[j-1],ordered_stop_names[j+1]])
        station_info.loc[ordered_stop_names[-1],"reachable_by_train"].extend([ordered_stop_names[-2]])
    else:
        print("ERROR")
 
station_info
def make_values_unique(l):
    return list(set(l))

    
station_info["reachable_by_tram"] = station_info["reachable_by_tram"].apply(make_values_unique)
station_info["reachable_by_bus"] = station_info["reachable_by_bus"].apply(make_values_unique)
station_info["reachable_by_train"] = station_info["reachable_by_train"].apply(make_values_unique)
station_info
station_info_csv = station_info.to_csv("station_info", index=True)
station_dict = station_info.reset_index().to_dict('records')
for idx,station in enumerate(station_dict):
    station["id"]= idx+1

def find_station_id(name):
    for station in station_dict:
        if station["name"]== name:
            return station["id"]
        
for station in station_dict:
    temp_bus = []
    temp_tram = []
    temp_train = []
    for bus in station["reachable_by_bus"]:
        temp_bus.append(find_station_id(bus))
    for tram in station["reachable_by_tram"]:
        temp_tram.append(find_station_id(tram))
    for train in station["reachable_by_train"]:
        temp_train.append(find_station_id(train))
    station["reachable_by_bus"] = temp_bus
    station["reachable_by_tram"] = temp_tram
    station["reachable_by_train"] = temp_train
    

print(station_dict)
import json
with open('station_dict.json', 'w', encoding='utf-8') as outfile:
    json.dump(station_dict, outfile, ensure_ascii=False)<jupyter_output>[{'name': '<NAME>', 'stop_lat': 47.3997177825593, 'stop_lon': 8.51832858176617, 'reachable_by_tram': [], 'reachable_by_bus': [98, 145], 'reachable_by_train': [], 'id': 1}, {'name': '<NAME>', 'stop_lat': 47.3977719825142, 'stop_lon': 8.4487271135429, 'reachable_by_tram': [92, 278], 'reachable_by_bus': [], 'reachable_by_train': [], 'id': 2}, {'name': '<NAME>', 'stop_lat': 47.4059499396091, 'stop_lon': 8.53764236037743, 'reachable_by_tram': [35, 124], 'reachable_by_bus': [], 'reachable_by_train': [], 'id': 3}, {'name': '<NAME>', 'stop_lat': 47.4167890961376, 'stop_lon': 8.5227572761175, 'reachable_by_tram': [], 'reachable_by_bus': [96, 207], 'reachable_by_train': [], 'id': 4}, {'name': '<NAME>', 'stop_lat': 47.3740091853096, 'stop_lon': 8.49521492950256, 'reachable_by_tram': [], 'reachable_by_bus': [21, 67], 'reachable_by_train': [], 'id': 5}, {'name': '<NAME>', 'stop_lat': 47.3702920484894, [...]
{'fraction_non_alphanumeric': 0.175978965161048, 'fraction_numerical': 0.07531223589069397, 'mean_word_length': 5.862113402061856, 'pattern_counts': {'":': 3, '<': 14, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '318026', 'license': 'Apache-2.0', 'n_tokens_mistral': 4559, 'n_tokens_neox': 4163, 'n_words': 505, 'path': 'data_mining_stations/ZVV-transit.ipynb', 'repo_name': 'sopra-fs21-group-08/sopra-fs21-group08-server', 'size': '10747'}