.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "auto_examples/04_fuzzy_joining_and_FeatureAugmenter.py"
.. LINE NUMBERS ARE GIVEN BELOW.

.. only:: html

    .. note::
        :class: sphx-glr-download-link-note

        :ref:`Go to the end <sphx_glr_download_auto_examples_04_fuzzy_joining_and_FeatureAugmenter.py>`
        to download the full example code or to run this example in your browser via Binder

.. rst-class:: sphx-glr-example-title

.. _sphx_glr_auto_examples_04_fuzzy_joining_and_FeatureAugmenter.py:


Fuzzy joining dirty tables and the FeatureAugmenter
=====================================================

Here we show how to combine data from different sources,
with a vocabulary not well normalized.

Joining is difficult: one entry on one side does not have
an exact match on the other side.

The |fj| function enables to join tables without cleaning the data by
accounting for the label variations.

To illustrate, we will join data from the `2022 World Happiness Report <https://worldhappiness.report/>`_.
with tables provided in `the World Bank open data platform <https://data.worldbank.org/>`_
in order to create a first prediction model.

Moreover, the |fa| is a scikit-learn Transformer that makes it easy to
use such fuzzy joining multiple tables to bring in information in a
machine-learning pipeline. In particular, it enables tuning parameters of
|fj| to find the matches that maximize prediction accuracy.


.. |fj| replace:: :func:`~dirty_cat.fuzzy_join`

.. |fa| replace:: :func:`~dirty_cat.FeatureAugmenter`

.. GENERATED FROM PYTHON SOURCE LINES 30-34

Data Importing and preprocessing
--------------------------------

We import the happiness score table first:

.. GENERATED FROM PYTHON SOURCE LINES 34-42

.. code-block:: default

    import pandas as pd

    df = pd.read_csv(
        "https://raw.githubusercontent.com/dirty-cat/datasets/master/data/Happiness_report_2022.csv",
        thousands=",",
    )
    df.drop(df.tail(1).index, inplace=True)








.. GENERATED FROM PYTHON SOURCE LINES 43-44

Let's look at the table:

.. GENERATED FROM PYTHON SOURCE LINES 44-46

.. code-block:: default

    df.head(3)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>RANK</th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>Whisker-high</th>
          <th>Whisker-low</th>
          <th>Dystopia (1.83) + residual</th>
          <th>Explained by: GDP per capita</th>
          <th>Explained by: Social support</th>
          <th>Explained by: Healthy life expectancy</th>
          <th>Explained by: Freedom to make life choices</th>
          <th>Explained by: Generosity</th>
          <th>Explained by: Perceptions of corruption</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>1</td>
          <td>Finland</td>
          <td>7821.0</td>
          <td>7886.0</td>
          <td>7756.0</td>
          <td>2518.0</td>
          <td>1892.0</td>
          <td>1258.0</td>
          <td>775.0</td>
          <td>736.0</td>
          <td>109.0</td>
          <td>534.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>2</td>
          <td>Denmark</td>
          <td>7636.0</td>
          <td>7710.0</td>
          <td>7563.0</td>
          <td>2226.0</td>
          <td>1953.0</td>
          <td>1243.0</td>
          <td>777.0</td>
          <td>719.0</td>
          <td>188.0</td>
          <td>532.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>3</td>
          <td>Iceland</td>
          <td>7557.0</td>
          <td>7651.0</td>
          <td>7464.0</td>
          <td>2320.0</td>
          <td>1936.0</td>
          <td>1320.0</td>
          <td>803.0</td>
          <td>718.0</td>
          <td>270.0</td>
          <td>191.0</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 47-51

This is a table that contains the happiness index of a country along with
some of the possible explanatory factors: GDP per capita, Social support,
Generosity etc.


.. GENERATED FROM PYTHON SOURCE LINES 53-55

For the sake of this example, we only keep the country names and our
variable of interest: the 'Happiness score'.

.. GENERATED FROM PYTHON SOURCE LINES 55-57

.. code-block:: default

    df = df[["Country", "Happiness score"]]








.. GENERATED FROM PYTHON SOURCE LINES 58-67

Additional tables from other sources
------------------------------------

