.. _chshpandasrst:

==================================
Uncommon operation with dataframes
==================================


.. only:: html

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


Cheat sheet on uncommand operation with pandas such as reading a big
file.

.. code:: ipython3

    from jyquickhelper import add_notebook_menu
    add_notebook_menu()






.. contents::
    :local:





Pointer on notebooks
--------------------


-  `Rappel de ce que vous savez déjà mais avez peut-être
   oublié <http://www.xavierdupre.fr/app/ensae_teaching_cs/helpsphinx/notebooks/td2_eco_rappels_1a.html>`__
-  `Python pour un Data Scientist /
   Economiste <http://www.xavierdupre.fr/app/ensae_teaching_cs/helpsphinx/td_2a.html>`__
-  `Exercices
   Pratiques <http://www.xavierdupre.fr/app/actuariat_python/helpsphinx/i_seances_base.html>`__

List of strings into binaries features
--------------------------------------

.. code:: ipython3

    import pandas
    df = pandas.DataFrame([{"target":0, "features":["a", "b", "c"]},
                           {"target":1, "features":["a", "b"]},
                           {"target":2, "features":["c", "b"]}])
    df






.. 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>features</th>
          <th>target</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>[a, b, c]</td>
          <td>0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>[a, b]</td>
          <td>1</td>
        </tr>
        <tr>
          <th>2</th>
          <td>[c, b]</td>
          <td>2</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df.features.str.join("*").str.get_dummies("*")






.. 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>a</th>
          <th>b</th>
          <th>c</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1</td>
          <td>1</td>
          <td>1</td>
        </tr>
        <tr>
          <th>1</th>
          <td>1</td>
          <td>1</td>
          <td>0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>0</td>
          <td>1</td>
          <td>1</td>
        </tr>
      </tbody>
    </table>
    </div>



Big files
---------

Let’s save some data first.

.. code:: ipython3

    from sklearn.datasets import load_breast_cancer
    data = load_breast_cancer()
    import pandas
    df = pandas.DataFrame(data.data, columns=data.feature_names)
    df.to_csv("cancer.txt", sep="\t", encoding="utf-8", index=False)

first lines : nrows
~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    df = pandas.read_csv("cancer.txt", nrows=3)
    df






.. 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>mean radius	mean texture	mean perimeter	mean area	mean smoothness	mean compactness	mean concavity	mean concave points	mean symmetry	mean fractal dimension	radius error	texture error	perimeter error	area error	smoothness error	compactness error	concavity error	concave points error	symmetry error	fractal dimension error	worst radius	worst texture	worst perimeter	worst area	worst smoothness	worst compactness	worst concavity	worst concave points	worst symmetry	worst fractal dimension</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>17.99\t10.38\t122.8\t1001.0\t0.1184\t0.2776\t0...</td>
        </tr>
        <tr>
          <th>1</th>
          <td>20.57\t17.77\t132.9\t1326.0\t0.08474\t0.07864\...</td>
        </tr>
        <tr>
          <th>2</th>
          <td>19.69\t21.25\t130.0\t1203.0\t0.1096\t0.1599\t0...</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df = pandas.read_csv("cancer.txt", nrows=3, sep="\t")
    df






.. 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>mean radius</th>
          <th>mean texture</th>
          <th>mean perimeter</th>
          <th>mean area</th>
          <th>mean smoothness</th>
          <th>mean compactness</th>
          <th>mean concavity</th>
          <th>mean concave points</th>
          <th>mean symmetry</th>
          <th>mean fractal dimension</th>
          <th>...</th>
          <th>worst radius</th>
          <th>worst texture</th>
          <th>worst perimeter</th>
          <th>worst area</th>
          <th>worst smoothness</th>
          <th>worst compactness</th>
          <th>worst concavity</th>
          <th>worst concave points</th>
          <th>worst symmetry</th>
          <th>worst fractal dimension</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>17.99</td>
          <td>10.38</td>
          <td>122.8</td>
          <td>1001.0</td>
          <td>0.11840</td>
          <td>0.27760</td>
          <td>0.3001</td>
          <td>0.14710</td>
          <td>0.2419</td>
          <td>0.07871</td>
          <td>...</td>
          <td>25.38</td>
          <td>17.33</td>
          <td>184.6</td>
          <td>2019.0</td>
          <td>0.1622</td>
          <td>0.6656</td>
          <td>0.7119</td>
          <td>0.2654</td>
          <td>0.4601</td>
          <td>0.11890</td>
        </tr>
        <tr>
          <th>1</th>
          <td>20.57</td>
          <td>17.77</td>
          <td>132.9</td>
          <td>1326.0</td>
          <td>0.08474</td>
          <td>0.07864</td>
          <td>0.0869</td>
          <td>0.07017</td>
          <td>0.1812</td>
          <td>0.05667</td>
          <td>...</td>
          <td>24.99</td>
          <td>23.41</td>
          <td>158.8</td>
          <td>1956.0</td>
          <td>0.1238</td>
          <td>0.1866</td>
          <td>0.2416</td>
          <td>0.1860</td>
          <td>0.2750</td>
          <td>0.08902</td>
        </tr>
        <tr>
          <th>2</th>
          <td>19.69</td>
          <td>21.25</td>
          <td>130.0</td>
          <td>1203.0</td>
          <td>0.10960</td>
          <td>0.15990</td>
          <td>0.1974</td>
          <td>0.12790</td>
          <td>0.2069</td>
          <td>0.05999</td>
          <td>...</td>
          <td>23.57</td>
          <td>25.53</td>
          <td>152.5</td>
          <td>1709.0</td>
          <td>0.1444</td>
          <td>0.4245</td>
          <td>0.4504</td>
          <td>0.2430</td>
          <td>0.3613</td>
          <td>0.08758</td>
        </tr>
      </tbody>
    </table>
    <p>3 rows × 30 columns</p>
    </div>



