.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "auto_examples/07_multiple_key_join.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_07_multiple_key_join.py>`
        to download the full example code. or to run this example in your browser via JupyterLite or Binder

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

.. _sphx_glr_auto_examples_07_multiple_key_join.py:


.. _example_multiple_key_join :

Spatial join for flight data: Joining across multiple columns
=============================================================

Joining tables may be difficult if one entry on one side does not have
an exact match on the other side.

This problem becomes even more complex when multiple columns
are significant for the join. For instance, this is the case
for **spatial joins** on two columns, typically
longitude and latitude.

|joiner| is a scikit-learn compatible transformer that enables
performing joins across multiple keys,
independantly of the data type (numerical, string or mixed).

The following example uses US domestic flights data
to illustrate how space and time information from a
pool of tables are combined for machine learning.

.. |fj| replace:: :func:`~skrub.fuzzy_join`

.. |joiner| replace:: :func:`~skrub.Joiner`

.. |Pipeline| replace::
     :class:`~sklearn.pipeline.Pipeline`

.. GENERATED FROM PYTHON SOURCE LINES 32-38

Flight-delays data
------------------
The goal is to predict flight delays.
We have a pool of tables that we will use to improve our prediction.

The following tables are at our disposal:

.. GENERATED FROM PYTHON SOURCE LINES 40-45

The main table: flights dataset
...............................
    - The `flights` datasets. It contains all US flights date, origin
      and destination airports and flight time.
      Here, we consider only flights from 2008.

.. GENERATED FROM PYTHON SOURCE LINES 45-55

.. code-block:: Python


    import pandas as pd

    from skrub.datasets import fetch_figshare

    flights = fetch_figshare("41771418").X
    # Sampling for faster computation.
    flights = flights.sample(20_000, random_state=1, ignore_index=True)
    flights.head()






.. 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>Year_Month_DayofMonth</th>
          <th>DayOfWeek</th>
          <th>CRSDepTime</th>
          <th>CRSArrTime</th>
          <th>UniqueCarrier</th>
          <th>FlightNum</th>
          <th>TailNum</th>
          <th>CRSElapsedTime</th>
          <th>ArrDelay</th>
          <th>Origin</th>
          <th>Dest</th>
          <th>Distance</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2008-01-13</td>
          <td>7</td>
          <td>1900-01-01 18:35:00</td>
          <td>1900-01-01 20:08:00</td>
          <td>CO</td>
          <td>150</td>
          <td>N17244</td>
          <td>213.0</td>
          <td>1.0</td>
          <td>IAH</td>
          <td>ONT</td>
          <td>1334.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2008-02-21</td>
          <td>4</td>
          <td>1900-01-01 14:30:00</td>
          <td>1900-01-01 16:06:00</td>
          <td>NW</td>
          <td>807</td>
          <td>N590NW</td>
          <td>216.0</td>
          <td>2.0</td>
          <td>MSP</td>
          <td>SEA</td>
          <td>1399.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2008-04-17</td>
          <td>4</td>
          <td>1900-01-01 09:40:00</td>
          <td>1900-01-01 13:15:00</td>
          <td>WN</td>
          <td>1684</td>
          <td>N642WN</td>
          <td>155.0</td>
          <td>-13.0</td>
          <td>SEA</td>
          <td>DEN</td>
          <td>1024.0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>2008-01-03</td>
          <td>4</td>
          <td>1900-01-01 08:40:00</td>
          <td>1900-01-01 12:03:00</td>
          <td>CO</td>
          <td>287</td>
          <td>N21723</td>
          <td>383.0</td>
          <td>46.0</td>
          <td>EWR</td>
          <td>SNA</td>
          <td>2433.0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>2008-01-31</td>
          <td>4</td>
          <td>1900-01-01 12:50:00</td>
          <td>1900-01-01 14:10:00</td>
          <td>MQ</td>
          <td>3157</td>
          <td>N848AE</td>
          <td>80.0</td>
          <td>-14.0</td>
          <td>SJC</td>
          <td>SNA</td>
          <td>342.0</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 56-57

