Spatial join examples

[jeromedockes]

it seems joining weather data to the flights table does not help delay prediction.
The example 07 reports an an accuracy of 0.58, this is also what I see when not joining anything to the flights table:

from skrub.datasets import fetch_figshare
from skrub import TableVectorizer
from sklearn.ensemble import HistGradientBoostingClassifier
from sklearn.pipeline import make_pipeline
from sklearn.model_selection import cross_val_score

flights = fetch_figshare("41771418").X
flights = flights.sample(20_000, random_state=1, ignore_index=True)

pipe = make_pipeline(TableVectorizer(), HistGradientBoostingClassifier(random_state=1))

y = flights["ArrDelay"]
y = (y > 0).astype(int)

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

scores = cross_val_score(pipe, X, y)
print(scores)
print(scores.mean())

prints

[0.591   0.58575 0.583   0.58175 0.59725]
0.58775

I also see the same thing when using the interpolation join instead of the fuzzy
join used in example 7.

We can discuss ideas for other example datasets here rather than in #742 .

Also, in example 7 I wonder why we join weather to airports, and then the resulting join to the flights.
This causes some flights to be joined with the wrong airport because the second join involves the date.
IMO it would be more natural to join flights and airports, which come from the same database and have an exact correspondance, and then fuzzy-join the weather on top.

Finally, in the stations table "NAME" and other columns are empty and "STATE" doesn't seem to contain state names; there may be an issue with the dataset?

