import pandas as pd
import matplotlib.pyplot as plt
plt.style.use('seaborn-whitegrid')

"""
Explanation: <p><font size="6"><b> CASE - Bike count data</b></font></p>


© 2021, Joris Van den Bossche and Stijn Van Hoey  (&#106;&#111;&#114;&#105;&#115;&#118;&#97;&#110;&#100;&#101;&#110;&#98;&#111;&#115;&#115;&#99;&#104;&#101;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;, &#115;&#116;&#105;&#106;&#110;&#118;&#97;&#110;&#104;&#111;&#101;&#121;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;). Licensed under CC BY 4.0 Creative Commons


<img src="https://static.nieuwsblad.be/Assets/Images_Upload/2014/04/17/57b8f34e-5042-11e2-80ee-5d1d7b74455f_original.jpg.h380.jpg.568.jpg?maxheight=460&maxwidth=638&scale=both">
In this case study, we will make use of the openly available bike count data of the city of Ghent (Belgium). At the Coupure Links, next to the Faculty of Bioscience Engineering, a counter keeps track of the number of passing cyclists in both directions.
End of explanation
"""


df = pd.read_csv("data/fietstellingencoupure.csv", sep=';')

df.head()

df.tail()

len(df)

df.dtypes

"""
Explanation: Reading and processing the data
Read csv data
The data were previously available on the open data portal of the city, and we downloaded them in the CSV format, and provided the original file as data/fietstellingencoupure.csv.
This dataset contains the historical data of the bike counters, and consists of the following columns:

The first column datum is the date, in dd/mm/yy format
The second column tijd is the time of the day, in hh:mm format
The third and fourth column ri Centrum and ri Mariakerke are the counts at that point in time (counts between this timestamp and the previous)

<div class="alert alert-success">

**EXERCISE**

- Read the csv file from the url into a DataFrame `df`, the delimiter of the data is `;`
- Inspect the first and last 5 rows, and check the number of observations
- Inspect the data types of the different columns

<details><summary>Hints</summary>

- With the cursor on a function, you can combine the SHIFT + TAB keystrokes to see the documentation of a function.
- Both the `sep` and `delimiter` argument will work to define the delimiter.
- Methods like `head`/`tail` have round brackets `()`, attributes like `dtypes` not.

</details>

</div>
End of explanation
"""


combined = df['datum'] + ' ' + df['tijd']
combined.head()

df.index = pd.to_datetime(combined, format="%d/%m/%Y %H:%M")

df2 = df.drop(columns=['datum', 'tijd'])

df2 = df2.rename(columns={'ri Centrum': 'direction_centre',
                          'ri Mariakerke':'direction_mariakerke'})

df2.head()

"""
Explanation: Data processing
As explained above, the first and second column (respectively datum and tijd) indicate the date and hour of the day. To obtain a time series, we have to combine those two columns into one series of actual timestamp values.
<div class="alert alert-success">

**EXERCISE**

Pre-process the data:

* Combine the 'datum' and 'tijd' columns into one Pandas Series of string datetime values, call this new variable `combined`.
* Parse the string datetime values to `datetime` objects.
* Set the resulting `datetime` column as the index of the `df` DataFrame.
* Remove the original 'datum' and 'tijd' columns using the `drop` method, and call the new dataframe `df2`.
* Rename the columns in the DataFrame 'ri Centrum', 'ri Mariakerke' to resp. 'direction_centre', 'direction_mariakerke' using the `rename` method.

<details><summary>Hints</summary>

- Concatenating strings can be done with the addition operation `+`.
- When converting strings to a `datetime` with `pd.to_datetime`, specifying the format will make the conversion a lot faster.
- `drop` can remove both rows and columns using the names of the index or column name. Make sure to define `columns=` argument to remove columns.
- `rename` can be used for both rows/columns. It needs a dictionary with the current names as keys and the new names as values.

</details>
End of explanation
"""


fig, ax = plt.subplots(figsize=(10, 6))
df.plot(colormap='coolwarm', ax=ax)

"""
Explanation: Having the data available with an interpreted datetime, provides us the possibility of having time aware plotting:
End of explanation
"""


combined = df['datum'] + ' ' + df['tijd']

%timeit -n 1 -r 1 pd.to_datetime(combined, dayfirst=True)

"""
Explanation: <div class="alert alert-warning">

 <b>Remark</b>: Interpretation of the dates with and without predefined date format.

</div>

When we just want to interpret the dates, without specifying how the dates are formatted, Pandas makes an attempt as good as possible:
End of explanation
"""


%timeit pd.to_datetime(combined, format="%d/%m/%Y %H:%M")

"""
Explanation: However, when we already know the format of the dates (and if this is consistent throughout the full dataset), we can use this information to interpret the dates:
End of explanation
"""


