02_Format_Clean_Remove_duplicates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bcf5d27a",
   "metadata": {},
   "source": [
    "# Data preparation and transformation exercise\n",
    "\n",
    "## Part II - Format, Clean and Remove duplicates\n",
    "\n",
    "The objective of this exercise is to practice various steps of data preprocessing and feature engineering.\n",
    "\n",
    "The scenario is the preparation of data for a ML multilinear regressions.\n",
    "\n",
    "The dataset used is the \"Climate Weather Surface of Brazil - Hourly\", wich is available at <a href=\"https://www.kaggle.com/PROPPG-PPG/hourly-weather-surface-brazil-southeast-region?select=make_dataset.py\">Kaggle</a>.\n",
    "\n",
    "It contains hourly climate data taken from 122 weather stations in Brasil between 2000 and 2021.\n",
    "\n",
    "**Steps:**\n",
    "1. Load data\n",
    "2. Inspect data\n",
    "3. <a href=\"#Format-features\">Format features</a>\n",
    "4. <a href=\"#Clean-messy-data\">Clean messy data</a>\n",
    "5. <a href=\"#Remove-duplicate-values\">Remove duplicate values</a>\n",
    "6. Treat missing values\n",
    "7. Imputation\n",
    "8. Remove strongly correlated features\n",
    "9.  Remove outliers\n",
    "10. Aggregate features\n",
    "11. Encode categorical features\n",
    "12. Feature scaling\n",
    "13. Dimensionality reduction and feature decomposition\n",
    "14. Sample and balance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd0d36d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "dataset = pickle.load(open(\"newindex_dataset.pkl\", \"rb\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77816cd3",
   "metadata": {},
   "source": [
    "## Format features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e9dedea",
   "metadata": {},
   "source": [
    "Let's look at the first row, to get a feeling for how the features are presented."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "82990e7c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "full_time          2017-12-20 14:00:00\n",
       "precipitation                      0.0\n",
       "pressure                         899.6\n",
       "pressure_max                     900.0\n",
       "pressure_min                     899.6\n",
       "solar_radiation                   3391\n",
       "air_temperature                   26.5\n",
       "dp_temperature                    17.7\n",
       "air_temp_max                      26.5\n",
       "air_temp_min                      24.4\n",
       "dp_temp_max                       18.3\n",
       "dp_temp_min                       16.5\n",
       "rel_hum_max                         65\n",
       "rel_hum_min                         57\n",
       "Rel_humidity                        59\n",
       "wind_direction                      39\n",
       "wind_gust                          9.6\n",
       "wind_speed                         3.9\n",
       "region                              CO\n",
       "state                               DF\n",
       "station_name        PARANOA (COOPA-DF)\n",
       "station_id                        A047\n",
       "Latitude                    -16.011111\n",
       "Longitude                     -47.5575\n",
       "Elevation                       1043.0\n",
       "year                              2017\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "207b7a69",
   "metadata": {},
   "source": [
    "Let's check maximum and minimum values for each feature, to tee if they are within reasonable bounds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "078a0bf0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "full_time                       2021-04-30 23:00:00\n",
       "precipitation                                  96.0\n",
       "pressure                                     1028.8\n",
       "pressure_max                                 1030.6\n",
       "pressure_min                                 1028.1\n",
       "solar_radiation                               48898\n",
       "air_temperature                                45.0\n",
       "dp_temperature                                 44.8\n",
       "air_temp_max                                   45.0\n",
       "air_temp_min                                   45.0\n",
       "dp_temp_max                                    44.9\n",
       "dp_temp_min                                    44.7\n",
       "rel_hum_max                                     100\n",
       "rel_hum_min                                     100\n",
       "Rel_humidity                                    100\n",
       "wind_direction                                  360\n",
       "wind_gust                                      49.4\n",
       "wind_speed                                     20.0\n",
       "region                                           CO\n",
       "state                                            MT\n",
       "station_name       VILA BELA DA SANTISSIMA TRINDADE\n",
       "station_id                                     S717\n",
       "Latitude                                  -9.563333\n",
       "Longitude                                -46.366389\n",
       "Elevation                                    1264.7\n",
       "year                                           2021\n",
       "dtype: object"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7eb41d36",
   "metadata": {},
   "source": [
    "The maximum values seem ok for these feature types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "2ea1c7bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "full_time          2000-05-07 00:00:00\n",
       "precipitation                  -9999.0\n",
       "pressure                       -9999.0\n",
       "pressure_max                   -9999.0\n",
       "pressure_min                   -9999.0\n",
       "solar_radiation                  -9999\n",
       "air_temperature                -9999.0\n",
       "dp_temperature                 -9999.0\n",
       "air_temp_max                   -9999.0\n",
       "air_temp_min                   -9999.0\n",
       "dp_temp_max                    -9999.0\n",
       "dp_temp_min                    -9999.0\n",
       "rel_hum_max                      -9999\n",
       "rel_hum_min                      -9999\n",
       "Rel_humidity                     -9999\n",
       "wind_direction                   -9999\n",
       "wind_gust                      -9999.0\n",
       "wind_speed                     -9999.0\n",
       "region                              CO\n",
       "state                               DF\n",
       "station_name                  AGUA BOA\n",
       "station_id                        A001\n",
       "Latitude                    -23.966944\n",
       "Longitude                   -59.873056\n",
       "Elevation                          5.0\n",
       "year                              2000\n",
       "dtype: object"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.min()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7680421f",
   "metadata": {},
   "source": [
    "Clearly we have -9999 replacing missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d63e2ab",
   "metadata": {},
   "source": [
    "## Clean messy data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a60ec399",
   "metadata": {},
   "source": [
    "As we saw before, the values for Latitude, Longitude and Elevation in each station are not unique, but as they are categorical features that correspond to a same station, like 'region' and 'state', so we'll discard them, as well as the station_id, and use the categorical feature station_name (one hot encoded).\n",
    "\n",
    "This still leaves us with the two different values for station_name of the same station, so before discarding station_id, we'll unify the name for station with 'station_id' == A927 as \"BRASNORTE (NOVO MUNDO)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "739095a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.loc[dataset.station_id == 'A927', 'station_name'] = \"BRASNORTE (NOVO MUNDO)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "43f4ac8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['BRASNORTE (NOVO MUNDO)'], dtype=object)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.station_name.loc[dataset.station_id == 'A927'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "89f382b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.drop(columns = ['station_id', \n",
    "                        'Latitude', \n",
    "                        'Longitude', \n",
    "                        'Elevation', \n",
    "                        'year', \n",
    "                        'region', \n",
    "                        'state'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "02aaab63",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(dataset, open(\"clean_dataset.pkl\", \"wb\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bdc7ff1",
   "metadata": {},
   "source": [
    "## Remove duplicate values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "0bd26ed8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    11427120\n",
       "dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8d4923f",
   "metadata": {},
   "source": [
    "There are no duplicates in the dataset."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}