Let us see the arrival delay of the flights in the dataset:

.. GENERATED FROM PYTHON SOURCE LINES 57-66

.. code-block:: Python

    import matplotlib.pyplot as plt
    import seaborn as sns

    sns.set_theme(style="ticks")

    ax = sns.histplot(data=flights, x="ArrDelay")
    ax.set_yscale("log")
    plt.show()




.. image-sg:: /auto_examples/images/sphx_glr_07_multiple_key_join_001.png
   :alt: 07 multiple key join
   :srcset: /auto_examples/images/sphx_glr_07_multiple_key_join_001.png
   :class: sphx-glr-single-img





.. GENERATED FROM PYTHON SOURCE LINES 67-69

Interesting, most delays are relatively short (<100 min), but there
are some very long ones.

.. GENERATED FROM PYTHON SOURCE LINES 71-75

Airport data: an auxiliary table from the same database
.......................................................
    - The ``airports`` dataset, with information such as their name
      and location (longitude, latitude).

.. GENERATED FROM PYTHON SOURCE LINES 75-79

.. code-block:: Python


    airports = fetch_figshare("41710257").X
    airports.head()






.. 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>iata</th>
          <th>airport</th>
          <th>city</th>
          <th>state</th>
          <th>country</th>
          <th>lat</th>
          <th>long</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>00M</td>
          <td>Thigpen</td>
          <td>Bay Springs</td>
          <td>MS</td>
          <td>USA</td>
          <td>31.953765</td>
          <td>-89.234505</td>
        </tr>
        <tr>
          <th>1</th>
          <td>00R</td>
          <td>Livingston Municipal</td>
          <td>Livingston</td>
          <td>TX</td>
          <td>USA</td>
          <td>30.685861</td>
          <td>-95.017928</td>
        </tr>
        <tr>
          <th>2</th>
          <td>00V</td>
          <td>Meadow Lake</td>
          <td>Colorado Springs</td>
          <td>CO</td>
          <td>USA</td>
          <td>38.945749</td>
          <td>-104.569893</td>
        </tr>
        <tr>
          <th>3</th>
          <td>01G</td>
          <td>Perry-Warsaw</td>
          <td>Perry</td>
          <td>NY</td>
          <td>USA</td>
          <td>42.741347</td>
          <td>-78.052081</td>
        </tr>
        <tr>
          <th>4</th>
          <td>01J</td>
          <td>Hilliard Airpark</td>
          <td>Hilliard</td>
          <td>FL</td>
          <td>USA</td>
          <td>30.688012</td>
          <td>-81.905944</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 80-85

Weather data: auxiliary tables from external sources
....................................................
    - The ``weather`` table. Weather details by measurement station.
      Both tables are from the Global Historical Climatology Network.
      Here, we consider only weather measurements from 2008.

.. GENERATED FROM PYTHON SOURCE LINES 85-91

.. code-block:: Python


    weather = fetch_figshare("41771457").X
    # Sampling for faster computation.
    weather = weather.sample(100_000, random_state=1, ignore_index=True)
    weather.head()






.. 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>ID</th>
          <th>YEAR/MONTH/DAY</th>
          <th>TMAX</th>
          <th>PRCP</th>
          <th>SNOW</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>ASN00041037</td>
          <td>2008-06-18</td>
          <td>NaN</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>1</th>
          <td>USC00164696</td>
          <td>2008-01-04</td>
          <td>39.0</td>
          <td>0.0</td>
          <td>0.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>US1ILSP0008</td>
          <td>2008-08-19</td>
          <td>NaN</td>
          <td>0.0</td>
          <td>0.0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>USC00164931</td>
          <td>2008-10-05</td>
          <td>NaN</td>
          <td>0.0</td>
          <td>0.0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>NOE00111309</td>
          <td>2008-08-18</td>
          <td>NaN</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 92-94

- The ``stations`` dataset. Provides location of all the weather
  measurement stations in the US.

.. GENERATED FROM PYTHON SOURCE LINES 94-98

.. code-block:: Python


    stations = fetch_figshare("41710524").X
    stations.head()






