1. Extracting data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CEPAGRI datasets extraction\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Import libs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Reading datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = pd.read_excel('1997.xlsx', sheetname=\"111\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Cod</th>\n",
       "      <th>Ano</th>\n",
       "      <th>Dia Juliano</th>\n",
       "      <th>Hora - minuto</th>\n",
       "      <th>Veloc. Instantânea do Vento a 5 metros (m/s)</th>\n",
       "      <th>Veloc. Instantânea do Vento a 2 metros (m/s)</th>\n",
       "      <th>Direção do Vento no instante da aquisição (°)</th>\n",
       "      <th>Radiação Incidente total de 10 leituras (KW/m2)</th>\n",
       "      <th>Radiação Refletida Total de 10 leituras (KW/m2)</th>\n",
       "      <th>Fluxo Positivo de calor no solo total de 10 leituras (W/m2)</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 25</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Unnamed: 27</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "      <th>Unnamed: 29</th>\n",
       "      <th>Unnamed: 30</th>\n",
       "      <th>Unnamed: 31</th>\n",
       "      <th>Unnamed: 32</th>\n",
       "      <th>Unnamed: 33</th>\n",
       "      <th>Unnamed: 34</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1618.00</td>\n",
       "      <td>1115.000</td>\n",
       "      <td>8.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>0.99</td>\n",
       "      <td>0.763</td>\n",
       "      <td>20.59</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>2.05</td>\n",
       "      <td>1115.000</td>\n",
       "      <td>17.72</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>3089.00</td>\n",
       "      <td>1422.000</td>\n",
       "      <td>189.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111</td>\n",
       "      <td>1997</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>2128.00</td>\n",
       "      <td>1406.000</td>\n",
       "      <td>153.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Cod   Ano  Dia Juliano  Hora - minuto  \\\n",
       "0  111  1997            1             10   \n",
       "1  111  1997            1             20   \n",
       "2  111  1997            1             30   \n",
       "3  111  1997            1             40   \n",
       "4  111  1997            1             50   \n",
       "\n",
       "   Veloc. Instantânea do Vento a 5 metros (m/s)  \\\n",
       "0                                       1618.00   \n",
       "1                                          0.99   \n",
       "2                                          2.05   \n",
       "3                                       3089.00   \n",
       "4                                       2128.00   \n",
       "\n",
       "   Veloc. Instantânea do Vento a 2 metros (m/s)  \\\n",
       "0                                      1115.000   \n",
       "1                                         0.763   \n",
       "2                                      1115.000   \n",
       "3                                      1422.000   \n",
       "4                                      1406.000   \n",
       "\n",
       "   Direção do Vento no instante da aquisição (°)  \\\n",
       "0                                           8.32   \n",
       "1                                          20.59   \n",
       "2                                          17.72   \n",
       "3                                         189.20   \n",
       "4                                         153.20   \n",
       "\n",
       "   Radiação Incidente total de 10 leituras (KW/m2)  \\\n",
       "0                                              0.0   \n",
       "1                                              0.0   \n",
       "2                                              0.0   \n",
       "3                                              0.0   \n",
       "4                                              0.0   \n",
       "\n",
       "   Radiação Refletida Total de 10 leituras (KW/m2)  \\\n",
       "0                                              0.0   \n",
       "1                                              0.0   \n",
       "2                                              0.0   \n",
       "3                                              0.0   \n",
       "4                                              0.0   \n",
       "\n",
       "   Fluxo Positivo de calor no solo total de 10 leituras (W/m2)     ...       \\\n",
       "0                                                0.0               ...        \n",
       "1                                                0.0               ...        \n",
       "2                                                0.0               ...        \n",
       "3                                                0.0               ...        \n",
       "4                                                0.0               ...        \n",
       "\n",
       "   Unnamed: 25  Unnamed: 26  Unnamed: 27  Unnamed: 28  Unnamed: 29  \\\n",
       "0          NaN          NaN          NaN          NaN          NaN   \n",
       "1          NaN          NaN          NaN          NaN          NaN   \n",
       "2          NaN          NaN          NaN          NaN          NaN   \n",
       "3          NaN          NaN          NaN          NaN          NaN   \n",
       "4          NaN          NaN          NaN          NaN          NaN   \n",
       "\n",
       "   Unnamed: 30  Unnamed: 31  Unnamed: 32  Unnamed: 33  Unnamed: 34  \n",
       "0          NaN          NaN          NaN          NaN          NaN  \n",
       "1          NaN          NaN          NaN          NaN          NaN  \n",
       "2          NaN          NaN          NaN          NaN          NaN  \n",
       "3          NaN          NaN          NaN          NaN          NaN  \n",
       "4          NaN          NaN          NaN          NaN          NaN  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show some data\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Auxiliar array with sheets names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1997',\n",
       " '1998',\n",
       " '1999',\n",
       " '2000',\n",
       " '2001',\n",
       " '2002',\n",
       " '2003',\n",
       " '2004',\n",
       " '2005',\n",
       " '2006',\n",
       " '2007',\n",
       " '2008',\n",
       " '2009',\n",
       " '2010',\n",
       " '2011',\n",
       " '2012',\n",
       " '2013',\n",
       " '2014',\n",
       " '2015',\n",
       " '2016',\n",
       " '2017',\n",
       " '2018']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "planilha = [str(1997+w) for w in range(0,22)]\n",
    "planilha"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For each excel file (111,222,265 and 333) we create a dataframe\n",
    "\n",
    "If there is a string named \"Unnamed\" it is droped\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data_111 = []\n",
    "for p in planilha:\n",
    "    name =p+'.xlsx'\n",
    "    data_111.append(pd.read_excel(name, sheetname=\"111\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in range(len(planilha)):\n",
    "    to_drop = []\n",
    "    for var in data_111[i].columns:\n",
    "        if(\"Unnamed\" in var):\n",
    "            to_drop.append(var)\n",
    "    data_111[i] = data_111[i].drop(to_drop, axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n",
      "18\n"
     ]
    }
   ],
   "source": [
    "for i in range(len(planilha)):\n",
    "    print(data_111[i].shape[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>DAY365</th>\n",
       "      <th>MINUTE2400</th>\n",
       "      <th>N_WNDVEL_5M</th>\n",
       "      <th>N_WNDVEL_2M</th>\n",
       "      <th>N_WNDDIR_DEGREE</th>\n",
       "      <th>N_INCRAD_10MSR</th>\n",
       "      <th>N_REFRAD_10MSR</th>\n",
       "      <th>N_PHTFLOW_10MSR</th>\n",
       "      <th>N_NHTFLOW_10MSR</th>\n",
       "      <th>N_AIRMTR_PCT10</th>\n",
       "      <th>N_AIRTMP_MED10</th>\n",
       "      <th>N_GRDTMP_3CM</th>\n",
       "      <th>N_GRDTMP_6CM</th>\n",
       "      <th>N_GRDTMP_UV</th>\n",
       "      <th>N_PRESSION_HPA</th>\n",
       "      <th>N_RAIN_SUM10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>2.56</td>\n",
       "      <td>1728.0</td>\n",
       "      <td>129.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-486.6</td>\n",
       "      <td>100.0</td>\n",
       "      <td>22.14</td>\n",
       "      <td>26.06</td>\n",
       "      <td>27.25</td>\n",
       "      <td>29.02</td>\n",
       "      <td>946.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>2403.00</td>\n",
       "      <td>1682.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-482.9</td>\n",
       "      <td>100.0</td>\n",
       "      <td>22.13</td>\n",
       "      <td>26.05</td>\n",
       "      <td>27.25</td>\n",
       "      <td>29.02</td>\n",
       "      <td>946.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1814.00</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>153.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-480.7</td>\n",
       "      <td>100.0</td>\n",
       "      <td>22.31</td>\n",
       "      <td>26.05</td>\n",
       "      <td>27.20</td>\n",
       "      <td>29.01</td>\n",
       "      <td>947.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>111</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>1952.00</td>\n",
       "      <td>1223.0</td>\n",
       "      <td>120.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-478.1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>22.47</td>\n",
       "      <td>26.06</td>\n",
       "      <td>27.30</td>\n",
       "      <td>29.16</td>\n",
       "      <td>946.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>111</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>1932.00</td>\n",
       "      <td>1345.0</td>\n",
       "      <td>135.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-476.4</td>\n",
       "      <td>100.0</td>\n",
       "      <td>22.62</td>\n",
       "      <td>26.15</td>\n",
       "      <td>27.35</td>\n",
       "      <td>29.25</td>\n",
       "      <td>947.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COD  YEAR  DAY365  MINUTE2400  N_WNDVEL_5M  N_WNDVEL_2M  N_WNDDIR_DEGREE  \\\n",
       "0  111  1998       1          10         2.56       1728.0            129.2   \n",
       "1  111  1998       1          20      2403.00       1682.0             90.0   \n",
       "2  111  1998       1          30      1814.00       1452.0            153.9   \n",
       "3  111  1998       1          40      1952.00       1223.0            120.6   \n",
       "4  111  1998       1          50      1932.00       1345.0            135.1   \n",
       "\n",
       "   N_INCRAD_10MSR  N_REFRAD_10MSR  N_PHTFLOW_10MSR  N_NHTFLOW_10MSR  \\\n",
       "0             0.0             0.0              0.0           -486.6   \n",
       "1             0.0             0.0              0.0           -482.9   \n",
       "2             0.0             0.0              0.0           -480.7   \n",
       "3             0.0             0.0              0.0           -478.1   \n",
       "4             0.0             0.0              0.0           -476.4   \n",
       "\n",
       "   N_AIRMTR_PCT10  N_AIRTMP_MED10  N_GRDTMP_3CM  N_GRDTMP_6CM  N_GRDTMP_UV  \\\n",
       "0           100.0           22.14         26.06         27.25        29.02   \n",
       "1           100.0           22.13         26.05         27.25        29.02   \n",
       "2           100.0           22.31         26.05         27.20        29.01   \n",
       "3           100.0           22.47         26.06         27.30        29.16   \n",
       "4           100.0           22.62         26.15         27.35        29.25   \n",
       "\n",
       "   N_PRESSION_HPA  N_RAIN_SUM10  \n",
       "0           946.0           0.0  \n",
       "1           946.0           0.0  \n",
       "2           947.0           0.0  \n",
       "3           946.0           0.0  \n",
       "4           947.0           0.0  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_111[1].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COD</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>DAY365</th>\n",
       "      <th>MINUTE2400</th>\n",
       "      <th>N_WNDVEL_5M</th>\n",
       "      <th>N_WNDVEL_2M</th>\n",
       "      <th>N_WNDDIR_DEGREE</th>\n",
       "      <th>N_INCRAD_10MSR</th>\n",
       "      <th>N_REFRAD_10MSR</th>\n",
       "      <th>N_PHTFLOW_10MSR</th>\n",
       "      <th>N_NHTFLOW_10MSR</th>\n",
       "      <th>N_AIRMTR_PCT10</th>\n",
       "      <th>N_AIRTMP_MED10</th>\n",
       "      <th>N_GRDTMP_3CM</th>\n",
       "      <th>N_GRDTMP_6CM</th>\n",
       "      <th>N_GRDTMP_UV</th>\n",
       "      <th>N_PRESSION_HPA</th>\n",
       "      <th>N_RAIN_SUM10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.473</td>\n",
       "      <td>1.296</td>\n",
       "      <td>49.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-6999.0</td>\n",
       "      <td>85.1</td>\n",
       "      <td>20.75</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>-0.045</td>\n",
       "      <td>941.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.383</td>\n",
       "      <td>1.285</td>\n",
       "      <td>9.52</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-6999.0</td>\n",
       "      <td>84.7</td>\n",
       "      <td>20.76</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>-0.045</td>\n",
       "      <td>941.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>111.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1.273</td>\n",
       "      <td>1.120</td>\n",
       "      <td>35.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-6999.0</td>\n",
       "      <td>84.7</td>\n",
       "      <td>20.76</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>6999.0</td>\n",
       "      <td>-0.046</td>\n",
       "      <td>941.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     COD    YEAR  DAY365  MINUTE2400  N_WNDVEL_5M  N_WNDVEL_2M  \\\n",
       "0  111.0  2018.0     1.0        10.0        1.473        1.296   \n",
       "1  111.0  2018.0     1.0        20.0        1.383        1.285   \n",
       "2  111.0  2018.0     1.0        30.0        1.273        1.120   \n",
       "\n",
       "   N_WNDDIR_DEGREE  N_INCRAD_10MSR  N_REFRAD_10MSR  N_PHTFLOW_10MSR  \\\n",
       "0            49.00             0.0             0.0              0.0   \n",
       "1             9.52             0.0             0.0              0.0   \n",
       "2            35.58             0.0             0.0              0.0   \n",
       "\n",
       "   N_NHTFLOW_10MSR  N_AIRMTR_PCT10  N_AIRTMP_MED10  N_GRDTMP_3CM  \\\n",
       "0          -6999.0            85.1           20.75        6999.0   \n",
       "1          -6999.0            84.7           20.76        6999.0   \n",
       "2          -6999.0            84.7           20.76        6999.0   \n",
       "\n",
       "   N_GRDTMP_6CM  N_GRDTMP_UV  N_PRESSION_HPA  N_RAIN_SUM10  \n",
       "0        6999.0       -0.045           941.0           0.0  \n",
       "1        6999.0       -0.045           941.0           0.0  \n",
       "2        6999.0       -0.046           941.0           0.0  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_111[21].head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data_111[21] = data_111[21][data_111[21].columns[:18]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns = ['COD', 'YEAR', 'DAY365', 'MINUTE2400', 'N_WNDVEL_5M', 'N_WNDVEL_2M','N_WNDDIR_DEGREE', 'N_INCRAD_10MSR', 'N_REFRAD_10MSR', 'N_PHTFLOW_10MSR', 'N_NHTFLOW_10MSR', 'N_AIRMTR_PCT10', 'N_AIRTMP_MED10', 'N_GRDTMP_3CM', 'N_GRDTMP_6CM', 'N_GRDTMP_UV', 'N_PRESSION_HPA', 'N_RAIN_SUM10']\n",
    "\n",
    "for i in range(len(planilha)):\n",
    "    data_111[i].columns = columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# data_222 = []\n",
    "# for p in planilha:\n",
    "#     name =p+'.xlsx'\n",
    "#     data_222.append(pd.read_excel(name, sheetname=\"222\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for i in range(len(planilha)):\n",
    "#     to_drop = []\n",
    "#     for var in data_222[i].columns:\n",
    "#         if(\"Unnamed\" in var):\n",
    "#             to_drop.append(var)\n",
    "#     data_222[i] = data_222[i].drop(to_drop, axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# data_265 = []\n",
    "# for p in planilha:\n",
    "#     name =p+'.xlsx'\n",
    "#     data_265.append(pd.read_excel(name, sheetname=\"265\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for i in range(len(planilha)):\n",
    "#     to_drop = []\n",
    "#     for var in data_265[i].columns:\n",
    "#         if(\"Unnamed\" in var):\n",
    "#             to_drop.append(var)\n",
    "#     data_265[i] = data_265[i].drop(to_drop, axis=1) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Code just to check shape of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in range(len(data_111)):\n",
    "    print(\"Base \", i, \" tem tamanho \", data_111[i].shape, \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for i in range(len(data_222)):\n",
    "#     print(\"Base \", i, \" tem tamanho \", data_222[i].shape, \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for i in range(len(data_333)):\n",
    "#     print(\"Base \", i, \" tem tamanho \", data_333[i].shape, \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for i in range(len(data_265)):\n",
    "#     print(\"Base \", i, \" tem tamanho \", data_265[i].shape, \"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Defining data to export as CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n"
     ]
    }
   ],
   "source": [
    "full_111 = data_111[0]\n",
    "\n",
    "for i in range(1,len(data_111)):\n",
    "    print(i)\n",
    "    full_111 = pd.concat([full_111[columns], data_111[i][columns]], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# full_222 = data_222[0]\n",
    "\n",
    "# for i in range(1,len(data_222)):\n",
    "#     full_222 = pd.concat([full_222, data_222[i]], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# full_222.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# full_333 = data_333[0]\n",
    "\n",
    "# for i in range(1,len(data_333)):\n",
    "#     full_333 = pd.concat([full_333, data_333[i]], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# full_333.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# full_265 = data_265[0]\n",
    "\n",
    "# for i in range(1,len(data_265)):\n",
    "#     full_265 = pd.concat([full_265, data_265[i]], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# full_265.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exporting data in CSV files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "full_111.to_csv(\"base_111.csv\", index = False)\n",
    "# full_222.to_csv(\"base_222.csv\", index = False)\n",
    "# full_333.to_csv(\"base_333.csv\", index = False)\n",
    "# full_265.to_csv(\"base_265.csv\", index = False)"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}