Now, we need to include explanatory factors from other sources, to
complete our covariates (X table).

Interesting tables can be found on `the World Bank open data platform
<https://data.worldbank.org/>`_, for which we have a downloading
function:

.. GENERATED FROM PYTHON SOURCE LINES 67-69

.. code-block:: default

    from dirty_cat.datasets import fetch_world_bank_indicator








.. GENERATED FROM PYTHON SOURCE LINES 70-71

We extract the table containing GDP per capita by country:

.. GENERATED FROM PYTHON SOURCE LINES 71-74

.. code-block:: default

    gdppc = fetch_world_bank_indicator(indicator_id="NY.GDP.PCAP.CD").X
    gdppc.head(3)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country Name</th>
          <th>GDP per capita (current US$)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>Aruba</td>
          <td>29342.100858</td>
        </tr>
        <tr>
          <th>1</th>
          <td>Africa Eastern and Southern</td>
          <td>1549.772730</td>
        </tr>
        <tr>
          <th>2</th>
          <td>Afghanistan</td>
          <td>368.754614</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 75-76

Then another table, with life expectancy by country:

.. GENERATED FROM PYTHON SOURCE LINES 76-79

.. code-block:: default

    life_exp = fetch_world_bank_indicator("SP.DYN.LE00.IN", "life_exp").X
    life_exp.head(3)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country Name</th>
          <th>Life expectancy at birth, total (years)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>Aruba</td>
          <td>75.723000</td>
        </tr>
        <tr>
          <th>1</th>
          <td>Africa Eastern and Southern</td>
          <td>63.313856</td>
        </tr>
        <tr>
          <th>2</th>
          <td>Afghanistan</td>
          <td>62.575000</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 80-81

And a table with legal rights strength by country:

.. GENERATED FROM PYTHON SOURCE LINES 81-84

.. code-block:: default

    legal_rights = fetch_world_bank_indicator("IC.LGL.CRED.XQ").X
    legal_rights.head(3)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country Name</th>
          <th>Strength of legal rights index (0=weak to 12=strong)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>Africa Eastern and Southern</td>
          <td>4.538462</td>
        </tr>
        <tr>
          <th>1</th>
          <td>Afghanistan</td>
          <td>10.000000</td>
        </tr>
        <tr>
          <th>2</th>
          <td>Africa Western and Central</td>
          <td>5.863636</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 85-91

A correspondance problem
------------------------

Alas, the entries for countries do not perfectly match between our
original table (df), and those that we downloaded from the worldbank
(gdppc):

.. GENERATED FROM PYTHON SOURCE LINES 91-94

.. code-block:: default


    df.sort_values(by="Country").tail(7)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>29</th>
          <td>Uruguay</td>
          <td>6474.0</td>
        </tr>
        <tr>
          <th>52</th>
          <td>Uzbekistan</td>
          <td>6063.0</td>
        </tr>
        <tr>
          <th>107</th>
          <td>Venezuela</td>
          <td>4925.0</td>
        </tr>
        <tr>
          <th>76</th>
          <td>Vietnam</td>
          <td>5485.0</td>
        </tr>
        <tr>
          <th>131</th>
          <td>Yemen*</td>
          <td>4197.0</td>
        </tr>
        <tr>
          <th>136</th>
          <td>Zambia</td>
          <td>3760.0</td>
        </tr>
        <tr>
          <th>143</th>
          <td>Zimbabwe</td>
          <td>2995.0</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 95-97

.. code-block:: default

    gdppc.sort_values(by="Country Name").tail(7)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country Name</th>
          <th>GDP per capita (current US$)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>253</th>
          <td>Vietnam</td>
          <td>3756.489121</td>
        </tr>
        <tr>
          <th>252</th>
          <td>Virgin Islands (U.S.)</td>
          <td>39552.168595</td>
        </tr>
        <tr>
          <th>193</th>
          <td>West Bank and Gaza</td>
          <td>3663.969055</td>
        </tr>
        <tr>
          <th>255</th>
          <td>World</td>
          <td>12236.615743</td>
        </tr>
        <tr>
          <th>258</th>
          <td>Yemen, Rep.</td>
          <td>701.714878</td>
        </tr>
        <tr>
          <th>260</th>
          <td>Zambia</td>
          <td>1137.343633</td>
        </tr>
        <tr>
          <th>261</th>
          <td>Zimbabwe</td>
          <td>1773.920411</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 98-103

