3.column_mapping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we'll discuss the 4 different entity mapping methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "import os\n",
    "import pandas as pd\n",
    "import textwrap\n",
    "\n",
    "from langchain_community.graphs import Neo4jGraph\n",
    "from langchain_openai import ChatOpenAI\n",
    "from langchain_core.prompts import ChatPromptTemplate,SystemMessagePromptTemplate, PromptTemplate\n",
    "from langchain_community.vectorstores import Neo4jVector\n",
    "from langchain_openai import OpenAIEmbeddings\n",
    "from langchain.text_splitter import RecursiveCharacterTextSplitter\n",
    "from langchain.chains import RetrievalQAWithSourcesChain\n",
    "from langchain import LLMChain, OpenAI\n",
    "from langchain_core.output_parsers import StrOutputParser\n",
    "from langchain_core.runnables import RunnablePassthrough\n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv('.env', override=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load from environment\n",
    "NEO4J_URI = os.getenv('NEO4J_URI')\n",
    "NEO4J_USERNAME = os.getenv('NEO4J_USERNAME')\n",
    "NEO4J_PASSWORD = os.getenv('NEO4J_PASSWORD')\n",
    "NEO4J_DATABASE = os.getenv('NEO4J_DATABASE')\n",
    "\n",
    "OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')\n",
    "OPENAI_ENDPOINT = os.getenv('OPENAI_BASE_URL') + '/embeddings'\n",
    "\n",
    "os.environ[\"LANGCHAIN_TRACING_V2\"]=\"true\"\n",
    "os.environ[\"LANGCHAIN_API_KEY\"]=os.getenv(\"LANGCHAIN_API_KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "kg = Neo4jGraph(\n",
    "    url=NEO4J_URI, username=NEO4J_USERNAME, password=NEO4J_PASSWORD, database=NEO4J_DATABASE\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method 1\n",
    "Here are we just doing a cosine similarity of the vectors that we have embedded.\n",
    "Similar columns will have a high similarity score."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "~~~sql\n",
    "  MATCH (n1:column), (n2:column)\n",
    "  WHERE n1.source <> n2.source\n",
    "  AND (n1.col_type='reference' and n2.col_type='reference')\n",
    "  RETURN n1.source, n1.name , n2.source, n2.name, \n",
    "  vector.similarity.cosine(n1.embedding, n2.embedding) as similarity\n",
    "~~~\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n1.source</th>\n",
       "      <th>n1.name</th>\n",
       "      <th>n2.source</th>\n",
       "      <th>n2.name</th>\n",
       "      <th>similarity</th>\n",
       "      <th>unique</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>country</td>\n",
       "      <td>weather</td>\n",
       "      <td>country</td>\n",
       "      <td>0.983184</td>\n",
       "      <td>{foot_traffic.country, weather.country}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>symbol</td>\n",
       "      <td>sec_master</td>\n",
       "      <td>ticker</td>\n",
       "      <td>0.960115</td>\n",
       "      <td>{sec_master.ticker, foot_traffic.symbol}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>post_code</td>\n",
       "      <td>weather</td>\n",
       "      <td>zip_code</td>\n",
       "      <td>0.954602</td>\n",
       "      <td>{weather.zip_code, foot_traffic.post_code}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>symbol</td>\n",
       "      <td>sec_master</td>\n",
       "      <td>entity_name</td>\n",
       "      <td>0.936529</td>\n",
       "      <td>{sec_master.entity_name, foot_traffic.symbol}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>web_traffic</td>\n",
       "      <td>website_owner</td>\n",
       "      <td>social_media</td>\n",
       "      <td>page_owner</td>\n",
       "      <td>0.935326</td>\n",
       "      <td>{web_traffic.website_owner, social_media.page_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>weather</td>\n",
       "      <td>zip_code</td>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>country</td>\n",
       "      <td>0.935087</td>\n",
       "      <td>{foot_traffic.country, weather.zip_code}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>symbol</td>\n",
       "      <td>sec_master</td>\n",
       "      <td>security_name</td>\n",
       "      <td>0.934660</td>\n",
       "      <td>{foot_traffic.symbol, sec_master.security_name}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>web_traffic</td>\n",
       "      <td>website_brand</td>\n",
       "      <td>social_media</td>\n",
       "      <td>page_owner</td>\n",
       "      <td>0.927484</td>\n",
       "      <td>{web_traffic.website_brand, social_media.page_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>web_traffic</td>\n",
       "      <td>website_owner</td>\n",
       "      <td>sec_master</td>\n",
       "      <td>entity_name</td>\n",
       "      <td>0.926716</td>\n",
       "      <td>{web_traffic.website_owner, sec_master.entity_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>foot_traffic</td>\n",
       "      <td>location_name</td>\n",
       "      <td>weather</td>\n",
       "      <td>zip_code</td>\n",
       "      <td>0.921447</td>\n",
       "      <td>{weather.zip_code, foot_traffic.location_name}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      n1.source        n1.name     n2.source        n2.name  similarity  \\\n",
       "0  foot_traffic        country       weather        country    0.983184   \n",
       "1  foot_traffic         symbol    sec_master         ticker    0.960115   \n",
       "2  foot_traffic      post_code       weather       zip_code    0.954602   \n",
       "3  foot_traffic         symbol    sec_master    entity_name    0.936529   \n",
       "4   web_traffic  website_owner  social_media     page_owner    0.935326   \n",
       "5       weather       zip_code  foot_traffic        country    0.935087   \n",
       "6  foot_traffic         symbol    sec_master  security_name    0.934660   \n",
       "7   web_traffic  website_brand  social_media     page_owner    0.927484   \n",
       "8   web_traffic  website_owner    sec_master    entity_name    0.926716   \n",
       "9  foot_traffic  location_name       weather       zip_code    0.921447   \n",
       "\n",
       "                                              unique  \n",
       "0            {foot_traffic.country, weather.country}  \n",
       "1           {sec_master.ticker, foot_traffic.symbol}  \n",
       "2         {weather.zip_code, foot_traffic.post_code}  \n",
       "3      {sec_master.entity_name, foot_traffic.symbol}  \n",
       "4  {web_traffic.website_owner, social_media.page_...  \n",
       "5           {foot_traffic.country, weather.zip_code}  \n",
       "6    {foot_traffic.symbol, sec_master.security_name}  \n",
       "7  {web_traffic.website_brand, social_media.page_...  \n",
       "8  {web_traffic.website_owner, sec_master.entity_...  \n",
       "9     {weather.zip_code, foot_traffic.location_name}  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "  MATCH (n1:column), (n2:column)\n",
    "  WHERE n1.source <> n2.source\n",
    "  AND (n1.col_type='reference' and n2.col_type='reference')\n",
    "  RETURN n1.source, n1.name , n2.source, n2.name, vector.similarity.cosine(n1.embedding, n2.embedding) as similarity\n",
    "\"\"\"\n",
    "result = kg.query(query)\n",
    "# result\n",
    "df = pd.DataFrame(result)\n",
    "s1 = df['n1.source']+'.'+df['n1.name']\n",
    "s2 = df['n2.source']+'.'+df['n2.name']\n",
    "df['unique'] = pd.Series(pd.concat([s1, s2], axis=1).values.tolist()).apply(set)\n",
    "df.sort_values('similarity',ascending=False,inplace=True)\n",
    "df = df.drop_duplicates('unique').reset_index(drop=True)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method 2\n",
    "Non rag - we are giving the column descriptions in the prompts for two columns and we are asking if they are the same."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_column_details(col_name,col_source):\n",
    "    query = f\"\"\"\n",
    "    match (c:column)\n",
    "    where c.name = '{col_name}' and c.source = '{col_source}'\n",
    "    return c.name, c.description, c.values\n",
    "    \"\"\"\n",
    "    result = kg.query(query)\n",
    "\n",
    "    output_string = \"\"\n",
    "    for key, value in result[0].items():\n",
    "        output_string += f\"{key.strip('.').replace('c.', '')}: {value},\\n\"\n",
    "    return output_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\saeed\\anaconda3\\envs\\conda38\\lib\\site-packages\\langchain_core\\_api\\deprecation.py:139: LangChainDeprecationWarning: The class `OpenAI` was deprecated in LangChain 0.0.10 and will be removed in 0.3.0. An updated version of the class exists in the langchain-openai package and should be used instead. To use it run `pip install -U langchain-openai` and import as `from langchain_openai import OpenAI`.\n",
      "  warn_deprecated(\n",
      "c:\\Users\\saeed\\anaconda3\\envs\\conda38\\lib\\site-packages\\langchain_core\\_api\\deprecation.py:139: LangChainDeprecationWarning: The class `LLMChain` was deprecated in LangChain 0.1.17 and will be removed in 0.3.0. Use RunnableSequence, e.g., `prompt | llm` instead.\n",
      "  warn_deprecated(\n"
     ]
    }
   ],
   "source": [
    "prompt_template = PromptTemplate.from_template(\n",
    "\"\"\"\n",
    "You are given two columns from different tables that need to be compared to determine if they represent the same identifiers. \n",
    "Here are the details of the columns:\n",
    "\n",
    "Column 1:\n",
    "{col1}\n",
    "\n",
    "Column 2:\n",
    "{col2}\n",
    "\n",
    "Compare these columns and answer the following questions:\n",
    "1.Do these columns seem to represent the same type of identifier based on name and the description?\n",
    "2.Based on the sample values, can we infer that these values are similar enough that they are taken from the same identifier ?\n",
    "\"\"\"\n",
    ")\n",
    "prompt_chain = LLMChain(llm=OpenAI(temperature=0.5),prompt=prompt_template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: zip_code,\n",
      "description: ZIP code of the location where the data was collected.,\n",
      "values: 10001,\n",
      "\n",
      "name: post_code,\n",
      "description: Postal code where the location is situated.,\n",
      "values: 10001,73070,\n",
      "\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\saeed\\anaconda3\\envs\\conda38\\lib\\site-packages\\langchain_core\\_api\\deprecation.py:139: LangChainDeprecationWarning: The method `Chain.run` was deprecated in langchain 0.1.0 and will be removed in 0.3.0. Use invoke instead.\n",
      "  warn_deprecated(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "1. Yes, based on the name and description, it appears that both columns represent some type of geographic identifier related to a location.\n",
      "2. Based on the sample values, it is likely that these values are taken from the same identifier. Both values are numerical and have the same number of digits, and the values in column 2 are a subset of the values in column 1. However, without more context or information it is not possible to definitively say that they are from the same identifier. \n"
     ]
    }
   ],
   "source": [
    "row = df.iloc[2]\n",
    "\n",
    "col_name = row['n2.name']\n",
    "col_source = row['n2.source']\n",
    "col2 = get_column_details(col_name,col_source)\n",
    "print(col2)\n",
    "\n",
    "col_name = row['n1.name']\n",
    "col_source = row['n1.source']\n",
    "col1 = get_column_details(col_name,col_source)\n",
    "print(col1)\n",
    "print()\n",
    "result = prompt_chain.run(col1=col1, col2=col2)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: entity_name,\n",
      "description: Name of the company or entity underlying the security.,\n",
      "values: Starbucks Corp,Tesla Inc,Alphabet Inc,Nvidia Corp,Nike Inc,Costco Wholesale Corp,Apple Inc,\n",
      "\n",
      "name: symbol,\n",
      "description: Stock symbol or identifier for the business.,\n",
      "values: SBUX,CVS,\n",
      "\n",
      "\n",
      "1. Yes, these columns seem to represent the same type of identifier based on the name and description. Both columns are related to identifying businesses or entities, with column 1 specifically mentioning stock symbols and column 2 mentioning company or entity names.\n",
      "\n",
      "2. No, we cannot infer that these values are similar enough to be taken from the same identifier. While some of the values in both columns may match (e.g. SBUX and Starbucks Corp), there are also values in column 2 that do not have a corresponding match in column 1 (e.g. Tesla Inc, Alphabet Inc). Additionally, the values in column 2 are not limited to just stock symbols, as there are also company names listed. Therefore, we cannot assume that the values in these columns are taken from the same identifier.\n"
     ]
    }
   ],
   "source": [
    "row = df.iloc[3]\n",
    "\n",
    "col_name = row['n2.name']\n",
    "col_source = row['n2.source']\n",
    "col2 = get_column_details(col_name,col_source)\n",
    "print(col2)\n",
    "\n",
    "col_name = row['n1.name']\n",
    "col_source = row['n1.source']\n",
    "col1 = get_column_details(col_name,col_source)\n",
    "print(col1)\n",
    "print()\n",
    "result = prompt_chain.run(col1=col1, col2=col2)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: security_name,\n",
      "description: Name of the security.,\n",
      "values: Starbucks Corp,Tesla Inc,Alphabet Inc-Cl A,Nvidia Corp,Nike Inc -Cl B,Costco Wholesale Corp,Apple Inc,\n",
      "\n",
      "name: symbol,\n",
      "description: Stock symbol or identifier for the business.,\n",
      "values: SBUX,CVS,\n",
      "\n",
      "\n",
      "\n",
      "1. Yes, these columns appear to represent the same type of identifier. Both columns contain names or symbols that represent a specific business or company.\n",
      "\n",
      "2. No, based on the sample values, we cannot infer that these values are similar enough to be taken from the same identifier. While some values in column 2 (security_name) may match with values in column 1 (symbol), there are also values in column 2 that do not have a direct match in column 1. Therefore, we cannot assume that all values in column 2 are taken from the same identifiers as those in column 1.\n"
     ]
    }
   ],
   "source": [
    "row = df.iloc[6]\n",
    "col_name = row['n2.name']\n",
    "col_source = row['n2.source']\n",
    "col2 = get_column_details(col_name,col_source)\n",
    "print(col2)\n",
    "col_name = row['n1.name']\n",
    "col_source = row['n1.source']\n",
    "col1 = get_column_details(col_name,col_source)\n",
    "print(col1)\n",
    "print()\n",
    "\n",
    "result = prompt_chain.run(col1=col1, col2=col2)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: page_owner,\n",
      "description: Name of the owner or brand of the Instagram page.,\n",
      "values: Nike,Adidas,\n",
      "\n",
      "name: website_owner,\n",
      "description: Corporation or entity that owns the website.,\n",
      "values: Costco Wholesale Corporation,Nike Inc,\n",
      "\n",
      "\n",
      "1. Based on the name and description, it appears that these columns represent the same type of identifier, as they both pertain to ownership of a brand or entity.\n",
      "2. It is possible that the values in these columns are taken from the same identifier, as both columns contain values from well-known corporations and brands in the retail industry. However, without further context or information, it is not possible to definitively infer that these values are similar enough to be from the same identifier. \n"
     ]
    }
   ],
   "source": [
    "row = df.iloc[4]\n",
    "col_name = row['n2.name']\n",
    "col_source = row['n2.source']\n",
    "col2 = get_column_details(col_name,col_source)\n",
    "print(col2)\n",
    "col_name = row['n1.name']\n",
    "col_source = row['n1.source']\n",
    "col1 = get_column_details(col_name,col_source)\n",
    "print(col1)\n",
    "print()\n",
    "\n",
    "result = prompt_chain.run(col1=col1, col2=col2)\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "# these are the pairs that we know are supposed to match\n",
    "int_pairs = [[{'name':'post_code','source':'foot_traffic'}, {'name':'zip_code','source':'weather'}],\n",
    "[{'name':'symbol','source':'foot_traffic'}, {'name':'ticker','source':'sec_master'}],\n",
    "[{'name':'website_owner','source':'web_traffic'}, {'name':'entity_name','source':'sec_master'}],\n",
    "[{'name':'website_brand','source':'web_traffic'}, {'name':'page_owner','source':'social_media'}]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: post_code,\n",
      "description: Postal code where the location is situated.,\n",
      "values: 10001,73070,\n",
      "\n",
      "name: zip_code,\n",
      "description: ZIP code of the location where the data was collected.,\n",
      "values: 10001,\n",
      "\n",
      "\n",
      "\n",
      "1. Yes, these columns seem to represent the same type of identifier based on their names and descriptions. Both columns are related to location and have codes that identify specific areas.\n",
      "\n",
      "2. Based on the sample values, it is possible that these values are similar enough that they are taken from the same identifier. The value \"10001\" appears in both columns, which suggests that they could be referring to the same location. However, without more information it is not possible to definitively say that they are from the same identifier. It is also possible that they could be referring to different locations with the same code.\n",
      "--------------------------------------------------\n",
      "name: symbol,\n",
      "description: Stock symbol or identifier for the business.,\n",
      "values: SBUX,CVS,\n",
      "\n",
      "name: ticker,\n",
      "description: Stock ticker symbol representing the security.,\n",
      "values: SBUX,TSLA,GOOGL,NVDA,NKE,COST,AAPL,\n",
      "\n",
      "\n",
      "\n",
      "1. Yes, both columns seem to represent stock symbols or identifiers for businesses.\n",
      "2. No, based on the sample values, we cannot infer that these values are similar enough to be taken from the same identifier. While there is one matching value (SBUX), the other values (TSLA, GOOGL, NVDA, NKE, COST, AAPL) are different. This could indicate that the columns are representing different sets of identifiers or that they are not fully comprehensive. Further analysis would be needed to determine if these columns represent the same identifiers.\n",
      "--------------------------------------------------\n",
      "name: website_owner,\n",
      "description: Corporation or entity that owns the website.,\n",
      "values: Costco Wholesale Corporation,Nike Inc,\n",
      "\n",
      "name: entity_name,\n",
      "description: Name of the company or entity underlying the security.,\n",
      "values: Starbucks Corp,Tesla Inc,Alphabet Inc,Nvidia Corp,Nike Inc,Costco Wholesale Corp,Apple Inc,\n",
      "\n",
      "\n",
      "1. Yes, both columns seem to represent the same type of identifier, which is the name of a corporation or entity.\n",
      "2. Yes, based on the sample values, we can infer that they are similar enough to be taken from the same identifier. Both columns contain the names of well-known companies, and there is an overlap in values between the two columns (Nike Inc and Costco Wholesale Corp). This suggests that both columns are referencing the same type of entity, which is a corporation or company.\n",
      "--------------------------------------------------\n",
      "name: website_brand,\n",
      "description: Brand name associated with the website.,\n",
      "values: Costco,Nike,\n",
      "\n",
      "name: page_owner,\n",
      "description: Name of the owner or brand of the Instagram page.,\n",
      "values: Nike,Adidas,\n",
      "\n",
      "\n",
      "1. Yes, these columns seem to represent the same type of identifier based on the name and description. Both columns are related to brands and their associated names.\n",
      "\n",
      "2. Based on the sample values, we can infer that these values are similar enough that they are taken from the same identifier. Both columns have Nike as a value, indicating that it is a common identifier between the two columns. However, it is not possible to determine if all the values are taken from the same identifier without more information.\n",
      "--------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for p in int_pairs:\n",
    "    col1 = get_column_details(p[0]['name'],p[0]['source'])\n",
    "    col2 = get_column_details(p[1]['name'],p[1]['source'])\n",
    "    print(col1)\n",
    "    print(col2)\n",
    "    print()\n",
    "    result = prompt_chain.run(col1=col1, col2=col2)\n",
    "    print(result)\n",
    "    print(\"--------------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method 3\n",
    "Asking what columns are similar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Received notification from DBMS server: {severity: WARNING} {code: Neo.ClientNotification.Statement.FeatureDeprecationWarning} {category: DEPRECATION} {title: This feature is deprecated and will be removed in future versions.} {description: The query used a deprecated procedure. ('db.create.setVectorProperty' has been replaced by 'db.create.setNodeVectorProperty')} {position: line: 1, column: 72, offset: 71} for query: \"UNWIND $data AS row MATCH (n:`embeddable`) WHERE elementId(n) = row.id CALL db.create.setVectorProperty(n, 'embedding', row.embedding) YIELD node RETURN count(*)\"\n"
     ]
    }
   ],
   "source": [
    "neo4j_vector_store = Neo4jVector.from_existing_graph(\n",
    "    embedding=OpenAIEmbeddings(),\n",
    "    url=NEO4J_URI,\n",
    "    username=NEO4J_USERNAME,\n",
    "    password=NEO4J_PASSWORD,\n",
    "    index_name='embedded_index',\n",
    "    node_label='embeddable',\n",
    "    text_node_properties=['name','type','description','values'], \n",
    "    embedding_node_property='embedding',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {},
   "outputs": [],
   "source": [
    "template = \"\"\"\n",
    "You are given the below details about a column, find columns that represent the same identifier. There could be more than one match.\n",
    "Use only the information from the given context\n",
    "Compare these columns based on the flowing criterias and provide an explanation.\n",
    "1.Do these columns seem to represent the same type of identifier based on name and the description?\n",
    "2.Compare the sample values to infer if these values are similar enough that they are taken from the same identifier, they don't have to be the same.\n",
    "\n",
    "Column details:\n",
    "{col}\n",
    "\n",
    "Retrieved information:\n",
    "{context}\n",
    "\"\"\"\n",
    "\n",
    "prompt = ChatPromptTemplate.from_template(template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = ChatOpenAI(model_name=\"gpt-3.5-turbo\", temperature=0)\n",
    "rag_chain = (\n",
    "    {\"context\": neo4j_vector_store.as_retriever(search_kwargs={'k': 4}), \"col\": RunnablePassthrough()}\n",
    "    | prompt\n",
    "    | llm\n",
    "    | StrOutputParser()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "metadata": {},
   "outputs": [],
   "source": [
    "# these are the pairs that we know are supposed to match\n",
    "int_pairs = [[{'name':'post_code','source':'foot_traffic'}, {'name':'zip_code','source':'weather'}],\n",
    "[{'name':'symbol','source':'foot_traffic'}, {'name':'ticker','source':'sec_master'}],\n",
    "[{'name':'website_owner','source':'web_traffic'}, {'name':'entity_name','source':'sec_master'}],\n",
    "[{'name':'website_brand','source':'web_traffic'}, {'name':'page_owner','source':'social_media'}]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: post_code,\n",
      "description: Postal code where the location is situated.,\n",
      "values: 10001,73070,\n",
      "\n",
      "\n",
      "Based on the information provided, the columns \"post_code\" and \"zip_code\" seem to represent the same type of identifier. Both columns refer to codes related to the location where the data was collected, with \"post_code\" specifically mentioning postal code and \"zip_code\" mentioning ZIP code. The values in both columns also include the same value \"10001\", which further supports the idea that they represent the same type of identifier.\n",
      "\n",
      "Therefore, based on the name, description, and sample values, it can be inferred that the columns \"post_code\" and \"zip_code\" likely represent the same type of identifier.\n",
      "---------------------------------------------\n",
      "name: symbol,\n",
      "description: Stock symbol or identifier for the business.,\n",
      "values: SBUX,CVS,\n",
      "\n",
      "\n",
      "Based on the information provided, the columns \"symbol\" and \"ticker\" seem to represent the same type of identifier based on name and description. Both columns are related to stock symbols or identifiers for businesses. The values in both columns include \"SBUX\", which indicates that they are likely referring to the same identifier.\n",
      "\n",
      "While the \"symbol\" column only includes \"SBUX\" and \"CVS\" as values, the \"ticker\" column includes additional values such as \"TSLA\", \"GOOGL\", \"NVDA\", \"NKE\", \"COST\", and \"AAPL\". However, since both columns share the common value \"SBUX\", it is likely that they are representing the same type of identifier, even though the values are not exactly the same.\n",
      "\n",
      "Therefore, based on the name, description, and shared value \"SBUX\", it can be inferred that the \"symbol\" and \"ticker\" columns represent the same type of identifier.\n",
      "---------------------------------------------\n",
      "name: website_owner,\n",
      "description: Corporation or entity that owns the website.,\n",
      "values: Costco Wholesale Corporation,Nike Inc,\n",
      "\n",
      "\n",
      "Based on the information provided, the columns \"website_owner\" and \"website_brand\" seem to represent the same type of identifier. \n",
      "\n",
      "1. Both columns have similar names and descriptions related to ownership or association with a website. The name \"website_owner\" and the description \"Corporation or entity that owns the website\" align closely with the name \"website_brand\" and the description \"Brand name associated with the website.\"\n",
      "\n",
      "2. When comparing the sample values, \"Costco Wholesale Corporation\" from the \"website_owner\" column and \"Costco\" from the \"website_brand\" column are related as they both refer to the same entity. Similarly, \"Nike Inc\" from the \"website_owner\" column and \"Nike\" from the \"website_brand\" column are also related as they refer to the same entity.\n",
      "\n",
      "Therefore, based on the name, description, and sample values, it can be inferred that the columns \"website_owner\" and \"website_brand\" represent the same type of identifier related to the ownership or association with a website.\n",
      "---------------------------------------------\n",
      "name: website_brand,\n",
      "description: Brand name associated with the website.,\n",
      "values: Costco,Nike,\n",
      "\n",
      "\n",
      "Based on the information provided, the columns \"website_brand\" and \"website_owner\" seem to represent the same type of identifier based on name and description. Both columns are related to brands associated with websites. However, the values in the \"website_brand\" column (Costco, Nike) are not exactly the same as the values in the \"website_owner\" column (Costco Wholesale Corporation, Nike Inc). \n",
      "\n",
      "While the values are related (Costco and Nike are brands associated with the websites owned by Costco Wholesale Corporation and Nike Inc), they are not identical. This suggests that the columns may be related but not representing the exact same identifier.\n",
      "---------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for p in int_pairs:\n",
    "    col1 = get_column_details(p[0]['name'],p[0]['source'])\n",
    "    col2 = get_column_details(p[1]['name'],p[1]['source'])\n",
    "    print(col1)\n",
    "    # print(col2)\n",
    "    print(rag_chain.invoke(col1))\n",
    "    print(\"---------------------------------------------------------------------------------------------------------------------------------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Method 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [],
   "source": [
    "contextualize_query = \"\"\"\n",
    "match (node)-[:RELATED_TO]-(c:column)\n",
    "with ('column:'+'\\n'+'name:'+ node.name +'\\n'+'type:'+node.type+'\\n'+'description:'+node.description +'\\n'+'values:'+node.values) as self,\n",
    "reduce(s=\"\", item in collect(c) | s + \"\\n\\n\" + 'column:'+'\\n' + 'name:'+item.name +'\\n'+ 'description:'+item.description +'\\n'+ 'values:'+item.values ) as c_name,\n",
    "score, {source: ' '} as metadata limit 1\n",
    "return (self +'\\n'+ c_name) as text, score, metadata  \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [],
   "source": [
    "neo4j_vector_store = Neo4jVector.from_existing_index(\n",
    "    embedding=OpenAIEmbeddings(),\n",
    "    url=NEO4J_URI,\n",
    "    username=NEO4J_USERNAME,\n",
    "    password=NEO4J_PASSWORD,\n",
    "    index_name='embedded_index',\n",
    "    retrieval_query = contextualize_query\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {},
   "outputs": [],
   "source": [
    "template = \"\"\"\n",
    "You are given the below details about a column, find columns that represent the same identifier. There could be more than one match.\n",
    "Use only the information from the given context\n",
    "Compare these columns based on the flowing criterias and provide an explanation.\n",
    "1.Do these columns seem to represent the same type of identifier based on name and the description?\n",
    "2.Compare the sample values to infer if these values are similar enough that they are taken from the same identifier, they don't have to be the same.\n",
    "\n",
    "Column details:\n",
    "{col}\n",
    "\n",
    "Retrieved information:\n",
    "{context}\n",
    "\"\"\"\n",
    "\n",
    "prompt = ChatPromptTemplate.from_template(template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [],
   "source": [
    "llm = ChatOpenAI(model_name=\"gpt-3.5-turbo\", temperature=0)\n",
    "rag_chain = (\n",
    "    {\"context\": neo4j_vector_store.as_retriever(search_kwargs={'k': 4}), \"col\": RunnablePassthrough()}\n",
    "    | prompt\n",
    "    | llm\n",
    "    | StrOutputParser()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = \"\"\"\n",
    "name: ZIPCODE,\n",
    "description: location zip code where the data was collected\n",
    "values: 07304, 10004,\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Based on the information provided, the columns that could potentially represent the same identifier as \"ZIPCODE\" are \"zip_code\" and \"post_code\".\n",
      "\n",
      "1. Comparing the names and descriptions:\n",
      "- \"zip_code\" and \"ZIPCODE\" both refer to a code related to the location where the data was collected. They seem to represent the same type of identifier based on the similarity in name and description.\n",
      "- \"post_code\" also seems to represent a similar identifier as it refers to a postal code where the location is situated. While the name is slightly different, the description indicates a similar concept.\n",
      "\n",
      "2. Comparing the sample values:\n",
      "- The sample value \"10001\" in the \"zip_code\" column matches one of the values in the \"ZIPCODE\" column, indicating a potential match.\n",
      "- The sample value \"10001\" in the \"post_code\" column also matches one of the values in the \"ZIPCODE\" column, further suggesting a potential match.\n",
      "\n",
      "Based on the above analysis, both \"zip_code\" and \"post_code\" columns seem to represent the same type of identifier as \"ZIPCODE\" based on the name, description, and sample values provided.\n"
     ]
    }
   ],
   "source": [
    "print(rag_chain.invoke(col))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda38",
   "language": "python",
   "name": "python3"
  },
  "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.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}