` 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_02_data_ops_1120_multiple_tables.py: Multiples tables: building machine learning pipelines with DataOps ================================================================== In this example, we show how to build a DataOps plan to handle pre-processing, validation and hyperparameter tuning of a dataset with **multiple tables**. We consider the credit fraud dataset, which contains two tables: one for baskets (orders) and one for products. The goal is to predict whether a basket (a single order that has been placed with the website) is fraudulent or not, based on the products it contains. .. currentmodule:: skrub .. |choose_from| replace:: :func:`skrub.choose_from` .. |choose_int| replace:: :func:`skrub.choose_int` .. |choose_float| replace:: :func:`skrub.choose_float` .. |MinHashEncoder| replace:: :class:`~skrub.MinHashEncoder` .. |StringEncoder| replace:: :class:`~skrub.StringEncoder` .. |TableVectorizer| replace:: :class:`~skrub.TableVectorizer` .. |var| replace:: :func:`skrub.var` .. |TableReport| replace:: :class:`~skrub.TableReport` .. |HistGradientBoostingClassifier| replace:: :class:`~sklearn.ensemble.HistGradientBoostingClassifier` .. |make_randomized_search| replace:: :func:`~skrub.DataOp.skb.make_randomized_search` .. |RocCurveDisplay| replace:: :class:`~sklearn.metrics.RocCurveDisplay` .. GENERATED FROM PYTHON SOURCE LINES 33-40 The credit fraud dataset ------------------------ We fetch the credit fraud dataset using ``fetch_credit_fraud``. This dataset contains two tables: ``baskets`` and ``products``. We load the training split of the dataset to train the model. At the end of the example, we will load the test split to evaluate the model on unseen data. .. GENERATED FROM PYTHON SOURCE LINES 42-53 .. code-block:: Python import pandas as pd import skrub import skrub.datasets # Small display detail: open the graphs by default in the visualizations shown # in the rest of this notebook. skrub.set_config(data_ops_open_graph_dropdown=True) dataset = skrub.datasets.fetch_credit_fraud(split="train") .. GENERATED FROM PYTHON SOURCE LINES 54-59 We define two skrub variables that store the content of the two csv files. These variables will be used as inputs to the DataOps plan we will build. Later, when we want to apply the resulting model to new data, we will need to provide dataframes to the same variables, but with the content of the test split of the dataset instead. .. GENERATED FROM PYTHON SOURCE LINES 59-62 .. code-block:: Python baskets = skrub.var("baskets", pd.read_csv(dataset.baskets_path)) products = skrub.var("products", pd.read_csv(dataset.products_path)) .. GENERATED FROM PYTHON SOURCE LINES 63-66 Now we can use the |TableReport| provided by the Data Ops to inspect the two tables. The ``baskets`` table contains the list of basket IDs, and a fraud flag indicating whether the basket is fraudulent or not. .. GENERATED FROM PYTHON SOURCE LINES 66-67 .. code-block:: Python baskets .. raw:: html

<Var 'baskets'>

Show/Hide graph

Result:

	ID	fraud_flag
0	51,113	0
1	41,798	0
2	39,361	0
3	38,615	0
4	70,262	0

61,236	21,243	0
61,237	45,891	0
61,238	42,613	0
61,239	43,567	0
61,240	68,268	0

Column	Column name	dtype	Is sorted	Null values	Unique values	Mean	Std	Min	Median	Max
0	ID	Int64DType	False	0 (0.0%)	61241 (100.0%)	3.82e+04	2.21e+04	0	38,158	76,543
1	fraud_flag	Int64DType	False	0 (0.0%)	2 (< 0.1%)	0.0130	0.113	0	0	1

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 68-72 We mark the "ID" column of the ``baskets`` table as ``X``, and the ``"fraud_flag"`` column as ``y``. This allows the Data Ops to track the indices of the variables when splitting for cross-validation. so that DataOps can use their indices for train-test splitting and cross-validation. .. GENERATED FROM PYTHON SOURCE LINES 72-74 .. code-block:: Python basket_ids = baskets[["ID"]].skb.mark_as_X() fraud_flags = baskets["fraud_flag"].skb.mark_as_y() .. GENERATED FROM PYTHON SOURCE LINES 75-79 The ``products`` table contains information about the products that have been purchased, and the basket they belong to. A basket contains at least one product. Products can be associated with the corresponding basket through the "basket_ID" column. .. GENERATED FROM PYTHON SOURCE LINES 81-82 .. code-block:: Python products .. raw:: html

<Var 'products'>

Show/Hide graph

Result:

	basket_ID	item	cash_price	make	model	goods_code	Nbr_of_prod_purchas
0	51,113	COMPUTER PERIPHERALS ACCESSORIES	409	APPLE	APPLE WATCH SERIES 6 GPS 44MM SPACE GREY ALUMINIUM	239001518	1
1	41,798	COMPUTERS	1,187	APPLE	2020 APPLE MACBOOK PRO 13 TOUCH BAR M1 PROCESSOR 8	239246780	1
2	39,361	COMPUTERS	898	APPLE	2020 APPLE MACBOOK AIR 13 3 RETINA DISPLAY M1 PROC	239246776	1
3	38,615	COMPUTER PERIPHERALS ACCESSORIES	379	APPLE	APPLE WATCH SERIES 6 GPS 40MM BLUE ALUMINIUM CASE	239001540	1
4	70,262	COMPUTERS	1,899	APPLE	2021 APPLE MACBOOK PRO 14 M1 PRO PROCESSOR 16GB RA	240575990	1

109,375	42,613	BEDROOM FURNITURE	259	SILENTNIGHT	SILENTNIGHT SLEEP GENIUS FULL HEIGHT HEADBOARD DOU	236938439	1
109,376	42,613	OUTDOOR FURNITURE	949	LG OUTDOOR	LG OUTDOOR BERGEN 2-SEAT GARDEN SIDE TABLE RECLINI	239742814	1
109,377	43,567	COMPUTERS	1,099	APPLE	2021 APPLE IPAD PRO 12 9 M1 PROCESSOR IOS WI-FI 25	240040978	1
109,378	43,567	COMPUTERS	2,099	APPLE	2020 APPLE IMAC 27 ALL-IN-ONE INTEL CORE I7 8GB RA	238923518	1
109,379	68,268	TELEVISIONS HOME CINEMA	799	LG	LG OLED48A16LA 2021 OLED HDR 4K ULTRA HD SMART TV	239866717	1

Column	Column name	dtype	Is sorted	Null values	Unique values	Mean	Std	Min	Median	Max
0	basket_ID	Int64DType	False	0 (0.0%)	61241 (56.0%)	3.59e+04	2.24e+04	0	35,203	76,543
1	item	StringDtype	False	0 (0.0%)	166 (0.2%)
2	cash_price	Int64DType	False	0 (0.0%)	1280 (1.2%)	672.	714.	0	499	18,349
3	make	StringDtype	False	1273 (1.2%)	690 (0.6%)
4	model	StringDtype	False	1273 (1.2%)	6477 (5.9%)
5	goods_code	StringDtype	False	0 (0.0%)	10738 (9.8%)
6	Nbr_of_prod_purchas	Int64DType	False	0 (0.0%)	19 (< 0.1%)	1.05	0.426	1	1	40

Please enable javascript

.. GENERATED FROM PYTHON SOURCE LINES 83-112 A data-processing challenge ---------------------------- The general structure of the DataOps plan we want to build looks like this: .. image:: ../../_static/credit_fraud_diagram.svg :width: 300 We want to fit a |HistGradientBoostingClassifier| to predict the fraud flag (y). However, since the features for each basket are stored in the products table, we need to extract these features, aggregate them at the basket level, and merge the result with the basket data. .. admonition:: Why building a pipeline for this is hard :collapsible: closed We can use the |TableVectorizer| to vectorize the products, but we then need to aggregate the resulting vectors to obtain a single row per basket. Using a scikit-learn Pipeline is tricky because the |TableVectorizer| would be fitted on a table with a different number of rows than the target y (the baskets table), which scikit-learn does not allow. While we could fit the |TableVectorizer| manually, this would forfeit scikit-learn’s tooling for managing transformations, storing fitted estimators, splitting data, cross-validation, and hyper-parameter tuning. We would also have to handle the aggregation and join ourselves, likely with error-prone Pandas code. Fortunately, skrub DataOps provide a powerful alternative for building flexible plans that address these problems. .. GENERATED FROM PYTHON SOURCE LINES 114-122 Building a multi-table DataOps plan ------------------------------------ Since our DataOps expect dataframes for products, baskets and fraud flags, we manipulate those objects as we would manipulate pandas dataframes. For instance, we filter products to keep only those that match one of the baskets in the ``baskets`` table, and then add a column containing the total amount for each kind of product in a basket: %% .. GENERATED FROM PYTHON SOURCE LINES 122-128 .. code-block:: Python kept_products = products[products["basket_ID"].isin(basket_ids["ID"])] products_with_total = kept_products.assign( total_price=kept_products["Nbr_of_prod_purchas"] * kept_products["cash_price"] ) products_with_total .. raw:: html

<CallMethod 'assign'>

Show/Hide graph

Result:

	basket_ID	item	cash_price	make	model	goods_code	Nbr_of_prod_purchas	total_price
0	51,113	COMPUTER PERIPHERALS ACCESSORIES	409	APPLE	APPLE WATCH SERIES 6 GPS 44MM SPACE GREY ALUMINIUM	239001518	1	409
1	41,798	COMPUTERS	1,187	APPLE	2020 APPLE MACBOOK PRO 13 TOUCH BAR M1 PROCESSOR 8	239246780	1	1,187
2	39,361	COMPUTERS	898	APPLE	2020 APPLE MACBOOK AIR 13 3 RETINA DISPLAY M1 PROC	239246776	1	898
3	38,615	COMPUTER PERIPHERALS ACCESSORIES	379	APPLE	APPLE WATCH SERIES 6 GPS 40MM BLUE ALUMINIUM CASE	239001540	1	379
4	70,262	COMPUTERS	1,899	APPLE	2021 APPLE MACBOOK PRO 14 M1 PRO PROCESSOR 16GB RA	240575990	1	1,899

109,375	42,613	BEDROOM FURNITURE	259	SILENTNIGHT	SILENTNIGHT SLEEP GENIUS FULL HEIGHT HEADBOARD DOU	236938439	1	259
109,376	42,613	OUTDOOR FURNITURE	949	LG OUTDOOR	LG OUTDOOR BERGEN 2-SEAT GARDEN SIDE TABLE RECLINI	239742814	1	949
109,377	43,567	COMPUTERS	1,099	APPLE	2021 APPLE IPAD PRO 12 9 M1 PROCESSOR IOS WI-FI 25	240040978	1	1,099
109,378	43,567	COMPUTERS	2,099	APPLE	2020 APPLE IMAC 27 ALL-IN-ONE INTEL CORE I7 8GB RA	238923518	1	2,099
109,379	68,268	TELEVISIONS HOME CINEMA	799	LG	LG OLED48A16LA 2021 OLED HDR 4K ULTRA HD SMART TV	239866717	1	799

Column	Column name	dtype	Is sorted	Null values	Unique values	Mean	Std	Min	Median	Max
0	basket_ID	Int64DType	False	0 (0.0%)	61241 (56.0%)	3.59e+04	2.24e+04	0	35,203	76,543
1	item	StringDtype	False	0 (0.0%)	166 (0.2%)
2	cash_price	Int64DType	False	0 (0.0%)	1280 (1.2%)	672.	714.	0	499	18,349
3	make	StringDtype	False	1273 (1.2%)	690 (0.6%)
4	model	StringDtype	False	1273 (1.2%)	6477 (5.9%)
5	goods_code	StringDtype	False	0 (0.0%)	10738 (9.8%)
6	Nbr_of_prod_purchas	Int64DType	False	0 (0.0%)	19 (< 0.1%)	1.05	0.426	1	1	40
7	total_price	Int64DType	False	0 (0.0%)	1454 (1.3%)	704.	882.	0	519	71,280

Please enable javascript

.. GENERATED FROM PYTHON SOURCE LINES 129-140 We then build a skrub |TableVectorizer| with different choices of the type of encoder for high-cardinality categorical or string columns, and the number of components it uses. With skrub, there’s no need to specify a separate grid of hyperparameters outside the pipeline. Instead, within a DataOps plan, we can directly replace a parameter’s value using one of skrub’s ``choose_*`` functions, which define the range of values to consider during hyperparameter selection. In this example, we use |choose_int| to select the number of components for the encoder and |choose_from| to select the type of encoder. .. GENERATED FROM PYTHON SOURCE LINES 142-152 .. code-block:: Python n = skrub.choose_int(5, 15, name="n_components") encoder = skrub.choose_from( { "MinHash": skrub.MinHashEncoder(n_components=n), "LSA": skrub.StringEncoder(n_components=n), }, name="encoder", ) vectorizer = skrub.TableVectorizer(high_cardinality=encoder) .. GENERATED FROM PYTHON SOURCE LINES 153-156 We can restrict the vectorizer to a subset of columns: in our case, we want to vectorize all columns except the ``"basket_ID"`` column, which is not a feature but a link to the basket it belongs to. .. GENERATED FROM PYTHON SOURCE LINES 158-162 .. code-block:: Python vectorized_products = products_with_total.skb.apply( vectorizer, exclude_cols="basket_ID" ) .. GENERATED FROM PYTHON SOURCE LINES 163-165 We then aggregate the vectorized products by basket ID, and then merge the result with the baskets table. .. GENERATED FROM PYTHON SOURCE LINES 167-172 .. code-block:: Python aggregated_products = vectorized_products.groupby("basket_ID").agg("mean").reset_index() augmented_baskets = basket_ids.merge( aggregated_products, left_on="ID", right_on="basket_ID" ).drop(columns=["ID", "basket_ID"]) .. GENERATED FROM PYTHON SOURCE LINES 173-175 Finally, we add a supervised estimator, and use |choose_float| to add the learning rate as a hyperparameter to tune. .. GENERATED FROM PYTHON SOURCE LINES 177-185 .. code-block:: Python from sklearn.ensemble import HistGradientBoostingClassifier hgb = HistGradientBoostingClassifier( learning_rate=skrub.choose_float(0.01, 0.9, log=True, name="learning_rate") ) predictions = augmented_baskets.skb.apply(hgb, y=fraud_flags) predictions .. raw:: html

<Apply HistGradientBoostingClassifier>

Show/Hide graph

Result:

	fraud_flag
0	0
1	0
2	0
3	0
4	0

61,236	0
61,237	0
61,238	0
61,239	0
61,240	0

Column	Column name	dtype	Is sorted	Null values	Unique values	Mean	Std	Min	Median	Max
0	fraud_flag	Int64DType	False	0 (0.0%)	2 (< 0.1%)	0.00260	0.0509	0	0	1

Please enable javascript

.. GENERATED FROM PYTHON SOURCE LINES 186-191 And our DataOps plan is complete! We can now use |make_randomized_search| to perform hyperparameter tuning and find the best hyperparameters for our model. Below, we display the hyperparameter combinations that define our search space. .. GENERATED FROM PYTHON SOURCE LINES 193-195 .. code-block:: Python print(predictions.skb.describe_param_grid()) .. rst-class:: sphx-glr-script-out .. code-block:: none - learning_rate: choose_float(0.01, 0.9, log=True, name='learning_rate') encoder: 'MinHash' n_components: choose_int(5, 15, name='n_components') - learning_rate: choose_float(0.01, 0.9, log=True, name='learning_rate') encoder: 'LSA' n_components: choose_int(5, 15, name='n_components') .. GENERATED FROM PYTHON SOURCE LINES 196-198 |make_randomized_search| returns a :class:`~skrub.ParamSearch` object, which contains our search result and some plotting logic. .. GENERATED FROM PYTHON SOURCE LINES 198-203 .. code-block:: Python search = predictions.skb.make_randomized_search( scoring="roc_auc", n_iter=8, n_jobs=4, random_state=0, fitted=True ) search.results_ .. raw:: html

	n_components	encoder	learning_rate	mean_test_score
0	10	LSA	0.0673	0.889
1	9	LSA	0.0381	0.884
2	12	LSA	0.0524	0.883
3	13	MinHash	0.0867	0.880
4	15	MinHash	0.0561	0.880
5	15	LSA	0.0138	0.875
6	13	LSA	0.501	0.731
7	11	MinHash	0.447	0.731

Column	Column name	dtype	Is sorted	Unique values	Mean	Std	Min	Median	Max
0	n_components	Int64DType	False	6 (75.0%)	12.2	2.19	9	13	15
1	encoder	StringDtype	False	2 (25.0%)
2	learning_rate	Float64DType	False	8 (100.0%)	0.158	0.197	0.0138	0.0673	0.501
3	mean_test_score	Float64DType	True	8 (100.0%)	0.844	0.0698	0.731	0.880	0.889

Column 1	Column 2	Cramér's V	Pearson's Correlation
learning_rate	mean_test_score	1.00	-0.987
encoder	mean_test_score	1.00
encoder	learning_rate	1.00
n_components	mean_test_score	1.00	0.0229
n_components	learning_rate	1.00	-0.0708
n_components	encoder	0.683

Please enable javascript

.. GENERATED FROM PYTHON SOURCE LINES 204-205 We can also display the results of the search in a parallel coordinates plot: .. GENERATED FROM PYTHON SOURCE LINES 205-207 .. code-block:: Python search.plot_results() .. raw:: html

.. GENERATED FROM PYTHON SOURCE LINES 208-215 It seems here that using the LSA as an encoder brings better test scores, but at the expense of training and scoring time. We can get the best performing :class:`~skrub.SkrubLearner` via ``best_learner_``, and use it for inference on new data. We load the test split of the credit fraud dataset, and apply the best learner to it to obtain predictions. .. GENERATED FROM PYTHON SOURCE LINES 215-224 .. code-block:: Python new_data = skrub.datasets.fetch_credit_fraud(split="test") new_baskets = pd.read_csv(new_data.baskets_path) new_products = pd.read_csv(new_data.products_path) probabilities = search.best_learner_.predict_proba( {"baskets": new_baskets, "products": new_products} ) .. GENERATED FROM PYTHON SOURCE LINES 225-228 We can evaluate the performance of our model by plotting the ROC curve and calculating the AUC score. We can use the |RocCurveDisplay| from scikit-learn to plot the ROC curve. .. GENERATED FROM PYTHON SOURCE LINES 228-234 .. code-block:: Python import matplotlib.pyplot as plt from sklearn.metrics import RocCurveDisplay RocCurveDisplay.from_predictions(new_baskets["fraud_flag"], probabilities[:, 1]) plt.show() .. image-sg:: /auto_examples/02_data_ops/images/sphx_glr_1120_multiple_tables_001.png :alt: 1120 multiple tables :srcset: /auto_examples/02_data_ops/images/sphx_glr_1120_multiple_tables_001.png :class: sphx-glr-single-img .. GENERATED FROM PYTHON SOURCE LINES 235-247 Conclusion ---------- In this example, we have shown how to build a multi-table machine learning pipeline with skrub DataOps. We have seen how DataOps allow us to use familiar Pandas operations to manipulate dataframes, and how we can build a DataOps plan that works with multiple tables and performs hyperparameter tuning on the resulting pipeline. If you want to learn more about tuning hyperparameters using skrub DataOps, see the :ref:`Tuning Pipelines example ` for an in-depth tutorial. .. rst-class:: sphx-glr-timing **Total running time of the script:** (2 minutes 43.078 seconds) **Estimated memory usage:** 860 MB .. _sphx_glr_download_auto_examples_02_data_ops_1120_multiple_tables.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/02_data_ops/1120_multiple_tables.ipynb :alt: Launch binder :width: 150 px .. container:: lite-badge .. image:: images/jupyterlite_badge_logo.svg :target: ../../lite/lab/index.html?path=auto_examples/02_data_ops/1120_multiple_tables.ipynb :alt: Launch JupyterLite :width: 150 px .. container:: sphx-glr-download sphx-glr-download-jupyter :download:`Download Jupyter notebook: 1120_multiple_tables.ipynb <1120_multiple_tables.ipynb>` .. container:: sphx-glr-download sphx-glr-download-python :download:`Download Python source code: 1120_multiple_tables.py <1120_multiple_tables.py>` .. container:: sphx-glr-download sphx-glr-download-zip :download:`Download zipped: 1120_multiple_tables.zip <1120_multiple_tables.zip>` .. only:: html .. rst-class:: sphx-glr-signature `Gallery generated by Sphinx-Gallery `_

ID

fraud_flag

Please enable javascript

basket_ID

item

cash_price

make

model

goods_code

Nbr_of_prod_purchas

Please enable javascript

basket_ID

item

cash_price

make

model

goods_code

Nbr_of_prod_purchas

total_price

Please enable javascript

fraud_flag

Please enable javascript

n_components

encoder

learning_rate

mean_test_score

n_components

encoder

learning_rate

mean_test_score

Please enable javascript