We can see that Yemen is written "Yemen*" on one side, and
"Yemen, Rep." on the other.

We also have entries that probably do not have correspondances: "World"
on one side, whereas the other table only has country-level data.

.. GENERATED FROM PYTHON SOURCE LINES 105-111

Joining tables with imperfect correspondance
--------------------------------------------

We will now join our initial table, df, with the 3 additional ones that
we have extracted.


.. GENERATED FROM PYTHON SOURCE LINES 113-119

.. _example_fuzzy_join:

1. Joining GDP per capita table
...............................

To join them with dirty_cat, we only need to do the following:

.. GENERATED FROM PYTHON SOURCE LINES 119-137

.. code-block:: default

    from dirty_cat import fuzzy_join

    # We will ignore the warnings:
    import warnings

    warnings.filterwarnings("ignore")

    df1 = fuzzy_join(
        df,  # our table to join
        gdppc,  # the table to join with
        left_on="Country",  # the first join key column
        right_on="Country Name",  # the second join key column
        return_score=True,
    )

    df1.tail(20)
    # We merged the first WB table to our initial one.






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>Country Name</th>
          <th>GDP per capita (current US$)</th>
          <th>matching_score</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>126</th>
          <td>Sri Lanka</td>
          <td>4362.0</td>
          <td>Sri Lanka</td>
          <td>4013.687657</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>127</th>
          <td>Madagascar*</td>
          <td>4339.0</td>
          <td>Madagascar</td>
          <td>500.511032</td>
          <td>0.779244</td>
        </tr>
        <tr>
          <th>128</th>
          <td>Egypt</td>
          <td>4288.0</td>
          <td>Egypt, Arab Rep.</td>
          <td>3698.834981</td>
          <td>0.627379</td>
        </tr>
        <tr>
          <th>129</th>
          <td>Chad*</td>
          <td>4251.0</td>
          <td>Chad</td>
          <td>685.690284</td>
          <td>0.658960</td>
        </tr>
        <tr>
          <th>130</th>
          <td>Ethiopia</td>
          <td>4241.0</td>
          <td>Ethiopia</td>
          <td>925.077428</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>131</th>
          <td>Yemen*</td>
          <td>4197.0</td>
          <td>Yemen, Rep.</td>
          <td>701.714878</td>
          <td>0.626980</td>
        </tr>
        <tr>
          <th>132</th>
          <td>Mauritania*</td>
          <td>4153.0</td>
          <td>Mauritania</td>
          <td>2166.046799</td>
          <td>0.796150</td>
        </tr>
        <tr>
          <th>133</th>
          <td>Jordan</td>
          <td>4152.0</td>
          <td>Jordan</td>
          <td>4103.258966</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>134</th>
          <td>Togo</td>
          <td>4112.0</td>
          <td>Togo</td>
          <td>973.206129</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>135</th>
          <td>India</td>
          <td>3777.0</td>
          <td>India</td>
          <td>2256.590409</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>136</th>
          <td>Zambia</td>
          <td>3760.0</td>
          <td>Zambia</td>
          <td>1137.343633</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>137</th>
          <td>Malawi</td>
          <td>3750.0</td>
          <td>Malawi</td>
          <td>634.835660</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>138</th>
          <td>Tanzania</td>
          <td>3702.0</td>
          <td>Tanzania</td>
          <td>1099.287598</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>139</th>
          <td>Sierra Leone</td>
          <td>3574.0</td>
          <td>Sierra Leone</td>
          <td>480.039211</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>140</th>
          <td>Lesotho*</td>
          <td>3512.0</td>
          <td>Lesotho</td>
          <td>1094.098185</td>
          <td>0.736366</td>
        </tr>
        <tr>
          <th>141</th>
          <td>Botswana*</td>
          <td>3471.0</td>
          <td>Botswana</td>
          <td>6805.221274</td>
          <td>0.780092</td>
        </tr>
        <tr>
          <th>142</th>
          <td>Rwanda*</td>
          <td>3268.0</td>
          <td>Rwanda</td>
          <td>822.347989</td>
          <td>0.735689</td>
        </tr>
        <tr>
          <th>143</th>
          <td>Zimbabwe</td>
          <td>2995.0</td>
          <td>Zimbabwe</td>
          <td>1773.920411</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>144</th>
          <td>Lebanon</td>
          <td>2955.0</td>
          <td>Lebanon</td>
          <td>4136.146347</td>
          <td>1.000000</td>
        </tr>
        <tr>
          <th>145</th>
          <td>Afghanistan</td>
          <td>2404.0</td>
          <td>Afghanistan</td>
          <td>368.754614</td>
          <td>1.000000</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 138-142

