scripts/generate_exercise_precursors.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Generate exercise precursors\n",
    "This notebook generates sentence pairs ready for quality control to incorporate into pre-generated language learning exercises.\n",
    "\n",
    "**Inputs:** See below\n",
    "- Most important is the `IN_WORDS` parameter which is a list of words (in target language) for which exercises should be generated.\n",
    "  The list is loaded from the `data-import/in-words.tsv` file which is expected to have the following structure:\n",
    "  ```\n",
    "  word\n",
    "  Haus\n",
    "  Maus\n",
    "  ...\n",
    "  ```\n",
    "\n",
    "**Outputs:** A list of exercise precursors stored in `data-generated/exercise-precursors.tsv`\n",
    "\n",
    "This list can be quality checked, before it will be finally used to populate the exercise table which is exposed in the API.\n",
    "\n",
    "**Prerequisites:**\n",
    "\n",
    "- Generate and import SQL data (see README).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%pip install pandas textdistance tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import gc\n",
    "import random\n",
    "import re\n",
    "\n",
    "import pandas as pd\n",
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Source language of the translation exercices.\n",
    "IN_SOURCE_LANG = \"UK\"\n",
    "\n",
    "# Target language of the translation exercises.\n",
    "IN_TARGET_LANG = \"DE\"\n",
    "\n",
    "# Words in the target language to train. If empty, a few of the most common words are automatically selected for demonstration purposes.\n",
    "# IN_WORDS = []\n",
    "IN_WORDS = pd.read_csv(\"data-import/in-words.tsv\", sep=\"\\t\").word\n",
    "\n",
    "# For each word, the number of translation pairs to generate.\n",
    "IN_NUM_TRANSLATIONS = 5\n",
    "\n",
    "# Minimum number of words in the target sentence.\n",
    "IN_MIN_WORDS = 4\n",
    "\n",
    "# Maximum number of words in the target sentence.\n",
    "IN_MAX_WORDS = 9\n",
    "\n",
    "# If IN_WORDS is blank, populate it with this many sample words\n",
    "DEFAULT_N_WORDS = 10\n",
    "\n",
    "# If IN_WORDS is blank, find default words having at least this length.\n",
    "DEFAULT_MIN_WORDS_SIZE = 4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Use sample data for input words\n",
    "If `IN_WORDS` is blank, provide some common sample words instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "if len(IN_WORDS) == 0:\n",
    "    def get_words_by_frequency():\n",
    "        r_nonword = re.compile(r\"\"\"[.,?!:;()\"]\"\"\")\n",
    "        r_whitespace = re.compile(r\"[/\\s]+\")\n",
    "        word_counts = {}\n",
    "\n",
    "        df_sentences = pd.read_csv(\"./data-generated/sentences.tsv\",\n",
    "                                   sep=\"\\t\", index_col=\"id\")\n",
    "        df_targets = df_sentences[df_sentences.language == IN_TARGET_LANG]\n",
    "\n",
    "        # Split words and get word counts\n",
    "        for sentence in tqdm(df_targets.text):\n",
    "            words = [r_nonword.sub(\"\",w.lower())\n",
    "                     for w in r_whitespace.split(sentence)]\n",
    "            for word in words:\n",
    "                if len(word) >= DEFAULT_MIN_WORDS_SIZE:\n",
    "                    word_counts[word] = word_counts.get(word, 0) + 1\n",
    "\n",
    "        sort_dict = sorted(word_counts.items(), key=lambda x: x[1], reverse=True)\n",
    "        sort_dict = dict(sort_dict)\n",
    "\n",
    "        return sort_dict\n",
    "\n",
    "    most_common_words = list(get_words_by_frequency().keys())[0:DEFAULT_N_WORDS * 10]\n",
    "    sampled_common_words = random.sample(most_common_words, DEFAULT_N_WORDS)\n",
    "    IN_WORDS = sampled_common_words\n",
    "\n",
    "gc.collect()\n",
    "IN_WORDS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "\n",
    "translations_q = \"\"\"\n",
    "SELECT\n",
    "    word,\n",
    "    t.id as translation_id,\n",
    "    source.id as sourceId,\n",
    "    source.text as sourceText,\n",
    "    source.language as sourceLanguage,\n",
    "    target.id as targetId,\n",
    "    target.text as targetText,\n",
    "    target.word_count as twc,\n",
    "    target.language as targetLanguage FROM vocabulary v\n",
    "                                               JOIN sentence_vocabulary sv on v.id = sv.vocabulary_id\n",
    "                                               JOIN sentences target on sv.sentence_id = target.id\n",
    "                                               JOIN translations t on target.id = t.s1 or target.id = t.s2\n",
    "                                               JOIN sentences source on source.id = t.s1 or source.id = t.s2\n",
    "WHERE v.language = :target_lang and word = :word and :min_words <= twc and twc <= :max_words\n",
    "\"\"\"\n",
    "\n",
    "with sqlite3.connect(\"../taskpool.db\") as conn:\n",
    "    all_results = []\n",
    "    for word in tqdm(IN_WORDS):\n",
    "        results = conn.execute(translations_q, {\"target_lang\": IN_TARGET_LANG, \"word\": word, \"min_words\": IN_MIN_WORDS, \"max_words\": IN_MAX_WORDS}).fetchall()\n",
    "\n",
    "        added = 0\n",
    "\n",
    "        for x in results:\n",
    "            if x[4] == IN_SOURCE_LANG:\n",
    "                added = added + 1\n",
    "                all_results.append({\n",
    "                    \"translation_id\": x[1],\n",
    "                    \"word\": word,\n",
    "                    \"source_sentence_id\": x[2],\n",
    "                    \"source_sentence\": x[3],\n",
    "                    \"target_language\": IN_TARGET_LANG,\n",
    "                    \"target_sentence_id\": x[5],\n",
    "                    \"target_sentence\": x[6],\n",
    "                    \"difficulty\": 0,\n",
    "                })\n",
    "\n",
    "                if added >= IN_NUM_TRANSLATIONS:\n",
    "                    break\n",
    "\n",
    "        print(len(all_results))\n",
    "\n",
    "df = pd.DataFrame(all_results)\n",
    "df.to_csv(\"data-generated/exercise-precursors.tsv\", sep=\"\\t\", index=False)\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}