.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "auto_examples/01_filtering.py"
.. LINE NUMBERS ARE GIVEN BELOW.

.. only:: html

    .. note::
        :class: sphx-glr-download-link-note

        :ref:`Go to the end <sphx_glr_download_auto_examples_01_filtering.py>`
        to download the full example code.

.. rst-class:: sphx-glr-example-title

.. _sphx_glr_auto_examples_01_filtering.py:


Filtering
=========

A simple example to demonstrate how to filter a parquet file using a pandas-like expression.

This example uses the `parq_tools` library to filter a Parquet file based on a specified condition.
Pyarrow filtering is not structured like the filtering in pandas, but parq-tools uses custom parser
allowing pandas-like expressions to be used.

.. GENERATED FROM PYTHON SOURCE LINES 12-20

.. code-block:: Python

    import tempfile

    import pandas as pd
    import pyarrow as pa
    import pyarrow.parquet as pq
    from pathlib import Path









.. GENERATED FROM PYTHON SOURCE LINES 21-25

Create a Parquet file
---------------------

Create a temporary parquet file for demonstration

.. GENERATED FROM PYTHON SOURCE LINES 26-49

.. code-block:: Python



    def create_parquet_file(file_path: Path):
        # Define the dataset
        data = {
            "x": range(1, 11),  # Index column
            "y": range(11, 21),  # Index column
            "z": range(21, 31),  # Index column
            "a": [f"val{i}" for i in range(1, 11)],  # Supplementary column
            "b": [i * 2 for i in range(1, 11)],  # Supplementary column
            "c": [i % 3 for i in range(1, 11)],  # Supplementary column
        }

        # Create a DataFrame
        df = pa.Table.from_pydict(data)

        # Write the DataFrame to a Parquet file
        pq.write_table(df, file_path)


    parquet_file_path = Path(tempfile.gettempdir()) / "example_data.parquet"
    create_parquet_file(parquet_file_path)








.. GENERATED FROM PYTHON SOURCE LINES 50-51

View the file as a DataFrame

.. GENERATED FROM PYTHON SOURCE LINES 51-54

.. code-block:: Python

    df = pd.read_parquet(parquet_file_path)
    df






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <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>x</th>
          <th>y</th>
          <th>z</th>
          <th>a</th>
          <th>b</th>
          <th>c</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1</td>
          <td>11</td>
          <td>21</td>
          <td>val1</td>
          <td>2</td>
          <td>1</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2</td>
          <td>12</td>
          <td>22</td>
          <td>val2</td>
          <td>4</td>
          <td>2</td>
        </tr>
        <tr>
          <th>2</th>
          <td>3</td>
          <td>13</td>
          <td>23</td>
          <td>val3</td>
          <td>6</td>
          <td>0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>4</td>
          <td>14</td>
          <td>24</td>
          <td>val4</td>
          <td>8</td>
          <td>1</td>
        </tr>
        <tr>
          <th>4</th>
          <td>5</td>
          <td>15</td>
          <td>25</td>
          <td>val5</td>
          <td>10</td>
          <td>2</td>
        </tr>
        <tr>
          <th>5</th>
          <td>6</td>
          <td>16</td>
          <td>26</td>
          <td>val6</td>
          <td>12</td>
          <td>0</td>
        </tr>
        <tr>
          <th>6</th>
          <td>7</td>
          <td>17</td>
          <td>27</td>
          <td>val7</td>
          <td>14</td>
          <td>1</td>
        </tr>
        <tr>
          <th>7</th>
          <td>8</td>
          <td>18</td>
          <td>28</td>
          <td>val8</td>
          <td>16</td>
          <td>2</td>
        </tr>
        <tr>
          <th>8</th>
          <td>9</td>
          <td>19</td>
          <td>29</td>
          <td>val9</td>
          <td>18</td>
          <td>0</td>
        </tr>
        <tr>
          <th>9</th>
          <td>10</td>
          <td>20</td>
          <td>30</td>
          <td>val10</td>
          <td>20</td>
          <td>1</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 55-60

Filter with Pandas
------------------

We can use pandas directly to load the Parquet file and filter it using a pandas-like expression.
First we filter early with read_parquet for efficiency.  Additionally, we have manually set the index in this example.

.. GENERATED FROM PYTHON SOURCE LINES 60-67