.. topic:: Note:

   We fix the ``return_score`` parameter to `True` so as to keep the matching
   score, that we will use later to show what are the worst matches.

.. GENERATED FROM PYTHON SOURCE LINES 144-160

We see that our |fj| succesfully identified the countries,
even though some country names differ between tables.

For instance, "Czechia" is well identified as "Czech Republic" and
"Luxembourg*" as "Luxembourg".

.. topic:: Note:

   This would all be missed out if we were using other methods such as
   `pandas.merge <https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.merge.html>`_,
   which can only find exact matches.
   In this case, to reach the best result, we would have to `manually` clean
   the data (e.g. remove the * after country name) and look
   for matching patterns in every observation.

Let's do some more inspection of the merging done.

.. GENERATED FROM PYTHON SOURCE LINES 163-165

Let's print the four worst matches, which will give
us an overview of the situation:

.. GENERATED FROM PYTHON SOURCE LINES 165-168

.. code-block:: default


    df1.sort_values("matching_score").head(4)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>Country Name</th>
          <th>GDP per capita (current US$)</th>
          <th>matching_score</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>87</th>
          <td>Ivory Coast</td>
          <td>5235.0</td>
          <td>East Asia &amp; Pacific</td>
          <td>13041.781186</td>
          <td>0.500000</td>
        </tr>
        <tr>
          <th>121</th>
          <td>Palestinian Territories*</td>
          <td>4483.0</td>
          <td>Palau</td>
          <td>12083.888149</td>
          <td>0.501106</td>
        </tr>
        <tr>
          <th>94</th>
          <td>Laos</td>
          <td>5140.0</td>
          <td>Lao PDR</td>
          <td>2535.623432</td>
          <td>0.562278</td>
        </tr>
        <tr>
          <th>111</th>
          <td>Turkey</td>
          <td>4744.0</td>
          <td>Turkiye</td>
          <td>9661.235975</td>
          <td>0.567053</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 169-172

We see that some matches were unsuccesful
(e.g "Palestinian Territories*" and "Palau"),
because there is simply no match in the two tables.

.. GENERATED FROM PYTHON SOURCE LINES 174-177

In this case, it is better to use the threshold parameter
so as to include only precise-enough matches:


.. GENERATED FROM PYTHON SOURCE LINES 177-187

.. code-block:: default

    df1 = fuzzy_join(
        df,
        gdppc,
        left_on="Country",
        right_on="Country Name",
        match_score=0.35,
        return_score=True,
    )
    df1.sort_values("matching_score").head(4)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>Country Name</th>
          <th>GDP per capita (current US$)</th>
          <th>matching_score</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>87</th>
          <td>Ivory Coast</td>
          <td>5235.0</td>
          <td>East Asia &amp; Pacific</td>
          <td>13041.781186</td>
          <td>0.500000</td>
        </tr>
        <tr>
          <th>121</th>
          <td>Palestinian Territories*</td>
          <td>4483.0</td>
          <td>Palau</td>
          <td>12083.888149</td>
          <td>0.501106</td>
        </tr>
        <tr>
          <th>94</th>
          <td>Laos</td>
          <td>5140.0</td>
          <td>Lao PDR</td>
          <td>2535.623432</td>
          <td>0.562278</td>
        </tr>
        <tr>
          <th>111</th>
          <td>Turkey</td>
          <td>4744.0</td>
          <td>Turkiye</td>
          <td>9661.235975</td>
          <td>0.567053</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 188-190