.. 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>ID</th>
          <th>LATITUDE</th>
          <th>LONGITUDE</th>
          <th>ELEVATION</th>
          <th>STATE</th>
          <th>NAME</th>
          <th>GSN FLAG</th>
          <th>HCN/CRN FLAG</th>
          <th>WMO ID</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>ACW00011604</td>
          <td>17.1167</td>
          <td>-61.7833</td>
          <td>10.1</td>
          <td>ST JOHNS COOLIDGE FLD</td>
          <td>None</td>
          <td>None</td>
          <td>NaN</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>1</th>
          <td>ACW00011647</td>
          <td>17.1333</td>
          <td>-61.7833</td>
          <td>19.2</td>
          <td>ST JOHNS</td>
          <td>None</td>
          <td>None</td>
          <td>NaN</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>2</th>
          <td>AE000041196</td>
          <td>25.3330</td>
          <td>55.5170</td>
          <td>34.0</td>
          <td>SHARJAH INTER. AIRP</td>
          <td>None</td>
          <td>GSN</td>
          <td>41196.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>3</th>
          <td>AEM00041194</td>
          <td>25.2550</td>
          <td>55.3640</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>4</th>
          <td>AEM00041217</td>
          <td>24.4330</td>
          <td>54.6510</td>
          <td>26.8</td>
          <td>ABU DHABI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41217.0</td>
          <td>NaN</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 99-102

Joining: feature augmentation across tables
-------------------------------------------
First we join the stations with weather on the ID (exact join):

.. GENERATED FROM PYTHON SOURCE LINES 102-106

.. code-block:: Python


    aux = pd.merge(stations, weather, on="ID")
    aux.head()






.. 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>ID</th>
          <th>LATITUDE</th>
          <th>LONGITUDE</th>
          <th>ELEVATION</th>
          <th>STATE</th>
          <th>NAME</th>
          <th>GSN FLAG</th>
          <th>HCN/CRN FLAG</th>
          <th>WMO ID</th>
          <th>YEAR/MONTH/DAY</th>
          <th>TMAX</th>
          <th>PRCP</th>
          <th>SNOW</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>AE000041196</td>
          <td>25.333</td>
          <td>55.517</td>
          <td>34.0</td>
          <td>SHARJAH INTER. AIRP</td>
          <td>None</td>
          <td>GSN</td>
          <td>41196.0</td>
          <td>NaN</td>
          <td>2008-08-15</td>
          <td>422.0</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>1</th>
          <td>AE000041196</td>
          <td>25.333</td>
          <td>55.517</td>
          <td>34.0</td>
          <td>SHARJAH INTER. AIRP</td>
          <td>None</td>
          <td>GSN</td>
          <td>41196.0</td>
          <td>NaN</td>
          <td>2008-05-08</td>
          <td>374.0</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>2</th>
          <td>AEM00041194</td>
          <td>25.255</td>
          <td>55.364</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
          <td>2008-04-04</td>
          <td>288.0</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>3</th>
          <td>AEM00041194</td>
          <td>25.255</td>
          <td>55.364</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
          <td>2008-05-21</td>
          <td>365.0</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
        <tr>
          <th>4</th>
          <td>AEM00041194</td>
          <td>25.255</td>
          <td>55.364</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
          <td>2008-01-21</td>
          <td>221.0</td>
          <td>0.0</td>
          <td>NaN</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 107-109

Then we join this table with the airports so that we get all auxilliary
tables into one.

.. GENERATED FROM PYTHON SOURCE LINES 109-118

.. code-block:: Python


    from skrub import Joiner

    joiner = Joiner(airports, aux_key=["lat", "long"], main_key=["LATITUDE", "LONGITUDE"])

    aux_augmented = joiner.fit_transform(aux)

    aux_augmented.head()






