"""
This notebook will show how to read in, clean, and visualize the Citi Bike Stations dataset.

## Load Libraries and Data
"""
%config InlineBackend.figure_format = 'retina'

import matplotlib as mpl
import matplotlib.pyplot as plt
import pandas as pd
# Make things a little bigger
mpl.rcParams['figure.titlesize'] = 22
mpl.rcParams['axes.labelsize'] = 18
mpl.rcParams['axes.titlesize'] = 20
mpl.rcParams['legend.fontsize'] = 18
mpl.rcParams['xtick.labelsize'] = 16
mpl.rcParams['ytick.labelsize'] = 16

# Don't limit the number of columns displayed
pd.set_option("display.max_columns", None)
def read_csv(filename: str) -> pd.DataFrame:
    """
    Read DataFrame from a CSV file ``filename`` and convert to a 
    preferred schema.
    """
    df = pd.read_csv(
        filename,
        sep=",",
        na_values="\\N",
        dtype={
            "station_id": str,
            # Use Pandas Int16 dtype to allow for nullable integers
            "num_bikes_available": "Int16",
            "num_ebikes_available": "Int16",
            "num_bikes_disabled": "Int16",
            "num_docks_available": "Int16",
            "num_docks_disabled": "Int16",
            "is_installed": "Int16",
            "is_renting": "Int16",
            "is_returning": "Int16",
            "station_status_last_reported": "Int64",
            "station_name": str,
            "lat": float,
            "lon": float,
            "region_id": str,
            "capacity": "Int16",
            # Use pandas boolean dtype to allow for nullable booleans
            "has_kiosk": "boolean",
            "station_information_last_updated": "Int64",
            "missing_station_information": "boolean"
        },
    )
    # Read in timestamps as UNIX/POSIX epochs but then convert to the local
    # bike share timezone.
    df["station_status_last_reported"] = pd.to_datetime(
        df["station_status_last_reported"], unit="s", origin="unix", utc=True
    ).dt.tz_convert("US/Eastern")

    df["station_information_last_updated"] = pd.to_datetime(
        df["station_information_last_updated"], unit="s", origin="unix", utc=True
    ).dt.tz_convert("US/Eastern")
    return df
df = read_csv("/kaggle/input/citi-bike-stations/citi_bike_data_00000.csv")
df.head()
"""
## Some Basic Stats

Below, we print out some basic stats about the different stations. Why do some stations not have a name? I have no idea. What's with the weird `motivate` stations with the UUIDs? I have no idea.
"""
def station_stats(station: pd.DataFrame) -> pd.Series:
    stats = {
        "most_recent_name": station.iloc[station["station_status_last_reported"].argmax()]["station_name"],
        "num_samples": len(station),
        "status_first_reported": station["station_status_last_reported"].min(),
        "status_last_reported": station["station_status_last_reported"].max(),
        "min_capacity": station["capacity"].min(),
        "max_capacity": station["capacity"].max(),
        "max_ebikes_ever_available": station["num_ebikes_available"].max(),
        "max_bikes_disabled": station["num_bikes_disabled"].max(),
        "max_docks_disabled": station["num_docks_disabled"].max(),
    }
    return pd.Series(stats)


print(f"Number of samples: {len(df)}")
print(f"Number of stations: {df['station_id'].nunique()}")

print("Station-specific stats:")
# Filter out stations with erroneous timestamps
mask = df["station_status_last_reported"] >= "2016-01-01"
(
    df[mask]
    .groupby("station_id")
    .apply(station_stats)
    .sort_values("num_samples", ascending=False)
)
"""
## Diving into a Single Station
"""
# Pick out data for a particular station
station_id = "466"
station = df[df["station_id"] == station_id]
# Print out some info about the station.
# This information is null for all data prior to 8/2019.
station_info = station[station["station_name"].notna()].iloc[0]
station_info[["station_name", "lat", "lon", "capacity"]]
# Turn into a clean time series

# Only include columns whose values are present throughout the full 
# time period of the dataset.
columns_to_keep = [
    "num_bikes_available", 
    "num_docks_available", 
    "station_status_last_reported"
]
station = station[columns_to_keep]

# There are occasions where station_status_last_reported is clearly
# erroneous (it's ~1970-01-01). Let's just drop this data.
station = station[station["station_status_last_reported"] >= "2016-01-01"]

# Index by time and sort
station = station.set_index("station_status_last_reported").sort_index()

# Now resample so that the index corresponds to 5 minute increments. 
# We pick .last() so that we are not leaking future information into the 
# past.
station = station.resample("5T").last()

# Finally, we fill any missing data after the resampling using the most
# recently known data.
station = station.fillna(method="ffill")
station.head()
"""
### Some Light Viz
We'll start by looking at 2 weeks worth of data from June 1st to June 14th in 2019. June 1st was a Saturday, and you can clearly see the daily and weekly periodicity in this station. Also the number of bikes available is perfectly out of phase with the number of docks available which makes sense!
"""
fig, ax = plt.subplots(figsize=(16, 8))
station.loc["2019-06-01":"2019-06-14"].plot(ax=ax)
None
"""
The station is in the Flatiron neighborhood and not too far from Madison Square Park. My guess is that this is a commuter _destination_ station because there are lots of companies in this area. We can confirm this by zooming in on the data and setting the timezone to the local, New York time (the time is currently in UTC).

As you can see, the number of bikes at the station rapidly increases during the morning commute and then slowly drops at the end of the work day as people pickup bikes and ride home. This is a good lesson not to work past 6 PM -- there won't be a bike available!
"""
fig, ax = plt.subplots(figsize=(16, 8))
station.loc["2019-06-04":"2019-06-06 00:00:00", "num_bikes_available"].plot(ax=ax)
None