Pointast 🗺️

{ "packages": ["pandas", "networkx", "openpyxl"], "files": { "/Data/routes/MetroDatabase.xlsm": "./MetroDatabase.xlsm" } }

import json import pandas as pd import networkx as nx import matplotlib.pyplot as plt import re database = pd.ExcelFile('./MetroDatabase.xlsm') # Initialize Stations Dictionary stations = {} stationsData = pd.read_excel(database, sheet_name="Station") stationsData['Line'] = stationsData['Line'].ffill(axis=0) stationsData = stationsData.groupby(['Line']) lineList = list(stationsData.groups.keys()) # Process each line for line in lineList: stations[line] = {} lineData = stationsData.get_group((line,)).copy() lineData.drop('Line', axis=1, inplace=True) lineData.set_index('Code', inplace=True) for station in lineData.index: stationName = f"{station} สถานี{lineData.loc[station, 'Thai']} - {lineData.loc[station, 'Eng']} Station" stations[line][station] = stationName # Initialize Interchange Dictionary interchange = {} interchangeData = pd.read_excel(database, sheet_name="Interchange") interchangeDict = interchangeData.transpose().to_dict() # Process each pair for pair in interchangeDict.values(): interchange[pair['From Station']] = pair['To Station'] # Initialize Prices Dictionary prices = {} # Process Pricing Data for line in stations: prices[line] = {} lineData = pd.read_excel(database, sheet_name=f"{line}Price") lineData['Type'] = lineData['Type'].ffill(axis=0) lineData = lineData.groupby(['Type']) for pay in lineData.groups.keys(): prices[line][pay] = {} payData = lineData.get_group((pay,)).copy() payData.drop(['Type'], axis=1, inplace=True) payData.set_index('Station', inplace=True) payData.columns = payData.iloc[0] payData = payData.iloc[1:] payData.dropna(axis='columns', how='all', inplace=True) payData.dropna(axis='index', how='all', inplace=True) prices[line][pay] = payData.to_dict() # Map G = nx.DiGraph() def add_bidirectional_edges(prefix, first, last): for i in range(first, last): j = i + 1 if 9 >= i >=0: i = f'0{str(i)}' if 9 >= j >=0: j = f'0{str(j)}' G.add_edge(f'{prefix}{i}', f'{prefix}{j}', weight=0) G.add_edge(f'{prefix}{j}', f'{prefix}{i}', weight=0) def set_group_attribute(prefix, first, last, line_name): for i in range(first, last + 1): if 9 >= i >= 0: i = f'0{str(i)}' G.nodes[f'{prefix}{i}']['line'] = line_name # Blue Line add_bidirectional_edges('BL', 1, 38) G.add_edge('BL32', 'BL01', weight=0) G.add_edge('BL01', 'BL32', weight=0) set_group_attribute('BL', 1, 38, 'Blue') # Purple Line add_bidirectional_edges('PP', 1, 16) set_group_attribute('PP', 1, 16, 'Purple') # Yellow Line add_bidirectional_edges('YL', 1, 23) set_group_attribute('YL', 1, 23, 'Yellow') # Pink Line add_bidirectional_edges('PK', 1, 30) add_bidirectional_edges('MT', 1, 2) G.add_edge('MT01', 'PK10', weight=0) G.add_edge('PK10', 'MT01', weight=0) set_group_attribute('PK', 1, 30, 'Pink') set_group_attribute('MT', 1, 2, 'Pink') # Green Line add_bidirectional_edges('N', 1, 24) G.add_edge('N01', 'CEN', weight=0) G.add_edge('CEN', 'N01', weight=0) add_bidirectional_edges('E', 1, 23) G.add_edge('E01', 'CEN', weight=0) G.add_edge('CEN', 'E01', weight=0) add_bidirectional_edges('S', 1, 12) G.add_edge('S01', 'CEN', weight=0) G.add_edge('CEN', 'S01', weight=0) G.add_edge('W01', 'CEN', weight=0) G.add_edge('CEN', 'W01', weight=0) set_group_attribute('N', 1, 24, 'Green') set_group_attribute('E', 1, 23, 'Green') set_group_attribute('S', 1, 12, 'Green') set_group_attribute('W', 1, 1, 'Green') G.nodes['CEN']['line'] = 'Green' # Gold Line add_bidirectional_edges('G', 1, 3) set_group_attribute('G', 1, 3, 'Gold') # Red Line add_bidirectional_edges('RN', 2, 10) G.add_edge('RW01-RN01', 'RN02', weight=0) G.add_edge('RN02', 'RW01-RN01', weight=0) add_bidirectional_edges('RW', 2, 6) G.add_edge('RW01-RN01', 'RW02', weight=0) G.add_edge('RW02', 'RW01-RN01', weight=0) set_group_attribute('RN', 2, 10, 'Red') set_group_attribute('RW', 2, 6, 'Red') G.nodes['RW01-RN01']['line'] = 'Red' # ARL Line add_bidirectional_edges('A', 1, 8) set_group_attribute('A', 1, 8, 'ARL') # BRT Line add_bidirectional_edges('B', 1, 3) G.add_edge('B03', 'B03A', weight=0) G.add_edge('B03A', 'B03', weight=0) G.add_edge('B03A', 'B04', weight=0) G.add_edge('B04', 'B03A', weight=0) G.add_edge('B04', 'B04A', weight=0) G.add_edge('B04A', 'B04', weight=0) G.add_edge('B04A', 'B05', weight=0) G.add_edge('B05', 'B04A', weight=0) add_bidirectional_edges('B', 5, 12) set_group_attribute('B', 1, 12, 'BRT') G.nodes['B03A']['line'] = 'BRT' G.nodes['B04A']['line'] = 'BRT' # Cross Between Line for station in interchange: G.add_edge(station, interchange[station], weight=1) G.add_edge( interchange[station], station, weight=1) # Helpers def inDict(ndict, item): if isinstance(ndict, dict): for key, nvalue in ndict.items(): if key == item: return key elif nvalue == item: return key elif isinstance(nvalue, dict): result = inDict(nvalue, item) if result is not None: return key elif isinstance(nvalue, list): for item in nvalue: if isinstance(item, dict): result = inDict(item, item) if result is not None: return key return None def find_paths_with_weight(graph, start, end, target_weight): all_paths = nx.all_simple_paths(graph, source=start, target=end) matching_paths = [] for path in all_paths: weight = sum(graph[u][v]['weight'] for u, v in zip(path[:-1], path[1:])) if weight == target_weight: matching_paths.append(path) return matching_paths # Init def init(): return json.dumps([stations, interchange, prices], sort_keys=True) # Calculate code = [code for line in stations.values() for code in line.keys()] def calculate(origin, destination): Best_Path_1 = [] Best_Path_2 = [] Best_Path_3 = [] BPrice = float('inf') BStation = float('inf') BInterchange = float('inf') if origin in code and destination in code: shortest_path = nx.dijkstra_path(G, origin, destination, weight='weight') shortest_length = nx.dijkstra_path_length(G, origin, destination, weight='weight') all_paths = find_paths_with_weight(G, origin, destination, shortest_length) calculated_path = [] for path in all_paths: Path_Line = {} Sum_Price = {'Adult': 0, 'Student': 0, 'Child': 0, 'Senior (Elder)': 0} Station_Count = -1 for station in path: if inDict(stations, station) not in Path_Line: Path_Line.update({inDict(stations, station) : []}) Path_Line[inDict(stations, station)].append(station) Station_Count += 1 for line in Path_Line: start = Path_Line[line][0] end = Path_Line[line][-1] Adult = int(prices[line]['Adult'][start][end]) Student = int(prices[line]['Student'][start][end]) Child = int(prices[line]['Child'][start][end]) Senior = int(prices[line]['Senior\n(Elder)'][start][end]) Sum_Price['Adult'] += Adult Sum_Price['Student'] += Student Sum_Price['Child'] += Child Sum_Price['Senior (Elder)'] += Senior Final_Path = {} for line in Path_Line: if line == "Blue": test = True for i in range(0, len(Path_Line[line])-1): if (Path_Line[line][i] == "BL32" and Path_Line[line][i+1] == "BL01") or (Path_Line[line][i] == "BL01" and Path_Line[line][i+1] == "BL32"): Final_Path["Blue1"] = Path_Line[line][0:i+1] Final_Path["Blue2"] = Path_Line[line][i:] test = False if test: Final_Path["Blue"] = Path_Line[line] elif line == "Pink": MT = any(x.startswith("MT") for x in Path_Line[line]) PK = any(x.startswith("PK") for x in Path_Line[line]) if (MT and PK): for i in range(0, len(Path_Line[line])-1): if (Path_Line[line][i] == "PK10" and Path_Line[line][i+1] == "MT01") or (Path_Line[line][i] == "MT01" and Path_Line[line][i+1] == "PK10 "): Final_Path["Pink1"] = Path_Line[line][0:i+1] Final_Path["Pink2"] = Path_Line[line][i:] else: Final_Path["Pink"] = Path_Line[line] elif line == "Green": north = any(x.startswith("N") for x in Path_Line[line]) east = any(x.startswith("E") for x in Path_Line[line]) south = any(x.startswith("S") for x in Path_Line[line]) west = any(x.startswith("W") for x in Path_Line[line]) if (north and south) or (north and west) or (east and south) or (east and west): for i in range(0, len(Path_Line[line])-1): if Path_Line[line][i] == "CEN": if Path_Line[line][0][0] == "N" or Path_Line[line][0][0] == "E": Final_Path["LightGreen"] = Path_Line[line][0:i+1] Final_Path["DarkGreen"] = Path_Line[line][i:] elif Path_Line[line][0][0] == "W" or Path_Line[line][0][0] == "S": Final_Path["DarkGreen"] = Path_Line[line][0:i+1] Final_Path["LightGreen"] = Path_Line[line][i:] elif south or west: Final_Path["DarkGreen"] = Path_Line[line] elif north or east: Final_Path["LightGreen"] = Path_Line[line] elif line == "Red": RN = any(x.startswith("RN") for x in Path_Line[line]) RW = any(x.startswith("RW") for x in Path_Line[line]) if (RN and RW): for i in range(0, len(Path_Line[line])-1): if Path_Line[line][i] == "RW01-RN01": if Path_Line[line][0][1] == "N": Final_Path["LightRed"] = Path_Line[line][0:i+1] Final_Path["DarkRed"] = Path_Line[line][i:] elif Path_Line[line][0][1] == "S": Final_Path["DarkRed"] = Path_Line[line][0:i+1] Final_Path["LightRed"] = Path_Line[line][i:] elif RN: Final_Path["DarkRed"] = Path_Line[line] elif RW: Final_Path["LightRed"] = Path_Line[line] else: Final_Path[line] = Path_Line[line] Interchange_Count = len(Final_Path) - 1 if Sum_Price['Adult'] < BPrice: BPrice = Sum_Price['Adult'] if Station_Count < BStation: BStation = Station_Count if Interchange_Count < BInterchange: BInterchange = Interchange_Count calculated_path.append({ "path": Final_Path, "price": Sum_Price, "stations": Station_Count, "interchanges": Interchange_Count }) for data in calculated_path: if data['price']['Adult'] == BPrice and data['stations'] == BStation and data['interchanges'] == BInterchange: Best_Path_1.append(data) elif data['price']['Adult'] == BPrice and data['stations'] == BStation: Best_Path_2.append(data) elif data['stations'] == BStation and data['interchanges'] == BInterchange: Best_Path_2.append(data) elif data['interchanges'] == BInterchange and data['price']['Adult'] == BPrice: Best_Path_2.append(data) elif data['price']['Adult'] == BPrice: Best_Path_3.append(data) elif data['stations'] == BStation: Best_Path_3.append(data) elif data['interchanges'] == BInterchange: Best_Path_3.append(data) return json.dumps([Best_Path_1, Best_Path_2, Best_Path_3, BPrice, BStation, BInterchange]) __export__ = ["init", "calculate"]

