1.5 English Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.5 Load and Merge English Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amh\n",
      "['amh', 'Hana', 'Bereket T', 'Bereket A']\tewe\n",
      "['ewe', 'ALASSE', 'KLOVE', 'APELETE']\thau\n",
      "['hau', 'Ruqayya', 'Saminu', 'Yusuf']\tibo\n",
      "['ibo', 'Adaeze', 'Winnie', 'Tochukwu']\tkin\n",
      "['Happy', 'Jean', 'Emile', 'Melance']\tlin\n",
      "['lin', 'Christian', 'Ebenya', 'Pacifique']\tlug\n",
      "['lug', 'Ivan', 'Ibra', 'Deborah']\torm\n",
      "['orm', 'Maraf Mengesha', 'Irandufa Indebu', 'Irandufa and Maraf']\tsna\n",
      "['sna', 'Mr Mupini', 'Mr Zenda', 'Mr Mhizha']\tsot\n",
      "['sot', 'Jan1', 'Madibe', 'Refilwe']\tswa\n",
      "['swa', 'Iddy', 'Nelson', 'Pauline']\ttwi\n",
      "['twi', 'Bernard', 'Godwin', 'Stephen']\twol\n",
      "['wol', 'Fadel', 'Khady', 'Penda']\txho\n",
      "['raw', 'DIKO', 'SILO', 'NYAKAMBI']\tyor\n",
      "['yor', 'Tolulope', 'Ganiyat', 'Johnson']\tzul\n",
      "['zul', 'Babalo', 'Brilliant', 'Neo']\t"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pprint import pprint\n",
    "from mlds import data_loader\n",
    "from mlds.utils.converter import simple_majority_vote\n",
    "# disable warnings\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "entity_manager = data_loader.EntityDataManager(data_folder=\"data/json\")\n",
    "intent_manager = data_loader.IntentDataManager(\n",
    "    data_folder=\"data/UtteranceGen/practice\"\n",
    ")\n",
    "\n",
    "english_entity = entity_manager.load_english_data(reviewed=True, merged=True)\n",
    "english_intent = intent_manager.load_english_data()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "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>split</th>\n",
       "      <th>domain</th>\n",
       "      <th>intent</th>\n",
       "      <th>raw</th>\n",
       "      <th>text</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>train</td>\n",
       "      <td>travel</td>\n",
       "      <td>translate</td>\n",
       "      <td>\"አውቶቢሱን መጠቀም አፈልጋለው\" በኦሮምኛ እንዴት ነው ምለው?</td>\n",
       "      <td>how do I say \"I want to use the bus\" in Oromo ...</td>\n",
       "      <td>amh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>train</td>\n",
       "      <td>banking</td>\n",
       "      <td>transfer</td>\n",
       "      <td>50000 ብር ከንግድ ባንክ አካውንቴ ወደሲቢኢ ብር ልታስተላልፍልኝ ትችላለህ?</td>\n",
       "      <td>Can you transfer 50000 birr from my commercial...</td>\n",
       "      <td>amh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>train</td>\n",
       "      <td>utility</td>\n",
       "      <td>time</td>\n",
       "      <td>በአዲስ አበባ ስንት ሰአት ነው?</td>\n",
       "      <td>what time is it in Addis Ababa</td>\n",
       "      <td>amh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>train</td>\n",
       "      <td>home</td>\n",
       "      <td>shopping_list_update</td>\n",
       "      <td>የአስቤዛ ዝርዝር ላይ ጨምርበት</td>\n",
       "      <td>Add teff to the groceries list.</td>\n",
       "      <td>amh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>train</td>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>restaurant_reservation</td>\n",
       "      <td>ቶቶት ክትፎ ቤት እራት ቦታ ልቲዝልኝ ትችላለህ?</td>\n",
       "      <td>Can you reserve a dinner place for me at Totot...</td>\n",
       "      <td>amh</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>train</td>\n",
       "      <td>home</td>\n",
       "      <td>update_playlist</td>\n",
       "      <td>ngicela wengeze i-albhamu kaBritney Spears - i...</td>\n",
       "      <td>please add Britney Spears - Baby One More Time...</td>\n",
       "      <td>zul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>train</td>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>cancel_reservation</td>\n",
       "      <td>ngiyikhansela kanjani indawo yami engiyibhukil...</td>\n",
       "      <td>how do I cancel my reservation at The Little I...</td>\n",
       "      <td>zul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>train</td>\n",
       "      <td>travel</td>\n",
       "      <td>car_rental</td>\n",
       "      <td>kubiza malini ukuqasha iHyundai i20 kwaCeza?</td>\n",
       "      <td>how much does it cost to rent a Hyundai i20 in...</td>\n",
       "      <td>zul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>train</td>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>meal_suggestion</td>\n",
       "      <td>phakamisa indawo yokudlela ye-vegan eWestville</td>\n",
       "      <td>suggest a vegan restaurant in Westville</td>\n",
       "      <td>zul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>train</td>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>recipe</td>\n",
       "      <td>phakamisa iresiphi yacurry wemutton</td>\n",
       "      <td>suggest a recipe for lamb shanks with red wine</td>\n",
       "      <td>zul</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1871 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    split              domain                  intent  \\\n",
       "0   train              travel               translate   \n",
       "1   train             banking                transfer   \n",
       "2   train             utility                    time   \n",
       "3   train                home    shopping_list_update   \n",
       "4   train  kitchen_and_dining  restaurant_reservation   \n",
       "..    ...                 ...                     ...   \n",
       "35  train                home         update_playlist   \n",
       "36  train  kitchen_and_dining      cancel_reservation   \n",
       "37  train              travel              car_rental   \n",
       "38  train  kitchen_and_dining         meal_suggestion   \n",
       "39  train  kitchen_and_dining                  recipe   \n",
       "\n",
       "                                                  raw  \\\n",
       "0             \"አውቶቢሱን መጠቀም አፈልጋለው\" በኦሮምኛ እንዴት ነው ምለው?   \n",
       "1   50000 ብር ከንግድ ባንክ አካውንቴ ወደሲቢኢ ብር ልታስተላልፍልኝ ትችላለህ?   \n",
       "2                                በአዲስ አበባ ስንት ሰአት ነው?   \n",
       "3                                 የአስቤዛ ዝርዝር ላይ ጨምርበት   \n",
       "4                      ቶቶት ክትፎ ቤት እራት ቦታ ልቲዝልኝ ትችላለህ?   \n",
       "..                                                ...   \n",
       "35  ngicela wengeze i-albhamu kaBritney Spears - i...   \n",
       "36  ngiyikhansela kanjani indawo yami engiyibhukil...   \n",
       "37       kubiza malini ukuqasha iHyundai i20 kwaCeza?   \n",
       "38     phakamisa indawo yokudlela ye-vegan eWestville   \n",
       "39                phakamisa iresiphi yacurry wemutton   \n",
       "\n",
       "                                                 text language  \n",
       "0   how do I say \"I want to use the bus\" in Oromo ...      amh  \n",
       "1   Can you transfer 50000 birr from my commercial...      amh  \n",
       "2                      what time is it in Addis Ababa      amh  \n",
       "3                     Add teff to the groceries list.      amh  \n",
       "4   Can you reserve a dinner place for me at Totot...      amh  \n",
       "..                                                ...      ...  \n",
       "35  please add Britney Spears - Baby One More Time...      zul  \n",
       "36  how do I cancel my reservation at The Little I...      zul  \n",
       "37  how much does it cost to rent a Hyundai i20 in...      zul  \n",
       "38            suggest a vegan restaurant in Westville      zul  \n",
       "39     suggest a recipe for lamb shanks with red wine      zul  \n",
       "\n",
       "[1871 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "english_intent.drop(columns=[\"text\"], inplace=True)\n",
    "english_intent.rename(columns={\"English translation\": \"text\", \"generated\": \"raw\"}, inplace=True)\n",
    "english_intent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[[ Start amh\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start ewe\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start hau\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start ibo\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start kin\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "119\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 119\n",
      "[[[ Start lin\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start lug\n",
      "lan_entity: 93\n",
      "lan_intent: 93\n",
      "93\n",
      "lan_entity: 93\n",
      "lan_intent: 93\n",
      "merged df: 93\n",
      "[[[ Start orm\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "100\n",
      "lan_entity: 102\n",
      "lan_intent: 102\n",
      "merged df: 100\n",
      "[[[ Start sna\n",
      "lan_entity: 93\n",
      "lan_intent: 96\n",
      "93\n",
      "lan_entity: 93\n",
      "lan_intent: 93\n",
      "merged df: 93\n",
      "[[[ Start sot\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start swa\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "115\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 115\n",
      "[[[ Start twi\n",
      "lan_entity: 120\n",
      "lan_intent: 122\n",
      "80\n",
      "lan_entity: 120\n",
      "lan_intent: 121\n",
      "merged df: 80\n",
      "[[[ Start wol\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "119\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 119\n",
      "[[[ Start xho\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start yor\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "120\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "merged df: 120\n",
      "[[[ Start zul\n",
      "lan_entity: 120\n",
      "lan_intent: 120\n",
      "100\n",
      "lan_entity: 100\n",
      "lan_intent: 100\n",
      "merged df: 100\n"
     ]
    }
   ],
   "source": [
    "def to_entity_spans(data):\n",
    "    result = []\n",
    "    for annotation in data['annotations']:\n",
    "        if annotation['completed_by']['id'] == 'MAJORITY_VOTE':\n",
    "            for item in annotation['result']:\n",
    "                start = item['value']['start']\n",
    "                end = item['value']['end']\n",
    "                label = item['value']['labels'][0]\n",
    "                result.append(f\"{start}:{end}:SL:{label}\")\n",
    "\n",
    "    return ','.join(sorted(result, key=lambda x: int(x.split(':')[0])))\n",
    "\n",
    "def merge_entity_spans(text_a, spana, text_b, spanb, joint_char=\"\"):\n",
    "    # remember to add a space between the two texts\n",
    "    # the start and end index of the second text should be updated\n",
    "    l = []\n",
    "    for item in spanb.split(\",\"):\n",
    "        if item == \"\":\n",
    "            continue\n",
    "        start, end, _, label = item.split(\":\")\n",
    "        l.append( f\"{int(start) + len(text_a) + len(joint_char)}:{int(end) + len(text_a) + len(joint_char)}:SL:{label}\")\n",
    "    spanb = \",\".join(l)\n",
    "    if spana:\n",
    "        spana += \",\" + spanb\n",
    "    return spanb\n",
    "    \n",
    "def to_logical_form(text, intent, spans):\n",
    "    # [IN:GET_MESSAGE [SL:CONTACT Angelika Kratzer ] [SL:TYPE_CONTENT video ] [SL:RECIPIENT me ] ]\n",
    "    intent = intent.replace(\" \", \"_\")\n",
    "    ss = spans.split(\",\")\n",
    "    # if ss == [\"\"]:\n",
    "    #     return \"\"\n",
    "    result = f\"[IN:{intent} \"\n",
    "    for item in ss:\n",
    "        if item == \"\":\n",
    "            continue\n",
    "        start, end, _, label = item.split(\":\")\n",
    "        result += f\"[SL:{label} {text[int(start):int(end)]}] \"\n",
    "    result += \"]\"\n",
    "    return result\n",
    "\n",
    "\n",
    "def merge_entity_intent(lan, lan_entity, lan_intent):\n",
    "    print(f\"[[[ Start {lan}\")\n",
    "    lan_entity = simple_majority_vote(lan_entity)\n",
    "\n",
    "    lan_entity = pd.DataFrame([{\n",
    "            # \"text\": item[\"data\"][\"text\"],\n",
    "            \"text4match\": item[\"data\"][\"text\"].replace('\"', '').lower(),\n",
    "            \"spans\": to_entity_spans(item),\n",
    "            # \"changed\": item[\"changed\"],\n",
    "        } for item in lan_entity])\n",
    "    # lan_entity.drop(columns=[\"text\"], inplace=True)\n",
    "    # lan_entity.sort_values(\"text4match\").to_csv(f\"data/output/{lan}_entity.csv\", index=False)\n",
    "    \n",
    "    # lan_intent = intent_manager.load_english_data()\n",
    "    lan_intent[\"text4match\"] = lan_intent[\"text\"].str.replace('\"', '').str.lower()#.str.replace('\"', '').str.strip(\"\\n\")\n",
    "    # lan_intent.drop(columns=[\"text\"], inplace=True)\n",
    "    # lan_intent.sort_values(\"text4match\").to_csv(f\"data/output/{lan}_intent.csv\", index=False)\n",
    "\n",
    "    # lan_entity = lan_entity[lan_entity[\"changed\"]]\n",
    "    print(f\"lan_entity: {len(lan_entity)}\")\n",
    "    print(f\"lan_intent: {len(lan_intent)}\")\n",
    "\n",
    "    # lan_intent.sort_values(by=\"text4match\", inplace=True)#.to_csv(f\"data/output/{lan}_intent.csv\", index=False)\n",
    "    # lan_entity.sort_values(by=\"text4match\", inplace=True)\n",
    "\n",
    "    # combine the two dataframes\n",
    "    print(len(set(lan_entity[\"text4match\"].tolist()).intersection(set(lan_intent[\"text4match\"].tolist()))))\n",
    "    # drop the duplicates and nan\n",
    "    def clean(df):\n",
    "        df = df.drop_duplicates(subset=\"text4match\")\n",
    "        df = df.dropna(subset=[\"text4match\"])\n",
    "        return df\n",
    "    lan_intent = clean(lan_intent)\n",
    "    lan_entity = clean(lan_entity)\n",
    "    print(f\"lan_entity: {len(lan_entity)}\")\n",
    "    print(f\"lan_intent: {len(lan_intent)}\")\n",
    "    df = pd.merge(lan_intent, lan_entity, on=\"text4match\", how=\"inner\")\n",
    "    # If both key columns contain rows where the key is a null value, those rows will be matched against each other. This is different from usual SQL join behaviour and can lead to unexpected results.\n",
    "\n",
    "    print(f\"merged df: {len(df)}\")\n",
    "    df[\"logical_form\"]  = df.apply(lambda x: to_logical_form(x[\"text\"], x[\"intent\"], x[\"spans\"]), axis=1)\n",
    "    df.drop(columns=[\"text4match\", \"split\"], inplace=True)\n",
    " \n",
    "    return df\n",
    "\n",
    "langs = []\n",
    "for lan in data_loader.LANGUAGES:\n",
    "    langs.append(\n",
    "    merge_entity_intent(lan, english_entity[lan], english_intent[english_intent[\"language\"] == lan])\n",
    "    )\n",
    "\n",
    "\n",
    "merged = pd.concat(langs)\n",
    "merged.to_csv(\"data/output/eng.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "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>domain</th>\n",
       "      <th>intent</th>\n",
       "      <th>raw</th>\n",
       "      <th>text</th>\n",
       "      <th>language</th>\n",
       "      <th>spans</th>\n",
       "      <th>logical_form</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>travel</td>\n",
       "      <td>translate</td>\n",
       "      <td>\"አውቶቢሱን መጠቀም አፈልጋለው\" በኦሮምኛ እንዴት ነው ምለው?</td>\n",
       "      <td>how do I say \"I want to use the bus\" in Oromo ...</td>\n",
       "      <td>amh</td>\n",
       "      <td>40:45:SL:LANGUAGE_NAME</td>\n",
       "      <td>[IN:translate [SL:LANGUAGE_NAME Oromo] ]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>banking</td>\n",
       "      <td>transfer</td>\n",
       "      <td>50000 ብር ከንግድ ባንክ አካውንቴ ወደሲቢኢ ብር ልታስተላልፍልኝ ትችላለህ?</td>\n",
       "      <td>Can you transfer 50000 birr from my commercial...</td>\n",
       "      <td>amh</td>\n",
       "      <td>17:27:SL:MONEY,36:51:SL:BANK_NAME,63:71:SL:PAY...</td>\n",
       "      <td>[IN:transfer [SL:MONEY 50000 birr] [SL:BANK_NA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>utility</td>\n",
       "      <td>time</td>\n",
       "      <td>በአዲስ አበባ ስንት ሰአት ነው?</td>\n",
       "      <td>what time is it in Addis Ababa</td>\n",
       "      <td>amh</td>\n",
       "      <td>19:30:SL:CITY_OR_PROVINCE</td>\n",
       "      <td>[IN:time [SL:CITY_OR_PROVINCE Addis Ababa] ]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>home</td>\n",
       "      <td>shopping_list_update</td>\n",
       "      <td>የአስቤዛ ዝርዝር ላይ ጨምርበት</td>\n",
       "      <td>Add teff to the groceries list.</td>\n",
       "      <td>amh</td>\n",
       "      <td>4:8:SL:DISH_OR_FOOD</td>\n",
       "      <td>[IN:shopping_list_update [SL:DISH_OR_FOOD teff] ]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>restaurant_reservation</td>\n",
       "      <td>ቶቶት ክትፎ ቤት እራት ቦታ ልቲዝልኝ ትችላለህ?</td>\n",
       "      <td>Can you reserve a dinner place for me at Totot...</td>\n",
       "      <td>amh</td>\n",
       "      <td>18:24:SL:MEAL_PERIOD,41:58:SL:RESTAURANT_NAME</td>\n",
       "      <td>[IN:restaurant_reservation [SL:MEAL_PERIOD din...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>home</td>\n",
       "      <td>update_playlist</td>\n",
       "      <td>ngicela wengeze i-albhamu kaBritney Spears - i...</td>\n",
       "      <td>please add Britney Spears - Baby One More Time...</td>\n",
       "      <td>zul</td>\n",
       "      <td>11:25:SL:ARTIST_NAME,28:46:SL:SONG_NAME</td>\n",
       "      <td>[IN:update_playlist [SL:ARTIST_NAME Britney Sp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>cancel_reservation</td>\n",
       "      <td>ngiyikhansela kanjani indawo yami engiyibhukil...</td>\n",
       "      <td>how do I cancel my reservation at The Little I...</td>\n",
       "      <td>zul</td>\n",
       "      <td>34:61:SL:RESTAURANT_NAME</td>\n",
       "      <td>[IN:cancel_reservation [SL:RESTAURANT_NAME The...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>travel</td>\n",
       "      <td>car_rental</td>\n",
       "      <td>kubiza malini ukuqasha iHyundai i20 kwaCeza?</td>\n",
       "      <td>how much does it cost to rent a Hyundai i20 in...</td>\n",
       "      <td>zul</td>\n",
       "      <td>47:51:SL:PLACE_NAME</td>\n",
       "      <td>[IN:car_rental [SL:PLACE_NAME Ceza] ]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>meal_suggestion</td>\n",
       "      <td>phakamisa indawo yokudlela ye-vegan eWestville</td>\n",
       "      <td>suggest a vegan restaurant in Westville</td>\n",
       "      <td>zul</td>\n",
       "      <td>30:39:SL:PLACE_NAME</td>\n",
       "      <td>[IN:meal_suggestion [SL:PLACE_NAME Westville] ]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>kitchen_and_dining</td>\n",
       "      <td>recipe</td>\n",
       "      <td>phakamisa iresiphi yacurry wemutton</td>\n",
       "      <td>suggest a recipe for lamb shanks with red wine</td>\n",
       "      <td>zul</td>\n",
       "      <td>21:32:SL:DISH_OR_FOOD,38:46:SL:DISH_OR_FOOD</td>\n",
       "      <td>[IN:recipe [SL:DISH_OR_FOOD lamb shanks] [SL:D...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1779 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                domain                  intent  \\\n",
       "0               travel               translate   \n",
       "1              banking                transfer   \n",
       "2              utility                    time   \n",
       "3                 home    shopping_list_update   \n",
       "4   kitchen_and_dining  restaurant_reservation   \n",
       "..                 ...                     ...   \n",
       "95                home         update_playlist   \n",
       "96  kitchen_and_dining      cancel_reservation   \n",
       "97              travel              car_rental   \n",
       "98  kitchen_and_dining         meal_suggestion   \n",
       "99  kitchen_and_dining                  recipe   \n",
       "\n",
       "                                                  raw  \\\n",
       "0             \"አውቶቢሱን መጠቀም አፈልጋለው\" በኦሮምኛ እንዴት ነው ምለው?   \n",
       "1   50000 ብር ከንግድ ባንክ አካውንቴ ወደሲቢኢ ብር ልታስተላልፍልኝ ትችላለህ?   \n",
       "2                                በአዲስ አበባ ስንት ሰአት ነው?   \n",
       "3                                 የአስቤዛ ዝርዝር ላይ ጨምርበት   \n",
       "4                      ቶቶት ክትፎ ቤት እራት ቦታ ልቲዝልኝ ትችላለህ?   \n",
       "..                                                ...   \n",
       "95  ngicela wengeze i-albhamu kaBritney Spears - i...   \n",
       "96  ngiyikhansela kanjani indawo yami engiyibhukil...   \n",
       "97       kubiza malini ukuqasha iHyundai i20 kwaCeza?   \n",
       "98     phakamisa indawo yokudlela ye-vegan eWestville   \n",
       "99                phakamisa iresiphi yacurry wemutton   \n",
       "\n",
       "                                                 text language  \\\n",
       "0   how do I say \"I want to use the bus\" in Oromo ...      amh   \n",
       "1   Can you transfer 50000 birr from my commercial...      amh   \n",
       "2                      what time is it in Addis Ababa      amh   \n",
       "3                     Add teff to the groceries list.      amh   \n",
       "4   Can you reserve a dinner place for me at Totot...      amh   \n",
       "..                                                ...      ...   \n",
       "95  please add Britney Spears - Baby One More Time...      zul   \n",
       "96  how do I cancel my reservation at The Little I...      zul   \n",
       "97  how much does it cost to rent a Hyundai i20 in...      zul   \n",
       "98            suggest a vegan restaurant in Westville      zul   \n",
       "99     suggest a recipe for lamb shanks with red wine      zul   \n",
       "\n",
       "                                                spans  \\\n",
       "0                              40:45:SL:LANGUAGE_NAME   \n",
       "1   17:27:SL:MONEY,36:51:SL:BANK_NAME,63:71:SL:PAY...   \n",
       "2                           19:30:SL:CITY_OR_PROVINCE   \n",
       "3                                 4:8:SL:DISH_OR_FOOD   \n",
       "4       18:24:SL:MEAL_PERIOD,41:58:SL:RESTAURANT_NAME   \n",
       "..                                                ...   \n",
       "95            11:25:SL:ARTIST_NAME,28:46:SL:SONG_NAME   \n",
       "96                           34:61:SL:RESTAURANT_NAME   \n",
       "97                                47:51:SL:PLACE_NAME   \n",
       "98                                30:39:SL:PLACE_NAME   \n",
       "99        21:32:SL:DISH_OR_FOOD,38:46:SL:DISH_OR_FOOD   \n",
       "\n",
       "                                         logical_form  \n",
       "0            [IN:translate [SL:LANGUAGE_NAME Oromo] ]  \n",
       "1   [IN:transfer [SL:MONEY 50000 birr] [SL:BANK_NA...  \n",
       "2        [IN:time [SL:CITY_OR_PROVINCE Addis Ababa] ]  \n",
       "3   [IN:shopping_list_update [SL:DISH_OR_FOOD teff] ]  \n",
       "4   [IN:restaurant_reservation [SL:MEAL_PERIOD din...  \n",
       "..                                                ...  \n",
       "95  [IN:update_playlist [SL:ARTIST_NAME Britney Sp...  \n",
       "96  [IN:cancel_reservation [SL:RESTAURANT_NAME The...  \n",
       "97              [IN:car_rental [SL:PLACE_NAME Ceza] ]  \n",
       "98    [IN:meal_suggestion [SL:PLACE_NAME Westville] ]  \n",
       "99  [IN:recipe [SL:DISH_OR_FOOD lamb shanks] [SL:D...  \n",
       "\n",
       "[1779 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing balance with {'sna', 'lug'}\n",
      "Missing confirm_reservation with {'sna', 'lug'}\n",
      "Missing freeze_account with {'sna', 'lug'}\n",
      "Missing restaurant_reservation with {'sna', 'lug'}\n",
      "Missing shopping_list_update with {'sna', 'lug'}\n",
      "Missing time with {'sna', 'lug'}\n",
      "Missing timezone with {'sna', 'lug'}\n",
      "Missing transfer with {'sna', 'lug'}\n",
      "Missing translate with {'sna', 'lug'}\n"
     ]
    }
   ],
   "source": [
    "slot_manager = data_loader.SlotDataManager(data_folder=\"data/output\")\n",
    "eng = slot_manager.load_data(\"eng\", \"split\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22.4\n",
      "2.325\n",
      "12.7\n"
     ]
    }
   ],
   "source": [
    "# eng.columns\n",
    "# Index(['domain', 'intent', 'raw', 'text', 'language', 'spans', 'logical_form',\n",
    "#        'xtreme-up', 'source_language'],\n",
    "#       dtype='object')\n",
    "for k in [\"train\", \"dev\", \"test\"]:\n",
    "    print(eng[k][['intent', 'spans']].groupby(\"intent\").count()[\"spans\"].sort_values(ascending=False).mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37.425"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eng[['intent', 'spans']].groupby(\"intent\").count()[\"spans\"].sort_values(ascending=False).mean()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "rag",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}