OpenAlex Import to Neo4j.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e39c47a2-164b-4510-81e0-d0920099906c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download and install Python packages needed for this Jupyter Notebook\n",
    "\n",
    "!pip install neo4j pyalex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad7a038d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This imports the Python packages needed for this Jupyter Notebook \n",
    "\n",
    "# Note: 'ast' 'json' and 'os' are part of the Python Standard Library\n",
    "# If not already included in your Python installer, \n",
    "# they will need to be installed manually \n",
    "\n",
    "import pyalex\n",
    "from pyalex import Works, Authors, Sources, Institutions, Concepts, Publishers\n",
    "import json \n",
    "from neo4j import GraphDatabase\n",
    "import os\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ce953ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The block connect the Jupyter Notebook to your Neo4j Database\n",
    "# Note: Your Neo4j Database must be running and accepting connections\n",
    "# Note: This example is for connecting to a local instance of Neo4j\n",
    "# More information on interfacing with can be found at\n",
    "# https://neo4j.com/docs/python-manual/current/connect/\n",
    "\n",
    "uri = 'bolt://localhost:7687'\n",
    "username = 'neo4j'\n",
    "password = 'password'\n",
    "driver = GraphDatabase.driver(uri, auth=(username, password))\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30602c32-de45-4ac3-8f84-c244d9afad8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This block is to provide your email address to OpenAlex for best performance\n",
    "# More information can be found at https://docs.openalex.org/\n",
    "\n",
    "pyalex.config.email = \"Enter your email address here\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94b1d1a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This block will retrieve all works from the OpenAlex Concept ID \n",
    "# provided. Hitting enter will select \"Classroom Design\", which \n",
    "# contains a single page with 99 Works entries. Some broader concepts \n",
    "#like \"Computer Science\" can contain tens of thousands of Works. \n",
    "\n",
    "concept_input = input('Please enter an OpenAlex Concept ID.\\\n",
    " Hit enter for C2991895030 - \"Classroom design\" which will\\\n",
    " return one page with 99 Works: ') \n",
    "\n",
    "if concept_input == \"\":\n",
    "    concept_input = \"C2991895030\"\n",
    "\n",
    "with driver.session() as session:\n",
    "    \n",
    "    # Returns path for neo4j instance running\n",
    "    neodir = session.run('CALL dbms.listConfig() YIELD name, value WHERE \\\n",
    "        name = \\'server.directories.import\\' RETURN value').values()\n",
    "    path = neodir[0][0] \n",
    "\n",
    "    os.chdir(path)\n",
    "    \n",
    "    pager = Works().filter(concept={\"id\" : {concept_input}}).paginate(\\\n",
    "        per_page=200, n_max=None)\n",
    "\n",
    "    page_count = 1\n",
    "\n",
    "    for page in pager:\n",
    "        file = concept_input+\"_Page_\"+str(page_count)\n",
    "        out_file = open(file, \"w\")\n",
    "        json.dump(page, out_file, indent = 6)\n",
    "        out_file.close()\n",
    "        #print(page)\n",
    "        print(\"Now Downloading Page \" + str(page_count) + \\\n",
    "              \" For Concept ID \" + concept_input)\n",
    "        page_count += 1\n",
    "        \n",
    "    directory_list = os.listdir(path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15d975b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This block creates indexes on the following properties to greatly \n",
    "#speed data import and data queries\n",
    "\n",
    "driver.execute_query('CREATE INDEX Institutions IF NOT EXISTS FOR \\\n",
    "    (i:Institutions) ON (i.id)')\n",
    "driver.execute_query('CREATE INDEX Concept IF NOT EXISTS FOR \\\n",
    "    (i:Concept) ON (i.id)')\n",
    "driver.execute_query('CREATE INDEX Work_ID IF NOT EXISTS FOR \\\n",
    "    (i:Work) ON (i.id)')\n",
    "driver.execute_query('CREATE INDEX Author IF NOT EXISTS FOR \\\n",
    "    (i:Author) ON (i.id)')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b028e77",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This block imports the files previously downloaded to create Works nodes\n",
    "# containing all information fields retrieved from OpenAlex\n",
    "\n",
    "with driver.session() as session:\n",
    "    \n",
    "    # Returns path for neo4j instance running\n",
    "    neodir = session.run('CALL dbms.listConfig() YIELD name, value \\\n",
    "    WHERE name = \\'server.directories.import\\' RETURN value').values()\n",
    "    path = neodir[0][0] \n",
    "    #print(path)\n",
    "    \n",
    "    os.chdir(path)\n",
    "\n",
    "\n",
    "directory_list = sorted(os.listdir(path))\n",
    "    \n",
    "for file in directory_list:\n",
    "    if not file.startswith('.'): \n",
    "        print(\"File being imported: \" + file)\n",
    "        work_node_creation =  \\\n",
    "            \"CALL apoc.periodic.iterate(\\\"CALL apoc.load.json(\\'file:///\" \\\n",
    "            + file + \"') YIELD value\\\",\\\"MERGE (w:Work {id: value.id}) \\\n",
    "            SET w.source = \\'OpenAlex\\',  \\\n",
    "            w.cited_by_api_url = coalesce(value.cited_by_api_url, \\'\\'),\\\n",
    "            w.cited_by_count = coalesce(value.cited_by_count, \\'\\'), \\\n",
    "            w.pass = 1, w.corresponding_author_ids  = \\\n",
    "            coalesce(value.corresponding_author_ids , \\'\\'), \\\n",
    "            w.corresponding_institution_ids  = \\\n",
    "            coalesce(value.corresponding_institution_ids , \\'\\'), \\\n",
    "            w.created_date = coalesce(value.created_date, \\'\\'), \\\n",
    "            w.display_name = coalesce(value.display_name, \\'\\'), \\\n",
    "            w.doi = coalesce(value.doi, \\'\\'), \\\n",
    "            w.is_paratext = coalesce(value.is_paratext, \\'\\'), \\\n",
    "            w.is_retracted = coalesce(value.is_retracted, \\'\\'), \\\n",
    "            w.language = coalesce(value.language, \\'\\'), \\\n",
    "            w.locations_count = coalesce(value.locations_count, \\'\\'), \\\n",
    "            w.ngrams_url = coalesce(value.ngrams_url, \\'\\'), \\\n",
    "            w.publication_date = coalesce(value.publication_date, \\'\\'), \\\n",
    "            w.publication_year = coalesce(value.publication_year, \\'\\'), \\\n",
    "            w.publication_year = coalesce(value.publication_year, \\'\\'), \\\n",
    "            w.title = coalesce(value.title, \\'\\'), \\\n",
    "            w.type = coalesce(value.type, \\'\\'), \\\n",
    "            w.updated_date = coalesce(value.updated_date, \\'\\'), \\\n",
    "            w.is_oa = coalesce(value.is_oa, \\'\\'), \\\n",
    "            w.license = coalesce(value.license, \\'\\'), \\\n",
    "            w.url = coalesce(value.url, \\'\\'), \\\n",
    "            w.version = coalesce(value.version, \\'\\') WITH w, value \\\n",
    "            CALL apoc.convert.setJsonProperty(w, \\'inverted_abstract\\', \\\n",
    "            value.abstract_inverted_index) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'authorships\\', value.authorships) \\\n",
    "            CALL apoc.convert.setJsonProperty(w, \\'apc_payment\\', \\\n",
    "            value.apc_payment) CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'best_oa_location\\', value.best_oa_location) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'biblio\\', value.biblio) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'concepts\\', value.concepts) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'counts_by_year\\', value.counts_by_year) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'grants\\', value.grants) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'ids\\', value.ids) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'locations\\', value.locations) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'mesh\\', value.mesh) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'open_access\\', value.open_access) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'primary_location\\', value.primary_location) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'referenced_works\\', value.referenced_works) \\\n",
    "            CALL apoc.convert.setJsonProperty(\\\n",
    "            w, \\'related_works\\', \\\n",
    "            value.related_works)\\\",{ batchSize: 200, \\\n",
    "            parallel: true, retries: 2} ) \\\n",
    "            YIELD batches, total, operations\"\n",
    "\n",
    "        #Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "        #print(work_node_creation)\n",
    "        \n",
    "        record, summary, keys = driver.execute_query(work_node_creation)\n",
    "        print(\"Operations executed during file import - \" + str(record[0][2]))\n",
    "        print(\"File - \" + file + \" import complete\")\n",
    "\n",
    "print(\"All works imported\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17e72939",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves works referenced by existing Works nodes and \n",
    "# creates a REFERENCED_WORK relationship\n",
    "#If the referenced work does not exist it is created using the\n",
    "# id retrieved from the list of \"referenced_works\"\n",
    "#These works are identified with a 2 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the work\n",
    " \n",
    "referenced_node_creation = \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) RETURN w\\\",\\\"WITH \\\n",
    "    apoc.convert.fromJsonList(w.referenced_works) AS ref_works,w \\\n",
    "    UNWIND ref_works AS ref_work WITH ref_work,w \\\n",
    "    MERGE (z:Work {id: ref_work}) SET z.pass = \\\n",
    "    CASE WHEN any (x in z.pass WHERE x = 1) \\\n",
    "    THEN z.pass ELSE 2 END WITH ref_work, z, w \\\n",
    "    MERGE (z)<-[:REFERENCED_WORK]-(w)\\\",{batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(referenced_node_creation)\n",
    "\n",
    "record, summary, keys = driver.execute_query(referenced_node_creation)\n",
    "print(record[0][8])\n",
    "print(\"Referenced work relationships creation complete\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e70374de-acf6-49f9-8dbf-7a8a602bfd47",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves works related to existing Works nodes and \n",
    "# creates a RELATED_WORK relationship\n",
    "#If the related work does not exist it is created using the\n",
    "# id retrieved from the list of \"related_works\"\n",
    "#These works are identified with a 2 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the work\n",
    "\n",
    "related_node_creation = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) RETURN w\\\",\\\"WITH \\\n",
    "    apoc.convert.fromJsonList(w.related_works) AS rel_works,w \\\n",
    "    UNWIND rel_works AS rel_work WITH rel_work,w \\\n",
    "    MERGE (z:Work {id: rel_work}) SET z.pass = \\\n",
    "    CASE WHEN any (x in z.pass WHERE x = 1) THEN z.pass \\\n",
    "    ELSE 2 END WITH rel_work, z, w WHERE z.id <> w.id \\\n",
    "    MERGE (z)<-[:RELATED_WORK]-(w)\\\",{batchSize:200, parallel:false})\"  \n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(related_node_creation)\n",
    "\n",
    "record, summary, keys = driver.execute_query(related_node_creation)\n",
    "print(record[0][8])\n",
    "print(\"Related work relationships creation complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46cc8c0d-9a08-4633-b234-360a9bde9371",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves concepts associated with existing Works nodes and \n",
    "# creates a ASSOC_CONCEPT relationship\n",
    "#If the associated concept does not exist it is created using the\n",
    "# id retrieved from the list of \"concepts\"\n",
    "#Concepts are identified with a 1 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the concept\n",
    "\n",
    "concept_node_creation = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) RETURN w\\\",\\\"WITH \\\n",
    "    apoc.convert.fromJsonList(w.concepts) AS concepts,w UNWIND \\\n",
    "    concepts AS concept MERGE (c:Concept {id: concept.id}) SET \\\n",
    "    c.pass = 1, \\\n",
    "    c.score = concept.score, \\\n",
    "    c.level = concept.level, \\\n",
    "    c.display_name = concept.display_name, \\\n",
    "    c.wikidata = concept.wikidata \\\n",
    "    MERGE (c)<-[:ASSOC_CONCEPT]-(w)\\\",{batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(concept_node_creation)\n",
    "\n",
    "record, summary, keys = driver.execute_query(concept_node_creation)\n",
    "print(record[0][8])\n",
    "print(\"Concept import complete\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "808a0692-b320-4776-bd29-fb1e39507152",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves authors of existing Works nodes and \n",
    "# creates a WROTE relationship that includes a \"author_position\"\n",
    "# property to identify the position of the author's name\n",
    "#If the author does not exist it is created using the\n",
    "# id retrieved from the list of \"authorships\"\n",
    "#Newly crated authors are identified with a 1 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the author\n",
    "\n",
    "author_node_creation = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) RETURN w\\\",\\\"WITH \\\n",
    "    apoc.convert.fromJsonList(w.authorships) AS ships,w \\\n",
    "    UNWIND ships AS ship MERGE (a:Author {id: ship.author.id}) \\\n",
    "    SET a.institution = [] WITH a,ship,w SET \\\n",
    "    a.source = \\'OpenAlex\\', \\\n",
    "    a.pass = 1, \\\n",
    "    a.display_name = ship.author.display_name, \\\n",
    "    a.orcid = ship.author.orcid, a.institution = \\\n",
    "    CASE WHEN any (x in a.institution WHERE x = ship.institutions[0].id) \\\n",
    "    THEN a.institution ELSE \\\n",
    "    a.institution + coalesce(ship.institutions[0].id,'')\\\n",
    "    END MERGE (a)-[:WROTE {author_position: ship.author_position}]->(w)\\\",\\\n",
    "    {batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(author_node_creation)\n",
    "\n",
    "record, summary, keys = driver.execute_query(author_node_creation)\n",
    "print(record[0][8])\n",
    "print(\"Author import complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b58c72c2-3ecc-4752-a711-cda81e6fed94",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves the institutions of authors from existing Works nodes and \n",
    "# creates an institution node if one does not already exisit with \n",
    " # \"display_name\", \"country_code\", \"ror\" and \"type\" properties\n",
    "#Newly created institutions are identified with a 1 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the institution\n",
    "\n",
    "institution_node_creation = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) RETURN w\\\",\\\"WITH \\\n",
    "    apoc.convert.fromJsonList(w.authorships) AS ships,w UNWIND ships AS ship\\\n",
    "    MERGE (i:Institutions {id: coalesce(ship.institutions[0].id,\\'\\')}) \\\n",
    "    SET i.source = \\'OpenAlex\\', \\\n",
    "    i.pass = 1, \\\n",
    "    i.display_name = ship.institutions[0].display_name, \\\n",
    "    i.country_code = ship.institutions[0].country_code, \\\n",
    "    i.ror = ship.institutions[0].ror, \\\n",
    "    i.type = ship.institutions[0].type\\\",\\\n",
    "    {batchSize:200, parallel:false})\"                           \n",
    "\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(institution_node_creation)\n",
    "\n",
    "record, summary, keys = driver.execute_query(institution_node_creation)\n",
    "print(record[0][8])\n",
    "print(\"Institutions import complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bb13a1f-93ab-4de5-8d5b-3e5ce170f0cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves creates an AFFILIATED_WITH relationship between\n",
    "    # Author and Institution nodes on matches between both\n",
    "\n",
    "institution_relationship_creation = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (a:Author) RETURN a\\\",\\\"UNWIND \\\n",
    "    a.institution AS inst WITH inst,a WHERE inst <> \\'\\' MATCH (i:Institutions) \\\n",
    "    WHERE i.id = inst MERGE (a)-[:AFFILIATED_WITH]->(i)\\\", \\\n",
    "    {batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(institution_relationship_creation)\n",
    "\n",
    "record, summary, keys = driver.execute_query(institution_relationship_creation)\n",
    "print(record[0][8])\n",
    "print(\"Author institution relationships creation complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72caedd0-73f7-4833-ad99-c76ec61e8e50",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block transforms the inverted_abstract for each Work node into\n",
    "# a regular abstract using pyalex's invert_abstract function\n",
    "\n",
    "records, summary, keys = driver.execute_query(\n",
    "    \"MATCH (n:Work) WHERE n.inverted_abstract IS NOT NULL AND \\\n",
    "        n.inverted_abstract <> 'null' RETURN n.inverted_abstract, n.id\")\n",
    "\n",
    "work_abstract_count = 0 \n",
    "\n",
    "# Loop through retrieved works to convert inverted abstracts to\n",
    "    # regular abstracts \n",
    "\n",
    "for record in records:  \n",
    "    node_id = record.data('n.id').get('n.id')\n",
    "\n",
    "    try:\n",
    "        phase1_abstract = '{' + record.data('n.inverted_abstract').get( \\\n",
    "            'n.inverted_abstract')[1:-1] + '}'\n",
    "        phase2_abstract = ast.literal_eval(phase1_abstract)\n",
    "        #print(phase2_abstract)\n",
    "        driver.execute_query(\"MATCH (w:Work {id: $id}) \\\n",
    "            SET w += {abstract: $abstract}\", \\\n",
    "            id = node_id,abstract = pyalex.invert_abstract(phase2_abstract))\n",
    "        work_abstract_count += 1 \n",
    "    except:\n",
    "        print(\"An exception occurred for Work id - \" + node_id )\n",
    "        print(\"Here is the abstract for this work:\")\n",
    "        print(record.data('n.inverted_abstract').get('n.inverted_abstract'))\n",
    "\n",
    "works_with_data_imported, summary, keys = driver.execute_query(\n",
    "    \"MATCH (n:Work) WHERE n.display_name IS NOT NULL RETURN COUNT(n)\")\n",
    "\n",
    "print(str(work_abstract_count) + \" Works have abstracts out of \" \\\n",
    "    + str(works_with_data_imported[0][0]) + \\\n",
    "    \" Works with data imported\")\n",
    "\n",
    "print(str(len(records) - work_abstract_count) + \\\n",
    "    \" Works with an inverted abstract failed conversion\" +\n",
    "    \" to a normal abstract\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dc00913-9f19-4d16-80c5-4775525116f7",
   "metadata": {},
   "source": [
    "#Retrieving Metadata for Referenced and Related Works\n",
    "\n",
    "Scripting below this block retrieves information for Reference and Related works that were created earlier, but were not included in the original download of Works.\n",
    "\n",
    "API calls are restricted to 10 per second and 100,000 per day. \n",
    "More information can be found at - https://docs.openalex.org/how-to-use-the-api/rate-limits-and-authentication"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b5a3dcc-e08f-490a-88b9-5054843e99f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This block retrieves information from OpenAlex in batches of 60 \n",
    "#on Related and Referenced Works created earlier that were not included \n",
    "#in the initial download of requested works.\n",
    "\n",
    "# This greatly expands the scope of Works considered\n",
    "# From the \"Classroom Design\" example, 99 Works originally imported have\n",
    "# 1700 additional Works Related to or Referenced, which will be imported\n",
    "\n",
    "# In some cases retrieval of a specified Work (based on ID) returns information \n",
    "#for another Work ID. This appears to be when the original Work was deleted \n",
    "#from the OpenAlex database\n",
    "\n",
    "records_for_work_retrieval_start, summary_for_work_retrieval_start, \\\n",
    "    keys_for_work_retrieval_start = driver.execute_query(\\\n",
    "    \"MATCH (n:Work) WHERE n.display_name IS NULL RETURN COUNT(n)\")\n",
    "\n",
    "works_without_information = records_for_work_retrieval_start[0][0]\n",
    "\n",
    "print(\"A total of \" + str(works_without_information) + \\\n",
    "      \" Works will be retrieved\")\n",
    "\n",
    "while works_without_information > 0:\n",
    "    records_for_work_retrieval_start, summary_for_work_retrieval_start, \\\n",
    "        keys_for_work_retrieval_start = driver.execute_query(\\\n",
    "        \"MATCH (n:Work) WHERE n.display_name IS NULL RETURN COUNT(n)\")\n",
    "   \n",
    "    works_without_information = records_for_work_retrieval_start[0][0]\n",
    "    print(\"Processing work number \" + str(works_without_information) + \\\n",
    "          \" in increments of 60\")\n",
    "\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        \"MATCH (n:Work) WHERE n.display_name IS NULL AND \\\n",
    "        n.pass = 2 WITH substring(n.id,21,32) AS work_id \\\n",
    "        RETURN work_id LIMIT 60\")\n",
    "     \n",
    "    # Loop through results returned and retrieve Work information from OpenAlex for each\n",
    "    for record in records:  \n",
    "        try:\n",
    "            records, summary, keys = driver.execute_query( \\\n",
    "                \"WITH '\" + record.data('work_id').get('work_id') + \\\n",
    "                \"' AS work_id CALL apoc.load.jsonParams(\\\n",
    "                'https://api.openalex.org/works/' + work_id, null, null) \\\n",
    "                YIELD value MATCH (w:Work) WHERE w.id CONTAINS work_id \\\n",
    "                SET w.display_name = CASE \\\n",
    "                WHEN value.id CONTAINS work_id \\\n",
    "                THEN value.title ELSE coalesce('Deleted Work', '') END, \\\n",
    "                w.pass = CASE WHEN value.id CONTAINS work_id \\\n",
    "                THEN 3 ELSE 999 END, \\\n",
    "                w.source = \\'OpenAlex\\', \\\n",
    "                w.cited_by_api_url = coalesce(value.cited_by_api_url, ''), \\\n",
    "                w.cited_by_count = coalesce(value.cited_by_count, ''), \\\n",
    "                w.corresponding_author_ids  = \\\n",
    "                coalesce(value.corresponding_author_ids , ''), \\\n",
    "                w.corresponding_institution_ids  = \\\n",
    "                coalesce(value.corresponding_institution_ids , ''), \\\n",
    "                w.created_date = coalesce(value.created_date, ''), \\\n",
    "                w.display_name = coalesce(value.display_name, ''), \\\n",
    "                w.doi = coalesce(value.doi, ''), \\\n",
    "                w.is_paratext = coalesce(value.is_paratext, ''), \\\n",
    "                w.is_retracted = coalesce(value.is_retracted, ''), \\\n",
    "                w.language = coalesce(value.language, ''), \\\n",
    "                w.locations_count = coalesce(value.locations_count, ''), \\\n",
    "                w.ngrams_url = coalesce(value.ngrams_url, ''), \\\n",
    "                w.publication_date = coalesce(value.publication_date, ''), \\\n",
    "                w.publication_year = coalesce(value.publication_year, ''), \\\n",
    "                w.publication_year = coalesce(value.publication_year, ''), \\\n",
    "                w.title = coalesce(value.title, ''), \\\n",
    "                w.type = coalesce(value.type, ''), \\\n",
    "                w.updated_date = coalesce(value.updated_date, ''), \\\n",
    "                w.is_oa = coalesce(value.is_oa, ''), \\\n",
    "                w.license = coalesce(value.license, ''), \\\n",
    "                w.url = coalesce(value.url, ''), \\\n",
    "                w.version = coalesce(value.version, '') WITH w, value CALL \\\n",
    "                apoc.convert.setJsonProperty(\\\n",
    "                w, 'inverted_abstract', value.abstract_inverted_index) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'authorships', value.authorships) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'apc_payment', value.apc_payment) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'best_oa_location', value.best_oa_location) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'biblio', value.biblio) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'concepts', value.concepts) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'counts_by_year', value.counts_by_year) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'grants', value.grants) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'ids', value.ids) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'locations', value.locations) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'mesh', value.mesh) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'open_access', value.open_access) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'primary_location', value.primary_location) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'referenced_works', value.referenced_works) \\\n",
    "                CALL apoc.convert.setJsonProperty(\\\n",
    "                w, 'related_works', value.related_works)\")\n",
    "\n",
    "        except: \n",
    "            records, summary, keys = driver.execute_query( \\\n",
    "                \"WITH '\" + \\\n",
    "                record.data('work_id').get('work_id') + \"' AS work_id \\\n",
    "                MATCH (w:Work) WHERE w.id CONTAINS work_id \\\n",
    "                SET w.display_name = 'Work Not Found', \\\n",
    "                w.pass = 999\" \\\n",
    "                )\n",
    "            print(\"Work was not found for ID: \" + \\\n",
    "                  str(record.data('work_id').get('work_id')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5a21608-af1e-41f1-a11f-bda416eed9f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves concepts associated with Works nodes previously imported\n",
    "# and creates a ASSOC_CONCEPT relationship\n",
    "#If the associated concept does not exist it is created using the\n",
    "# id retrieved from the list of \"concepts\"\n",
    "#Concepts are identified with a 3 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the concept\n",
    "\n",
    "concept_node_creation2 = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) WHERE \\\n",
    "    w.pass = 3 RETURN w\\\", \\\n",
    "    \\\"WITH apoc.convert.fromJsonList(w.concepts) AS concepts,w \\\n",
    "    UNWIND concepts AS concept \\\n",
    "    MERGE (c:Concept {id: concept.id}) \\\n",
    "    SET c.source = \\'OpenAlex\\', \\\n",
    "    c.pass = 3, \\\n",
    "    c.score = concept.score, \\\n",
    "    c.level = concept.level, \\\n",
    "    c.display_name = concept.display_name, \\\n",
    "    c.wikidata = concept.wikidata \\\n",
    "    MERGE (c)<-[:ASSOC_CONCEPT]-(w)\\\", \\\n",
    "    {batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(concept_node_creation2)\n",
    "\n",
    "record, summary, keys = driver.execute_query(concept_node_creation2)\n",
    "print(record[0][8])\n",
    "print(\"2nd Round Concept import complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aca1bc6b-186e-4458-a5c1-ab915e656c00",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves authors of existing Works nodes previously imported \n",
    "# and creates a WROTE relationship that includes a \"author_position\"\n",
    "# property to identify the position of the author's name\n",
    "#If the author does not exist it is created using the\n",
    "# id retrieved from the list of \"authorships\"\n",
    "#Newly crated authors are identified with a 3 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the author\n",
    "\n",
    "author_node_creation2 = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) \\\n",
    "    WHERE w.pass = 3 RETURN w\\\",\\\n",
    "    \\\"WITH apoc.convert.fromJsonList(w.authorships) AS ships,w \\\n",
    "    UNWIND ships AS ship MERGE (a:Author {id: ship.author.id}) \\\n",
    "    SET a.institution = [] WITH a,ship,w SET \\\n",
    "    a.source = \\'OpenAlex\\', \\\n",
    "    a.pass = 3, \\\n",
    "    a.display_name = ship.author.display_name, \\\n",
    "    a.orcid = ship.author.orcid, \\\n",
    "    a.institution = CASE WHEN any \\\n",
    "    (x in a.institution WHERE x = ship.institutions[0].id) \\\n",
    "    THEN a.institution ELSE \\\n",
    "    a.institution + coalesce(ship.institutions[0].id,'')\\\n",
    "    END MERGE (a)-[:WROTE {author_position: ship.author_position}]->(w)\\\",\\\n",
    "    {batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(author_node_creation2)\n",
    "\n",
    "record, summary, keys = driver.execute_query(author_node_creation2)\n",
    "print(record[0][8])\n",
    "print(\"2nd Round Author creation complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e6fc7d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves the institutions of authors from existing Works nodes \n",
    "# previously imported and creates an institution node if one does not already \n",
    "# exisit with \"display_name\", \"country_code\", \"ror\" and \"type\" properties\n",
    "#Newly created institutions are identified with a 3 in the \"pass\" property to \n",
    "# allow processing to retrieve all information about the institution\n",
    "\n",
    "institution_node_creation2 = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (w:Work) \\\n",
    "    WHERE w.pass = 3 RETURN w\\\", \\\n",
    "    \\\"WITH apoc.convert.fromJsonList(w.authorships) AS ships,w \\\n",
    "    UNWIND ships AS ship \\\n",
    "    MERGE (i:Institutions {id: coalesce(ship.institutions[0].id,\\'\\')}) \\\n",
    "    SET i.source = \\'OpenAlex\\', \\\n",
    "    i.pass = 3, \\\n",
    "    i.display_name = ship.institutions[0].display_name, \\\n",
    "    i.country_code = ship.institutions[0].country_code, \\\n",
    "    i.ror = ship.institutions[0].ror, \\\n",
    "    i.type = ship.institutions[0].type\\\", \\\n",
    "    {batchSize:200, parallel:false})\"      \n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(institution_node_creation2)\n",
    "\n",
    "record, summary, keys = driver.execute_query(institution_node_creation2)\n",
    "print(record[0][8])\n",
    "print(\"2nd Round Institution creation complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "621de53a-eb70-4b25-9889-e4fa8167cff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block retrieves creates an AFFILIATED_WITH relationship between\n",
    "    # Author and Institution nodes on matches between both\n",
    "\n",
    "institution_relationship_creation2 = \\\n",
    "    \"CALL apoc.periodic.iterate(\\\"MATCH (a:Author) WHERE a.pass = 3 \\\n",
    "    RETURN a\\\",\\\"UNWIND a.institution AS inst WITH inst,a \\\n",
    "    WHERE inst <> \\'\\' MATCH (i:Institutions) \\\n",
    "    WHERE i.id = inst MERGE (a)-[:AFFILIATED_WITH]->(i)\\\", \\\n",
    "    {batchSize:200, parallel:false})\"\n",
    "\n",
    "#Uncomment the print command below to view the raw Cypher script used by Neo4j\n",
    "#print(institution_relationship_creation2)\n",
    "\n",
    "record, summary, keys = \\\n",
    "    driver.execute_query(institution_relationship_creation2)\n",
    "print(record[0][8])\n",
    "print(\"2nd Round Institution Author Relationship creation complete\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55a99926",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This block transforms the inverted_abstract for each Work node into\n",
    "# a regular abstract using pyalex's invert_abstract function\n",
    "\n",
    "records, summary, keys = driver.execute_query(\n",
    "    \"MATCH (n:Work) WHERE n.pass = 3 AND n.inverted_abstract IS NOT NULL \\\n",
    "    AND n.inverted_abstract <> 'null' RETURN n.inverted_abstract, n.id\")\n",
    "\n",
    "works_abstract_count2 = 0 \n",
    "\n",
    "for record in records:  \n",
    "    node_id = record.data('n.id').get('n.id')\n",
    "    \n",
    "    try:\n",
    "        phase1_abstract = '{' + record.data('n.inverted_abstract').get( \\\n",
    "            'n.inverted_abstract')[1:-1] + '}'\n",
    "        phase2_abstract = ast.literal_eval(phase1_abstract)\n",
    "        driver.execute_query(\"MATCH (w:Work {id: $id}) \\\n",
    "            SET w += {abstract: $abstract}\", \\\n",
    "            id = node_id,abstract = pyalex.invert_abstract(phase2_abstract))\n",
    "        works_abstract_count2 += 1 \n",
    "    except:\n",
    "        print(\"An exception occurred for Work id - \" + node_id )\n",
    "        print(\"Here is the abstract for this work:\")\n",
    "        print(record.data('n.inverted_abstract').get('n.inverted_abstract'))\n",
    "\n",
    "works_with_data_imported, summary, keys = driver.execute_query(\n",
    "    \"MATCH (n:Work) WHERE n.display_name IS NOT NULL RETURN COUNT(n)\")\n",
    "\n",
    "print(str(works_abstract_count2) + \" Works have abstracts out of \" + \\\n",
    "    str(works_with_data_imported[0][0]) + \\\n",
    "    \" Works with data imported\")\n",
    "\n",
    "print(str(len(records) - works_abstract_count2) + \\\n",
    "    \" Works with an inverted abstract failed conversion\" +\n",
    "    \" to a normal abstract\")"
   ]
  }
 ],
 "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}