UNECE_Fasttext_Step1_Format_clean_splitdata.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The following European Classification of Individual Consumption according to Purpose (ECOICOP) data used for this example is open-source, provided by Statistics Poland\n",
    "\n",
    "#### Link: https://github.com/UNECE/ML_dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "pd.options.mode.chained_assignment = None  # default='warn'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define folder locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_directory = (\"C:\\\\Users\\\\Justin Evans\\\\Documents\\\\Python\\\\UNECE\\\\Poland_FastText\\\\\")\n",
    "data = pd.read_excel('https://raw.githubusercontent.com/UNECE/ML_dataset/master/Stats%20Poland%20ECOICOP%20data.xlsx', sheet_name = 'Data')\n",
    "\n",
    "# if using a local copy\n",
    "#data_directory = (\"C:\\\\Users\\\\Justin Evans\\\\Documents\\\\Python\\\\UNECE\\\\Poland_FastText\\\\\")\n",
    "#data = pd.read_excel(data_dir+\"Stats Poland ECOICOP data translated to English and French.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset size:  17099\n"
     ]
    },
    {
     "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>Desc_P</th>\n",
       "      <th>Code_P</th>\n",
       "      <th>Desc_E</th>\n",
       "      <th>Code_E</th>\n",
       "      <th>Desc_F</th>\n",
       "      <th>Code_F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>owsianka brzoskwiniowa</td>\n",
       "      <td>Pozostałe produkty mleczne</td>\n",
       "      <td>Peach Porridge</td>\n",
       "      <td>Other dairy products</td>\n",
       "      <td>Bouillie de pêche</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Owsianka Truskawkowa Mlekovita</td>\n",
       "      <td>Pozostałe produkty mleczne</td>\n",
       "      <td>Mlekovita Strawberry Porridge</td>\n",
       "      <td>Other dairy products</td>\n",
       "      <td>Bouillie de fraises Mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Owsianka wiśniowa</td>\n",
       "      <td>Pozostałe produkty mleczne</td>\n",
       "      <td>Cherry Porridge</td>\n",
       "      <td>Other dairy products</td>\n",
       "      <td>Bouillie de cerise</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Owsianka Truskawkowa Mlekovita</td>\n",
       "      <td>Pozostałe produkty mleczne</td>\n",
       "      <td>Mlekovita Strawberry Porridge</td>\n",
       "      <td>Other dairy products</td>\n",
       "      <td>Bouillie de fraises Mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sałatka z batatów</td>\n",
       "      <td>Pozostałe warzywa bulwiaste i przetwory z warz...</td>\n",
       "      <td>sweet potato salad</td>\n",
       "      <td>Other tuber vegetables and preparations of tub...</td>\n",
       "      <td>salade de patates douces</td>\n",
       "      <td>Autres légumes à légumes et préparations à bas...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Desc_P  \\\n",
       "0          owsianka brzoskwiniowa   \n",
       "1  Owsianka Truskawkowa Mlekovita   \n",
       "2               Owsianka wiśniowa   \n",
       "3  Owsianka Truskawkowa Mlekovita   \n",
       "4               sałatka z batatów   \n",
       "\n",
       "                                              Code_P  \\\n",
       "0                         Pozostałe produkty mleczne   \n",
       "1                         Pozostałe produkty mleczne   \n",
       "2                         Pozostałe produkty mleczne   \n",
       "3                         Pozostałe produkty mleczne   \n",
       "4  Pozostałe warzywa bulwiaste i przetwory z warz...   \n",
       "\n",
       "                          Desc_E  \\\n",
       "0                 Peach Porridge   \n",
       "1  Mlekovita Strawberry Porridge   \n",
       "2                Cherry Porridge   \n",
       "3  Mlekovita Strawberry Porridge   \n",
       "4             sweet potato salad   \n",
       "\n",
       "                                              Code_E  \\\n",
       "0                               Other dairy products   \n",
       "1                               Other dairy products   \n",
       "2                               Other dairy products   \n",
       "3                               Other dairy products   \n",
       "4  Other tuber vegetables and preparations of tub...   \n",
       "\n",
       "                          Desc_F  \\\n",
       "0              Bouillie de pêche   \n",
       "1  Bouillie de fraises Mlekovita   \n",
       "2             Bouillie de cerise   \n",
       "3  Bouillie de fraises Mlekovita   \n",
       "4       salade de patates douces   \n",
       "\n",
       "                                              Code_F  \n",
       "0                           Autres produits laitiers  \n",
       "1                           Autres produits laitiers  \n",
       "2                           Autres produits laitiers  \n",
       "3                           Autres produits laitiers  \n",
       "4  Autres légumes à légumes et préparations à bas...  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First we visualize the raw data\n",
    "print(\"Dataset size: \",data.shape[0])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocess the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>text</th>\n",
       "      <th>code_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bouillie de pêche</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bouillie de fraises Mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bouillie de cerise</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bouillie de fraises Mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>salade de patates douces</td>\n",
       "      <td>Autres légumes à légumes et préparations à bas...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            text  \\\n",
       "0              Bouillie de pêche   \n",
       "1  Bouillie de fraises Mlekovita   \n",
       "2             Bouillie de cerise   \n",
       "3  Bouillie de fraises Mlekovita   \n",
       "4       salade de patates douces   \n",
       "\n",
       "                                           code_text  \n",
       "0                           Autres produits laitiers  \n",
       "1                           Autres produits laitiers  \n",
       "2                           Autres produits laitiers  \n",
       "3                           Autres produits laitiers  \n",
       "4  Autres légumes à légumes et préparations à bas...  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# set the df - define our variables of interest\n",
    "df = data[['Desc_F','Code_F']]\n",
    "\n",
    "# rename columns for consistency \n",
    "df = df.rename(columns={'Desc_F': 'text', 'Code_F': 'code_text'})\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique codes:  61\n"
     ]
    },
    {
     "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>text</th>\n",
       "      <th>code_text</th>\n",
       "      <th>code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bouillie de pêche</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bouillie de fraises Mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bouillie de cerise</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bouillie de fraises Mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>salade de patates douces</td>\n",
       "      <td>Autres légumes à légumes et préparations à bas...</td>\n",
       "      <td>003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            text  \\\n",
       "0              Bouillie de pêche   \n",
       "1  Bouillie de fraises Mlekovita   \n",
       "2             Bouillie de cerise   \n",
       "3  Bouillie de fraises Mlekovita   \n",
       "4       salade de patates douces   \n",
       "\n",
       "                                           code_text code  \n",
       "0                           Autres produits laitiers  007  \n",
       "1                           Autres produits laitiers  007  \n",
       "2                           Autres produits laitiers  007  \n",
       "3                           Autres produits laitiers  007  \n",
       "4  Autres légumes à légumes et préparations à bas...  003  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a numerical key \n",
    "code = df[['code_text']]\n",
    "code_key = code.groupby(['code_text']).size().reset_index().\n",
    "print(\"Unique codes: \", code_key.shape[0])\n",
    "code_key['code'] = code_key.index + 1\n",
    "code_key['code'] = code_key['code'].astype(str).str.zfill(3)\n",
    "code_key.index = code_key.code_text\n",
    "key_dict = pd.Series(code_key.code.values, index=code_key.code_text).to_dict()\n",
    "\n",
    "# save numerical key to merge back onto predicted file\n",
    "with open(\"code_key.txt\", \"wb\") as file:\n",
    "    pickle.dump(key_dict, file)\n",
    "\n",
    "# merge numerical code onto original dataframe\n",
    "df['code'] = df.code_text.map(code_key.code)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "this is an example\n"
     ]
    }
   ],
   "source": [
    "# remove accents from french data\n",
    "import unicodedata\n",
    "\n",
    "def strip_accents(text):\n",
    "    text = unicodedata.normalize(\"NFD\",str(text)).encode(\"ascii\",\"ignore\").decode(\"utf-8\")\n",
    "    return str(text)\n",
    "\n",
    "print(strip_accents(\"this is àn èxamplé\"))\n",
    "\n",
    "# remove accents from text\n",
    "df['text'] = df.apply(lambda row : strip_accents(row[\"text\"]),axis = 1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>text</th>\n",
       "      <th>code_text</th>\n",
       "      <th>code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bouillie de peche</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bouillie de fraises mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bouillie de cerise</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bouillie de fraises mlekovita</td>\n",
       "      <td>Autres produits laitiers</td>\n",
       "      <td>007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>salade de patates douces</td>\n",
       "      <td>Autres légumes à légumes et préparations à bas...</td>\n",
       "      <td>003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            text  \\\n",
       "0              bouillie de peche   \n",
       "1  bouillie de fraises mlekovita   \n",
       "2             bouillie de cerise   \n",
       "3  bouillie de fraises mlekovita   \n",
       "4       salade de patates douces   \n",
       "\n",
       "                                           code_text code  \n",
       "0                           Autres produits laitiers  007  \n",
       "1                           Autres produits laitiers  007  \n",
       "2                           Autres produits laitiers  007  \n",
       "3                           Autres produits laitiers  007  \n",
       "4  Autres légumes à légumes et préparations à bas...  003  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# lowercase column\n",
    "df['text'] = df['text'].str.lower()\n",
    "    \n",
    "# remove blank lines\n",
    "import re\n",
    "def removeblanklines(text):\n",
    "    formatted_1 = re.sub(\"-\", \" \", text) # remove dashes\n",
    "    formatted_2 = re.sub(r'\\s{2}', \" \", formatted_1) # remove double spaces\n",
    "    return (formatted_2)\n",
    "\n",
    "df[\"text\"] = df.apply(lambda row: removeblanklines(row[\"text\"]), axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "vjku ku cp gzcorng\n"
     ]
    }
   ],
   "source": [
    "# a caesar cipher used to combine multiple variables\n",
    "\n",
    "def cipher(sentence,shift_value):\n",
    "    translated = \"\" #new empty string of soon to be transformed string\n",
    "    \n",
    "    if sentence == \"\":\n",
    "        return \"\"\n",
    "    \n",
    "    for char in sentence: \n",
    "        if char.isalpha():\n",
    "            ascii_num = ord(char) #get the ascii rep of the character\n",
    "            ascii_num += shift_value # add the shifted value\n",
    "            \n",
    "            #handle index rapping\n",
    "            if char.isupper():\n",
    "                if ascii_num > ord(\"Z\"):\n",
    "                    ascii_num -= 26\n",
    "                elif ascii_num < ord(\"A\"):\n",
    "                    ascii_num += 26\n",
    "            elif char.islower():\n",
    "                if ascii_num > ord(\"z\"):\n",
    "                    ascii_num -= 26\n",
    "                elif ascii_num < ord(\"a\"):\n",
    "                    ascii_num += 26\n",
    "            translated += chr(ascii_num)\n",
    "        else:\n",
    "            translated += char\n",
    "    return str(translated)\n",
    "\n",
    "print(cipher(\"this is an example\",2))\n",
    "\n",
    "# example below used to cipher and concatenate multiple variables\n",
    "\n",
    "#df[\"text\"] = df.apply(lambda row:  cipher(row[\"name_business\"],1) + \" \"  \n",
    "#                             +cipher(row[\"kind_business\"],2) +\" \"+ \n",
    "#                                cipher(row[\"kind_work\"],3) +\" \"+ cipher(row[\"main_activities\"],0),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                       __label__007 bouillie de peche\n",
       "1           __label__007 bouillie de fraises mlekovita\n",
       "2                      __label__007 bouillie de cerise\n",
       "3           __label__007 bouillie de fraises mlekovita\n",
       "4                __label__003 salade de patates douces\n",
       "5    __label__055 salade de pommes de terre aux bet...\n",
       "6    __label__055 salade de pommes de terre cuisine...\n",
       "7                __label__003 salade de patates douces\n",
       "8    __label__055 salade de pommes de terre aux bet...\n",
       "9    __label__055 salade de pommes de terre cuisine...\n",
       "Name: formatted, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# format the data so it is consumable by fasttext\n",
    "df[\"formatted\"] = df.apply(lambda row: str('__label__'+row['code']+' '+row['text']) ,axis=1)\n",
    "df[\"formatted\"].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Split the processed data into train and test datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13679\n",
      "3420\n"
     ]
    }
   ],
   "source": [
    "# create a training and testing dataset\n",
    "from sklearn.model_selection import train_test_split\n",
    "train, test = train_test_split(df, test_size=0.2, stratify=None) \n",
    "\n",
    "# create a set of txt files for ML, csv for analysis\n",
    "train_formatted = train[[\"formatted\"]]\n",
    "test_formatted = test[[\"formatted\"]]\n",
    "\n",
    "print(\"Train Dataset Size: \", train.shape[0])\n",
    "print(\"Test Dataset Size: \", test.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export preprocessed files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export training and testing sets\n",
    "np.savetxt(output_directory+\"train.txt\",train_formatted.values,fmt=\"%s\") # fasttext will read this txt file\n",
    "test.to_csv(output_directory+\"test.csv\") # used for analysis in step 4\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done!\n"
     ]
    }
   ],
   "source": [
    "print(\"done!\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}