Note
Go to the end to download the full example code or to run this example in your browser via JupyterLite or Binder.
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.
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:
The main table: flights dataset#
The
flightsdataset. It contains all US flights date, origin and destination airports and flight time. Here, we consider only flights from 2008.
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()
| Year_Month_DayofMonth | DayOfWeek | CRSDepTime | CRSArrTime | UniqueCarrier | FlightNum | TailNum | CRSElapsedTime | ArrDelay | Origin | Dest | Distance | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2008-01-13 | 7 | 1900-01-01 18:35:00 | 1900-01-01 20:08:00 | CO | 150 | N17244 | 213. | 1.00 | IAH | ONT | 1.33e+03 |
| 1 | 2008-02-21 | 4 | 1900-01-01 14:30:00 | 1900-01-01 16:06:00 | NW | 807 | N590NW | 216. | 2.00 | MSP | SEA | 1.40e+03 |
| 2 | 2008-03-26 | 3 | 1900-01-01 07:00:00 | 1900-01-01 09:38:00 | US | 455 | N627AW | 98.0 | -1.00 | PHX | SLC | 507. |
| 3 | 2008-01-03 | 4 | 1900-01-01 08:40:00 | 1900-01-01 12:03:00 | CO | 287 | N21723 | 383. | 46.0 | EWR | SNA | 2.43e+03 |
| 4 | 2008-01-31 | 4 | 1900-01-01 12:50:00 | 1900-01-01 14:10:00 | MQ | 3,157 | N848AE | 80.0 | -14.0 | SJC | SNA | 342. |
Year_Month_DayofMonth
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
DayOfWeek
Int64DType- Null values
- 0 (0.0%)
- Unique values
- 3 (60.0%)
- Mean ± Std
- 4.40 ± 1.52
- Median ± IQR
- 4 ± 0
- Min | Max
- 3 | 7
CRSDepTime
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
CRSArrTime
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
UniqueCarrier
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
FlightNum
Int64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 971. ± 1.25e+03
- Median ± IQR
- 455 ± 520
- Min | Max
- 150 | 3,157
TailNum
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
CRSElapsedTime
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 198. ± 121.
- Median ± IQR
- 213. ± 118.
- Min | Max
- 80.0 | 383.
ArrDelay
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 6.80 ± 22.8
- Median ± IQR
- 1.00 ± 3.00
- Min | Max
- -14.0 | 46.0
Origin
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
Dest
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
Distance
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 1.20e+03 ± 836.
- Median ± IQR
- 1.33e+03 ± 892.
- Min | Max
- 342. | 2.43e+03
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Year_Month_DayofMonth | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 1 | DayOfWeek | Int64DType | False | 0 (0.0%) | 3 (60.0%) | 4.40 | 1.52 | 3 | 4 | 7 |
| 2 | CRSDepTime | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 3 | CRSArrTime | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 4 | UniqueCarrier | StringDtype | False | 0 (0.0%) | 4 (80.0%) | |||||
| 5 | FlightNum | Int64DType | False | 0 (0.0%) | 5 (100.0%) | 971. | 1.25e+03 | 150 | 455 | 3,157 |
| 6 | TailNum | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 7 | CRSElapsedTime | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 198. | 121. | 80.0 | 213. | 383. |
| 8 | ArrDelay | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 6.80 | 22.8 | -14.0 | 1.00 | 46.0 |
| 9 | Origin | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 10 | Dest | StringDtype | True | 0 (0.0%) | 4 (80.0%) | |||||
| 11 | Distance | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 1.20e+03 | 836. | 342. | 1.33e+03 | 2.43e+03 |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
Let us see the arrival delay of the flights in the dataset:
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()