Matches that are not available (or precise enough) are marked as `NaN`.
We will remove them using the drop_unmatched parameter:

.. GENERATED FROM PYTHON SOURCE LINES 190-202

.. code-block:: default


    df1 = fuzzy_join(
        df,
        gdppc,
        left_on="Country",
        right_on="Country Name",
        match_score=0.35,
        drop_unmatched=True,
    )

    df1.drop(columns=["Country Name"], inplace=True)








.. GENERATED FROM PYTHON SOURCE LINES 203-205

We can finally plot and look at the link between GDP per capital
and happiness:

.. GENERATED FROM PYTHON SOURCE LINES 205-222

.. code-block:: default

    import matplotlib.pyplot as plt
    import seaborn as sns

    sns.set_context("notebook")

    plt.figure(figsize=(4, 3))
    ax = sns.regplot(
        data=df1,
        x="GDP per capita (current US$)",
        y="Happiness score",
        lowess=True,
    )
    ax.set_ylabel("Happiness index")
    ax.set_title("Is a higher GDP per capita linked to happiness?")
    plt.tight_layout()
    plt.show()




.. image-sg:: /auto_examples/images/sphx_glr_04_fuzzy_joining_and_FeatureAugmenter_001.png
   :alt: Is a higher GDP per capita linked to happiness?
   :srcset: /auto_examples/images/sphx_glr_04_fuzzy_joining_and_FeatureAugmenter_001.png
   :class: sphx-glr-single-img





.. GENERATED FROM PYTHON SOURCE LINES 223-227

It seems that the happiest countries are those
having a high GDP per capita.
However, unhappy countries do not have only low levels
of GDP per capita. We have to search for other patterns.

.. GENERATED FROM PYTHON SOURCE LINES 229-234

2. Joining life expectancy table
................................

Now let's include other information that may be relevant, such as in the
life_exp table:

.. GENERATED FROM PYTHON SOURCE LINES 234-246

.. code-block:: default

    df2 = fuzzy_join(
        df1,
        life_exp,
        left_on="Country",
        right_on="Country Name",
        match_score=0.45,
    )

    df2.drop(columns=["Country Name"], inplace=True)

    df2.head(3)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>GDP per capita (current US$)</th>
          <th>Life expectancy at birth, total (years)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>Finland</td>
          <td>7821.0</td>
          <td>53654.750296</td>
          <td>82.131707</td>
        </tr>
        <tr>
          <th>1</th>
          <td>Denmark</td>
          <td>7636.0</td>
          <td>68007.756673</td>
          <td>81.551220</td>
        </tr>
        <tr>
          <th>2</th>
          <td>Iceland</td>
          <td>7557.0</td>
          <td>68727.636665</td>
          <td>83.065854</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 247-248

Let's plot this relation:

.. GENERATED FROM PYTHON SOURCE LINES 248-260

.. code-block:: default

    plt.figure(figsize=(4, 3))
    fig = sns.regplot(
        data=df2,
        x="Life expectancy at birth, total (years)",
        y="Happiness score",
        lowess=True,
    )
    fig.set_ylabel("Happiness index")
    fig.set_title("Is a higher life expectancy linked to happiness?")
    plt.tight_layout()
    plt.show()




.. image-sg:: /auto_examples/images/sphx_glr_04_fuzzy_joining_and_FeatureAugmenter_002.png
   :alt: Is a higher life expectancy linked to happiness?
   :srcset: /auto_examples/images/sphx_glr_04_fuzzy_joining_and_FeatureAugmenter_002.png
   :class: sphx-glr-single-img





.. GENERATED FROM PYTHON SOURCE LINES 261-263

It seems the answer is yes!
Countries with higher life expectancy are also happier.

.. GENERATED FROM PYTHON SOURCE LINES 266-270

3. Joining legal rights strength table
......................................

And the table with a measure of legal rights strength in the country:

.. GENERATED FROM PYTHON SOURCE LINES 270-282

