.. DO NOT EDIT. .. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY. .. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE: .. "auto_examples/03_joining/0060_multiple_key_join.py" .. LINE NUMBERS ARE GIVEN BELOW. .. only:: html .. note:: :class: sphx-glr-download-link-note :ref:`Go to the end ` 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_03_joining_0060_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, independently 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` dataset. 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-58 .. code-block:: Python import pandas as pd from skrub.datasets import fetch_flight_delays dataset = fetch_flight_delays() seed = 1 flights = pd.read_csv(dataset.flights_path) # Sampling for faster computation. flights = flights.sample(5_000, random_state=seed, ignore_index=True) flights.head() .. raw:: html

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 59-60 Let us see the arrival delay of the flights in the dataset: .. GENERATED FROM PYTHON SOURCE LINES 60-69 .. 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/03_joining/images/sphx_glr_0060_multiple_key_join_001.png :alt: 0060 multiple key join :srcset: /auto_examples/03_joining/images/sphx_glr_0060_multiple_key_join_001.png :class: sphx-glr-single-img .. GENERATED FROM PYTHON SOURCE LINES 70-72 Interesting, most delays are relatively short (<100 min), but there are some very long ones. .. GENERATED FROM PYTHON SOURCE LINES 74-78 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 78-82 .. code-block:: Python airports = pd.read_csv(dataset.airports_path) airports.head() .. raw:: html

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 83-88 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 88-94 .. code-block:: Python weather = pd.read_csv(dataset.weather_path) # Sampling for faster computation. weather = weather.sample(10_000, random_state=seed, ignore_index=True) weather.head() .. raw:: html

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 95-97 - The ``stations`` dataset. Provides location of all the weather measurement stations in the US. .. GENERATED FROM PYTHON SOURCE LINES 97-101 .. code-block:: Python stations = pd.read_csv(dataset.stations_path) stations.head() .. raw:: html

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 102-105 Joining: feature augmentation across tables ------------------------------------------- First we join the stations with weather on the ID (exact join): .. GENERATED FROM PYTHON SOURCE LINES 105-109 .. code-block:: Python aux = pd.merge(stations, weather, on="ID") aux.head() .. raw:: html

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 110-112 Then we join this table with the airports so that we get all auxiliary tables into one. .. GENERATED FROM PYTHON SOURCE LINES 112-121 .. 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

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 122-124 Joining airports with flights data: Let's instantiate another multiple key joiner on the date and the airport: .. GENERATED FROM PYTHON SOURCE LINES 124-133 .. 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

Please enable javascript

The skrub table reports need javascript to display correctly. If you are displaying a report in a Jupyter notebook and you see this message, you may need to re-execute the cell or to trust the notebook (button on the top right or "File > Trust notebook").



.. GENERATED FROM PYTHON SOURCE LINES 134-140 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 140-151 .. 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 152-153 We isolate our target variable and remove useless ID variables: .. GENERATED FROM PYTHON SOURCE LINES 153-157 .. code-block:: Python y = flights["ArrDelay"] X = flights.drop(columns=["ArrDelay"]) .. GENERATED FROM PYTHON SOURCE LINES 158-164 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 164-168 .. code-block:: Python y = (y > 0).astype(int) y.value_counts() .. rst-class:: sphx-glr-script-out .. code-block:: none ArrDelay 0 2727 1 2273 Name: count, dtype: int64 .. GENERATED FROM PYTHON SOURCE LINES 169-170 The results: .. GENERATED FROM PYTHON SOURCE LINES 170-176 .. code-block:: Python from sklearn.model_selection import train_test_split X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=seed) pipeline_hgb.fit(X_train, y_train).score(X_test, y_test) .. rst-class:: sphx-glr-script-out .. code-block:: none 0.5568 .. GENERATED FROM PYTHON SOURCE LINES 177-185 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.55. .. rst-class:: sphx-glr-timing **Total running time of the script:** (0 minutes 44.886 seconds) **Estimated memory usage:** 2986 MB .. _sphx_glr_download_auto_examples_03_joining_0060_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/0.10.0?urlpath=lab/tree/notebooks/auto_examples/03_joining/0060_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/03_joining/0060_multiple_key_join.ipynb :alt: Launch JupyterLite :width: 150 px .. container:: sphx-glr-download sphx-glr-download-jupyter :download:`Download Jupyter notebook: 0060_multiple_key_join.ipynb <0060_multiple_key_join.ipynb>` .. container:: sphx-glr-download sphx-glr-download-python :download:`Download Python source code: 0060_multiple_key_join.py <0060_multiple_key_join.py>` .. container:: sphx-glr-download sphx-glr-download-zip :download:`Download zipped: 0060_multiple_key_join.zip <0060_multiple_key_join.zip>` .. only:: html .. rst-class:: sphx-glr-signature `Gallery generated by Sphinx-Gallery `_