.. 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>ID</th>
          <th>LATITUDE</th>
          <th>LONGITUDE</th>
          <th>ELEVATION</th>
          <th>STATE</th>
          <th>NAME</th>
          <th>GSN FLAG</th>
          <th>HCN/CRN FLAG</th>
          <th>WMO ID</th>
          <th>YEAR/MONTH/DAY</th>
          <th>TMAX</th>
          <th>PRCP</th>
          <th>SNOW</th>
          <th>iata</th>
          <th>airport</th>
          <th>city</th>
          <th>state</th>
          <th>country</th>
          <th>lat</th>
          <th>long</th>
          <th>skrub_Joiner_distance</th>
          <th>skrub_Joiner_rescaled_distance</th>
          <th>skrub_Joiner_match_accepted</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>AE000041196</td>
          <td>25.333</td>
          <td>55.517</td>
          <td>34.0</td>
          <td>SHARJAH INTER. AIRP</td>
          <td>None</td>
          <td>GSN</td>
          <td>41196.0</td>
          <td>NaN</td>
          <td>2008-08-15</td>
          <td>422.0</td>
          <td>0.0</td>
          <td>NaN</td>
          <td>ROP</td>
          <td>Prachinburi</td>
          <td>None</td>
          <td>None</td>
          <td>Thailand</td>
          <td>14.078333</td>
          <td>101.378334</td>
          <td>2.418438</td>
          <td>3.314383</td>
          <td>True</td>
        </tr>
        <tr>
          <th>1</th>
          <td>AE000041196</td>
          <td>25.333</td>
          <td>55.517</td>
          <td>34.0</td>
          <td>SHARJAH INTER. AIRP</td>
          <td>None</td>
          <td>GSN</td>
          <td>41196.0</td>
          <td>NaN</td>
          <td>2008-05-08</td>
          <td>374.0</td>
          <td>0.0</td>
          <td>NaN</td>
          <td>ROP</td>
          <td>Prachinburi</td>
          <td>None</td>
          <td>None</td>
          <td>Thailand</td>
          <td>14.078333</td>
          <td>101.378334</td>
          <td>2.418438</td>
          <td>3.314383</td>
          <td>True</td>
        </tr>
        <tr>
          <th>2</th>
          <td>AEM00041194</td>
          <td>25.255</td>
          <td>55.364</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
          <td>2008-04-04</td>
          <td>288.0</td>
          <td>0.0</td>
          <td>NaN</td>
          <td>ROP</td>
          <td>Prachinburi</td>
          <td>None</td>
          <td>None</td>
          <td>Thailand</td>
          <td>14.078333</td>
          <td>101.378334</td>
          <td>2.418781</td>
          <td>3.314853</td>
          <td>True</td>
        </tr>
        <tr>
          <th>3</th>
          <td>AEM00041194</td>
          <td>25.255</td>
          <td>55.364</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
          <td>2008-05-21</td>
          <td>365.0</td>
          <td>0.0</td>
          <td>NaN</td>
          <td>ROP</td>
          <td>Prachinburi</td>
          <td>None</td>
          <td>None</td>
          <td>Thailand</td>
          <td>14.078333</td>
          <td>101.378334</td>
          <td>2.418781</td>
          <td>3.314853</td>
          <td>True</td>
        </tr>
        <tr>
          <th>4</th>
          <td>AEM00041194</td>
          <td>25.255</td>
          <td>55.364</td>
          <td>10.4</td>
          <td>DUBAI INTL</td>
          <td>None</td>
          <td>None</td>
          <td>41194.0</td>
          <td>NaN</td>
          <td>2008-01-21</td>
          <td>221.0</td>
          <td>0.0</td>
          <td>NaN</td>
          <td>ROP</td>
          <td>Prachinburi</td>
          <td>None</td>
          <td>None</td>
          <td>Thailand</td>
          <td>14.078333</td>
          <td>101.378334</td>
          <td>2.418781</td>
          <td>3.314853</td>
          <td>True</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 119-121

Joining airports with flights data:
Let's instanciate another multiple key joiner on the date and the airport:

.. GENERATED FROM PYTHON SOURCE LINES 121-130

.. code-block:: Python


    joiner = Joiner(
        aux_augmented,
        aux_key=["YEAR/MONTH/DAY", "iata"],
        main_key=["Year_Month_DayofMonth", "Origin"],
    )

    flights.drop(columns=["TailNum", "FlightNum"])