.. code-block:: default

    df3 = fuzzy_join(
        df2,
        legal_rights,
        left_on="Country",
        right_on="Country Name",
        match_score=0.45,
    )

    df3.drop(columns=["Country Name"], inplace=True)

    df3.head(3)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>GDP per capita (current US$)</th>
          <th>Life expectancy at birth, total (years)</th>
          <th>Strength of legal rights index (0=weak to 12=strong)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>Finland</td>
          <td>7821.0</td>
          <td>53654.750296</td>
          <td>82.131707</td>
          <td>6.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>Denmark</td>
          <td>7636.0</td>
          <td>68007.756673</td>
          <td>81.551220</td>
          <td>8.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>Iceland</td>
          <td>7557.0</td>
          <td>68727.636665</td>
          <td>83.065854</td>
          <td>4.0</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 283-284

Let's take a look at their correspondance in a figure:

.. GENERATED FROM PYTHON SOURCE LINES 284-296

.. code-block:: default

    plt.figure(figsize=(4, 3))
    fig = sns.regplot(
        data=df3,
        x="Strength of legal rights index (0=weak to 12=strong)",
        y="Happiness score",
        lowess=True,
    )
    fig.set_ylabel("Happiness index")
    fig.set_title("Does a country's legal rights strength lead to happiness?")
    plt.tight_layout()
    plt.show()




.. image-sg:: /auto_examples/images/sphx_glr_04_fuzzy_joining_and_FeatureAugmenter_003.png
   :alt: Does a country's legal rights strength lead to happiness?
   :srcset: /auto_examples/images/sphx_glr_04_fuzzy_joining_and_FeatureAugmenter_003.png
   :class: sphx-glr-single-img





.. GENERATED FROM PYTHON SOURCE LINES 297-299

From this plot, it is not clear that this measure of legal strength
is linked to happiness.

.. GENERATED FROM PYTHON SOURCE LINES 301-303

Great! Our joined table has become bigger and full of useful information.
And now we are ready to apply a first machine learning model to it!

.. GENERATED FROM PYTHON SOURCE LINES 305-310

Prediction model
----------------

We now separate our covariates (X), from the target (or exogenous)
variables: y

.. GENERATED FROM PYTHON SOURCE LINES 310-313

.. code-block:: default

    X = df3.drop("Happiness score", axis=1).select_dtypes(exclude=object)
    y = df3[["Happiness score"]]








.. GENERATED FROM PYTHON SOURCE LINES 314-315

Let us now define the model that will be used to predict the happiness score:

.. GENERATED FROM PYTHON SOURCE LINES 315-325

.. code-block:: default

    from sklearn import __version__ as sklearn_version

    if sklearn_version < "1.0":
        from sklearn.experimental import enable_hist_gradient_boosting
    from sklearn.ensemble import HistGradientBoostingRegressor
    from sklearn.model_selection import KFold

    hgdb = HistGradientBoostingRegressor(random_state=0)
    cv = KFold(n_splits=2, shuffle=True, random_state=0)








.. GENERATED FROM PYTHON SOURCE LINES 326-330

To evaluate our model, we will apply a `4-fold cross-validation`.
We evaluate our model using the `R2` score.

Let's finally assess the results of our models:

.. GENERATED FROM PYTHON SOURCE LINES 330-338

.. code-block:: default

    from sklearn.model_selection import cross_validate

    cv_results_t = cross_validate(hgdb, X, y, cv=cv, scoring="r2")

    cv_r2_t = cv_results_t["test_score"]

    print(f"Mean R2 score is {cv_r2_t.mean():.2f} +- {cv_r2_t.std():.2f}")





.. rst-class:: sphx-glr-script-out

 .. code-block:: none

    Mean R2 score is 0.63 +- 0.03




.. GENERATED FROM PYTHON SOURCE LINES 339-350

We have a satisfying first result: an R2 of 0.66!

Data cleaning varies from dataset to dataset: there are as
many ways to clean a table as there are errors. |fj|
method is generalizable across all datasets.

Data transformation is also often very costly in both time and ressources.
|fj| is fast and easy-to-use.

Now up to you, try improving our model by adding information into it and
beating our result!

.. GENERATED FROM PYTHON SOURCE LINES 352-359

Using the |fa| to fuzzy join multiple tables
--------------------------------------------
A faster way to merge different tables from the World Bank
to `X` is to use the |fa|.

The |fa| is a transformer that can easily chain joins of tables on
a main table.

