simplER_eval.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "39015936-d8e7-45e0-8d5f-f1ac99c2fece",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Environment note: this runs on DBR standard runtime 11.3\n",
    "\n",
    "# ---------\n",
    "# Imports\n",
    "# ---------\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "# -------\n",
    "# IMDB\n",
    "# -------\n",
    "\n",
    "# Flair accuracy\n",
    "spark.sql('''\n",
    "SELECT rank_first_match, COUNT(*) as n_in_rank\n",
    "FROM user_nsulliv3.simpler_imdb_eval_maj_ranks\n",
    "GROUP BY 1\n",
    ";\n",
    "''')\n",
    "\n",
    "# 555/572 = 97% accuracy\n",
    "\n",
    "# GPT accuracy\n",
    "imdb_gpt = pd.read_csv('https://raw.githubusercontent.com/sullivannicole/simplER/main/data/imdb_after_gpt.csv')\n",
    "\n",
    "spark.createDataFrame(imdb_gpt).createOrReplaceTempView('imdb_gpt')\n",
    "\n",
    "imdb_gpt = spark.sql('''\n",
    "WITH gpt_entities AS (SELECT *, LOWER(RTRIM(REGEXP_REPLACE(movie, '[^0-9a-zA-Z ]', ''))) AS movie_no_punc, LOWER(RTRIM(REGEXP_REPLACE(gpt_movie_title, '[^0-9a-zA-Z ]', ''))) AS gpt_movie_no_punc\n",
    "FROM imdb_gpt\n",
    "WHERE gpt_movie_title IS NOT NULL),\n",
    "\n",
    "classified AS (SELECT COUNT(*) AS n_obs\n",
    "              FROM gpt_entities)\n",
    "\n",
    "SELECT COUNT(a.user_review) AS n_matches, b.n_obs\n",
    "FROM gpt_entities a\n",
    "JOIN classified b\n",
    "WHERE a.movie_no_punc = a.gpt_movie_no_punc\n",
    "OR CONTAINS(a.movie_no_punc, a.gpt_movie_no_punc)\n",
    "OR CONTAINS(a.gpt_movie_no_punc, a.movie_no_punc)\n",
    "GROUP BY 2;\n",
    "\n",
    "''')\n",
    "\n",
    "# These preds aren't captured using absolute rules above, but may still be the correct prediction technically (just missing a token like \"the\", \"a\" or \"and\"), so manually check - quicker than building out an eval pipeline at this point\n",
    "imdb_gpt_check = spark.sql('''\n",
    "WITH gpt_entities AS (SELECT *, LOWER(RTRIM(REGEXP_REPLACE(movie, '[^0-9a-zA-Z ]', ''))) AS movie_no_punc, LOWER(RTRIM(REGEXP_REPLACE(gpt_movie_title, '[^0-9a-zA-Z ]', ''))) AS gpt_movie_no_punc\n",
    "FROM imdb_gpt\n",
    "WHERE gpt_movie_title IS NOT NULL),\n",
    "\n",
    "correct_class AS (SELECT *\n",
    "FROM gpt_entities a\n",
    "WHERE a.movie_no_punc = a.gpt_movie_no_punc\n",
    "OR CONTAINS(a.movie_no_punc, a.gpt_movie_no_punc)\n",
    "OR CONTAINS(a.gpt_movie_no_punc, a.movie_no_punc))\n",
    "\n",
    "SELECT *\n",
    "FROM gpt_entities\n",
    "WHERE `Unnamed: 0` NOT IN (SELECT DISTINCT `Unnamed: 0` FROM correct_class)\n",
    ";\n",
    "\n",
    "''')\n",
    "\n",
    "# Manually identify additional correct (n = 72): 31, 32, 35, 43, 69, 97, 109, 113, 135, 163, 166, 167, 177, 197, 235, 249, 261, 287, 315, 317, 325, 326\n",
    "# 329, 330, 334, 362, 374, 378, 390, 401, 402, 416, 436, 437, 477, 486, 487, 491, 492, 502, 513, 638, 665, 676, 817, 825, 850, 856, 882, 895,\n",
    "# 896, 918, 919, 939, 968, 984, 1023, 1028, 1095, 1104, 1113, 1119, 1120, 1125, 1176, 1240, 1247, 1253, 1316, 1317, 1357, 1358\n",
    "imdb_gpt_check.display()\n",
    "\n",
    "# imdb_gpt.display() # 808+72 / 911 = 96.5% accuracy\n",
    "\n",
    "# Altogether: 0.9676331759946055% accuracy\n",
    "\n",
    "# --------------\n",
    "# COVID - user\n",
    "# --------------\n",
    "\n",
    "covid_usr_w_ids = pd.read_csv('https://raw.githubusercontent.com/sullivannicole/simplER/main/data/covid_raw_user_sentences_w_ids.csv')\n",
    "covid_usr_df = covid_usr_w_ids.drop(columns = ['Unnamed: 0'])\n",
    "spark.createDataFrame(covid_usr_df).write.mode('overwrite').saveAsTable('user_nsulliv3.simpler_covid_usr_ids')\n",
    "\n",
    "# Run in SQL editor; UNPIVOT doesn't work in notebook\n",
    "spark.sql('''\n",
    "create or replace table user_nsulliv3.simpler_covid_usr_long AS\n",
    "select id, sentence, country\n",
    "from user_nsulliv3.simpler_covid_usr_ids\n",
    "unpivot (country for col in (country_1, country_2))\n",
    "''')\n",
    "\n",
    "# Flair accuracy\n",
    "covid_flair = pd.read_csv('https://raw.githubusercontent.com/sullivannicole/simplER/main/data/covid_user_flair.csv')\n",
    "\n",
    "spark.createDataFrame(covid_flair).createOrReplaceTempView('covid_flair')\n",
    "\n",
    "covid_flair_correct = spark.sql('''\n",
    "            SELECT a.id, a.text AS country_pred \n",
    "            FROM covid_flair a \n",
    "            LEFT JOIN user_nsulliv3.simpler_covid_usr_long b \n",
    "            ON a.id = b.id \n",
    "            AND a.text = b.country\n",
    "            WHERE entity_detected = 'GPE';''')\n",
    "\n",
    "# 1 sentence got missed in my original classification but Flair classifies correctly when checked manually: 42\n",
    "covid_flair_check = spark.sql('''\n",
    "WITH flair_preds AS (SELECT a.id, a.text AS country_pred \n",
    "            FROM covid_flair a \n",
    "            LEFT JOIN user_nsulliv3.simpler_covid_usr_long b \n",
    "            ON a.id = b.id \n",
    "            AND a.text = b.country\n",
    "            WHERE entity_detected = 'GPE')\n",
    "\n",
    "SELECT a.*, b.*\n",
    "FROM user_nsulliv3.simpler_covid_usr_long a \n",
    "LEFT JOIN covid_flair b \n",
    "ON a.id = b.id\n",
    "WHERE a.id NOT IN (SELECT DISTINCT id FROM flair_preds)\n",
    "''')\n",
    "\n",
    "# (57+1) / 64 = 91% accuracy\n",
    "\n",
    "# GPT accuracy\n",
    "covid_gpt_raw = pd.read_csv('https://raw.githubusercontent.com/sullivannicole/simplER/main/data/covid_raw_user_sentences_w_ids_after_gpt.csv')\n",
    "spark.createDataFrame(covid_gpt_raw).createOrReplaceTempView('covid_gpt')\n",
    "\n",
    "covid_gpt_correct = spark.sql('''\n",
    "SELECT COUNT(*) AS n_matches\n",
    "FROM covid_gpt\n",
    "WHERE count_type1 = LOWER(count_type_gpt)\n",
    "''')\n",
    "\n",
    "# Manually check \"incorrect\" predictions\n",
    "# +2 for incorrect labels on ids = 40, 47, correctly predicted by GPT\n",
    "spark.sql('''\n",
    "SELECT *\n",
    "FROM covid_gpt\n",
    "WHERE count_type_gpt IS NOT NULL\n",
    "AND LOWER(count_type_gpt) != 'unknown'\n",
    "AND count_type1 != LOWER(count_type_gpt)\n",
    "''').display()\n",
    "\n",
    "# How many total observations were there?\n",
    "spark.sql('''\n",
    "SELECT COUNT(*)\n",
    "FROM covid_gpt\n",
    "WHERE count_type_gpt IS NOT NULL\n",
    "AND LOWER(count_type_gpt) != 'unknown'\n",
    "''').display()\n",
    "\n",
    "# GPT accuracy: 73.5%\n",
    "\n",
    "# --------------------\n",
    "# COVID - generated\n",
    "# --------------------\n",
    "\n",
    "spark.sql('''\n",
    "with tokenized AS (SELECT *, SPLIT(sentence, ' ') AS tokens\n",
    "FROM user_nsulliv3.simpler_covid_gen),\n",
    "\n",
    "count_type AS (SELECT *, CONCAT_WS(' ', tokens[3], tokens[4]) AS count_type_pattern\n",
    "FROM tokenized),\n",
    "\n",
    "-- used to write case when\n",
    "-- SELECT DISTINCT table, count_type_pattern\n",
    "-- FROM count_type\n",
    "\n",
    "regex_pred AS (SELECT sentence, table,\n",
    "CASE WHEN count_type_pattern = 'new cases' THEN 'new confirmed'\n",
    "WHEN count_type_pattern IN ('total confirmed', 'cases in', 'confirmed cases', 'total cases', 'cases .') THEN 'total confirmed'\n",
    "WHEN count_type_pattern = 'deaths cases' THEN 'total deaths'\n",
    "WHEN count_type_pattern = 'recovered cases' THEN 'total recovered'\n",
    "ELSE count_type_pattern \n",
    "END AS count_type_pred\n",
    "FROM count_type)\n",
    "\n",
    "SELECT COUNT(*) AS n_matches\n",
    "FROM regex_pred\n",
    "WHERE table = count_type_pred\n",
    ";\n",
    "''')\n",
    "\n",
    "# count_type: 100% accuracy\n",
    "# country: 98.6% (6912/7008)"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 2
   },
   "notebookName": "simplER_eval",
   "notebookOrigID": 4020398906430707,
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}