.. code-block:: Python


    index_cols = ["x", "y", "z"]
    df_from_pandas_1: pd.DataFrame = pd.read_parquet(parquet_file_path,
                                                     columns=["x", "y", "z", "a", "c"],
                                                     filters=[("x", ">", 3), ("y", "<=", 15)]).set_index(index_cols)
    df_from_pandas_1






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <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></th>
          <th></th>
          <th>a</th>
          <th>c</th>
        </tr>
        <tr>
          <th>x</th>
          <th>y</th>
          <th>z</th>
          <th></th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>4</th>
          <th>14</th>
          <th>24</th>
          <td>val4</td>
          <td>1</td>
        </tr>
        <tr>
          <th>5</th>
          <th>15</th>
          <th>25</th>
          <td>val5</td>
          <td>2</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 68-69

An alternative but less efficient way is to load all records and then apply a filter

.. GENERATED FROM PYTHON SOURCE LINES 69-74

.. code-block:: Python

    df_from_pandas_2 = pd.read_parquet(parquet_file_path,
                                       columns=["x", "y", "z", "a", "c"]).query("x > 3 and y <= 15").set_index(index_cols)

    df_from_pandas_2






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <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></th>
          <th></th>
          <th>a</th>
          <th>c</th>
        </tr>
        <tr>
          <th>x</th>
          <th>y</th>
          <th>z</th>
          <th></th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>4</th>
          <th>14</th>
          <th>24</th>
          <td>val4</td>
          <td>1</td>
        </tr>
        <tr>
          <th>5</th>
          <th>15</th>
          <th>25</th>
          <td>val5</td>
          <td>2</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 75-76

Compare the two DataFrames to ensure they are equal

.. GENERATED FROM PYTHON SOURCE LINES 76-78

.. code-block:: Python

    pd.testing.assert_frame_equal(df_from_pandas_1, df_from_pandas_2)








.. GENERATED FROM PYTHON SOURCE LINES 79-85

Filter with Parq Tools
----------------------

The `parq_tools` library provides a way to filter Parquet files that do not fit into memory,
using a pandas-like expression.  The output is a new Parquet file containing only the filtered records
and selected columns.  This can be useful in pipelines with large datasets.

.. GENERATED FROM PYTHON SOURCE LINES 85-93

.. code-block:: Python


    from parq_tools import filter_parquet_file

    filter_parquet_file(parquet_file_path,
                        output_path=parquet_file_path.with_suffix('.filtered.parquet'),
                        columns=["x", "y", "z", "a", "c"], filter_expression='x > 3 and y <= 15',
                        show_progress=True)





.. rst-class:: sphx-glr-script-out

 .. code-block:: none


    Filtering:   0%|          | 0/10 [00:00<?, ?rows/s]
    Filtering:  20%|██        | 2/10 [00:00<00:00, 1908.67rows/s]




.. GENERATED FROM PYTHON SOURCE LINES 94-95

Read the filtered Parquet file

.. GENERATED FROM PYTHON SOURCE LINES 95-98

.. code-block:: Python

    df_filtered = pd.read_parquet(parquet_file_path.with_suffix('.filtered.parquet')).set_index(index_cols)
    df_filtered






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <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></th>
          <th></th>
          <th>a</th>
          <th>c</th>
        </tr>
        <tr>
          <th>x</th>
          <th>y</th>
          <th>z</th>
          <th></th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>4</th>
          <th>14</th>
          <th>24</th>
          <td>val4</td>
          <td>1</td>
        </tr>
        <tr>
          <th>5</th>
          <th>15</th>
          <th>25</th>
          <td>val5</td>
          <td>2</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 99-100

Compare the filtered DataFrame with the one from pandas

.. GENERATED FROM PYTHON SOURCE LINES 100-101

.. code-block:: Python

    pd.testing.assert_frame_equal(df_filtered, df_from_pandas_1)








.. rst-class:: sphx-glr-timing

   **Total running time of the script:** (0 minutes 0.060 seconds)


.. _sphx_glr_download_auto_examples_01_filtering.py:

.. only:: html

  .. container:: sphx-glr-footer sphx-glr-footer-example

    .. container:: sphx-glr-download sphx-glr-download-jupyter

      :download:`Download Jupyter notebook: 01_filtering.ipynb <01_filtering.ipynb>`

    .. container:: sphx-glr-download sphx-glr-download-python

      :download:`Download Python source code: 01_filtering.py <01_filtering.py>`


.. only:: html

 .. rst-class:: sphx-glr-signature

    `Gallery generated by Sphinx-Gallery <https://sphinx-gallery.github.io>`_