def process_bike_count_data(df):
    """Process the provided dataframe: parse datetimes and rename columns.

    Parameters
    ----------
    df : pandas.DataFrame
        DataFrame as read from the raw `fietstellingen`,
        containing the `datum`, `tijd`, `ri Centrum`
        and `ri Mariakerke` columns.

    Returns
    -------
    df2 : pandas.DataFrame
        DataFrame with the datetime info as index and the
        `direction_centre` and `direction_mariakerke` columns
        with the counts.
    """
    df.index = pd.to_datetime(df['datum'] + ' ' + df['tijd'],
                              format="%d/%m/%Y %H:%M")
    df2 = df.drop(columns=['datum', 'tijd'])
    df2 = df2.rename(columns={'ri Centrum': 'direction_centre',
                              'ri Mariakerke':'direction_mariakerke'})
    return df2

df_raw = pd.read_csv("data/fietstellingencoupure.csv", sep=';')
df_preprocessed = process_bike_count_data(df_raw)
df_preprocessed.head()

"""
Explanation: <div class="alert alert-info">

 <b>Remember</b>: Whenever possible, specify the date format to interpret the dates to `datetime` values!

</div>

Write the dataset cleaning as a function
In order to make it easier to reuse the code for the pre-processing we have implemented, let's convert the code to a Python function:
<div class="alert alert-success">

**EXERCISE**

Write a function `process_bike_count_data(df)` that performs the processing steps as done above for an input Pandas DataFrame and returns the updated DataFrame.

<details><summary>Hints</summary>

- Want to know more about proper documenting your Python functions? Check out the official guide of [numpydoc](https://numpydoc.readthedocs.io/en/latest/format.html). The `Parameters` and `Returns` sections should always be explained.

</details>
End of explanation
"""


df_preprocessed.to_csv("bike_count_interim.csv")

"""
Explanation: Store our collected dataset as an interim data product
As we finished our data-collection step, we want to save this result as an interim data output of our small investigation. As such, we do not have to re-download all the files each time something went wrong, but can restart from our interim step.
End of explanation
"""


df = pd.read_csv("bike_count_interim.csv", index_col=0, parse_dates=True)

"""
Explanation: Data exploration and analysis
We now have a cleaned-up dataset of the bike counts at Coupure Links in Ghent (Belgium). Next, we want to get an impression of the characteristics and properties of the data
Load the interim data
Reading the file in from the interim file (when you want to rerun the whole analysis on the updated online data, you would comment out this cell...)
End of explanation
"""


df.index.to_series().diff()

"""
Explanation: Count interval verification
The number of bikers are counted for intervals of approximately 15 minutes. But let's check if this is indeed the case. Calculate the difference between each of the consecutive values of the index. We can use the Series.diff() method:
End of explanation
"""


df.index.to_series().diff().value_counts()

"""
Explanation: The count of the possible intervals is of interest:
End of explanation
"""


df.describe()

"""
Explanation: There are a few records that are not exactly 15min. But given it are only a few ones, we will ignore this for the current case study and just keep them for this explorative study.
Bonus question: do you know where the values of -1 days +23:15:01 and 01:15:00 are coming from?
End of explanation
"""


df_both = df.sum(axis=1)
df_both

"""
Explanation: Quiet periods
<div class="alert alert-success">

**EXERCISE**

Create a new Pandas Series `df_both` which contains the sum of the counts of both directions.

<details><summary>Hints</summary>

- Check the purpose of the `axis` argument of the `sum` method.

</details>
End of explanation
"""


df_quiet = df_both[df_both < 5]

"""
Explanation: <div class="alert alert-success">

**EXERCISE**

Using the `df_both` from the previous exercise, create a new Series `df_quiet` which contains only those intervals for which less than 5 cyclists passed in both directions combined

<details><summary>Hints</summary>

- Use the `[]` to select data. You can use conditions (so-called _boolean indexing_) returning True/False inside the brackets.

</details>
End of explanation
"""


df[(df['direction_centre'] < 3) | (df['direction_mariakerke'] < 3)]

"""
Explanation: <div class="alert alert-success">

**EXERCISE**

Using the original data `df`, select only the intervals for which less than 3 cyclists passed in one or the other direction. Hence, less than 3 cyclists towards the center or less than 3 cyclists towards Mariakerke.

<details><summary>Hints</summary>

- To combine conditions use the `|` (or) or the `&` (and) operators.
- Make sure to use `()` around each individual condition.

</details>
End of explanation
"""


df.mean()

"""
Explanation: Count statistics
<div class="alert alert-success">

**EXERCISE**

What is the average number of bikers passing every 15 min in each direction?

<details><summary>Hints</summary>

- As the time series is already 15min level, this is just the same as taking the mean.

</details>
End of explanation
"""


df.resample('H').sum().mean()

