Postprocessing__comparison_operator_correction.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "DZ7yMeIGc1YX"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "df1=pd.read_csv(\"/content/results_improved - results_improved (1).csv\") #in the results_improved file I added one more col of nlp queries\n",
        "import re\n",
        "\n",
        "nlp_queries = df1[[\"Text\"]]\n",
        "gold_queries = df1[[\"Gold\"]]\n",
        "predicted_queries = df1[[\"Preds_ec_trb\"]]\n",
        "\n",
        "\n",
        "nlp_queries.to_csv(\"nlp_queries.csv\", index=False)\n",
        "gold_queries.to_csv(\"gold_queries.csv\", index=False)\n",
        "predicted_queries.to_csv(\"predicted_queries.csv\", index=False)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FwI31MMvE-qI",
        "outputId": "ff9afcbd-7c21-40f6-fc08-cdd072d217db"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def find_comparison_sign_regex(nlp_query):\n",
        "    less_than_match = re.search(r'\\b(less|smaller|shorter|fewer|below)(?:\\s+than)?\\b', nlp_query)\n",
        "    greater_than_match = re.search(r'\\b(greater|larger|longer|more|above)(?:\\s+than)?\\b', nlp_query)\n",
        "    equal_to_match = re.search(r'\\bequal\\s+to\\b', nlp_query)\n",
        "\n",
        "    if less_than_match:\n",
        "        return '<'\n",
        "    elif greater_than_match:\n",
        "        return '>'\n",
        "    elif equal_to_match:\n",
        "        return '='\n",
        "    else:\n",
        "        return None\n",
        "\n",
        "\n",
        "\n",
        "def correct_comparison_sign(predicted_query, correct_sign):\n",
        "    if correct_sign:\n",
        "        return re.sub(r'(<unk>)', correct_sign, predicted_query)\n",
        "    else:\n",
        "        return re.sub(r'<unk>', '', predicted_query)\n",
        "def correct_or_remove_tags(predicted_query):\n",
        "    return re.sub(r'<.*?>','',predicted_query)\n",
        "\n",
        "for i in range(len(nlp_queries)):\n",
        "    nlp_query = nlp_queries.iloc[i][\"Text\"]\n",
        "    gold_query = gold_queries.iloc[i][\"Gold\"]\n",
        "    predicted_query = predicted_queries.iloc[i][\"Preds_ec_trb\"]\n",
        "\n",
        "    correct_sign = find_comparison_sign_regex(nlp_query)\n",
        "    if correct_sign:\n",
        "        corrected_query = correct_comparison_sign(predicted_query, correct_sign)\n",
        "        corrected_query = correct_or_remove_tags(corrected_query)\n",
        "        predicted_queries.at[i, \"Corrected_Predicted_Query\"] = corrected_query\n",
        "        \n",
        "        print(f\"NLP Query: {nlp_query} | Original: {predicted_query} | Corrected: {corrected_query}\")   "
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kYbkTn-7ddXg",
        "outputId": "8abe4456-a3d0-4dff-ceed-b22a63b99fad"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "NLP Query: Show the names of singers that have more than one song. | Original: select t1.name from singer as t1 join song as t2 on | Corrected: select t1.name from singer as t1 join song as t2 on\n",
            "NLP Query: Which district has both stores with less than 3000 products and stores with more than 10000 products? | Original: select district from shop where store_id not in (select store | Corrected: select district from shop where store_id not in (select store\n",
            "NLP Query: Count the number of dogs of an age below the average. | Original: select count(*) from dogs where age <unk>(select | Corrected: select count(*) from dogs where age <(select\n",
            "NLP Query: List all song names by singers above the average age. | Original: select name from singer where age > (select avg( | Corrected: select name from singer where age > (select avg(\n",
            "NLP Query: What are the African countries that have a\\'a0 population less than any country in Asia? | Original: select countrycode from country where population <unk>(select country_ | Corrected: select countrycode from country where population <(select country_\n",
            "NLP Query: What are the different years in which there were cars produced that weighed less than 4000 and also cars that weighted more than 3000 ? | Original: select year from cars where weight <unk>4000 and year > 3000 | Corrected: select year from cars where weight  3000\n",
            "NLP Query: what are the different names of the singers that have sales more than 300000? | Original: select highest_position from singer where net worth_millions | Corrected: select highest_position from singer where net worth_millions\n",
            "NLP Query: How many cars have a larger accelerate than the car with the largest horsepower? | Original: select count(*) from cars where accelerate = \"accrob | Corrected: select count(*) from cars where accelerate = \"accrob\n",
            "NLP Query: What are the names of the singers that have more than one songs? | Original: select name from singer where net worth_millions > 1</s> | Corrected: select name from singer where net worth_millions > 1\n",
            "NLP Query: Which grades have 4 or more high schoolers? | Original: select grade from high_schooler where grade >= 4</s> | Corrected: select grade from high_schooler where grade >= 4\n",
            "NLP Query: What are the codes of template types that have fewer than 3 templates? | Original: select template_type_code from templates where template_type_code | Corrected: select template_type_code from templates where template_type_code\n",
            "NLP Query: What are the ids of documents that have 2 or more paragraphs? | Original: select document_id from documents where document_name >= 2</s> | Corrected: select document_id from documents where document_name >= 2\n",
            "NLP Query: Show the names of conductors that have conducted more than one orchestras. | Original: select t1.name from conductor as t1 join performance as t2 | Corrected: select t1.name from conductor as t1 join performance as t2\n",
            "NLP Query: Return the version numbers and template type codes of templates with a version number greater than 5. | Original: select version_number, template_type_code from templates where | Corrected: select version_number, template_type_code from templates where\n",
            "NLP Query: What is the model for the car with a weight smaller than the average? | Original: select model from cars where weight <unk>(select | Corrected: select model from cars where weight <(select\n",
            "NLP Query: Give the names of countries that are in Europe and have a population equal to 80000. | Original: select name from country where population = \"europe\" and population > 80000 | Corrected: select name from country where population = \"europe\" and population > 80000\n",
            "NLP Query: Which makers designed more than 3 car models? List full name and the id. | Original: select maker from car_mades where full_name > 3</s> | Corrected: select maker from car_mades where full_name > 3\n",
            "NLP Query: Show all template type codes with less than three templates. | Original: select template_type_code from templates where template_type_code | Corrected: select template_type_code from templates where template_type_code\n",
            "NLP Query: What are the ids of all tv channels that have more than 2 TV channels? | Original: select id from tv_channel where channel > 2</s> | Corrected: select id from tv_channel where channel > 2\n",
            "NLP Query: Which shops' number products is above the average? Give me the shop names. | Original: select t2.name from shop as t1 join shop as t2 on | Corrected: select t2.name from shop as t1 join shop as t2 on\n",
            "NLP Query: What is the maximum number of final tables made among poker players with earnings less than 200000? | Original: select max(t1.final_table_made) from poker_players w | Corrected: select max(t1.final_table_made) from poker_players w\n",
            "NLP Query: How many cities in each district have a population that is above the average population across all cities? | Original: select count(*), district from city group by district h | Corrected: select count(*), district from city group by district h\n",
            "NLP Query: Among the cars with more than lowest horsepower, which ones do not have more than 3 cylinders? List the car makeid and make name. | Original: select t1.make, t1.make from cars_data as t1 | Corrected: select t1.make, t1.make from cars_data as t1\n",
            "NLP Query: What is the number of the cars with horsepower more than 150? | Original: select count(*) from cars where horsepower > 150</s> | Corrected: select count(*) from cars where horsepower > 150\n",
            "NLP Query: What is the number of cars with a horsepower greater than 150? | Original: select count(*) from cars where horsepower > 150</s> | Corrected: select count(*) from cars where horsepower > 150\n",
            "NLP Query: How many pets have a greater weight than 10? | Original: select count(*) from pets where weight > 10</s> | Corrected: select count(*) from pets where weight > 10\n",
            "NLP Query: What are the ids and makers of all car makers that produce at least 2 models and make more than 3 cars? | Original: select maker, id from cars group by maker hav | Corrected: select maker, id from cars group by maker hav\n",
            "NLP Query: What are the different models created by either the car maker General Motors or weighed more than 3500? | Original: select model from car_makers where maker = ' | Corrected: select model from car_makers where maker = '\n",
            "NLP Query: Find the id, last name and cell phone of the professionals who live in the state of Indiana or have performed more than two treatments. | Original: select professional_id, last_name, cell_number from | Corrected: select professional_id, last_name, cell_number from\n",
            "NLP Query: What are the first name and last name of the professionals who have done treatment with cost below average? | Original: select first_name, last_name from treatment where cost_of | Corrected: select first_name, last_name from treatment where cost_of\n",
            "NLP Query: Which African countries have a smaller population than that of any country in Asia? | Original: select countrycode from country where population > (select countrycode | Corrected: select countrycode from country where population > (select countrycode\n",
            "NLP Query: Which distinctive models are produced by maker with the full name General Motors or weighing more than 3500? | Original: select distinct t1.model from cars_data as t1 jo | Corrected: select distinct t1.model from cars_data as t1 jo\n",
            "NLP Query: Which dogs have not cost their owner more than 1000 for treatment ? List the dog names . | Original: select t1.name from dogs as t1 join treatment as t2 on | Corrected: select t1.name from dogs as t1 join treatment as t2 on\n",
            "NLP Query: Find the id and cell phone of the professionals who operate two or more types of treatments. | Original: select professional_id, cell_number from treatments where treatment | Corrected: select professional_id, cell_number from treatments where treatment\n",
            "NLP Query: What is the number of cars with more than 4 cylinders? | Original: select count(*) from cars where horsepower > 4</s> | Corrected: select count(*) from cars where horsepower > 4\n",
            "NLP Query: Find the cities that have more than one employee under age 30. | Original: select city from hiring where age > 1</s> | Corrected: select city from hiring where age > 1\n",
            "NLP Query: What are the id, role, and first name of the professionals who have performed two or more treatments? | Original: select role_code, role_code from treatments where treatment_type | Corrected: select role_code, role_code from treatments where treatment_type\n",
            "NLP Query: Find the government form name and total population for each government form whose average life expectancy is longer than 72. | Original: select name, sum(population) from government where av | Corrected: select name, sum(population) from government where av\n",
            "NLP Query: Give the total population and average surface area corresponding to countries in North America that have a surface area greater than 3000 . | Original: select sum(population), avg(surface_area) | Corrected: select sum(population), avg(surface_area)\n",
            "NLP Query: Show the years in which orchestras that have given more than one performance are founded. | Original: select type from performance order by type_of_founded desc limit | Corrected: select type from performance order by type_of_founded desc limit\n",
            "NLP Query: Show ids for all templates that are used by more than one document. | Original: select t1.document_id from templates as t1 join documents as | Corrected: select t1.document_id from templates as t1 join documents as\n",
            "NLP Query: Find the number of cities in each district whose population is greater than the average population of cities? | Original: select count(*), district from city group by district h | Corrected: select count(*), district from city group by district h\n",
            "NLP Query: What are the names of students who have 2 or more likes? | Original: select t1.name from friend as t1 join likes as t2 | Corrected: select t1.name from friend as t1 join likes as t2\n",
            "NLP Query: In which years cars were produced weighing no less than 3000 and no more than 4000 ? | Original: select year from cars where weight <unk> 3000 and weight <unk>4 | Corrected: select year from cars where weight < 3000 and weight <4\n",
            "NLP Query: find id of the tv channels that from the countries where have more than two tv channels. | Original: select channel from tv_country where tv_ | Corrected: select channel from tv_country where tv_\n",
            "NLP Query: Return the maximum final tables made across all poker players who have earnings below 200000. | Original: select max(t1.final_table_made) from poker_players w | Corrected: select max(t1.final_table_made) from poker_players w\n",
            "NLP Query: How many countries has more than 2 car makers ? | Original: select count(*) from countries where maker > 2</s> | Corrected: select count(*) from countries where maker > 2\n",
            "NLP Query: Which are the car makers which produce at least 2 models and more than 3 car makers ? List the id and the maker . | Original: select maker, id from car_making group by maker | Corrected: select maker, id from car_making group by maker\n",
            "NLP Query: Which professionals live in the state of Indiana or have done treatment on more than 2 treatments? List his or her id, last name and cell phone. | Original: select professional_id from treatments where state = \"indiana\" | Corrected: select professional_id from treatments where state = \"indiana\"\n",
            "NLP Query: Find all airlines that have fewer than 200 flights. | Original: select t1.airline from flights as t1 join aircraft as | Corrected: select t1.airline from flights as t1 join aircraft as\n",
            "NLP Query: What are the names of countries that speak more than 2 languages, as well as how many languages they speak? | Original: select name from country where language > 2</s> | Corrected: select name from country where language > 2\n",
            "NLP Query: What are the ids and names of the battles that led to more than 10 people killed in total. | Original: select t1.id, t1.name from battle as t1 jo | Corrected: select t1.id, t1.name from battle as t1 jo\n",
            "NLP Query: What is the first name and gender of the all the students who have more than one pet? | Original: select t1.fname, t1.age from pets as t1 | Corrected: select t1.fname, t1.age from pets as t1\n",
            "NLP Query: Find the codes of countries that have more than 50 players. | Original: select country_code from players where count(*) > 50</s> | Corrected: select country_code from players where count(*) > 50\n",
            "NLP Query: What are the id and names of the countries which have more than 3 car makers or produce the 'fiat' model? | Original: select country, id from countries where country_name > 3 | Corrected: select country, id from countries where country_name > 3\n",
            "NLP Query: find the names of museums which have more staff than the minimum staff number of all museums opened after 2010. | Original: select t1.name from museum as t1 join visit as t2 on | Corrected: select t1.name from museum as t1 join visit as t2 on\n",
            "NLP Query: Which Asian countries have a population that is larger than any country in Africa? | Original: select countrycode from country where population > (select countrycode | Corrected: select countrycode from country where population > (select countrycode\n",
            "NLP Query: Find the name of tourney that has more than 10 matches. | Original: select t1.tourney_name from matches as t1 join tourney_namets as | Corrected: select t1.tourney_name from matches as t1 join tourney_namets as\n",
            "NLP Query: What are the Asian countries which have a population larger than that of any country in Africa? | Original: select countrycode from country where population > (select countrycode | Corrected: select countrycode from country where population > (select countrycode\n",
            "NLP Query: What are the names of high schoolers who have 3 or more friends? | Original: select t1.name from highschooler as t1 join likes | Corrected: select t1.name from highschooler as t1 join likes\n",
            "NLP Query: What are\\'a0 the different countries with singers above age 20? | Original: select country from singer where age > 20</s> | Corrected: select country from singer where age > 20\n",
            "NLP Query: What is the number of cars with a greater accelerate than the one with the most horsepower? | Original: select count(*) from cars where accelerate > (select speed from | Corrected: select count(*) from cars where accelerate > (select speed from\n",
            "NLP Query: Which professionals have operated a treatment that costs less than the average? Give me theor first names and last names. | Original: select professional_id from treatments where cost_of_treatment > ( | Corrected: select professional_id from treatments where cost_of_treatment > (\n",
            "NLP Query: Find the average life expectancy and total population for each continent where the average life expectancy is shorter than 72? | Original: select avg(life_attendancy), sum(life_ | Corrected: select avg(life_attendancy), sum(life_\n",
            "NLP Query: Show countries where a singer above age 40 and a singer below 30 are from. | Original: select country from singer where age > 40 intersect select | Corrected: select country from singer where age > 40 intersect select\n",
            "NLP Query: What are the ids and names of all countries that either have more than 3 car makers or produce fiat model ? | Original: select country, id from countries where country_name > 3 | Corrected: select country, id from countries where country_name > 3\n",
            "NLP Query: What is the number of countries with more than 2 car makers ? | Original: select count(*) from countries where maker > 2</s> | Corrected: select count(*) from countries where maker > 2\n",
            "NLP Query: How many dogs have an age below the average? | Original: select count(*) from dogs where age <unk>(select | Corrected: select count(*) from dogs where age <(select\n",
            "NLP Query: What are the names of properties that are either houses or apartments with more than 1 room? | Original: select property_name from properties where room_count > 1</s> | Corrected: select property_name from properties where room_count > 1\n",
            "NLP Query: Find the districts in which there are both shops selling less than 3000 products and shops selling more than 10000 products. | Original: select district from shop where shop_id not in (select shop | Corrected: select district from shop where shop_id not in (select shop\n",
            "NLP Query: Find the first name and gender of student who have more than one pet. | Original: select t1.fname, t1.age from student as t1 | Corrected: select t1.fname, t1.age from student as t1\n",
            "NLP Query: Which airlines have less than 200 flights? | Original: select t1.airline from flights as t1 join aircraft as | Corrected: select t1.airline from flights as t1 join aircraft as\n",
            "NLP Query: How many visitors below age 30 are there? | Original: select count(*) from visit where age <unk> 30</s> | Corrected: select count(*) from visit where age \n",
            "NLP Query: What are the names of high schoolers who have a grade of over 5 and have 2 or more friends? | Original: select t1.name from highschooler as t1 join likes | Corrected: select t1.name from highschooler as t1 join likes\n",
            "NLP Query: Find the names of stores whose number products is more than the average number of products. | Original: select t1.name from shop as t1 join number_products as | Corrected: select t1.name from shop as t1 join number_products as\n",
            "NLP Query: What are the different government forms and what is the total population of each for government forms that have an average life expectancy greater than 72? | Original: select governmentform, sum(population) from government | Corrected: select governmentform, sum(population) from government\n",
            "NLP Query: Give the names of poker players who have earnings above 300000. | Original: select t1.name from poker_player as t1 join people as | Corrected: select t1.name from poker_player as t1 join people as\n",
            "NLP Query: What are the names of the dogs for which the owner has not spend more than 1000 for treatment ? | Original: select t1.name from dogs as t1 join treatment as t2 on | Corrected: select t1.name from dogs as t1 join treatment as t2 on\n",
            "NLP Query: Find the model of the car whose weight is below the average weight. | Original: select model from cars where weight <unk>(select | Corrected: select model from cars where weight <(select\n",
            "NLP Query: Return the nationalities for which there are two or more people. | Original: select nationality from people where nationality >= 2</s> | Corrected: select nationality from people where nationality >= 2\n",
            "NLP Query: What are the codes of countries with more than 50 players? | Original: select country_code from players where country_code > 50</s> | Corrected: select country_code from players where country_code > 50\n",
            "NLP Query: Show distinct names of singers that have songs with sales more than 300000. | Original: select highest_position from singer where net worth_millions | Corrected: select highest_position from singer where net worth_millions\n",
            "NLP Query: What are years of founding for orchestras that have had more than a single performance? | Original: select year_of_founded from performance order by count(*) | Corrected: select year_of_founded from performance order by count(*)\n",
            "NLP Query: Among the cars that do not have the minimum horsepower , what are the make ids and names of all those with less than 4 cylinders ? | Original: select make, mpg from cars where horsepower <unk> 4</s> | Corrected: select make, mpg from cars where horsepower \n",
            "NLP Query: How many pets are owned by students that have an age greater than 20? | Original: select count(*) from pets where age > 20</s> | Corrected: select count(*) from pets where age > 20\n",
            "NLP Query: What are the template ids of any templates used in more than a single document? | Original: select t1.document_id from templates as t1 join documents as | Corrected: select t1.document_id from templates as t1 join documents as\n",
            "NLP Query: What are the names and ids of every course with less than 2 sections? | Original: select course_name, course_id from students where semester_ | Corrected: select course_name, course_id from students where semester_\n",
            "NLP Query: What are all distinct countries where singers above age 20 are from? | Original: select country from singer where age > 20</s> | Corrected: select country from singer where age > 20\n",
            "NLP Query: What are the names and ids of all makers with more than 3 models? | Original: select maker, fullname from cars_makers where id | Corrected: select maker, fullname from cars_makers where id\n",
            "NLP Query: Which countries have greater area than that of any country in Europe? | Original: select countrycode from country where surface_area > (select country | Corrected: select countrycode from country where surface_area > (select country\n",
            "NLP Query: Which cities do more than one employee under age 30 come from? | Original: select city from hiring where age > 1</s> | Corrected: select city from hiring where age > 1\n",
            "NLP Query: What are the countries that have greater surface area than any country in Europe? | Original: select t1.countrycode from countrycode as t1 join countrycode as t2 | Corrected: select t1.countrycode from countrycode as t1 join countrycode as t2\n",
            "NLP Query: find the id, name and age for visitors who visited some museums more than once. | Original: select t1.name, t1.name, t1.age from visit as | Corrected: select t1.name, t1.name, t1.age from visit as\n",
            "NLP Query: What are the names of tournaments that have more than 10 matches? | Original: select t1.tourney_name from matches as t1 join tourney_namets as | Corrected: select t1.tourney_name from matches as t1 join tourney_namets as\n",
            "NLP Query: How many cars have more than 4 cylinders? | Original: select count(*) from cars where horsepower > 4</s> | Corrected: select count(*) from cars where horsepower > 4\n",
            "NLP Query: What are the names of conductors who have conducted at more than one orchestra? | Original: select t1.name from conductor as t1 join performance as t2 | Corrected: select t1.name from conductor as t1 join performance as t2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Way 2 (Using SpaCy), if in report we want to present it in complex way"
      ],
      "metadata": {
        "id": "QUBjUW0Ldt3x"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "nlp = spacy.load(\"en_core_web_sm\") #for parsing nlp queries , this is a predefined nlp model\n",
        "\n",
        "def find_comparison_sign(nlp_query):\n",
        "    doc = nlp(nlp_query)\n",
        "    comparison_sign = None\n",
        "    for token in doc:\n",
        "        if token.dep_ in (\"prep\", \"relcl\") and token.head.text in (\"less\", \"more\", \"equal\"):\n",
        "            if token.text == \"than\":\n",
        "                if token.head.text == \"less\":\n",
        "                    comparison_sign = \"<\"\n",
        "                elif token.head.text == \"more\":\n",
        "                    comparison_sign = \">\"\n",
        "            elif token.text == \"to\":\n",
        "                if token.head.text == \"equal\":\n",
        "                    comparison_sign = \"=\"\n",
        "            break\n",
        "    return comparison_sign\n",
        "\n",
        "\n",
        "  "
      ],
      "metadata": {
        "id": "Nk7xAaCEeCix"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}