{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This quickstart guide explains how to join two tables A and B using edit distance measure. First, you need to import the required packages as follows (if you have installed **py_stringsimjoin** it will automatically install the dependencies **py_stringmatching** and **pandas**):"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"# Import libraries\n",
"import py_stringsimjoin as ssj\n",
"import py_stringmatching as sm\n",
"import pandas as pd\n",
"import os, sys"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"python version: 2.7.12 |Anaconda 2.5.0 (x86_64)| (default, Jul 2 2016, 17:43:17) \n",
"[GCC 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00)]\n",
"py_stringsimjoin version: 0.1.0\n",
"py_stringmatching version: 0.2.0\n",
"pandas version: 0.17.1\n"
]
}
],
"source": [
"print('python version: ' + sys.version)\n",
"print('py_stringsimjoin version: ' + ssj.__version__)\n",
"print('py_stringmatching version: ' + sm.__version__)\n",
"print('pandas version: ' + pd.__version__)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Joining two tables using edit distance measure typically consists of three steps:\n",
"1. Loading the input tables\n",
"2. Profiling the tables\n",
"3. Performing the join"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 1. Loading the input tables"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We begin by loading the two tables. For the purpose of this guide, \n",
"we use the sample dataset that comes with the package. "
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"# construct the path of the tables to be loaded. Since we are loading a \n",
"# dataset from the package, we need to access the data from the path \n",
"# where the package is installed. If you need to load your own data, you can directly\n",
"# provide your table path to the read_csv command.\n",
"\n",
"table_A_path = os.sep.join([ssj.get_install_path(), 'datasets', 'data', 'person_table_A.csv'])\n",
"table_B_path = os.sep.join([ssj.get_install_path(), 'datasets', 'data', 'person_table_B.csv'])"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Number of records in A: 7\n",
"Number of records in B: 8\n"
]
}
],
"source": [
"# Load csv files as dataframes.\n",
"A = pd.read_csv(table_A_path)\n",
"B = pd.read_csv(table_B_path)\n",
"print('Number of records in A: ' + str(len(A)))\n",
"print('Number of records in B: ' + str(len(B)))"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" A.id | \n",
" A.name | \n",
" A.birth_year | \n",
" A.hourly_wage | \n",
" A.address | \n",
" A.zipcode | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" a1 | \n",
" Kevin Smith | \n",
" 1989 | \n",
" 30.0 | \n",
" 607 From St, San Francisco | \n",
" 94107 | \n",
"
\n",
" \n",
" 1 | \n",
" a2 | \n",
" Michael Franklin | \n",
" 1988 | \n",
" 27.5 | \n",
" 1652 Stockton St, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 2 | \n",
" a3 | \n",
" William Bridge | \n",
" 1986 | \n",
" 32.0 | \n",
" 3131 Webster St, San Francisco | \n",
" 94107 | \n",
"
\n",
" \n",
" 3 | \n",
" a4 | \n",
" Binto George | \n",
" 1987 | \n",
" 32.5 | \n",
" 423 Powell St, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 4 | \n",
" a5 | \n",
" Alphonse Kemper | \n",
" 1984 | \n",
" 35.0 | \n",
" 1702 Post Street, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 5 | \n",
" a6 | \n",
" | \n",
" 1990 | \n",
" 40.0 | \n",
" 24th Street, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 6 | \n",
" a7 | \n",
" NaN | \n",
" 1986 | \n",
" 25.0 | \n",
" 20th Street, San Francisco | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" A.id A.name A.birth_year A.hourly_wage \\\n",
"0 a1 Kevin Smith 1989 30.0 \n",
"1 a2 Michael Franklin 1988 27.5 \n",
"2 a3 William Bridge 1986 32.0 \n",
"3 a4 Binto George 1987 32.5 \n",
"4 a5 Alphonse Kemper 1984 35.0 \n",
"5 a6 1990 40.0 \n",
"6 a7 NaN 1986 25.0 \n",
"\n",
" A.address A.zipcode \n",
"0 607 From St, San Francisco 94107 \n",
"1 1652 Stockton St, San Francisco 94122 \n",
"2 3131 Webster St, San Francisco 94107 \n",
"3 423 Powell St, San Francisco 94122 \n",
"4 1702 Post Street, San Francisco 94122 \n",
"5 24th Street, San Francisco 94122 \n",
"6 20th Street, San Francisco NaN "
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"A"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" B.id | \n",
" B.name | \n",
" B.birth_year | \n",
" B.hourly_wage | \n",
" B.address | \n",
" B.zipcode | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" b1 | \n",
" Mark Levene | \n",
" 1987 | \n",
" 29.5 | \n",
" 108 Clement St, San Francisco | \n",
" 94107 | \n",
"
\n",
" \n",
" 1 | \n",
" b2 | \n",
" Bill Bridge | \n",
" 1986 | \n",
" 32.0 | \n",
" 3131 Webster St, San Francisco | \n",
" 94107 | \n",
"
\n",
" \n",
" 2 | \n",
" b3 | \n",
" Mike Franklin | \n",
" 1988 | \n",
" 27.5 | \n",
" 1652 Stockton St, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 3 | \n",
" b4 | \n",
" Joseph Kuan | \n",
" 1982 | \n",
" 26.0 | \n",
" 108 South Park, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 4 | \n",
" b5 | \n",
" Alfons Kemper | \n",
" 1984 | \n",
" 35.0 | \n",
" 170 Post St, Apt 4, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 5 | \n",
" b6 | \n",
" Michael Brodie | \n",
" 1987 | \n",
" 32.5 | \n",
" 133 Clement Street, San Francisco | \n",
" 94107 | \n",
"
\n",
" \n",
" 6 | \n",
" b7 | \n",
" | \n",
" 1990 | \n",
" 40.0 | \n",
" 24th Street, San Francisco | \n",
" 94122 | \n",
"
\n",
" \n",
" 7 | \n",
" b8 | \n",
" NaN | \n",
" 1986 | \n",
" 25.0 | \n",
" 20th Street, San Francisco | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" B.id B.name B.birth_year B.hourly_wage \\\n",
"0 b1 Mark Levene 1987 29.5 \n",
"1 b2 Bill Bridge 1986 32.0 \n",
"2 b3 Mike Franklin 1988 27.5 \n",
"3 b4 Joseph Kuan 1982 26.0 \n",
"4 b5 Alfons Kemper 1984 35.0 \n",
"5 b6 Michael Brodie 1987 32.5 \n",
"6 b7 1990 40.0 \n",
"7 b8 NaN 1986 25.0 \n",
"\n",
" B.address B.zipcode \n",
"0 108 Clement St, San Francisco 94107 \n",
"1 3131 Webster St, San Francisco 94107 \n",
"2 1652 Stockton St, San Francisco 94122 \n",
"3 108 South Park, San Francisco 94122 \n",
"4 170 Post St, Apt 4, San Francisco 94122 \n",
"5 133 Clement Street, San Francisco 94107 \n",
"6 24th Street, San Francisco 94122 \n",
"7 20th Street, San Francisco NaN "
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"B"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 2. Profiling the tables"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Before performing the join, we may want to profile the tables to \n",
"know about the characteristics of the attributes. This can help identify:\n",
"\n",
"a) unique attributes in the table which can be used as key attribute when performing \n",
" the join. A key attribute is needed to uniquely identify a tuple. \n",
" \n",
"b) the number of missing values present in each attribute. This can \n",
" help you in deciding the attribute on which to perform the join. \n",
" For example, an attribute with a lot of missing values may not be a good \n",
" join attribute. Further, based on the missing value information you \n",
" need to decide on how to handle missing values when performing the join \n",
" (See the section below on 'Handling missing values' to know more about\n",
" the options available for handling missing values when performing the join).\n",
" \n",
"You can profile the attributes in a table using the following command:"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Unique values | \n",
" Missing values | \n",
" Comments | \n",
"
\n",
" \n",
" Attribute | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" A.id | \n",
" 7 (100.0%) | \n",
" 0 (0.0%) | \n",
" This attribute can be used as a key attribute. | \n",
"
\n",
" \n",
" A.name | \n",
" 7 (100.0%) | \n",
" 1 (14.29%) | \n",
" Joining on this attribute will ignore 1 (14.29%) rows. | \n",
"
\n",
" \n",
" A.birth_year | \n",
" 6 (85.71%) | \n",
" 0 (0.0%) | \n",
" | \n",
"
\n",
" \n",
" A.hourly_wage | \n",
" 7 (100.0%) | \n",
" 0 (0.0%) | \n",
" This attribute can be used as a key attribute. | \n",
"
\n",
" \n",
" A.address | \n",
" 7 (100.0%) | \n",
" 0 (0.0%) | \n",
" This attribute can be used as a key attribute. | \n",
"
\n",
" \n",
" A.zipcode | \n",
" 3 (42.86%) | \n",
" 1 (14.29%) | \n",
" Joining on this attribute will ignore 1 (14.29%) rows. | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Unique values Missing values \\\n",
"Attribute \n",
"A.id 7 (100.0%) 0 (0.0%) \n",
"A.name 7 (100.0%) 1 (14.29%) \n",
"A.birth_year 6 (85.71%) 0 (0.0%) \n",
"A.hourly_wage 7 (100.0%) 0 (0.0%) \n",
"A.address 7 (100.0%) 0 (0.0%) \n",
"A.zipcode 3 (42.86%) 1 (14.29%) \n",
"\n",
" Comments \n",
"Attribute \n",
"A.id This attribute can be used as a key attribute. \n",
"A.name Joining on this attribute will ignore 1 (14.29%) rows. \n",
"A.birth_year \n",
"A.hourly_wage This attribute can be used as a key attribute. \n",
"A.address This attribute can be used as a key attribute. \n",
"A.zipcode Joining on this attribute will ignore 1 (14.29%) rows. "
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# profile attributes in table A\n",
"ssj.profile_table_for_join(A)"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Unique values | \n",
" Missing values | \n",
" Comments | \n",
"
\n",
" \n",
" Attribute | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" B.id | \n",
" 8 (100.0%) | \n",
" 0 (0.0%) | \n",
" This attribute can be used as a key attribute. | \n",
"
\n",
" \n",
" B.name | \n",
" 8 (100.0%) | \n",
" 1 (12.5%) | \n",
" Joining on this attribute will ignore 1 (12.5%) rows. | \n",
"
\n",
" \n",
" B.birth_year | \n",
" 6 (75.0%) | \n",
" 0 (0.0%) | \n",
" | \n",
"
\n",
" \n",
" B.hourly_wage | \n",
" 8 (100.0%) | \n",
" 0 (0.0%) | \n",
" This attribute can be used as a key attribute. | \n",
"
\n",
" \n",
" B.address | \n",
" 8 (100.0%) | \n",
" 0 (0.0%) | \n",
" This attribute can be used as a key attribute. | \n",
"
\n",
" \n",
" B.zipcode | \n",
" 3 (37.5%) | \n",
" 1 (12.5%) | \n",
" Joining on this attribute will ignore 1 (12.5%) rows. | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Unique values Missing values \\\n",
"Attribute \n",
"B.id 8 (100.0%) 0 (0.0%) \n",
"B.name 8 (100.0%) 1 (12.5%) \n",
"B.birth_year 6 (75.0%) 0 (0.0%) \n",
"B.hourly_wage 8 (100.0%) 0 (0.0%) \n",
"B.address 8 (100.0%) 0 (0.0%) \n",
"B.zipcode 3 (37.5%) 1 (12.5%) \n",
"\n",
" Comments \n",
"Attribute \n",
"B.id This attribute can be used as a key attribute. \n",
"B.name Joining on this attribute will ignore 1 (12.5%) rows. \n",
"B.birth_year \n",
"B.hourly_wage This attribute can be used as a key attribute. \n",
"B.address This attribute can be used as a key attribute. \n",
"B.zipcode Joining on this attribute will ignore 1 (12.5%) rows. "
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# profile attributes in table B\n",
"ssj.profile_table_for_join(B)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If the input tables does not contain any key attribute, then you need \n",
"to create a key attribute. In the current example, both the input tables\n",
"A and B have key attributes, and hence you can proceed to the next step.\n",
"In the case the table does not have any key attribute, you can \n",
"add a key attribute using the following command:"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" B.id | \n",
" B.name | \n",
" B.birth_year | \n",
" B.hourly_wage | \n",
" B.address | \n",
" B.zipcode | \n",
" new_key_attr | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" b1 | \n",
" Mark Levene | \n",
" 1987 | \n",
" 29.5 | \n",
" 108 Clement St, San Francisco | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 1 | \n",
" b2 | \n",
" Bill Bridge | \n",
" 1986 | \n",
" 32.0 | \n",
" 3131 Webster St, San Francisco | \n",
" 94107 | \n",
" 1 | \n",
"
\n",
" \n",
" 2 | \n",
" b3 | \n",
" Mike Franklin | \n",
" 1988 | \n",
" 27.5 | \n",
" 1652 Stockton St, San Francisco | \n",
" 94122 | \n",
" 2 | \n",
"
\n",
" \n",
" 3 | \n",
" b4 | \n",
" Joseph Kuan | \n",
" 1982 | \n",
" 26.0 | \n",
" 108 South Park, San Francisco | \n",
" 94122 | \n",
" 3 | \n",
"
\n",
" \n",
" 4 | \n",
" b5 | \n",
" Alfons Kemper | \n",
" 1984 | \n",
" 35.0 | \n",
" 170 Post St, Apt 4, San Francisco | \n",
" 94122 | \n",
" 4 | \n",
"
\n",
" \n",
" 5 | \n",
" b6 | \n",
" Michael Brodie | \n",
" 1987 | \n",
" 32.5 | \n",
" 133 Clement Street, San Francisco | \n",
" 94107 | \n",
" 5 | \n",
"
\n",
" \n",
" 6 | \n",
" b7 | \n",
" | \n",
" 1990 | \n",
" 40.0 | \n",
" 24th Street, San Francisco | \n",
" 94122 | \n",
" 6 | \n",
"
\n",
" \n",
" 7 | \n",
" b8 | \n",
" NaN | \n",
" 1986 | \n",
" 25.0 | \n",
" 20th Street, San Francisco | \n",
" NaN | \n",
" 7 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" B.id B.name B.birth_year B.hourly_wage \\\n",
"0 b1 Mark Levene 1987 29.5 \n",
"1 b2 Bill Bridge 1986 32.0 \n",
"2 b3 Mike Franklin 1988 27.5 \n",
"3 b4 Joseph Kuan 1982 26.0 \n",
"4 b5 Alfons Kemper 1984 35.0 \n",
"5 b6 Michael Brodie 1987 32.5 \n",
"6 b7 1990 40.0 \n",
"7 b8 NaN 1986 25.0 \n",
"\n",
" B.address B.zipcode new_key_attr \n",
"0 108 Clement St, San Francisco 94107 0 \n",
"1 3131 Webster St, San Francisco 94107 1 \n",
"2 1652 Stockton St, San Francisco 94122 2 \n",
"3 108 South Park, San Francisco 94122 3 \n",
"4 170 Post St, Apt 4, San Francisco 94122 4 \n",
"5 133 Clement Street, San Francisco 94107 5 \n",
"6 24th Street, San Francisco 94122 6 \n",
"7 20th Street, San Francisco NaN 7 "
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"B['new_key_attr'] = range(0, len(B))\n",
"B"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For the purpose of this guide, we will now join tables A and B on \n",
"'name' attribute using edit distance measure. Next, we need to decide on what \n",
"threshold to use for the join. For this guide, we will use a threshold of 5. \n",
"Specifically, the join will now find tuple pairs from A and B such that \n",
"the edit distance over the 'name' attributes is at most 5."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 3. Performing the join"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The next step is to perform the edit distance join using the following command:"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"0% 100%\n",
"[#######] | ETA: 00:00:00\n",
"Total time elapsed: 00:00:00\n"
]
}
],
"source": [
"# find all pairs from A and B such that the edit distance\n",
"# on 'name' is at most 5.\n",
"# l_out_attrs and r_out_attrs denote the attributes from the \n",
"# left table (A) and right table (B) that need to be included in the output.\n",
"\n",
"output_pairs = ssj.edit_distance_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', 5, \n",
" l_out_attrs=['A.name'], r_out_attrs=['B.name'])"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"4"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"len(output_pairs)"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" _id | \n",
" l_A.id | \n",
" r_B.id | \n",
" l_A.name | \n",
" r_B.name | \n",
" _sim_score | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 0 | \n",
" a3 | \n",
" b2 | \n",
" William Bridge | \n",
" Bill Bridge | \n",
" 4 | \n",
"
\n",
" \n",
" 1 | \n",
" 1 | \n",
" a2 | \n",
" b3 | \n",
" Michael Franklin | \n",
" Mike Franklin | \n",
" 4 | \n",
"
\n",
" \n",
" 2 | \n",
" 2 | \n",
" a5 | \n",
" b5 | \n",
" Alphonse Kemper | \n",
" Alfons Kemper | \n",
" 3 | \n",
"
\n",
" \n",
" 3 | \n",
" 3 | \n",
" a6 | \n",
" b7 | \n",
" | \n",
" | \n",
" 0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" _id l_A.id r_B.id l_A.name r_B.name _sim_score\n",
"0 0 a3 b2 William Bridge Bill Bridge 4\n",
"1 1 a2 b3 Michael Franklin Mike Franklin 4\n",
"2 2 a5 b5 Alphonse Kemper Alfons Kemper 3\n",
"3 3 a6 b7 0"
]
},
"execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# examine the output pairs\n",
"output_pairs"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": false
},
"source": [
"# Handling missing values"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"By default, pairs with missing values are not included\n",
"in the output. This is because a string with a missing value\n",
"can potentially match with all strings in the other table and \n",
"hence the number of output pairs can become huge. If you want \n",
"to include pairs with missing value in the output, you need to \n",
"set the **allow_missing** flag to True, as shown below:"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"0% 100%\n",
"[#######] | ETA: 00:00:00\n",
"Total time elapsed: 00:00:00\n",
"0% 100%\n",
"[##] | ETA: 00:00:00"
]
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Finding pairs with missing value...\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"\n",
"Total time elapsed: 00:00:00\n"
]
}
],
"source": [
"output_pairs = ssj.edit_distance_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', 5, allow_missing=True, \n",
" l_out_attrs=['A.name'], r_out_attrs=['B.name'])"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" _id | \n",
" l_A.id | \n",
" r_B.id | \n",
" l_A.name | \n",
" r_B.name | \n",
" _sim_score | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 0 | \n",
" a3 | \n",
" b2 | \n",
" William Bridge | \n",
" Bill Bridge | \n",
" 4 | \n",
"
\n",
" \n",
" 1 | \n",
" 1 | \n",
" a2 | \n",
" b3 | \n",
" Michael Franklin | \n",
" Mike Franklin | \n",
" 4 | \n",
"
\n",
" \n",
" 2 | \n",
" 2 | \n",
" a5 | \n",
" b5 | \n",
" Alphonse Kemper | \n",
" Alfons Kemper | \n",
" 3 | \n",
"
\n",
" \n",
" 3 | \n",
" 3 | \n",
" a6 | \n",
" b7 | \n",
" | \n",
" | \n",
" 0 | \n",
"
\n",
" \n",
" 0 | \n",
" 4 | \n",
" a7 | \n",
" b1 | \n",
" NaN | \n",
" Mark Levene | \n",
" NaN | \n",
"
\n",
" \n",
" 1 | \n",
" 5 | \n",
" a7 | \n",
" b2 | \n",
" NaN | \n",
" Bill Bridge | \n",
" NaN | \n",
"
\n",
" \n",
" 2 | \n",
" 6 | \n",
" a7 | \n",
" b3 | \n",
" NaN | \n",
" Mike Franklin | \n",
" NaN | \n",
"
\n",
" \n",
" 3 | \n",
" 7 | \n",
" a7 | \n",
" b4 | \n",
" NaN | \n",
" Joseph Kuan | \n",
" NaN | \n",
"
\n",
" \n",
" 4 | \n",
" 8 | \n",
" a7 | \n",
" b5 | \n",
" NaN | \n",
" Alfons Kemper | \n",
" NaN | \n",
"
\n",
" \n",
" 5 | \n",
" 9 | \n",
" a7 | \n",
" b6 | \n",
" NaN | \n",
" Michael Brodie | \n",
" NaN | \n",
"
\n",
" \n",
" 6 | \n",
" 10 | \n",
" a7 | \n",
" b7 | \n",
" NaN | \n",
" | \n",
" NaN | \n",
"
\n",
" \n",
" 7 | \n",
" 11 | \n",
" a7 | \n",
" b8 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 8 | \n",
" 12 | \n",
" a1 | \n",
" b8 | \n",
" Kevin Smith | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 9 | \n",
" 13 | \n",
" a2 | \n",
" b8 | \n",
" Michael Franklin | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 10 | \n",
" 14 | \n",
" a3 | \n",
" b8 | \n",
" William Bridge | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 11 | \n",
" 15 | \n",
" a4 | \n",
" b8 | \n",
" Binto George | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 12 | \n",
" 16 | \n",
" a5 | \n",
" b8 | \n",
" Alphonse Kemper | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" 13 | \n",
" 17 | \n",
" a6 | \n",
" b8 | \n",
" | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" _id l_A.id r_B.id l_A.name r_B.name _sim_score\n",
"0 0 a3 b2 William Bridge Bill Bridge 4\n",
"1 1 a2 b3 Michael Franklin Mike Franklin 4\n",
"2 2 a5 b5 Alphonse Kemper Alfons Kemper 3\n",
"3 3 a6 b7 0\n",
"0 4 a7 b1 NaN Mark Levene NaN\n",
"1 5 a7 b2 NaN Bill Bridge NaN\n",
"2 6 a7 b3 NaN Mike Franklin NaN\n",
"3 7 a7 b4 NaN Joseph Kuan NaN\n",
"4 8 a7 b5 NaN Alfons Kemper NaN\n",
"5 9 a7 b6 NaN Michael Brodie NaN\n",
"6 10 a7 b7 NaN NaN\n",
"7 11 a7 b8 NaN NaN NaN\n",
"8 12 a1 b8 Kevin Smith NaN NaN\n",
"9 13 a2 b8 Michael Franklin NaN NaN\n",
"10 14 a3 b8 William Bridge NaN NaN\n",
"11 15 a4 b8 Binto George NaN NaN\n",
"12 16 a5 b8 Alphonse Kemper NaN NaN\n",
"13 17 a6 b8 NaN NaN"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"output_pairs"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Enabling parallel processing"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you have multiple cores which you want to exploit for performing the \n",
"join, you need to use the **n_jobs** option. If n_jobs is -1, all CPUs \n",
"are used. If 1 is given, no parallel computing code is used at all, \n",
"which is useful for debugging and is the default option. For n_jobs below \n",
"-1, (n_cpus + 1 + n_jobs) are used (where n_cpus is the total number of \n",
"CPUs in the machine). Thus for n_jobs = -2, all CPUs but one are used. If \n",
"(n_cpus + 1 + n_jobs) becomes less than 1, then no parallel computing code \n",
"will be used (i.e., equivalent to the default).\n",
"\n",
"The following command exploits all the cores available to perform the join:"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"0% 100%\n",
"[ ]\r",
"[# ] | ETA: 00:00:00\r",
"[##] | ETA: 00:00:00\r",
"[##] | ETA: 00:00:00\n",
"Total time elapsed: 00:00:00\n"
]
}
],
"source": [
"output_pairs = ssj.edit_distance_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', 5, \n",
" l_out_attrs=['A.name'], r_out_attrs=['B.name'], n_jobs=-1)"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"4"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"len(output_pairs)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You need to set n_jobs to 1 when you are debugging or you do not want \n",
"to use any parallel computing code. If you want to execute the join as \n",
"fast as possible, you need to set n_jobs to -1 which will exploit all \n",
"the CPUs in your machine. In case there are other concurrent processes \n",
"running in your machine and you do not want to halt them, then you may \n",
"need to set n_jobs to a value below -1."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Performing join on numeric attributes"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The join method expects the join attributes to be of string type. \n",
"If you need to perform the join over numeric attributes, then you need \n",
"to first convert the attributes to string type and then perform the join.\n",
"For example, if you need to join 'A.zipcode' in table A with 'B.zipcode' in\n",
"table B, you need to first convert the attributes to string type using \n",
"the following command:"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"True"
]
},
"execution_count": 36,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ssj.dataframe_column_to_str(A, 'A.zipcode', inplace=True)\n",
"ssj.dataframe_column_to_str(B, 'B.zipcode', inplace=True)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that the above command preserves the NaN values while converting the numeric column to string type. Next, you can perform the join as shown below:"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"0% 100%\n",
"[#######] | ETA: 00:00:00\n",
"Total time elapsed: 00:00:00\n"
]
},
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" _id | \n",
" l_A.id | \n",
" r_B.id | \n",
" l_A.zipcode | \n",
" r_B.zipcode | \n",
" _sim_score | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 0 | \n",
" a1 | \n",
" b1 | \n",
" 94107 | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 1 | \n",
" 1 | \n",
" a3 | \n",
" b1 | \n",
" 94107 | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 2 | \n",
" 2 | \n",
" a1 | \n",
" b2 | \n",
" 94107 | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 3 | \n",
" 3 | \n",
" a3 | \n",
" b2 | \n",
" 94107 | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 4 | \n",
" 4 | \n",
" a2 | \n",
" b3 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 5 | \n",
" 5 | \n",
" a4 | \n",
" b3 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 6 | \n",
" 6 | \n",
" a5 | \n",
" b3 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 7 | \n",
" 7 | \n",
" a6 | \n",
" b3 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 8 | \n",
" 8 | \n",
" a2 | \n",
" b4 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 9 | \n",
" 9 | \n",
" a4 | \n",
" b4 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 10 | \n",
" 10 | \n",
" a5 | \n",
" b4 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 11 | \n",
" 11 | \n",
" a6 | \n",
" b4 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 12 | \n",
" 12 | \n",
" a2 | \n",
" b5 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 13 | \n",
" 13 | \n",
" a4 | \n",
" b5 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 14 | \n",
" 14 | \n",
" a5 | \n",
" b5 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 15 | \n",
" 15 | \n",
" a6 | \n",
" b5 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 16 | \n",
" 16 | \n",
" a1 | \n",
" b6 | \n",
" 94107 | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 17 | \n",
" 17 | \n",
" a3 | \n",
" b6 | \n",
" 94107 | \n",
" 94107 | \n",
" 0 | \n",
"
\n",
" \n",
" 18 | \n",
" 18 | \n",
" a2 | \n",
" b7 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 19 | \n",
" 19 | \n",
" a4 | \n",
" b7 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 20 | \n",
" 20 | \n",
" a5 | \n",
" b7 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
" 21 | \n",
" 21 | \n",
" a6 | \n",
" b7 | \n",
" 94122 | \n",
" 94122 | \n",
" 0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" _id l_A.id r_B.id l_A.zipcode r_B.zipcode _sim_score\n",
"0 0 a1 b1 94107 94107 0\n",
"1 1 a3 b1 94107 94107 0\n",
"2 2 a1 b2 94107 94107 0\n",
"3 3 a3 b2 94107 94107 0\n",
"4 4 a2 b3 94122 94122 0\n",
"5 5 a4 b3 94122 94122 0\n",
"6 6 a5 b3 94122 94122 0\n",
"7 7 a6 b3 94122 94122 0\n",
"8 8 a2 b4 94122 94122 0\n",
"9 9 a4 b4 94122 94122 0\n",
"10 10 a5 b4 94122 94122 0\n",
"11 11 a6 b4 94122 94122 0\n",
"12 12 a2 b5 94122 94122 0\n",
"13 13 a4 b5 94122 94122 0\n",
"14 14 a5 b5 94122 94122 0\n",
"15 15 a6 b5 94122 94122 0\n",
"16 16 a1 b6 94107 94107 0\n",
"17 17 a3 b6 94107 94107 0\n",
"18 18 a2 b7 94122 94122 0\n",
"19 19 a4 b7 94122 94122 0\n",
"20 20 a5 b7 94122 94122 0\n",
"21 21 a6 b7 94122 94122 0"
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"output_pairs = ssj.edit_distance_join(A, B, 'A.id', 'B.id', 'A.zipcode', 'B.zipcode', 1, \n",
" l_out_attrs=['A.zipcode'], r_out_attrs=['B.zipcode'])\n",
"output_pairs"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Additional options"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can find all the options available for the edit distance \n",
"join function using the **help** command as shown below:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"help(ssj.edit_distance_join)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# More information"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Similar to edit distance measure, you can use the package to perform \n",
"join using other measures such as cosine, Dice, Jaccard, overlap and \n",
"overlap coefficient. For measures such as TF-IDF which are not \n",
"directly supported, you can perform the join using the filters provided \n",
"in the package. To know more about other join methods as well as how to \n",
"use filters, refer to the how-to guide (available from the \n",
"[package homepage](https://sites.google.com/site/anhaidgroup/projects/py_stringsimjoin))."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.10"
}
},
"nbformat": 4,
"nbformat_minor": 0
}