HSR-Visualization/parseChinaData.py at main · ianckruger/HSR-Visualization · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
# This file is specifically for parsing high speed trains operation data from the below source:
# https://figshare.com/articles/dataset/A_high-speed_railway_network_dataset_from_train_operation_records_and_weather_data/15087882?file=30853027
# This lists all high speed rail train operations. Using this, we can derive the specific names of stations that operate HSR within china,
# the frequency with which the trains come and go, as well as all connecting HSR routes.

import pandas as pd

# Load the data from hstod
df = pd.read_csv("data/high-speed trains operation data.csv")

# Quick check to see the structure, need to know where to extract data from
# print("Columns: ", df.columns)
# print(df.head())

# standardizing column names to make querying easier
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")
# might rename things like train_number --> train but if its a difficulty i will if not i wont
# or just use a constant

# clean out missing data
df = df.dropna(subset=['train_number', 'station_name', 'station_order'])
df['station_order'] = df['station_order'].astype('int')

print("Unique trains:", df['train_number'].nunique()) # 3356
print("Unique stations:", df['station_name'].nunique()) # 720

dfSorted = df.sort_values(['train_number', 'station_order'])
# print(dfSorted.head(20))

# need to build a "map" network of station to station
edges = []

for train_id, group in dfSorted.groupby('train_number'):
    group = group.sort_values('station_order')

    stations = group['station_name'].tolist()
    # create the connections
    for i in range(len(stations) -1):
        fromStation = stations[i]
        toStation = stations[i + 1]

        edges.append({
            'train': train_id,
            'fromStation': fromStation,
            'toStation': toStation
        })

# also retrospectively, cleaning stations to filter out the A --> B - B --> C duplication
clean = [stations[0]]
for s in stations[1:]:
    if s != clean[-1]:
        clean.append(s)

stations = clean


# edges should now have all trains with their to and from destinations
edgesDF = pd.DataFrame(edges)

# print("Total edges: ", len(edgesDF))
# print(edgesDF.head())

# lots of repeat connection for each seperate train so count the frequency (2748356 to be exact...)
# get proxy for ridership

#have to filter out self loops, the numbers were inflated vastly for Station X --> Station X
# adding this in retrospectively
edgesDF = edgesDF[edgesDF['fromStation'] != edgesDF['toStation']]
edgeCounts = (
    edgesDF.groupby(['fromStation', 'toStation'])
    .size().reset_index(name="frequency").sort_values('frequency', ascending=False)
)

# looks a lot cleaner, frequency included which is good
# weird number in the front that doesnt show up in the columns listing so must be the index? but theyre all numbers that are 100 or 1000+
print(edgeCounts.head(20))
print(edgeCounts.columns)
# duplicate directions inflate number count A --> B; B --> A doesnt matter since we just need one connection between two cities
edgeCounts['pair'] = edgeCounts.apply(
    lambda row: tuple(sorted([row['fromStation'], row['toStation']])),
    axis = 1
)
edgeCounts = (
    edgeCounts.groupby('pair')['frequency'].sum().reset_index()
)

# re-split into the og columns
edgeCounts[['stationA','stationB']] = pd.DataFrame(
    edgeCounts['pair'].tolist(),
    index=edgeCounts.index
)

edgeCounts = edgeCounts.drop(columns=['pair'])
print(edgeCounts.head(20))

# count each station appreance
stationCount = (
    df['station_name'].value_counts().reset_index()
)

stationCount.columns =  ['station', 'num_stops']
print(stationCount.head(20))

# after cleaning the data heads
# save to csv

edgesDF.to_csv("outputData/raw_station_edges.csv", index=False)
edgeCounts.to_csv("outputData/aggregate_station_connection.csv", index=False)
stationCount.to_csv("outputData/station_importance.csv", index=False)
print("saved all output")
print("\n Top 10 busiest connections")
print(edgeCounts.sort_values('frequency' , ascending=False).head(10))
print("\nTop 10 busiest stations: ")
print(stationCount.head(10))