.. _mapreducetimeseriesrst:

================================
Séries temporelles et map reduce
================================


.. only:: html

    **Links:** :download:`notebook <map_reduce_timeseries.ipynb>`, :downloadlink:`html <map_reduce_timeseries2html.html>`, :download:`PDF <map_reduce_timeseries.pdf>`, :download:`python <map_reduce_timeseries.py>`, :downloadlink:`slides <map_reduce_timeseries.slides.html>`, :githublink:`GitHub|_doc/notebooks/sql/map_reduce_timeseries.ipynb|*`


Map/Reduce est un concept qui permet de distribuer les données
facilement si elles sont indépendantes. C’est une condition qu’une série
temporelle ne vérifie pas du fait de la dépendance temporelle. Voyons ce
que cela change.

.. code:: ipython3

    from jyquickhelper import add_notebook_menu
    add_notebook_menu()






.. contents::
    :local:





.. code:: ipython3

    # Répare une incompatibilité entre scipy 1.0 et statsmodels 0.8.
    from pymyinstall.fix import fix_scipy10_for_statsmodels08
    fix_scipy10_for_statsmodels08()

On souhaite simplement calculer la dérivée de la série temporelle :
:math:`\Delta Y_t = Y_t - Y_{t-1}`.

.. code:: ipython3

    %matplotlib inline

Data
----

.. code:: ipython3

    from pyensae.finance import StockPrices
    stock = StockPrices("MSFT", folder=".", url="yahoo")
    stock.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Date</th>
          <th>High</th>
          <th>Low</th>
          <th>Open</th>
          <th>Close</th>
          <th>Volume</th>
          <th>Adj Close</th>
        </tr>
        <tr>
          <th>Date</th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>2020-12-24</th>
          <td>2020-12-24</td>
          <td>223.610001</td>
          <td>221.199997</td>
          <td>221.419998</td>
          <td>222.750000</td>
          <td>10550600.0</td>
          <td>222.750000</td>
        </tr>
        <tr>
          <th>2020-12-28</th>
          <td>2020-12-28</td>
          <td>226.029999</td>
          <td>223.020004</td>
          <td>224.449997</td>
          <td>224.960007</td>
          <td>17933500.0</td>
          <td>224.960007</td>
        </tr>
        <tr>
          <th>2020-12-29</th>
          <td>2020-12-29</td>
          <td>227.179993</td>
          <td>223.580002</td>
          <td>226.309998</td>
          <td>224.149994</td>
          <td>17403200.0</td>
          <td>224.149994</td>
        </tr>
        <tr>
          <th>2020-12-30</th>
          <td>2020-12-30</td>
          <td>225.630005</td>
          <td>221.470001</td>
          <td>225.229996</td>
          <td>221.679993</td>
          <td>20272300.0</td>
          <td>221.679993</td>
        </tr>
        <tr>
          <th>2020-12-31</th>
          <td>2020-12-31</td>
          <td>223.000000</td>
          <td>219.679993</td>
          <td>221.699997</td>
          <td>222.419998</td>
          <td>20926900.0</td>
          <td>222.419998</td>
        </tr>
      </tbody>
    </table>
    </div>



On crée une colonne supplémentaire pour l’indice :math:`t` de la série
temporelle :

.. code:: ipython3

    dt = stock.df()
    dt = dt[["Close"]]
    dt = dt.reset_index(drop=True)
    data = dt
    dt.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Close</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>5279</th>
          <td>222.750000</td>
        </tr>
        <tr>
          <th>5280</th>
          <td>224.960007</td>
        </tr>
        <tr>
          <th>5281</th>
          <td>224.149994</td>
        </tr>
        <tr>
          <th>5282</th>
          <td>221.679993</td>
        </tr>
        <tr>
          <th>5283</th>
          <td>222.419998</td>
        </tr>
      </tbody>
    </table>
    </div>



La fonction
`shift <http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.shift.html>`__
rend le calcul très simple avec pandas.

