soilwise_dublincore-V2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "984cb3f6-89d0-44a9-aad8-47e25d6fd785",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from py2neo import Graph, Node, Relationship\n",
    "from neo4j import GraphDatabase\n",
    "from graphdatascience import GraphDataScience\n",
    "import requests\n",
    "from datetime import datetime\n",
    "import string\n",
    "import json\n",
    "import xml.etree.ElementTree as ET\n",
    "import httpx\n",
    "from lxml import html\n",
    "import nltk\n",
    "import re\n",
    "\n",
    "nltk.download(\"stopwords\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c005154-216b-4cf2-a6d6-383e49201868",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Opening config file -> this config file is found in a config folder, the config structure is:\n",
    "# {access_token_zenodo: \"......\"\n",
    "# openai_api_key: \"......\"}\n",
    "\n",
    "config = open('config/config.json', 'r')\n",
    "config = json.load(config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ddce98c-0476-4faf-aba2-0c956cdb14cd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# possible to use OAI-PMH (Open Archives Initiative Protocol for Metadata Harvesting) to extract zenodo and other data\n",
    "# however, see no option to select for 'soil', this can be fed as parameter when using zenodo API directly, so worked further with Zenoodo API\n",
    "base_url = \"https://zenodo.org/oai2d\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8cc66b1-9cf5-453e-9d89-93dd8e44efe6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Define the API endpoint URL\n",
    "api_url = \"https://zenodo.org/api/records\"\n",
    "\n",
    "# query for datasources with 'soil'\n",
    "# get first 200 datasources\n",
    "# loop through pages to retrieve next part of data\n",
    "# only get sources with status published\n",
    "#  options: https://developers.zenodo.org/?python#list36\n",
    "\n",
    "# list to gather all data through API requests\n",
    "zenodo_data = []\n",
    "\n",
    "# loop through pages to retreive next part of data\n",
    "for i in range(1,11):\n",
    "    print(f'loop num {i}')\n",
    "    params = {'q': 'soil',\n",
    "              'size':'20',\n",
    "              'page':f'{i}',\n",
    "              'status':'published',\n",
    "              'access_token': config['access_token_zenodo']}\n",
    "    headers = {\"Content-Type\": \"application/json\"}\n",
    "\n",
    "    # Make the API request\n",
    "    response = requests.get(api_url,params = params, headers=headers)\n",
    "\n",
    "    # Check if the request was successful\n",
    "    if response.status_code == 200:\n",
    "        # Parse the JSON response\n",
    "        data = response.json()\n",
    "        zenodo_data = zenodo_data + data['hits']['hits']\n",
    "    else:\n",
    "        print(f\"Failed to retrieve data. Status code: {response.status_code}\")\n",
    "    \n",
    "zenodo_data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbc3c317-b8f9-455f-b330-742a0e1e3363",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# gather the id's in list to then extract data directly as dublincore representation\n",
    "id_list = []\n",
    "for i in range(0,len(zenodo_data)):\n",
    "    id_list.append(zenodo_data[i]['id'])\n",
    "\n",
    "print(f'length of list is {len(id_list)}, first 10 ids are: {id_list[:10]}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd0589c4-a86b-4166-a1b8-af546af54008",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#NLP functions\n",
    "\n",
    "# Generate a set of stop words to remove from text.\n",
    "stoplist_base = \"\"\" the is and are of\"\"\"\n",
    "stoplist_symbols = \"km +/- _ … -\"\n",
    "\n",
    "stopwords = {\n",
    "        w\n",
    "        for stoplist in [\n",
    "            stoplist_base.split(),\n",
    "            stoplist_symbols.split(),\n",
    "            nltk.corpus.stopwords.words(\"english\"),\n",
    "        ]\n",
    "        for w in stoplist\n",
    "        if w  # \"if w\" makes that no empty strings ('') are added\n",
    "    }\n",
    "\n",
    "# Genereate patterns for cleaning up text\n",
    "tags_websites_emails_pattern = re.compile(r\"<[^>]+>|\\n\\n|\\n|\\r|http\\S+|www\\S+|\\S+@\\S+|/\")\n",
    "punctuation_pattern = re.compile(f\"[{string.punctuation}]\")\n",
    "numbers_pattern = re.compile(r\"\\b\\d+(?:\\.\\d+)?\\s+\")\n",
    "time_pattern = re.compile(r\"(?:\\d+[uh:](?:\\d\\d+)?)\\b\")\n",
    "\n",
    "def clean_normalize(row):\n",
    "    \"\"\"Clean and normalize the text data.\"\"\"\n",
    "    row = row.lower()\n",
    "    row = re.sub(tags_websites_emails_pattern, \" \", row)\n",
    "    row = re.sub(punctuation_pattern, \" \", row)\n",
    "    row = re.sub(numbers_pattern, \" \", row)  # replace numbers\n",
    "    row = re.sub(time_pattern, \" \", row)  # replace time indications\n",
    "    row = re.sub(r\"(\\d+\\S*)\\b\", \" \", row)  # drop all numbers with optional word\n",
    "    row = re.sub(r\"\\W\", \" \", row, flags=re.I)  # remove all the non-word characters\n",
    "    row = re.sub(r\"\\s+\", \" \", row).strip()  # replace multiple white strings\n",
    "    return row\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96b68e86-51ef-42cd-81fa-808764d8ed41",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# extract DublinCore standardized metadata and generate json with all data\n",
    "zenodo_dc_data = {}\n",
    "for i in range(len(id_list)):\n",
    "    # print(i)\n",
    "\n",
    "    # Define the API endpoint URL\n",
    "    api_url = f\"https://zenodo.org/records/{id_list[i]}/export/dublincore\"\n",
    "    params = {'access_token': config['access_token_zenodo']}\n",
    "    headers = {\"Content-Type\": \"application/json\"}\n",
    "\n",
    "    # Make the API request\n",
    "    response = requests.get(api_url,params = params, headers=headers)\n",
    "\n",
    "    # Check if the request was successful\n",
    "    if response.status_code == 200:\n",
    "        try:\n",
    "            # Parse the XML response\n",
    "            root = ET.fromstring(response.content)\n",
    "\n",
    "            # Check if the root has child elements\n",
    "            if root:\n",
    "                # Find the dc:identifier element\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}identifier')\n",
    "                identifier_element = element.text if element is not None else None\n",
    "\n",
    "                # Find the dc:title element and clean & normalize the title with NLP functions\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}title')\n",
    "                title_element = [x.lower() for x in clean_normalize(element.text).split()] if element is not None else None\n",
    "                title_element = [token for token in title_element if token not in stopwords] if title_element is not None else None\n",
    "\n",
    "                 # Find the dc:date element\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}date')\n",
    "                date_element = element.text if element is not None else None\n",
    "\n",
    "                 # Find the dc:description element\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}description')\n",
    "                description_element = element.text if element is not None else None\n",
    "\n",
    "                # Find the dc:creator element\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}creator')\n",
    "                creator_element = [x.lower() for x in re.sub(r\"'\", \"\", element.text).split(',')] if element is not None else None\n",
    "\n",
    "                # Find the dc:subject element\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}subject')\n",
    "                subject_element = [x.lower() for x in re.sub(r\"'\", \"\", element.text).split(',')] if element is not None else None\n",
    "\n",
    "                # Find the dc:type element\n",
    "                element = root.find('.//{http://purl.org/dc/elements/1.1/}type')\n",
    "                type_element = element.text if element is not None else None\n",
    "\n",
    "                # combine to dict\n",
    "                zenodo_dict={\n",
    "                    i:{\n",
    "                        \"dc:identifier\": identifier_element,\n",
    "                        \"dc:title\": title_element,\n",
    "                        \"dc:date\": date_element,\n",
    "                        \"dc:description\": description_element,\n",
    "                        \"dc:creator\": creator_element,\n",
    "                        \"dc:subject\": subject_element,\n",
    "                        \"dc:type\": type_element\n",
    "                    }\n",
    "                }\n",
    "                \n",
    "                #add to general json\n",
    "                zenodo_dc_data.update(zenodo_dict)\n",
    "\n",
    "            else:\n",
    "                print(\"No repositories found in the response.\")\n",
    "        except ET.ParseError:\n",
    "            print(\"Error: Unable to parse XML content.\")\n",
    "    else:\n",
    "        # Print an error message if the request was not successful\n",
    "        print(f\"Error: Unable to fetch data. Status code: {response.status_code}\")\n",
    "        print(response.text)\n",
    "\n",
    "    \n",
    "zenodo_dc_data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "396a8308-6565-425a-b3a8-096ba5a037ee",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# test, see title of certain datasource\n",
    "zenodo_data[0]['metadata']['title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce4a8dcf-a983-4db0-86de-3a579f40f9a4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Connect to local neo4J db\n",
    "neo4j_config = {\n",
    "    'pathway': '.....',\n",
    "    'username': '.....',\n",
    "    'password': '.....',\n",
    "}\n",
    "\n",
    "pathway = neo4j_config['pathway']\n",
    "username = neo4j_config['username']\n",
    "password = neo4j_config['password']\n",
    "\n",
    "driver = GraphDatabase.driver(pathway, auth=(username, password))\n",
    "graph = Graph(pathway, auth=(username, password))  # Replace with your database credentials\n",
    "gds = GraphDataScience(pathway, auth=(username, password))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a797d5a-59d0-4ba1-8f83-f4901cae35cd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# drop existing data in Neo4J graph\n",
    "graph.run(\"MATCH (n) DETACH DELETE n\") #delete database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03cb0321-4c3b-49f9-9d31-3221eadf395b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# start converting the data to knowledge graph and inport into Neo4J database\n",
    "# import info for each row\n",
    "def create_nodes_and_relationships(tx, row):\n",
    "    # Define Cypher query to create nodes and relationships\n",
    "    query = (\n",
    "        \"MERGE (node1:SoilwiseDataSource {identifier: $value1})\"\n",
    "        \"MERGE (node2:PublicationDate {publicationDate: $value2})\"\n",
    "        \"MERGE (node3:DataSourceType {dataSourceType: $value3})\"\n",
    "       \n",
    "        \"CREATE (node1)-[:PUBLISHED_ON]->(node2)\" \n",
    "        \"CREATE (node1)-[:TYPE_OF_DATASOURCE]->(node3)\"\n",
    "    )\n",
    "    # Update the query with DataFrame data\n",
    "    parameters = {\n",
    "        \"value1\": row['dc:identifier'],\n",
    "        \"value2\": row['dc:date'],\n",
    "        \"value3\": re.sub(r\"info:eu-repo/semantics/\", \"\", row['dc:type']) \n",
    "    }\n",
    "    # Execute the query\n",
    "    tx.run(query, **parameters)\n",
    "    \n",
    "with driver.session() as session:\n",
    "    for row in range(len(zenodo_dc_data)):\n",
    "        session.execute_write(create_nodes_and_relationships, zenodo_dc_data[row])\n",
    "session.close()\n",
    "\n",
    "\n",
    "def write_creator_nodes_and_relations(tx, row):\n",
    "        node_num = 2\n",
    "        # import general info\n",
    "        query = (\"MERGE (node1:SoilwiseDataSource {identifier: $value1})\" )\n",
    "        # Update the query with  DataFrame data\n",
    "        parameters = { \"value1\": row['dc:identifier'],}\n",
    "        for i in row['dc:creator']:\n",
    "            if i != '':\n",
    "                i = i.strip()\n",
    "                sub_query = (\n",
    "                    f\"MERGE (node{node_num}:Creator {{CreatorName: '{i}'}}) \"\n",
    "                    f\"MERGE (node1)-[:IS_CREATED_BY]->(node{node_num}) \"\n",
    "                )\n",
    "                query += sub_query\n",
    "                node_num += 1  \n",
    "        # Execute the query\n",
    "        tx.run(query, **parameters)\n",
    "\n",
    "\n",
    "with driver.session() as session:\n",
    "    for row in range(len(zenodo_dc_data)):\n",
    "        if zenodo_dc_data[row]['dc:creator']:\n",
    "            session.execute_write(write_creator_nodes_and_relations, zenodo_dc_data[row])\n",
    "session.close()\n",
    "\n",
    "\n",
    "\n",
    "def write_subject_nodes_and_relations(tx, row):\n",
    "        node_num = 2\n",
    "        # import general info\n",
    "        query = (\"MERGE (node1:SoilwiseDataSource {identifier: $value1})\" )\n",
    "        # Update the query with  DataFrame data\n",
    "        parameters = { \"value1\": row['dc:identifier'],}\n",
    "        for i in row['dc:subject']:\n",
    "            if i != '':\n",
    "                i = i.strip()\n",
    "                sub_query = (\n",
    "                    f\"MERGE (node{node_num}:Subject {{SubjectName: '{i}'}}) \"\n",
    "                    f\"MERGE (node1)-[:HAS_SUBJECT]->(node{node_num}) \"\n",
    "                )\n",
    "                query += sub_query\n",
    "                node_num += 1  \n",
    "        # Execute the query\n",
    "        tx.run(query, **parameters)\n",
    "\n",
    "\n",
    "with driver.session() as session:\n",
    "    for row in range(len(zenodo_dc_data)):\n",
    "        if zenodo_dc_data[row]['dc:subject']:\n",
    "            session.execute_write(write_subject_nodes_and_relations, zenodo_dc_data[row])\n",
    "session.close()\n",
    "\n",
    "\n",
    "def write_title_nodes_and_relations(tx, row):\n",
    "        node_num = 2\n",
    "        # import general info\n",
    "        query = (\"MERGE (node1:SoilwiseDataSource {identifier: $value1})\" )\n",
    "        # Update the query with  DataFrame data\n",
    "        parameters = { \"value1\": row['dc:identifier'],}\n",
    "        for i in row['dc:title']:\n",
    "            if i != '':\n",
    "                i = i.strip()\n",
    "                sub_query = (\n",
    "                    f\"MERGE (node{node_num}:Title {{TitleName: '{i}'}}) \"\n",
    "                    f\"MERGE (node1)-[:HAS_TITLE]->(node{node_num}) \"\n",
    "                )\n",
    "                query += sub_query\n",
    "                node_num += 1  \n",
    "        # Execute the query\n",
    "        tx.run(query, **parameters)\n",
    "\n",
    "\n",
    "with driver.session() as session:\n",
    "    for row in range(len(zenodo_dc_data)):\n",
    "        if zenodo_dc_data[row]['dc:title']:\n",
    "            session.execute_write(write_title_nodes_and_relations, zenodo_dc_data[row])\n",
    "session.close()\n",
    "\n",
    "\n",
    "# mossing values were filled in with \"\" or 0, drop these\n",
    "graph.run(\"\"\"\n",
    "MATCH (n)\n",
    "where n.SubjectName = \"\"\n",
    "DETACH DELETE n\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8c2215f-6d68-4531-9bca-f285a89b8242",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# setup LLM to consult knowledge in NEO4J\n",
    "import os\n",
    "\n",
    "from langchain.chat_models import ChatOpenAI\n",
    "from langchain.chains import GraphCypherQAChain\n",
    "from langchain.graphs import Neo4jGraph\n",
    "\n",
    "os.environ['OPENAI_API_KEY'] = config['openai_api_key']\n",
    "\n",
    "graph = Neo4jGraph(\n",
    "    url=neo4j_config['pathway'], \n",
    "    username=neo4j_config['username'], \n",
    "    password=neo4j_config['password'])\n",
    "\n",
    "chain = GraphCypherQAChain.from_llm(\n",
    "    ChatOpenAI(temperature=0), graph=graph, verbose=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c517e792-8502-452f-acf8-d195857623ad",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "Which datasources deal with scotland?\n",
    "\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results.\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17001d0d-8a67-4b17-b19d-6ecd5eeaabc8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "Which datasources have a title about scotland?\n",
    "\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e50acc0-8565-4620-886e-78927581698d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "Which datasources have a subject 'land use'?\n",
    "\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9d93b5e-d649-48ce-bdef-28bfda064727",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "Which datasources have a subject 'cover crops'?\n",
    "\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fae80ebe-6535-4f10-a243-49e76266f7df",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "When was a report published?\n",
    "\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1bc96ba-3a2e-4881-8863-018d00524ebc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "When was a report published?\n",
    "\n",
    "Take into account that the question \n",
    "\"When was a report published?\"\n",
    "should be interpreted as the cypher query:\n",
    "MATCH (s:SoilwiseDataSource)-[:PUBLISHED_ON]->(p:PublicationDate)\n",
    "WHERE (s)-[:TYPE_OF_DATASOURCE]->(:DataSourceType {dataSourceType: 'report'})\n",
    "RETURN p\n",
    "\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "562e8ee7-88fa-40ef-a58e-6f2e618bec26",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# ask queston to knowledge graph\n",
    "\n",
    "chain.run(\"\"\"\n",
    "When was an article published?\n",
    "\n",
    "Take into account that the question \n",
    "\"When was a report published?\"\n",
    "should be interpreted as the cypher query:\n",
    "MATCH (s:SoilwiseDataSource)-[:PUBLISHED_ON]->(p:PublicationDate)\n",
    "WHERE (s)-[:TYPE_OF_DATASOURCE]->(:DataSourceType {dataSourceType: 'report'})\n",
    "RETURN p\n",
    "\n",
    "The following are a datasource when generating the cypher query: article, report, poster, software, other\n",
    "When querying for certain subject always use lowercase.\n",
    "Generate a short answer using all retreived results\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3db3cccd-8bd1-4077-b0df-0f4c557ba096",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# check which graph projections exist\n",
    "gds.graph.list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac00a0f1-a481-4a69-8925-d9c7a52ad7db",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# if necessary, clean up existing projection before making new one\n",
    "G = gds.graph.get('creators_centrality')\n",
    "gds.graph.drop(G)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab033789-fadf-4004-afcb-786dbc4ff5d8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# if necessary, clean up existing projection before making new one\n",
    "G = gds.graph.get('title_similarity')\n",
    "gds.graph.drop(G)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ec8f189-3d86-4416-b0b9-41455582d916",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# if necessary, clean up existing projection before making new one\n",
    "G = gds.graph.get('keyword_similarity')\n",
    "gds.graph.drop(G)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1b19ed7-faf4-4fb4-b4fd-8b9d193a6585",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# create graph projection we are interested in\n",
    "G, result = gds.graph.project(\n",
    "    \"creators_centrality\",  # Graph name\n",
    "    [\"Creator\", \"SoilwiseDataSource\"],  # Node projection\n",
    "    {\"IS_CREATED_BY\": {\"orientation\": \"UNDIRECTED\"}},  # Relationship projection\n",
    "    readConcurrency=4  # Configuration parameters\n",
    ")\n",
    "\n",
    "# stream predictions using \"degree centrality\" algorithm\n",
    "stream_result = gds.degree.stream(G)\n",
    "data_nodes = gds.run_cypher(\n",
    "        \"\"\"\n",
    "          MATCH (n:Creator)\n",
    "          RETURN id(n) as id, n.CreatorName as CreatorName\n",
    "        \"\"\"\n",
    "    )\n",
    "\n",
    "# merge predictions and original data to report\n",
    "data = pd.merge(stream_result, data_nodes, how=\"left\", left_on=\"nodeId\", right_on=\"id\")\n",
    "data = data.dropna()\n",
    "data = data.sort_values('score', ascending=False)\n",
    "data = data[[\"CreatorName\", \"score\"]]\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d4ab7c6-7b2b-4b71-80d4-96d738407dfa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# # create graph projection we are interested in\n",
    "G, result = gds.graph.project(\n",
    "    \"title_similarity\",  # Graph name\n",
    "    [ \"SoilwiseDataSource\", \"Title\"],  # Node projection\n",
    "    {\"HAS_TITLE\": {\"orientation\": \"UNDIRECTED\"}},  # Relationship projection\n",
    "    readConcurrency=4  # Configuration parameters\n",
    ")\n",
    "\n",
    "# stream predictions using \"node similarity\" algorithm\n",
    "stream_result = gds.nodeSimilarity.stream(G)\n",
    "data_nodes = gds.run_cypher(\n",
    "        \"\"\"\n",
    "          MATCH (n:SoilwiseDataSource)\n",
    "          RETURN id(n) as id, n.identifier as SoilwiseDataSource_id\n",
    "        \"\"\"\n",
    "    )\n",
    "\n",
    "# merge predictions and original data to report\n",
    "# merge datasets and rename\n",
    "data = pd.merge(stream_result, data_nodes, how=\"left\", left_on=\"node1\", right_on=\"id\")\n",
    "data = pd.merge(data, data_nodes, how=\"left\", left_on=\"node2\", right_on=\"id\")\n",
    "data = data.rename(columns={\"SoilwiseDataSource_id_x\": \"SoilwiseDataSource_id1\", \"SoilwiseDataSource_id_y\": \"SoilwiseDataSource_id2\"})\n",
    "data = data[[\"SoilwiseDataSource_id1\", \"SoilwiseDataSource_id2\", \"similarity\"]]\n",
    "\n",
    "# make dataframe with distinct title words for each datasource\n",
    "data_titles = gds.run_cypher(\n",
    "        \"\"\"\n",
    "            MATCH (n:SoilwiseDataSource) -[:HAS_TITLE]-> (t:Title)\n",
    "            RETURN n.identifier AS id, COLLECT(DISTINCT t.TitleName) AS distinct_titles\n",
    "        \"\"\"\n",
    "    )\n",
    "data_titles.distinct_titles = data_titles.distinct_titles.apply(lambda x: list(set(x)))\n",
    "\n",
    "# add title words\n",
    "data = pd.merge(data, data_titles, how=\"left\", left_on=\"SoilwiseDataSource_id1\", right_on=\"id\")\n",
    "data = pd.merge(data, data_titles, how=\"left\", left_on=\"SoilwiseDataSource_id2\", right_on=\"id\")\n",
    "\n",
    "# cleanup and select columns to report\n",
    "data = data.dropna()\n",
    "data = data.sort_values('similarity', ascending=False)\n",
    "data = data[[\"SoilwiseDataSource_id1\", \"SoilwiseDataSource_id2\", \"similarity\",\"distinct_titles_x\", \"distinct_titles_y\"]]\n",
    "data = data.rename(columns={\"distinct_titles_x\": \"distinct_titles_1\", \"distinct_titles_y\": \"distinct_titles_2\"})\n",
    "data = data.drop_duplicates(subset=['SoilwiseDataSource_id1', 'SoilwiseDataSource_id2','similarity'])\n",
    "\n",
    "# Create a new column with frozensets of the sorted pairs\n",
    "data['pair_frozenset'] = data.apply(lambda row: frozenset(sorted([row['SoilwiseDataSource_id1'], row['SoilwiseDataSource_id2']])), axis=1)\n",
    "\n",
    "# Drop duplicates based on the frozensets\n",
    "data = data.drop_duplicates(subset='pair_frozenset')\n",
    "\n",
    "# Resetting the index after dropping duplicates\n",
    "data = data.drop(columns='pair_frozenset').reset_index(drop=True)\n",
    "\n",
    "data.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "267db8c0-20a1-47a2-b60a-6260fbb700f4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# create graph projection we are interested in\n",
    "G, result = gds.graph.project(\n",
    "    \"keyword_similarity\",  # Graph name\n",
    "    [ \"SoilwiseDataSource\", \"Subject\"],  # Node projection\n",
    "    {\"HAS_SUBJECT\": {\"orientation\": \"UNDIRECTED\"}},  # Relationship projection\n",
    "    readConcurrency=4  # Configuration parameters\n",
    ")\n",
    "\n",
    "# stream predictions using \"node similarity\" algorithm\n",
    "stream_result = gds.nodeSimilarity.stream(G)\n",
    "data_nodes = gds.run_cypher(\n",
    "        \"\"\"\n",
    "          MATCH (n:SoilwiseDataSource)\n",
    "          RETURN id(n) as id, n.identifier as SoilwiseDataSource_id\n",
    "        \"\"\"\n",
    "    )\n",
    "\n",
    "# merge predictions and original data to report\n",
    "# merge datasets and rename\n",
    "data = pd.merge(stream_result, data_nodes, how=\"left\", left_on=\"node1\", right_on=\"id\")\n",
    "data = pd.merge(data, data_nodes, how=\"left\", left_on=\"node2\", right_on=\"id\")\n",
    "data = data.rename(columns={\"SoilwiseDataSource_id_x\": \"SoilwiseDataSource_id1\", \"SoilwiseDataSource_id_y\": \"SoilwiseDataSource_id2\"})\n",
    "data = data[[\"SoilwiseDataSource_id1\", \"SoilwiseDataSource_id2\", \"similarity\"]]\n",
    "\n",
    "# make dataframe with distinct title words for each datasource\n",
    "data_subjects = gds.run_cypher(\n",
    "        \"\"\"\n",
    "            MATCH (n:SoilwiseDataSource) -[:HAS_SUBJECT]-> (t:Subject)\n",
    "            RETURN n.identifier AS id, COLLECT(DISTINCT t.SubjectName) AS distinct_subjects\n",
    "        \"\"\"\n",
    "    )\n",
    "data_subjects.distinct_subjects = data_subjects.distinct_subjects.apply(lambda x: list(set(x)))\n",
    "\n",
    "# add title words\n",
    "data = pd.merge(data, data_subjects, how=\"left\", left_on=\"SoilwiseDataSource_id1\", right_on=\"id\")\n",
    "data = pd.merge(data, data_subjects, how=\"left\", left_on=\"SoilwiseDataSource_id2\", right_on=\"id\")\n",
    "\n",
    "# cleanup and select columns to report\n",
    "data = data.dropna()\n",
    "data = data.sort_values('similarity', ascending=False)\n",
    "data = data[[\"SoilwiseDataSource_id1\", \"SoilwiseDataSource_id2\", \"similarity\",\"distinct_subjects_x\", \"distinct_subjects_y\"]]\n",
    "data = data.rename(columns={\"distinct_subjects_x\": \"distinct_subjects_1\", \"distinct_subjects_y\": \"distinct_subjects_2\"})\n",
    "data = data.drop_duplicates(subset=['SoilwiseDataSource_id1', 'SoilwiseDataSource_id2','similarity'])\n",
    "\n",
    "# Create a new column with frozensets of the sorted pairs\n",
    "data['pair_frozenset'] = data.apply(lambda row: frozenset(sorted([row['SoilwiseDataSource_id1'], row['SoilwiseDataSource_id2']])), axis=1)\n",
    "\n",
    "# Drop duplicates based on the frozensets\n",
    "data = data.drop_duplicates(subset='pair_frozenset')\n",
    "\n",
    "# Resetting the index after dropping duplicates\n",
    "data = data.drop(columns='pair_frozenset').reset_index(drop=True)\n",
    "\n",
    "data.head(100)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}