d1_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATA CLEANING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample_data = pd.read_csv(\n",
    "    \"https://raw.githubusercontent.com/davidbrandon16/binusMachineLearning/master/sample.csv\",\n",
    "    sep=\";\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 260 entries, 0 to 259\n",
      "Data columns (total 8 columns):\n",
      "Year                           260 non-null int64\n",
      "Industry_aggregation_NZSIOC    260 non-null object\n",
      "Industry_code_NZSIOC           260 non-null object\n",
      "Industry_name_NZSIOC           260 non-null object\n",
      "Units                          260 non-null object\n",
      "Variable_code                  260 non-null object\n",
      "Variable_category              260 non-null object\n",
      "Value                          251 non-null float64\n",
      "dtypes: float64(1), int64(1), object(6)\n",
      "memory usage: 16.4+ KB\n"
     ]
    }
   ],
   "source": [
    "sample_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1 = sample_data.drop_duplicates().dropna().reset_index(\n",
    "    drop=True\n",
    ").rename(\n",
    "    columns={\n",
    "        \"Industry_aggregation_NZSIOC\": \"level\",\n",
    "        \"Industry_code_NZSIOC\": \"code\",\n",
    "        \"Industry_name_NZSIOC\": \"name\",\n",
    "    }\n",
    ").drop(columns=\"Year\")"
   ]
  },
  {
   "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>level</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>Units</th>\n",
       "      <th>Variable_code</th>\n",
       "      <th>Variable_category</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H01</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>4.432000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H04</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>4.119000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H07</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>2.110000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H08</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>3.579000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H09</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.800000e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     level  code                            name               Units  \\\n",
       "0  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "1  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "2  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "3  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "4  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "\n",
       "  Variable_code      Variable_category         Value  \n",
       "0           H01  Financial performance  4.432000e+09  \n",
       "1           H04  Financial performance  4.119000e+09  \n",
       "2           H07  Financial performance  2.110000e+08  \n",
       "3           H08  Financial performance  3.579000e+09  \n",
       "4           H09  Financial performance  1.800000e+08  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 231 entries, 0 to 230\n",
      "Data columns (total 7 columns):\n",
      "level                231 non-null object\n",
      "code                 231 non-null object\n",
      "name                 231 non-null object\n",
      "Units                231 non-null object\n",
      "Variable_code        231 non-null object\n",
      "Variable_category    231 non-null object\n",
      "Value                231 non-null float64\n",
      "dtypes: float64(1), object(6)\n",
      "memory usage: 12.8+ KB\n"
     ]
    }
   ],
   "source": [
    "data_1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['level', 'code', 'name', 'Units', 'Variable_code', 'Variable_category',\n",
       "       'Value'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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></th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>level</th>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Level 3</th>\n",
       "      <th>Dairy Cattle Farming</th>\n",
       "      <td>1.116443e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Horticulture and Fruit Growing</th>\n",
       "      <td>2.505578e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poultry, Deer and Other Livestock Farming</th>\n",
       "      <td>1.216081e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sheep, Beef Cattle and Grain Farming</th>\n",
       "      <td>1.008699e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Level 4</th>\n",
       "      <th>Dairy Cattle Farming</th>\n",
       "      <td>8.938504e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Horticulture and Fruit Growing</th>\n",
       "      <td>2.421525e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poultry, Deer and Other Livestock Farming</th>\n",
       "      <td>1.175546e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sheep, Beef Cattle and Grain Farming</th>\n",
       "      <td>9.761539e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          Value\n",
       "level   name                                                   \n",
       "Level 3 Dairy Cattle Farming                       1.116443e+10\n",
       "        Horticulture and Fruit Growing             2.505578e+09\n",
       "        Poultry, Deer and Other Livestock Farming  1.216081e+09\n",
       "        Sheep, Beef Cattle and Grain Farming       1.008699e+10\n",
       "Level 4 Dairy Cattle Farming                       8.938504e+09\n",
       "        Horticulture and Fruit Growing             2.421525e+09\n",
       "        Poultry, Deer and Other Livestock Farming  1.175546e+09\n",
       "        Sheep, Beef Cattle and Grain Farming       9.761539e+09"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1.groupby([\"level\", \"name\"]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1.loc[(data_1.Units == \"Dollars (millions)\"), \"Value\"] = data_1.Value * 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>level</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>Units</th>\n",
       "      <th>Variable_code</th>\n",
       "      <th>Variable_category</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H01</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.353700e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H04</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.301100e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H05</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.810000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H07</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>3.450000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H08</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.165800e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H09</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.568000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H11</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.330000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H12</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.442000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H13</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H19</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>7.807000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H20</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>3.500000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H21</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.521000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H22</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.388000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H23</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.746000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H24</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>6.472400e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H25</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>1.174900e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H26</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>4.123700e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H29</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>1.173800e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H30</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>6.472400e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H31</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>2.247300e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H32</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>1.055800e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H33</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>3.169300e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>H34</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>5.364000e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>H35</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>6.920000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H36</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>1.110000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H37</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>5.100000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H39</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>8.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H40</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA13</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H41</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>3.500000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA131</td>\n",
       "      <td>Dairy Cattle Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H01</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.353700e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA12</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H41</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>5.500000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H01</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.155500e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H04</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.081200e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H05</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.350000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H07</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.080000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H08</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>9.867000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H09</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.990000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H10</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.780000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H11</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>5.260000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H12</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>9.980000e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H13</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H19</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>7.416000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H21</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.246000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H22</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>6.393000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H23</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>1.835000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H24</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>5.645400e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H25</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>1.280300e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H26</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>3.585800e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H29</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>7.793000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H30</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>5.645400e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H31</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>3.105600e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H32</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>9.333000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H33</td>\n",
       "      <td>Financial position</td>\n",
       "      <td>1.606500e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>H34</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>5.423000e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Dollars</td>\n",
       "      <td>H35</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>8.610000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H36</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>1.370000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H37</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>6.900000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H39</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H40</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Level 4</td>\n",
       "      <td>AA121</td>\n",
       "      <td>Sheep, Beef Cattle and Grain Farming</td>\n",
       "      <td>Percentage</td>\n",
       "      <td>H41</td>\n",
       "      <td>Financial ratios</td>\n",
       "      <td>5.500000e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>231 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       level   code                                  name               Units  \\\n",
       "114  Level 3   AA13                  Dairy Cattle Farming  Dollars (millions)   \n",
       "115  Level 3   AA13                  Dairy Cattle Farming  Dollars (millions)   \n",
       "116  Level 3   AA13                  Dairy Cattle Farming  Dollars (millions)   \n",
       "117  Level 3   AA13                  Dairy Cattle Farming  Dollars (millions)   \n",
       "118  Level 3   AA13                  Dairy Cattle Farming  Dollars (millions)   \n",
       "..       ...    ...                                   ...                 ...   \n",
       "109  Level 4  AA121  Sheep, Beef Cattle and Grain Farming          Percentage   \n",
       "110  Level 4  AA121  Sheep, Beef Cattle and Grain Farming          Percentage   \n",
       "111  Level 4  AA121  Sheep, Beef Cattle and Grain Farming          Percentage   \n",
       "112  Level 4  AA121  Sheep, Beef Cattle and Grain Farming          Percentage   \n",
       "113  Level 4  AA121  Sheep, Beef Cattle and Grain Farming          Percentage   \n",
       "\n",
       "    Variable_code      Variable_category         Value  \n",
       "114           H01  Financial performance  1.353700e+10  \n",
       "115           H04  Financial performance  1.301100e+10  \n",
       "116           H05  Financial performance  1.810000e+08  \n",
       "117           H07  Financial performance  3.450000e+08  \n",
       "118           H08  Financial performance  1.165800e+10  \n",
       "..            ...                    ...           ...  \n",
       "109           H36       Financial ratios  1.370000e+02  \n",
       "110           H37       Financial ratios  6.900000e+01  \n",
       "111           H39       Financial ratios  6.000000e+00  \n",
       "112           H40       Financial ratios  3.000000e+00  \n",
       "113           H41       Financial ratios  5.500000e+01  \n",
       "\n",
       "[231 rows x 7 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1.sort_values(by=[\n",
    "    \"name\",\n",
    "    \"code\",\n",
    "    \"Variable_code\",\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2 = sample_data.drop_duplicates().fillna(\n",
    "    0,\n",
    ").reset_index(\n",
    "    drop=True,\n",
    ").rename(\n",
    "    columns={\n",
    "        \"Industry_aggregation_NZSIOC\": \"level\",\n",
    "        \"Industry_code_NZSIOC\": \"code\",\n",
    "        \"Industry_name_NZSIOC\": \"name\",\n",
    "    }\n",
    ").drop(columns=\"Year\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>level</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>Units</th>\n",
       "      <th>Variable_code</th>\n",
       "      <th>Variable_category</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H01</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>4432.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H04</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>4119.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H05</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H07</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>211.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Level 3</td>\n",
       "      <td>AA11</td>\n",
       "      <td>Horticulture and Fruit Growing</td>\n",
       "      <td>Dollars (millions)</td>\n",
       "      <td>H08</td>\n",
       "      <td>Financial performance</td>\n",
       "      <td>3579.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     level  code                            name               Units  \\\n",
       "0  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "1  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "2  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "3  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "4  Level 3  AA11  Horticulture and Fruit Growing  Dollars (millions)   \n",
       "\n",
       "  Variable_code      Variable_category   Value  \n",
       "0           H01  Financial performance  4432.0  \n",
       "1           H04  Financial performance  4119.0  \n",
       "2           H05  Financial performance     0.0  \n",
       "3           H07  Financial performance   211.0  \n",
       "4           H08  Financial performance  3579.0  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2.head()"
   ]
  },
  {
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}