"""
Explanation: <div class="alert alert-success">

**EXERCISE**

What is the average number of bikers passing each hour?

<details><summary>Hints</summary>

- Use `resample` to first calculate the number of bikers passing each hour.
- `resample` requires an aggregation function that defines how to combine the values within each group (in this case all values within each hour).

</details>
End of explanation
"""


df['direction_centre'].nlargest(10)
# alternative:
# df['direction_centre'].sort_values(ascending=False).head(10)

"""
Explanation: <div class="alert alert-success">

**EXERCISE**

What are the 10 highest peak values observed during any of the intervals for the direction towards the center of Ghent?

<details><summary>Hints</summary>

- Pandas provides the `nsmallest` and  `nlargest` methods to derive N smallest/largest values of a column.

</details>
End of explanation
"""


df_both = df.sum(axis=1)

df_daily = df_both.resample('D').sum()

df_daily.max()

df_daily.nlargest(10)

"""
Explanation: <div class="alert alert-success">

**EXERCISE**

What is the maximum number of cyclist that passed on a single day calculated on both directions combined?

<details><summary>Hints</summary>

- Combine both directions by taking the sum.
- Next, `resample` to daily values
- Get the maximum value or ask for the n largest to see the dates as well.

</details>
End of explanation
"""


df_monthly = df.resample('M').sum()
df_monthly.plot()

"""
Explanation: The high number of bikers passing on 2013-06-05 was not by coincidence: http://www.nieuwsblad.be/cnt/dmf20130605_022 ;-)
Trends as function of time
<div class="alert alert-success">

**EXERCISE**

How does the long-term trend look like? Calculate monthly sums and plot the result.

<details><summary>Hints</summary>

- The symbol for monthly resampling is `M`.
- Use the `plot` method of Pandas, which will generate a line plot of each numeric column by default.

</details>
End of explanation
"""


df_hourly = df.resample('H').sum()

df_hourly.head()

df_hourly['2014-01-01':'2014-01-20'].plot()

"""
Explanation: <div class="alert alert-success">

**EXERCISE**

Let's have a look at some short term patterns. For the data of the first 3 weeks of January 2014, calculate the hourly counts and visualize them.

<details><summary>Hints</summary>

- Slicing is done using `[]`, you can use string representation of dates to select from a `datetime` index: e.g. `'2010-01-01':'2020-12-31'`

</details>
End of explanation
"""


newyear = df["2013-12-31 12:00:00": "2014-01-01 12:00:00"]

newyear.plot()

newyear.rolling(10, center=True).mean().plot(linewidth=2)

"""
Explanation: New Year's Eve 2013-2014
<div class="alert alert-success">

**EXERCISE**

- Select a subset of the dataset from 2013-12-31 12:00:00 until 2014-01-01 12:00:00 and assign the result to a new variable `newyear`
- Plot the selected data `newyear`.
- Use a `rolling` function with a window of 10 values (check documentation of the function) to smooth the data of this period and make a plot of the smoothed version.

<details><summary>Hints</summary>

- Just like `resample`, `rolling` requires an aggregate statistic (e.g. mean, median,...) to combine the values within the window.

</details>
End of explanation
"""


# A more in-detail plotting version of the graph.
fig, ax = plt.subplots()
newyear.plot(ax=ax, color=['LightGreen', 'LightBlue'], legend=False, rot=0)
newyear.rolling(10, center=True).mean().plot(linewidth=2, ax=ax, color=['DarkGreen', 'DarkBlue'], rot=0)

ax.set_xlabel('')
ax.set_ylabel('Cyclists count')

"""
Explanation: A more advanced usage of Matplotlib to create a combined plot:
End of explanation
"""


df_daily = df.resample('D').sum()

df_daily.groupby(df_daily.index.dayofweek).mean().plot(kind='bar')

"""
Explanation: The power of groupby...
Looking at the data in the above exercises, there seems to be clearly a:

daily pattern
weekly pattern
yearly pattern

Such patterns can easily be calculated and visualized in pandas using the DatetimeIndex attributes dayofweek combined with groupby functionality. Below a taste of the possibilities, and we will learn about this in the proceeding notebooks:
Weekly pattern:
End of explanation
"""


df_hourly = df.resample('H').sum()

df_hourly.groupby(df_hourly.index.hour).mean().plot()

"""
Explanation: Daily pattern:
End of explanation
"""


df_monthly = df.resample('M').sum()

from calendar import month_abbr

ax = df_monthly.groupby(df_monthly.index.month).mean().plot()
ax.set_ylim(0)
xlabels = ax.set_xticklabels(list(month_abbr)[0::2]) #too lazy to write the month values yourself...

"""
Explanation: So the daily pattern is clearly different for both directions. In the morning more people go north, in the evening more people go south. The morning peak is also more condensed.
Monthly pattern
End of explanation
"""


