make-phase-one-product-list.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Making Phase One Product List\n",
    "\n",
    "The first step reads in the excel file. What I did was take the offical document and then with Adobe, extracted Annex 6-1 which has the product lists from which purchases should be made. Then with the extracted .pdf file, I converted it to an excel file. Now some simple cleaning needs to be done. The end product is something where we have an (i) and hs code, (ii) description and (iii) a low catagory and (iv) a high catagory. The high catagory is the catagory for which purchase commitments are made. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = \"./data/annex-6-1.xlsx\"\n",
    "\n",
    "df = pd.read_excel(file_path, skiprows = 1, header = None, dtype = {1: str})\n",
    "\n",
    "df.columns = [\"catagory\", \"hs4_o\", \"description\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Some basic cleaning.\n",
    "\n",
    "The first step steps are to create the low and high product catagories and seperate them into their own column. Then the remaing steps is to drop headings etc. The only issue is that some HS codes are messed up (i.e. ones are viewed as Ls) and two other issues. I manually replace these. Then we are done."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.catagory.ffill(inplace = True)\n",
    "# This will take the catagory and push down, giving rise to the numbers, etc.\n",
    "\n",
    "df[\"low_catagory\"] = df[df.description.isnull()].hs4_o\n",
    "\n",
    "df[\"high_catagory\"] = df[df.description.isnull() & df.hs4_o.isnull()].catagory\n",
    "\n",
    "df[\"low_catagory\"].ffill(inplace = True)\n",
    "\n",
    "df[\"high_catagory\"].ffill(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis = 0, subset = [\"hs4_o\"], inplace = True)\n",
    "\n",
    "df.dropna(axis = 0, subset = [\"description\"], inplace = True)\n",
    "\n",
    "replace_list = ['85ll', '72ll', '220 1', '290543/\\n290544', \"290151\"]\n",
    "\n",
    "df.replace(to_replace = replace_list, value = [\"8511\", \"7211\", \"2201\", \"2905\", \"290511\"], inplace = True)\n",
    "\n",
    "df[\"foo\"] = df.hs4_o.apply(pd.to_numeric, args=('coerce',))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dropna(axis = 0, subset = [\"foo\"], inplace = True)\n",
    "\n",
    "df.drop(axis = 1, labels = [\"foo\", \"catagory\"], inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The final issue is somehow the \"0\" infront of the hs4 codes are not being read in. In excel they display, but not read in. This will fix these entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>hs4_o</th>\n",
       "      <th>description</th>\n",
       "      <th>low_catagory</th>\n",
       "      <th>high_catagory</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>2709</td>\n",
       "      <td>Petroleum oils and oilsobtained from bituminou...</td>\n",
       "      <td>Crude oil</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>271112</td>\n",
       "      <td>Liquefied propane</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>271113</td>\n",
       "      <td>Liquefied butane</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>27111990</td>\n",
       "      <td>Other unlisted liquefied petroleum gases and g...</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>271311</td>\n",
       "      <td>U n ca lc ined   petroleum coke</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>271312</td>\n",
       "      <td>Calcined petroleum coke</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>271012250</td>\n",
       "      <td>Naphtha (Excluding Motor Fue,l)  blend Stock n...</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>572</th>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575</th>\n",
       "      <td>2701</td>\n",
       "      <td>Coal; briquett,es  ovoids and similar solid fu...</td>\n",
       "      <td>Coal</td>\n",
       "      <td>3. Energy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         hs4_o                                        description  \\\n",
       "560     271111                              Liquefied natural gas   \n",
       "563       2709  Petroleum oils and oilsobtained from bituminou...   \n",
       "566     271112                                  Liquefied propane   \n",
       "567     271113                                   Liquefied butane   \n",
       "568   27111990  Other unlisted liquefied petroleum gases and g...   \n",
       "569     271311                    U n ca lc ined   petroleum coke   \n",
       "570     271312                            Calcined petroleum coke   \n",
       "571  271012250  Naphtha (Excluding Motor Fue,l)  blend Stock n...   \n",
       "572     290511                                          Me thanol   \n",
       "575       2701  Coal; briquett,es  ovoids and similar solid fu...   \n",
       "\n",
       "              low_catagory high_catagory  \n",
       "560  Liquefied natural gas     3. Energy  \n",
       "563              Crude oil     3. Energy  \n",
       "566       Refined products     3. Energy  \n",
       "567       Refined products     3. Energy  \n",
       "568       Refined products     3. Energy  \n",
       "569       Refined products     3. Energy  \n",
       "570       Refined products     3. Energy  \n",
       "571       Refined products     3. Energy  \n",
       "572       Refined products     3. Energy  \n",
       "575                   Coal     3. Energy  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.high_catagory == \"3. Energy\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_zero(x):\n",
    "    \n",
    "    if len(x) < 4:\n",
    "        x = \"0\" + x\n",
    "        \n",
    "    if len(x) > 4:\n",
    "        # Some codes are above hs (almost all are energy),\n",
    "        # now I have a fix for energy\n",
    "        x = x[0:4]\n",
    "        \n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "is_energy = df.high_catagory == \"3. Energy\"\n",
    "\n",
    "df.loc[~is_energy, \"hs4_o\"] = df.loc[~is_energy, \"hs4_o\"].apply(add_zero)\n",
    "# This fixes the zero problem on all the codes but energy which does not\n",
    "# have a zero problem\n",
    "\n",
    "df[\"hs4\"] = df.hs4_o.apply(add_zero)\n",
    "# then this creates the hs4 codes that mimic the original setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "560    2711\n",
       "563    2709\n",
       "566    2711\n",
       "567    2711\n",
       "568    2711\n",
       "569    2713\n",
       "570    2713\n",
       "571    2710\n",
       "572    2905\n",
       "575    2701\n",
       "Name: hs4, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[is_energy, \"hs4\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### And we are done\n",
    "\n",
    "Check out the end product. **To Do** make a .csv file for posting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "grp = df.groupby(\"hs4_o\")\n",
    "\n",
    "outdf = grp.agg({\"description\": \"first\", \"low_catagory\": \"first\", \"high_catagory\": \"first\", \"hs4\": \"first\"})\n",
    "# Given the truncation, this will collapse things so that there is a unique hs4 code\n",
    "# the descrition will be off, but low and high catagory will be right.\n",
    "\n",
    "outdf.reset_index(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>hs4_o</th>\n",
       "      <th>description</th>\n",
       "      <th>low_catagory</th>\n",
       "      <th>high_catagory</th>\n",
       "      <th>hs4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>2701</td>\n",
       "      <td>Coal; briquett,es  ovoids and similar solid fu...</td>\n",
       "      <td>Coal</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>2709</td>\n",
       "      <td>Petroleum oils and oilsobtained from bituminou...</td>\n",
       "      <td>Crude oil</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>271012250</td>\n",
       "      <td>Naphtha (Excluding Motor Fue,l)  blend Stock n...</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>271111</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>Liquefied natural gas</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>271112</td>\n",
       "      <td>Liquefied propane</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>271113</td>\n",
       "      <td>Liquefied butane</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>27111990</td>\n",
       "      <td>Other unlisted liquefied petroleum gases and g...</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>271311</td>\n",
       "      <td>U n ca lc ined   petroleum coke</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>271312</td>\n",
       "      <td>Calcined petroleum coke</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>290511</td>\n",
       "      <td>Me thanol</td>\n",
       "      <td>Refined products</td>\n",
       "      <td>3. Energy</td>\n",
       "      <td>2905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         hs4_o                                        description  \\\n",
       "194       2701  Coal; briquett,es  ovoids and similar solid fu...   \n",
       "195       2709  Petroleum oils and oilsobtained from bituminou...   \n",
       "196  271012250  Naphtha (Excluding Motor Fue,l)  blend Stock n...   \n",
       "197     271111                              Liquefied natural gas   \n",
       "198     271112                                  Liquefied propane   \n",
       "199     271113                                   Liquefied butane   \n",
       "200   27111990  Other unlisted liquefied petroleum gases and g...   \n",
       "201     271311                    U n ca lc ined   petroleum coke   \n",
       "202     271312                            Calcined petroleum coke   \n",
       "259     290511                                          Me thanol   \n",
       "\n",
       "              low_catagory high_catagory   hs4  \n",
       "194                   Coal     3. Energy  2701  \n",
       "195              Crude oil     3. Energy  2709  \n",
       "196       Refined products     3. Energy  2710  \n",
       "197  Liquefied natural gas     3. Energy  2711  \n",
       "198       Refined products     3. Energy  2711  \n",
       "199       Refined products     3. Energy  2711  \n",
       "200       Refined products     3. Energy  2711  \n",
       "201       Refined products     3. Energy  2713  \n",
       "202       Refined products     3. Energy  2713  \n",
       "259       Refined products     3. Energy  2905  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outdf[outdf.high_catagory == \"3. Energy\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pyarrow\\pandas_compat.py:383: FutureWarning: RangeIndex._start is deprecated and will be removed in a future version. Use RangeIndex.start instead\n",
      "  'start': level._start,\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pyarrow\\pandas_compat.py:384: FutureWarning: RangeIndex._stop is deprecated and will be removed in a future version. Use RangeIndex.stop instead\n",
      "  'stop': level._stop,\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pyarrow\\pandas_compat.py:385: FutureWarning: RangeIndex._step is deprecated and will be removed in a future version. Use RangeIndex.step instead\n",
      "  'step': level._step\n"
     ]
    }
   ],
   "source": [
    "out_file = \".\\\\data\"+ \"\\\\annex-6-1.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(outdf), out_file)\n",
    "\n",
    "outdf.to_csv(\".\\\\data\"+ \"\\\\annex-6-1.csv\",index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'foo' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-21-e398ff824abe>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mfoo\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtail\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m20\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'foo' is not defined"
     ]
    }
   ],
   "source": [
    "foo.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 4
}