Interesting, most delays are relatively short (<100 min), but there are some very long ones.
Airport data: an auxiliary table from the same database#
The
airportsdataset, with information such as their name and location (longitude, latitude).
| iata | airport | city | state | country | lat | long | |
|---|---|---|---|---|---|---|---|
| 0 | 00M | Thigpen | Bay Springs | MS | USA | 32.0 | -89.2 |
| 1 | 00R | Livingston Municipal | Livingston | TX | USA | 30.7 | -95.0 |
| 2 | 00V | Meadow Lake | Colorado Springs | CO | USA | 38.9 | -105. |
| 3 | 01G | Perry-Warsaw | Perry | NY | USA | 42.7 | -78.1 |
| 4 | 01J | Hilliard Airpark | Hilliard | FL | USA | 30.7 | -81.9 |
iata
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
airport
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
city
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
state
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
country
StringDtype- Null values
- 0 (0.0%)
USA
lat
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 35.0 ± 5.52
- Median ± IQR
- 32.0 ± 8.26
- Min | Max
- 30.7 | 42.7
long
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- -89.8 ± 10.6
- Median ± IQR
- -89.2 ± 13.1
- Min | Max
- -105. | -78.1
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | iata | StringDtype | True | 0 (0.0%) | 5 (100.0%) | |||||
| 1 | airport | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 2 | city | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 3 | state | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 4 | country | StringDtype | True | 0 (0.0%) | 1 (20.0%) | |||||
| 5 | lat | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 35.0 | 5.52 | 30.7 | 32.0 | 42.7 |
| 6 | long | Float64DType | False | 0 (0.0%) | 5 (100.0%) | -89.8 | 10.6 | -105. | -89.2 | -78.1 |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
Weather data: auxiliary tables from external sources#
The
weathertable. Weather details by measurement station. Both tables are from the Global Historical Climatology Network. Here, we consider only weather measurements from 2008.
weather = pd.read_csv(dataset.weather_path)
# Sampling for faster computation.
weather = weather.sample(10_000, random_state=seed, ignore_index=True)
weather.head()
| ID | YEAR/MONTH/DAY | TMAX | PRCP | SNOW | |
|---|---|---|---|---|---|
| 0 | RPM00098325 | 2008-08-20 | 290. | 856. | |
| 1 | ASN00023820 | 2008-07-20 | 28.0 | ||
| 2 | MXN00024056 | 2008-04-13 | 250. | 0.00 | |
| 3 | GME00126742 | 2008-11-06 | 116. | 4.00 | |
| 4 | ASN00074201 | 2008-04-12 | 0.00 |
ID
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
YEAR/MONTH/DAY
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
TMAX
Float64DType- Null values
- 2 (40.0%)
- Unique values
- 3 (60.0%)
- Mean ± Std
- 219. ± 91.1
- Median ± IQR
- 250. ± 174.
- Min | Max
- 116. | 290.
PRCP
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 178. ± 379.
- Median ± IQR
- 4.00 ± 28.0
- Min | Max
- 0.00 | 856.
SNOW
Float64DType- Null values
- 5 (100.0%)
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ID | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 1 | YEAR/MONTH/DAY | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 2 | TMAX | Float64DType | True | 2 (40.0%) | 3 (60.0%) | 219. | 91.1 | 116. | 250. | 290. |
| 3 | PRCP | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 178. | 379. | 0.00 | 4.00 | 856. |
| 4 | SNOW | Float64DType | False | 5 (100.0%) |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
The
stationsdataset. Provides location of all the weather measurement stations in the US.
| ID | LATITUDE | LONGITUDE | ELEVATION | STATE | NAME | GSN FLAG | HCN/CRN FLAG | WMO ID | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | ACW00011604 | 17.1 | -61.8 | 10.1 | ST JOHNS COOLIDGE FLD | ||||
| 1 | ACW00011647 | 17.1 | -61.8 | 19.2 | ST JOHNS | ||||
| 2 | AE000041196 | 25.3 | 55.5 | 34.0 | SHARJAH INTER. AIRP | GSN | 4.12e+04 | ||
| 3 | AEM00041194 | 25.3 | 55.4 | 10.4 | DUBAI INTL | 4.12e+04 | |||
| 4 | AEM00041217 | 24.4 | 54.7 | 26.8 | ABU DHABI INTL | 4.12e+04 |
ID
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
LATITUDE
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 21.9 ± 4.33
- Median ± IQR
- 24.4 ± 8.12
- Min | Max
- 17.1 | 25.3
LONGITUDE
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 8.39 ± 64.1
- Median ± IQR
- 54.7 ± 117.
- Min | Max
- -61.8 | 55.5
ELEVATION
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 20.1 ± 10.4
- Median ± IQR
- 19.2 ± 16.4
- Min | Max
- 10.1 | 34.0
STATE
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
NAME
StringDtype- Null values
- 5 (100.0%)
GSN FLAG
StringDtype- Null values
- 4 (80.0%)
GSN
HCN/CRN FLAG
Float64DType- Null values
- 2 (40.0%)
- Unique values
- 3 (60.0%)
- Mean ± Std
- 4.12e+04 ± 12.7
- Median ± IQR
- 4.12e+04 ± 23.0
- Min | Max
- 4.12e+04 | 4.12e+04
WMO ID
Float64DType- Null values
- 5 (100.0%)
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ID | StringDtype | True | 0 (0.0%) | 5 (100.0%) | |||||
| 1 | LATITUDE | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 21.9 | 4.33 | 17.1 | 24.4 | 25.3 |
| 2 | LONGITUDE | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 8.39 | 64.1 | -61.8 | 54.7 | 55.5 |
| 3 | ELEVATION | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 20.1 | 10.4 | 10.1 | 19.2 | 34.0 |
| 4 | STATE | StringDtype | True | 0 (0.0%) | 5 (100.0%) | |||||
| 5 | NAME | StringDtype | False | 5 (100.0%) | ||||||
| 6 | GSN FLAG | StringDtype | True | 4 (80.0%) | 1 (20.0%) | |||||
| 7 | HCN/CRN FLAG | Float64DType | False | 2 (40.0%) | 3 (60.0%) | 4.12e+04 | 12.7 | 4.12e+04 | 4.12e+04 | 4.12e+04 |
| 8 | WMO ID | Float64DType | False | 5 (100.0%) |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
Joining: feature augmentation across tables#
First we join the stations with weather on the ID (exact join):
| ID | LATITUDE | LONGITUDE | ELEVATION | STATE | NAME | GSN FLAG | HCN/CRN FLAG | WMO ID | YEAR/MONTH/DAY | TMAX | PRCP | SNOW | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | AGE00147708 | 36.7 | 4.05 | 222. | TIZI OUZOU | 6.04e+04 | 2008-04-17 | 225. | 0.00 | ||||
| 1 | AGM00060403 | 36.5 | 7.47 | 228. | GUELMA | 6.04e+04 | 2008-09-17 | 324. | 0.00 | ||||
| 2 | AGM00060403 | 36.5 | 7.47 | 228. | GUELMA | 6.04e+04 | 2008-04-11 | 245. | 0.00 | ||||
| 3 | AGM00060419 | 36.3 | 6.62 | 690. | MOHAMED BOUDIAF INTL | 6.04e+04 | 2008-06-30 | 340. | 0.00 | ||||
| 4 | AGM00060430 | 36.3 | 2.23 | 721. | MILIANA | 6.04e+04 | 2008-08-17 | 323. | 0.00 |
ID
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
LATITUDE
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 36.4 ± 0.178
- Median ± IQR
- 36.5 ± 0.167
- Min | Max
- 36.3 | 36.7
LONGITUDE
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 5.57 ± 2.33
- Median ± IQR
- 6.62 ± 3.42
- Min | Max
- 2.23 | 7.47
ELEVATION
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 418. ± 263.
- Median ± IQR
- 228. ± 462.
- Min | Max
- 222. | 721.
STATE
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
NAME
StringDtype- Null values
- 5 (100.0%)
GSN FLAG
StringDtype- Null values
- 5 (100.0%)
HCN/CRN FLAG
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 6.04e+04 ± 14.2
- Median ± IQR
- 6.04e+04 ± 16.0
- Min | Max
- 6.04e+04 | 6.04e+04
WMO ID
Float64DType- Null values
- 5 (100.0%)
YEAR/MONTH/DAY
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
TMAX
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 291. ± 52.4
- Median ± IQR
- 323. ± 79.0
- Min | Max
- 225. | 340.
PRCP
Float64DType- Null values
- 0 (0.0%)
0.0
SNOW
Float64DType- Null values
- 5 (100.0%)
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ID | StringDtype | True | 0 (0.0%) | 4 (80.0%) | |||||
| 1 | LATITUDE | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 36.4 | 0.178 | 36.3 | 36.5 | 36.7 |
| 2 | LONGITUDE | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 5.57 | 2.33 | 2.23 | 6.62 | 7.47 |
| 3 | ELEVATION | Float64DType | True | 0 (0.0%) | 4 (80.0%) | 418. | 263. | 222. | 228. | 721. |
| 4 | STATE | StringDtype | False | 0 (0.0%) | 4 (80.0%) | |||||
| 5 | NAME | StringDtype | False | 5 (100.0%) | ||||||
| 6 | GSN FLAG | StringDtype | False | 5 (100.0%) | ||||||
| 7 | HCN/CRN FLAG | Float64DType | True | 0 (0.0%) | 4 (80.0%) | 6.04e+04 | 14.2 | 6.04e+04 | 6.04e+04 | 6.04e+04 |
| 8 | WMO ID | Float64DType | False | 5 (100.0%) | ||||||
| 9 | YEAR/MONTH/DAY | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 10 | TMAX | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 291. | 52.4 | 225. | 323. | 340. |
| 11 | PRCP | Float64DType | True | 0 (0.0%) | 1 (20.0%) | 0.00 | 0.00 | |||
| 12 | SNOW | Float64DType | False | 5 (100.0%) |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
Then we join this table with the airports so that we get all auxiliary tables into one.
from skrub import Joiner
joiner = Joiner(airports, aux_key=["lat", "long"], main_key=["LATITUDE", "LONGITUDE"])
aux_augmented = joiner.fit_transform(aux)
aux_augmented.head()
| ID | LATITUDE | LONGITUDE | ELEVATION | STATE | NAME | GSN FLAG | HCN/CRN FLAG | WMO ID | YEAR/MONTH/DAY | TMAX | PRCP | SNOW | iata | airport | city | state | country | lat | long | skrub_Joiner_distance | skrub_Joiner_rescaled_distance | skrub_Joiner_match_accepted | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | AGE00147708 | 36.7 | 4.05 | 222. | TIZI OUZOU | 6.04e+04 | 2008-04-17 | 225. | 0.00 | EPM | Eastport Municipal | Eastport | ME | USA | 44.9 | -67.0 | 3.26 | 4.47 | True | ||||
| 1 | AGM00060403 | 36.5 | 7.47 | 228. | GUELMA | 6.04e+04 | 2008-09-17 | 324. | 0.00 | EPM | Eastport Municipal | Eastport | ME | USA | 44.9 | -67.0 | 3.41 | 4.68 | True | ||||
| 2 | AGM00060403 | 36.5 | 7.47 | 228. | GUELMA | 6.04e+04 | 2008-04-11 | 245. | 0.00 | EPM | Eastport Municipal | Eastport | ME | USA | 44.9 | -67.0 | 3.41 | 4.68 | True | ||||
| 3 | AGM00060419 | 36.3 | 6.62 | 690. | MOHAMED BOUDIAF INTL | 6.04e+04 | 2008-06-30 | 340. | 0.00 | EPM | Eastport Municipal | Eastport | ME | USA | 44.9 | -67.0 | 3.38 | 4.64 | True | ||||
| 4 | AGM00060430 | 36.3 | 2.23 | 721. | MILIANA | 6.04e+04 | 2008-08-17 | 323. | 0.00 | EPM | Eastport Municipal | Eastport | ME | USA | 44.9 | -67.0 | 3.20 | 4.39 | True |
ID
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
LATITUDE
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 36.4 ± 0.178
- Median ± IQR
- 36.5 ± 0.167
- Min | Max
- 36.3 | 36.7
LONGITUDE
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 5.57 ± 2.33
- Median ± IQR
- 6.62 ± 3.42
- Min | Max
- 2.23 | 7.47
ELEVATION
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 418. ± 263.
- Median ± IQR
- 228. ± 462.
- Min | Max
- 222. | 721.
STATE
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
NAME
StringDtype- Null values
- 5 (100.0%)
GSN FLAG
StringDtype- Null values
- 5 (100.0%)
HCN/CRN FLAG
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 6.04e+04 ± 14.2
- Median ± IQR
- 6.04e+04 ± 16.0
- Min | Max
- 6.04e+04 | 6.04e+04
WMO ID
Float64DType- Null values
- 5 (100.0%)
YEAR/MONTH/DAY
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
TMAX
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 5 (100.0%)
- Mean ± Std
- 291. ± 52.4
- Median ± IQR
- 323. ± 79.0
- Min | Max
- 225. | 340.
PRCP
Float64DType- Null values
- 0 (0.0%)
0.0
SNOW
Float64DType- Null values
- 5 (100.0%)
iata
StringDtype- Null values
- 0 (0.0%)
EPM
airport
StringDtype- Null values
- 0 (0.0%)
Eastport Municipal
city
StringDtype- Null values
- 0 (0.0%)
Eastport
state
StringDtype- Null values
- 0 (0.0%)
ME
country
StringDtype- Null values
- 0 (0.0%)
USA
lat
Float64DType- Null values
- 0 (0.0%)
44.91011111
long
Float64DType- Null values
- 0 (0.0%)
-67.01269444
skrub_Joiner_distance
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 3.33 ± 0.0973
- Median ± IQR
- 3.38 ± 0.152
- Min | Max
- 3.20 | 3.41
skrub_Joiner_rescaled_distance
Float64DType- Null values
- 0 (0.0%)
- Unique values
- 4 (80.0%)
- Mean ± Std
- 4.57 ± 0.133
- Median ± IQR
- 4.64 ± 0.208
- Min | Max
- 4.39 | 4.68
skrub_Joiner_match_accepted
BoolDType- Null values
- 0 (0.0%)
True
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ID | StringDtype | True | 0 (0.0%) | 4 (80.0%) | |||||
| 1 | LATITUDE | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 36.4 | 0.178 | 36.3 | 36.5 | 36.7 |
| 2 | LONGITUDE | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 5.57 | 2.33 | 2.23 | 6.62 | 7.47 |
| 3 | ELEVATION | Float64DType | True | 0 (0.0%) | 4 (80.0%) | 418. | 263. | 222. | 228. | 721. |
| 4 | STATE | StringDtype | False | 0 (0.0%) | 4 (80.0%) | |||||
| 5 | NAME | StringDtype | False | 5 (100.0%) | ||||||
| 6 | GSN FLAG | StringDtype | False | 5 (100.0%) | ||||||
| 7 | HCN/CRN FLAG | Float64DType | True | 0 (0.0%) | 4 (80.0%) | 6.04e+04 | 14.2 | 6.04e+04 | 6.04e+04 | 6.04e+04 |
| 8 | WMO ID | Float64DType | False | 5 (100.0%) | ||||||
| 9 | YEAR/MONTH/DAY | StringDtype | False | 0 (0.0%) | 5 (100.0%) | |||||
| 10 | TMAX | Float64DType | False | 0 (0.0%) | 5 (100.0%) | 291. | 52.4 | 225. | 323. | 340. |
| 11 | PRCP | Float64DType | True | 0 (0.0%) | 1 (20.0%) | 0.00 | 0.00 | |||
| 12 | SNOW | Float64DType | False | 5 (100.0%) | ||||||
| 13 | iata | StringDtype | True | 0 (0.0%) | 1 (20.0%) | |||||
| 14 | airport | StringDtype | True | 0 (0.0%) | 1 (20.0%) | |||||
| 15 | city | StringDtype | True | 0 (0.0%) | 1 (20.0%) | |||||
| 16 | state | StringDtype | True | 0 (0.0%) | 1 (20.0%) | |||||
| 17 | country | StringDtype | True | 0 (0.0%) | 1 (20.0%) | |||||
| 18 | lat | Float64DType | True | 0 (0.0%) | 1 (20.0%) | 44.9 | 0.00 | |||
| 19 | long | Float64DType | True | 0 (0.0%) | 1 (20.0%) | -67.0 | 0.00 | |||
| 20 | skrub_Joiner_distance | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 3.33 | 0.0973 | 3.20 | 3.38 | 3.41 |
| 21 | skrub_Joiner_rescaled_distance | Float64DType | False | 0 (0.0%) | 4 (80.0%) | 4.57 | 0.133 | 4.39 | 4.64 | 4.68 |
| 22 | skrub_Joiner_match_accepted | BoolDType | True | 0 (0.0%) | 1 (20.0%) | 1.00 |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
Joining airports with flights data: Let’s instantiate another multiple key joiner on the date and the airport:
joiner = Joiner(
aux_augmented,
aux_key=["YEAR/MONTH/DAY", "iata"],
main_key=["Year_Month_DayofMonth", "Origin"],
)
flights.drop(columns=["TailNum", "FlightNum"])
| Year_Month_DayofMonth | DayOfWeek | CRSDepTime | CRSArrTime | UniqueCarrier | CRSElapsedTime | ArrDelay | Origin | Dest | Distance | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2008-01-13 | 7 | 1900-01-01 18:35:00 | 1900-01-01 20:08:00 | CO | 213. | 1.00 | IAH | ONT | 1.33e+03 |
| 1 | 2008-02-21 | 4 | 1900-01-01 14:30:00 | 1900-01-01 16:06:00 | NW | 216. | 2.00 | MSP | SEA | 1.40e+03 |
| 2 | 2008-03-26 | 3 | 1900-01-01 07:00:00 | 1900-01-01 09:38:00 | US | 98.0 | -1.00 | PHX | SLC | 507. |
| 3 | 2008-01-03 | 4 | 1900-01-01 08:40:00 | 1900-01-01 12:03:00 | CO | 383. | 46.0 | EWR | SNA | 2.43e+03 |
| 4 | 2008-01-31 | 4 | 1900-01-01 12:50:00 | 1900-01-01 14:10:00 | MQ | 80.0 | -14.0 | SJC | SNA | 342. |
| 4,995 | 2008-04-01 | 2 | 1900-01-01 10:14:00 | 1900-01-01 10:45:00 | EV | 91.0 | 50.0 | ATL | PFN | 247. |
| 4,996 | 2008-02-25 | 1 | 1900-01-01 12:00:00 | 1900-01-01 13:30:00 | AA | 210. | -2.00 | DFW | RNO | 1.34e+03 |
| 4,997 | 2008-01-20 | 7 | 1900-01-01 06:00:00 | 1900-01-01 07:30:00 | AQ | 90.0 | -13.0 | LAS | OAK | 407. |
| 4,998 | 2008-03-14 | 5 | 1900-01-01 06:42:00 | 1900-01-01 08:04:00 | XE | 82.0 | -16.0 | ROC | CLE | 245. |
| 4,999 | 2008-04-18 | 5 | 1900-01-01 19:38:00 | 1900-01-01 20:06:00 | OO | 88.0 | -3.00 | ICT | DEN | 419. |
Year_Month_DayofMonth
StringDtype- Null values
- 0 (0.0%)
- Unique values
-
120 (2.4%)
This column has a high cardinality (> 40).
DayOfWeek
Int64DType- Null values
- 0 (0.0%)
- Unique values
- 7 (0.1%)
- Mean ± Std
- 3.93 ± 1.99
- Median ± IQR
- 4 ± 4
- Min | Max
- 1 | 7
CRSDepTime
StringDtype- Null values
- 0 (0.0%)
- Unique values
-
736 (14.7%)
This column has a high cardinality (> 40).
CRSArrTime
StringDtype- Null values
- 14 (0.3%)
- Unique values
-
1,025 (20.5%)
This column has a high cardinality (> 40).
UniqueCarrier
StringDtype- Null values
- 0 (0.0%)
- Unique values
- 20 (0.4%)
CRSElapsedTime
Float64DType- Null values
- 4 (< 0.1%)
- Unique values
-
340 (6.8%)
This column has a high cardinality (> 40).
- Mean ± Std
- 129. ± 68.9
- Median ± IQR
- 110. ± 81.0
- Min | Max
- 27.0 | 565.
ArrDelay
Float64DType- Null values
- 153 (3.1%)
- Unique values
-
249 (5.0%)
This column has a high cardinality (> 40).
- Mean ± Std
- 10.6 ± 44.9
- Median ± IQR
- -1.00 ± 23.0
- Min | Max
- -45.0 | 1.10e+03
Origin
StringDtype- Null values
- 0 (0.0%)
- Unique values
-
230 (4.6%)
This column has a high cardinality (> 40).
Dest
StringDtype- Null values
- 0 (0.0%)
- Unique values
-
230 (4.6%)
This column has a high cardinality (> 40).
Distance
Float64DType- Null values
- 0 (0.0%)
- Unique values
-
1,022 (20.4%)
This column has a high cardinality (> 40).
- Mean ± Std
- 720. ± 554.
- Median ± IQR
- 580. ± 626.
- Min | Max
- 31.0 | 4.96e+03
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
|
Column
|
Column name
|
dtype
|
Is sorted
|
Null values
|
Unique values
|
Mean
|
Std
|
Min
|
Median
|
Max
|
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Year_Month_DayofMonth | StringDtype | False | 0 (0.0%) | 120 (2.4%) | |||||
| 1 | DayOfWeek | Int64DType | False | 0 (0.0%) | 7 (0.1%) | 3.93 | 1.99 | 1 | 4 | 7 |
| 2 | CRSDepTime | StringDtype | False | 0 (0.0%) | 736 (14.7%) | |||||
| 3 | CRSArrTime | StringDtype | False | 14 (0.3%) | 1025 (20.5%) | |||||
| 4 | UniqueCarrier | StringDtype | False | 0 (0.0%) | 20 (0.4%) | |||||
| 5 | CRSElapsedTime | Float64DType | False | 4 (< 0.1%) | 340 (6.8%) | 129. | 68.9 | 27.0 | 110. | 565. |
| 6 | ArrDelay | Float64DType | False | 153 (3.1%) | 249 (5.0%) | 10.6 | 44.9 | -45.0 | -1.00 | 1.10e+03 |
| 7 | Origin | StringDtype | False | 0 (0.0%) | 230 (4.6%) | |||||
| 8 | Dest | StringDtype | False | 0 (0.0%) | 230 (4.6%) | |||||
| 9 | Distance | Float64DType | False | 0 (0.0%) | 1022 (20.4%) | 720. | 554. | 31.0 | 580. | 4.96e+03 |
No columns match the selected filter: . You can change the column filter in the dropdown menu above.
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").
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.
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)
We isolate our target variable and remove useless ID variables:
y = flights["ArrDelay"]
X = flights.drop(columns=["ArrDelay"])
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).
y = (y > 0).astype(int)
y.value_counts()
ArrDelay
0 2727
1 2273
Name: count, dtype: int64
The results:
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)
0.548
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.
Total running time of the script: (0 minutes 42.137 seconds)
Estimated memory usage: 3044 MB