{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Deduplicating data\n", "\n", "In this notebook, we deduplicate data using the [Dedupe](https://docs.dedupe.io/en/latest/) library, which uses a flat neural network to learn from a little training.\n", "\n", "
\n", "\n", "**See also:**\n", "\n", "* [csvdedupe](https://github.com/dedupeio/csvdedupe) offers a command line interface for Dedupe.\n", "
\n", "\n", "In addition, the same developers have created [parserator](https://github.com/datamade/parserator), which you can use to extract text functions and train your own text extraction." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Load sample data" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "customers = pd.read_csv(\n", " \"https://raw.githubusercontent.com/kjam/data-cleaning-101/master/data/customer_data_duped.csv\",\n", " encoding=\"utf-8\",\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Deduplicate with pandas" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namejobcompanystreet_addresscitystateemailuser_name
0Patricia SchaeferProgrammer, systemsEstrada-Best398 Paul DriveChristianviewDelawarelambdavid@gmail.comndavidson
1Olivie DuboisIngénieur recherche et développement en agroal...Morenorue Lucas BenardSaint Anastasie-les-BainsARberthelotjacqueline@mahe.frmanonallain
2Mary Davies-KirkPublic affairs consultantBaker LtdFlat 3\\nPugh mewsStanleyfurtZAmiddletonconor@hotmail.comcolemanmichael
3Miroslawa EckbauerDispensing opticianLadeck GmbHMijo-Lübs-Straße 12NeubrandenburgBerlinsophia01@yahoo.deromanjunitz
4Richard BauerAccountant, chartered certifiedHoffman-Rocha6541 Rodriguez WallCarlosmouthTexastross@jensen-ware.orgadam78
...........................
2075Maurice SteySystems developerLinke Margraf GmbH & Co. OHGLaila-Scheibe-Allee 2/0LuckenwaldeHamburggutknechtevelyn@niemeier.comdkreusel
2076Linda AlexanderCommrcil horiculuriWebb, Ballald and Vasquel5594 Persn CiffMooneyburyMarylandahleythoa@ail.cokennethrchn
2077Diane BaillyPharmacienVoisin527, rue DijouxDuval-les-BainsCHaruiz@reynaud.frdorothee41
2078Jorge Riba CerdánHotel managerAmador-DiegoRambla de Adriana Barceló 854 Puerta 3HuescaAsturiasmanuelamosquera@yahoo.comeugenia17
2079Ryan ThompsonBrewing technologistSmith-Sullivan136 Rodriguez PointBradfordboroughNorth Dakotalcruz@gmail.comcnewton
\n", "

2080 rows × 8 columns

\n", "
" ], "text/plain": [ " name job \\\n", "0 Patricia Schaefer Programmer, systems \n", "1 Olivie Dubois Ingénieur recherche et développement en agroal... \n", "2 Mary Davies-Kirk Public affairs consultant \n", "3 Miroslawa Eckbauer Dispensing optician \n", "4 Richard Bauer Accountant, chartered certified \n", "... ... ... \n", "2075 Maurice Stey Systems developer \n", "2076 Linda Alexander Commrcil horiculuri \n", "2077 Diane Bailly Pharmacien \n", "2078 Jorge Riba Cerdán Hotel manager \n", "2079 Ryan Thompson Brewing technologist \n", "\n", " company street_address \\\n", "0 Estrada-Best 398 Paul Drive \n", "1 Moreno rue Lucas Benard \n", "2 Baker Ltd Flat 3\\nPugh mews \n", "3 Ladeck GmbH Mijo-Lübs-Straße 12 \n", "4 Hoffman-Rocha 6541 Rodriguez Wall \n", "... ... ... \n", "2075 Linke Margraf GmbH & Co. OHG Laila-Scheibe-Allee 2/0 \n", "2076 Webb, Ballald and Vasquel 5594 Persn Ciff \n", "2077 Voisin 527, rue Dijoux \n", "2078 Amador-Diego Rambla de Adriana Barceló 854 Puerta 3 \n", "2079 Smith-Sullivan 136 Rodriguez Point \n", "\n", " city state email \\\n", "0 Christianview Delaware lambdavid@gmail.com \n", "1 Saint Anastasie-les-Bains AR berthelotjacqueline@mahe.fr \n", "2 Stanleyfurt ZA middletonconor@hotmail.com \n", "3 Neubrandenburg Berlin sophia01@yahoo.de \n", "4 Carlosmouth Texas tross@jensen-ware.org \n", "... ... ... ... \n", "2075 Luckenwalde Hamburg gutknechtevelyn@niemeier.com \n", "2076 Mooneybury Maryland ahleythoa@ail.co \n", "2077 Duval-les-Bains CH aruiz@reynaud.fr \n", "2078 Huesca Asturias manuelamosquera@yahoo.com \n", "2079 Bradfordborough North Dakota lcruz@gmail.com \n", "\n", " user_name \n", "0 ndavidson \n", "1 manonallain \n", "2 colemanmichael \n", "3 romanjunitz \n", "4 adam78 \n", "... ... \n", "2075 dkreusel \n", "2076 kennethrchn \n", "2077 dorothee41 \n", "2078 eugenia17 \n", "2079 cnewton \n", "\n", "[2080 rows x 8 columns]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.2 Show data types\n", "\n", "For this we use [pandas.DataFrame.dtypes](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.dtypes.html):" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "name object\n", "job object\n", "company object\n", "street_address object\n", "city object\n", "state object\n", "email object\n", "user_name object\n", "dtype: object" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers.dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.3 Determine missing values\n", "\n", "[pandas.isnull](https://pandas.pydata.org/docs/reference/api/pandas.isnull.html) shows for an array-like object whether values are missing:\n", "\n", "* `NaN` in numeric arrays\n", "* `None` or `NaN` in object arrays\n", "* `NaT` in datetimelike\n", "\n", "
\n", "\n", "**See also:**\n", "\n", "* [notna](https://pandas.pydata.org/docs/reference/api/pandas.notna.html) for the boolean inverse of [pandas.isna](https://pandas.pydata.org/docs/reference/api/pandas.isna.html)\n", "* [Series.isna](https://pandas.pydata.org/docs/reference/api/pandas.Series.isna.html) for the missing values in a series\n", "* [DataFrame.isna](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isna.html) for the missing values in a DataFrame\n", "* [Index.isna](https://pandas.pydata.org/docs/reference/api/pandas.Index.isna.html) for the missing values in an index\n", "
" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "name 0\n", "job 0\n", "company 0\n", "street_address 0\n", "city 0\n", "state 0\n", "email 0\n", "user_name 0\n" ] } ], "source": [ "for col in customers.columns:\n", " print(col, customers[col].isnull().sum())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.4 Determine duplicate records" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 False\n", "1 False\n", "2 False\n", "3 False\n", "4 False\n", " ... \n", "2075 False\n", "2076 False\n", "2077 False\n", "2078 False\n", "2079 False\n", "Length: 2080, dtype: bool" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers.duplicated()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`customers.duplicated()` does not yet give us the desired indication of whether there are duplicate records. In the following, we will output all data records for which `True` is returned:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namejobcompanystreet_addresscitystateemailuser_name
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [name, job, company, street_address, city, state, email, user_name]\n", "Index: []" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers[customers.duplicated()]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Apparently there are no duplicated records." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2.5 Delete duplicated data\n", "\n", "Deleting duplicated records with `drop_duplicates` should therefore not change anything and leave the number of records at 2080:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namejobcompanystreet_addresscitystateemailuser_name
0Patricia SchaeferProgrammer, systemsEstrada-Best398 Paul DriveChristianviewDelawarelambdavid@gmail.comndavidson
1Olivie DuboisIngénieur recherche et développement en agroal...Morenorue Lucas BenardSaint Anastasie-les-BainsARberthelotjacqueline@mahe.frmanonallain
2Mary Davies-KirkPublic affairs consultantBaker LtdFlat 3\\nPugh mewsStanleyfurtZAmiddletonconor@hotmail.comcolemanmichael
3Miroslawa EckbauerDispensing opticianLadeck GmbHMijo-Lübs-Straße 12NeubrandenburgBerlinsophia01@yahoo.deromanjunitz
4Richard BauerAccountant, chartered certifiedHoffman-Rocha6541 Rodriguez WallCarlosmouthTexastross@jensen-ware.orgadam78
...........................
2075Maurice SteySystems developerLinke Margraf GmbH & Co. OHGLaila-Scheibe-Allee 2/0LuckenwaldeHamburggutknechtevelyn@niemeier.comdkreusel
2076Linda AlexanderCommrcil horiculuriWebb, Ballald and Vasquel5594 Persn CiffMooneyburyMarylandahleythoa@ail.cokennethrchn
2077Diane BaillyPharmacienVoisin527, rue DijouxDuval-les-BainsCHaruiz@reynaud.frdorothee41
2078Jorge Riba CerdánHotel managerAmador-DiegoRambla de Adriana Barceló 854 Puerta 3HuescaAsturiasmanuelamosquera@yahoo.comeugenia17
2079Ryan ThompsonBrewing technologistSmith-Sullivan136 Rodriguez PointBradfordboroughNorth Dakotalcruz@gmail.comcnewton
\n", "

2080 rows × 8 columns

\n", "
" ], "text/plain": [ " name job \\\n", "0 Patricia Schaefer Programmer, systems \n", "1 Olivie Dubois Ingénieur recherche et développement en agroal... \n", "2 Mary Davies-Kirk Public affairs consultant \n", "3 Miroslawa Eckbauer Dispensing optician \n", "4 Richard Bauer Accountant, chartered certified \n", "... ... ... \n", "2075 Maurice Stey Systems developer \n", "2076 Linda Alexander Commrcil horiculuri \n", "2077 Diane Bailly Pharmacien \n", "2078 Jorge Riba Cerdán Hotel manager \n", "2079 Ryan Thompson Brewing technologist \n", "\n", " company street_address \\\n", "0 Estrada-Best 398 Paul Drive \n", "1 Moreno rue Lucas Benard \n", "2 Baker Ltd Flat 3\\nPugh mews \n", "3 Ladeck GmbH Mijo-Lübs-Straße 12 \n", "4 Hoffman-Rocha 6541 Rodriguez Wall \n", "... ... ... \n", "2075 Linke Margraf GmbH & Co. OHG Laila-Scheibe-Allee 2/0 \n", "2076 Webb, Ballald and Vasquel 5594 Persn Ciff \n", "2077 Voisin 527, rue Dijoux \n", "2078 Amador-Diego Rambla de Adriana Barceló 854 Puerta 3 \n", "2079 Smith-Sullivan 136 Rodriguez Point \n", "\n", " city state email \\\n", "0 Christianview Delaware lambdavid@gmail.com \n", "1 Saint Anastasie-les-Bains AR berthelotjacqueline@mahe.fr \n", "2 Stanleyfurt ZA middletonconor@hotmail.com \n", "3 Neubrandenburg Berlin sophia01@yahoo.de \n", "4 Carlosmouth Texas tross@jensen-ware.org \n", "... ... ... ... \n", "2075 Luckenwalde Hamburg gutknechtevelyn@niemeier.com \n", "2076 Mooneybury Maryland ahleythoa@ail.co \n", "2077 Duval-les-Bains CH aruiz@reynaud.fr \n", "2078 Huesca Asturias manuelamosquera@yahoo.com \n", "2079 Bradfordborough North Dakota lcruz@gmail.com \n", "\n", " user_name \n", "0 ndavidson \n", "1 manonallain \n", "2 colemanmichael \n", "3 romanjunitz \n", "4 adam78 \n", "... ... \n", "2075 dkreusel \n", "2076 kennethrchn \n", "2077 dorothee41 \n", "2078 eugenia17 \n", "2079 cnewton \n", "\n", "[2080 rows x 8 columns]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers.drop_duplicates()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we want to delete only those records whose `user_name` is identical:" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namejobcompanystreet_addresscitystateemailuser_name
0Patricia SchaeferProgrammer, systemsEstrada-Best398 Paul DriveChristianviewDelawarelambdavid@gmail.comndavidson
1Olivie DuboisIngénieur recherche et développement en agroal...Morenorue Lucas BenardSaint Anastasie-les-BainsARberthelotjacqueline@mahe.frmanonallain
2Mary Davies-KirkPublic affairs consultantBaker LtdFlat 3\\nPugh mewsStanleyfurtZAmiddletonconor@hotmail.comcolemanmichael
3Miroslawa EckbauerDispensing opticianLadeck GmbHMijo-Lübs-Straße 12NeubrandenburgBerlinsophia01@yahoo.deromanjunitz
4Richard BauerAccountant, chartered certifiedHoffman-Rocha6541 Rodriguez WallCarlosmouthTexastross@jensen-ware.orgadam78
...........................
2074Rhonda JamesRecruitment consultantTurner, Bradley and Scott28382 Stokes ExpresswayPort GabrielaportNew Hampshirezroberts@hotmail.comheathscott
2076Linda AlexanderCommrcil horiculuriWebb, Ballald and Vasquel5594 Persn CiffMooneyburyMarylandahleythoa@ail.cokennethrchn
2077Diane BaillyPharmacienVoisin527, rue DijouxDuval-les-BainsCHaruiz@reynaud.frdorothee41
2078Jorge Riba CerdánHotel managerAmador-DiegoRambla de Adriana Barceló 854 Puerta 3HuescaAsturiasmanuelamosquera@yahoo.comeugenia17
2079Ryan ThompsonBrewing technologistSmith-Sullivan136 Rodriguez PointBradfordboroughNorth Dakotalcruz@gmail.comcnewton
\n", "

2029 rows × 8 columns

\n", "
" ], "text/plain": [ " name job \\\n", "0 Patricia Schaefer Programmer, systems \n", "1 Olivie Dubois Ingénieur recherche et développement en agroal... \n", "2 Mary Davies-Kirk Public affairs consultant \n", "3 Miroslawa Eckbauer Dispensing optician \n", "4 Richard Bauer Accountant, chartered certified \n", "... ... ... \n", "2074 Rhonda James Recruitment consultant \n", "2076 Linda Alexander Commrcil horiculuri \n", "2077 Diane Bailly Pharmacien \n", "2078 Jorge Riba Cerdán Hotel manager \n", "2079 Ryan Thompson Brewing technologist \n", "\n", " company street_address \\\n", "0 Estrada-Best 398 Paul Drive \n", "1 Moreno rue Lucas Benard \n", "2 Baker Ltd Flat 3\\nPugh mews \n", "3 Ladeck GmbH Mijo-Lübs-Straße 12 \n", "4 Hoffman-Rocha 6541 Rodriguez Wall \n", "... ... ... \n", "2074 Turner, Bradley and Scott 28382 Stokes Expressway \n", "2076 Webb, Ballald and Vasquel 5594 Persn Ciff \n", "2077 Voisin 527, rue Dijoux \n", "2078 Amador-Diego Rambla de Adriana Barceló 854 Puerta 3 \n", "2079 Smith-Sullivan 136 Rodriguez Point \n", "\n", " city state email \\\n", "0 Christianview Delaware lambdavid@gmail.com \n", "1 Saint Anastasie-les-Bains AR berthelotjacqueline@mahe.fr \n", "2 Stanleyfurt ZA middletonconor@hotmail.com \n", "3 Neubrandenburg Berlin sophia01@yahoo.de \n", "4 Carlosmouth Texas tross@jensen-ware.org \n", "... ... ... ... \n", "2074 Port Gabrielaport New Hampshire zroberts@hotmail.com \n", "2076 Mooneybury Maryland ahleythoa@ail.co \n", "2077 Duval-les-Bains CH aruiz@reynaud.fr \n", "2078 Huesca Asturias manuelamosquera@yahoo.com \n", "2079 Bradfordborough North Dakota lcruz@gmail.com \n", "\n", " user_name \n", "0 ndavidson \n", "1 manonallain \n", "2 colemanmichael \n", "3 romanjunitz \n", "4 adam78 \n", "... ... \n", "2074 heathscott \n", "2076 kennethrchn \n", "2077 dorothee41 \n", "2078 eugenia17 \n", "2079 cnewton \n", "\n", "[2029 rows x 8 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers.drop_duplicates([\"user_name\"])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This deleted 51 records." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. dedupe\n", "\n", "Alternatively, we can detect the duplicated data with the [Dedupe](https://docs.dedupe.io/en/latest/) library, which uses a flat neural network to learn from a small training.\n", "\n", "
\n", "\n", "**See also:**\n", "\n", "[csvdedupe](https://github.com/dedupeio/csvdedupe) provides a command line tool for dedupe.\n", "
\n", "\n", "In addition, the same developers have created [parserator](https://github.com/datamade/parserator), which you can use to extract text functions and train your own text extraction." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3.1 Configure Dedupe\n", "\n", "Now we define the fields to be taken care of during deduplication and create a new `deduper` object:" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "import os\n", "\n", "import dedupe\n", "\n", "\n", "customers = pd.read_csv(\n", " \"https://raw.githubusercontent.com/kjam/data-cleaning-101/master/data/customer_data_duped.csv\",\n", " encoding=\"utf-8\",\n", ")" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "variables = [\n", " {\"field\": \"name\", \"type\": \"String\"},\n", " {\"field\": \"job\", \"type\": \"String\"},\n", " {\"field\": \"company\", \"type\": \"String\"},\n", " {\"field\": \"street_address\", \"type\": \"String\"},\n", " {\"field\": \"city\", \"type\": \"String\"},\n", " {\"field\": \"state\", \"type\": \"String\", \"has_missing\": True},\n", " {\"field\": \"email\", \"type\": \"String\", \"has_missing\": True},\n", " {\"field\": \"user_name\", \"type\": \"String\"},\n", "]\n", "\n", "deduper = dedupe.Dedupe(variables)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If the value of a field is missing, this missing value should be represented as a `None` object. However, by `'has_missing': True`, a new, additional field is created to indicate whether the data was present or not, and the missing data is given a null.\n", "\n", "
\n", "\n", "**See also:**\n", "\n", "* [Missing Data](https://docs.dedupe.io/en/latest/Variable-definition.html#missing-data)\n", "
" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "deduper" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(2080, 8)" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Create training data" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "deduper.prepare_training(customers.T.to_dict())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`prepare_training` initialises active learning with our data and, optionally, with existing training data.\n", "\n", "`T` mirrors the DataFrame across its diagonal by writing rows as columns and vice versa. For this, [pandas.DataFrame.transpose](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.transpose.html) is used." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. Active learning\n", "\n", "Use `dedupe.console_label` to train your dedupe instance. When Dedupe finds a record pair, you will be asked to label it as a duplicate. You can use the `y`, `n` and `u` keys to label duplicates. Press `f` when you are finished." ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "name : Frédérique Lejeune-Daniel\n", "job : Technicien chimiste\n", "company : Schmitt\n", "street_address : chemin Denise Ferrand\n", "city : Saint CharlotteVille\n", "state : IE\n", "email : jchretien@costa.com\n", "user_name : joseph60\n", "\n", "name : Frédérique Lejeune-Daniel\n", "job : Tecce cse\n", "company : Sctmitt\n", "street_address : chemin Denise Ferrand\n", "city : Saint ChalotteVille\n", "state : IE\n", "email : jchretien@costacom\n", "user_name : joseph60\n", "\n", "0/10 positive, 0/10 negative\n", "Do these records refer to the same thing?\n", "(y)es / (n)o / (u)nsure / (f)inished\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "y\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "name : Jose Carlos Pérez Arias\n", "job : Engineer, maintenance (IT)\n", "company : Marquez PLC\n", "street_address : Pasadizo Ángel Sureda 715 Piso 3 \n", "city : La Rioja\n", "state : Córdoba\n", "email : cifuentesraquel@peralta.com\n", "user_name : gonzalo63\n", "\n", "name : Jose Carlos Pérez Arias\n", "job : Egieer, maiteace (IT)\n", "company : Marquez PLC\n", "street_address : Psdizo Ángel Sured 715 Piso \n", "city : La Rioja\n", "state : Córdob\n", "email : ifuenteraque@perata.om\n", "user_name : gonzalo6\n", "\n", "1/10 positive, 0/10 negative\n", "Do these records refer to the same thing?\n", "(y)es / (n)o / (u)nsure / (f)inished / (p)revious\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "y\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "name : Julio Agustín Amaya\n", "job : Tax adviser\n", "company : Piñol, Belmonte and Codina\n", "street_address : Callejón de Gregorio Bustamante 28 Piso 7 \n", "city : Las Palmas\n", "state : Salamanca\n", "email : usolana@jáuregui-pedraza.com\n", "user_name : gloriaolmo\n", "\n", "name : Julio Agustín Amaya\n", "job : Tax aviser\n", "company : Piñolk Belmonke and Codina\n", "street_address : Calleón de Gregorio Bustamante 28 Piso 7 \n", "city : La Pala\n", "state : Salamanca\n", "email : usolana@jáuregui-pedraza.om\n", "user_name : gloriaolmo\n", "\n", "2/10 positive, 0/10 negative\n", "Do these records refer to the same thing?\n", "(y)es / (n)o / (u)nsure / (f)inished / (p)revious\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "y\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "name : Monique Marty\n", "job : Maoqiie\n", "company : Arnfud\n", "street_address : 70, rue de Carre\n", "city : CheallierBour\n", "state : EC\n", "email : frederiquerichard@cohen.com\n", "user_name : marquesseastie\n", "\n", "name : Monique Marty\n", "job : Maroquinier\n", "company : Arnaud\n", "street_address : 70, rue de Carre\n", "city : ChevallierBourg\n", "state : EC\n", "email : frederiquerichard@cohen.com\n", "user_name : marquessebastien\n", "\n", "3/10 positive, 0/10 negative\n", "Do these records refer to the same thing?\n", "(y)es / (n)o / (u)nsure / (f)inished / (p)revious\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "y\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "name : Susan Aubry\n", "job : Direeur d'gee bire\n", "company : Payet George2 S2A2S2\n", "street_address : , rue Inè Valentn\n", "city : Nicolas\n", "state : FI\n", "email : milletedith@sf.f\n", "user_name : tthierry\n", "\n", "name : Susan Aubry\n", "job : Directeur d'agence bancaire\n", "company : Payet Georges S.A.S.\n", "street_address : 67, rue Inès Valentin\n", "city : Nicolas\n", "state : FI\n", "email : milletedith@sfr.fr\n", "user_name : tthierry\n", "\n", "4/10 positive, 0/10 negative\n", "Do these records refer to the same thing?\n", "(y)es / (n)o / (u)nsure / (f)inished / (p)revious\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "f\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "Finished labeling\n" ] } ], "source": [ "dedupe.console_label(deduper)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The last training dataset compared make it clear that we did not delete this duplicate with our `drop_duplicates` example above - `marquesseastie` and `marquessebastien` were recognised as different.\n", "\n", "`Dedupe.train` adds the record pairs you marked to the training data and updates the matching model.\n", "\n", "With `index_predicates=True`, deduplication also takes into account predicates based on the indexing of the data.\n", "\n", "When you are done, save your training data with `Dedupe.write_settings`." ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "settings_file = \"csv_example_learned_settings\"\n", "if os.path.exists(settings_file):\n", " print(\"reading from\", settings_file)\n", " with open(settings_file, \"rb\") as f:\n", " deduper = dedupe.StaticDedupe(f)\n", "else:\n", " deduper.train(index_predicates=True)\n", " with open(settings_file, \"wb\") as sf:\n", " deduper.write_settings(sf)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "With `dedupe.Dedupe.partition`, records that all refer to the same entity are identified and returned as tuples that are a sequence of record IDs and confidence values. For more details on the confidence value, see `dedupe.Dedupe.cluster`." ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "dupes = deduper.partition(customers.T.to_dict())" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[((84, 1600), (1.0, 1.0)),\n", " ((136, 1360), (1.0, 1.0)),\n", " ((670, 1170), (1.0, 1.0)),\n", " ((856, 1781), (1.0, 1.0)),\n", " ((902, 942), (1.0, 1.0)),\n", " ((1395, 1560), (1.0, 1.0)),\n", " ((1594, 1706), (1.0, 1.0)),\n", " ((0,), (1.0,)),\n", " ((1,), (1.0,)),\n", " ...]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dupes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also output only individual entries:" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "((136, 1360), (1.0, 1.0))" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dupes[1]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can then display these with [pandas.DataFrame.iloc](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.iloc.html):" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namejobcompanystreet_addresscitystateemailuser_name
136Frédérique Lejeune-DanielTechnicien chimisteSchmittchemin Denise FerrandSaint CharlotteVilleIEjchretien@costa.comjoseph60
1360Frédérique Lejeune-DanielTecce cseSctmittchemin Denise FerrandSaint ChalotteVilleIEjchretien@costacomjoseph60
\n", "
" ], "text/plain": [ " name job company \\\n", "136 Frédérique Lejeune-Daniel Technicien chimiste Schmitt \n", "1360 Frédérique Lejeune-Daniel Tecce cse Sctmitt \n", "\n", " street_address city state email \\\n", "136 chemin Denise Ferrand Saint CharlotteVille IE jchretien@costa.com \n", "1360 chemin Denise Ferrand Saint ChalotteVille IE jchretien@costacom \n", "\n", " user_name \n", "136 joseph60 \n", "1360 joseph60 " ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "customers.iloc[[136,1360]]" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.13 Kernel", "language": "python", "name": "python313" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.0" }, "latex_envs": { "LaTeX_envs_menu_present": true, "autoclose": false, "autocomplete": true, "bibliofile": "biblio.bib", "cite_by": "apalike", "current_citInitial": 1, "eqLabelWithNumbers": true, "eqNumInitial": 1, "hotkeys": { "equation": "Ctrl-E", "itemize": "Ctrl-I" }, "labels_anchors": false, "latex_user_defs": false, "report_style_numbering": false, "user_envs_cfg": false }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 4 }