# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
"""
Define the *availability* $U(s,t)$ of a station $s$ at a moment in time $t$ as
* $$U(s,0)=0, \forall s$$
* $$U(s,t)=U(s,t-\delta t)+Arrivals(s,t)-Departures(s,t), \forall s, \forall t>0$$
where
* $Arrivals(s,t)$ is the number of observations with *end station* $s$ and *stoptime* $t$
* $Departures(s,t)$ is the number of observations with *start station* $s$ and *starttime* $t$
"""
"""
As we know the latitude and longitude for each station $s$, we can express $U$ as
$$U(s,t)=U(x,y,t)$$
where $x,y$ are the geographical coordinates of station $s$.
"""
ds = pd.read_csv('../input/citibike-system-data/201306-citibike-tripdata.csv')
ds.head()
"""
We create a dataframe about $Departures(s,t)$ for each station $s$ and time $t$, grouped by station and sorted by time (ascending).
"""
start = ds.groupby(['start station id', 'starttime', 'start station name', 'start station latitude',
                    'start station longitude'])\
          .size().reset_index(name='counts')
start = start.sort_values(['start station id', 'starttime'])
start.head()
"""
We create a dataframe about $Arrivals(s,t)$ for each station $s$ and time $t$, grouped by station and sorted by time (ascending).
"""
end = ds.groupby(['end station id', 'stoptime', 'end station name', 'end station latitude', 'end station longitude'])\
        .size().reset_index(name='counts')
end = end.sort_values(['end station id', 'stoptime'])
end.head()
"""
We convert each value to its opposite as this will help with later calculations (remember the definition of $U$).
"""
start['counts'] = -start['counts']
start.head()
end['end station id'] = end['end station id'].astype('int64')
end.head()
"""
As *departures* and *arrivals* now have the same structure, we rename the corresponding columns to common names and append the dataframes.
"""
start = start.rename(columns={"start station id":"station id", "starttime":"time", "start station name":"station name",
                              "start station latitude":"station latitude", "start station longitude":"station longitude"})
end = end.rename(columns={"end station id":"station id", "stoptime":"time", "end station name":"station name",
                          "end station latitude":"station latitude", "end station longitude":"station longitude"})
U = start.append(end)
U.head()
"""
We group by station and sort by time (ascending).
"""
U = U.sort_values(['station id', 'time'])
U.head()
"""
We calculate the *availability* column as the running sum for each station. (Remember that *departures* were stored as their opposites)
"""
U['counts'] = U.groupby('station id')['counts'].cumsum()
U = U.rename(columns={"counts":"availability"})
U.head()
output = U
output.to_csv('CitiBike System Data Stations.csv', index=False)