Citibike

import pandas as pd
import geopandas as gpd

ntas = gpd.read_file("../Data/nyc_ntas_2020.geojson")

cb = pd.read_csv(
    "/Users/marioag/Documents/GitHub/citibike-viewer/data/od/202302-citibike-tripdata.csv"
)

/var/folders/g5/b592wl6x12s0tx4jfw9f7_j40000gn/T/ipykernel_17048/4078996454.py:1: DtypeWarning: Columns (5,7) have mixed types. Specify dtype option on import or set low_memory=False.
  cb = pd.read_csv(

cb.shape

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In[1], line 1
----> 1 cb.shape

NameError: name 'cb' is not defined

cb.rename(
    columns={
        "start_station_id": "source",
        "end_station_id": "target",
    },
    inplace=True,
)

cb["lat"] = cb["start_lat"]
cb["lon"] = cb["start_lng"]

# cb.to_csv("../Data/citibike_sample.csv", index=False)

# groupby start and end stations
cb_grouped = (
    cb.groupby(["source", "target", "lat", "lon", "start_station_name"])
    .size()
    .reset_index(name="weight")
)

cb_grouped.shape

(834630, 6)

# cb_grouped.to_csv("../Data/citibike_grouped.csv", index=False)

# assign nta stats to stations
stations_gdf = gpd.GeoDataFrame(
    cb_grouped,
    geometry=gpd.points_from_xy(cb_grouped["lon"], cb_grouped["lat"], crs="EPSG:4326"),
)
stations_w_ntas = gpd.sjoin(stations_gdf, ntas, how="left", predicate="intersects")

# get unique stations and their coordinates
stations = (
    cb_grouped[
        [
            "source",
            "lat",
            "lon",
            "start_station_name",
        ]
    ]
    .drop_duplicates(subset=["source", "start_station_name"], keep="first")
    .rename(
        columns={
            "source": "id",
            "lat": "latitude",
            "lon": "longitude",
            "start_station_name": "name",
        }
    )
)

stations

stations_attr = stations.merge(
    stations_w_ntas[
        ["source", "start_station_name", "BoroName", "NTAName"]
    ].drop_duplicates(subset=["source", "start_station_name"], keep="first"),
    left_on=["id", "name"],
    right_on=["source", "start_station_name"],
    how="left",
    suffixes=("", "_nta"),
)  # .shape

stations_attr

stations_attr.to_csv("../Data/citibike_stations_attr.csv", index=False)