ETL _PROCESS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "60e84d73-59e4-4062-96bf-2922849a27af",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# The medallion lakehouse architecture"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0a6cbe33-850e-4778-9999-780a94e6abfd",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##### 1/. *BRONZE LAYER:* LOADING DATA FROM BLOB STORAGE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5dd07c57-5fc2-48eb-a92e-b91555c9cdaf",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "A. Access to azure SQL database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "445fa88c-2e8f-4159-b4f0-1a96cfa1a7b9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\n|               title|            abstract|      date|            inventor|             country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|world intellectua...|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|               china|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|world intellectua...|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|               china|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|world intellectua...|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|european patent o...|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|european patent o...|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|               china|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|               china|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|              canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|              canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|               china|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|               china|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|               china|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|               china|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|               china|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|               china|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|               china|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|               china|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|               china|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# Configure connection properties\n",
    "jdbcHostname = \"najma.database.windows.net\"\n",
    "jdbcPort = 1433\n",
    "jdbcDatabase = \"datatables\"\n",
    "jdbcUsername = \"Fatima\"\n",
    "jdbcPassword = \"Away!123\"\n",
    "\n",
    "# Create the JDBC URL\n",
    "jdbcUrl = f\"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};database={jdbcDatabase}\"\n",
    "\n",
    "connectionProperties = {\n",
    "    \"user\": jdbcUsername,\n",
    "    \"password\": jdbcPassword,\n",
    "    \"driver\": \"com.microsoft.sqlserver.jdbc.SQLServerDriver\"\n",
    "}\n",
    "\n",
    "# Load data from Azure SQL Database\n",
    "table_name = \"staging_table\"\n",
    "df = spark.read.jdbc(url=jdbcUrl, table=table_name, properties=connectionProperties)\n",
    "\n",
    "# Show the loaded data\n",
    "df.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a06983c8-3c9f-4226-9020-31a69191e5dd",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "**Bronze Layer (Raw Data):**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5a709080-7100-4bf4-a311-0f4d10157a54",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {
        "dataframeName": null
       },
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "DROP table if exists bronze_table_1;\n",
    "CREATE TABLE bronze_table_1 (\n",
    "    title STRING,\n",
    "    abstract STRING,\n",
    "    date STRING,\n",
    "    inventor STRING,\n",
    "    country STRING,\n",
    "    document_id STRING,\n",
    "    language STRING\n",
    ")\n",
    "USING DELTA\n",
    "LOCATION '/mnt/bronze_table6'\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "80318dd2-d793-4216-905c-75ca37508e60",
     "showTitle": false,
     "title": ""
    }
   },
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "662f3ddf-91b9-4745-abd8-c237eb80f64f",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "**Load data on Bronze table:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "04a33ac7-7dde-4a3d-9821-63c471a0e462",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Assuming raw_data_df is your DataFrame containing the raw data\n",
    "df.write.format(\"delta\").mode(\"append\").save(\"/mnt/bronze_table6\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "19ab46d3-1753-464e-9ba2-7205a403ae03",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "select * from bronze_table_1;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5e5055f3-12c8-4011-ac78-3631370e9598",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>count(1)</th></tr></thead><tbody><tr><td>15405</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         15405
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {
        "dataframeName": "_sqldf",
        "executionCount": 19
       },
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{\"__autoGeneratedAlias\":\"true\"}",
         "name": "count(1)",
         "type": "\"long\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "select count(*) from bronze_table_1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5c66c292-b9cb-45ed-aafc-36cf6e852e41",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "**Silver data: DATA VALIDAZTION**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "72a7ff08-b340-4f5c-94de-f3ea917559ff",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bronze_df = spark.read.format(\"delta\").load(\"/mnt/bronze_table6\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0291c702-bf05-4f45-9d22-2aa1824c8b3e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\n|               title|            abstract|      date|            inventor|             country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|world intellectua...|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|               china|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|world intellectua...|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|               china|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|world intellectua...|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|european patent o...|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|european patent o...|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|               china|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|               china|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|              canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|              canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|               china|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|               china|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|               china|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|               china|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|               china|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|               china|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|               china|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|               china|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|               china|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "bronze_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a4001d25-e17b-413d-bd06-ad7209682b37",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "1. Transformed to correct format date yyyy-MM-dd:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2149384a-33b2-48b0-92f2-b03f395716b3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import to_date, date_format\n",
    "\n",
    "# Check if the 'date' column exists in the DataFrame\n",
    "if 'date' in bronze_df.columns:\n",
    "    df = bronze_df.withColumn('date', date_format(to_date(bronze_df['date'], 'yyyy-MM-dd'), 'yyyy-MM-dd'))\n",
    "else:\n",
    "    # Handle the case when 'date' column is missing\n",
    "    print(\"The 'date' column does not exist in the DataFrame.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "621901a6-b5c9-4185-91b5-f81d13acc77f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\n|               title|            abstract|      date|            inventor|             country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|world intellectua...|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|               china|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|world intellectua...|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|               china|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|world intellectua...|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|european patent o...|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|european patent o...|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|               china|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|               china|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|              canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|              canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|               china|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|               china|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|               china|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|               china|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|               china|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|               china|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|               china|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|               china|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|               china|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+--------------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c2fb8c4e-456c-42ec-a3a7-4ae2ff178f18",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "1. Country column Processing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0336936c-5df7-405c-92a3-a1301d91301e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import col, when"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "8830a9ff-ac04-4b3a-b1fc-06c55f5509c9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "def replace_country(dataframe, column_name):\n",
    "    dataframe = dataframe.withColumn(column_name,\n",
    "                                     when((col(column_name) == 'world intellectual property organization wipo') |\n",
    "                                          (col(column_name) == 'World Intellectual Property Organization (WIPO)') |\n",
    "                                          (col(column_name) == 'European Patent Office (EPO)') |\n",
    "                                          (col(column_name) == 'Eurasian Patent Organization (EAPO)') |\n",
    "                                          (col(column_name) == 'eurasian patent organization eapo') |\n",
    "                                          (col(column_name) == 'european patent office epo') |\n",
    "                                          (col(column_name) == 'European Patent Office') |\n",
    "                                          (col(column_name) == 'WIPO (PCT)'),\n",
    "                                          'unknown country').otherwise(col(column_name)))\n",
    "    return dataframe\n",
    "\n",
    "column_name = 'country'  # Name of the column to be replaced\n",
    "\n",
    "df = replace_country(df, column_name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3dd32572-b248-4e95-aff3-68a20a0668de",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          china|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          china|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          china|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          china|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          china|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          china|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          china|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          china|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          china|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          china|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          china|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          china|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          china|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# Show the DataFrame\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "bbc40eef-fdca-4855-9cb9-ce11651919fe",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Distinct Countries: ['australia', 'Russia', 'portugal', 'us', 'south korea', 'jp', 'Germany', 'lv', 'vn', 'pl', 'cn', 'za', 'France', 'japan', 'Taiwan', 'pt', 'in', 'au', 'sg', 'Finland', 'China', 'United States', 'gb', 'serbia', 'br', 'Spain', 'es', 'canada', 'Denmark', 'kr', 'france', 'ar', 'ph', 'nl', 'United States of America', 'South Korea', 'ca', 'my', 'nz', 'ee', 'united states of america', 'ru', 'united kingdom great britain', 'lt', 'germany', 'Canada', 'russia', 'wo', 'Brazil', 'mx', 'Japan', 'china', 'fr', 'Country', 'Australia', 'spain', 'dk', 'id', 'Serbia', 'ep', 'United Kingdom', 'Netherlands']\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import col\n",
    "\n",
    "# Function to determine distinct countries\n",
    "def get_distinct_countries(df):\n",
    "    countries_df = df.select(col(\"country\")).distinct().filter(col(\"country\") != \"unknown country\")\n",
    "    countries = [row[\"country\"] for row in countries_df.collect()]\n",
    "    return countries\n",
    "\n",
    "# Call the function\n",
    "distinct_countries = get_distinct_countries(df)\n",
    "print(\"Distinct Countries:\", distinct_countries)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5b165607-7f3a-4de1-985f-9b57b3b3d4dc",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import udf\n",
    "from pyspark.sql.types import StringType\n",
    "\n",
    "# Mapping dictionary for normalization\n",
    "country_mapping = {\n",
    "    'australia': 'Australia',\n",
    "    'russia': 'Russia',\n",
    "    'portugal': 'Portugal',\n",
    "    'us': 'United States',\n",
    "    'south korea': 'South Korea',\n",
    "    'jp': 'Japan',\n",
    "    'germany': 'Germany',\n",
    "    'lv': 'Latvia',\n",
    "    'vn': 'Vietnam',\n",
    "    'pl': 'Poland',\n",
    "    'cn': 'China',\n",
    "    'za': 'South Africa',\n",
    "    'france': 'France',\n",
    "    'japan': 'Japan',\n",
    "    'taiwan': 'Taiwan',\n",
    "    'pt': 'Portugal',\n",
    "    'in': 'India',\n",
    "    'au': 'Australia',\n",
    "    'sg': 'Singapore',\n",
    "    'finland': 'Finland',\n",
    "    'united states': 'United States',\n",
    "    'gb': 'United Kingdom',\n",
    "    'serbia': 'Serbia',\n",
    "    'br': 'Brazil',\n",
    "    'spain': 'Spain',\n",
    "    'es': 'Spain',\n",
    "    'canada': 'Canada',\n",
    "    'denmark': 'Denmark',\n",
    "    'kr': 'South Korea',\n",
    "    'ar': 'Argentina',\n",
    "    'ph': 'Philippines',\n",
    "    'nl': 'Netherlands',\n",
    "    'united states of america': 'United States',\n",
    "    'ca': 'Canada',\n",
    "    'my': 'Malaysia',\n",
    "    'nz': 'New Zealand',\n",
    "    'ee': 'Estonia',\n",
    "    'ru': 'Russia',\n",
    "    'united kingdom great britain': 'United Kingdom',\n",
    "    'lt': 'Lithuania',\n",
    "    'wo': 'unknown country',\n",
    "    'mx': 'Mexico',\n",
    "    'spain': 'Spain',\n",
    "    'dk': 'Denmark',\n",
    "    'id': 'Indonesia',\n",
    "    'united kingdom': 'United Kingdom',\n",
    "    'ep': 'Europe',\n",
    "    'china': 'China',\n",
    "    'fr': 'France',\n",
    "    'Country':'unknown country'\n",
    "    \n",
    "}\n",
    "\n",
    "# UDF for transforming country names\n",
    "@udf(StringType())\n",
    "def normalize_country(country):\n",
    "    return country_mapping.get(country.lower(), country)\n",
    "\n",
    "# Function to apply transformation to country column\n",
    "def transform_country_column(df, country_column):\n",
    "    df_transformed = df.withColumn(country_column, normalize_country(df[country_column]))\n",
    "    return df_transformed\n",
    "\n",
    "\n",
    "# Apply transformation to country column\n",
    "df_transformed = transform_country_column(df, \"country\")\n",
    "\n",
    "# Show the transformed DataFrame\n",
    "df_transformed.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "68b0c93b-6d5f-472f-8626-396c4b657885",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Russia', 'Philippines', 'Malaysia', 'Singapore', 'Germany', 'Europe', 'France', 'Taiwan', 'Argentina', 'Finland', 'China', 'United States', 'India', 'Lithuania', 'Spain', 'Denmark', 'South Korea', 'Mexico', 'unknown country', 'Estonia', 'Indonesia', 'Latvia', 'Canada', 'Brazil', 'Japan', 'New Zealand', 'Country', 'Poland', 'Portugal', 'Australia', 'Serbia', 'South Africa', 'United Kingdom', 'Vietnam', 'Netherlands']\n"
     ]
    }
   ],
   "source": [
    "# Select the 'country' column and get distinct values\n",
    "distinct_countries = df_transformed.select('country').distinct()\n",
    "\n",
    "# Collect the distinct country names\n",
    "country_names = [row.country for row in distinct_countries.collect()]\n",
    "\n",
    "print(country_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5d617d84-1cbc-4772-96c9-77e7840d84dd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_transformed.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3b573756-9747-4783-aad6-75fbfc1f3897",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of null values in the 'country' column: 0\nNumber of non-null values in the 'country' column: 15405\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import col\n",
    "\n",
    "# Check for null values in the 'country' column\n",
    "null_country_count = df_transformed.filter((col('country').isNull()) | (col('country') == '') | (col('country') == 'NULL')).count()\n",
    "\n",
    "print(\"Number of null values in the 'country' column:\", null_country_count)\n",
    "\n",
    "# Alternatively, you can check for non-null values using isNotNull()\n",
    "non_null_country_count = df_transformed.filter(col('country').isNotNull() & (col('country') != '') & (col('country') != 'NULL')).count()\n",
    "\n",
    "print(\"Number of non-null values in the 'country' column:\", non_null_country_count)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "1649a2ed-f2c3-4014-811c-f2502b1d9afd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_silver = df_transformed# No transformation for this example\n",
    "\n",
    "df_transformed.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c908b001-a2f8-482c-bd9e-dd5e4443e451",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {
        "dataframeName": null
       },
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "DROP TABLE IF EXISTS silver_table;  -- Correcting the table name\n",
    "CREATE TABLE silver_table(\n",
    "    title STRING,\n",
    "    abstract STRING,\n",
    "    date STRING,\n",
    "    inventor STRING,\n",
    "    country STRING,\n",
    "    document_id STRING,\n",
    "    language STRING\n",
    ")\n",
    "USING DELTA\n",
    "LOCATION '/mnt/ SilverTable'; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b65caae4-2d13-4d60-9027-893ae6df6d1d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[FileInfo(path='dbfs:/mnt/GoldTable/', name='GoldTable/', size=0, modificationTime=1717010002000),\n",
       " FileInfo(path='dbfs:/mnt/GoldTable1/', name='GoldTable1/', size=0, modificationTime=1717010194000),\n",
       " FileInfo(path='dbfs:/mnt/GoldTable2/', name='GoldTable2/', size=0, modificationTime=1717010269000),\n",
       " FileInfo(path='dbfs:/mnt/bronze_table5/', name='bronze_table5/', size=0, modificationTime=1716992043000),\n",
       " FileInfo(path='dbfs:/mnt/bronze_table6/', name='bronze_table6/', size=0, modificationTime=1717005227000),\n",
       " FileInfo(path='dbfs:/mnt/gold_data/', name='gold_data/', size=0, modificationTime=1716995587000),\n",
       " FileInfo(path='dbfs:/mnt/silver_table/', name='silver_table/', size=0, modificationTime=1716991800000),\n",
       " FileInfo(path='dbfs:/mnt/temp_delta_location/', name='temp_delta_location/', size=0, modificationTime=1716994272000)]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List files in the '/mnt/' directory\n",
    "dbutils.fs.ls(\"/mnt/\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "4074f82d-91a0-4ef1-b20a-06e30a94e215",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- title: string (nullable = true)\n |-- abstract: string (nullable = true)\n |-- date: string (nullable = true)\n |-- inventor: string (nullable = true)\n |-- country: string (nullable = true)\n |-- document_id: string (nullable = true)\n |-- language: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import col\n",
    "\n",
    "\n",
    "\n",
    "# Verify the schema after casting\n",
    "df_transformed.printSchema()\n",
    "\n",
    "# Append the DataFrame to the Delta table with mergeSchema option\n",
    "df_transformed.write.format(\"delta\").mode(\"append\").option(\"mergeSchema\", \"true\").save(\"/mnt/SilverTable\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "b25dcbe9-c6fa-43ab-ad8f-221eddb3c839",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "### Gold Layer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "773c4f1d-0d3b-47da-81b0-c52854a1d7ef",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df_Gold= df_silver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b086d643-8e50-49e6-a9ad-3ece6bae2d9b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_Gold.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c5164150-63c2-4a60-a092-fcc5eebf37c6",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Performe some analysis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6518d8d0-35a0-41ba-be80-c522dd414bf8",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+-----+\n|country        |count|\n+---------------+-----+\n|China          |5866 |\n|United States  |4926 |\n|unknown country|1838 |\n|Japan          |760  |\n|Australia      |504  |\n|Russia         |398  |\n|Canada         |249  |\n|Spain          |229  |\n|South Korea    |177  |\n|Europe         |131  |\n|Taiwan         |104  |\n|India          |71   |\n|Denmark        |60   |\n|Germany        |30   |\n|Mexico         |9    |\n|Netherlands    |7    |\n|France         |6    |\n|Country        |6    |\n|New Zealand    |5    |\n|United Kingdom |5    |\n+---------------+-----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import col\n",
    "\n",
    "# Group by 'country' and count the number of patents\n",
    "patents_per_country = df_Gold.groupBy('country').count().orderBy(col('count').desc())\n",
    "\n",
    "# Show the result\n",
    "patents_per_country.show(truncate=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "aa19b239-5f7c-4c72-b76f-55208ad0888c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_Gold.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "11e3b689-9219-4041-80e6-6a84535bf136",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Set the legacy time parser policy\n",
    "spark.conf.set(\"spark.sql.legacy.timeParserPolicy\", \"LEGACY\")\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "49e2283a-d384-402f-b933-c520fe17b5fe",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|year|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|2010|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|2003|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|1999|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|2005|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|2003|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|1989|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|1993|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|2022|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|2009|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|2021|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|2021|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|1997|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|2008|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|2019|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|2022|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|2022|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|2000|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|2023|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|2015|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|2015|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "from pyspark.sql.functions import udf, col, when\n",
    "from pyspark.sql.types import DateType\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "# Define a function to generate a random date between 2020-01-01 and 2023-12-31\n",
    "def generate_random_date():\n",
    "    start_date = datetime(2020, 1, 1)\n",
    "    end_date = datetime(2023, 12, 31)\n",
    "    delta = end_date - start_date\n",
    "    random_days = random.randint(0, delta.days)\n",
    "    random_date = start_date + timedelta(days=random_days)\n",
    "    return random_date\n",
    "\n",
    "# Register the function as a UDF\n",
    "generate_random_date_udf = udf(generate_random_date, DateType())\n",
    "\n",
    "# Replace NULL dates with random dates\n",
    "df_gold = df.withColumn('date', col('date').cast(DateType()))  # Ensure 'date' column is of DateType\n",
    "df_gold = df_gold.withColumn('date', when(col('date').isNotNull(), col('date'))\n",
    "                                    .otherwise(generate_random_date_udf()))\n",
    "\n",
    "# Verify the replacement\n",
    "df_gold.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3c876893-8be6-4321-84cb-44c73b08fdaf",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|year|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|2010|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|2003|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|1999|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|2005|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|2003|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|1989|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|1993|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|2022|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|2009|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|2021|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|2021|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|1997|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|2008|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|2019|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|2022|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|2022|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|2000|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|2023|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|2015|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|2015|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_g.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "4114ec1c-5ed5-446e-8c1a-0af0565ea5a0",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- title: string (nullable = true)\n |-- abstract: string (nullable = true)\n |-- date: date (nullable = true)\n |-- inventor: string (nullable = true)\n |-- country: string (nullable = true)\n |-- document_id: string (nullable = true)\n |-- language: string (nullable = true)\n |-- year: integer (nullable = true)\n\n+----+-----+\n|year|count|\n+----+-----+\n|1973|    1|\n|1979|    1|\n|1982|    1|\n|1983|    5|\n|1984|    2|\n|1985|    6|\n|1986|   19|\n|1987|   15|\n|1988|   16|\n|1989|   15|\n|1990|   13|\n|1991|   14|\n|1992|   22|\n|1993|   24|\n|1994|   20|\n|1995|   24|\n|1996|   30|\n|1997|   16|\n|1998|   19|\n|1999|   33|\n+----+-----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import year, col\n",
    "\n",
    "# Check the column names in the DataFrame\n",
    "df.printSchema()\n",
    "\n",
    "# Extract year from 'date' column\n",
    "df = df_g.withColumn('year', year(col('date')))\n",
    "\n",
    "# Group by 'year' and count the number of patents\n",
    "patents_per_year = df.groupBy('year').count().orderBy(col('year'))\n",
    "\n",
    "# Show the result\n",
    "patents_per_year.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "02c53148-92b5-47e2-9d2c-fc4087608a7c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n|            inventor|count|\n+--------------------+-----+\n|         tian kegong|   66|\n|                    |   61|\n|                  ru|   60|\n|          zhang xuke|   56|\n|lanzhou veterinar...|   34|\n|         wang hualin|   31|\n|          univ fudan|   28|\n|        zheng haixue|   24|\n|         Katie Brown|   22|\n|        Laura Taylor|   20|\n|      Michael Wilson|   20|\n|         Laura Moore|   20|\n|         Laura Brown|   20|\n|      yusibov vidadi|   20|\n|         Chris Brown|   20|\n|hubei xinzongke v...|   19|\n|       univ yangzhou|   19|\n|       Michael Smith|   19|\n|univ huazhong agr...|   19|\n|         Chris Davis|   19|\n+--------------------+-----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "# Group by 'inventor' and count the number of patents\n",
    "top_inventors = df_gold.groupBy('inventor').count().orderBy(col('count').desc()).limit(50)\n",
    "\n",
    "# Show the result\n",
    "top_inventors.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "9ed4d525-bd6a-41d9-bcd3-82d3cebd8d12",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Check null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f6a07995-9051-47ed-b6a9-9985ac404423",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+--------+----+--------+-------+-----------+--------+----+\n|title|abstract|date|inventor|country|document_id|language|year|\n+-----+--------+----+--------+-------+-----------+--------+----+\n+-----+--------+----+--------+-------+-----------+--------+----+\n\n+----------------+--------+----------+------------+-------+--------------------+--------+----+\n|           title|abstract|      date|    inventor|country|         document_id|language|year|\n+----------------+--------+----------+------------+-------+--------------------+--------+----+\n|    ????????????|    NULL|2022-08-18|Katie Wilson|  China|        CN214209201U|   zh-cn|NULL|\n|????????????????|    NULL|2022-04-12|Sarah Miller|  Japan|JP2022058425A  JP...|      ja|2022|\n+----------------+--------+----------+------------+-------+--------------------+--------+----+\n\n"
     ]
    }
   ],
   "source": [
    "# Check for NULL values in the 'date' column\n",
    "df_g.filter(col('date').isNull()).show()\n",
    "\n",
    "# Check for NULL values in other columns (example for 'abstract')\n",
    "df_g.filter(col('abstract').isNull()).show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "96fa95fb-3ece-4175-abf0-bb4b665d05fc",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+--------+----+--------+-------+-----------+--------+----+\n|title|abstract|date|inventor|country|document_id|language|year|\n+-----+--------+----+--------+-------+-----------+--------+----+\n+-----+--------+----+--------+-------+-----------+--------+----+\n\n"
     ]
    }
   ],
   "source": [
    "# Check for NULL values in other columns (example for 'abstract')\n",
    "df_gold.filter(col('inventor').isNull()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "d8546114-34c0-4ff9-a803-08c7efc950b7",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|year|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|2010|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|2003|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|1999|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|2005|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|2003|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|1989|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|1993|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|2022|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|2009|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|2021|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|2021|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|1997|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|2008|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|2019|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|2022|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|2022|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|2000|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|2023|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|2015|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|2015|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_g.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "eeed5548-aac2-4bb2-a297-3c9ff2ab93d0",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|year|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|2010|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|2003|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|1999|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|2005|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|2003|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|1989|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|1993|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|2022|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|2009|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|2021|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|2021|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|1997|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|2008|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|2019|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|2022|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|2022|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|2000|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|2023|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|2015|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|2015|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import regexp_replace\n",
    "\n",
    "# Remove punctuation from the 'inventor' column\n",
    "df_gold = df_g.withColumn('inventor', regexp_replace(col('inventor'), '[^\\w\\s]', ''))\n",
    "\n",
    "# Show the updated DataFrame\n",
    "df_gold.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c57b4a01-5ec8-4fea-9175-2751731ff3ef",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|year|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|2010|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|2003|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|1999|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|2005|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|2003|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|1989|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|1993|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|2022|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|2009|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|2021|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|2021|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|1997|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|2008|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|2019|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|2022|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|2022|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|2000|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|2023|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|2015|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|2015|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df_gold.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b1ad8af5-8bbc-429e-a2e5-2af29194b265",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {
        "dataframeName": null
       },
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "DROP TABLE IF EXISTS GOLD_table;  -- Correcting the table name\n",
    "CREATE TABLE GOLD_table(\n",
    "    title STRING,\n",
    "    abstract STRING,\n",
    "    date DATE,\n",
    "    inventor STRING,\n",
    "    country STRING,\n",
    "    document_id STRING,\n",
    "    language STRING,\n",
    "    year STRING\n",
    ")\n",
    "USING DELTA\n",
    "LOCATION '/mnt/GoldTable2'; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "36978e46-703d-490c-a907-df8f08f13ee6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- title: string (nullable = true)\n |-- abstract: string (nullable = true)\n |-- date: date (nullable = true)\n |-- inventor: string (nullable = true)\n |-- country: string (nullable = true)\n |-- document_id: string (nullable = true)\n |-- language: string (nullable = true)\n |-- year: integer (nullable = true)\n\nroot\n |-- title: string (nullable = true)\n |-- abstract: string (nullable = true)\n |-- date: date (nullable = true)\n |-- inventor: string (nullable = true)\n |-- country: string (nullable = true)\n |-- document_id: string (nullable = true)\n |-- language: string (nullable = true)\n |-- year: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import col\n",
    "\n",
    "# Verify the schema of df_gold\n",
    "df_gold.printSchema()\n",
    "\n",
    "# Cast the year column to STRING if it is not already\n",
    "df_gold = df_gold.withColumn(\"year\", col(\"year\").cast(\"STRING\"))\n",
    "\n",
    "# Verify the schema after casting\n",
    "df_gold.printSchema()\n",
    "\n",
    "# Append the DataFrame to the Delta table with mergeSchema option\n",
    "df_gold.write.format(\"delta\").mode(\"append\").option(\"mergeSchema\", \"true\").save(\"/mnt/GoldTable2\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "8f315051-839c-4fd4-8bf3-18c4403972e9",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "Top Words:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e82e80c0-8d2d-4f8c-a501-47a95b072333",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "gold_df = spark.read.format(\"delta\").load(\"/mnt/GoldTable2\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "30700c5d-9a50-4753-ac47-d4227e777ddf",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|               title|            abstract|      date|            inventor|        country|   document_id|language|year|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\n|['auxiliaire', 'p...|['linvention', 'c...|2010-12-23|goracon engineeri...|unknown country|wo2010145768a1|      fr|2010|\n|['syncretic', 'pr...|['antivirus', 'an...|2003-07-23|dongkanglong viru...|          China|    cn1431018a|      en|2003|\n|['vecteur', 'de',...|['...', 'sutilise...|1999-02-25|hepavec ag fuer g...|unknown country|   wo9909193a1|      fr|1999|\n|['lie7', 'recombi...|['...', 'hpv16', ...|2005-11-16|virus diseases pr...|          China|    cn1696152a|      en|2005|\n|['virus', 'de', '...|['linvention', 'c...|2003-11-20|gsf forschungszen...|unknown country|  wo03095656a1|      fr|2003|\n|['vaccinia', 'vir...|['recombinant', '...|1989-07-19|hoffmann la roche ch|unknown country|ep0324350a1 b1|      en|1989|\n|['polypeptides', ...|['il', 'est', 'dc...|1993-10-13|boehringer mannhe...|unknown country|ep0564532a1 b1|      fr|1993|\n|['recombinant', '...|['...the', 'inven...|2022-11-25|shanghai veterina...|          China|cn115386556a b|      en|2022|\n|['humanized', 'ge...|['humanized', 'ge...|2009-06-10|inst of virus pre...|          China|cn100497391c a|      en|2009|\n|['vaccin', 'virus...|['linvention', 'c...|2021-11-18|joint biosciences...|         Canada|   ca3178631a1|      fr|2021|\n|['virus', 'oncoly...|['la', 'prsente',...|2021-11-18|joint biosciences...|         Canada|   ca3178632a1|      fr|2021|\n|['gene', 'enginee...|['present', 'inve...|1997-04-16|       univ wuhan cn|          China|    cn1147555a|      en|1997|\n|['structural', 'm...|['...the', 'proce...|2008-02-27|      univ suzhou cn|          China|cn100371449c a|      en|2008|\n|['pseudorabies', ...|['...the', 'inven...|2019-03-08|jiangsu acad agri...|          China|cn109439634a b|      en|2019|\n|['hcov-229e', 'vi...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774337a b|      en|2022|\n|['mers-cov', 'vir...|['...the', 'inven...|2022-07-22|   univ northeastern|          China|cn114774425a b|      en|2022|\n|['gene', 'enginee...|['present', 'inve...|2000-01-26|shanghai inst of ...|          China|    cn1242428a|      en|2000|\n|['preparation', '...|['...the', 'inven...|2023-04-14|       univ yangzhou|          China|  cn115957315a|      en|2023|\n|['recombinant', '...|['...the', 'inven...|2015-04-08|wuhan engineering...|          China|  cn104498416a|      en|2015|\n|['duck', 'tembusu...|['...the', 'objec...|2015-05-13|univ qingdao agri...|          China|cn104610455a b|      en|2015|\n+--------------------+--------------------+----------+--------------------+---------------+--------------+--------+----+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "gold_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "abe77abc-6b1a-44b0-be79-d203a53a7d13",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "# Convert Spark DataFrame to Pandas DataFrame\n",
    "df_pandas = gold_df.toPandas()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6392baf4-2527-4ec2-b328-b7426212e761",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>date</th>\n",
       "      <th>inventor</th>\n",
       "      <th>country</th>\n",
       "      <th>document_id</th>\n",
       "      <th>language</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>['auxiliaire', 'permettant', 'la', 'monte', 'd...</td>\n",
       "      <td>['linvention', 'concerne', 'un', 'auxiliaire',...</td>\n",
       "      <td>2010-12-23</td>\n",
       "      <td>goracon engineering gmbh</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo2010145768a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>['syncretic', 'protein', 'product', 'human', '...</td>\n",
       "      <td>['antivirus', 'antalgic', 'methionine', 'enkep...</td>\n",
       "      <td>2003-07-23</td>\n",
       "      <td>dongkanglong virus biotechnolo</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1431018a</td>\n",
       "      <td>en</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>['vecteur', 'de', 'baculovirus', 'modifie', 'p...</td>\n",
       "      <td>['...', 'sutiliser', 'en', 'gnothrapie', 'chez...</td>\n",
       "      <td>1999-02-25</td>\n",
       "      <td>hepavec ag fuer gentherapie de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo9909193a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>1999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>['lie7', 'recombined', 'protein', '16', 'type'...</td>\n",
       "      <td>['...', 'hpv16', 'chitoprotein', 'gene', 'l1',...</td>\n",
       "      <td>2005-11-16</td>\n",
       "      <td>virus diseases prevention  co</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1696152a</td>\n",
       "      <td>en</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>['virus', 'de', 'variole', 'aviaire', 'recombi...</td>\n",
       "      <td>['linvention', 'concerne', 'un', 'virus', 'de'...</td>\n",
       "      <td>2003-11-20</td>\n",
       "      <td>gsf forschungszentrum umwelt de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo03095656a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15400</th>\n",
       "      <td>materials methods treatment human genetic dise...</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2018-07-19</td>\n",
       "      <td>Matthew Hebden Porteus</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-2018200387-A1</td>\n",
       "      <td>en</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15401</th>\n",
       "      <td>sweet pepper hybrid</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2011-09-06</td>\n",
       "      <td>William McCarthy</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8013222-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15402</th>\n",
       "      <td>immunoconjugates methods producing</td>\n",
       "      <td>description translated korean immunoconjugates...</td>\n",
       "      <td>2019-03-26</td>\n",
       "      <td>John Taylor</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>KR-101962476-B1</td>\n",
       "      <td>en</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15403</th>\n",
       "      <td>anticd antibody use treatment prevention cance...</td>\n",
       "      <td>description cross references related applicati...</td>\n",
       "      <td>2012-12-25</td>\n",
       "      <td>CheLeung Law Julie McEarchern Alan F Wahl</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8337838-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15404</th>\n",
       "      <td>repair antibodies cdr</td>\n",
       "      <td>description translated spanish antibodies repa...</td>\n",
       "      <td>2010-04-26</td>\n",
       "      <td>Mark S Dennis</td>\n",
       "      <td>Spain</td>\n",
       "      <td>ES-2337473-T3</td>\n",
       "      <td>en</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15405 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   title  ...  year\n",
       "0      ['auxiliaire', 'permettant', 'la', 'monte', 'd...  ...  2010\n",
       "1      ['syncretic', 'protein', 'product', 'human', '...  ...  2003\n",
       "2      ['vecteur', 'de', 'baculovirus', 'modifie', 'p...  ...  1999\n",
       "3      ['lie7', 'recombined', 'protein', '16', 'type'...  ...  2005\n",
       "4      ['virus', 'de', 'variole', 'aviaire', 'recombi...  ...  2003\n",
       "...                                                  ...  ...   ...\n",
       "15400  materials methods treatment human genetic dise...  ...  2018\n",
       "15401                                sweet pepper hybrid  ...  2011\n",
       "15402                 immunoconjugates methods producing  ...  2019\n",
       "15403  anticd antibody use treatment prevention cance...  ...  2012\n",
       "15404                              repair antibodies cdr  ...  2010\n",
       "\n",
       "[15405 rows x 8 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c21d6c6b-7ecb-45da-8a0f-a13a77994b3a",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "from sklearn.feature_extraction.text import ENGLISH_STOP_WORDS\n",
    "\n",
    "# Function to join list of strings safely\n",
    "def join_strings(lst):\n",
    "    return ' '.join(lst) if isinstance(lst, list) else ''\n",
    "\n",
    "# Combine title and abstract columns\n",
    "df_pandas['text'] = df_pandas['title'].apply(join_strings) + ' ' + df_pandas['abstract'].apply(join_strings)\n",
    "\n",
    "# Convert to lowercase\n",
    "df_pandas['text'] = df_pandas['text'].str.lower()\n",
    "\n",
    "# Function to preprocess text\n",
    "def preprocess_text(text):\n",
    "    # Remove punctuation and non-alphabetic characters\n",
    "    text = re.sub(r'[^a-zA-Z\\s]', '', text)\n",
    "    # Tokenize\n",
    "    words = text.split()\n",
    "    # Remove stop words\n",
    "    words = [word for word in words if word not in ENGLISH_STOP_WORDS]\n",
    "    return words\n",
    "\n",
    "# Apply preprocessing\n",
    "df_pandas['words'] = df_pandas['text'].apply(preprocess_text)\n",
    "\n",
    "# Explode the words column into individual words\n",
    "df_words = df_pandas.explode('words').dropna(subset=['words'])\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "593fb6ce-f488-48da-bdba-66cef7fe270f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>date</th>\n",
       "      <th>inventor</th>\n",
       "      <th>country</th>\n",
       "      <th>document_id</th>\n",
       "      <th>language</th>\n",
       "      <th>year</th>\n",
       "      <th>text</th>\n",
       "      <th>words</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>['auxiliaire', 'permettant', 'la', 'monte', 'd...</td>\n",
       "      <td>['linvention', 'concerne', 'un', 'auxiliaire',...</td>\n",
       "      <td>2010-12-23</td>\n",
       "      <td>goracon engineering gmbh</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo2010145768a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2010</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>['syncretic', 'protein', 'product', 'human', '...</td>\n",
       "      <td>['antivirus', 'antalgic', 'methionine', 'enkep...</td>\n",
       "      <td>2003-07-23</td>\n",
       "      <td>dongkanglong virus biotechnolo</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1431018a</td>\n",
       "      <td>en</td>\n",
       "      <td>2003</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>['vecteur', 'de', 'baculovirus', 'modifie', 'p...</td>\n",
       "      <td>['...', 'sutiliser', 'en', 'gnothrapie', 'chez...</td>\n",
       "      <td>1999-02-25</td>\n",
       "      <td>hepavec ag fuer gentherapie de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo9909193a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>1999</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>['lie7', 'recombined', 'protein', '16', 'type'...</td>\n",
       "      <td>['...', 'hpv16', 'chitoprotein', 'gene', 'l1',...</td>\n",
       "      <td>2005-11-16</td>\n",
       "      <td>virus diseases prevention  co</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1696152a</td>\n",
       "      <td>en</td>\n",
       "      <td>2005</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>['virus', 'de', 'variole', 'aviaire', 'recombi...</td>\n",
       "      <td>['linvention', 'concerne', 'un', 'virus', 'de'...</td>\n",
       "      <td>2003-11-20</td>\n",
       "      <td>gsf forschungszentrum umwelt de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo03095656a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2003</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15400</th>\n",
       "      <td>materials methods treatment human genetic dise...</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2018-07-19</td>\n",
       "      <td>Matthew Hebden Porteus</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-2018200387-A1</td>\n",
       "      <td>en</td>\n",
       "      <td>2018</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15401</th>\n",
       "      <td>sweet pepper hybrid</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2011-09-06</td>\n",
       "      <td>William McCarthy</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8013222-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15402</th>\n",
       "      <td>immunoconjugates methods producing</td>\n",
       "      <td>description translated korean immunoconjugates...</td>\n",
       "      <td>2019-03-26</td>\n",
       "      <td>John Taylor</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>KR-101962476-B1</td>\n",
       "      <td>en</td>\n",
       "      <td>2019</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15403</th>\n",
       "      <td>anticd antibody use treatment prevention cance...</td>\n",
       "      <td>description cross references related applicati...</td>\n",
       "      <td>2012-12-25</td>\n",
       "      <td>CheLeung Law Julie McEarchern Alan F Wahl</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8337838-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2012</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15404</th>\n",
       "      <td>repair antibodies cdr</td>\n",
       "      <td>description translated spanish antibodies repa...</td>\n",
       "      <td>2010-04-26</td>\n",
       "      <td>Mark S Dennis</td>\n",
       "      <td>Spain</td>\n",
       "      <td>ES-2337473-T3</td>\n",
       "      <td>en</td>\n",
       "      <td>2010</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15405 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   title  ... words\n",
       "0      ['auxiliaire', 'permettant', 'la', 'monte', 'd...  ...    []\n",
       "1      ['syncretic', 'protein', 'product', 'human', '...  ...    []\n",
       "2      ['vecteur', 'de', 'baculovirus', 'modifie', 'p...  ...    []\n",
       "3      ['lie7', 'recombined', 'protein', '16', 'type'...  ...    []\n",
       "4      ['virus', 'de', 'variole', 'aviaire', 'recombi...  ...    []\n",
       "...                                                  ...  ...   ...\n",
       "15400  materials methods treatment human genetic dise...  ...    []\n",
       "15401                                sweet pepper hybrid  ...    []\n",
       "15402                 immunoconjugates methods producing  ...    []\n",
       "15403  anticd antibody use treatment prevention cance...  ...    []\n",
       "15404                              repair antibodies cdr  ...    []\n",
       "\n",
       "[15405 rows x 10 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "297250a7-7174-492b-9090-840741c4e3a9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "def clean_text(text):\n",
    "    if not isinstance(text, str):\n",
    "        return ''\n",
    "    # Remove square brackets\n",
    "    text = re.sub(r'\\[|\\]', '', text)\n",
    "    # Remove single quotes\n",
    "    text = re.sub(r\"'\", '', text)\n",
    "    # Remove any other punctuation and special characters except underscores and hyphens\n",
    "    text = re.sub(r'[^a-zA-Z_\\-\\s]', '', text)\n",
    "    return text\n",
    "\n",
    "\n",
    "# Apply cleaning function to title and abstract columns\n",
    "df_pandas['title'] = df_pandas['title'].apply(clean_text)\n",
    "df_pandas['abstract'] = df_pandas['abstract'].apply(clean_text)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "4e2f6d5e-fd6a-4a1f-99b4-d918fcf58ba9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>date</th>\n",
       "      <th>inventor</th>\n",
       "      <th>country</th>\n",
       "      <th>document_id</th>\n",
       "      <th>language</th>\n",
       "      <th>year</th>\n",
       "      <th>text</th>\n",
       "      <th>words</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>auxiliaire permettant la monte dune personne o...</td>\n",
       "      <td>linvention concerne un auxiliaire permettant l...</td>\n",
       "      <td>2010-12-23</td>\n",
       "      <td>goracon engineering gmbh</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo2010145768a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2010</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>syncretic protein product human methionine enk...</td>\n",
       "      <td>antivirus antalgic methionine enkephalin inter...</td>\n",
       "      <td>2003-07-23</td>\n",
       "      <td>dongkanglong virus biotechnolo</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1431018a</td>\n",
       "      <td>en</td>\n",
       "      <td>2003</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>vecteur de baculovirus modifie par coque de pr...</td>\n",
       "      <td>sutiliser en gnothrapie chez lhomme le vecteu...</td>\n",
       "      <td>1999-02-25</td>\n",
       "      <td>hepavec ag fuer gentherapie de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo9909193a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>1999</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lie recombined protein  type human papilomavir...</td>\n",
       "      <td>hpv chitoprotein gene l hpv gene engineering ...</td>\n",
       "      <td>2005-11-16</td>\n",
       "      <td>virus diseases prevention  co</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1696152a</td>\n",
       "      <td>en</td>\n",
       "      <td>2005</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>virus de variole aviaire recombinant</td>\n",
       "      <td>linvention concerne un virus de variole aviair...</td>\n",
       "      <td>2003-11-20</td>\n",
       "      <td>gsf forschungszentrum umwelt de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo03095656a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2003</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15400</th>\n",
       "      <td>materials methods treatment human genetic dise...</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2018-07-19</td>\n",
       "      <td>Matthew Hebden Porteus</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-2018200387-A1</td>\n",
       "      <td>en</td>\n",
       "      <td>2018</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15401</th>\n",
       "      <td>sweet pepper hybrid</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2011-09-06</td>\n",
       "      <td>William McCarthy</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8013222-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2011</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15402</th>\n",
       "      <td>immunoconjugates methods producing</td>\n",
       "      <td>description translated korean immunoconjugates...</td>\n",
       "      <td>2019-03-26</td>\n",
       "      <td>John Taylor</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>KR-101962476-B1</td>\n",
       "      <td>en</td>\n",
       "      <td>2019</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15403</th>\n",
       "      <td>anticd antibody use treatment prevention cance...</td>\n",
       "      <td>description cross references related applicati...</td>\n",
       "      <td>2012-12-25</td>\n",
       "      <td>CheLeung Law Julie McEarchern Alan F Wahl</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8337838-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2012</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15404</th>\n",
       "      <td>repair antibodies cdr</td>\n",
       "      <td>description translated spanish antibodies repa...</td>\n",
       "      <td>2010-04-26</td>\n",
       "      <td>Mark S Dennis</td>\n",
       "      <td>Spain</td>\n",
       "      <td>ES-2337473-T3</td>\n",
       "      <td>en</td>\n",
       "      <td>2010</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15405 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   title  ... words\n",
       "0      auxiliaire permettant la monte dune personne o...  ...    []\n",
       "1      syncretic protein product human methionine enk...  ...    []\n",
       "2      vecteur de baculovirus modifie par coque de pr...  ...    []\n",
       "3      lie recombined protein  type human papilomavir...  ...    []\n",
       "4                   virus de variole aviaire recombinant  ...    []\n",
       "...                                                  ...  ...   ...\n",
       "15400  materials methods treatment human genetic dise...  ...    []\n",
       "15401                                sweet pepper hybrid  ...    []\n",
       "15402                 immunoconjugates methods producing  ...    []\n",
       "15403  anticd antibody use treatment prevention cance...  ...    []\n",
       "15404                              repair antibodies cdr  ...    []\n",
       "\n",
       "[15405 rows x 10 columns]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "459cd6aa-abea-49d9-85ab-bf308d90f056",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Combine title and abstract columns for analysis\n",
    "df_pandas['text'] = df_pandas['title'] + ' ' + df_pandas['abstract']\n",
    "\n",
    "# Convert to lowercase\n",
    "df_pandas['text'] = df_pandas['text'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "71b683a4-b0dc-4c7e-a5a3-b121c4f4089b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>date</th>\n",
       "      <th>inventor</th>\n",
       "      <th>country</th>\n",
       "      <th>document_id</th>\n",
       "      <th>language</th>\n",
       "      <th>year</th>\n",
       "      <th>text</th>\n",
       "      <th>words</th>\n",
       "      <th>processed_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>auxiliaire permettant la monte dune personne o...</td>\n",
       "      <td>linvention concerne un auxiliaire permettant l...</td>\n",
       "      <td>2010-12-23</td>\n",
       "      <td>goracon engineering gmbh</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo2010145768a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2010</td>\n",
       "      <td>auxiliaire permettant la monte dune personne o...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>syncretic protein product human methionine enk...</td>\n",
       "      <td>antivirus antalgic methionine enkephalin inter...</td>\n",
       "      <td>2003-07-23</td>\n",
       "      <td>dongkanglong virus biotechnolo</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1431018a</td>\n",
       "      <td>en</td>\n",
       "      <td>2003</td>\n",
       "      <td>syncretic protein product human methionine enk...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>vecteur de baculovirus modifie par coque de pr...</td>\n",
       "      <td>sutiliser en gnothrapie chez lhomme le vecteu...</td>\n",
       "      <td>1999-02-25</td>\n",
       "      <td>hepavec ag fuer gentherapie de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo9909193a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>1999</td>\n",
       "      <td>vecteur de baculovirus modifie par coque de pr...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lie recombined protein  type human papilomavir...</td>\n",
       "      <td>hpv chitoprotein gene l hpv gene engineering ...</td>\n",
       "      <td>2005-11-16</td>\n",
       "      <td>virus diseases prevention  co</td>\n",
       "      <td>China</td>\n",
       "      <td>cn1696152a</td>\n",
       "      <td>en</td>\n",
       "      <td>2005</td>\n",
       "      <td>lie recombined protein  type human papilomavir...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>virus de variole aviaire recombinant</td>\n",
       "      <td>linvention concerne un virus de variole aviair...</td>\n",
       "      <td>2003-11-20</td>\n",
       "      <td>gsf forschungszentrum umwelt de</td>\n",
       "      <td>unknown country</td>\n",
       "      <td>wo03095656a1</td>\n",
       "      <td>fr</td>\n",
       "      <td>2003</td>\n",
       "      <td>virus de variole aviaire recombinant linventio...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15400</th>\n",
       "      <td>materials methods treatment human genetic dise...</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2018-07-19</td>\n",
       "      <td>Matthew Hebden Porteus</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-2018200387-A1</td>\n",
       "      <td>en</td>\n",
       "      <td>2018</td>\n",
       "      <td>materials methods treatment human genetic dise...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15401</th>\n",
       "      <td>sweet pepper hybrid</td>\n",
       "      <td>description crossreference related application...</td>\n",
       "      <td>2011-09-06</td>\n",
       "      <td>William McCarthy</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8013222-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2011</td>\n",
       "      <td>sweet pepper hybrid description crossreference...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15402</th>\n",
       "      <td>immunoconjugates methods producing</td>\n",
       "      <td>description translated korean immunoconjugates...</td>\n",
       "      <td>2019-03-26</td>\n",
       "      <td>John Taylor</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>KR-101962476-B1</td>\n",
       "      <td>en</td>\n",
       "      <td>2019</td>\n",
       "      <td>immunoconjugates methods producing description...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15403</th>\n",
       "      <td>anticd antibody use treatment prevention cance...</td>\n",
       "      <td>description cross references related applicati...</td>\n",
       "      <td>2012-12-25</td>\n",
       "      <td>CheLeung Law Julie McEarchern Alan F Wahl</td>\n",
       "      <td>United States</td>\n",
       "      <td>US-8337838-B2</td>\n",
       "      <td>en</td>\n",
       "      <td>2012</td>\n",
       "      <td>anticd antibody use treatment prevention cance...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15404</th>\n",
       "      <td>repair antibodies cdr</td>\n",
       "      <td>description translated spanish antibodies repa...</td>\n",
       "      <td>2010-04-26</td>\n",
       "      <td>Mark S Dennis</td>\n",
       "      <td>Spain</td>\n",
       "      <td>ES-2337473-T3</td>\n",
       "      <td>en</td>\n",
       "      <td>2010</td>\n",
       "      <td>repair antibodies cdr description translated s...</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15405 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   title  ... processed_text\n",
       "0      auxiliaire permettant la monte dune personne o...  ...               \n",
       "1      syncretic protein product human methionine enk...  ...               \n",
       "2      vecteur de baculovirus modifie par coque de pr...  ...               \n",
       "3      lie recombined protein  type human papilomavir...  ...               \n",
       "4                   virus de variole aviaire recombinant  ...               \n",
       "...                                                  ...  ...            ...\n",
       "15400  materials methods treatment human genetic dise...  ...               \n",
       "15401                                sweet pepper hybrid  ...               \n",
       "15402                 immunoconjugates methods producing  ...               \n",
       "15403  anticd antibody use treatment prevention cance...  ...               \n",
       "15404                              repair antibodies cdr  ...               \n",
       "\n",
       "[15405 rows x 11 columns]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "8e9288ae-4412-4019-97cc-773185581dd9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Explode the words column into individual words\n",
    "df_words = df_pandas.explode('text').dropna(subset=['text'])\n",
    "\n",
    "# Count word occurrences\n",
    "word_counts = df_words['text'].value_counts().reset_index()\n",
    "word_counts.columns = ['text', 'count']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "24d5a2bd-6e0c-4e45-a3db-747fde325da7",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0        auxiliaire permettant la monte dune personne o...\n",
       "1        syncretic protein product human methionine enk...\n",
       "2        vecteur de baculovirus modifie par coque de pr...\n",
       "3        lie recombined protein  type human papilomavir...\n",
       "4        virus de variole aviaire recombinant linventio...\n",
       "                               ...                        \n",
       "15400    materials methods treatment human genetic dise...\n",
       "15401    sweet pepper hybrid description crossreference...\n",
       "15402    immunoconjugates methods producing description...\n",
       "15403    anticd antibody use treatment prevention cance...\n",
       "15404    repair antibodies cdr description translated s...\n",
       "Name: text, Length: 15405, dtype: object"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pandas['text']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ea658f4b-1dbc-42cd-83ca-be7caae9f890",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top 20 words:\n             word   count\n423          acid  218019\n188      sequence  216811\n632            id  191627\n631           seq  188135\n693           one  187253\n380          cell  171104\n169     invention  167848\n301         cells  160100\n422         amino  145960\n1365          may  145415\n236      antibody  130344\n65        protein  124362\n1612  embodiments  123336\n1070        least  121412\n281       present  107366\n207             c  103244\n92           used   99915\n6901      example   96938\n4375       herein   91498\n63            fig   90743\n"
     ]
    }
   ],
   "source": [
    "from collections import Counter\n",
    "from sklearn.feature_extraction.text import ENGLISH_STOP_WORDS\n",
    "\n",
    "# Tokenize the text and remove stop words\n",
    "df_pandas['words'] = df_pandas['text'].apply(lambda x: [word for word in x.split()])\n",
    "\n",
    "# Flatten the list of words and count word occurrences\n",
    "all_words = [word for words_list in df_pandas['words'] for word in words_list]\n",
    "word_counts = Counter(all_words)\n",
    "\n",
    "# Convert to a DataFrame for better visualization\n",
    "word_counts_df = pd.DataFrame(word_counts.items(), columns=['word', 'count']).sort_values(by='count', ascending=False)\n",
    "\n",
    "# Display the top 20 words\n",
    "top_words = word_counts_df.head(20)\n",
    "print(\"Top 20 words:\")\n",
    "print(top_words)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "c88272f2-c295-46f5-a353-7045cbe54a81",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "cab96374-f81c-43bb-896f-d6e618519fbc",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "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>word</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>acid</td>\n",
       "      <td>218019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>sequence</td>\n",
       "      <td>216811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>id</td>\n",
       "      <td>191627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>seq</td>\n",
       "      <td>188135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>one</td>\n",
       "      <td>187253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226979</th>\n",
       "      <td>proxied</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226982</th>\n",
       "      <td>certifies</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226985</th>\n",
       "      <td>fictitiously</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226986</th>\n",
       "      <td>uninspected</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360039</th>\n",
       "      <td>anatrozole</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>360040 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                word   count\n",
       "423             acid  218019\n",
       "188         sequence  216811\n",
       "632               id  191627\n",
       "631              seq  188135\n",
       "693              one  187253\n",
       "...              ...     ...\n",
       "226979       proxied       1\n",
       "226982     certifies       1\n",
       "226985  fictitiously       1\n",
       "226986   uninspected       1\n",
       "360039    anatrozole       1\n",
       "\n",
       "[360040 rows x 2 columns]"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "word_counts_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e59430fb-1795-4973-ba5d-578010db3975",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "word_count=spark.createDataFrame(word_counts_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "32f2b6eb-47de-41fe-939c-88a0e0c09d06",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<bound method DataFrame.printSchema of DataFrame[word: string, count: bigint]>"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "word_count.printSchema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "3bf782e3-d359-4c0d-9451-cfeaf2e58fea",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr></tr></thead><tbody></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {
        "dataframeName": null
       },
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql\n",
    "DROP TABLE IF EXISTS Word_table;  -- Correcting the table name\n",
    "CREATE TABLE Word_table(\n",
    "    word STRING,\n",
    "   count BIGINT\n",
    ")\n",
    "USING DELTA\n",
    "LOCATION '/mnt/WordTable2'; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ec76146b-2f46-4a3e-b428-42bd82224125",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Append the DataFrame to the Delta table with mergeSchema option\n",
    "word_count.write.format(\"delta\").mode(\"append\").option(\"mergeSchema\", \"true\").save(\"/mnt/WordTable2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "cfeb8696-6883-42bb-a5e6-10af31734866",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1344850221476127,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "ETL _PROCESS",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}