%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
try:
    import seaborn
except ImportError:
    pass

pd.options.display.max_rows = 8

"""
Explanation: Reshaping data with stack and unstack
End of explanation
"""


!head -1 ./data/BETR8010000800100hour.1-1-1990.31-12-2012

"""
Explanation: Case study: air quality data of European monitoring stations (AirBase)
Going further with the time series case study test on the AirBase (The European Air quality dataBase) data: the actual data downloaded from the Airbase website did not look like a nice csv file (data/airbase_data.csv).
One of the actual downloaded raw data files of AirBase is included in the repo:
End of explanation
"""


data = pd.read_csv("data/BETR8010000800100hour.1-1-1990.31-12-2012", sep='\t')#, header=None)

data.head()

"""
Explanation: Just reading the tab-delimited data:
End of explanation
"""


df = pd.DataFrame({'A':['one', 'one', 'two', 'two'], 'B':['a', 'b', 'a', 'b'], 'C':range(4)})
df

"""
Explanation: The above data is clearly not ready to be used! Each row contains the 24 measurements for each hour of the day, and also contains a flag (0/1) indicating the quality of the data.
<div class="alert alert-success">
    <b>EXERCISE</b>: Clean up this dataframe using more options of `read_csv`
</div>


specify that the values of -999 and -9999 should be regarded as NaN
specify are own column names (http://stackoverflow.com/questions/6356041/python-intertwining-two-lists)

For now, we disregard the 'flag' columns
Now, we want to reshape it: our goal is to have the different hours as row indices, merged with the date into a datetime-index.
Intermezzo: reshaping your data with stack, unstack and pivot
The docs say:

Pivot a level of the (possibly hierarchical) column labels, returning a
DataFrame (or Series in the case of an object with a single level of
column labels) having a hierarchical index with a new inner-most level
of row labels.

<img src="img/stack.png" width=70%>
End of explanation
"""


df = df.set_index(['A', 'B'])
df

result = df['C'].unstack()
result

df = result.stack().reset_index(name='C')
df

"""
Explanation: To use stack/unstack, we need the values we want to shift from rows to columns or the other way around as the index:
End of explanation
"""


df.pivot(index='A', columns='B', values='C')

"""
Explanation: pivot is similar to unstack, but let you specify column names:
End of explanation
"""


df = pd.DataFrame({'A':['one', 'one', 'two', 'two', 'one', 'two'], 'B':['a', 'b', 'a', 'b', 'a', 'b'], 'C':range(6)})
df

df.pivot_table(index='A', columns='B', values='C', aggfunc='count') #'mean'

"""
Explanation: pivot_table is similar as pivot, but can work with duplicate indices and let you specify an aggregation function:
End of explanation
"""


colnames = ['date'] + [item for pair in zip(["{:02d}".format(i) for i in range(24)], ['flag']*24) for item in pair]

data = pd.read_csv("data/BETR8010000800100hour.1-1-1990.31-12-2012",
                   sep='\t', header=None, na_values=[-999, -9999], names=colnames)
data = data.drop('flag', axis=1)

data.head()

"""
Explanation: Back to our case study
We can now use stack and some other functions to create a timeseries from the original dataframe:
End of explanation
"""


cast = pd.read_csv('data/cast.csv')
cast.head()

titles = pd.read_csv('data/titles.csv')
titles.head()

"""
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Reshape the dataframe to a timeseries
</div>

The end result should look like:
<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>BETR801</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>1990-01-02 09:00:00</th>
      <td>48.0</td>
    </tr>
    <tr>
      <th>1990-01-02 12:00:00</th>
      <td>48.0</td>
    </tr>
    <tr>
      <th>1990-01-02 13:00:00</th>
      <td>50.0</td>
    </tr>
    <tr>
      <th>1990-01-02 14:00:00</th>
      <td>55.0</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
    </tr>
    <tr>
      <th>2012-12-31 20:00:00</th>
      <td>16.5</td>
    </tr>
    <tr>
      <th>2012-12-31 21:00:00</th>
      <td>14.5</td>
    </tr>
    <tr>
      <th>2012-12-31 22:00:00</th>
      <td>16.5</td>
    </tr>
    <tr>
      <th>2012-12-31 23:00:00</th>
      <td>15.0</td>
    </tr>
  </tbody>
</table>
<p>170794 rows × 1 columns</p>
</div>

First, reshape the dataframe so that each row consists of one observation for one date + hour combination:
Now, combine the date and hour colums into a datetime (tip: string columns can be summed to concatenate the strings):
We can also use this with the movie data
End of explanation
"""