D create table stations as (select * from 'ghcnd-stations.parquet');
D SUMMARIZE stations;
┌──────────────┬─────────────┬────────────────────┬─────────────┬───────────────┬────────────────────┬────────────────────┬────────────────────┬───────────────────┬────────────────────┬────────┬─────────────────┐
│ column_name  │ column_type │        min         │     max     │ approx_unique │        avg         │        std         │        q25         │        q50        │        q75         │ count  │ null_percentage │
│   varchar    │   varchar   │      varchar       │   varchar   │    varchar    │      varchar       │      varchar       │      varchar       │      varchar      │      varchar       │ int64  │     varchar     │
├──────────────┼─────────────┼────────────────────┼─────────────┼───────────────┼────────────────────┼────────────────────┼────────────────────┼───────────────────┼────────────────────┼────────┼─────────────────┤
│ ID           │ VARCHAR     │ ACW00011604        │ ZI000067991 │ 123462        │                    │                    │                    │                   │                    │ 124245 │ 0.0%            │
│ LATITUDE     │ DOUBLE      │ -9.99              │ 90.0        │ 67200         │ 36.357696809529266 │ 11.917568433949313 │ 30.591063924257146 │ 37.53172333992126 │ 42.86030323153433  │ 124245 │ 0.0%            │
│ LONGITUDE    │ DOUBLE      │ -99.9996           │ 179.983     │ 79428         │ 15.067416970502352 │ 98.70572462644033  │ -83.9791737081754  │ 8.760004264718507 │ 113.41383116110536 │ 124245 │ 0.0%            │
│ ELEVATION    │ DOUBLE      │ -999.9             │ 5033.0      │ 9683          │ 447.46570727194216 │ 658.2239622651306  │ 84.67912606666525  │ 256.9698438082354 │ 599.8694827312933  │ 124245 │ 0.0%            │
│ STATE        │ VARCHAR     │ "HOME" MONOGORILBY │ Zirkel      │ 116632        │                    │                    │                    │                   │                    │ 124245 │ 0.0%            │
│ NAME         │ VARCHAR     │ &R.                │ ZAC         │ 825           │                    │                    │                    │                   │                    │ 124245 │ 98.3%           │
│ GSN FLAG     │ VARCHAR     │ GSN                │ GSN         │ 1             │                    │                    │                    │                   │                    │ 124245 │ 99.2%           │
│ HCN/CRN FLAG │ DOUBLE      │ 1001.0             │ 99999.0     │ 7673          │ 55164.2227672956   │ 26548.38870264178  │ 33909.633997680925 │ 57841.59091049783 │ 72566.23236906205  │ 124245 │ 93.6%           │
│ WMO ID       │ DOUBLE      │                    │             │ 0             │                    │                    │                    │                   │                    │ 124245 │ 100.0%          │
└──────────────┴─────────────┴────────────────────┴─────────────┴───────────────┴────────────────────┴────────────────────┴────────────────────┴───────────────────┴────────────────────┴────────┴─────────────────┘
D select * from stations limit 10;
┌─────────────┬──────────┬───────────┬───────────┬───────────────────────┬─────────┬──────────┬──────────────┬────────┐
│     ID      │ LATITUDE │ LONGITUDE │ ELEVATION │         STATE         │  NAME   │ GSN FLAG │ HCN/CRN FLAG │ WMO ID │
│   varchar   │  double  │  double   │  double   │        varchar        │ varchar │ varchar  │    double    │ double │
├─────────────┼──────────┼───────────┼───────────┼───────────────────────┼─────────┼──────────┼──────────────┼────────┤
│ ACW00011604 │  17.1167 │  -61.7833 │      10.1 │ ST JOHNS COOLIDGE FLD │         │          │              │        │
│ ACW00011647 │  17.1333 │  -61.7833 │      19.2 │ ST JOHNS              │         │          │              │        │
│ AE000041196 │   25.333 │    55.517 │      34.0 │ SHARJAH INTER. AIRP   │         │ GSN      │      41196.0 │        │
│ AEM00041194 │   25.255 │    55.364 │      10.4 │ DUBAI INTL            │         │          │      41194.0 │        │
│ AEM00041217 │   24.433 │    54.651 │      26.8 │ ABU DHABI INTL        │         │          │      41217.0 │        │
│ AEM00041218 │   24.262 │    55.609 │     264.9 │ AL AIN INTL           │         │          │      41218.0 │        │
│ AF000040930 │   35.317 │    69.017 │    3366.0 │ NORTH-SALANG          │         │ GSN      │      40930.0 │        │
│ AFM00040938 │    34.21 │    62.228 │     977.2 │ HERAT                 │         │          │      40938.0 │        │
│ AFM00040948 │   34.566 │    69.212 │    1791.3 │ KABUL INTL            │         │          │      40948.0 │        │
│ AFM00040990 │     31.5 │     65.85 │    1010.0 │ KANDAHAR AIRPORT      │         │          │      40990.0 │        │
├─────────────┴──────────┴───────────┴───────────┴───────────────────────┴─────────┴──────────┴──────────────┴────────┤
│ 10 rows                                                                                                   9 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D

[Vincent-Maladiere]

cc @jovan-stojanovic who worked on it

[jovan-stojanovic]

Also, in example 7 I wonder why we join weather to airports, and then the resulting join to the flights. This causes some flights to be joined with the wrong airport because the second join involves the date. IMO it would be more natural to join flights and airports, which come from the same database and have an exact correspondance, and then fuzzy-join the weather on top.

This is true, but the reason for doing this was that joining airports to weather was equivalent to joining: 3 376 rows to 11 282 238 rows.
If you join airports and flights first, and then weather, you would end up joining: 2 370 030 rows x 11 282 238 rows.
This was something that the fuzzy_join took simply too long to do. Only solution would be severely sub-sampling the data.

[jeromedockes]

interesting, thanks!

[jovan-stojanovic]

Finally, in the stations table "NAME" and other columns are empty and "STATE" doesn't seem to contain state names; there may be an issue with the dataset?

Yes, this is something that was directly extracted from the Global Historical Climatology Network website and was left uncurated, in the spirit of working with "real-world" dirty data.

I don't know if there is a way to extract this additional information from other datasets from the website, but for this example, I used longitude and latitude.

[jeromedockes]

If the task is to predict the delays of future flights I wonder if we should use a TimeSeriesSplit in the flight delays examples rather than KFold?