<jupyter_start><jupyter_text>Investigating Stop Assignment  Loading data<jupyter_code>import requests
import pandas as pd
import numpy as np
import geopy<jupyter_output><empty_output><jupyter_text>A single day's (05/24/2020) full data pulled from api<jupyter_code>url = 'http://sfmta-ds.eba-hqpuyrup.us-east-1.elasticbeanstalk.com/daily-general-json'
json = requests.get(url, params={'day': '2020-05-24'}).json()
# making df

df = pd.DataFrame(data=json)
# paring down to a couple of buses 
# (most reports and second-most reports) 
# on a single route to simplify
# and making sure we're sorted by time (stupid-check)

nbus = df[df['rid']=='NBUS']
nbus_highest = nbus[nbus['vid']==(nbus['vid'].value_counts().index[0])].sort_values('timestamp')
nbus_second = nbus[nbus['vid']==(nbus['vid'].value_counts().index[1])].sort_values('timestamp')
# using stops gathered by Labs 22 for expediency

stops = pd.read_csv('https://raw.githubusercontent.com/Lambda-School-Labs/sfmta-data-analysis-ds/master/deprecated_assets/datasets/route_info.csv')
nbus_stops = stops[stops['route_id']=='NBUS']
nbus_stops<jupyter_output><empty_output><jupyter_text>Engineering Probable Nearest Stop and Distance (For Confidence)  Original helper function for wrangling<jupyter_code>def wrangle_bus(df):
  """
  preps dataframe for a single bus
  gives accurate timestamps and naively calculates 
  dwell time as 1min per checkin with motion (kph <= 0)
  """
  times = df['timestamp'].values
  ages = df['age'].values
  df['adjusted_timestamp'] = [pd.Timestamp(times[x]) - pd.Timedelta(seconds=ages[x]) for 
                              x in range(len(df['timestamp']))]
  df['timestamp'] = [pd.Timestamp(times[x]) for x in range(len(df['timestamp']))]

  dwell_count = 0
  dwell_totals = []
  df['dwell'] = 0
  for x in df['kph']:
    if x <= 0:
      dwell_count += 1
      dwell_totals.append(dwell_count)
    elif x > 0:
      dwell_totals.append(0)
      dwell_count = 0
  df['dwell'] = [dwell_totals[x] for x in range(len(df))]

  return df<jupyter_output><empty_output><jupyter_text>Function to calculate nearest stop within $X$ km by geodesic distanceThis is definitely not the most efficient code; I haven't done any refactoring yet\ so its slow AF and probably (100%) has extra steps.\There's definitely MUCH more fast/efficient implementations of what I'm doing here.However, based off of the eyeball test it seems to be doing a pretty good job of assigning stops\appropriately. Dwell times are lining up closely with time at stops, and the progression of stops\appears to be accurate in comparison to the route.I'm using geopy (plays nice with lat/lon) to calculate the geodesic distance between reported\locations and stops. Essentially straight-line (euclidean) distance but generalized to a curved surface.The calculated distance is used to decide whether to assign a stop at a reported location or not;\it also serves as a measure of confidence in the assigned stop.\The smaller the distance, the more certain we are the bus is at or very near that stop.Code below (or similar) should generalize - time/route/vehicle agnostic - but as is larger\amounts of data will equate to extravagant execution times. For the purpose of scheduled\generation of daily reports this probably doesn't matter all that much; a user won't have to sit and\wait while the report generates from scratch. Still, leaves a lot to be desired. :coolcry:<jupyter_code>def assign_stop(df, stops):
  """
  applies basic wrangling function
  calculates nearest stop from reported location in km
  returns dataframe with reported location, 
  nearest stop (coords and name), and distance between

  tested with single buses on single routes on a single day;
  technically route/vehicle/time agnostic
  don't foresee any issues beyond extravagant execution time
  but haven't tested yet
  """

  # original wrangling function for continuity
  wrangle_bus(df)

  # creating list of lat/lon dictionaries for stops and reported bus locations
  stop_lats = stops['lat'].values
  stop_lons = stops['lon'].values

  reported_lats = df['latitude'].values
  reported_lons = df['longitude'].values

  stop_points = [{'latitude': stop_lats[x], 'longitude': stop_lons[x]} 
                 for x in range(len(stops))]

  reported_points = [{'latitude': reported_lats[x], 
                      'longitude': reported_lons[x]} 
                     for x in range(len(df))]

  # just less than the smallest distance between stops as reported by sfmta
  # to minimize possible overlap between probable stops
  # In km to play nice with geopy
  radius = .09

  # dict to tuples to play nice with geopy
  stop_point_tuples = [tuple(stop_points[x].values()) 
                       for x in range(len(stop_points))]

  reported_point_tuples = [tuple(reported_points[x].values()) 
                           for x in range(len(reported_points))]
  
  df['reported_location'] = reported_point_tuples
  
  # generating ((lat/lon), distance) tuples for nearest stop within range
  point_stops = []
  for location in reported_point_tuples:
    distances = {x: geopy.distance.distance(location, x) 
                 for x in stop_point_tuples}

    distances_sorted = {k: v for k, v in sorted(distances.items(), 
                                                key=lambda item: item[1])}

    smallest_distance = next(iter(distances_sorted.items()))

    if smallest_distance[1] <= radius:
      point_stops.append(smallest_distance)
    else:
      point_stops.append(None)

  # assigning stop name from stops table based on lat/lon from previous step
  stop_titles = []
  for stop in point_stops:
    if stop != None:
      stop = stop[0] # if not none, grab lat/lon from tuple

      # if assigned stop in stops table, grab name of stop from stops table
      if stop in list(zip(stops['lat'], stops['lon'])):
        title_pos = list(zip(stops['lat'], stops['lon'])).index(stop)
        stop_titles.append(stops['title'].iloc[title_pos])
      else:
        # possible assigned stops are generated from stops table
        # theoretically this else statement should never execute
        stop_titles.append(None)

    else:
      # if no assigned stop, don't assign stop ¯\_(ツ)_/¯
      stop_titles.append(None)
  
  # pulling lat/lon and distance from tuples for df
  df['nearest_stop'] = [x[0] if x != None else x for x in point_stops]
  df['distance'] = [x[1] if x != None else x for x in point_stops]

  # pulling stop names from list for df
  df['stops'] = [stop_titles[x] for x in range(len(stop_titles))]

  # dropping columns of redundant information
  df = df.drop(columns=['age', 'rid', 'vid', 'latitude', 'longitude'])
  
  return df<jupyter_output><empty_output><jupyter_text>Results<jupyter_code>%%timeit

nbus_highest_wrangled = assign_stop(nbus_highest, nbus_stops)
nbus_highest_wrangled[['adjusted_timestamp', 'kph', 'direction', 
                       'dwell', 'reported_location', 'stops', 
                       'nearest_stop', 'distance']].head(20)
nbus_highest_wrangled[['adjusted_timestamp', 'kph', 'direction', 
                       'dwell', 'reported_location', 'stops', 
                       'nearest_stop', 'distance']].tail(20)
nbus_second_wrangled = assign_stop(nbus_second, nbus_stops)

nbus_second_wrangled[['adjusted_timestamp', 'kph', 'direction', 
                      'dwell', 'reported_location', 'stops', 
                      'nearest_stop', 'distance']].head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10503077334760504, 'fraction_numerical': 0.005887075194005887, 'mean_word_length': 3.767219387755102, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 22, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '217549', 'license': 'MIT', 'n_tokens_mistral': 2368, 'n_tokens_neox': 2216, 'n_words': 719, 'path': 'labs24_notebooks/calculating_stops/CalculatingStops_FirstLook.ipynb', 'repo_name': 'Lambda-School-Labs/sftma-data-analysis-ds', 'size': '7474'}