from google.colab import drive
drive.mount('/content/drive')

TRAFFIC_MAY_URL="/content/drive/My Drive/Colab Notebooks/trams-barcelona-data/2019_05_Maig_TRAMS_TRAMS.csv"
STREETS_MAPPING_URL="/content/drive/My Drive/Colab Notebooks/trams-barcelona-data/transit_relacio_trams.csv"
STREETS_MAPPING_URL_LONG="/content/drive/My Drive/Colab Notebooks/trams-barcelona-data/transit_relacio_trams_format_long.csv"

import pandas as pd
import matplotlib.pyplot as plt

# define default size of plots
plt.rcParams["figure.figsize"] = (20,3)

trf = pd.read_csv(TRAFFIC_MAY_URL, sep=",")
trf.head(10)

trf.info()

old_size, _ = trf.shape

# clean trf fr
trf = trf[
    ~(
        trf['estatActual'].isin([0, 6]) | trf['estatPrevist'].isin([0, 6])
    )
]

trf.info()

new_size, _ = trf.shape

# print the amount of rows that were dropped
print(f"{old_size - new_size} of {old_size} rows were dropped ({(old_size-new_size)/old_size:0%})")

streets = pd.read_csv(STREETS_MAPPING_URL, sep=",")
streets.head(10)

streets.dtypes

streets["Coordenades"].iloc[1]

# we check for possible missing data
streets["Coordenades"].isna().sum()

streets["Coordenades"].isnull().sum()

expanded_coords = streets["Coordenades"].str.split(",", expand=True)
expanded_coords.head()

len(expanded_coords.columns)

!pip install shapely

sample = streets["Coordenades"].str.split(",")[0]
sample

import typing as T

from itertools import zip_longest

# not tested
def reshape_line(line: T.List[str], chunksize: int, fillvalue: int) -> T.Iterable[T.Tuple[float]]:
    """Reshape a line to match the LineString WKT format
    
    This is based on `zip_longest`, read more in 
    <https://docs.python.org/3/library/itertools.html#itertools.zip_longest> and
    in the StackOverflow solution posted in <https://stackoverflow.com/a/434411/5819113>
    
    
        zip_longest('ABCD', 'xy', fillvalue='-') --> Ax By C- D-
       
    """
    args = [iter(float(el) for el in line)] * chunksize
    return zip_longest(*args, fillvalue=fillvalue)

list(reshape_line(sample, chunksize=2, fillvalue=0))

from shapely.geometry import LineString

sample_wkt = LineString(reshape_line(sample, 2, 0))
sample_wkt

def convert_line_to_wkt(line: str) -> LineString:
    splitted_line = line.split(",")
    reshaped_line = reshape_line(splitted_line, chunksize=2, fillvalue=None)
    return LineString(reshaped_line)

# let's process the whole column
coords_wkt = streets["Coordenades"].apply(lambda line: convert_line_to_wkt(line))

# access the __str__ of the first element
print(coords_wkt[1])

!pip install geopandas

from geopandas import GeoDataFrame

crs = {'init': 'epsg:4326'}
gdf = GeoDataFrame(streets, crs=crs, geometry=coords_wkt)
ax = gdf.plot(cmap="viridis")

!pip install folium

import os
import folium

print(folium.__version__)

barcelona_map = folium.Map([41.3947,2.1557], zoom_start=12.4, tiles='cartodbpositron')
folium.GeoJson(gdf).add_to(barcelona_map)
# plot map
barcelona_map

trf["data"].head()

# https://pandas.pydata.org/pandas-docs/version/0.20/generated/pandas.to_datetime.html
pd.to_datetime(trf["data"].astype(str)[:5], format='%Y%m%d%H%M%S')

trf["data_datetime"] = pd.to_datetime(trf["data"].astype(str), format='%Y%m%d%H%M%S')

trf.columns

trf.head(5)

# caveat: this is an *aggregated* measure of the whole month, but it provides
# a rough measure

monthly_traffic = trf.groupby(by=['idTram'], as_index=True).mean()
monthly_traffic["diff_perc"] = monthly_traffic["estatActual"]/monthly_traffic["estatPrevist"]*100
monthly_traffic["was_forecast_right"] = monthly_traffic["diff_perc"] <= 100
monthly_traffic.head()

# line continuation according to PEP8 
# see https://stackoverflow.com/a/17630918/5819113

trams_hourly = (
    # drop unwanted columns
    trf.drop(columns=['data', 'idTram'])
        # group by desired columns
        .groupby(by=[trf['idTram'], trf["data_datetime"].dt.day], as_index=True)
        # show the mean of the aggregated values
        .mean()
)

trams_hourly["diff_perc"] = trams_hourly["estatActual"]/trams_hourly["estatPrevist"]*100
trams_hourly["was_forecast_right"] = trams_hourly["diff_perc"] <= 100
trams_hourly.head(24) # first 24 hours

