<jupyter_start><jupyter_text>**Important Disclaimer:** Mockup. So far, the example here does not supportfit or predict, let alone hyperparameter tuning etc.The pipeline shown here assumes the example input tables fromFig. 2of the following paper:<NAME>, <NAME>, <NAME>, <NAME>, <NAME>, and <NAME>."One button machine for automating feature engineering in relational databases". 2017. https://arxiv.org/abs/1706.00327<jupyter_code>from lale.expressions import it, replace, sum, max, count, month, day_of_month, item
import numpy as np
from lale.lib.lale import Scan, Join, Map, GroupBy, Aggregate, ConcatFeatures
from sklearn.feature_selection import SelectKBest as SelectFeatures
from sklearn.pipeline import Pipeline
from lale.lib.autoai_libs import NumpyColumnSelector, CatEncoder, OptStandardScaler, FS1
from sklearn.linear_model import LogisticRegression as LR
from sklearn.neighbors import KNeighborsClassifier as KNN
from xgboost import XGBClassifier as XGBoost
import lale
lale.wrap_imported_operators()
# one-to-one path doesn't need GroupBy >> Aggregate
info_features = (
       (Scan(table=it.main) & Scan(table=it.info))
    >> Join(pred=[it.main.TrainId == it.info.Train_Id, #note the underscore
                  it.main['Arrival time'] >= it.info.TimeStamp])
    >> Map(columns=[replace(it['Train class'], {'Regional': 0, 'Intercity': 1}),
                    it['Max Speed (km/h)'],
                    month(it['Arrival time'], fmt='YYYY-MM-DD HH:MM:SS'),
                    day_of_month(it['Arrival time'])]))
# one-to-many path (multiple delay rows per main-table row)
delay_features = (
       (Scan(table=it.main) & Scan(table=it.delay))
    >> Join(pred=[it.main.TrainId == it.delay.TrainId,
                  it.main['Arrival time'] >= it.delay.TimeStamp])
    >> GroupBy(by=it.MessageId) #primary key of main table
    >> Aggregate(columns=[sum(it.Delay), max(it.Delay)]))
# multi-hop one-to-many path uses multi-way join
event_features = (
       (Scan(table=it.main) & Scan(table=it.delay) & Scan(table=it.event))
    >> Join(pred=[it.main.TrainId == it.delay.TrainId,
                  it.main['Arrival time'] >= it.delay.TimeStamp,
                  it.delay.StationId == it.event.StationId,
                  it.main.TimeStamp >= it.event.TimeStamp])
    >> GroupBy(by=it.MessageId) #primary key of main table
    >> Aggregate(columns=[count(it.Event),
                          item(it['Train class'], 'Roadwork')]))
all_features = Pipeline(steps=[('data_joins',
       (info_features & delay_features & event_features)
    >> ConcatFeatures >> SelectFeatures())])
cats_prep = NumpyColumnSelector(columns=[0]) >> CatEncoder(dtype=np.float64)
cont_prep = NumpyColumnSelector(columns=[1,2]) >> OptStandardScaler(use_scaler_flag=True)
all_prep = Pipeline(steps=[('preprocessing',
    (cats_prep & cont_prep) >> ConcatFeatures >> FS1(additional_col_count_to_keep=3))])
classifier = LR | KNN | XGBoost
pipeline = all_features >> all_prep >> classifier
pipeline.visualize()
pipeline.pretty_print(ipython_display=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13253012048192772, 'fraction_numerical': 0.007815043959622273, 'mean_word_length': 4.171717171717172, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 49, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '504694', 'license': 'Apache-2.0', 'n_tokens_mistral': 1024, 'n_tokens_neox': 960, 'n_words': 223, 'path': 'examples/demo_multi_table.ipynb', 'repo_name': 'ksrinivs64/lale', 'size': '3112'}