.. 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>Year_Month_DayofMonth</th>
          <th>DayOfWeek</th>
          <th>CRSDepTime</th>
          <th>CRSArrTime</th>
          <th>UniqueCarrier</th>
          <th>CRSElapsedTime</th>
          <th>ArrDelay</th>
          <th>Origin</th>
          <th>Dest</th>
          <th>Distance</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>2008-01-13</td>
          <td>7</td>
          <td>1900-01-01 18:35:00</td>
          <td>1900-01-01 20:08:00</td>
          <td>CO</td>
          <td>213.0</td>
          <td>1.0</td>
          <td>IAH</td>
          <td>ONT</td>
          <td>1334.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2008-02-21</td>
          <td>4</td>
          <td>1900-01-01 14:30:00</td>
          <td>1900-01-01 16:06:00</td>
          <td>NW</td>
          <td>216.0</td>
          <td>2.0</td>
          <td>MSP</td>
          <td>SEA</td>
          <td>1399.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>2008-04-17</td>
          <td>4</td>
          <td>1900-01-01 09:40:00</td>
          <td>1900-01-01 13:15:00</td>
          <td>WN</td>
          <td>155.0</td>
          <td>-13.0</td>
          <td>SEA</td>
          <td>DEN</td>
          <td>1024.0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>2008-01-03</td>
          <td>4</td>
          <td>1900-01-01 08:40:00</td>
          <td>1900-01-01 12:03:00</td>
          <td>CO</td>
          <td>383.0</td>
          <td>46.0</td>
          <td>EWR</td>
          <td>SNA</td>
          <td>2433.0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>2008-01-31</td>
          <td>4</td>
          <td>1900-01-01 12:50:00</td>
          <td>1900-01-01 14:10:00</td>
          <td>MQ</td>
          <td>80.0</td>
          <td>-14.0</td>
          <td>SJC</td>
          <td>SNA</td>
          <td>342.0</td>
        </tr>
        <tr>
          <th>...</th>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
          <td>...</td>
        </tr>
        <tr>
          <th>19995</th>
          <td>2008-01-14</td>
          <td>1</td>
          <td>1900-01-01 09:50:00</td>
          <td>1900-01-01 11:45:00</td>
          <td>WN</td>
          <td>115.0</td>
          <td>-7.0</td>
          <td>SMF</td>
          <td>SEA</td>
          <td>605.0</td>
        </tr>
        <tr>
          <th>19996</th>
          <td>2008-03-19</td>
          <td>3</td>
          <td>1900-01-01 13:55:00</td>
          <td>1900-01-01 18:40:00</td>
          <td>AA</td>
          <td>165.0</td>
          <td>-27.0</td>
          <td>LAS</td>
          <td>DFW</td>
          <td>1055.0</td>
        </tr>
        <tr>
          <th>19997</th>
          <td>2008-03-15</td>
          <td>6</td>
          <td>1900-01-01 12:00:00</td>
          <td>1900-01-01 18:50:00</td>
          <td>WN</td>
          <td>230.0</td>
          <td>-29.0</td>
          <td>PHX</td>
          <td>PIT</td>
          <td>1813.0</td>
        </tr>
        <tr>
          <th>19998</th>
          <td>2008-02-27</td>
          <td>3</td>
          <td>1900-01-01 07:20:00</td>
          <td>1900-01-01 09:00:00</td>
          <td>MQ</td>
          <td>100.0</td>
          <td>NaN</td>
          <td>XNA</td>
          <td>ORD</td>
          <td>522.0</td>
        </tr>
        <tr>
          <th>19999</th>
          <td>2008-03-04</td>
          <td>2</td>
          <td>1900-01-01 06:20:00</td>
          <td>1900-01-01 08:01:00</td>
          <td>9E</td>
          <td>101.0</td>
          <td>-8.0</td>
          <td>IAH</td>
          <td>MEM</td>
          <td>469.0</td>
        </tr>
      </tbody>
    </table>
    <p>20000 rows × 10 columns</p>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 131-136