.. code:: ipython3

    data_pandas = data.copy()
    data_pandas["Close2"] = data.shift(1)
    data_pandas["delta"] = data_pandas["Close"] - data_pandas["Close2"]
    data_pandas.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Close</th>
          <th>Close2</th>
          <th>delta</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>5279</th>
          <td>222.750000</td>
          <td>221.020004</td>
          <td>1.729996</td>
        </tr>
        <tr>
          <th>5280</th>
          <td>224.960007</td>
          <td>222.750000</td>
          <td>2.210007</td>
        </tr>
        <tr>
          <th>5281</th>
          <td>224.149994</td>
          <td>224.960007</td>
          <td>-0.810013</td>
        </tr>
        <tr>
          <th>5282</th>
          <td>221.679993</td>
          <td>224.149994</td>
          <td>-2.470001</td>
        </tr>
        <tr>
          <th>5283</th>
          <td>222.419998</td>
          <td>221.679993</td>
          <td>0.740005</td>
        </tr>
      </tbody>
    </table>
    </div>



La fonction est très rapide car elle utilise l’ordre des données et son
index. Pour s’en passer, on ajoute une colonne qui contient l’index
original puis on mélange pour simuler le fait qu’en map/reduce, l’ordre
des informations n’est pas connu à l’avance.

.. code:: ipython3

    import numpy
    data = dt.reindex(numpy.random.permutation(data.index))
    data = data.reset_index(drop=False)
    data.columns = ["t", "close"]
    data.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>close</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>5279</th>
          <td>4660</td>
          <td>104.190002</td>
        </tr>
        <tr>
          <th>5280</th>
          <td>476</td>
          <td>31.870001</td>
        </tr>
        <tr>
          <th>5281</th>
          <td>3522</td>
          <td>36.910000</td>
        </tr>
        <tr>
          <th>5282</th>
          <td>2867</td>
          <td>24.670000</td>
        </tr>
        <tr>
          <th>5283</th>
          <td>4348</td>
          <td>65.480003</td>
        </tr>
      </tbody>
    </table>
    </div>



Dérivée avec pandas
-------------------

Le traitement de chaque ligne est indépendant et ne doit pas prendre en
compte aucune autre ligne mais on a besoin de :math:`Y_{t-1}` pour
calculer :math:`\Delta Y_t`.

.. code:: ipython3

    data["tt"] = data["t"] - 1 

méthode efficace
~~~~~~~~~~~~~~~~

Lors d’une jointure, `pandas <http://pandas.pydata.org/>`__ va trier
chaque côté de la jointure par ordre croissant de clé. S’il y a
:math:`N` observations, cela a un coût de :math:`N\ln N`, il réalise
ensuite une fusion des deux bases en ne considérant que les lignes
partageant la même clé. Cette façon de faire ne convient que lorsqu’on
fait une jointure avec une condition n’incluant que des ET logique et
des égalités.

.. code:: ipython3

    join = data.merge(data, left_on="t", right_on="tt", suffixes=("", "2"))
    join.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>close</th>
          <th>tt</th>
          <th>t2</th>
          <th>close2</th>
          <th>tt2</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>5278</th>
          <td>4660</td>
          <td>104.190002</td>
          <td>4659</td>
          <td>4661</td>
          <td>105.430000</td>
          <td>4660</td>
        </tr>
        <tr>
          <th>5279</th>
          <td>476</td>
          <td>31.870001</td>
          <td>475</td>
          <td>477</td>
          <td>31.400000</td>
          <td>476</td>
        </tr>
        <tr>
          <th>5280</th>
          <td>3522</td>
          <td>36.910000</td>
          <td>3521</td>
          <td>3523</td>
          <td>36.130001</td>
          <td>3522</td>
        </tr>
        <tr>
          <th>5281</th>
          <td>2867</td>
          <td>24.670000</td>
          <td>2866</td>
          <td>2868</td>
          <td>24.760000</td>
          <td>2867</td>
        </tr>
        <tr>
          <th>5282</th>
          <td>4348</td>
          <td>65.480003</td>
          <td>4347</td>
          <td>4349</td>
          <td>65.389999</td>
          <td>4348</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    derivee = join.copy()
    derivee["derivee"] = derivee["close"] - derivee["close2"]
    derivee.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>close</th>
          <th>tt</th>
          <th>t2</th>
          <th>close2</th>
          <th>tt2</th>
          <th>derivee</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>5278</th>
          <td>4660</td>
          <td>104.190002</td>
          <td>4659</td>
          <td>4661</td>
          <td>105.430000</td>
          <td>4660</td>
          <td>-1.239998</td>
        </tr>
        <tr>
          <th>5279</th>
          <td>476</td>
          <td>31.870001</td>
          <td>475</td>
          <td>477</td>
          <td>31.400000</td>
          <td>476</td>
          <td>0.470001</td>
        </tr>
        <tr>
          <th>5280</th>
          <td>3522</td>
          <td>36.910000</td>
          <td>3521</td>
          <td>3523</td>
          <td>36.130001</td>
          <td>3522</td>
          <td>0.779999</td>
        </tr>
        <tr>
          <th>5281</th>
          <td>2867</td>
          <td>24.670000</td>
          <td>2866</td>
          <td>2868</td>
          <td>24.760000</td>
          <td>2867</td>
          <td>-0.090000</td>
        </tr>
        <tr>
          <th>5282</th>
          <td>4348</td>
          <td>65.480003</td>
          <td>4347</td>
          <td>4349</td>
          <td>65.389999</td>
          <td>4348</td>
          <td>0.090004</td>
        </tr>
      </tbody>
    </table>
    </div>



