nft/DiffbotNLP + Neo4j.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0c3dfe14",
   "metadata": {},
   "source": [
    "# Monitoring Cryptocurrency space with NLP and Knowledge Graphs\n",
    "\n",
    "There are thousands if not more articles produced every day. While there is a lot of knowledge hidden in those articles, it is virtually impossible to read all of them. Even if you only focus on a specific domain, it is still hard to find all relevant articles and read them to get valuable insights. However, there are tools that could help you avoid manual labor and extract those insights automatically. I am, of course, talking about various NLP tools and services.\n",
    "\n",
    "# Agenda\n",
    "* Retrieve articles that talk about cryptocurrency\n",
    "* Translate foreign articles with Google Translate API\n",
    "* Import articles into Neo4j\n",
    "* Extract entities and facts with Diffbot's NLP API\n",
    "* Import entities and facts into Neo4j\n",
    "* Graph analysis\n",
    "\n",
    "\n",
    "# Retrieve articles about cryptocurrencies\n",
    "As mentioned, we will use the Diffbot APIs to retrieve articles that talk about cryptocurrencies. If you want to follow this post, you can create a free trial account on their page, which should be enough to complete all the steps presented here. Once you login to their portal, you can explore their visual query builder interface and inspect what is available.\n",
    "\n",
    "There is a lot of data available by Diffbot's Knowledge Graph API. So not only can you search for various articles, but you could use their KG APIs to retrieve information around organizations, products, persons, jobs, and more.\n",
    "This example will retrieve the latest 5000 articles with a tag label Cryptocurrency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d108a861",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "DIFF_TOKEN = \"<Insert Diffbot token>\"\n",
    "search_query = 'query=type%3AArticle+tags.label%3A\"cryptocurrency\"++sortBy%3Adate'\n",
    "article_count = 5000\n",
    "articles_per_request = 50\n",
    "\n",
    "def get_articles(query, offset):\n",
    "    \"\"\"\n",
    "    Fetch relevant articles from Diffbot KG endpoint\n",
    "    \"\"\"\n",
    "    search_host = \"https://kg.diffbot.com/kg/dql_endpoint?\"\n",
    "    url = f\"{search_host}{query}&token={DIFF_TOKEN}&from={offset}&size={articles_per_request}\"\n",
    "    return requests.get(url).json()['data']\n",
    "\n",
    "articles = []\n",
    "for offset in range(0,article_count, articles_per_request):\n",
    "    articles.extend(get_articles(search_query, offset))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "128d44a4",
   "metadata": {},
   "source": [
    "I have constructed the search query in their visual builder and simply copied it to my Python script. That's all the code required to fetch any number of articles that are relevant to your use-case.\n",
    "# Translate foreign articles with Google Translate API\n",
    "The retrieved articles are from all over the world and in many languages. In the next step, you will use Google Translate API to translate them to English. You will need to enable the Google Translate API and create an API key. Make sure to check their pricing, as it ended up a bit more than expected for me to use their translation API. I've checked pricing on other sites, and it is usually between $15 to $20 to translate a million characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fa352b81",
   "metadata": {},
   "outputs": [],
   "source": [
    "GOOGLE_API_KEY = \"<Insert you google API key>\"\n",
    "translate_url = \"https://translation.googleapis.com/language/translate/v2\"\n",
    "\n",
    "def translate(text, language):\n",
    "    \"\"\"\n",
    "    Translate text to English with Google Translate API.\n",
    "    If the text is already in English, simply return it.\n",
    "    \"\"\"\n",
    "    if language == 'en':\n",
    "        return text\n",
    "    \n",
    "    data = {'q': text, 'target': 'en', 'format':'text', 'source': language, 'key': GOOGLE_API_KEY}\n",
    "    try:\n",
    "        response = requests.post(translate_url, data=data).json()\n",
    "        return response['data']['translations'][0]['translatedText']\n",
    "    except Exception as e:\n",
    "        print(response)\n",
    "        return None\n",
    "\n",
    "# Google Translate API has a limit of 100kb per request\n",
    "max_character_length = 95_000\n",
    "\n",
    "for row in articles:\n",
    "    row['date'] = row.get('date').get('timestamp') if row.get('date') else None\n",
    "    row['translatedText'] = translate(row['text'][:max_character_length], row['language'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bae55ffa",
   "metadata": {},
   "source": [
    "Before we move on to the NLP extraction part, we will import the articles into Neo4j.\n",
    "# Import articles into Neo4j\n",
    "I suggest you either download Neo4j Desktop or use the free Neo4j Aura cloud instance, which should be enough to store information about these 5000 articles. First of all, we have to define the connection to Neo4j instance."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b88fc7ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define Neo4j connections\n",
    "from neo4j import GraphDatabase\n",
    "\n",
    "host = 'bolt://localhost:7687'\n",
    "user = 'neo4j'\n",
    "password = 'letmein'\n",
    "driver = GraphDatabase.driver(host,auth=(user, password))\n",
    "                                         \n",
    "\n",
    "def run_query(query, params={}):\n",
    "    with driver.session() as session:\n",
    "        result = session.run(query, params)\n",
    "        return pd.DataFrame([r.values() for r in result], columns=result.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4959bc3f",
   "metadata": {},
   "source": [
    "We have some meta-data around articles. For example, we know the overall sentiment of the paper and when it was published. In addition, for most of the articles, we know who wrote them and on which site. Lastly, the Diffbot API also returns the categories of an article.\n",
    "\n",
    "Before continuing, we will define unique constraints in Neo4j, which will speed up the import and subsequent queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "51243f76",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"CREATE CONSTRAINT IF NOT EXISTS ON (a:Article) ASSERT a.id IS UNIQUE;\")\n",
    "run_query(\"CREATE CONSTRAINT IF NOT EXISTS ON (e:Entity) ASSERT e.id IS UNIQUE;\")\n",
    "run_query(\"CREATE CONSTRAINT IF NOT EXISTS ON (c:Category) ASSERT c.id IS UNIQUE;\")\n",
    "run_query(\"CREATE CONSTRAINT IF NOT EXISTS ON (a:Author) ASSERT a.url IS UNIQUE;\")\n",
    "run_query(\"CREATE CONSTRAINT IF NOT EXISTS ON (r:Region) ASSERT r.name IS UNIQUE;\")\n",
    "run_query(\"CREATE CONSTRAINT IF NOT EXISTS ON (s:Site) ASSERT s.name IS UNIQUE;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4f5ebdb",
   "metadata": {},
   "source": [
    "Now we can go ahead and import articles into Neo4j."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "cdd6132d",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import_articles_query = \"\"\"\n",
    "UNWIND $data as row\n",
    "MERGE (a:Article {id: row.id})\n",
    "SET a.title = row.title,\n",
    "    a.url = row.url,\n",
    "    a.sentiment = row.sentiment,\n",
    "    a.date = CASE WHEN row.date IS NOT NULL THEN datetime({epochMillis:row.date}) ELSE Null END,\n",
    "    a.language = row.language,\n",
    "    a.text = row.text,\n",
    "    a.translatedText = row.translatedText\n",
    "MERGE (s:Site {name: row.siteName})\n",
    "MERGE (a)-[:ON_SITE]->(s)\n",
    "FOREACH (_ in case WHEN row.authorUrl IS NOT NULL THEN [1] ELSE [] END |\n",
    "   MERGE (au:Author {url: row.authorUrl})\n",
    "    ON CREATE SET au.name = row.author\n",
    "    MERGE (au)-[:WROTE]->(a))\n",
    "FOREACH (_ in case WHEN row.publisherRegion IS NOT NULL THEN [1] ELSE [] END |\n",
    "   MERGE (r:Region {name: row.publisherRegion})\n",
    "   MERGE (s)-[:HAS_REGION]->(r))\n",
    "WITH a, row.categories as categories\n",
    "UNWIND categories AS category\n",
    "MERGE (c:Category {id:category.id})\n",
    "ON CREATE SET c.name = category.name\n",
    "MERGE (a)-[:HAS_CATEGORY]->(c)\n",
    "\"\"\"\n",
    "\n",
    "run_query(import_articles_query, {'data': articles})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7328e93b",
   "metadata": {},
   "source": [
    "Before we move on to the analysis part of the post, we will use the NLP API to extract entities and relationships, or as Diffbot calls them, facts. The Diffbot website offers an online NLP demo, where you can input any text and evaluate the results.\n",
    "\n",
    "\n",
    "The NLP API will identify all the entities that appear in the text and possible relationships between them. In this example, we can see that Jack Dorsey is the CEO of Block, which is based in San Francisco and deals with payments and mining. Jack's coworker at Block is Thomas Templeton, who has a background in computer hardware.\n",
    "To process the entities in the response and store the to Neo4j, we will use the following code:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "23117b43",
   "metadata": {},
   "outputs": [],
   "source": [
    "FIELDS = \"entities,sentiment,facts\"\n",
    "HOST = \"nl.diffbot.com\"\n",
    "\n",
    "def nlp_request(payload):\n",
    "    res = requests.post(f\"https://{HOST}/v1/?fields={FIELDS}&token={DIFF_TOKEN}\", json=payload)\n",
    "    return res.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6544f851",
   "metadata": {},
   "outputs": [],
   "source": [
    "allowed_types = ['organization', 'person', 'location', 'product']\n",
    "entity_confidence = 0.7\n",
    "\n",
    "entity_query = \"\"\"\n",
    "MATCH (a:Article {id: $article_id})\n",
    "WITH a\n",
    "UNWIND $entities as e\n",
    "MERGE (entity:Entity {id: e.id})\n",
    "ON CREATE SET entity.name = e.name\n",
    "WITH a, entity, e\n",
    "CALL apoc.create.addLabels(entity, e.types) YIELD node\n",
    "MERGE (a)-[m:MENTIONS]->(entity)\n",
    "SET m.confidence = e.confidence,\n",
    "    m.salience = e.salience,\n",
    "    m.sentiment = e.sentiment\n",
    "\"\"\"\n",
    "\n",
    "def clean_and_store_entities(batch, response):\n",
    "    for article, nlp in zip(batch, response):\n",
    "        article_id = article['id']\n",
    "        # Skip processing if there are not entities found\n",
    "        if (not 'entities' in nlp) or (not nlp['entities']):\n",
    "            continue\n",
    "            \n",
    "        entities = pd.DataFrame.from_dict(nlp['entities'])\n",
    "        # Filter allowed entity types and capitalize type names\n",
    "        entities['types'] = [[type['name'].capitalize() for type in types if type['name'] in allowed_types] \n",
    "                             for types in entities['allTypes']]\n",
    "\n",
    "        # Filter entities without a type and confidence greater than entity confidence\n",
    "        entity_import = entities[[len(e['types']) > 0 and e['confidence'] >= entity_confidence for i,e in entities.iterrows()]]\n",
    "        # Filter persons who have only a single word\n",
    "        entity_import = entity_import[entity_import.apply(lambda x: ('Person' not in x['types']) or (len(x['name'].split(' '))) > 1, axis=1)]\n",
    "        # Define entity id\n",
    "        entity_import['id'] = [l['allUris'][0] if 0 < len(l['allUris']) else l['name'] for i, l in entity_import.iterrows()]\n",
    "        entity_params = {'article_id': article_id, 'entities': entity_import.to_dict('records')} \n",
    "        # Import to Neo4j\n",
    "        run_query(entity_query, entity_params)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25426ac8",
   "metadata": {},
   "source": [
    "This example will import only entities that have allowed types such as organization, person, product, and location, and their confidence level is greater than 0.7. Diffbot's NLP API also features entity linking, where entities are linked to Wikipedia, Crunchbase, or LinkedIn, as far as I have seen.\n",
    "\n",
    "Next, we have to prepare the function that will clean and import relationships into Neo4j."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "088ecdf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "skipProperties = ['gender', 'founding date', 'academic degree', 'age', \n",
    "                  'cause of death', 'date of birth', 'date of death']\n",
    "\n",
    "rel_confidence = 0.7\n",
    "\n",
    "rels_query =\"\"\"\n",
    "UNWIND $data as row\n",
    "MATCH (s:Entity {id: row.source})\n",
    "MATCH (t:Entity {id: row.target})\n",
    "CALL apoc.merge.relationship(s, row.type,\n",
    "  {},\n",
    "  {},\n",
    "  t,\n",
    "  {}\n",
    ")\n",
    "YIELD rel\n",
    "RETURN distinct 'done';\n",
    "\"\"\"\n",
    "\n",
    "def clean_and_store_rels(batch, response):\n",
    "    relParams = []\n",
    "    for article, nlp in zip(batch, response):\n",
    "        article_id = article['id']\n",
    "        if not 'facts' in nlp or len(nlp['facts']) == 0:\n",
    "            continue\n",
    "        facts = pd.DataFrame.from_dict(nlp['facts'])\n",
    "        # define confidence level\n",
    "        facts = facts[facts['confidence'] >= rel_confidence]\n",
    "        # Skip unrelated facts\n",
    "        facts = facts[facts['property'].apply(lambda x: x['name'] not in skipProperties)]\n",
    "        # skip if facts is empty\n",
    "        if len(facts) == 0:\n",
    "            continue\n",
    "\n",
    "        # Construct data\n",
    "        for i, row in facts[['entity', 'property', 'value']].iterrows():\n",
    "            source = row['entity']['name'] if len(row['entity']['allUris']) == 0 else row['entity']['allUris'][0]\n",
    "            target = row['value']['name'] if len(row['value']['allUris']) == 0 else row['value']['allUris'][0]\n",
    "            type = row['property']['name'].replace(' ', '_').upper()\n",
    "            relParams.append({'source':source,'target':target,'type':type})\n",
    "        run_query(rels_query, {'data': relParams})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "199481d6",
   "metadata": {},
   "source": [
    "I have omitted the import of the properties that are defined in the skipProperties list. To me, it makes more sense to store them as node properties rather than relationships between entities. However, in this example, we will simply ignore them during import.\n",
    "Now that we have the functions for importing entities and relationships prepared, we can go ahead and process the articles. You can send multiple articles in a single request. I've chosen to batch the requests by 50 pieces of content."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8ca9014c",
   "metadata": {},
   "outputs": [],
   "source": [
    "step = 50\n",
    "total = len(articles)\n",
    "\n",
    "for offset in range(0, total, step):\n",
    "    batch = [el for el in articles[offset:offset + step] if el['translatedText']]\n",
    "    # Prepare payload\n",
    "    payload = [{'content': el['translatedText']} for el in batch]\n",
    "    # Make the request to Diffbot API\n",
    "    nlp_response = nlp_request(payload)\n",
    "    # Clean and store entities and facts in Neo4j\n",
    "    clean_and_store_entities(batch, nlp_response)\n",
    "    clean_and_store_rels(batch, nlp_response)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca706433",
   "metadata": {},
   "source": [
    "By following these steps you have successfully constructed a knowledge graph in Neo4j.\n",
    "\n",
    "# Graph analysis\n",
    "In the last part of this post, I will walk you through some example applications that you could use with a knowledge graph like this. First, we will evaluate the timeline of the articles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "88b4451c",
   "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>date</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-01-14</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-01-13</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-12</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-01-11</td>\n",
       "      <td>388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-01-10</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-01-09</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-01-08</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-01-06</td>\n",
       "      <td>427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-01-05</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  count\n",
       "0  2022-01-14    144\n",
       "1  2022-01-13    256\n",
       "2  2022-01-12    290\n",
       "3  2022-01-11    388\n",
       "4  2022-01-10    365\n",
       "5  2022-01-09    140\n",
       "6  2022-01-08    198\n",
       "7  2022-01-07    316\n",
       "8  2022-01-06    427\n",
       "9  2022-01-05    326"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (a:Article)\n",
    "RETURN date(a.date) AS date,\n",
    "       count(*) AS count\n",
    "ORDER BY date DESC\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "746d5d91",
   "metadata": {},
   "source": [
    "There is between 150 to 450 articles per day about cryptocurrencies around the world. Next, we will evaluate which entities are most frequently mentioned in articles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c2b34c4c",
   "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>entity</th>\n",
       "      <th>articles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cryptocurrency</td>\n",
       "      <td>3182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bitcoin</td>\n",
       "      <td>1928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>1553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ethereum</td>\n",
       "      <td>1137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>blockchain</td>\n",
       "      <td>998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     entity  articles\n",
       "0            cryptocurrency      3182\n",
       "1                   bitcoin      1928\n",
       "2  United States of America      1553\n",
       "3                  Ethereum      1137\n",
       "4                blockchain       998"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (e:Entity)\n",
    "RETURN e.name AS entity,\n",
    "       size((e)<-[:MENTIONS]-()) AS articles\n",
    "ORDER BY articles\n",
    "DESC LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66873ea1",
   "metadata": {},
   "source": [
    "As you would expect from articles revolving around cryptocurrencies, the most frequently mentioned entities are cryptocurrency, bitcoin, Ethereum, and blockchain. The sentiment is available on the article level as well as entity level. For example, we can examine the sentiment regarding bitcoin grouped by region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e0c3706d",
   "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>region</th>\n",
       "      <th>avgSentiment</th>\n",
       "      <th>stdSentiment</th>\n",
       "      <th>maxSentiment</th>\n",
       "      <th>minSentiment</th>\n",
       "      <th>articles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>North America</td>\n",
       "      <td>0.273279</td>\n",
       "      <td>0.658506</td>\n",
       "      <td>0.997222</td>\n",
       "      <td>-0.997094</td>\n",
       "      <td>592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Western Europe</td>\n",
       "      <td>0.120103</td>\n",
       "      <td>0.706812</td>\n",
       "      <td>0.996824</td>\n",
       "      <td>-0.988779</td>\n",
       "      <td>174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>0.283040</td>\n",
       "      <td>0.647172</td>\n",
       "      <td>0.994452</td>\n",
       "      <td>-0.996317</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South America</td>\n",
       "      <td>0.346131</td>\n",
       "      <td>0.600056</td>\n",
       "      <td>0.996450</td>\n",
       "      <td>-0.993456</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Northern Europe</td>\n",
       "      <td>0.157956</td>\n",
       "      <td>0.685148</td>\n",
       "      <td>0.992812</td>\n",
       "      <td>-0.994532</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            region  avgSentiment  stdSentiment  maxSentiment  minSentiment  \\\n",
       "0    North America      0.273279      0.658506      0.997222     -0.997094   \n",
       "1   Western Europe      0.120103      0.706812      0.996824     -0.988779   \n",
       "2    Southern Asia      0.283040      0.647172      0.994452     -0.996317   \n",
       "3    South America      0.346131      0.600056      0.996450     -0.993456   \n",
       "4  Northern Europe      0.157956      0.685148      0.992812     -0.994532   \n",
       "\n",
       "   articles  \n",
       "0       592  \n",
       "1       174  \n",
       "2       105  \n",
       "3        79  \n",
       "4        71  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (e:Entity {name:'bitcoin'})<-[m:MENTIONS]-()-[:ON_SITE]->()-[:HAS_REGION]->(region)\n",
    "WITH region.name AS region, m.sentiment AS sentiment\n",
    "RETURN region, avg(sentiment) AS avgSentiment, \n",
    "       stdev(sentiment) AS stdSentiment, \n",
    "       max(sentiment) AS maxSentiment, \n",
    "       min(sentiment) AS minSentiment, \n",
    "       count(*) AS articles\n",
    "ORDER BY articles DESC\n",
    "LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "431d6a03",
   "metadata": {},
   "source": [
    "The sentiment is on average positive, but it heavily fluctuates between articles based on the standard deviation values. We could explore bitcoin sentiment more. Instead, we will examine which persons have the highest and lowest average sentiment in and also present in most articles in North America."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c1319f1b",
   "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>entity</th>\n",
       "      <th>articles</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Shiba Inu</td>\n",
       "      <td>92</td>\n",
       "      <td>0.116428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Elon Musk</td>\n",
       "      <td>47</td>\n",
       "      <td>0.214960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack Dorsey</td>\n",
       "      <td>23</td>\n",
       "      <td>0.170886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brandon Brown</td>\n",
       "      <td>9</td>\n",
       "      <td>0.427313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mark Cuban</td>\n",
       "      <td>9</td>\n",
       "      <td>0.347880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Floyd Mayweather</td>\n",
       "      <td>17</td>\n",
       "      <td>-0.365198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Charles Ponzi</td>\n",
       "      <td>8</td>\n",
       "      <td>-0.673924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Paul Pierce</td>\n",
       "      <td>14</td>\n",
       "      <td>-0.261665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>14</td>\n",
       "      <td>-0.154147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Kim Kardashian</td>\n",
       "      <td>18</td>\n",
       "      <td>-0.104895</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             entity  articles  sentiment\n",
       "0         Shiba Inu        92   0.116428\n",
       "1         Elon Musk        47   0.214960\n",
       "2       Jack Dorsey        23   0.170886\n",
       "3     Brandon Brown         9   0.427313\n",
       "4        Mark Cuban         9   0.347880\n",
       "5  Floyd Mayweather        17  -0.365198\n",
       "6     Charles Ponzi         8  -0.673924\n",
       "7       Paul Pierce        14  -0.261665\n",
       "8      Donald Trump        14  -0.154147\n",
       "9    Kim Kardashian        18  -0.104895"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (e:Person)<-[m:MENTIONS]-()-[:ON_SITE]->()-[:HAS_REGION]->(region)\n",
    "WHERE region.name = \"North America\"\n",
    "RETURN e.name AS entity,\n",
    "       count(*) AS articles,\n",
    "       avg(m.sentiment) AS sentiment\n",
    "ORDER BY sentiment * articles DESC\n",
    "LIMIT 5\n",
    "UNION\n",
    "MATCH (e:Person)<-[m:MENTIONS]-()-[:ON_SITE]->()-[:HAS_REGION]->(region)\n",
    "WHERE region.name = \"North America\"\n",
    "RETURN e.name AS entity,\n",
    "       count(*) AS articles,\n",
    "       avg(m.sentiment) AS sentiment\n",
    "ORDER BY sentiment * articles ASC\n",
    "LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f78b9b3",
   "metadata": {},
   "source": [
    "Now, we can explore the titles of articles in which, for example, Mark Cuban appears."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "15927b95",
   "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>title</th>\n",
       "      <th>language</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>site</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The biggest consumer brands that engaged with ...</td>\n",
       "      <td>en</td>\n",
       "      <td>0.955021</td>\n",
       "      <td>Cointelegraph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Billionaire Investor Mark Cuban to Share Stage...</td>\n",
       "      <td>en</td>\n",
       "      <td>0.920241</td>\n",
       "      <td>Crowdfund Insider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Billionaire Investor Mark Cuban to Share Stage...</td>\n",
       "      <td>en</td>\n",
       "      <td>0.897312</td>\n",
       "      <td>Crowdfund Insider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mark Cuban says this is ‘the least important p...</td>\n",
       "      <td>en</td>\n",
       "      <td>0.601242</td>\n",
       "      <td>CNBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mark Cuban says 80% of his investments that ar...</td>\n",
       "      <td>en</td>\n",
       "      <td>0.520630</td>\n",
       "      <td>CNBC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title language  sentiment  \\\n",
       "0  The biggest consumer brands that engaged with ...       en   0.955021   \n",
       "1  Billionaire Investor Mark Cuban to Share Stage...       en   0.920241   \n",
       "2  Billionaire Investor Mark Cuban to Share Stage...       en   0.897312   \n",
       "3  Mark Cuban says this is ‘the least important p...       en   0.601242   \n",
       "4  Mark Cuban says 80% of his investments that ar...       en   0.520630   \n",
       "\n",
       "                site  \n",
       "0      Cointelegraph  \n",
       "1  Crowdfund Insider  \n",
       "2  Crowdfund Insider  \n",
       "3               CNBC  \n",
       "4               CNBC  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (site)<-[:ON_SITE]-(a:Article)-[m:MENTIONS]->(e:Entity {name: 'Mark Cuban'})\n",
    "RETURN a.title AS title, a.language AS language, m.sentiment AS sentiment, site.name AS site\n",
    "ORDER BY sentiment DESC\n",
    "LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "131b3517",
   "metadata": {},
   "source": [
    "While the titles themselves might not the most descriptive, we can also examine which other entities frequently co-occur in articles where Mark Cuban is mentioned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "6d1ec54d",
   "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>entity</th>\n",
       "      <th>countOfArticles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cryptocurrency</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bitcoin</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dallas Mavericks</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>blockchain</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ethereum</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             entity  countOfArticles\n",
       "0    cryptocurrency               17\n",
       "1           bitcoin               16\n",
       "2  Dallas Mavericks               16\n",
       "3        blockchain                9\n",
       "4          Ethereum                7"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (o:Entity)<-[:MENTIONS]-(a:Article)-[m:MENTIONS]->(e:Entity {name: 'Mark Cuban'})\n",
    "WITH o, count(*) AS countOfArticles\n",
    "ORDER BY countOfArticles DESC\n",
    "LIMIT 5\n",
    "RETURN o.name AS entity, countOfArticles\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cf05de8",
   "metadata": {},
   "source": [
    "Not surprisingly, various crypto tokens are present. Also, the Dallas Mavericks appear, which is the NBA club that Mark owns. Does Dallas Mavericks support crypto, or do reporters like to state that Mark owns the Dallas Mavericks, that I don't know. You could proceed with that route of analysis, but here, we'll also look at what facts we extracted during NLP processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "18a31296",
   "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>source</th>\n",
       "      <th>relationship</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mark Cuban</td>\n",
       "      <td>EMPLOYEE_OR_MEMBER_OF</td>\n",
       "      <td>National Basketball Association</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mark Cuban</td>\n",
       "      <td>WORK_RELATIONSHIP</td>\n",
       "      <td>Francis X. Suarez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mark Cuban</td>\n",
       "      <td>WORK_RELATIONSHIP</td>\n",
       "      <td>Francis X. Suarez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mark Cuban</td>\n",
       "      <td>EMPLOYEE_OR_MEMBER_OF</td>\n",
       "      <td>Dallas Mavericks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mark Cuban</td>\n",
       "      <td>EMPLOYEE_OR_MEMBER_OF</td>\n",
       "      <td>AXS TV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       source           relationship                           target\n",
       "0  Mark Cuban  EMPLOYEE_OR_MEMBER_OF  National Basketball Association\n",
       "1  Mark Cuban      WORK_RELATIONSHIP                Francis X. Suarez\n",
       "2  Mark Cuban      WORK_RELATIONSHIP                Francis X. Suarez\n",
       "3  Mark Cuban  EMPLOYEE_OR_MEMBER_OF                 Dallas Mavericks\n",
       "4  Mark Cuban  EMPLOYEE_OR_MEMBER_OF                           AXS TV"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH p=(e:Entity {name: \"Mark Cuban\"})-[r]-(e2:Entity)\n",
    "WITH distinct e,r,e2\n",
    "RETURN e.name AS source, type(r) AS relationship, e2.name AS target\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ce420a8",
   "metadata": {},
   "source": [
    "Next, we will quickly evaluate the article titles where Floyd Mayweather appears, as the average sentiment is quite low."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "0f661a0a",
   "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>title</th>\n",
       "      <th>language</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kim Kardashian, Floyd Mayweather Accused of ‘P...</td>\n",
       "      <td>en</td>\n",
       "      <td>-0.960115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kim Kardashian and Floyd Mayweather sued over ...</td>\n",
       "      <td>en</td>\n",
       "      <td>-0.947587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Kim Kardashian, Floyd Mayweather Sued by Crypt...</td>\n",
       "      <td>en</td>\n",
       "      <td>-0.919837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kim Kardashian sued over crypto that crashed 9...</td>\n",
       "      <td>en</td>\n",
       "      <td>-0.912651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kim Kardashian and Floyd Mayweather Jr ‘misled...</td>\n",
       "      <td>en</td>\n",
       "      <td>-0.901501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title language  sentiment\n",
       "0  Kim Kardashian, Floyd Mayweather Accused of ‘P...       en  -0.960115\n",
       "1  Kim Kardashian and Floyd Mayweather sued over ...       en  -0.947587\n",
       "2  Kim Kardashian, Floyd Mayweather Sued by Crypt...       en  -0.919837\n",
       "3  Kim Kardashian sued over crypto that crashed 9...       en  -0.912651\n",
       "4  Kim Kardashian and Floyd Mayweather Jr ‘misled...       en  -0.901501"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (a:Article)-[m:MENTIONS]->(e:Entity {name: 'Floyd Mayweather'})\n",
    "RETURN a.title AS title, a.language AS language, m.sentiment AS sentiment\n",
    "ORDER BY sentiment ASC\n",
    "LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b9ae5be",
   "metadata": {},
   "source": [
    "It seems that Kim Kardashian and Floyd Mayweather are being sued over an alleged crypto scam. The NLP processing also identifies various tokens and stock tickers, so we can analyze which are popular at the moment and their sentiment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "c5397c3c",
   "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>stock</th>\n",
       "      <th>mentions</th>\n",
       "      <th>averageSentiment</th>\n",
       "      <th>minSentiment</th>\n",
       "      <th>maxSentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>XRP</td>\n",
       "      <td>156</td>\n",
       "      <td>0.167954</td>\n",
       "      <td>-0.966287</td>\n",
       "      <td>0.995899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DOT</td>\n",
       "      <td>64</td>\n",
       "      <td>0.137851</td>\n",
       "      <td>-0.981497</td>\n",
       "      <td>0.977433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BUSD</td>\n",
       "      <td>63</td>\n",
       "      <td>0.245850</td>\n",
       "      <td>-0.884344</td>\n",
       "      <td>0.971559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DOGE</td>\n",
       "      <td>59</td>\n",
       "      <td>0.108355</td>\n",
       "      <td>-0.978014</td>\n",
       "      <td>0.985040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LINK</td>\n",
       "      <td>57</td>\n",
       "      <td>0.183612</td>\n",
       "      <td>-0.859450</td>\n",
       "      <td>0.990779</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  stock  mentions  averageSentiment  minSentiment  maxSentiment\n",
       "0   XRP       156          0.167954     -0.966287      0.995899\n",
       "1   DOT        64          0.137851     -0.981497      0.977433\n",
       "2  BUSD        63          0.245850     -0.884344      0.971559\n",
       "3  DOGE        59          0.108355     -0.978014      0.985040\n",
       "4  LINK        57          0.183612     -0.859450      0.990779"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "run_query(\"\"\"\n",
    "MATCH (e:Entity)<-[m:MENTIONS]-()\n",
    "WHERE (e)<-[:STOCK_SYMBOL]-()\n",
    "RETURN e.name AS stock, \n",
    "       count(*) as mentions, \n",
    "       avg(m.sentiment) AS averageSentiment,\n",
    "       min(m.sentiment) AS minSentiment,\n",
    "       max(m.sentiment) AS maxSentiment\n",
    "ORDER BY mentions DESC\n",
    "LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd20ffa4",
   "metadata": {},
   "source": [
    "I have only scratched the surface of the available insights we could extract. Play around and develop your own visualizations!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bbd7a1d3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}