Training data is then passed through a |Pipeline|:

- We will combine all the information from our pool of tables into "flights",
our main table.
- We will use this main table to model the prediction of flight delay.

.. GENERATED FROM PYTHON SOURCE LINES 136-147

.. code-block:: Python


    from sklearn.ensemble import HistGradientBoostingClassifier
    from sklearn.pipeline import make_pipeline

    from skrub import TableVectorizer

    tv = TableVectorizer()
    hgb = HistGradientBoostingClassifier()

    pipeline_hgb = make_pipeline(joiner, tv, hgb)








.. GENERATED FROM PYTHON SOURCE LINES 148-149

We isolate our target variable and remove useless ID variables:

.. GENERATED FROM PYTHON SOURCE LINES 149-153

.. code-block:: Python


    y = flights["ArrDelay"]
    X = flights.drop(columns=["ArrDelay"])








.. GENERATED FROM PYTHON SOURCE LINES 154-160

We want to frame this as a classification problem:
suppose that your company is obliged to reimburse the ticket
price if the flight is delayed.

We have a binary classification problem:
the flight was delayed (1) or not (0).

.. GENERATED FROM PYTHON SOURCE LINES 160-164

.. code-block:: Python


    y = (y > 0).astype(int)
    y.value_counts()





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

 .. code-block:: none


    ArrDelay
    0    10686
    1     9314
    Name: count, dtype: int64



.. GENERATED FROM PYTHON SOURCE LINES 165-166

The results:

.. GENERATED FROM PYTHON SOURCE LINES 166-172

.. code-block:: Python


    from sklearn.model_selection import cross_val_score

    scores = cross_val_score(pipeline_hgb, X, y)
    scores.mean()





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

 .. code-block:: none

    /home/circleci/project/.pixi/envs/doc/lib/python3.12/site-packages/sklearn/preprocessing/_encoders.py:242: UserWarning: Found unknown categories in columns [0] during transform. These unknown categories will be encoded as all zeros
      warnings.warn(
    /home/circleci/project/.pixi/envs/doc/lib/python3.12/site-packages/sklearn/preprocessing/_encoders.py:242: UserWarning: Found unknown categories in columns [0] during transform. These unknown categories will be encoded as all zeros
      warnings.warn(
    /home/circleci/project/.pixi/envs/doc/lib/python3.12/site-packages/sklearn/preprocessing/_encoders.py:242: UserWarning: Found unknown categories in columns [0] during transform. These unknown categories will be encoded as all zeros
      warnings.warn(

    np.float64(0.58435)



.. GENERATED FROM PYTHON SOURCE LINES 173-181

Conclusion
----------

In this example, we have combined multiple tables with complex joins
on imprecise and multiple-key correspondences.
This is made easy by skrub's |Joiner| transformer.

Our final cross-validated accuracy score is 0.58.


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

   **Total running time of the script:** (6 minutes 56.410 seconds)


.. _sphx_glr_download_auto_examples_07_multiple_key_join.py:

.. only:: html

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

    .. container:: binder-badge

      .. image:: images/binder_badge_logo.svg
        :target: https://mybinder.org/v2/gh/skrub-data/skrub/main?urlpath=lab/tree/notebooks/auto_examples/07_multiple_key_join.ipynb
        :alt: Launch binder
        :width: 150 px

    .. container:: lite-badge

      .. image:: images/jupyterlite_badge_logo.svg
        :target: ../lite/lab/index.html?path=auto_examples/07_multiple_key_join.ipynb
        :alt: Launch JupyterLite
        :width: 150 px

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

      :download:`Download Jupyter notebook: 07_multiple_key_join.ipynb <07_multiple_key_join.ipynb>`

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

      :download:`Download Python source code: 07_multiple_key_join.py <07_multiple_key_join.py>`

    .. container:: sphx-glr-download sphx-glr-download-zip

      :download:`Download zipped: 07_multiple_key_join.zip <07_multiple_key_join.zip>`


.. only:: html

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

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