2021_EDA_Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from plotnine import ggplot, aes, geom_density, geom_line, geom_point, ggtitle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Here, I would be combining 2021 quarters data to do a comparison with other years (2011-2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Ex_bysection = pd.read_excel('Data/FT 2021 Q2.xlsx', sheet_name='1.2')\n",
    "Q2_Ex_bysection.to_csv('Cleaned_data/FT_2021_Q2_1-2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Im_bysection = pd.read_excel('Data/FT 2021 Q2.xlsx', sheet_name='2.1')\n",
    "Q2_Im_bysection.to_csv('Cleaned_data/FT_2021_Q2_2-1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Im_bycountry = pd.read_excel('Data/FT 2021 Q2.xlsx', sheet_name='2.3')\n",
    "Q2_Im_bycountry.to_csv('Cleaned_data/FT_2021_Q2_2-3.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Ex_bysection = pd.read_excel('Data/ITR Q42021A.xlsx', sheet_name='1.1')\n",
    "Q4_Ex_bysection.to_csv('Cleaned_data/ITR Q42021A_1-2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bysection = pd.read_excel('Data/ITR Q42021A.xlsx', sheet_name='2.1')\n",
    "Q4_Im_bysection.to_csv('Cleaned_data/ITR Q42021A_2-1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bycountry = pd.read_excel('Data/ITR Q42021A.xlsx', sheet_name='2.3')\n",
    "Q4_Im_bycountry.to_csv('Cleaned_data/ITR Q42021A_2-3.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q2 sheet 1.2, Exports by Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>الفهرس / Index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>الصادرات حسب الاقسام</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Exports by Section</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>رقم القسم</td>\n",
       "      <td>وصف القسم</td>\n",
       "      <td>الربع الثاني / Q2</td>\n",
       "      <td>الربع الأول / Q1</td>\n",
       "      <td>الربع الثاني / Q2</td>\n",
       "      <td>Section Description</td>\n",
       "      <td>Sec.\\nNo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>القيمة (مليون ريال) / (Million Riyals)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>الحيوانات الحية والمنتجات الحيوانية</td>\n",
       "      <td>1306.95934</td>\n",
       "      <td>1330.587771</td>\n",
       "      <td>1246.109388</td>\n",
       "      <td>Live animals; animal products</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>منتجات نباتية</td>\n",
       "      <td>331.183015</td>\n",
       "      <td>730.502396</td>\n",
       "      <td>314.094411</td>\n",
       "      <td>Vegetable products</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...</td>\n",
       "      <td>198.899452</td>\n",
       "      <td>321.665141</td>\n",
       "      <td>370.525448</td>\n",
       "      <td>Animal or vegetable fats and oils and their cl...</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...</td>\n",
       "      <td>1324.093697</td>\n",
       "      <td>1696.196372</td>\n",
       "      <td>1528.11005</td>\n",
       "      <td>Prepared foodstuffs; beverages, spirits and vi...</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Unnamed: 0                                         Unnamed: 1  \\\n",
       "0                   NaN                                                NaN   \n",
       "1  الصادرات حسب الاقسام                                                NaN   \n",
       "2    Exports by Section                                                NaN   \n",
       "3             رقم القسم                                          وصف القسم   \n",
       "4                   NaN                                                NaN   \n",
       "5                   NaN                                                NaN   \n",
       "6                     1                الحيوانات الحية والمنتجات الحيوانية   \n",
       "7                     2                                      منتجات نباتية   \n",
       "8                     3  شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...   \n",
       "9                     4  منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...   \n",
       "\n",
       "                               Unnamed: 2        Unnamed: 3  \\\n",
       "0                                     NaN               NaN   \n",
       "1                                     NaN               NaN   \n",
       "2                                     NaN               NaN   \n",
       "3                       الربع الثاني / Q2  الربع الأول / Q1   \n",
       "4                                    2020              2021   \n",
       "5  القيمة (مليون ريال) / (Million Riyals)               NaN   \n",
       "6                              1306.95934       1330.587771   \n",
       "7                              331.183015        730.502396   \n",
       "8                              198.899452        321.665141   \n",
       "9                             1324.093697       1696.196372   \n",
       "\n",
       "          Unnamed: 4                                         Unnamed: 5  \\\n",
       "0                NaN                                                NaN   \n",
       "1                NaN                                                NaN   \n",
       "2                NaN                                                NaN   \n",
       "3  الربع الثاني / Q2                                Section Description   \n",
       "4               2021                                                NaN   \n",
       "5                NaN                                                NaN   \n",
       "6        1246.109388                      Live animals; animal products   \n",
       "7         314.094411                                 Vegetable products   \n",
       "8         370.525448  Animal or vegetable fats and oils and their cl...   \n",
       "9         1528.11005  Prepared foodstuffs; beverages, spirits and vi...   \n",
       "\n",
       "  Unnamed: 6  Unnamed: 7  الفهرس / Index  \n",
       "0        NaN         NaN             NaN  \n",
       "1        NaN         NaN             NaN  \n",
       "2        NaN         NaN             NaN  \n",
       "3   Sec.\\nNo         NaN             NaN  \n",
       "4        NaN         NaN             NaN  \n",
       "5        NaN         NaN             NaN  \n",
       "6          1         NaN             NaN  \n",
       "7          2         NaN             NaN  \n",
       "8          3         NaN             NaN  \n",
       "9          4         NaN             NaN  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2_Ex_bysection.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Ex_bysection.drop(['Unnamed: 0', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 7', 'الفهرس / Index'], axis=1, inplace=True)\n",
    "Q2_Ex_bysection.drop([0,1,2,3,4,5], axis=0, inplace=True)\n",
    "Q2_Ex_bysection.rename(columns={'Unnamed: 3': '2021_Q1_mSAR', 'Unnamed: 4': '2021_Q2_mSAR', 'Unnamed: 5': 'Section Description',\n",
    "'Unnamed: 6': 'Section_Num'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Ex_bysection.to_csv('Cleaned_data/FT_2021_Q2_1-2_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q2 sheet 2.1, Imports by Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>الفهرس / Index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>الواردات حسب الاقسام</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Imports by Section</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>رقم القسم</td>\n",
       "      <td>وصف القسم</td>\n",
       "      <td>الربع الثاني / Q2</td>\n",
       "      <td>الربع الأول / Q1</td>\n",
       "      <td>الربع الثاني / Q2</td>\n",
       "      <td>Section Description</td>\n",
       "      <td>Sec.\\nNo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>القيمة (مليون ريال) / (Million Riyals)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>الحيوانات الحية والمنتجات الحيوانية</td>\n",
       "      <td>4832.169671</td>\n",
       "      <td>5124.105264</td>\n",
       "      <td>4644.582062</td>\n",
       "      <td>Live animals; animal products</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>منتجات نباتية</td>\n",
       "      <td>8994.589992</td>\n",
       "      <td>8425.51836</td>\n",
       "      <td>8412.106256</td>\n",
       "      <td>Vegetable products</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...</td>\n",
       "      <td>737.920117</td>\n",
       "      <td>1052.075508</td>\n",
       "      <td>1014.701295</td>\n",
       "      <td>Animal or vegetable fats and oils and their cl...</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...</td>\n",
       "      <td>7107.622813</td>\n",
       "      <td>7074.709286</td>\n",
       "      <td>7146.520745</td>\n",
       "      <td>Prepared foodstuffs; beverages, spirits and vi...</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Unnamed: 0                                         Unnamed: 1  \\\n",
       "0                   NaN                                                NaN   \n",
       "1  الواردات حسب الاقسام                                                NaN   \n",
       "2    Imports by Section                                                NaN   \n",
       "3             رقم القسم                                          وصف القسم   \n",
       "4                   NaN                                                NaN   \n",
       "5                   NaN                                                NaN   \n",
       "6                     1                الحيوانات الحية والمنتجات الحيوانية   \n",
       "7                     2                                      منتجات نباتية   \n",
       "8                     3  شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...   \n",
       "9                     4  منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...   \n",
       "\n",
       "                               Unnamed: 2        Unnamed: 3  \\\n",
       "0                                     NaN               NaN   \n",
       "1                                     NaN               NaN   \n",
       "2                                     NaN               NaN   \n",
       "3                       الربع الثاني / Q2  الربع الأول / Q1   \n",
       "4                                    2020              2021   \n",
       "5  القيمة (مليون ريال) / (Million Riyals)               NaN   \n",
       "6                             4832.169671       5124.105264   \n",
       "7                             8994.589992        8425.51836   \n",
       "8                              737.920117       1052.075508   \n",
       "9                             7107.622813       7074.709286   \n",
       "\n",
       "          Unnamed: 4                                         Unnamed: 5  \\\n",
       "0                NaN                                                NaN   \n",
       "1                NaN                                                NaN   \n",
       "2                NaN                                                NaN   \n",
       "3  الربع الثاني / Q2                                Section Description   \n",
       "4               2021                                                NaN   \n",
       "5                NaN                                                NaN   \n",
       "6        4644.582062                      Live animals; animal products   \n",
       "7        8412.106256                                 Vegetable products   \n",
       "8        1014.701295  Animal or vegetable fats and oils and their cl...   \n",
       "9        7146.520745  Prepared foodstuffs; beverages, spirits and vi...   \n",
       "\n",
       "  Unnamed: 6  Unnamed: 7  الفهرس / Index  \n",
       "0        NaN         NaN             NaN  \n",
       "1        NaN         NaN             NaN  \n",
       "2        NaN         NaN             NaN  \n",
       "3   Sec.\\nNo         NaN             NaN  \n",
       "4        NaN         NaN             NaN  \n",
       "5        NaN         NaN             NaN  \n",
       "6          1         NaN             NaN  \n",
       "7          2         NaN             NaN  \n",
       "8          3         NaN             NaN  \n",
       "9          4         NaN             NaN  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2_Im_bysection.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Im_bysection.drop(['Unnamed: 0', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 7', 'الفهرس / Index'], axis=1, inplace=True)\n",
    "Q2_Im_bysection.drop([0,1,2,3,4,5], axis=0, inplace=True)\n",
    "Q2_Im_bysection.rename(columns={'Unnamed: 3': '2021_Q1_mSAR', 'Unnamed: 4': '2021_Q2_mSAR', 'Unnamed: 5': 'Section Description',\n",
    "'Unnamed: 6': 'Section_Num'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>2021_Q1_mSAR</th>\n",
       "      <th>2021_Q2_mSAR</th>\n",
       "      <th>Section Description</th>\n",
       "      <th>Section_Num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5124.105264</td>\n",
       "      <td>4644.582062</td>\n",
       "      <td>Live animals; animal products</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8425.51836</td>\n",
       "      <td>8412.106256</td>\n",
       "      <td>Vegetable products</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1052.075508</td>\n",
       "      <td>1014.701295</td>\n",
       "      <td>Animal or vegetable fats and oils and their cl...</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7074.709286</td>\n",
       "      <td>7146.520745</td>\n",
       "      <td>Prepared foodstuffs; beverages, spirits and vi...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4123.354467</td>\n",
       "      <td>7310.717963</td>\n",
       "      <td>Mineral products</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>13903.884165</td>\n",
       "      <td>15887.925115</td>\n",
       "      <td>Products of the chemical or allied industries</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5050.023616</td>\n",
       "      <td>5604.43242</td>\n",
       "      <td>Plastics and articles thereof; rubber and arti...</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>466.308885</td>\n",
       "      <td>408.905142</td>\n",
       "      <td>Raw hides and skins, leather, furskins and art...</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1174.583182</td>\n",
       "      <td>1410.686669</td>\n",
       "      <td>Wood and articles of wood; wood charcoal; cork...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1452.55486</td>\n",
       "      <td>1747.468422</td>\n",
       "      <td>Pulp of wood or of other fibrous cellulosic ma...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5505.859544</td>\n",
       "      <td>4910.724366</td>\n",
       "      <td>Textiles and textile articles</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1103.814127</td>\n",
       "      <td>906.115958</td>\n",
       "      <td>Footwear, headgear, umbrellas, sun umbrellas, ...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2063.730818</td>\n",
       "      <td>1764.369658</td>\n",
       "      <td>Articles of stone, plaster, cement, asbestos, ...</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5375.119551</td>\n",
       "      <td>5072.590651</td>\n",
       "      <td>Natural or cultured pearls, precious or semi-p...</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>11657.990332</td>\n",
       "      <td>12541.020653</td>\n",
       "      <td>Base metals and articles of base metal</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>27893.963758</td>\n",
       "      <td>26582.811135</td>\n",
       "      <td>Machinery and mechanical appliances; electrica...</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22649.439999</td>\n",
       "      <td>24740.171216</td>\n",
       "      <td>Vehicles, aircraft, vessels and associated tra...</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>4127.212287</td>\n",
       "      <td>3807.063015</td>\n",
       "      <td>Optical, photographic, cinematographic, measur...</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>6003.02639</td>\n",
       "      <td>1219.652885</td>\n",
       "      <td>Arms and ammunition; parts and accessories the...</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>3591.651804</td>\n",
       "      <td>3383.210658</td>\n",
       "      <td>Miscellaneous manufactured articles</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1683.282102</td>\n",
       "      <td>2428.455551</td>\n",
       "      <td>Works of art, collectors' pieces and antiques</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>139502.208305</td>\n",
       "      <td>140944.231835</td>\n",
       "      <td>Total</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     2021_Q1_mSAR   2021_Q2_mSAR  \\\n",
       "6     5124.105264    4644.582062   \n",
       "7      8425.51836    8412.106256   \n",
       "8     1052.075508    1014.701295   \n",
       "9     7074.709286    7146.520745   \n",
       "10    4123.354467    7310.717963   \n",
       "11   13903.884165   15887.925115   \n",
       "12    5050.023616     5604.43242   \n",
       "13     466.308885     408.905142   \n",
       "14    1174.583182    1410.686669   \n",
       "15     1452.55486    1747.468422   \n",
       "16    5505.859544    4910.724366   \n",
       "17    1103.814127     906.115958   \n",
       "18    2063.730818    1764.369658   \n",
       "19    5375.119551    5072.590651   \n",
       "20   11657.990332   12541.020653   \n",
       "21   27893.963758   26582.811135   \n",
       "22   22649.439999   24740.171216   \n",
       "23    4127.212287    3807.063015   \n",
       "24     6003.02639    1219.652885   \n",
       "25    3591.651804    3383.210658   \n",
       "26    1683.282102    2428.455551   \n",
       "27  139502.208305  140944.231835   \n",
       "\n",
       "                                  Section Description Section_Num  \n",
       "6                       Live animals; animal products           1  \n",
       "7                                  Vegetable products           2  \n",
       "8   Animal or vegetable fats and oils and their cl...           3  \n",
       "9   Prepared foodstuffs; beverages, spirits and vi...           4  \n",
       "10                                   Mineral products           5  \n",
       "11      Products of the chemical or allied industries           6  \n",
       "12  Plastics and articles thereof; rubber and arti...           7  \n",
       "13  Raw hides and skins, leather, furskins and art...           8  \n",
       "14  Wood and articles of wood; wood charcoal; cork...           9  \n",
       "15  Pulp of wood or of other fibrous cellulosic ma...          10  \n",
       "16                      Textiles and textile articles          11  \n",
       "17  Footwear, headgear, umbrellas, sun umbrellas, ...          12  \n",
       "18  Articles of stone, plaster, cement, asbestos, ...          13  \n",
       "19  Natural or cultured pearls, precious or semi-p...          14  \n",
       "20             Base metals and articles of base metal          15  \n",
       "21  Machinery and mechanical appliances; electrica...          16  \n",
       "22  Vehicles, aircraft, vessels and associated tra...          17  \n",
       "23  Optical, photographic, cinematographic, measur...          18  \n",
       "24  Arms and ammunition; parts and accessories the...          19  \n",
       "25                Miscellaneous manufactured articles          20  \n",
       "26      Works of art, collectors' pieces and antiques          21  \n",
       "27                                              Total         NaN  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Im_bysection.to_csv('Cleaned_data/FT_2021_Q2_2-1_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q2 sheet 2.3, Imports by Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>الفهرس / Index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>الواردات حسب الدول</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Imports by Country</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>مرتبة</td>\n",
       "      <td>الدول</td>\n",
       "      <td>الربع الثاني / Q2</td>\n",
       "      <td>الربع الأول / Q1</td>\n",
       "      <td>الربع الثاني / Q2</td>\n",
       "      <td>Country</td>\n",
       "      <td>Rank</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>القيمة (مليون ريال) / (Million Riyals)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>الصـين</td>\n",
       "      <td>27762.571408</td>\n",
       "      <td>28672.17113</td>\n",
       "      <td>25679.954387</td>\n",
       "      <td>CHINA</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>الامارات العربية المتحدة</td>\n",
       "      <td>7072.644815</td>\n",
       "      <td>12586.885145</td>\n",
       "      <td>13482.467715</td>\n",
       "      <td>UNITED ARAB EMIRATES</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>الولايات المتحدة الامريكية</td>\n",
       "      <td>13618.785107</td>\n",
       "      <td>16078.199931</td>\n",
       "      <td>13099.942399</td>\n",
       "      <td>U.S.A</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>المانيا</td>\n",
       "      <td>6877.631138</td>\n",
       "      <td>6621.06716</td>\n",
       "      <td>7448.297571</td>\n",
       "      <td>GERMANY</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Unnamed: 0                  Unnamed: 1  \\\n",
       "0                 NaN                         NaN   \n",
       "1  الواردات حسب الدول                         NaN   \n",
       "2  Imports by Country                         NaN   \n",
       "3               مرتبة                       الدول   \n",
       "4                 NaN                         NaN   \n",
       "5                 NaN                         NaN   \n",
       "6                   1                      الصـين   \n",
       "7                   2    الامارات العربية المتحدة   \n",
       "8                   3  الولايات المتحدة الامريكية   \n",
       "9                   4                     المانيا   \n",
       "\n",
       "                               Unnamed: 2        Unnamed: 3  \\\n",
       "0                                     NaN               NaN   \n",
       "1                                     NaN               NaN   \n",
       "2                                     NaN               NaN   \n",
       "3                       الربع الثاني / Q2  الربع الأول / Q1   \n",
       "4                                    2020              2021   \n",
       "5  القيمة (مليون ريال) / (Million Riyals)               NaN   \n",
       "6                            27762.571408       28672.17113   \n",
       "7                             7072.644815      12586.885145   \n",
       "8                            13618.785107      16078.199931   \n",
       "9                             6877.631138        6621.06716   \n",
       "\n",
       "          Unnamed: 4            Unnamed: 5 Unnamed: 6  Unnamed: 7  \\\n",
       "0                NaN                   NaN        NaN         NaN   \n",
       "1                NaN                   NaN        NaN         NaN   \n",
       "2                NaN                   NaN        NaN         NaN   \n",
       "3  الربع الثاني / Q2               Country       Rank         NaN   \n",
       "4               2021                   NaN        NaN         NaN   \n",
       "5                NaN                   NaN        NaN         NaN   \n",
       "6       25679.954387                 CHINA          1         NaN   \n",
       "7       13482.467715  UNITED ARAB EMIRATES          2         NaN   \n",
       "8       13099.942399                 U.S.A          3         NaN   \n",
       "9        7448.297571               GERMANY          4         NaN   \n",
       "\n",
       "   الفهرس / Index  \n",
       "0             NaN  \n",
       "1             NaN  \n",
       "2             NaN  \n",
       "3             NaN  \n",
       "4             NaN  \n",
       "5             NaN  \n",
       "6             NaN  \n",
       "7             NaN  \n",
       "8             NaN  \n",
       "9             NaN  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2_Im_bycountry.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Im_bycountry.drop(['Unnamed: 0', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 7', 'الفهرس / Index', 'Unnamed: 6'], axis=1, inplace=True)\n",
    "Q2_Im_bycountry.drop([0,1,2,3,4,5], axis=0, inplace=True)\n",
    "Q2_Im_bycountry.rename(columns={'Unnamed: 3': '2021_Q1_mSAR', 'Unnamed: 4': '2021_Q2_mSAR', 'Unnamed: 5': 'Country'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>2021_Q1_mSAR</th>\n",
       "      <th>2021_Q2_mSAR</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>28672.17113</td>\n",
       "      <td>25679.954387</td>\n",
       "      <td>CHINA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12586.885145</td>\n",
       "      <td>13482.467715</td>\n",
       "      <td>UNITED ARAB EMIRATES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>16078.199931</td>\n",
       "      <td>13099.942399</td>\n",
       "      <td>U.S.A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6621.06716</td>\n",
       "      <td>7448.297571</td>\n",
       "      <td>GERMANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7501.885938</td>\n",
       "      <td>7185.96151</td>\n",
       "      <td>INDIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>0.191555</td>\n",
       "      <td>0.329661</td>\n",
       "      <td>CURACAO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>0.039809</td>\n",
       "      <td>0.300528</td>\n",
       "      <td>LIBYA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>0.050014</td>\n",
       "      <td>0.230318</td>\n",
       "      <td>BRUNEI DARUSSALAM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>107.592465</td>\n",
       "      <td>7.647941</td>\n",
       "      <td>OTHER COUNTRIES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>139502.208305</td>\n",
       "      <td>140944.231835</td>\n",
       "      <td>TOTAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>151 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      2021_Q1_mSAR   2021_Q2_mSAR               Country\n",
       "6      28672.17113   25679.954387                 CHINA\n",
       "7     12586.885145   13482.467715  UNITED ARAB EMIRATES\n",
       "8     16078.199931   13099.942399                 U.S.A\n",
       "9       6621.06716    7448.297571               GERMANY\n",
       "10     7501.885938     7185.96151                 INDIA\n",
       "..             ...            ...                   ...\n",
       "152       0.191555       0.329661               CURACAO\n",
       "153       0.039809       0.300528                 LIBYA\n",
       "154       0.050014       0.230318     BRUNEI DARUSSALAM\n",
       "155     107.592465       7.647941       OTHER COUNTRIES\n",
       "156  139502.208305  140944.231835                 TOTAL\n",
       "\n",
       "[151 rows x 3 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2_Im_bycountry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>2021_Q1_mSAR</th>\n",
       "      <th>2021_Q2_mSAR</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>-</td>\n",
       "      <td>2.303105</td>\n",
       "      <td>REPUBLIC OF KOSOVO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>-</td>\n",
       "      <td>0.871246</td>\n",
       "      <td>SURINAME</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    2021_Q1_mSAR 2021_Q2_mSAR             Country\n",
       "126            -     2.303105  REPUBLIC OF KOSOVO\n",
       "140            -     0.871246            SURINAME"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2_Im_bycountry[Q2_Im_bycountry['2021_Q1_mSAR'].isin(['-'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6        28672.17113\n",
       "7       12586.885145\n",
       "8       16078.199931\n",
       "9         6621.06716\n",
       "10       7501.885938\n",
       "           ...      \n",
       "152         0.191555\n",
       "153         0.039809\n",
       "154         0.050014\n",
       "155       107.592465\n",
       "156    139502.208305\n",
       "Name: 2021_Q1_mSAR, Length: 151, dtype: object"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q2_Im_bycountry['2021_Q1_mSAR'].replace({'-': '1.5'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q2_Im_bycountry.to_csv('Cleaned_data/FT_2021_Q2_2-3_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q4 sheet 1.1, Exports by Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>الفهرس</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>الصادرات حسب الاقسام</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>رقم القسم</td>\n",
       "      <td>وصف القسم</td>\n",
       "      <td>الربع الرابع</td>\n",
       "      <td>الربع الثالث</td>\n",
       "      <td>الربع الرابع</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>القيمة (مليون ريال)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>الحيوانات الحية والمنتجات الحيوانية</td>\n",
       "      <td>1305.255429</td>\n",
       "      <td>1261.434116</td>\n",
       "      <td>1533.349188</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>منتجات نباتية</td>\n",
       "      <td>435.302366</td>\n",
       "      <td>299.184048</td>\n",
       "      <td>531.968384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...</td>\n",
       "      <td>212.914862</td>\n",
       "      <td>340.523261</td>\n",
       "      <td>415.289859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...</td>\n",
       "      <td>1498.496898</td>\n",
       "      <td>1742.577819</td>\n",
       "      <td>1937.558877</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>المنتجات المعدنية</td>\n",
       "      <td>122612.141296</td>\n",
       "      <td>207865.871133</td>\n",
       "      <td>241827.571306</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Unnamed: 0                                         Unnamed: 1  \\\n",
       "0                   NaN                                                NaN   \n",
       "1  الصادرات حسب الاقسام                                                NaN   \n",
       "2             رقم القسم                                          وصف القسم   \n",
       "3                   NaN                                                NaN   \n",
       "4                   NaN                                                NaN   \n",
       "5                     1                الحيوانات الحية والمنتجات الحيوانية   \n",
       "6                     2                                      منتجات نباتية   \n",
       "7                     3  شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...   \n",
       "8                     4  منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...   \n",
       "9                     5                                  المنتجات المعدنية   \n",
       "\n",
       "            Unnamed: 2     Unnamed: 3     Unnamed: 4  Unnamed: 5  الفهرس  \n",
       "0                  NaN            NaN            NaN         NaN     NaN  \n",
       "1                  NaN            NaN            NaN         NaN     NaN  \n",
       "2         الربع الرابع   الربع الثالث   الربع الرابع         NaN     NaN  \n",
       "3                 2020           2021           2021         NaN     NaN  \n",
       "4  القيمة (مليون ريال)            NaN            NaN         NaN     NaN  \n",
       "5          1305.255429    1261.434116    1533.349188         NaN     NaN  \n",
       "6           435.302366     299.184048     531.968384         NaN     NaN  \n",
       "7           212.914862     340.523261     415.289859         NaN     NaN  \n",
       "8          1498.496898    1742.577819    1937.558877         NaN     NaN  \n",
       "9        122612.141296  207865.871133  241827.571306         NaN     NaN  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q4_Ex_bysection.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Ex_bysection.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 5', 'الفهرس'], axis=1, inplace=True)\n",
    "Q4_Ex_bysection.drop([0,1,2,3,4], axis=0, inplace=True)\n",
    "Q4_Ex_bysection.rename(columns={'Unnamed: 1': 'Section Description', 'Unnamed: 3': '2021_Q3_mSAR', 'Unnamed: 4': '2021_Q4_mSAR' }, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Ex_bysection.to_csv('Cleaned_data/ITR Q42021A_1-2_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q4 sheet 2.1, Imports by Section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>الفهرس</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>الواردات حسب الاقسام</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>رقم القسم</td>\n",
       "      <td>وصف القسم</td>\n",
       "      <td>الربع الرابع</td>\n",
       "      <td>الربع الثالث</td>\n",
       "      <td>الربع الرابع</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>القيمة (مليون ريال)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>الحيوانات الحية والمنتجات الحيوانية</td>\n",
       "      <td>4620.311351</td>\n",
       "      <td>4706.252804</td>\n",
       "      <td>4526.153091</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>منتجات نباتية</td>\n",
       "      <td>7607.059606</td>\n",
       "      <td>8340.891727</td>\n",
       "      <td>7558.866638</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...</td>\n",
       "      <td>825.368692</td>\n",
       "      <td>1096.588408</td>\n",
       "      <td>1099.633078</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...</td>\n",
       "      <td>7141.004899</td>\n",
       "      <td>7006.645802</td>\n",
       "      <td>7373.740892</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>المنتجات المعدنية</td>\n",
       "      <td>5535.802553</td>\n",
       "      <td>8626.331296</td>\n",
       "      <td>8623.421906</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Unnamed: 0                                         Unnamed: 1  \\\n",
       "0                   NaN                                                NaN   \n",
       "1  الواردات حسب الاقسام                                                NaN   \n",
       "2             رقم القسم                                          وصف القسم   \n",
       "3                   NaN                                                NaN   \n",
       "4                   NaN                                                NaN   \n",
       "5                     1                الحيوانات الحية والمنتجات الحيوانية   \n",
       "6                     2                                      منتجات نباتية   \n",
       "7                     3  شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...   \n",
       "8                     4  منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...   \n",
       "9                     5                                  المنتجات المعدنية   \n",
       "\n",
       "            Unnamed: 2    Unnamed: 3    Unnamed: 4  Unnamed: 5  الفهرس  \n",
       "0                  NaN           NaN           NaN         NaN     NaN  \n",
       "1                  NaN           NaN           NaN         NaN     NaN  \n",
       "2         الربع الرابع  الربع الثالث  الربع الرابع         NaN     NaN  \n",
       "3                 2020          2021          2021         NaN     NaN  \n",
       "4  القيمة (مليون ريال)           NaN           NaN         NaN     NaN  \n",
       "5          4620.311351   4706.252804   4526.153091         NaN     NaN  \n",
       "6          7607.059606   8340.891727   7558.866638         NaN     NaN  \n",
       "7           825.368692   1096.588408   1099.633078         NaN     NaN  \n",
       "8          7141.004899   7006.645802   7373.740892         NaN     NaN  \n",
       "9          5535.802553   8626.331296   8623.421906         NaN     NaN  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q4_Im_bysection.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bysection.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 5', 'الفهرس'], axis=1, inplace=True)\n",
    "Q4_Im_bysection.drop([0,1,2,3,4], axis=0, inplace=True)\n",
    "Q4_Im_bysection.rename(columns={'Unnamed: 1': 'Section Description', 'Unnamed: 3': '2021_Q3_mSAR', 'Unnamed: 4': '2021_Q4_mSAR'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Section Description</th>\n",
       "      <th>2021_Q3_mSAR</th>\n",
       "      <th>2021_Q4_mSAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>الحيوانات الحية والمنتجات الحيوانية</td>\n",
       "      <td>4706.252804</td>\n",
       "      <td>4526.153091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>منتجات نباتية</td>\n",
       "      <td>8340.891727</td>\n",
       "      <td>7558.866638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...</td>\n",
       "      <td>1096.588408</td>\n",
       "      <td>1099.633078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...</td>\n",
       "      <td>7006.645802</td>\n",
       "      <td>7373.740892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>المنتجات المعدنية</td>\n",
       "      <td>8626.331296</td>\n",
       "      <td>8623.421906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>منتجات الصناعات الكيماوية وما يتصل بها</td>\n",
       "      <td>15053.749638</td>\n",
       "      <td>15532.935368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>لدائن ومصنوعاتها؛ مطاط ومصنوعاته</td>\n",
       "      <td>5735.614725</td>\n",
       "      <td>5355.059265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>صلال وجلود خام و جلود مدبوغة وجلود بفراء ومصنو...</td>\n",
       "      <td>445.31853</td>\n",
       "      <td>426.146853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>خشـب ومصنوعاتــه؛ فحم خشبـــي؛ فلين ومصنوعاته؛...</td>\n",
       "      <td>1559.317872</td>\n",
       "      <td>1523.854678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>عجائن من خشب أو من مواد ليفية سليلوزية أخر؛ ور...</td>\n",
       "      <td>1834.251064</td>\n",
       "      <td>1856.035267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>مواد نسـجية ومصنوعات من هذه المواد</td>\n",
       "      <td>5265.496644</td>\n",
       "      <td>5289.98217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>أحذية، أغطية رأس، مظلات مطر، مظلات شمس، عصي مش...</td>\n",
       "      <td>1044.552202</td>\n",
       "      <td>875.018064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>مصنوعات من حجر أو جص أو إسمنت أو حرير صخري (اس...</td>\n",
       "      <td>1744.404144</td>\n",
       "      <td>1857.123988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>لؤلؤ طبيعي أو مستنبت، أحجار كريمة أو شبه كريمة...</td>\n",
       "      <td>4432.812363</td>\n",
       "      <td>5548.635918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>معادن عادية ومصنوعاتها</td>\n",
       "      <td>13875.196698</td>\n",
       "      <td>15319.101001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>آلات وأجهزة آلية؛ معدات كهربائية؛ أجزاؤها؛ أجه...</td>\n",
       "      <td>27815.685118</td>\n",
       "      <td>31557.284471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>عربات، طائرات، بواخر، ومعدات نقل مماثلة</td>\n",
       "      <td>25416.158582</td>\n",
       "      <td>24157.388522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>أدوات وأجهزة للبصريات أو للتصوير الفوتوغرافي أ...</td>\n",
       "      <td>3713.413469</td>\n",
       "      <td>4028.803242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>أسلحة وذخائر؛ أجزاؤها ولوازمها</td>\n",
       "      <td>2817.120787</td>\n",
       "      <td>2349.069322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>سلع ومنتجات متـنوعة</td>\n",
       "      <td>3706.456827</td>\n",
       "      <td>3792.00799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>تحف فنية، قطع للمجموعات وقطع أثرية</td>\n",
       "      <td>2319.568116</td>\n",
       "      <td>2488.478854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>الإجمالي</td>\n",
       "      <td>146555.826816</td>\n",
       "      <td>151138.740578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  Section Description   2021_Q3_mSAR  \\\n",
       "5                 الحيوانات الحية والمنتجات الحيوانية    4706.252804   \n",
       "6                                       منتجات نباتية    8340.891727   \n",
       "7   شحوم ودهون وزيوت حيوانية أو نباتية ومنتجات تفك...    1096.588408   \n",
       "8   منتجات صناعة الأغذية؛ مشروبات؛ سوائل كحولية وخ...    7006.645802   \n",
       "9                                   المنتجات المعدنية    8626.331296   \n",
       "10             منتجات الصناعات الكيماوية وما يتصل بها   15053.749638   \n",
       "11                   لدائن ومصنوعاتها؛ مطاط ومصنوعاته    5735.614725   \n",
       "12  صلال وجلود خام و جلود مدبوغة وجلود بفراء ومصنو...      445.31853   \n",
       "13  خشـب ومصنوعاتــه؛ فحم خشبـــي؛ فلين ومصنوعاته؛...    1559.317872   \n",
       "14  عجائن من خشب أو من مواد ليفية سليلوزية أخر؛ ور...    1834.251064   \n",
       "15                 مواد نسـجية ومصنوعات من هذه المواد    5265.496644   \n",
       "16  أحذية، أغطية رأس، مظلات مطر، مظلات شمس، عصي مش...    1044.552202   \n",
       "17  مصنوعات من حجر أو جص أو إسمنت أو حرير صخري (اس...    1744.404144   \n",
       "18  لؤلؤ طبيعي أو مستنبت، أحجار كريمة أو شبه كريمة...    4432.812363   \n",
       "19                             معادن عادية ومصنوعاتها   13875.196698   \n",
       "20  آلات وأجهزة آلية؛ معدات كهربائية؛ أجزاؤها؛ أجه...   27815.685118   \n",
       "21            عربات، طائرات، بواخر، ومعدات نقل مماثلة   25416.158582   \n",
       "22  أدوات وأجهزة للبصريات أو للتصوير الفوتوغرافي أ...    3713.413469   \n",
       "23                     أسلحة وذخائر؛ أجزاؤها ولوازمها    2817.120787   \n",
       "24                                سلع ومنتجات متـنوعة    3706.456827   \n",
       "25                 تحف فنية، قطع للمجموعات وقطع أثرية    2319.568116   \n",
       "26                                           الإجمالي  146555.826816   \n",
       "\n",
       "     2021_Q4_mSAR  \n",
       "5     4526.153091  \n",
       "6     7558.866638  \n",
       "7     1099.633078  \n",
       "8     7373.740892  \n",
       "9     8623.421906  \n",
       "10   15532.935368  \n",
       "11    5355.059265  \n",
       "12     426.146853  \n",
       "13    1523.854678  \n",
       "14    1856.035267  \n",
       "15     5289.98217  \n",
       "16     875.018064  \n",
       "17    1857.123988  \n",
       "18    5548.635918  \n",
       "19   15319.101001  \n",
       "20   31557.284471  \n",
       "21   24157.388522  \n",
       "22    4028.803242  \n",
       "23    2349.069322  \n",
       "24     3792.00799  \n",
       "25    2488.478854  \n",
       "26  151138.740578  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q4_Im_bysection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bysection.to_csv('Cleaned_data/ITR Q42021A_2-1_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Q4 sheet 2.3, Imports by Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>الفهرس</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>الواردات حسب الدول</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>مرتبة</td>\n",
       "      <td>الدول</td>\n",
       "      <td>الربع الرابع</td>\n",
       "      <td>الربع الثالث</td>\n",
       "      <td>الربع الرابع</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>القيمة (مليون ريال)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>الصين</td>\n",
       "      <td>27663.397783</td>\n",
       "      <td>31235.878496</td>\n",
       "      <td>31258.061824</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>الولايات المتحدة الأمريكية</td>\n",
       "      <td>14149.757937</td>\n",
       "      <td>14920.396897</td>\n",
       "      <td>16103.400957</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>الامارات العربية المتحدة</td>\n",
       "      <td>9758.62298</td>\n",
       "      <td>10385.818185</td>\n",
       "      <td>10805.359601</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>الهند</td>\n",
       "      <td>6691.131033</td>\n",
       "      <td>8140.645779</td>\n",
       "      <td>7367.978312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>المانيا</td>\n",
       "      <td>6473.256585</td>\n",
       "      <td>6832.224905</td>\n",
       "      <td>6978.209918</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Unnamed: 0                  Unnamed: 1           Unnamed: 2  \\\n",
       "0                 NaN                         NaN                  NaN   \n",
       "1  الواردات حسب الدول                         NaN                  NaN   \n",
       "2               مرتبة                       الدول         الربع الرابع   \n",
       "3                 NaN                         NaN                 2020   \n",
       "4                 NaN                         NaN  القيمة (مليون ريال)   \n",
       "5                   1                       الصين         27663.397783   \n",
       "6                   2  الولايات المتحدة الأمريكية         14149.757937   \n",
       "7                   3    الامارات العربية المتحدة           9758.62298   \n",
       "8                   4                       الهند          6691.131033   \n",
       "9                   5                     المانيا          6473.256585   \n",
       "\n",
       "     Unnamed: 3    Unnamed: 4  Unnamed: 5  الفهرس  \n",
       "0           NaN           NaN         NaN     NaN  \n",
       "1           NaN           NaN         NaN     NaN  \n",
       "2  الربع الثالث  الربع الرابع         NaN     NaN  \n",
       "3          2021          2021         NaN     NaN  \n",
       "4           NaN           NaN         NaN     NaN  \n",
       "5  31235.878496  31258.061824         NaN     NaN  \n",
       "6  14920.396897  16103.400957         NaN     NaN  \n",
       "7  10385.818185  10805.359601         NaN     NaN  \n",
       "8   8140.645779   7367.978312         NaN     NaN  \n",
       "9   6832.224905   6978.209918         NaN     NaN  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q4_Im_bycountry.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bycountry.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 5', 'الفهرس'], axis=1, inplace=True)\n",
    "Q4_Im_bycountry.drop([0,1,2,3,4], axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bycountry.rename(columns={'Unnamed: 1': 'Country', 'Unnamed: 3': '2021_Q3_mSAR', 'Unnamed: 4': '2021_Q4_mSAR'}, inplace=True)\n",
    "Q4_Im_bycountry.replace({'الصين': 'CHINA', 'الولايات المتحدة الأمريكية': 'USA','الامارات العربية المتحدة': 'UAE',\n",
    "'الهند': 'INDIA', 'المانيا': 'GERMANY'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>Country</th>\n",
       "      <th>2021_Q3_mSAR</th>\n",
       "      <th>2021_Q4_mSAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CHINA</td>\n",
       "      <td>31235.878496</td>\n",
       "      <td>31258.061824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USA</td>\n",
       "      <td>14920.396897</td>\n",
       "      <td>16103.400957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>UAE</td>\n",
       "      <td>10385.818185</td>\n",
       "      <td>10805.359601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>INDIA</td>\n",
       "      <td>8140.645779</td>\n",
       "      <td>7367.978312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>GERMANY</td>\n",
       "      <td>6832.224905</td>\n",
       "      <td>6978.209918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>موريتانيا</td>\n",
       "      <td>0.015553</td>\n",
       "      <td>0.462222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>جزيرة نورفولك</td>\n",
       "      <td>0.133750</td>\n",
       "      <td>0.378750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>كوراساو</td>\n",
       "      <td>0.085575</td>\n",
       "      <td>0.370466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>بقية الدول</td>\n",
       "      <td>102.835653</td>\n",
       "      <td>9.129419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>الإجمالي</td>\n",
       "      <td>146555.826816</td>\n",
       "      <td>151138.740578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>148 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Country   2021_Q3_mSAR   2021_Q4_mSAR\n",
       "5            CHINA   31235.878496   31258.061824\n",
       "6              USA   14920.396897   16103.400957\n",
       "7              UAE   10385.818185   10805.359601\n",
       "8            INDIA    8140.645779    7367.978312\n",
       "9          GERMANY    6832.224905    6978.209918\n",
       "..             ...            ...            ...\n",
       "148      موريتانيا       0.015553       0.462222\n",
       "149  جزيرة نورفولك       0.133750       0.378750\n",
       "150        كوراساو       0.085575       0.370466\n",
       "151     بقية الدول     102.835653       9.129419\n",
       "152       الإجمالي  146555.826816  151138.740578\n",
       "\n",
       "[148 rows x 3 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q4_Im_bycountry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "Q4_Im_bycountry.to_csv('Cleaned_data/ITR Q42021A_2-3_clean.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### - Use the concad method here, but first, make sure you call the columns to be combined\n",
    "### - Or you can do: overall = Q2imports['2021_Q2_mSAR'] + q4imports['2021_q3_mSAR'] ((much easier))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6      9768.687326\n",
       "7     16837.624616\n",
       "8      2066.776803\n",
       "9     14221.230031\n",
       "10     11434.07243\n",
       "11     29791.80928\n",
       "12    10654.456036\n",
       "13      875.214027\n",
       "14     2585.269851\n",
       "15     3200.023282\n",
       "16     10416.58391\n",
       "17     2009.930085\n",
       "18     3828.100476\n",
       "19    10447.710202\n",
       "20    24199.010985\n",
       "21    54476.774893\n",
       "22    47389.611215\n",
       "23     7934.275302\n",
       "24     7222.679275\n",
       "25     6974.862462\n",
       "26     4111.737653\n",
       "27    280446.44014\n",
       "dtype: object"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot_21_Im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Im_bysection2021 = pd.concat()"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3.8.0 64-bit",
   "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.8.0"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}