.. GENERATED FROM PYTHON SOURCE LINES 361-365

.. _example_feature_augmenter:

Instantiating the transformer
.............................

.. GENERATED FROM PYTHON SOURCE LINES 365-367

.. code-block:: default


    y = df["Happiness score"]







.. GENERATED FROM PYTHON SOURCE LINES 368-371

We gather the auxilliary tables into a
list of (tables, keys) for the `tables` parameter.
An instance of the transformer with the necessary information is:

.. GENERATED FROM PYTHON SOURCE LINES 371-382

.. code-block:: default

    from dirty_cat import FeatureAugmenter

    fa = FeatureAugmenter(
        tables=[
            (gdppc, "Country Name"),
            (life_exp, "Country Name"),
            (legal_rights, "Country Name"),
        ],
        main_key="Country",
    )








.. GENERATED FROM PYTHON SOURCE LINES 383-387

Fitting and transforming into the final table
.............................................
To get our final joined table we will fit and transform the main table (df)
with our create instance of the |fa|:

.. GENERATED FROM PYTHON SOURCE LINES 387-391

.. code-block:: default

    df_final = fa.fit_transform(df)

    df_final.head(10)






.. raw:: html

    <div class="output_subarea output_html rendered_html output_result">
    <div>
    <style scoped>
        .dataframe tbody tr th:only-of-type {
            vertical-align: middle;
        }

        .dataframe tbody tr th {
            vertical-align: top;
        }

        .dataframe thead th {
            text-align: right;
        }
    </style>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>Country</th>
          <th>Happiness score</th>
          <th>Country Name</th>
          <th>GDP per capita (current US$)</th>
          <th>Country Name_aux</th>
          <th>Life expectancy at birth, total (years)</th>
          <th>Country Name_aux</th>
          <th>Strength of legal rights index (0=weak to 12=strong)</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>Finland</td>
          <td>7821.0</td>
          <td>Finland</td>
          <td>53654.750296</td>
          <td>Finland</td>
          <td>82.131707</td>
          <td>Finland</td>
          <td>6.0</td>
        </tr>
        <tr>
          <th>1</th>
          <td>Denmark</td>
          <td>7636.0</td>
          <td>Denmark</td>
          <td>68007.756673</td>
          <td>Denmark</td>
          <td>81.551220</td>
          <td>Denmark</td>
          <td>8.0</td>
        </tr>
        <tr>
          <th>2</th>
          <td>Iceland</td>
          <td>7557.0</td>
          <td>Iceland</td>
          <td>68727.636665</td>
          <td>Iceland</td>
          <td>83.065854</td>
          <td>Iceland</td>
          <td>4.0</td>
        </tr>
        <tr>
          <th>3</th>
          <td>Switzerland</td>
          <td>7512.0</td>
          <td>Switzerland</td>
          <td>91991.600458</td>
          <td>Switzerland</td>
          <td>83.100000</td>
          <td>Switzerland</td>
          <td>6.0</td>
        </tr>
        <tr>
          <th>4</th>
          <td>Netherlands</td>
          <td>7415.0</td>
          <td>Netherlands</td>
          <td>57767.878811</td>
          <td>Netherlands</td>
          <td>81.409756</td>
          <td>Netherlands</td>
          <td>2.0</td>
        </tr>
        <tr>
          <th>5</th>
          <td>Luxembourg*</td>
          <td>7404.0</td>
          <td>Luxembourg</td>
          <td>133590.146976</td>
          <td>Luxembourg</td>
          <td>81.741463</td>
          <td>Luxembourg</td>
          <td>3.0</td>
        </tr>
        <tr>
          <th>6</th>
          <td>Sweden</td>
          <td>7384.0</td>
          <td>Sweden</td>
          <td>61028.738060</td>
          <td>Sweden</td>
          <td>82.407317</td>
          <td>Sweden</td>
          <td>7.0</td>
        </tr>
        <tr>
          <th>7</th>
          <td>Norway</td>
          <td>7365.0</td>
          <td>Norway</td>
          <td>89154.276093</td>
          <td>Norway</td>
          <td>83.209756</td>
          <td>Norway</td>
          <td>5.0</td>
        </tr>
        <tr>
          <th>8</th>
          <td>Israel</td>
          <td>7364.0</td>
          <td>Israel</td>
          <td>52170.711862</td>
          <td>Israel</td>
          <td>82.700000</td>
          <td>Israel</td>
          <td>6.0</td>
        </tr>
        <tr>
          <th>9</th>
          <td>New Zealand</td>
          <td>7200.0</td>
          <td>New Zealand</td>
          <td>48781.026633</td>
          <td>New Zealand</td>
          <td>82.056098</td>
          <td>New Zealand</td>
          <td>12.0</td>
        </tr>
      </tbody>
    </table>
    </div>
    </div>
    <br />
    <br />