En résumé :

.. code:: ipython3

    new_data = data.copy()
    new_data["tt"] = new_data["t"] + 1  # MAP
    new_data = new_data.merge(new_data, left_on="t", right_on="tt", suffixes=("", "2")) # JOIN = SORT + MAP + REDUCE
    new_data["derivee"] = new_data["close"] - new_data["close2"] # MAP
    print(new_data.shape)
    new_data.tail()


.. parsed-literal::
    (5283, 7)






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>close</th>
          <th>tt</th>
          <th>t2</th>
          <th>close2</th>
          <th>tt2</th>
          <th>derivee</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>5278</th>
          <td>4660</td>
          <td>104.190002</td>
          <td>4661</td>
          <td>4659</td>
          <td>101.980003</td>
          <td>4660</td>
          <td>2.209999</td>
        </tr>
        <tr>
          <th>5279</th>
          <td>476</td>
          <td>31.870001</td>
          <td>477</td>
          <td>475</td>
          <td>32.570000</td>
          <td>476</td>
          <td>-0.699999</td>
        </tr>
        <tr>
          <th>5280</th>
          <td>3522</td>
          <td>36.910000</td>
          <td>3523</td>
          <td>3521</td>
          <td>37.160000</td>
          <td>3522</td>
          <td>-0.250000</td>
        </tr>
        <tr>
          <th>5281</th>
          <td>2867</td>
          <td>24.670000</td>
          <td>2868</td>
          <td>2866</td>
          <td>24.190001</td>
          <td>2867</td>
          <td>0.480000</td>
        </tr>
        <tr>
          <th>5282</th>
          <td>4348</td>
          <td>65.480003</td>
          <td>4349</td>
          <td>4347</td>
          <td>64.949997</td>
          <td>4348</td>
          <td>0.530006</td>
        </tr>
      </tbody>
    </table>
    </div>



mesure de coût
~~~~~~~~~~~~~~

La série n’est pas assez longue pour observer la relation entre le temps
de calcul et le nombre d’observations.

.. code:: ipython3

    def derive(data):
        new_data = data.copy()
        new_data["tt"] = new_data["t"] + 1  # MAP
        new_data = new_data.merge(new_data, left_on="t", right_on="tt", suffixes=("", "2")) # JOIN = MAP + REDUCE
        new_data["derivee"] = new_data["close"] - new_data["close2"] # MAP
        return new_data

On choisit une régression linéaire de type
`HuberRegressor <http://scikit-learn.org/stable/modules/generated/sklearn.linear_model.HuberRegressor.html#sklearn.linear_model.HuberRegressor>`__
pour régresser :math:`C(N) \sim a N + b \ln N + c N \ln N + d`. Ce
modèle est beaucoup moins sensible aux points aberrants que les autres
formes de régressions. On utilise cette régression avec le module
`RobustLinearModels <http://statsmodels.sourceforge.net/stable/rlm.html>`__
du module *statsmodels* pour calculer les p-values

