data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Load libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import glob"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download data from the period 2010-2020 \n",
    "all_files = glob.glob(\"./data/datos*.csv\")\n",
    "\n",
    "dfs = []\n",
    "\n",
    "for filename in all_files:\n",
    "    df = pd.read_csv(filename, header=0, sep=';')\n",
    "    dfs.append(df)\n",
    "\n",
    "df = pd.concat(dfs, axis=0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "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>PROVINCIA</th>\n",
       "      <th>MUNICIPIO</th>\n",
       "      <th>ESTACION</th>\n",
       "      <th>MAGNITUD</th>\n",
       "      <th>PUNTO_MUESTREO</th>\n",
       "      <th>ANO</th>\n",
       "      <th>MES</th>\n",
       "      <th>D01</th>\n",
       "      <th>V01</th>\n",
       "      <th>D02</th>\n",
       "      <th>...</th>\n",
       "      <th>D27</th>\n",
       "      <th>V27</th>\n",
       "      <th>D28</th>\n",
       "      <th>V28</th>\n",
       "      <th>D29</th>\n",
       "      <th>V29</th>\n",
       "      <th>D30</th>\n",
       "      <th>V30</th>\n",
       "      <th>D31</th>\n",
       "      <th>V31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28</td>\n",
       "      <td>79</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>28079004_1_38</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>V</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "      <td>7.0</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28</td>\n",
       "      <td>79</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>28079004_1_38</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28</td>\n",
       "      <td>79</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>28079004_1_38</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "      <td>7.0</td>\n",
       "      <td>V</td>\n",
       "      <td>7.0</td>\n",
       "      <td>V</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28</td>\n",
       "      <td>79</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>28079004_1_38</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>0.0</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>79</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>28079004_1_38</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V</td>\n",
       "      <td>5.0</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 69 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   PROVINCIA  MUNICIPIO  ESTACION  MAGNITUD PUNTO_MUESTREO   ANO  MES  D01  \\\n",
       "0         28         79         4         1  28079004_1_38  2017    1  6.0   \n",
       "1         28         79         4         1  28079004_1_38  2017    2  8.0   \n",
       "2         28         79         4         1  28079004_1_38  2017    3  5.0   \n",
       "3         28         79         4         1  28079004_1_38  2017    4  4.0   \n",
       "4         28         79         4         1  28079004_1_38  2017    5  4.0   \n",
       "\n",
       "  V01  D02  ...  D27  V27  D28  V28  D29  V29  D30  V30  D31  V31  \n",
       "0   V  8.0  ...  5.0    V  4.0    V  4.0    V  5.0    V  7.0    V  \n",
       "1   V  4.0  ...  4.0    V  4.0    V  0.0    N  0.0    N  0.0    N  \n",
       "2   V  7.0  ...  4.0    V  5.0    V  7.0    V  7.0    V  5.0    V  \n",
       "3   V  4.0  ...  3.0    V  4.0    V  4.0    V  4.0    V  0.0    N  \n",
       "4   V  5.0  ...  5.0    V  5.0    V  4.0    V  4.0    V  5.0    V  \n",
       "\n",
       "[5 rows x 69 columns]"
      ]
     },
     "execution_count": 256,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lower column names\n",
    "df.columns = map(str.lower, df.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Data cleansing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "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>d01</th>\n",
       "      <th>d02</th>\n",
       "      <th>d03</th>\n",
       "      <th>d04</th>\n",
       "      <th>d05</th>\n",
       "      <th>d06</th>\n",
       "      <th>d07</th>\n",
       "      <th>d08</th>\n",
       "      <th>d09</th>\n",
       "      <th>d10</th>\n",
       "      <th>...</th>\n",
       "      <th>d22</th>\n",
       "      <th>d23</th>\n",
       "      <th>d24</th>\n",
       "      <th>d25</th>\n",
       "      <th>d26</th>\n",
       "      <th>d27</th>\n",
       "      <th>d28</th>\n",
       "      <th>d29</th>\n",
       "      <th>d30</th>\n",
       "      <th>d31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   d01  d02   d03  d04  d05   d06   d07   d08   d09   d10  ...   d22   d23  \\\n",
       "0  6.0  8.0  12.0  9.0  7.0  10.0  10.0  11.0  10.0  12.0  ...   7.0   9.0   \n",
       "1  8.0  4.0   3.0  3.0  3.0   4.0   5.0   5.0   6.0   8.0  ...  12.0  11.0   \n",
       "2  5.0  7.0   5.0  3.0  3.0   4.0   6.0   9.0  10.0  10.0  ...   4.0   3.0   \n",
       "3  4.0  4.0   5.0  7.0  4.0   4.0   5.0   5.0   5.0   6.0  ...   5.0   5.0   \n",
       "4  4.0  5.0   5.0  6.0  5.0   4.0   4.0   4.0   5.0   4.0  ...   5.0   5.0   \n",
       "\n",
       "    d24  d25  d26  d27  d28  d29  d30  d31  \n",
       "0  12.0  9.0  9.0  5.0  4.0  4.0  5.0  7.0  \n",
       "1   6.0  6.0  5.0  4.0  4.0  0.0  0.0  0.0  \n",
       "2   4.0  4.0  4.0  4.0  5.0  7.0  7.0  5.0  \n",
       "3   6.0  4.0  4.0  3.0  4.0  4.0  4.0  0.0  \n",
       "4   5.0  5.0  5.0  5.0  5.0  4.0  4.0  5.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select columns with daily values\n",
    "days= df.filter(regex=(\"^d.*$\"))\n",
    "days.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "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>v01</th>\n",
       "      <th>v02</th>\n",
       "      <th>v03</th>\n",
       "      <th>v04</th>\n",
       "      <th>v05</th>\n",
       "      <th>v06</th>\n",
       "      <th>v07</th>\n",
       "      <th>v08</th>\n",
       "      <th>v09</th>\n",
       "      <th>v10</th>\n",
       "      <th>...</th>\n",
       "      <th>v22</th>\n",
       "      <th>v23</th>\n",
       "      <th>v24</th>\n",
       "      <th>v25</th>\n",
       "      <th>v26</th>\n",
       "      <th>v27</th>\n",
       "      <th>v28</th>\n",
       "      <th>v29</th>\n",
       "      <th>v30</th>\n",
       "      <th>v31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>...</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "      <td>V</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  v01 v02 v03 v04 v05 v06 v07 v08 v09 v10  ... v22 v23 v24 v25 v26 v27 v28  \\\n",
       "0   V   V   V   V   V   V   V   V   V   V  ...   V   V   V   V   V   V   V   \n",
       "1   V   V   V   V   V   V   V   V   V   V  ...   V   V   V   V   V   V   V   \n",
       "2   V   V   V   V   V   V   V   V   V   V  ...   V   V   V   V   V   V   V   \n",
       "3   V   V   V   V   V   V   V   V   V   V  ...   V   V   V   V   V   V   V   \n",
       "4   V   V   V   V   V   V   V   V   V   V  ...   V   V   V   V   V   V   V   \n",
       "\n",
       "  v29 v30 v31  \n",
       "0   V   V   V  \n",
       "1   N   N   N  \n",
       "2   V   V   V  \n",
       "3   V   V   N  \n",
       "4   V   V   V  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select columns with data validity\n",
    "validity = df.filter(regex=(\"^v.*$\"))\n",
    "validity.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert V in null values\n",
    "validity = validity.replace('V', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change column names in validty and use 'days' column names\n",
    "validity.columns = days.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use combine_first to get valid data\n",
    "valid_data= validity.combine_first(days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "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>d01</th>\n",
       "      <th>d02</th>\n",
       "      <th>d03</th>\n",
       "      <th>d04</th>\n",
       "      <th>d05</th>\n",
       "      <th>d06</th>\n",
       "      <th>d07</th>\n",
       "      <th>d08</th>\n",
       "      <th>d09</th>\n",
       "      <th>d10</th>\n",
       "      <th>...</th>\n",
       "      <th>d22</th>\n",
       "      <th>d23</th>\n",
       "      <th>d24</th>\n",
       "      <th>d25</th>\n",
       "      <th>d26</th>\n",
       "      <th>d27</th>\n",
       "      <th>d28</th>\n",
       "      <th>d29</th>\n",
       "      <th>d30</th>\n",
       "      <th>d31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   d01  d02   d03  d04  d05   d06   d07   d08   d09   d10  ...   d22   d23  \\\n",
       "0  6.0  8.0  12.0  9.0  7.0  10.0  10.0  11.0  10.0  12.0  ...   7.0   9.0   \n",
       "1  8.0  4.0   3.0  3.0  3.0   4.0   5.0   5.0   6.0   8.0  ...  12.0  11.0   \n",
       "2  5.0  7.0   5.0  3.0  3.0   4.0   6.0   9.0  10.0  10.0  ...   4.0   3.0   \n",
       "3  4.0  4.0   5.0  7.0  4.0   4.0   5.0   5.0   5.0   6.0  ...   5.0   5.0   \n",
       "4  4.0  5.0   5.0  6.0  5.0   4.0   4.0   4.0   5.0   4.0  ...   5.0   5.0   \n",
       "\n",
       "    d24  d25  d26  d27  d28  d29  d30  d31  \n",
       "0  12.0  9.0  9.0  5.0  4.0  4.0  5.0  7.0  \n",
       "1   6.0  6.0  5.0  4.0  4.0  NaN  NaN  NaN  \n",
       "2   4.0  4.0  4.0  4.0  5.0  7.0  7.0  5.0  \n",
       "3   6.0  4.0  4.0  3.0  4.0  4.0  4.0  NaN  \n",
       "4   5.0  5.0  5.0  5.0  5.0  4.0  4.0  5.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace 'N' values for np.nan\n",
    "valid_data= valid_data.replace('N', np.nan)\n",
    "\n",
    "valid_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "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>estacion</th>\n",
       "      <th>magnitud</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>d01</th>\n",
       "      <th>d02</th>\n",
       "      <th>d03</th>\n",
       "      <th>d04</th>\n",
       "      <th>d05</th>\n",
       "      <th>d06</th>\n",
       "      <th>...</th>\n",
       "      <th>d22</th>\n",
       "      <th>d23</th>\n",
       "      <th>d24</th>\n",
       "      <th>d25</th>\n",
       "      <th>d26</th>\n",
       "      <th>d27</th>\n",
       "      <th>d28</th>\n",
       "      <th>d29</th>\n",
       "      <th>d30</th>\n",
       "      <th>d31</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   estacion  magnitud   ano  mes  d01  d02   d03  d04  d05   d06  ...   d22  \\\n",
       "0         4         1  2017    1  6.0  8.0  12.0  9.0  7.0  10.0  ...   7.0   \n",
       "1         4         1  2017    2  8.0  4.0   3.0  3.0  3.0   4.0  ...  12.0   \n",
       "2         4         1  2017    3  5.0  7.0   5.0  3.0  3.0   4.0  ...   4.0   \n",
       "3         4         1  2017    4  4.0  4.0   5.0  7.0  4.0   4.0  ...   5.0   \n",
       "4         4         1  2017    5  4.0  5.0   5.0  6.0  5.0   4.0  ...   5.0   \n",
       "\n",
       "    d23   d24  d25  d26  d27  d28  d29  d30  d31  \n",
       "0   9.0  12.0  9.0  9.0  5.0  4.0  4.0  5.0  7.0  \n",
       "1  11.0   6.0  6.0  5.0  4.0  4.0  NaN  NaN  NaN  \n",
       "2   3.0   4.0  4.0  4.0  4.0  5.0  7.0  7.0  5.0  \n",
       "3   5.0   6.0  4.0  4.0  3.0  4.0  4.0  4.0  NaN  \n",
       "4   5.0   5.0  5.0  5.0  5.0  5.0  4.0  4.0  5.0  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new dataframe concatenating columns from df_match_no2 and valid measurements\n",
    "df_valid = pd.concat([df[['estacion', 'magnitud', 'ano','mes']], valid_data], axis=1)\n",
    "\n",
    "df_valid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = valid_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "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>estacion</th>\n",
       "      <th>magnitud</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>dia</th>\n",
       "      <th>medicion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>d01</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>d01</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>d01</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>d01</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>d01</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   estacion  magnitud   ano  mes  dia  medicion\n",
       "0         4         1  2017    1  d01       6.0\n",
       "1         4         1  2017    2  d01       8.0\n",
       "2         4         1  2017    3  d01       5.0\n",
       "3         4         1  2017    4  d01       4.0\n",
       "4         4         1  2017    5  d01       4.0"
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use melt to create a column 'day'\n",
    "df_valid = pd.melt(df_valid,id_vars= ['estacion','magnitud','ano','mes'], value_vars =cols,\n",
    "                             var_name ='dia', value_name ='medicion')\n",
    "                                                           \n",
    "df_valid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows with NULL values\n",
    "df_valid = df_valid.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove 'd' from column 'day'\n",
    "df_valid['dia'] = df_valid['dia'].str.replace('d', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new column 'date' joining year, month and day\n",
    "df_valid['fecha'] = (df_valid['ano'].map(str) + \"-\" + df_valid['mes'].map(str) + \"-\" \n",
    "                                                             + df_valid['dia'].map(str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "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>estacion</th>\n",
       "      <th>magnitud</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>dia</th>\n",
       "      <th>medicion</th>\n",
       "      <th>fecha</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>01</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>2</td>\n",
       "      <td>01</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2017-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>01</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>01</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2017-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>01</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2017-05-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   estacion  magnitud   ano  mes dia  medicion      fecha\n",
       "0         4         1  2017    1  01       6.0 2017-01-01\n",
       "1         4         1  2017    2  01       8.0 2017-02-01\n",
       "2         4         1  2017    3  01       5.0 2017-03-01\n",
       "3         4         1  2017    4  01       4.0 2017-04-01\n",
       "4         4         1  2017    5  01       4.0 2017-05-01"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert 'date' to datetime\n",
    "df_valid['fecha'] = pd.to_datetime(df_valid['fecha'], format='%Y-%m-%d')\n",
    "\n",
    "df_valid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "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>estacion</th>\n",
       "      <th>magnitud</th>\n",
       "      <th>medicion</th>\n",
       "      <th>fecha</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2017-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2017-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2017-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2017-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2017-05-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   estacion  magnitud  medicion      fecha\n",
       "0         4         1       6.0 2017-01-01\n",
       "1         4         1       8.0 2017-02-01\n",
       "2         4         1       5.0 2017-03-01\n",
       "3         4         1       4.0 2017-04-01\n",
       "4         4         1       4.0 2017-05-01"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop columns 'year','month','day'\n",
    "df_valid.drop(columns=['ano', 'mes', 'dia'], inplace=True)\n",
    "\n",
    "df_valid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use grouby to calculate daily average emission by date and particle.\n",
    "df_avg_emissions = pd.DataFrame(df_valid.groupby(['fecha', 'magnitud'])\n",
    "                                ['medicion']\n",
    "                                .mean().reset_index())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pivot to turn every particle in a column\n",
    "df_avg_emissions = pd.pivot(df_avg_emissions, index= 'fecha', columns='magnitud',values='medicion')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "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>magnitud</th>\n",
       "      <th>1</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>12</th>\n",
       "      <th>14</th>\n",
       "      <th>20</th>\n",
       "      <th>30</th>\n",
       "      <th>35</th>\n",
       "      <th>42</th>\n",
       "      <th>43</th>\n",
       "      <th>44</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fecha</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>7.9</td>\n",
       "      <td>0.322222</td>\n",
       "      <td>7.105263</td>\n",
       "      <td>20.315789</td>\n",
       "      <td>7.00</td>\n",
       "      <td>10.250</td>\n",
       "      <td>31.210526</td>\n",
       "      <td>60.583333</td>\n",
       "      <td>1.750</td>\n",
       "      <td>0.575</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.343333</td>\n",
       "      <td>1.133333</td>\n",
       "      <td>0.210000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>9.5</td>\n",
       "      <td>0.433333</td>\n",
       "      <td>20.684211</td>\n",
       "      <td>42.368421</td>\n",
       "      <td>10.00</td>\n",
       "      <td>15.250</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>27.583333</td>\n",
       "      <td>3.675</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.416667</td>\n",
       "      <td>1.213333</td>\n",
       "      <td>0.206667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>11.6</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>37.421053</td>\n",
       "      <td>54.052632</td>\n",
       "      <td>12.25</td>\n",
       "      <td>16.625</td>\n",
       "      <td>111.578947</td>\n",
       "      <td>12.583333</td>\n",
       "      <td>5.960</td>\n",
       "      <td>1.440</td>\n",
       "      <td>1.58</td>\n",
       "      <td>1.543333</td>\n",
       "      <td>1.366667</td>\n",
       "      <td>0.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>11.6</td>\n",
       "      <td>0.511111</td>\n",
       "      <td>31.736842</td>\n",
       "      <td>55.526316</td>\n",
       "      <td>9.00</td>\n",
       "      <td>9.750</td>\n",
       "      <td>104.052632</td>\n",
       "      <td>13.416667</td>\n",
       "      <td>5.100</td>\n",
       "      <td>1.280</td>\n",
       "      <td>1.86</td>\n",
       "      <td>1.483333</td>\n",
       "      <td>1.306667</td>\n",
       "      <td>0.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>11.9</td>\n",
       "      <td>0.422222</td>\n",
       "      <td>22.736842</td>\n",
       "      <td>48.368421</td>\n",
       "      <td>8.00</td>\n",
       "      <td>12.125</td>\n",
       "      <td>83.263158</td>\n",
       "      <td>23.166667</td>\n",
       "      <td>3.675</td>\n",
       "      <td>0.725</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1.436667</td>\n",
       "      <td>1.256667</td>\n",
       "      <td>0.183333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "magnitud      1         6          7          8      9       10          12  \\\n",
       "fecha                                                                         \n",
       "2010-01-01   7.9  0.322222   7.105263  20.315789   7.00  10.250   31.210526   \n",
       "2010-01-02   9.5  0.433333  20.684211  42.368421  10.00  15.250   74.000000   \n",
       "2010-01-03  11.6  0.555556  37.421053  54.052632  12.25  16.625  111.578947   \n",
       "2010-01-04  11.6  0.511111  31.736842  55.526316   9.00   9.750  104.052632   \n",
       "2010-01-05  11.9  0.422222  22.736842  48.368421   8.00  12.125   83.263158   \n",
       "\n",
       "magnitud           14     20     30    35        42        43        44  \n",
       "fecha                                                                    \n",
       "2010-01-01  60.583333  1.750  0.575  1.00  1.343333  1.133333  0.210000  \n",
       "2010-01-02  27.583333  3.675  1.000  1.10  1.416667  1.213333  0.206667  \n",
       "2010-01-03  12.583333  5.960  1.440  1.58  1.543333  1.366667  0.180000  \n",
       "2010-01-04  13.416667  5.100  1.280  1.86  1.483333  1.306667  0.180000  \n",
       "2010-01-05  23.166667  3.675  0.725  1.60  1.436667  1.256667  0.183333  "
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_avg_emissions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create list with name of particles and rename columns\n",
    "particles = ['so2', 'co','no','no2','pm2_5','pm10','nox','o3','tol','ben','ebe','tch','ch4','nmhc']\n",
    "\n",
    "df_avg_emissions.columns = particles\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert index to datetime\n",
    "df_avg_emissions.index = pd.to_datetime(df_avg_emissions.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "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>so2</th>\n",
       "      <th>co</th>\n",
       "      <th>no</th>\n",
       "      <th>no2</th>\n",
       "      <th>pm2_5</th>\n",
       "      <th>pm10</th>\n",
       "      <th>nox</th>\n",
       "      <th>o3</th>\n",
       "      <th>tol</th>\n",
       "      <th>ben</th>\n",
       "      <th>ebe</th>\n",
       "      <th>tch</th>\n",
       "      <th>ch4</th>\n",
       "      <th>nmhc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fecha</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>7.9</td>\n",
       "      <td>0.322222</td>\n",
       "      <td>7.105263</td>\n",
       "      <td>20.315789</td>\n",
       "      <td>7.00</td>\n",
       "      <td>10.250</td>\n",
       "      <td>31.210526</td>\n",
       "      <td>60.583333</td>\n",
       "      <td>1.750</td>\n",
       "      <td>0.575</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.343333</td>\n",
       "      <td>1.133333</td>\n",
       "      <td>0.210000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-02</th>\n",
       "      <td>9.5</td>\n",
       "      <td>0.433333</td>\n",
       "      <td>20.684211</td>\n",
       "      <td>42.368421</td>\n",
       "      <td>10.00</td>\n",
       "      <td>15.250</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>27.583333</td>\n",
       "      <td>3.675</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.416667</td>\n",
       "      <td>1.213333</td>\n",
       "      <td>0.206667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>11.6</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>37.421053</td>\n",
       "      <td>54.052632</td>\n",
       "      <td>12.25</td>\n",
       "      <td>16.625</td>\n",
       "      <td>111.578947</td>\n",
       "      <td>12.583333</td>\n",
       "      <td>5.960</td>\n",
       "      <td>1.440</td>\n",
       "      <td>1.58</td>\n",
       "      <td>1.543333</td>\n",
       "      <td>1.366667</td>\n",
       "      <td>0.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-04</th>\n",
       "      <td>11.6</td>\n",
       "      <td>0.511111</td>\n",
       "      <td>31.736842</td>\n",
       "      <td>55.526316</td>\n",
       "      <td>9.00</td>\n",
       "      <td>9.750</td>\n",
       "      <td>104.052632</td>\n",
       "      <td>13.416667</td>\n",
       "      <td>5.100</td>\n",
       "      <td>1.280</td>\n",
       "      <td>1.86</td>\n",
       "      <td>1.483333</td>\n",
       "      <td>1.306667</td>\n",
       "      <td>0.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-05</th>\n",
       "      <td>11.9</td>\n",
       "      <td>0.422222</td>\n",
       "      <td>22.736842</td>\n",
       "      <td>48.368421</td>\n",
       "      <td>8.00</td>\n",
       "      <td>12.125</td>\n",
       "      <td>83.263158</td>\n",
       "      <td>23.166667</td>\n",
       "      <td>3.675</td>\n",
       "      <td>0.725</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1.436667</td>\n",
       "      <td>1.256667</td>\n",
       "      <td>0.183333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             so2        co         no        no2  pm2_5    pm10         nox  \\\n",
       "fecha                                                                         \n",
       "2010-01-01   7.9  0.322222   7.105263  20.315789   7.00  10.250   31.210526   \n",
       "2010-01-02   9.5  0.433333  20.684211  42.368421  10.00  15.250   74.000000   \n",
       "2010-01-03  11.6  0.555556  37.421053  54.052632  12.25  16.625  111.578947   \n",
       "2010-01-04  11.6  0.511111  31.736842  55.526316   9.00   9.750  104.052632   \n",
       "2010-01-05  11.9  0.422222  22.736842  48.368421   8.00  12.125   83.263158   \n",
       "\n",
       "                   o3    tol    ben   ebe       tch       ch4      nmhc  \n",
       "fecha                                                                    \n",
       "2010-01-01  60.583333  1.750  0.575  1.00  1.343333  1.133333  0.210000  \n",
       "2010-01-02  27.583333  3.675  1.000  1.10  1.416667  1.213333  0.206667  \n",
       "2010-01-03  12.583333  5.960  1.440  1.58  1.543333  1.366667  0.180000  \n",
       "2010-01-04  13.416667  5.100  1.280  1.86  1.483333  1.306667  0.180000  \n",
       "2010-01-05  23.166667  3.675  0.725  1.60  1.436667  1.256667  0.183333  "
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_avg_emissions.head()"
   ]
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}