Schema_coverage_calculation_BIO2RDF.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f03753bb-8c12-44f8-adc4-266b1ef4bc06",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "71b14ad0-4b0d-4df5-ac3a-da9645e38f78",
   "metadata": {},
   "source": [
    "Download Bio2RDF SPARQL query  Logs\n",
    "\n",
    "Delete http parameters\n",
    "\n",
    "count of uniques  \n",
    "\n",
    "Prefix Addition \n",
    "\n",
    "parse queries\n",
    "\n",
    "seperate valid and unvalid queries\n",
    "\n",
    "normalize vars in parse trees\n",
    "(later I ignore this step for now bc we do not need it: limit offset changes)\n",
    "(later: count of unique normalized parse tree)\n",
    "\n",
    "\n",
    "Extract triples \n",
    "\n",
    "Extract s, o, p\n",
    "\n",
    "write queries to retrieve schema elements and check predicates\n",
    "\n",
    "extract the one exist among bio2rdf domain\n",
    "\n",
    "diagrams\n",
    "\n",
    "graph diagrams: get schema relations. intractive graph. highlight. lay out after\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dab104b-3090-4ce9-ab7c-c8b9ec911aee",
   "metadata": {},
   "source": [
    "first we find line terminator and number of row\n",
    "\n",
    "without lineterminator='\\n', the row number is 6000000, which is not correct.\n",
    "\n",
    "import csv\n",
    "\n",
    "Specify the path to your CSV file\n",
    "file_path = 'bio2rdf_sparql_logs_processed_01-2019_to_07-2021.csv'\n",
    "count = 0\n",
    "null_count = 0\n",
    "\n",
    "Open the file\n",
    "with open(file_path, 'r', encoding='utf-8', errors='ignore') as csvfile:\n",
    "    # Pre-process each line in the file\n",
    "    def process_lines(file):\n",
    "        global null_count\n",
    "        for line in file:\n",
    "            nulls_in_line = line.count('\\0')\n",
    "            null_count += nulls_in_line\n",
    "            yield line.replace('\\0', '')\n",
    "    \n",
    "    lines = process_lines(csvfile)\n",
    "    # Create a CSV reader\n",
    "    reader = csv.reader(lines)\n",
    "    # Iterate over each row in the CSV\n",
    "    for row in reader:\n",
    "        count += 1\n",
    "\n",
    "print(f'Total rows: {count}')\n",
    "print(f'Number of null characters found and skipped: {null_count}')\n",
    "\n",
    "results\n",
    "\n",
    "Total rows: 3880961\n",
    "\n",
    "Number of null characters found and skipped: 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfeed056-a400-474c-8c5e-e5b663ccc7d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, CSV\n",
    "\n",
    "# SPARQL endpoint\n",
    "endpoint = \"https://lsq.data.dice-research.org/sparql\"\n",
    "\n",
    "# List of Bio2RDF datasets\n",
    "datasets = [\"biomedels\", \"affymetrix\", \"bioportal\", \"ctd\", \"dbsnp\",\n",
    "    \"drugbank\", \"genage\", \"gendr\", \"goa\", \"hgnc\", \"homologene\",\n",
    "    \"irefindex\", \"kegg\", \"linkedspl\", \"mgi\", \"ncbigene\", \"omim\", \"pharmgkb\", \"sabiork\",\n",
    "    \"sgd\", \"sidr\", \"taxonomy\", \"wormbase\"]\n",
    "\n",
    "# Base SPARQL query template without dataset name, LIMIT, and OFFSET\n",
    "base_query_template = \"\"\"\n",
    "PREFIX lsqv: <http://lsq.aksw.org/vocab#> \n",
    "PREFIX prov: <http://www.w3.org/ns/prov#>\n",
    "SELECT Distinct ?text ?timeStamp From <http://lsq.aksw.org/{dataset}>\n",
    "WHERE {{\n",
    "?query lsqv:text ?text . \n",
    "?query lsqv:hasRemoteExec ?re .\n",
    "?re prov:atTime ?timeStamp . \n",
    "}}\n",
    "\"\"\"\n",
    "\n",
    "# Initialize SPARQLWrapper with the endpoint\n",
    "sparql = SPARQLWrapper(endpoint)\n",
    "sparql.setTimeout(60)  # Set timeout to 60 seconds\n",
    "\n",
    "# Pagination settings\n",
    "limit = 10000  # Adjust based on the endpoint's capabilities\n",
    "offset = 0\n",
    "\n",
    "# Filename for the CSV file\n",
    "filename = \"data/bio2rdf_23_lsq.csv\"\n",
    "\n",
    "with open(filename, \"wb\") as file:\n",
    "    first_chunk = True\n",
    "    for dataset in datasets:\n",
    "        more_results = True\n",
    "        offset = 0\n",
    "        while more_results:\n",
    "            # Format the base query with the current dataset\n",
    "            paginated_query = base_query_template.format(dataset=dataset) + f\" LIMIT {limit} OFFSET {offset}\"\n",
    "            \n",
    "            # Set the query and return format\n",
    "            sparql.setQuery(paginated_query)\n",
    "            sparql.setReturnFormat(CSV)\n",
    "            \n",
    "            # Execute the query and get the results\n",
    "            results = sparql.query().convert()\n",
    "\n",
    "            if first_chunk:\n",
    "                # Write the first chunk with headers\n",
    "                file.write(results)\n",
    "                first_chunk = False\n",
    "            else:\n",
    "                # If not the first chunk, find the header's end and skip it\n",
    "                header_end = results.find(b'\\n') + 1\n",
    "                if header_end > 0 and len(results) > header_end:\n",
    "                    file.write(results[header_end:])\n",
    "\n",
    "            print(f\"Fetched rows for {dataset} up to offset {offset}. Results length: {len(results)}\")\n",
    "\n",
    "            # Update the offset for the next iteration\n",
    "            offset += limit\n",
    "\n",
    "            # Check if the fetched data is significantly smaller than the expected, indicating we may have fetched all available data for this dataset\n",
    "            if len(results) < limit * 10:  # Adjust based on your average row size\n",
    "                more_results = False\n",
    "\n",
    "print(f\"Results have been saved to {filename}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d1e6a869-89fe-4d05-b4bf-4c479fdfe6b4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31950878\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>text</td>\n",
       "      <td>timeStamp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PREFIX  granatum: &lt;http://chem.deri.ie/granatu...</td>\n",
       "      <td>2013-05-13T01:01:36Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX  biopax: &lt;http://www.biopax.org/release...</td>\n",
       "      <td>2013-07-22T16:37:22Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PREFIX  biopax: &lt;http://www.biopax.org/release...</td>\n",
       "      <td>2013-07-22T16:37:23Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX  biopax: &lt;http://www.biopax.org/release...</td>\n",
       "      <td>2013-07-23T08:27:12Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0                     1\n",
       "0                                               text             timeStamp\n",
       "1  PREFIX  granatum: <http://chem.deri.ie/granatu...  2013-05-13T01:01:36Z\n",
       "2  PREFIX  biopax: <http://www.biopax.org/release...  2013-07-22T16:37:22Z\n",
       "3  PREFIX  biopax: <http://www.biopax.org/release...  2013-07-22T16:37:23Z\n",
       "4  PREFIX  biopax: <http://www.biopax.org/release...  2013-07-23T08:27:12Z"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/bio2rdf_23_lsq.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "473d4608-6168-4dbc-a9af-ef2b17c52f1e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31950878\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>text</td>\n",
       "      <td>timeStamp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PREFIX  granatum: &lt;http://chem.deri.ie/granatu...</td>\n",
       "      <td>2013-05-13T01:01:36Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX  biopax: &lt;http://www.biopax.org/release...</td>\n",
       "      <td>2013-07-22T16:37:22Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PREFIX  biopax: &lt;http://www.biopax.org/release...</td>\n",
       "      <td>2013-07-22T16:37:23Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX  biopax: &lt;http://www.biopax.org/release...</td>\n",
       "      <td>2013-07-23T08:27:12Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0                     1\n",
       "0                                               text             timeStamp\n",
       "1  PREFIX  granatum: <http://chem.deri.ie/granatu...  2013-05-13T01:01:36Z\n",
       "2  PREFIX  biopax: <http://www.biopax.org/release...  2013-07-22T16:37:22Z\n",
       "3  PREFIX  biopax: <http://www.biopax.org/release...  2013-07-22T16:37:23Z\n",
       "4  PREFIX  biopax: <http://www.biopax.org/release...  2013-07-23T08:27:12Z"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/bio2rdf_23_lsq.csv', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c013cef0-7e7d-45fb-958e-2eaee36455e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Assuming the CSV file is named 'data.csv' and is located in the current directory\n",
    "file_path = 'data/bio2rdf_23_lsq.csv'\n",
    "\n",
    "# Read the CSV file, parse the 'timeStamp' column as datetime\n",
    "df = pd.read_csv(file_path, parse_dates=['timeStamp'], header=0)\n",
    "\n",
    "print(df.head())\n",
    "\n",
    "print(len(df))\n",
    "\n",
    "# Get the minimum and maximum values from the 'timeStamp' column\n",
    "min_time = df['timeStamp'].min()\n",
    "max_time = df['timeStamp'].max()\n",
    "\n",
    "# Print the minimum and maximum datetime values\n",
    "print(f\"Minimum timestamp: {min_time}\")\n",
    "print(f\"Maximum timestamp: {max_time}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a898b67-7390-4b3c-9a05-b48bf0d33f22",
   "metadata": {},
   "source": [
    "                                                text                 timeStamp\n",
    "0  PREFIX  granatum: <http://chem.deri.ie/granatu... 2013-05-13 01:01:36+00:00\n",
    "1  PREFIX  biopax: <http://www.biopax.org/release... 2013-07-22 16:37:22+00:00\n",
    "2  PREFIX  biopax: <http://www.biopax.org/release... 2013-07-22 16:37:23+00:00\n",
    "3  PREFIX  biopax: <http://www.biopax.org/release... 2013-07-23 08:27:12+00:00\n",
    "4  PREFIX  biopax: <http://www.biopax.org/release... 2013-07-22 16:37:22+00:00\n",
    "31950877\n",
    "Minimum timestamp: 2013-05-05 10:40:27+00:00\n",
    "Maximum timestamp: 2014-09-28 23:53:02+00:00"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b8f4a7b4-9653-4872-868f-4c6203a19a1f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique queries extracted with counts and written to data2/unique_bio2rdf_lsq_counts.csv\n"
     ]
    }
   ],
   "source": [
    "# count of uniques  \n",
    "\n",
    "import csv\n",
    "\n",
    "def extract_unique_queries(input_file, output_file):\n",
    "    try:\n",
    "        # Initialize a dictionary to store query counts\n",
    "        query_counts = {}\n",
    "\n",
    "        with open(input_file, 'r', newline='\\n', encoding='utf-8') as infile:\n",
    "            reader = csv.DictReader(infile)\n",
    "\n",
    "            # Iterate through the rows and count queries\n",
    "            for row in reader:\n",
    "                query = row['text']  # Assuming 'query' is the correct column name\n",
    "                query_counts[query] = query_counts.get(query, 0) + 1\n",
    "\n",
    "        # Sort queries and their counts based on the count column in descending order\n",
    "        sorted_queries = sorted(query_counts.items(), key=lambda item: item[1], reverse=True)\n",
    "\n",
    "        # Write sorted queries and their counts to the output CSV file\n",
    "        with open(output_file, 'w', newline='\\n', encoding='utf-8') as outfile:\n",
    "            writer = csv.writer(outfile, lineterminator='\\n')\n",
    "            writer.writerow(['query', 'count'])  # Write header\n",
    "\n",
    "            for query, count in sorted_queries:\n",
    "                writer.writerow([query, count])\n",
    "\n",
    "        print(\"Unique queries extracted with counts and written to\", output_file)\n",
    "\n",
    "    except FileNotFoundError:\n",
    "        print(\"Error: Input CSV file not found.\")\n",
    "    except Exception as e:\n",
    "        print(\"An error occurred:\", e)\n",
    "\n",
    "# Replace 'input_file.csv' with the name of your CSV file and 'output_file.csv' with the desired output filename\n",
    "extract_unique_queries('data2/bio2rdf_23_lsq.csv', 'data2/unique_bio2rdf_lsq_counts.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "89477c15-c82c-49fe-b625-4850cfa0e184",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1519794\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>query</td>\n",
       "      <td>count</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DESCRIBE &lt;http://localhost/ping&gt;\\n</td>\n",
       "      <td>5502522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX  rdf:  &lt;http://www.w3.org/1999/02/22-rd...</td>\n",
       "      <td>189746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASK\\nWHERE\\n  { ?s  ?p  ?o }\\n</td>\n",
       "      <td>128131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX  drugbank: &lt;http://www4.wiwiss.fu-berli...</td>\n",
       "      <td>27707</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0        1\n",
       "0                                              query    count\n",
       "1                 DESCRIBE <http://localhost/ping>\\n  5502522\n",
       "2  PREFIX  rdf:  <http://www.w3.org/1999/02/22-rd...   189746\n",
       "3                     ASK\\nWHERE\\n  { ?s  ?p  ?o }\\n   128131\n",
       "4  PREFIX  drugbank: <http://www4.wiwiss.fu-berli...    27707"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/unique_bio2rdf_lsq_counts.csv', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0d0fd7d5-fcce-440c-a554-680294b658a2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done!\n"
     ]
    }
   ],
   "source": [
    "#Prefix Addition \n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "def add_prefix(query):\n",
    "    # The common prefix to add to each query\n",
    "    prefix = \"\"\"PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "PREFIX owl: <http://www.w3.org/2002/07/owl#>\n",
    "PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>\n",
    "PREFIX foaf: <http://xmlns.com/foaf/0.1/>\n",
    "PREFIX dc: <http://purl.org/dc/elements/1.1/>\n",
    "PREFIX dcterms: <http://purl.org/dc/terms/>\n",
    "PREFIX skos: <http://www.w3.org/2004/02/skos/core#>\n",
    "PREFIX schema: <http://schema.org/>\n",
    "PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>\n",
    "\n",
    "\"\"\"\n",
    "    return prefix + query\n",
    "\n",
    "def add_prefix_to_csv(input_file, output_file):\n",
    "    # Read the CSV file into a Pandas DataFrame\n",
    "    df = pd.read_csv(input_file)\n",
    "\n",
    "    # Add the prefix to the \"query\" column using the add_prefix function\n",
    "    df['query'] = df['query'].apply(add_prefix)\n",
    "\n",
    "    # Save the updated DataFrame to a new CSV file\n",
    "    df.to_csv(output_file, index=False)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    input_csv_file = 'data2/unique_bio2rdf_lsq_counts.csv' # Replace with the path to your input CSV file\n",
    "    output_csv_file = \"data2/prefixes_added_bio2rdf.csv\"  # Replace with the path to your output CSV file\n",
    "    add_prefix_to_csv(input_csv_file, output_csv_file)\n",
    "    print('Done!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "008a4e3a-ec80-4cd9-8bed-bf8c57626a3b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1519794\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>query</td>\n",
       "      <td>count</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>5502522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>189746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>128131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>27707</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0        1\n",
       "0                                              query    count\n",
       "1  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...  5502522\n",
       "2  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   189746\n",
       "3  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   128131\n",
       "4  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...    27707"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data2/prefixes_added_bio2rdf.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "# 1051680"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "393a298f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read the CSV file\n",
    "df = pd.read_csv('data2/prefixes_added_bio2rdf.csv')\n",
    "\n",
    "# Function to apply transformations to the first column\n",
    "def process_string(input_string):\n",
    "    # Replace <> with ?class\n",
    "    processed_string = input_string.replace('<>', '?class')\n",
    "    \n",
    "    # Replace http%3A with http:\n",
    "    processed_string = processed_string.replace('http%3A', 'http:')\n",
    "    \n",
    "    # Replace ?rOFFSET with ?r OFFSET\n",
    "    processed_string = processed_string.replace('?rOFFSET', '?r OFFSET')\n",
    "    \n",
    "    # Replace >PREFIX with >\\nPREFIX\n",
    "    processed_string = processed_string.replace('>PREFIX', '>\\nPREFIX')\n",
    "    \n",
    "    # Replace >SELECT with >\\nSELECT\n",
    "    processed_string = processed_string.replace('>SELECT', '>\\nSELECT')\n",
    "    \n",
    "    return processed_string\n",
    "\n",
    "# Apply the function to the first column\n",
    "df.iloc[:, 0] = df.iloc[:, 0].apply(process_string)\n",
    "\n",
    "# Write the modified dataframe to a new CSV file\n",
    "df.to_csv('data2/processed_prefixes_added_bio2rdf.csv', index=False)\n",
    "\n",
    "print(\"Processing complete. Output saved to 'processed_prefixes_added_bio2rdf.csv'\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c20cbf2-4cbf-4d56-83a3-56bb2b40e3f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  parse queries\n",
    "# Install Node.js dependencies\n",
    "!npm install sparqljs csv-parser csv-stringify\n",
    "\n",
    "# Create the JavaScript file\n",
    "js_code = \"\"\"\n",
    "const fs = require('fs');\n",
    "const SparqlParser = require('sparqljs').Parser;\n",
    "const csvParser = require('csv-parser');\n",
    "const { stringify } = require('csv-stringify');\n",
    "\n",
    "const parser = new SparqlParser();\n",
    "\n",
    "async function executeQuery(query) {\n",
    "  try {\n",
    "    const parsedQuery = parser.parse(query);\n",
    "    return JSON.stringify(parsedQuery);\n",
    "  } catch (error) {\n",
    "    console.error('Error parsing query:', query);\n",
    "    return 'Error parsing the query.';\n",
    "  }\n",
    "}\n",
    "\n",
    "async function writeBatchToCsv(batch, outputFile) {\n",
    "  return new Promise((resolve, reject) => {\n",
    "    const writeStream = fs.createWriteStream(outputFile, { flags: 'a' });\n",
    "    const csvStringifier = stringify({ header: false, columns: [ 'Parsed_Query','Count'], delimiter: ',' });\n",
    "\n",
    "    writeStream.on('error', (error) => {\n",
    "      reject(error);\n",
    "    });\n",
    "\n",
    "    csvStringifier.pipe(writeStream);\n",
    "\n",
    "    csvStringifier.on('end', () => {\n",
    "      writeStream.end();\n",
    "      resolve();\n",
    "    });\n",
    "\n",
    "    batch.forEach((entry) => {\n",
    "      csvStringifier.write([ entry.Parsed_Query , entry.Count ]);\n",
    "    });\n",
    "\n",
    "    csvStringifier.end();\n",
    "  });\n",
    "}\n",
    "\n",
    "async function main() {\n",
    "  const inputCsvFile = 'query_prefixes_added.csv';\n",
    "  const outputCsvFile = 'wikidata-robotic-parsed.csv';\n",
    "  const batchSize = 1000000;\n",
    "\n",
    "  const readStream = fs.createReadStream(inputCsvFile).pipe(csvParser());\n",
    "\n",
    "  let batch = [];\n",
    "  for await (const row of readStream) {\n",
    "    const sparqlQuery = row['query'];\n",
    "    const count = row['count'];\n",
    "\n",
    "    const parsedQuery = await executeQuery(sparqlQuery);\n",
    "\n",
    "    batch.push({ Parsed_Query: parsedQuery ,Count: count });\n",
    "\n",
    "    if (batch.length >= batchSize) {\n",
    "      await writeBatchToCsv(batch, outputCsvFile);\n",
    "      console.log(`Processed ${batch.length} queries.`);\n",
    "      batch = [];\n",
    "    }\n",
    "  }\n",
    "\n",
    "  if (batch.length > 0) {\n",
    "    await writeBatchToCsv(batch, outputCsvFile);\n",
    "  }\n",
    "\n",
    "  console.log('All SPARQL queries executed and results written to output CSV file.');\n",
    "}\n",
    "\n",
    "main();\n",
    "\"\"\"\n",
    "\n",
    "# Write JavaScript code to a file\n",
    "with open(\"optimized.js\", \"w\") as f:\n",
    "    f.write(js_code)\n",
    "\n",
    "# Python code to run the JavaScript script\n",
    "python_code = \"\"\"\n",
    "import subprocess\n",
    "\n",
    "def run_script():\n",
    "    # Run the JavaScript code using Node.js with an increased memory limit\n",
    "    result = subprocess.run(['node', '--max-old-space-size=30720', 'optimized.js'], stdout=subprocess.PIPE, text=True)\n",
    "\n",
    "    # Print the output (JSON representation of the parsed SPARQL query)\n",
    "    print(result.stdout)\n",
    "    print('hi')\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    run_script()\n",
    "\"\"\"\n",
    "\n",
    "# Write Python code to a file\n",
    "with open(\"optimized_from_js_.py\", \"w\") as f:\n",
    "    f.write(python_code)\n",
    "\n",
    "# Execute the script directly\n",
    "print(\"Running the script...\")\n",
    "\n",
    "!python optimized_from_js_.py > parseoutput.txt 2>&1\n",
    "\n",
    "# Print the log output\n",
    "with open(\"parseoutput.txt\", \"r\") as log_file:\n",
    "    output = log_file.read()\n",
    "    print(output)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4a1124ac-7aa3-444f-b58c-75b1ca0678e9",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Valid rows have been written to 'data2/valid_parsed.csv'.\n"
     ]
    }
   ],
   "source": [
    "# seperate valid queries and unvalid queries\n",
    "\n",
    "import csv\n",
    "\n",
    "# Define the input and output file paths\n",
    "input_file_path = 'data2/parsed.csv'\n",
    "output_file_path = 'data2/valid_parsed.csv'\n",
    "\n",
    "# Open the input CSV file for reading\n",
    "with open(input_file_path, 'r', newline='\\n', encoding='utf-8') as input_file:\n",
    "    # Create a CSV reader without header\n",
    "    reader = csv.reader(input_file)\n",
    "    \n",
    "    # Create a list to store valid rows\n",
    "    valid_rows = []\n",
    "    \n",
    "    for row in reader:\n",
    "        # Check if the second column is not equal to 'Error parsing the query'\n",
    "        if len(row) >= 3 and row[2] != 'Error parsing the query.':\n",
    "            valid_rows.append(row)\n",
    "\n",
    "# Open the output CSV file for writing\n",
    "with open(output_file_path, 'w', newline='\\n', encoding='utf-8') as output_file:\n",
    "    # Create a CSV writer with column names\n",
    "    writer = csv.writer(output_file)\n",
    "    \n",
    "    # Write column names\n",
    "    writer.writerow(['query', 'count' ,'parsed_query'])\n",
    "    \n",
    "    # Write the valid rows\n",
    "    writer.writerows(valid_rows)\n",
    "\n",
    "print(f\"Valid rows have been written to '{output_file_path}'.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "35139441-de51-4f25-8bf0-2abe68bdb6ab",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1514545\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>query</td>\n",
       "      <td>count</td>\n",
       "      <td>parsed_query\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>5502522</td>\n",
       "      <td>{\"queryType\":\"DESCRIBE\",\"variables\":[{\"termTyp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>189746</td>\n",
       "      <td>{\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>128131</td>\n",
       "      <td>{\"queryType\":\"ASK\",\"where\":[{\"type\":\"bgp\",\"tri...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>27707</td>\n",
       "      <td>{\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0        1  \\\n",
       "0                                              query    count   \n",
       "1  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...  5502522   \n",
       "2  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   189746   \n",
       "3  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   128131   \n",
       "4  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...    27707   \n",
       "\n",
       "                                                   2  \n",
       "0                                     parsed_query\\r  \n",
       "1  {\"queryType\":\"DESCRIBE\",\"variables\":[{\"termTyp...  \n",
       "2  {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...  \n",
       "3  {\"queryType\":\"ASK\",\"where\":[{\"type\":\"bgp\",\"tri...  \n",
       "4  {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/valid_parsed.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "#634784"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d81a4a12-b035-4e25-bd03-36351b382b3f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "#normalize parse tree\n",
    "\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "# The normalization function from the previous discussion\n",
    "def find_and_normalize_variables(parsed_query):\n",
    "    unique_vars = set()\n",
    "\n",
    "    def find_variables(node):\n",
    "        if isinstance(node, dict):\n",
    "            for key, value in node.items():\n",
    "                if isinstance(value, dict) or isinstance(value, list):\n",
    "                    find_variables(value)\n",
    "                elif key == \"value\" and node.get(\"termType\") == \"Variable\":\n",
    "                    unique_vars.add(value)\n",
    "        elif isinstance(node, list):\n",
    "            for item in node:\n",
    "                find_variables(item)\n",
    "\n",
    "    def normalize_variables(node, var_mapping):\n",
    "        if isinstance(node, dict):\n",
    "            for key, value in node.items():\n",
    "                if isinstance(value, dict) or isinstance(value, list):\n",
    "                    normalize_variables(value, var_mapping)\n",
    "                elif key == \"value\" and node.get(\"termType\") == \"Variable\":\n",
    "                    node[key] = var_mapping.get(value, value)\n",
    "        elif isinstance(node, list):\n",
    "            for item in node:\n",
    "                normalize_variables(item, var_mapping)\n",
    "\n",
    "    find_variables(parsed_query)\n",
    "    var_mapping = {var: f\"var{index+1}\" for index, var in enumerate(sorted(unique_vars))}\n",
    "    normalize_variables(parsed_query, var_mapping)\n",
    "\n",
    "    if \"variables\" in parsed_query:\n",
    "        for variable in parsed_query[\"variables\"]:\n",
    "            var_name = variable.get(\"value\")\n",
    "            if var_name in var_mapping:\n",
    "                variable[\"value\"] = var_mapping[var_name]\n",
    "\n",
    "    return parsed_query\n",
    "\n",
    "# Function to apply normalization to each row's parsed query\n",
    "def process_row(row):\n",
    "    parsed_query = json.loads(row['parsed_query'])\n",
    "    normalized_query = find_and_normalize_variables(parsed_query)\n",
    "    return json.dumps(normalized_query)\n",
    "\n",
    "# Read the CSV file\n",
    "df = pd.read_csv('data2/valid_parsed.csv')  # Make sure to replace 'input.csv' with your actual file path\n",
    "\n",
    "# Normalize parsed query for each row\n",
    "df['normalized_parse_tree'] = df.apply(process_row, axis=1)\n",
    "\n",
    "# Write the result to a new CSV file\n",
    "df.to_csv('data2/tree_normalized.csv', index=False)\n",
    "print('done')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6169c261-a0b2-4392-ad79-5fe726ca561f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1514545\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>query</td>\n",
       "      <td>count</td>\n",
       "      <td>parsed_query</td>\n",
       "      <td>normalized_parse_tree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>5502522</td>\n",
       "      <td>{\"queryType\":\"DESCRIBE\",\"variables\":[{\"termTyp...</td>\n",
       "      <td>{\"queryType\": \"DESCRIBE\", \"variables\": [{\"term...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>189746</td>\n",
       "      <td>{\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...</td>\n",
       "      <td>{\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>128131</td>\n",
       "      <td>{\"queryType\":\"ASK\",\"where\":[{\"type\":\"bgp\",\"tri...</td>\n",
       "      <td>{\"queryType\": \"ASK\", \"where\": [{\"type\": \"bgp\",...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>27707</td>\n",
       "      <td>{\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...</td>\n",
       "      <td>{\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0        1  \\\n",
       "0                                              query    count   \n",
       "1  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...  5502522   \n",
       "2  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   189746   \n",
       "3  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   128131   \n",
       "4  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...    27707   \n",
       "\n",
       "                                                   2  \\\n",
       "0                                       parsed_query   \n",
       "1  {\"queryType\":\"DESCRIBE\",\"variables\":[{\"termTyp...   \n",
       "2  {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...   \n",
       "3  {\"queryType\":\"ASK\",\"where\":[{\"type\":\"bgp\",\"tri...   \n",
       "4  {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...   \n",
       "\n",
       "                                                   3  \n",
       "0                              normalized_parse_tree  \n",
       "1  {\"queryType\": \"DESCRIBE\", \"variables\": [{\"term...  \n",
       "2  {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...  \n",
       "3  {\"queryType\": \"ASK\", \"where\": [{\"type\": \"bgp\",...  \n",
       "4  {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/tree_normalized.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "#634784"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c673643b-c87a-4314-b78e-e6c686b8ece0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique values in 'normalized_parse_tree': 1486540\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Path to your CSV file\n",
    "file_path = 'data2/tree_normalized.csv'\n",
    "\n",
    "# Read the CSV file into a DataFrame\n",
    "df = pd.read_csv(file_path, lineterminator='\\n')\n",
    "\n",
    "# Count the unique values in the 'normalized_parse_tree' column\n",
    "unique_values_count = df['normalized_parse_tree'].nunique()\n",
    "\n",
    "print(f\"Number of unique values in 'normalized_parse_tree': {unique_values_count}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5a685012-34e6-4313-a3ae-edd6ff1cd44a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing complete. Output written to data2/query_features.csv\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "import json\n",
    "\n",
    "\n",
    "# Load your CSV data from the input file\n",
    "input_csv_file = 'data2/tree_normalized.csv'\n",
    "output_csv_file = 'data2/query_features.csv'\n",
    "# Define a recursive function to handle nested path types\n",
    "def extract_predicate_values(items):\n",
    "    predicates = []\n",
    "    for item in items:\n",
    "        if \"value\" in item:  # Direct predicate\n",
    "            predicates.append(item[\"value\"])\n",
    "        elif \"pathType\" in item:  # Nested path\n",
    "            # Recursively extract nested predicates\n",
    "            nested_predicates = extract_predicate_values(item.get(\"items\", []))\n",
    "            predicates.extend(nested_predicates)\n",
    "        else:\n",
    "            predicates.append(\"Unknown\")\n",
    "    return predicates\n",
    "\n",
    "# Modify the extract_triples function to use the recursive function\n",
    "def extract_triples(parse_tree):\n",
    "    local_triples = []\n",
    "    if \"triples\" in parse_tree:\n",
    "        for triple in parse_tree[\"triples\"]:\n",
    "            try:\n",
    "                subject = triple.get(\"subject\", {}).get(\"value\", \"Unknown\")\n",
    "                \n",
    "                # Check if the predicate is a direct value or a path\n",
    "                if \"value\" in triple.get(\"predicate\", {}):  # Direct predicate\n",
    "                    predicate = triple[\"predicate\"][\"value\"]\n",
    "                elif \"pathType\" in triple.get(\"predicate\", {}):  # Path type predicate\n",
    "                    # Use the recursive function to handle nested path types\n",
    "                    items = triple[\"predicate\"].get(\"items\", [])\n",
    "                    predicate_values = extract_predicate_values(items)\n",
    "                    predicate = \", \".join(predicate_values)\n",
    "                else:\n",
    "                    predicate = \"nothing\"\n",
    "                \n",
    "                obj = triple.get(\"object\", {}).get(\"value\", \"Unknown\")\n",
    "                local_triples.append((subject, predicate, obj))\n",
    "            except KeyError as e:\n",
    "                print(f\"Error extracting triple: {e}\")\n",
    "                print(\"Offending triple:\", triple)\n",
    "    \n",
    "    # Recursively call the function on child nodes\n",
    "    for key, value in parse_tree.items():\n",
    "        if isinstance(value, dict):\n",
    "            local_triples += extract_triples(value)\n",
    "        elif isinstance(value, list):\n",
    "            for item in value:\n",
    "                if isinstance(item, dict):\n",
    "                    local_triples += extract_triples(item)\n",
    "    \n",
    "    return local_triples\n",
    "\n",
    "# Prepare to read the CSV file and process each row\n",
    "with open(input_csv_file, 'r') as csvfile, open(output_csv_file, 'w', newline='') as outfile:\n",
    "    reader = csv.reader(csvfile)\n",
    "    writer = None  # We'll define this after we read the header row\n",
    "\n",
    "    for row_index, row in enumerate(reader):\n",
    "        if row_index == 0:\n",
    "            # Initialize the CSV writer with the header row from the input file plus the 'triples' column\n",
    "            fieldnames = row + ['triples']\n",
    "            writer = csv.DictWriter(outfile, fieldnames=fieldnames)\n",
    "            writer.writeheader()\n",
    "            continue\n",
    "\n",
    "        # Initialize a dictionary for the current row, preserving all input columns\n",
    "        row_dict = {fieldnames[i]: row[i] for i in range(len(row))}\n",
    "\n",
    "        # Extract triples from the fourth column if it exists and is not empty\n",
    "        if len(row) > 3 and row[3]:\n",
    "            try:\n",
    "                parse_tree = json.loads(row[3])\n",
    "                triples = extract_triples(parse_tree)\n",
    "                # Convert the list of triples to a string format to store in the CSV\n",
    "                triples_str = ' \\\\+ '.join([f\"{s}, {p}, {o}\" for s, p, o in triples])\n",
    "                row_dict['triples'] = triples_str\n",
    "            except json.JSONDecodeError:\n",
    "                row_dict['triples'] = \"Invalid JSON\"\n",
    "\n",
    "        # Write the modified row with the triples to the output CSV\n",
    "        writer.writerow(row_dict)\n",
    "\n",
    "print(\"Processing complete. Output written to\", output_csv_file)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "68e36279-88fe-46e4-b40a-3105ad9bed70",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1514545\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>query</td>\n",
       "      <td>count</td>\n",
       "      <td>parsed_query</td>\n",
       "      <td>normalized_parse_tree</td>\n",
       "      <td>triples\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>5502522</td>\n",
       "      <td>{\"queryType\":\"DESCRIBE\",\"variables\":[{\"termTyp...</td>\n",
       "      <td>{\"queryType\": \"DESCRIBE\", \"variables\": [{\"term...</td>\n",
       "      <td>\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>189746</td>\n",
       "      <td>{\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...</td>\n",
       "      <td>{\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...</td>\n",
       "      <td>var1, http://www.w3.org/1999/02/22-rdf-syntax-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>128131</td>\n",
       "      <td>{\"queryType\":\"ASK\",\"where\":[{\"type\":\"bgp\",\"tri...</td>\n",
       "      <td>{\"queryType\": \"ASK\", \"where\": [{\"type\": \"bgp\",...</td>\n",
       "      <td>var3, var2, var1\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PREFIX rdf: &lt;http://www.w3.org/1999/02/22-rdf-...</td>\n",
       "      <td>27707</td>\n",
       "      <td>{\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...</td>\n",
       "      <td>{\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...</td>\n",
       "      <td>var1, http://www.w3.org/1999/02/22-rdf-syntax-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0        1  \\\n",
       "0                                              query    count   \n",
       "1  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...  5502522   \n",
       "2  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   189746   \n",
       "3  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...   128131   \n",
       "4  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...    27707   \n",
       "\n",
       "                                                   2  \\\n",
       "0                                       parsed_query   \n",
       "1  {\"queryType\":\"DESCRIBE\",\"variables\":[{\"termTyp...   \n",
       "2  {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...   \n",
       "3  {\"queryType\":\"ASK\",\"where\":[{\"type\":\"bgp\",\"tri...   \n",
       "4  {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...   \n",
       "\n",
       "                                                   3  \\\n",
       "0                              normalized_parse_tree   \n",
       "1  {\"queryType\": \"DESCRIBE\", \"variables\": [{\"term...   \n",
       "2  {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...   \n",
       "3  {\"queryType\": \"ASK\", \"where\": [{\"type\": \"bgp\",...   \n",
       "4  {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...   \n",
       "\n",
       "                                                   4  \n",
       "0                                          triples\\r  \n",
       "1                                                 \\r  \n",
       "2  var1, http://www.w3.org/1999/02/22-rdf-syntax-...  \n",
       "3                                 var3, var2, var1\\r  \n",
       "4  var1, http://www.w3.org/1999/02/22-rdf-syntax-...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/query_features.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "# 1514545"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cdc60633-7c83-4205-a0e2-e8229a3312c1",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 5900\n",
      "parsed_query             {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...\n",
      "normalized_parse_tree    {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...\n",
      "triples                  var1, http://bio2rdf.org/homologene_vocabulary...\n",
      "Name: 218, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1890\n",
      "parsed_query             {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...\n",
      "normalized_parse_tree    {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...\n",
      "triples                  var2, http://bio2rdf.org/ctd_vocabulary:diseas...\n",
      "Name: 1159, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1871\n",
      "parsed_query             {\"queryType\":\"CONSTRUCT\",\"template\":[{\"subject...\n",
      "normalized_parse_tree    {\"queryType\": \"CONSTRUCT\", \"template\": [{\"subj...\n",
      "triples                                                   var3, var2, var1\n",
      "Name: 1173, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1822\n",
      "parsed_query             {\"queryType\":\"CONSTRUCT\",\"template\":[{\"subject...\n",
      "normalized_parse_tree    {\"queryType\": \"CONSTRUCT\", \"template\": [{\"subj...\n",
      "triples                                                   var3, var2, var1\n",
      "Name: 1180, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1762\n",
      "parsed_query             {\"queryType\":\"SELECT\",\"distinct\":true,\"variabl...\n",
      "normalized_parse_tree    {\"queryType\": \"SELECT\", \"distinct\": true, \"var...\n",
      "triples                  var1, http://www.w3.org/1999/02/22-rdf-syntax-...\n",
      "Name: 1183, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1616\n",
      "parsed_query             {\"queryType\":\"CONSTRUCT\",\"template\":[{\"subject...\n",
      "normalized_parse_tree    {\"queryType\": \"CONSTRUCT\", \"template\": [{\"subj...\n",
      "triples                                                   var3, var2, var1\n",
      "Name: 1214, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1596\n",
      "parsed_query             {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...\n",
      "normalized_parse_tree    {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...\n",
      "triples                  var1, http://rdfs.org/ns/void#entities, var3\\n...\n",
      "Name: 1218, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1481\n",
      "parsed_query             {\"queryType\":\"CONSTRUCT\",\"template\":[{\"subject...\n",
      "normalized_parse_tree    {\"queryType\": \"CONSTRUCT\", \"template\": [{\"subj...\n",
      "triples                                                   var3, var2, var1\n",
      "Name: 1242, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                 1012\n",
      "parsed_query             {\"queryType\":\"SELECT\",\"variables\":[{}],\"where\"...\n",
      "normalized_parse_tree    {\"queryType\": \"SELECT\", \"variables\": [{}], \"wh...\n",
      "triples                  e_b0, http://rdfs.org/ns/void#subset, e_b1\\ne_...\n",
      "Name: 2193, dtype: object\n",
      "query                    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-...\n",
      "count                                                                  919\n",
      "parsed_query             {\"queryType\":\"SELECT\",\"variables\":[{\"termType\"...\n",
      "normalized_parse_tree    {\"queryType\": \"SELECT\", \"variables\": [{\"termTy...\n",
      "triples                  http://bio2rdf.org/goa_resource:true_1, http:/...\n",
      "Name: 2236, dtype: object\n"
     ]
    }
   ],
   "source": [
    "#find what are the blank nodes symbols\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Replace 'your_file.csv' with the path to your CSV file\n",
    "file_path = 'data2/query_features.csv'\n",
    "\n",
    "# Read the CSV file into a DataFrame\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "# Counter for cases found\n",
    "cases_found = 0\n",
    "\n",
    "# Iterate through the DataFrame\n",
    "for index, row in df.iterrows():\n",
    "    # Check if the third column (index 2) starts with 'e_'\n",
    "    if 'e_' in str(row[2]):\n",
    "        # Print the entire row\n",
    "        print(row)\n",
    "        # Increment the counter\n",
    "        cases_found += 1\n",
    "        # If 10 cases have been found, stop\n",
    "        if cases_found == 10:\n",
    "            break\n",
    "\n",
    "if cases_found == 0:\n",
    "    print(\"No cases found.\")\n",
    "\n",
    "#e_b\n",
    "#g_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1494e100-6921-4e3a-b9d0-49d01fde8ddc",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV file \"count_triples.csv\" has been created successfully.\n"
     ]
    }
   ],
   "source": [
    "# count and triples \n",
    "\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Define the path to the input CSV file\n",
    "input_csv_path = 'data2/query_features.csv'  # Make sure to replace this with the actual path to your CSV file\n",
    "\n",
    "# Define the path to the output CSV file\n",
    "output_csv_path = 'data2/count_triples.csv'\n",
    "\n",
    "# Read the input CSV file into a pandas DataFrame\n",
    "df = pd.read_csv(input_csv_path)\n",
    "\n",
    "# Select the 'count' and 'triples' columns\n",
    "selected_columns = df[['count', 'triples']]\n",
    "\n",
    "# Write the selected columns to the output CSV file\n",
    "selected_columns.to_csv(output_csv_path, index=False)\n",
    "\n",
    "print('CSV file \"count_triples.csv\" has been created successfully.')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "793a1cca-2100-4e5b-9d8f-4272d732ba07",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1514545\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>triples</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5502522</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>189746</td>\n",
       "      <td>var1, http://www.w3.org/1999/02/22-rdf-syntax-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>128131</td>\n",
       "      <td>var3, var2, var1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27707</td>\n",
       "      <td>var1, http://www.w3.org/1999/02/22-rdf-syntax-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         0                                                  1\n",
       "0    count                                            triples\n",
       "1  5502522                                                NaN\n",
       "2   189746  var1, http://www.w3.org/1999/02/22-rdf-syntax-...\n",
       "3   128131                                   var3, var2, var1\n",
       "4    27707  var1, http://www.w3.org/1999/02/22-rdf-syntax-..."
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/count_triples.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e188107-9784-46f5-ae75-a1eafb056be1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# we have 659 null\n",
    "import csv\n",
    "\n",
    "def process_triples(triples):\n",
    "    entities = []\n",
    "    predicates = []\n",
    "    for triple in triples.split(' \\\\+ '):\n",
    "        parts = triple.split(', ')\n",
    "        if len(parts) == 3:\n",
    "            subj, pred, obj = parts\n",
    "            if not (subj.startswith('var') or subj.startswith('e_b') or subj.startswith('g_') or 'nonsensical' in subj):\n",
    "                entities.append(subj)\n",
    "            if not (obj.startswith('var') or obj.startswith('e_b') or obj.startswith('g_') or 'nonsensical' in obj):\n",
    "                entities.append(obj)\n",
    "            if not (pred.startswith('var') or pred.startswith('e_b') or pred.startswith('g_') or 'nonsensical' in pred):\n",
    "                 predicates.append(pred)\n",
    "        elif len(parts) > 3:\n",
    "                entities.append(parts[0])\n",
    "                entities.append(parts[-1])\n",
    "                predicates.extend(parts[1:-1])\n",
    "        else:  # Handling cases where len(parts) == 2\n",
    "                # entities.extend(parts[:2])\n",
    "                # predicates.append(parts[-1])\n",
    "                print(\"Error: Triple does not have three elements:\", triple)\n",
    "                continue\n",
    "\n",
    "    return entities, predicates\n",
    "\n",
    "def write_output(input_csv, output_csv):\n",
    "    with open(input_csv, mode='r', encoding='utf-8') as infile, \\\n",
    "         open(output_csv, mode='w', newline='', encoding='utf-8') as outfile:\n",
    "        reader = csv.DictReader(infile)\n",
    "        fieldnames = reader.fieldnames + ['entities', 'predicates']\n",
    "        writer = csv.DictWriter(outfile, fieldnames=fieldnames)\n",
    "        writer.writeheader()\n",
    "        for row in reader:\n",
    "            entities, predicates = process_triples(row['triples'])\n",
    "            row['entities'] = '; '.join(entities)\n",
    "            row['predicates'] = '; '.join(predicates)\n",
    "            writer.writerow(row)\n",
    "\n",
    "# Replace 'input.csv' and 'output.csv' with the actual filenames\n",
    "input_csv = 'data2/count_triples.csv'\n",
    "output_csv = 'data2/entity_predicates.csv'\n",
    "\n",
    "write_output(input_csv, output_csv)\n",
    "print(\"Enhanced data with entities and predicates have been saved to\", output_csv)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2d84f86b-5b81-4f7b-a778-f5b586b71d29",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1514545\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>triples</td>\n",
       "      <td>entities</td>\n",
       "      <td>predicates\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5502522</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>189746</td>\n",
       "      <td>var1, http://www.w3.org/1999/02/22-rdf-syntax-...</td>\n",
       "      <td>http://chem.deri.ie/granatum/chemSpiderURI</td>\n",
       "      <td>http://www.w3.org/1999/02/22-rdf-syntax-ns#type\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>128131</td>\n",
       "      <td>var3, var2, var1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27707</td>\n",
       "      <td>var1, http://www.w3.org/1999/02/22-rdf-syntax-...</td>\n",
       "      <td>http://www4.wiwiss.fu-berlin.de/drugbank/resou...</td>\n",
       "      <td>http://www.w3.org/1999/02/22-rdf-syntax-ns#type\\r</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         0                                                  1  \\\n",
       "0    count                                            triples   \n",
       "1  5502522                                                NaN   \n",
       "2   189746  var1, http://www.w3.org/1999/02/22-rdf-syntax-...   \n",
       "3   128131                                   var3, var2, var1   \n",
       "4    27707  var1, http://www.w3.org/1999/02/22-rdf-syntax-...   \n",
       "\n",
       "                                                   2  \\\n",
       "0                                           entities   \n",
       "1                                                NaN   \n",
       "2         http://chem.deri.ie/granatum/chemSpiderURI   \n",
       "3                                                NaN   \n",
       "4  http://www4.wiwiss.fu-berlin.de/drugbank/resou...   \n",
       "\n",
       "                                                   3  \n",
       "0                                       predicates\\r  \n",
       "1                                                 \\r  \n",
       "2  http://www.w3.org/1999/02/22-rdf-syntax-ns#type\\r  \n",
       "3                                                 \\r  \n",
       "4  http://www.w3.org/1999/02/22-rdf-syntax-ns#type\\r  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data2/entity_predicates.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "# 634784"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6c6224c-ced6-4267-9ad8-31f96f092793",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Schema predicates unique\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4fb899fe-bed2-4eea-83a3-c3d1b15f540b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aggregated counts written to gold/8_unique_count_predicates_no_repeat.csv\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read the input CSV file\n",
    "input_csv = \"data2/entity_predicates.csv\"  # Update this path to your input CSV file\n",
    "df = pd.read_csv(input_csv)\n",
    "\n",
    "# Initialize a dictionary to hold the aggregated counts\n",
    "aggregated_counts = {}\n",
    "\n",
    "# Iterate through each row of the DataFrame\n",
    "for index, row in df.iterrows():\n",
    "    # Check if the \"entities\" column value is a string\n",
    "    if isinstance(row['predicates'], str):\n",
    "        # Split the entities and convert to a set of unique entities\n",
    "        unique_entities = set(row['predicates'].strip().split(';'))\n",
    "    else:\n",
    "        # If not a string, proceed with an empty set for this row\n",
    "        unique_entities = set()\n",
    "\n",
    "    # Aggregate counts across all rows\n",
    "    for entity in unique_entities:\n",
    "        entity = entity.strip()\n",
    "        if entity in aggregated_counts:\n",
    "            aggregated_counts[entity] += 1\n",
    "        else:\n",
    "            aggregated_counts[entity] = 1\n",
    "\n",
    "# Convert the aggregated dictionary to a DataFrame for writing to CSV\n",
    "output_df = pd.DataFrame(list(aggregated_counts.items()), columns=['predicate', 'TotalCount'])\n",
    "\n",
    "# Write the output DataFrame to a CSV file\n",
    "output_csv = \"gold/8_unique_count_predicates_no_repeat.csv\"  # Update this path to your output CSV file\n",
    "output_df.to_csv(output_csv, index=False)\n",
    "\n",
    "print(f\"Aggregated counts written to {output_csv}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b98aa5a8-768c-4a52-886c-a1fa06f9c509",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2280\n"
     ]
    },
    {
     "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>predicate</th>\n",
       "      <th>TotalCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.w3.org/1999/02/22-rdf-syntax-ns#type</td>\n",
       "      <td>783992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://bio2rdf.org/goa_vocabulary:process</td>\n",
       "      <td>4731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.w3.org/2000/01/rdf-schema#subClassOf</td>\n",
       "      <td>38062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://purl.org/linked-data/cube#codeList</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.w3.org/2004/02/skos/core#prefLabel</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         predicate TotalCount\n",
       "0  http://www.w3.org/1999/02/22-rdf-syntax-ns#type     783992\n",
       "1        http://bio2rdf.org/goa_vocabulary:process       4731\n",
       "2  http://www.w3.org/2000/01/rdf-schema#subClassOf      38062\n",
       "3        http://purl.org/linked-data/cube#codeList          2\n",
       "4    http://www.w3.org/2004/02/skos/core#prefLabel         15"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('gold/8_unique_count_predicates_no_repeat.csv', dtype=str)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "#1911"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2c3c006f-425d-428e-bec0-876c4d81b4d3",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aggregated counts written to heatmap_predicates77.csv\n"
     ]
    }
   ],
   "source": [
    "# extract valid predicates that exist in schema predicate\n",
    "\n",
    "import csv\n",
    "\n",
    "# File paths\n",
    "input_file_path_1 = 'gold/8_unique_count_predicates_no_repeat.csv'  # First input file with two columns: type, TotalCount\n",
    "input_file_path_2 = 'opredicate.csv'  # Second input file with one column\n",
    "output_file_path = 'fire/no-resource_heatmap_unique_predicates_no_repeat.csv'   # Output file\n",
    "\n",
    "# Read the types from the second input file into a set for faster search\n",
    "types_in_second_file = set()\n",
    "with open(input_file_path_2, mode='r', newline='') as file:\n",
    "    reader = csv.reader(file)\n",
    "    next(reader, None)  # Skip header if there is one\n",
    "    for row in reader:\n",
    "        types_in_second_file.add(row[0])\n",
    "\n",
    "# Read the first input file and write relevant rows to the output file\n",
    "with open(input_file_path_1, mode='r', newline='') as infile, \\\n",
    "     open(output_file_path, mode='w', newline='') as outfile:\n",
    "    reader = csv.reader(infile)\n",
    "    writer = csv.writer(outfile)\n",
    "    \n",
    "    header = next(reader)  # Assuming the first row is a header\n",
    "    writer.writerow(header)  # Write the header to the output file\n",
    "    \n",
    "    for row in reader:\n",
    "        if row[0] in types_in_second_file:  # Check if the type is in the second file\n",
    "            writer.writerow(row)  # Write the whole row to the output file\n",
    "print(\"Aggregated counts written to heatmap_predicates77.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "00e16867-df5a-49e7-8fa6-469fdd20a93a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "91\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>predicate</td>\n",
       "      <td>TotalCount\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.w3.org/1999/02/22-rdf-syntax-ns#type</td>\n",
       "      <td>783992\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.w3.org/2000/01/rdf-schema#subClassOf</td>\n",
       "      <td>38062\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://bio2rdf.org/drugbank_vocabulary:target</td>\n",
       "      <td>47415\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://bio2rdf.org/drugbank_vocabulary:indication</td>\n",
       "      <td>30381\\r</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0             1\n",
       "0                                          predicate  TotalCount\\r\n",
       "1    http://www.w3.org/1999/02/22-rdf-syntax-ns#type      783992\\r\n",
       "2    http://www.w3.org/2000/01/rdf-schema#subClassOf       38062\\r\n",
       "3      http://bio2rdf.org/drugbank_vocabulary:target       47415\\r\n",
       "4  http://bio2rdf.org/drugbank_vocabulary:indication       30381\\r"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('fire/no-resource_heatmap_unique_predicates_no_repeat.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "619f4409-5089-4021-9f10-b6b4e633860c",
   "metadata": {},
   "source": [
    "# Unique queries\n",
    "## Schema classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "33e10f1f-13db-405a-b5b5-9d9255313172",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aggregated counts written to data/8_unique_count_entity.csv\n"
     ]
    }
   ],
   "source": [
    "# unique count\n",
    "# extract enties and their count\n",
    "# ignore query count\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Read the input CSV file\n",
    "input_csv = \"data/entity_predicates.csv\"  # Update this path to your input CSV file\n",
    "df = pd.read_csv(input_csv)\n",
    "\n",
    "# Initialize a dictionary to hold the aggregated counts\n",
    "aggregated_counts = {}\n",
    "\n",
    "# Iterate through each row of the DataFrame\n",
    "for index, row in df.iterrows():\n",
    "    # Check if the \"entities\" column value is a string\n",
    "    if isinstance(row['entities'], str):\n",
    "        # Split the entities and count occurrences\n",
    "        entities = row['entities'].strip().split(';')\n",
    "        row_entities_count = {entity: entities.count(entity) for entity in set(entities)}\n",
    "    else:\n",
    "        # If not a string, proceed with an empty dictionary for this row\n",
    "        row_entities_count = {}\n",
    "    \n",
    "    # Multiply each entity's count in the row by the \"count\" value for the row\n",
    "    multiplied_counts = {entity: count  for entity, count in row_entities_count.items()}\n",
    "    \n",
    "    # Aggregate counts across all rows\n",
    "    for entity, count in multiplied_counts.items():\n",
    "        entity = entity.strip()\n",
    "        if entity in aggregated_counts:\n",
    "            aggregated_counts[entity] += count\n",
    "        else:\n",
    "            aggregated_counts[entity] = count\n",
    "\n",
    "# Convert the aggregated dictionary to a DataFrame for writing to CSV\n",
    "output_df = pd.DataFrame(list(aggregated_counts.items()), columns=['Entity', 'TotalCount'])\n",
    "\n",
    "# Write the output DataFrame to a CSV file\n",
    "output_csv = \"data/8_unique_count_entity.csv\"  # Update this path to your output CSV file\n",
    "output_df.to_csv(output_csv, index=False)\n",
    "\n",
    "print(f\"Aggregated counts written to {output_csv}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "376917ab-eea6-4442-989e-93ca7b26c572",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31004\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Entity</td>\n",
       "      <td>TotalCount</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://example.org/thing</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://example.org/string</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>false</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0           1\n",
       "0                     Entity  TotalCount\n",
       "1   http://example.org/thing           5\n",
       "2  http://example.org/string           1\n",
       "3                      false           2\n",
       "4                         12           1"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data/8_unique_count_entity.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()\n",
    "#507"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb410ca0-9962-44a6-8165-acc5fddfc786",
   "metadata": {},
   "outputs": [],
   "source": [
    "# extract types and their count\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Read the input CSV file\n",
    "input_csv = \"data/8_unique_count_entity.csv\"  # Update this path to your input CSV file\n",
    "df = pd.read_csv(input_csv)\n",
    "\n",
    "# Initialize a dictionary to hold the aggregated counts\n",
    "aggregated_counts = {}\n",
    "\n",
    "# Iterate through each row of the DataFrame\n",
    "for index, row in df.iterrows():\n",
    "    # Check if the \"entities\" column value is a string\n",
    "    if isinstance(row['types'], str):\n",
    "        # Split the entities and count occurrences\n",
    "        entities = row['types'].strip().split('\\n')\n",
    "        row_entities_count = {entity: entities.count(entity) for entity in set(entities)}\n",
    "    else:\n",
    "        # If not a string, proceed with an empty dictionary for this row\n",
    "        row_entities_count = {}\n",
    "    \n",
    "    # Multiply each entity's count in the row by the \"count\" value for the row\n",
    "    multiplied_counts = {entity: count * row['TotalCount'] for entity, count in row_entities_count.items()}\n",
    "    \n",
    "    # Aggregate counts across all rows\n",
    "    for entity, count in multiplied_counts.items():\n",
    "        entity = entity.strip()\n",
    "        if entity in aggregated_counts:\n",
    "            aggregated_counts[entity] += count\n",
    "        else:\n",
    "            aggregated_counts[entity] = count\n",
    "\n",
    "# Convert the aggregated dictionary to a DataFrame for writing to CSV\n",
    "output_df = pd.DataFrame(list(aggregated_counts.items()), columns=['type', 'TotalCount'])\n",
    "\n",
    "# Write the output DataFrame to a CSV file\n",
    "output_csv = \"data/8_unique_count_types.csv\"  # Update this path to your output CSV file\n",
    "output_df.to_csv(output_csv, index=False)\n",
    "\n",
    "print(f\"Aggregated counts written to {output_csv}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "710d6873-6da7-4952-b589-82a4fa32155a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data/8_unique_count_types3.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "89f92547-33c1-469c-b429-9faaa6c74cd4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aggregated counts written to unique_heatmap_types.csv\n"
     ]
    }
   ],
   "source": [
    "# extract the types from queries that exist in KG schema\n",
    "\n",
    "import csv\n",
    "\n",
    "# File paths\n",
    "input_file_path_1 = 'data/8_unique_count_entity.csv'  # First input file with two columns: type, TotalCount\n",
    "input_file_path_2 = 'data/schema_classes_25_March.csv'  # Second input file with one column\n",
    "output_file_path = 'data/unique_heatmap_types3.csv'   # Output file\n",
    "\n",
    "# Read the types from the second input file into a set for faster search\n",
    "types_in_second_file = set()\n",
    "with open(input_file_path_2, mode='r', newline='') as file:\n",
    "    reader = csv.reader(file)\n",
    "    next(reader, None)  # Skip header if there is one\n",
    "    for row in reader:\n",
    "        types_in_second_file.add(row[0])\n",
    "\n",
    "# Read the first input file and write relevant rows to the output file\n",
    "with open(input_file_path_1, mode='r', newline='') as infile, \\\n",
    "     open(output_file_path, mode='w', newline='') as outfile:\n",
    "    reader = csv.reader(infile)\n",
    "    writer = csv.writer(outfile)\n",
    "    \n",
    "    header = next(reader)  # Assuming the first row is a header\n",
    "    writer.writerow(header)  # Write the header to the output file\n",
    "    \n",
    "    for row in reader:\n",
    "        if row[0] in types_in_second_file:  # Check if the type is in the second file\n",
    "            writer.writerow(row)  # Write the whole row to the output file\n",
    "        # else:\n",
    "        #     print( row[0])\n",
    "print(\"Aggregated counts written to unique_heatmap_types.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "55c56bf1-b032-4837-bfe6-4ec4d633983c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "526\n"
     ]
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Entity</td>\n",
       "      <td>TotalCount\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://bio2rdf.org/drugbank_vocabulary:Drug</td>\n",
       "      <td>121164\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://bio2rdf.org/hgnc_vocabulary:Resource</td>\n",
       "      <td>198\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://bio2rdf.org/omim_vocabulary:Gene</td>\n",
       "      <td>69374\\r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://bio2rdf.org/hgnc_vocabulary:Gene-Symbol</td>\n",
       "      <td>104\\r</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                0             1\n",
       "0                                          Entity  TotalCount\\r\n",
       "1     http://bio2rdf.org/drugbank_vocabulary:Drug      121164\\r\n",
       "2     http://bio2rdf.org/hgnc_vocabulary:Resource         198\\r\n",
       "3         http://bio2rdf.org/omim_vocabulary:Gene       69374\\r\n",
       "4  http://bio2rdf.org/hgnc_vocabulary:Gene-Symbol         104\\r"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data/unique_heatmap_types3.csv', lineterminator='\\n', dtype=str, header=None)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a5ca4d59-4c9d-4e18-9215-84e32428fbd4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File saved as excluded_resources_heatmap_types.csv with rows not ending in \":Resource\".\n"
     ]
    }
   ],
   "source": [
    "# excluded_resources from schema classes\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Load the CSV file\n",
    "df = pd.read_csv('data/unique_heatmap_types3.csv')\n",
    "\n",
    "# Filter rows where 'types' column does not end with ':Resource'\n",
    "filtered_df = df[~df['Entity'].str.endswith(':Resource')]\n",
    "\n",
    "# Save the filtered data to a new CSV file\n",
    "filtered_df.to_csv('data/excluded_resources_heatmap_types3.csv', index=False)\n",
    "\n",
    "print('File saved as excluded_resources_heatmap_types.csv with rows not ending in \":Resource\".')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:root] *",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}