.. code:: ipython3

    import time, pandas, numpy, sklearn.linear_model as lin
    import matplotlib.pyplot as plt
    import statsmodels.api as smapi
    from random import randint
    
    def graph_cout(data, h=100, nb=10, add_n2=False, derive=derive):
        dh = len(data) // h
        res = []
        for n in range(max(dh, 500), len(data), dh):
            df = data[0:n+randint(-10,10)]
            mean = []
            for i in range(0, nb):
                t = time.perf_counter()
                derive(df)
                dt = time.perf_counter() - t
                mean.append(dt)
            res.append((n, mean[len(mean)//2]))
        # stat
        stat = pandas.DataFrame(res, columns=["N", "processing time"])
        stat["logN"] = numpy.log(stat["N"]) / numpy.log(10)
        stat["NlogN"] = stat["N"] * stat["logN"]
        stat["N2"] = stat["N"] * stat["N"]
        # statsmodels
        stat["one"] = 1
        X = stat[["logN", "N", "NlogN", "N2", "one"]]
        if not add_n2:
            X = X.drop("N2", axis=1)
        rlm_model = smapi.RLM(stat["processing time"], X, M=smapi.robust.norms.HuberT())
        rlm_results = rlm_model.fit()
        yp = rlm_results.predict(X)
        print(yp.shape, type(yp))
        stat["smooth"] = yp
        # graph
        fig, ax = plt.subplots()
        stat.plot(x="N", y=["processing time", "smooth"], ax=ax)
        return ax, rlm_results
    
    ax, results = graph_cout(data)
    print(results.summary())
    ax;


.. parsed-literal::
    (92,) <class 'pandas.core.series.Series'>
                        Robust linear Model Regression Results                    
    ==============================================================================
    Dep. Variable:        processing time   No. Observations:                   92
    Model:                            RLM   Df Residuals:                       88
    Method:                          IRLS   Df Model:                            3
    Norm:                          HuberT                                         
    Scale Est.:                       mad                                         
    Cov Type:                          H1                                         
    Date:                Fri, 01 Jan 2021                                         
    Time:                        03:27:54                                         
    No. Iterations:                    50                                         
    ==============================================================================
                     coef    std err          z      P>|z|      [0.025      0.975]
    ------------------------------------------------------------------------------
    logN           0.0035      0.001      2.661      0.008       0.001       0.006
    N          -8.829e-06   2.77e-06     -3.191      0.001   -1.43e-05   -3.41e-06
    NlogN       2.117e-06   6.56e-07      3.228      0.001    8.31e-07     3.4e-06
    one           -0.0049      0.003     -1.544      0.123      -0.011       0.001
    ==============================================================================
    If the model instance has been used for another fit with different fit parameters, then the fit options might not be the correct ones anymore .



.. image:: map_reduce_timeseries_24_1.png


Le coût de l’algorithme est au moins linéaire. Cela signifie que le coût
du calcul qu’on cherche à mesurer est noyé dans plein d’autres choses
non négligeable. La valeur des coefficients n’indique pas grand chose
car les variables :math:`N`, :math:`Nlog N` évoluent sur des échelles
différentes.

.. code:: ipython3

    N = 200000
    from random import random
    data2 = pandas.DataFrame({"t":range(0,N), "close": [random() for i in range(0, N)]})
    ax, stats = graph_cout(data2, h=25)
    print(stats.summary())
    ax;


.. parsed-literal::
    (24,) <class 'pandas.core.series.Series'>
                        Robust linear Model Regression Results                    
    ==============================================================================
    Dep. Variable:        processing time   No. Observations:                   24
    Model:                            RLM   Df Residuals:                       20
    Method:                          IRLS   Df Model:                            3
    Norm:                          HuberT                                         
    Scale Est.:                       mad                                         
    Cov Type:                          H1                                         
    Date:                Fri, 01 Jan 2021                                         
    Time:                        03:28:04                                         
    No. Iterations:                    47                                         
    ==============================================================================
                     coef    std err          z      P>|z|      [0.025      0.975]
    ------------------------------------------------------------------------------
    logN          -0.0396      0.017     -2.327      0.020      -0.073      -0.006
    N            5.63e-06   1.75e-06      3.213      0.001     2.2e-06    9.06e-06
    NlogN      -9.342e-07   3.08e-07     -3.036      0.002   -1.54e-06   -3.31e-07
    one            0.1434      0.064      2.232      0.026       0.017       0.269
    ==============================================================================
    If the model instance has been used for another fit with different fit parameters, then the fit options might not be the correct ones anymore .



.. image:: map_reduce_timeseries_26_1.png


Les résultats sont assez volatiles. Il faut regarder les intervalles de
confiance et regarder lesquels n’incluent pas 0.

méthode inefficace
~~~~~~~~~~~~~~~~~~

Dans ce cas, on fait un produit en croix de toutes les lignes de la
bases avec elles-même puis on filtre le résultat pour ne garder que les
lignes qui vérifient la condition souhaitée quelle qu’elle soit. Le
temps d’exécution est en :math:`O(N^2)` et la différence est vite
significative.

.. code:: ipython3

    new_data2 = data.copy()
    new_data2["tt"] = new_data2["t"] + 1  # MAP
    new_data2["key"] = 1
    new_data2 = new_data2.merge(new_data2, on="key", suffixes=("", "2")) # JOIN = MAP^2
    new_data2.shape




.. parsed-literal::
    (27920656, 7)



.. code:: ipython3

    mapind = new_data2.t == new_data2.t2
    new_data2 = new_data2[mapind] # MAP
    new_data2.shape




.. parsed-literal::
    (5284, 7)



.. code:: ipython3

    new_data2["derivee"] = new_data2["t"] - new_data2["t2"] # MAP
    new_data2.tail()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>close</th>
          <th>tt</th>
          <th>key</th>
          <th>t2</th>
          <th>close2</th>
          <th>tt2</th>
          <th>derivee</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>27899515</th>
          <td>4660</td>
          <td>104.190002</td>
          <td>4661</td>
          <td>1</td>
          <td>4660</td>
          <td>104.190002</td>
          <td>4661</td>
          <td>0</td>
        </tr>
        <tr>
          <th>27904800</th>
          <td>476</td>
          <td>31.870001</td>
          <td>477</td>
          <td>1</td>
          <td>476</td>
          <td>31.870001</td>
          <td>477</td>
          <td>0</td>
        </tr>
        <tr>
          <th>27910085</th>
          <td>3522</td>
          <td>36.910000</td>
          <td>3523</td>
          <td>1</td>
          <td>3522</td>
          <td>36.910000</td>
          <td>3523</td>
          <td>0</td>
        </tr>
        <tr>
          <th>27915370</th>
          <td>2867</td>
          <td>24.670000</td>
          <td>2868</td>
          <td>1</td>
          <td>2867</td>
          <td>24.670000</td>
          <td>2868</td>
          <td>0</td>
        </tr>
        <tr>
          <th>27920655</th>
          <td>4348</td>
          <td>65.480003</td>
          <td>4349</td>
          <td>1</td>
          <td>4348</td>
          <td>65.480003</td>
          <td>4349</td>
          <td>0</td>
        </tr>
      </tbody>
    </table>
    </div>



mesure de coût inefficace
~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    def derive_inefficace(data):
        new_data2 = data.copy()
        new_data2["tt"] = new_data2["t"] + 1
        new_data2["key"] = 1
        new_data2 = new_data2.merge(new_data2, on="key", suffixes=("", "2"))
        new_data2 = new_data2[new_data2.t == new_data2.t2]
        new_data2["derivee"] = new_data2["t"] - new_data2["t2"] # MAP
        return new_data2
    
    ax, stats = graph_cout(data, h=10, nb=5, derive=derive_inefficace, add_n2=True)
    print(stats.summary())
    ax;


.. parsed-literal::
    (10,) <class 'pandas.core.series.Series'>
                        Robust linear Model Regression Results                    
    ==============================================================================
    Dep. Variable:        processing time   No. Observations:                   10
    Model:                            RLM   Df Residuals:                        5
    Method:                          IRLS   Df Model:                            4
    Norm:                          HuberT                                         
    Scale Est.:                       mad                                         
    Cov Type:                          H1                                         
    Date:                Fri, 01 Jan 2021                                         
    Time:                        03:29:44                                         
    No. Iterations:                    50                                         
    ==============================================================================
                     coef    std err          z      P>|z|      [0.025      0.975]
    ------------------------------------------------------------------------------
    logN          -3.5525      6.675     -0.532      0.595     -16.635       9.530
    N              0.0226      0.027      0.837      0.403      -0.030       0.076
    NlogN         -0.0064      0.007     -0.889      0.374      -0.021       0.008
    N2          6.411e-07   3.49e-07      1.836      0.066   -4.32e-08    1.33e-06
    one            6.8518     14.218      0.482      0.630     -21.015      34.718
    ==============================================================================
    If the model instance has been used for another fit with different fit parameters, then the fit options might not be the correct ones anymore .



.. image:: map_reduce_timeseries_33_1.png


avec des itérateurs
-------------------

version efficace
~~~~~~~~~~~~~~~~

.. code:: ipython3

    rows = data[["t", "close"]].itertuples()
    list(rows)[:2]




.. parsed-literal::
    [Pandas(Index=0, t=1726, close=29.350000381469727),
     Pandas(Index=1, t=1244, close=27.11000061035156)]



.. code:: ipython3

    rows = data[["t", "close"]].itertuples()
    rows = map(lambda r: (r.t, r.close, r.t+1), rows)
    list(rows)[:2]




.. parsed-literal::
    [(1726, 29.350000381469727, 1727), (1244, 27.11000061035156, 1245)]



.. code:: ipython3

    import copy
    from cytoolz.itertoolz import join
    
    def get_iterrows():
        rows_as_tuple = data[["t", "close"]].itertuples()
        rows = map(lambda r: (r.t, r.close, r.t+1), rows_as_tuple)
        return rows
    
    rows1 = get_iterrows()
    rows2 = get_iterrows()
    rows = join(lambda t: t[2], rows1, lambda t: t[0], rows2)
    rows = map(lambda tu: tu[0] + tu[1], rows)
    rows = map(lambda row: (row[0], row[4] - row[1]), rows)
    results = list(rows)
    results[:4]




.. parsed-literal::
    [(1725, 0.1100006103515625),
     (1243, -0.11999893188476918),
     (3128, -0.75),
     (4095, 0.29000091552734375)]



version inefficace
~~~~~~~~~~~~~~~~~~

La méthode inefficace avec *pandas* est particulièrement inefficace car
elle nécessite de stocker un tableau intermédiaire qui contient
:math:`N^2` lignes.

.. code:: ipython3

    import copy
    from cytoolz.itertoolz import join
    
    def get_iterrows():
        rows_as_tuple = data[["t", "close"]].itertuples()
        rows = map(lambda r: (r.t, r.close, r.t+1, 1), rows_as_tuple)   # on ajoute 1
        return rows
    
    rows1 = get_iterrows()
    rows2 = get_iterrows()
    rows = join(lambda t: t[-1], rows1, lambda t: t[-1], rows2)  # on fait un produit croisé
    rows = map(lambda tu: tu[0] + tu[1], rows)
    rows = filter(lambda t: t[2] == t[4], rows)     # on filtre les lignes qui nous intéresse
    rows = map(lambda row: (row[0], row[5] - row[1]), rows)
    results = list(rows)   # c'est très lent
    results[:4]




.. parsed-literal::
    [(1725, 0.1100006103515625),
     (1243, -0.11999893188476918),
     (3128, -0.75),
     (4095, 0.29000091552734375)]



avec SQL
--------

conversion du dataframe au format SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    data = stock.df()
    data.columns = [_.replace(" ", "_") for _ in data.columns]
    data = data.reset_index(drop=True).reset_index(drop=False)
    data.columns = ["t"] + list(data.columns[1:])
    data.head()






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>Date</th>
          <th>High</th>
          <th>Low</th>
          <th>Open</th>
          <th>Close</th>
          <th>Volume</th>
          <th>Adj_Close</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>0</td>
          <td>2000-01-03</td>
          <td>59.3125</td>
          <td>56.00000</td>
          <td>58.68750</td>
          <td>58.28125</td>
          <td>53228400.0</td>
          <td>37.102634</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1</td>
          <td>2000-01-04</td>
          <td>58.5625</td>
          <td>56.12500</td>
          <td>56.78125</td>
          <td>56.31250</td>
          <td>54119000.0</td>
          <td>35.849308</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2</td>
          <td>2000-01-05</td>
          <td>58.1875</td>
          <td>54.68750</td>
          <td>55.56250</td>
          <td>56.90625</td>
          <td>64059600.0</td>
          <td>36.227283</td>
        </tr>
        <tr>
          <th>3</th>
          <td>3</td>
          <td>2000-01-06</td>
          <td>56.9375</td>
          <td>54.18750</td>
          <td>56.09375</td>
          <td>55.00000</td>
          <td>54976600.0</td>
          <td>35.013741</td>
        </tr>
        <tr>
          <th>4</th>
          <td>4</td>
          <td>2000-01-07</td>
          <td>56.1250</td>
          <td>53.65625</td>
          <td>54.31250</td>
          <td>55.71875</td>
          <td>62013600.0</td>
          <td>35.471302</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    import sqlite3
    con = sqlite3.connect(":memory:")
    tbl = data.to_sql("stock", con, index=False)

.. code:: ipython3

    %load_ext pyensae

.. code:: ipython3

    %SQL_tables -v con




.. parsed-literal::
    ['stock']



.. code:: ipython3

    %%SQL -v con
    
    SELECT * FROM stock LIMIT 5






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>Date</th>
          <th>High</th>
          <th>Low</th>
          <th>Open</th>
          <th>Close</th>
          <th>Volume</th>
          <th>Adj_Close</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>0</td>
          <td>2000-01-03</td>
          <td>59.3125</td>
          <td>56.00000</td>
          <td>58.68750</td>
          <td>58.28125</td>
          <td>53228400.0</td>
          <td>37.102634</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1</td>
          <td>2000-01-04</td>
          <td>58.5625</td>
          <td>56.12500</td>
          <td>56.78125</td>
          <td>56.31250</td>
          <td>54119000.0</td>
          <td>35.849308</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2</td>
          <td>2000-01-05</td>
          <td>58.1875</td>
          <td>54.68750</td>
          <td>55.56250</td>
          <td>56.90625</td>
          <td>64059600.0</td>
          <td>36.227283</td>
        </tr>
        <tr>
          <th>3</th>
          <td>3</td>
          <td>2000-01-06</td>
          <td>56.9375</td>
          <td>54.18750</td>
          <td>56.09375</td>
          <td>55.00000</td>
          <td>54976600.0</td>
          <td>35.013741</td>
        </tr>
        <tr>
          <th>4</th>
          <td>4</td>
          <td>2000-01-07</td>
          <td>56.1250</td>
          <td>53.65625</td>
          <td>54.31250</td>
          <td>55.71875</td>
          <td>62013600.0</td>
          <td>35.471302</td>
        </tr>
      </tbody>
    </table>
    </div>



version efficace
~~~~~~~~~~~~~~~~

L’instruction ``ON`` précise sur quelle ou quelles colonnes opérer la
fusion.

.. code:: ipython3

    %%SQL -v con
    
    SELECT A.t AS t, A.Close AS Close, A.tt AS tt,
           B.t AS t2, B.Close AS Close2, B.tt AS tt2
    FROM (
        SELECT t, Close, t+1 AS tt FROM stock
        ) AS A
    INNER JOIN (
        SELECT t, Close, t+1 AS tt FROM stock
        ) AS B
    ON A.tt == B.t






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>Close</th>
          <th>tt</th>
          <th>t2</th>
          <th>Close2</th>
          <th>tt2</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>0</td>
          <td>58.28125</td>
          <td>1</td>
          <td>1</td>
          <td>56.31250</td>
          <td>2</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1</td>
          <td>56.31250</td>
          <td>2</td>
          <td>2</td>
          <td>56.90625</td>
          <td>3</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2</td>
          <td>56.90625</td>
          <td>3</td>
          <td>3</td>
          <td>55.00000</td>
          <td>4</td>
        </tr>
        <tr>
          <th>3</th>
          <td>3</td>
          <td>55.00000</td>
          <td>4</td>
          <td>4</td>
          <td>55.71875</td>
          <td>5</td>
        </tr>
        <tr>
          <th>4</th>
          <td>4</td>
          <td>55.71875</td>
          <td>5</td>
          <td>5</td>
          <td>56.12500</td>
          <td>6</td>
        </tr>
        <tr>
          <th>5</th>
          <td>5</td>
          <td>56.12500</td>
          <td>6</td>
          <td>6</td>
          <td>54.68750</td>
          <td>7</td>
        </tr>
        <tr>
          <th>6</th>
          <td>6</td>
          <td>54.68750</td>
          <td>7</td>
          <td>7</td>
          <td>52.90625</td>
          <td>8</td>
        </tr>
        <tr>
          <th>7</th>
          <td>7</td>
          <td>52.90625</td>
          <td>8</td>
          <td>8</td>
          <td>53.90625</td>
          <td>9</td>
        </tr>
        <tr>
          <th>8</th>
          <td>8</td>
          <td>53.90625</td>
          <td>9</td>
          <td>9</td>
          <td>56.12500</td>
          <td>10</td>
        </tr>
        <tr>
          <th>9</th>
          <td>9</td>
          <td>56.12500</td>
          <td>10</td>
          <td>10</td>
          <td>57.65625</td>
          <td>11</td>
        </tr>
      </tbody>
    </table>
    </div>



version inefficace
~~~~~~~~~~~~~~~~~~

Pour la version inefficace, l’instruction ``JOIN`` effectue tous les
combinaisons de lignes possibles, soit :math:`N^2`. Le mot-clé ``WHERE``
garde les couples de lignes qui nous intéresse.

.. code:: ipython3

    %%SQL -v con
    
    SELECT A.t AS t, A.Close AS Close, A.tt AS tt,
           B.t AS t2, B.Close AS Close2, B.tt AS tt2
    FROM (
        SELECT t, Close, t+1 AS tt FROM stock
        ) AS A
    INNER JOIN (
        SELECT t, Close, t+1 AS tt FROM stock
        ) AS B
    WHERE A.tt >= B.t AND A.tt <= B.t -- on écrit l'égalité comme ceci pour contourner les optimisations
                                      -- réalisée par SQLlite






.. raw:: html

    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>t</th>
          <th>Close</th>
          <th>tt</th>
          <th>t2</th>
          <th>Close2</th>
          <th>tt2</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>0</td>
          <td>58.28125</td>
          <td>1</td>
          <td>1</td>
          <td>56.31250</td>
          <td>2</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1</td>
          <td>56.31250</td>
          <td>2</td>
          <td>2</td>
          <td>56.90625</td>
          <td>3</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2</td>
          <td>56.90625</td>
          <td>3</td>
          <td>3</td>
          <td>55.00000</td>
          <td>4</td>
        </tr>
        <tr>
          <th>3</th>
          <td>3</td>
          <td>55.00000</td>
          <td>4</td>
          <td>4</td>
          <td>55.71875</td>
          <td>5</td>
        </tr>
        <tr>
          <th>4</th>
          <td>4</td>
          <td>55.71875</td>
          <td>5</td>
          <td>5</td>
          <td>56.12500</td>
          <td>6</td>
        </tr>
        <tr>
          <th>5</th>
          <td>5</td>
          <td>56.12500</td>
          <td>6</td>
          <td>6</td>
          <td>54.68750</td>
          <td>7</td>
        </tr>
        <tr>
          <th>6</th>
          <td>6</td>
          <td>54.68750</td>
          <td>7</td>
          <td>7</td>
          <td>52.90625</td>
          <td>8</td>
        </tr>
        <tr>
          <th>7</th>
          <td>7</td>
          <td>52.90625</td>
          <td>8</td>
          <td>8</td>
          <td>53.90625</td>
          <td>9</td>
        </tr>
        <tr>
          <th>8</th>
          <td>8</td>
          <td>53.90625</td>
          <td>9</td>
          <td>9</td>
          <td>56.12500</td>
          <td>10</td>
        </tr>
        <tr>
          <th>9</th>
          <td>9</td>
          <td>56.12500</td>
          <td>10</td>
          <td>10</td>
          <td>57.65625</td>
          <td>11</td>
        </tr>
      </tbody>
    </table>
    </div>