.. GENERATED FROM PYTHON SOURCE LINES 392-395

And that's it! As previously, we now have a big table
ready for machine learning.
Let's create our machine learning pipeline:

.. GENERATED FROM PYTHON SOURCE LINES 395-413

.. code-block:: default

    from sklearn.pipeline import make_pipeline
    from sklearn.compose import make_column_transformer

    # We include only the columns that will be pertinent for our regression:
    encoder = make_column_transformer(
        (
            "passthrough",
            [
                "GDP per capita (current US$)",
                "Life expectancy at birth, total (years)",
                "Strength of legal rights index (0=weak to 12=strong)",
            ],
        ),
        remainder="drop",
    )

    pipeline = make_pipeline(fa, encoder, HistGradientBoostingRegressor())








.. GENERATED FROM PYTHON SOURCE LINES 414-417

And the best part is that we are now able to evaluate the parameters of the |fj|.
For instance, the ``match_score`` was manually picked and can now be
introduced into a grid search:

.. GENERATED FROM PYTHON SOURCE LINES 417-427

.. code-block:: default


    from sklearn.model_selection import GridSearchCV

    # We will test four possible values of match_score:
    params = {"featureaugmenter__match_score": [0.2, 0.3, 0.4, 0.5]}

    grid = GridSearchCV(pipeline, param_grid=params)
    grid.fit(df, y)

    print(grid.best_params_)




.. rst-class:: sphx-glr-script-out

 .. code-block:: none

    {'featureaugmenter__match_score': 0.2}




.. GENERATED FROM PYTHON SOURCE LINES 428-431

The grid searching gave us the best value of 0.5 for the parameter
``match_score``. Let's use this value in our regression:


.. GENERATED FROM PYTHON SOURCE LINES 431-434

.. code-block:: default


    print(f"Mean R2 score with pipeline is {grid.score(df, y):.2f}")





.. rst-class:: sphx-glr-script-out

 .. code-block:: none

    Mean R2 score with pipeline is 0.83




.. GENERATED FROM PYTHON SOURCE LINES 435-446

.. topic:: Note:

   Here, ``grid.score()`` takes directly the best model
   (with ``match_score=0.5``) that was found during the grid search.
   Thus, it is equivalent to fixing the ``match_score`` to 0.5 and
   refitting the pipeline on the data.


Great, by evaluating the correct ``match_score`` we improved our
results significantly!



.. rst-class:: sphx-glr-timing

   **Total running time of the script:** ( 0 minutes  14.332 seconds)


.. _sphx_glr_download_auto_examples_04_fuzzy_joining_and_FeatureAugmenter.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/dirty-cat/dirty-cat/0.4.1?urlpath=lab/tree/notebooks/auto_examples/04_fuzzy_joining_and_FeatureAugmenter.ipynb
        :alt: Launch binder
        :width: 150 px



    .. container:: sphx-glr-download sphx-glr-download-python

      :download:`Download Python source code: 04_fuzzy_joining_and_FeatureAugmenter.py <04_fuzzy_joining_and_FeatureAugmenter.py>`

    .. container:: sphx-glr-download sphx-glr-download-jupyter

      :download:`Download Jupyter notebook: 04_fuzzy_joining_and_FeatureAugmenter.ipynb <04_fuzzy_joining_and_FeatureAugmenter.ipynb>`


.. only:: html

 .. rst-class:: sphx-glr-signature

    `Gallery generated by Sphinx-Gallery <https://sphinx-gallery.github.io>`_