<jupyter_start><jupyter_text>The direct colab link to this notebook is [here](https://colab.research.google.com/github/D3Mlab/ppandas/blob/master/examples/pJoin_mismatches.ipynb).The datasets used can be found [here](https://github.com/D3Mlab/ppandas/tree/master/tests/testing).  Installing pre-requisites and ppandas<jupyter_code>!pip install -i https://test.pypi.org/simple/ PPandas==0.0.1.6.9
!pip install pgmpy==0.1.9
!pip install networkx==2.4
!pip install matplotlib
!pip install python-intervals
import pandas as pd
from ppandas import PDataFrame<jupyter_output>Looking in indexes: https://test.pypi.org/simple/
Collecting PPandas==0.0.1.6.7
  Downloading https://test-files.pythonhosted.org/packages/10/9b/e9339c71f0d820977064f2a8332c87d57ac0a70ebb7d0c4d54cae946bd20/PPandas-0.0.1.6.7-py3-none-any.whl
Installing collected packages: PPandas
  Found existing installation: PPandas 0.0.1.6.6
    Uninstalling PPandas-0.0.1.6.6:
      Successfully uninstalled PPandas-0.0.1.6.6
Successfully installed PPandas-0.0.1.6.7<jupyter_text>**Spatial mismatch handling example** Reference dataframe uses polygons and secondary dataframe uses points<jupyter_code>df1 = pd.read_csv("testing/spatial-ref-region-point.csv")
df1.rename(columns={"Region":"Location"},inplace=True)
df2_point = pd.read_csv("testing/spatial-sec-region-point.csv",usecols=["Point","A"])
df2_point.rename(columns={"Point":"Location"},inplace=True)

pd1 = PDataFrame(["Location"],df1)
pd2_point = PDataFrame(["Location"],df2_point)
pd1.visualise(show_tables=True)
pd2_point.visualise(show_tables=True)
pd_join = pd1.pjoin(pd2_point, mismatches={"Location":"spatial"})
pd_join.visualise(show_tables=True)
queryResult = pd_join.query(['A'],{"Location":"POLYGON ((0 1, 1 1, 1 2, 0 2, 0 1))"})
queryResult<jupyter_output><empty_output><jupyter_text>Reference dataframe uses polygons and secondary dataframe uses two columns  for X,Y coordinates:<jupyter_code>#df2_x_y is identical to df2_point, except it has the X,Y coordinates in two columns
df2_x_y = pd.read_csv("testing/spatial-sec-region-point.csv",usecols=["X","Y","A"])
#zip together X and Y to make one Location column
df2_x_y['Location'] = list(zip(df2_x_y.X, df2_x_y.Y))
df2_x_y.drop(columns=["X","Y"],inplace=True)
pd2_x_y = PDataFrame(["Location"],df2_x_y)
pd_join = pd1.pjoin(pd2_x_y, mismatches={"Location":"spatial"})
queryResult = pd_join.query(['A'],{"Location":"POLYGON ((0 1, 1 1, 1 2, 0 2, 0 1))"})
queryResult<jupyter_output><empty_output><jupyter_text>Reference dataframe uses polygons and secondary dataframe uses polygons<jupyter_code>df1 = pd.read_csv("testing/spatial-ref-region-region.csv")
df2 = pd.read_csv("testing/spatial-sec-region-region.csv")
pd1 = PDataFrame(["Region"],df1)
pd2 = PDataFrame(["Region"],df2)
pd1.visualise(show_tables=True)
pd2.visualise(show_tables=True)
pd_join = pd1.pjoin(pd2, mismatches={"Region":"spatial"})
pd_join.visualise(show_tables=True)
queryResult = pd_join.query(['A'],{"Region":"POLYGON ((0 0, 1 0, 1 1.5, 0 1.5, 0 0))"})
queryResult<jupyter_output><empty_output><jupyter_text>**Numerical mismatch handling example** Load data<jupyter_code>df1 = pd.read_csv("numerical-1.csv")
df2 = pd.read_csv("numerical-2.csv")

pd1 = PDataFrame(["Gender","Age"],df1)
pd2 = PDataFrame(["Gender","Age"],df2)
pd1.visualise(show_tables=True)
pd2.visualise(show_tables=True)<jupyter_output><empty_output><jupyter_text>keyword argument mismatch takes in a dictionary with mismtach column names as keys and the type of mismatches as values<jupyter_code>pd_join = pd1.pjoin(pd2,mismatches={"Age":'numerical'})
pd_join.visualise(show_tables=True)
queryResult = pd_join.query(['Gun Control'],{"Gender":'female',"Age":'[40,60)'})
queryResult
pd_join2 = pd2.pjoin(pd1, mismatches={'Age':'numerical'})
pd_join2.visualise(show_tables=True)<jupyter_output><empty_output><jupyter_text>**Categorical mismatch handling example**<jupyter_code>pd_join3 = pd1.pjoin(pd2, mismatches={'Age':'categorical'})
pd_join3.visualise(show_tables=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15376959442647425, 'fraction_numerical': 0.04304553371485444, 'mean_word_length': 8.054054054054054, 'pattern_counts': {'":': 11, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 5, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 7, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '251599', 'license': 'MIT', 'n_tokens_mistral': 1649, 'n_tokens_neox': 1535, 'n_words': 249, 'path': 'examples/pJoin_mismatches.ipynb', 'repo_name': 'D3Mlab/ppandas', 'size': '4019'}