import json from pyscript import document, when, workers calculator = await workers["calculator"] result = await calculator.init() file = json.loads(result) stations = file[0] interchange = file[1] prices = file[2] colorDict = { "Blue": "#233180", "Purple": "#8f3387", "Yellow": "#ffd500", "Pink": "#e66086", "LightGreen": "#78b82a", "DarkGreen": "#00817e", "Gold": "#ad8c18", "LightRed": "#d05a63", "DarkRed": "#bc1320", "ARL": "#751633", "BRT": "#dddd00", "Blue1": "#233180", "Blue2": "#233180", "Pink1": "#e66086", "Pink2": "#e66086" } nameDict = { "Blue": "MRT Blue Line", "Purple": "MRT Purple Line", "Yellow": "MRT Yellow Line", "Pink": "MRT Pink Line", "LightGreen": "BTS Light Green Line", "DarkGreen": "BTS Dark Green Line", "Gold": "BTS Gold Line", "LightRed": "SRTET Light Red Line", "DarkRed": "SRTET Dark Red Line", "ARL": "Airport Rail Link", "BRT": "Bus Rapid Transit", "Blue1": "MRT Blue Line", "Blue2": "MRT Blue Line", "Pink1": "MRT Pink Line", "Pink2": "MRT Pink Line" } originElement = document.getElementById("origin") destinationElement = document.getElementById("destination") for line in stations: if line == "Green": optgroup1 = document.createElement("optgroup") optgroup1.label = nameDict["DarkGreen"] optgroup2 = document.createElement("optgroup") optgroup2.label = nameDict["LightGreen"] for code, name in stations[line].items(): if code == "CEN": option1 = document.createElement("option") option1.value = code option1.textContent = name optgroup1.appendChild(option1) option2 = document.createElement("option") option2.value = code option2.textContent = name optgroup2.appendChild(option2) elif code[0] == "W" or code[0] == "S": option = document.createElement("option") option.value = code option.textContent = name optgroup1.appendChild(option) elif code[0] == "E" or code[0] == "N": option = document.createElement("option") option.value = code option.textContent = name optgroup2.appendChild(option) originElement.appendChild(optgroup1) originElement.appendChild(optgroup2) elif line == "Red": optgroup1 = document.createElement("optgroup") optgroup1.label = nameDict["DarkRed"] optgroup2 = document.createElement("optgroup") optgroup2.label = nameDict["LightRed"] for code, name in stations[line].items(): if code == "RW01-RN01": option1 = document.createElement("option") option1.value = code option1.textContent = name optgroup1.appendChild(option1) option2 = document.createElement("option") option2.value = code option2.textContent = name optgroup2.appendChild(option2) elif code[1] == "W": option = document.createElement("option") option.value = code option.textContent = name optgroup1.appendChild(option) elif code[1] == "N": option = document.createElement("option") option.value = code option.textContent = name optgroup2.appendChild(option) originElement.appendChild(optgroup1) originElement.appendChild(optgroup2) else: optgroup = document.createElement("optgroup") optgroup.label = nameDict[line] for code, name in stations[line].items(): option = document.createElement("option") option.value = code option.textContent = name optgroup.appendChild(option) originElement.appendChild(optgroup) for line in stations: if line == "Green": optgroup1 = document.createElement("optgroup") optgroup1.label = nameDict["DarkGreen"] optgroup2 = document.createElement("optgroup") optgroup2.label = nameDict["LightGreen"] for code, name in stations[line].items(): if code == "CEN": option1 = document.createElement("option") option1.value = code option1.textContent = name optgroup1.appendChild(option1) option2 = document.createElement("option") option2.value = code option2.textContent = name optgroup2.appendChild(option2) elif code[0] == "W" or code[0] == "S": option = document.createElement("option") option.value = code option.textContent = name optgroup1.appendChild(option) elif code[0] == "E" or code[0] == "N": option = document.createElement("option") option.value = code option.textContent = name optgroup2.appendChild(option) destinationElement.appendChild(optgroup1) destinationElement.appendChild(optgroup2) elif line == "Red": optgroup1 = document.createElement("optgroup") optgroup1.label = nameDict["DarkRed"] optgroup2 = document.createElement("optgroup") optgroup2.label = nameDict["LightRed"] for code, name in stations[line].items(): if code == "RW01-RN01": option1 = document.createElement("option") option1.value = code option1.textContent = name optgroup1.appendChild(option1) option2 = document.createElement("option") option2.value = code option2.textContent = name optgroup2.appendChild(option2) elif code[1] == "W": option = document.createElement("option") option.value = code option.textContent = name optgroup1.appendChild(option) elif code[1] == "N": option = document.createElement("option") option.value = code option.textContent = name optgroup2.appendChild(option) destinationElement.appendChild(optgroup1) destinationElement.appendChild(optgroup2) else: optgroup = document.createElement("optgroup") optgroup.label = nameDict[line] for code, name in stations[line].items(): option = document.createElement("option") option.value = code option.textContent = name optgroup.appendChild(option) destinationElement.appendChild(optgroup) outputElement = document.getElementById("output") def createOutput(data, label): tab = document.createElement("input") tab.type = "radio" tab.name = "route" tab.className = "tab text-sm md:text-md" tab.ariaLabel = label outputElement.appendChild(tab) content = document.createElement("div") content.className = "tab-content !border-none" detail = document.createElement("div") detail.className = "grid grid-cols-2 grid-rows-3 p-8 gap-4" detail.innerHTML = f"ราคา{data['price']['Adult']} บาทจำนวนสถานี{data['stations']} สถานีจำนวนการเปลี่ยนสาย{data['interchanges']} ครั้ง" content.appendChild(detail) route = document.createElement("div") for line in data['path']: default = line if line == "Blue1" or line == "Blue2": default ="Blue" elif line == "Pink1" or line == "Pink2": default ="Pink" elif line == "DarkGreen" or line == "LightGreen": default ="Green" elif line == "DarkRed" or line == "LightRed": default ="Red" start = data['path'][line][0] end = data['path'][line][-1] color = colorDict[line] name = nameDict[line] details = document.createElement("details") details.className = "collapse !rounded-none" head = document.createElement("summary") head.className = f"collapse-title font-semibold bg-[{color}] text-white" head.innerHTML = f"{name} | {str(prices[default]['Adult'][start][end])} บาท | {str(len(data['path'][line]) - 1)} สถานี" details.appendChild(head) body = document.createElement("ul") body.className = "w-[calc(200%-20px)] collapse-content text-sm pt-[1rem] timeline timeline-vertical -translate-x-[calc(50%-20px)]" for station in data['path'][line]: dot = document.createElement("li") if station != start: hr1 = document.createElement("hr") hr1.className = f"w-[8px] h-[120%] !rounded-none bg-[{color}]" dot.appendChild(hr1) middle = document.createElement("div") middle.className = f"timeline-middle rounded-full size-[20px] bg-[{color}]" dot.appendChild(middle) text = document.createElement("div") text.className = "timeline-end m-4" text.innerHTML = stations[default][station] dot.appendChild(text) if station != end: hr2 = document.createElement("hr") hr2.className = f"w-[8px] h-[120%] !rounded-none bg-[{color}]" dot.appendChild(hr2) body.appendChild(dot) details.appendChild(body) route.appendChild(details) content.appendChild(route) outputElement.appendChild(content) @when("change", "#origin") async def originInput(): outputElement.innerHTML = "" result = await calculator.calculate(originElement.value, destinationElement.value) file = json.loads(result) Best_Path_1 = file[0] Best_Path_2 = file[1] Best_Path_3 = file[2] BPrice = file[3] BStation = file[4] BInterchange = file[5] if len(Best_Path_1) >= 1: for data in Best_Path_1: createOutput(data, "เส้นทางที่ดีที่สุด") elif len(Best_Path_2) >= 1: for data in Best_Path_2: if data['price']['Adult'] == BPrice and data['stations'] == BStation: createOutput(data, "ราคาต่ำที่สุดและระยะทางสั้นที่สุด") elif data['stations'] == BStation and data['interchanges'] == BInterchange: createOutput(data, "ระยะทางสั้นที่สุดและเปลี่ยนสายน้อยที่สุด") elif data['interchanges'] == BInterchange and data['price']['Adult'] == BPrice: createOutput(data, "ราคาต่ำที่สุดและเปลี่ยนสายน้อยที่สุด") elif len(Best_Path_3) >= 1: for data in Best_Path_3: if data['price']['Adult'] == BPrice: createOutput(data, "ราคาต่ำที่สุด") elif data['stations'] == BStation: createOutput(data, "ระยะทางสั้นที่สุด") elif data['interchanges'] == BInterchange: createOutput(data, "เปลี่ยนสายน้อยที่สุด") document.querySelector('input[type="radio"][name="route"]').checked = True @when("change", "#destination") async def destinationInput(): outputElement.innerHTML = "" result = await calculator.calculate(originElement.value, destinationElement.value) file = json.loads(result) Best_Path_1 = file[0] Best_Path_2 = file[1] Best_Path_3 = file[2] BPrice = file[3] BStation = file[4] BInterchange = file[5] if len(Best_Path_1) >= 1: for data in Best_Path_1: createOutput(data, "เส้นทางที่ดีที่สุด") elif len(Best_Path_2) >= 1: for data in Best_Path_2: if data['price']['Adult'] == BPrice and data['stations'] == BStation: createOutput(data, "ราคาต่ำที่สุดและระยะทางสั้นที่สุด") elif data['stations'] == BStation and data['interchanges'] == BInterchange: createOutput(data, "ระยะทางสั้นที่สุดและเปลี่ยนสายน้อยที่สุด") elif data['interchanges'] == BInterchange and data['price']['Adult'] == BPrice: createOutput(data, "ราคาต่ำที่สุดและเปลี่ยนสายน้อยที่สุด") elif len(Best_Path_3) >= 1: for data in Best_Path_3: if data['price']['Adult'] == BPrice: createOutput(data, "ราคาต่ำที่สุด") elif data['stations'] == BStation: createOutput(data, "ระยะทางสั้นที่สุด") elif data['interchanges'] == BInterchange: createOutput(data, "เปลี่ยนสายน้อยที่สุด") document.querySelector('input[type="radio"][name="route"]').checked = True document.getElementById("splash").classList.add("hidden")

ต้นทาง

ปลายทาง