# join dataframes, to *see* what happens. But make a copy first.
_trf_to_merge = trf.copy()
_streets_to_merge = streets.copy()

# merge both dataframes using the id of the tram as common column
_merged = _trf_to_merge.merge(_streets_to_merge, left_on="idTram", right_on="Tram")
# drop unnecesary columns and set the idTram
_merged = _merged.drop(columns=["data", "Tram", "Coordenades"])
_merged.head()

# `estatActual` and `estatPrevist` vary from 1 to 5
_merged.describe()

_merged.info()

from geopandas import GeoDataFrame

crs = {'init': 'epsg:4326'}
merged_gdf = GeoDataFrame(_merged, crs=crs, geometry=_merged['geometry'])

# create a colormap using branca
# following the examples from
# https://nbviewer.jupyter.org/github/python-visualization/folium/blob/master/examples/plugin-Search.ipynb

import branca

colorscale = branca.colormap.linear.viridis.scale(1, 5)
colorscale.caption="Tram usage level"


def style_function(feature):
    estat_actual = int(feature['properties']['estatActual']) or None
    return {
        'alpha': 0.5,
        'weight': 3,
        'color': '#black' if estat_actual is None else colorscale(estat_actual)
    }

# we will manipulate the dataframe to plot data using time scales, 
# since plotting the whole dataframe it's too expensive memory-wise

first_10_am = merged_gdf[(merged_gdf["data_datetime"].dt.day == 1) & (merged_gdf["data_datetime"].dt.hour == 10)]

# we need to drop this column because it raises an error
first_10_am = first_10_am.drop(columns="data_datetime", inplace=False)

# plot the map
barca_map_colors = folium.Map([41.3947,2.1557], zoom_start=12.4, tiles='cartodbpositron')

(
    folium.GeoJson(
        first_10_am,
        style_function=style_function)
    .add_to(barca_map_colors)
)

# add legend, see <https://github.com/python-visualization/folium/issues/528>
barca_map_colors.add_child(colorscale)

# plot map
barca_map_colors

# we will manipulate the dataframe to plot data using time scales

gdf_hourly = (
        # group by desired columns
        merged_gdf.groupby(
            by=[merged_gdf['idTram'],
                merged_gdf["data_datetime"].dt.day, # here is where the grouping happens
               ],
            as_index=True)
        # show the mean of the aggregated values
        .mean()
        .reset_index()
)

gdf_hourly["diff_perc"] = gdf_hourly["estatActual"]/gdf_hourly["estatPrevist"]*100
gdf_hourly["was_forecast_right"] = gdf_hourly["diff_perc"] <= 100
gdf_hourly_w_geometry = gdf_hourly.merge(streets, left_on="idTram", right_on="Tram")
gdf_hourly_w_geometry.tail()

import branca
from folium.plugins import Search
from geopandas import GeoDataFrame

# produce the geopandas dataframe
crs = {'init': 'epsg:4326'}
gdf_hourly = GeoDataFrame(
    gdf_hourly_w_geometry[gdf_hourly_w_geometry['data_datetime']==27], # here is where the filtering happens
    crs=crs,
    geometry=gdf_hourly_w_geometry['geometry']
)

# create colorscale
colorscale = branca.colormap.step.Accent_06.scale(1, 5)
colorscale.caption="estatActual (Daily average)"

# define a method for styling lines
def style_function_capacity(feature):
    not_exceeded = int(feature['properties']['was_forecast_right']) or None
    estat_actual = int(feature['properties']['estatActual']) or None
    
    # see for other options https://leafletjs.com/reference-1.5.0.html#path-option
    return {
        'alpha': 0.5,
        'dashArray': '5,10' if not_exceeded else '0', # dashed line unless forecast is wrong
        'weight': 3,
        'color': '#black' if estat_actual is None else colorscale(estat_actual),
    }
  
# plot the map of barcelona
hourly_map = folium.Map([41.3947,2.1557], zoom_start=12.4, tiles='cartodbpositron')

# plot the tram lines
tram_map = (
    folium.GeoJson(
        gdf_hourly,
        name="Barcelona Tram Capacity (27 May 2019)",
        style_function=style_function_capacity,
        tooltip=folium.GeoJsonTooltip(
            fields=['idTram', 'estatActual', 'estatPrevist', 'diff_perc', 'Descripció'],
            aliases=['Tram ID', 'Observed Capacity', 'Forecasted Capacity', 'Load (%)', 'Description'], 
            localize=True)
    )
    .add_to(hourly_map)
)

# add legend, see <https://github.com/python-visualization/folium/issues/528>
hourly_map.add_child(colorscale)

# add a searchbox bound to the Description of lines
statesearch = Search(
    layer=tram_map,
    geom_type='Line',
    placeholder='Search for a Tram Description',
    collapsed=False,
    search_label='Descripció',
    weight=3
).add_to(hourly_map)


# plot map
hourly_map