middle lines : nrows + skiprows
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    df = pandas.read_csv("cancer.txt", nrows=3, skiprows=100, sep="\t", header=None)
    df






.. 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>0</th>
          <th>1</th>
          <th>2</th>
          <th>3</th>
          <th>4</th>
          <th>5</th>
          <th>6</th>
          <th>7</th>
          <th>8</th>
          <th>9</th>
          <th>...</th>
          <th>20</th>
          <th>21</th>
          <th>22</th>
          <th>23</th>
          <th>24</th>
          <th>25</th>
          <th>26</th>
          <th>27</th>
          <th>28</th>
          <th>29</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>14.420</td>
          <td>19.77</td>
          <td>94.48</td>
          <td>642.5</td>
          <td>0.09752</td>
          <td>0.11410</td>
          <td>0.09388</td>
          <td>0.05839</td>
          <td>0.1879</td>
          <td>0.06390</td>
          <td>...</td>
          <td>16.33</td>
          <td>30.86</td>
          <td>109.50</td>
          <td>826.4</td>
          <td>0.1431</td>
          <td>0.3026</td>
          <td>0.3194</td>
          <td>0.1565</td>
          <td>0.2718</td>
          <td>0.09353</td>
        </tr>
        <tr>
          <th>1</th>
          <td>13.610</td>
          <td>24.98</td>
          <td>88.05</td>
          <td>582.7</td>
          <td>0.09488</td>
          <td>0.08511</td>
          <td>0.08625</td>
          <td>0.04489</td>
          <td>0.1609</td>
          <td>0.05871</td>
          <td>...</td>
          <td>16.99</td>
          <td>35.27</td>
          <td>108.60</td>
          <td>906.5</td>
          <td>0.1265</td>
          <td>0.1943</td>
          <td>0.3169</td>
          <td>0.1184</td>
          <td>0.2651</td>
          <td>0.07397</td>
        </tr>
        <tr>
          <th>2</th>
          <td>6.981</td>
          <td>13.43</td>
          <td>43.79</td>
          <td>143.5</td>
          <td>0.11700</td>
          <td>0.07568</td>
          <td>0.00000</td>
          <td>0.00000</td>
          <td>0.1930</td>
          <td>0.07818</td>
          <td>...</td>
          <td>7.93</td>
          <td>19.54</td>
          <td>50.41</td>
          <td>185.2</td>
          <td>0.1584</td>
          <td>0.1202</td>
          <td>0.0000</td>
          <td>0.0000</td>
          <td>0.2932</td>
          <td>0.09382</td>
        </tr>
      </tbody>
    </table>
    <p>3 rows × 30 columns</p>
    </div>



big files : iterator
~~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    for piece, df in enumerate(pandas.read_csv("cancer.txt", iterator=True, sep="\t", chunksize=3)):
        print(piece, df.shape)
        if piece > 2:
            break


.. parsed-literal::
    0 (3, 30)
    1 (3, 30)
    2 (3, 30)
    3 (3, 30)


sample on big files : iterator + concat
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: ipython3

    samples = []
    for df in pandas.read_csv("cancer.txt", iterator=True, sep="\t", chunksize=30):
        sample = df.sample(3)
        samples.append(sample)
    dfsample = pandas.concat(samples)
    dfsample.shape




.. parsed-literal::
    (57, 30)