<jupyter_start><jupyter_text>The goal of this notebook is to take the 29 million trips, count how many occur between each two stations, then sum then to find the undirected number of trips (ie all trips starting or ending at this station)  Creating trip summary tableOur goal is to create a table showing the stations in a certain pair of stations Loading trip dataFirst we load the table of all trips, and remove any with incomplete data<jupyter_code># df = cabi.return_trip_datatable()
# df.to_parquet("../data/interim/comb_trips.gzip", compression="gzip")
df = pd.read_parquet("../data/interim/comb_trips.gzip")
total = len(df)
print(f"{total} total trips")
df
# clean the NA values out
df = df.dropna()
total_nonna = len(df)
print(
    f"{total_nonna} after removing trips with NA values \n {total-total_nonna} trips removed due to NA values"
)<jupyter_output>31081858 after removing trips with NA values 
 461865 trips removed due to NA values<jupyter_text>Removing self-tripsThere are interesting but they aren't easy to show on a map<jupyter_code>df = df[df.start_station_id != df.end_station_id]
total_nonself = len(df)
print(
    f"{total_nonself} trips starting and ending at the same station \n {total_nonna-total_nonself} trips were removed"
)<jupyter_output>29690088 trips starting and ending at the same station 
 1391770 trips were removed<jupyter_text>Removing invalid tripsWe have some number of trips where the end station is listed as 0. Obviously these are invalid<jupyter_code>df = df[df.start_station_id > 0]
df = df[df.end_station_id > 0]
total_mappable = len(df)
print(
    f"{total_mappable} trips after removing trips with missing origin or destination \n {total_nonna-total_mappable} trips removed"
)<jupyter_output>29688538 trips after removing trips with missing origin or destination 
 1393320 trips removed<jupyter_text>Removing trips from removed stationsWe also need to make sure we know the location of the station. Therefore we load the list of stations names. Maybe in the future if I can find a table of the locations of removed stations and we can add them to the visualization loading our current stations<jupyter_code># Define which attributes to lookup from airports.csv
cabi_stations = "https://raw.githubusercontent.com/mlinds/cabi-data/main/data/processed/stationLookup.csv"
station_names_list = list(pd.read_csv(cabi_stations).short_name)<jupyter_output><empty_output><jupyter_text>Selecting only trips involving extant stationsWe rewrite the dataframe to include only stations that exist in our location lookup table<jupyter_code>df = df[df.end_station_id.map(lambda x: x in station_names_list)]
df = df[df.start_station_id.map(lambda x: x in station_names_list)]
total_current = len(df)
print(
    f"{total_current} trips after removing trips from stations that dont exist \n {total_mappable-total_current} trips were removed"
)
df.dtypes
import numpy as np

# @jit(uint32(uint16,uint16))
def sort_stations(st, end):
    a, b = sorted([st, end])
    return int(str(a) + str(b))
# @jit
def sort_stations_dataframe(starray, endarray, ind):
    st = starray.iloc[ind]
    end = endarray.iloc[ind]
    return sort_stations(st, end)


sorter = lambda i: sort_stations_dataframe(
    sample_df.start_station_id, sample_df.end_station_id, i
)
sample_df = df[["start_station_id", "end_station_id"]].reset_index()
%%time
popularity = sample_df.groupby(by=sorter).count()
new_pop = popularity.start_station_id
indicies = pd.DataFrame(new_pop).index
stlist = [int(str(item)[0:5]) for item in indicies]
endlist = [int(str(item)[5:10]) for item in indicies]
pop_df = (
    pd.DataFrame(new_pop)
    .assign(st=stlist, en=endlist)
    .reset_index(drop=True)
    .rename(columns={"start_station_id": "popularity"})[["st", "en", "popularity"]]
)
pop_df.to_csv(
    "../data/processed/connections_csv.csv",
    columns=["st", "en", "popularity"],
    index=False,
)
pop_df.sort_values("popularity", ascending=False)
all_stations = pd.read_csv(cabi_stations)
all_stations
32232, 32600
df
df.query("start_station_id == 32232 & end_station_id == 32600")
df.query("start_station_id == 32600 & end_station_id == 32232")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0962874251497006, 'fraction_numerical': 0.02155688622754491, 'mean_word_length': 5.703049759229534, 'pattern_counts': {'":': 1, '<': 18, '<?xml version=': 0, '>': 20, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '271657', 'license': 'MIT', 'n_tokens_mistral': 1443, 'n_tokens_neox': 1361, 'n_words': 422, 'path': 'notebooks/route_popularity.ipynb', 'repo_name': 'mlinds/cabi-data', 'size': '4175'}