AN_ALL-Absolute_totals.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pylab as plt\n",
    "import sys\n",
    "sys.path.append('/Users/denismariano/pcloud/mypy')\n",
    "import xuleta as xu\n",
    "from scipy import stats\n",
    "from functools import reduce\n",
    "import os\n",
    "month_names = pd.date_range(start='2016-01-01', periods=12, freq='MS').strftime('%b')\n",
    "\n",
    "\n",
    "#for SQL\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql://denis:_________@localhost:5432/drought')\n",
    "fout = '/Users/denismariano/pcloud/PB_carlos/Document/elsevier_sample/images/'#mac\n",
    "fout = '/home/denis/pcloud/PB_carlos/Document/elsevier_sample/images/' #ubuntu"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://stats.stackexchange.com/questions/235039/statsmodels-clarification-on-varp-results-attribute-results-forecast-interva"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#FUNCTION to calculate weighted average\n",
    "def weightaverage(var,lista):\n",
    "    '''\n",
    "        var = dataframe\n",
    "        lista = group of columns\n",
    "        ux = upper extreme value - it can be set to None \n",
    "        lx = lower extreme value - it can be set to None\n",
    "    '''\n",
    "    import pandas as pd\n",
    "    pd.options.mode.chained_assignment = None #error omit\n",
    "    a = aux[aux['geocodig_m'].isin(lista)]\n",
    "    a['w'] = a.area/sum(a.area)\n",
    "    a = a.drop(['sigla','nome_munic','area'],axis=1)\n",
    "    a.T.to_csv('Xaux.csv',header=False,index=None)\n",
    "    aw = pd.read_csv('Xaux.csv')\n",
    "    os.remove('Xaux.csv')\n",
    "    aw = aw.sort_index(axis=1)\n",
    "    l2 =  list(map(float, aw.columns))\n",
    "\n",
    "#weight values and get the weighted average\n",
    "    var_aw = pd.DataFrame(var[l2].values*aw.values, columns=aw.columns,index=var[l2].index)\n",
    "    var_final = var_aw.sum(axis=1,skipna=True)\n",
    "    var_final.index = pd.DatetimeIndex(var_final.index)\n",
    "    #var_final.columns = ['values']\n",
    "    return var_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def piv_table(var,lista,resample='M'):\n",
    "    region_var = weightaverage(var,lista)\n",
    "    region_var = pd.DataFrame(region_var)\n",
    "    region_var.columns = ['values']\n",
    "    region_var = region_var.resample('M').mean()\n",
    "\n",
    "    region_var['year'] = region_var.index.year\n",
    "    region_var['month'] = region_var.index.month\n",
    "    region_var_piv = region_var.pivot(index='year', columns='month', values='values')\n",
    "    region_var_piv.columns = month_names\n",
    "    return region_var_piv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cern = [2300705,2304277,2306009,2306702,2306801,2306900,2307601,2308708,2311231,2311504,2312502,2313104,2400505,\n",
    "         2400604,2400901,2401008,2401305,2401453,2402303,2403707,2403905,2404002,2404309,2404903,2405207,2405603,\n",
    "         2405900,2406007,2406908,2407005,2407252,2407302,2407401,2407609,2408003,2408409,2408607,2409308,2409407,\n",
    "         2410009,2410207,2410603,2410702,2410801,2411007,2411908,2413409,2413557,2413607,2413805,2414100,2414308,\n",
    "         2414456,2414506,2414605,2414902,2502003,2502300,2502805,2502904,2504306,2507408,2509370,2510907,2512036,\n",
    "         2512804,2513208,2513653,2513901,2514651,2516904]\n",
    "nd6 = [2300705,2306702,2306801,2306900,2308708] #\n",
    "nd5 = [2301505,2305605,2313302]\n",
    "nd4 = [2601607,2603009,2605707,2607406,2610400,2611002,2612208]\n",
    "nd3 = [2400109,2402402,2403004,2403103,2403400,2408904,2501708,2503407,2503902,2504702,2504074,\n",
    "      2504850,2505006,2506202,2506509,2501105,2510303,2510659,2510808,2511103,2511400,2513406,\n",
    "       2513851,2514107,2514701,2514800,2514909,2515401,2515500,2516508,2517100] #2516755\n",
    "nd2 = [2302800,2306108,2308377,2312205]\n",
    "nd1 = [2201309,2202901,2204402,2206605,2208700,2208858,2209757]\n",
    "pi1 = [2202307,2202505,2205532,2210953,2201988,2203800,2205102,2205359,2207355,2208874]\n",
    "pi2 = [2202109,2202778,2203859,2204907,2207009,2209104,2209500,2210805,2211506,2211704]\n",
    "ba1 = [2903805,2911907,2912608,2919009,2920809,2927200,2901304,2915007,2922854]\n",
    "ba2 = [2915353,2918506,2929255,2923357,2921401,2917508] \n",
    "al1 = [2600500,2606507,2607505,2700706,2700904,2701209,2701605,2701803,2702355,2702504,2703304,\n",
    "       2703403,2704401,2704609,2705408,2705705,2705804,2706000,2706109,2706208,2706406,2707107,\n",
    "       2707206,2708006,2708402,2708956]\n",
    "\n",
    "lr = [nd1,nd2,nd3,nd4,nd5,nd6,pi1,pi2,ba1,ba2,cern,al1]\n",
    "lrn = ['nd1','nd2','nd3','nd4','nd5','nd6','pi1','pi2','ba1','ba2','cern','al1']\n",
    "\n",
    "lr = [nd1,nd2,nd3,nd4,nd5,pi1+pi2,ba1+ba2,cern]\n",
    "lrn = ['nd1','nd2','nd3','nd4','nd5','pi1+pi2','ba1+ba2','cern+nd6']"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%time\n",
    "#FROM SQL Database\n",
    "# Data ananlysis V2 - resubmission\n",
    "lai = pd.read_sql(\"SELECT * FROM consultas_br.lai_neb\",engine).pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()*0.01\n",
    "\n",
    "et = pd.read_sql(\"SELECT * FROM consultas_br.et_neb\",engine).pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()*0.1\n",
    "albedo = pd.read_sql(\"SELECT * FROM consultas_br.albedo_neb\",engine).pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()*0.001\n",
    "prec = pd.read_sql(\"SELECT * FROM consultas_br.chirps_prec_muni\",engine).pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()\n",
    "aux = pd.read_sql(\"SELECT * FROM consultas_br.aux\",engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 6.31 s, sys: 472 ms, total: 6.78 s\n",
      "Wall time: 6.75 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "datafolder = \"../SQL/\"\n",
    "# Data ananlysis V2 - CSV version - resubmission\n",
    "lai = pd.read_csv(datafolder+\"drought_consultas_br_lai_neb.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()*0.01\n",
    "\n",
    "et = pd.read_csv(datafolder+\"drought_consultas_br_et_neb.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()*0.1\n",
    "albedo = pd.read_csv(datafolder+\"drought_consultas_br_albedo_neb.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()*0.001\n",
    "prec = pd.read_csv(datafolder+\"drought_consultas_br_chirps_prec_muni.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()\n",
    "\n",
    "aux = pd.read_csv(datafolder+\"drought_consultas_br_aux.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "preca = pd.read_csv(datafolder+\"drought_consultas_br_prec_abs_neb.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "datafolder = \"../SQL/\"\n",
    "\n",
    "prec = pd.read_csv(datafolder+\"drought_consultas_br_chirps_prec_muni.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()\n",
    "zprec = pd.read_csv(datafolder+\"drought_consultas_br_chirps_zscore_muni.csv\").pivot_table(index=\"acquisition\",\n",
    "                   columns=\"geocodig_m\",\n",
    "                   values=\"mean\").interpolate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dates = ['2002-08','2016-12']\n",
    "\n",
    "def construir(reg,dates,w=1):\n",
    "    '''\n",
    "        w: int, rolling mean window\n",
    "    '''\n",
    "    if w == 1:\n",
    "        e = weightaverage(et,reg).loc[dates[0]:dates[1]].to_frame()\n",
    "        l = weightaverage(lai,reg).loc[dates[0]:dates[1]].to_frame()\n",
    "        a = weightaverage(albedo,reg).loc[dates[0]:dates[1]].to_frame()\n",
    "        #p = weightaverage(prec,reg).loc[dates[0]:dates[1]].to_frame()\n",
    "        pp = weightaverage(preca,reg).loc[dates[0]:dates[1]].to_frame()\n",
    "    if w > 1:\n",
    "        e = weightaverage(et,reg).loc[dates[0]:dates[1]].rolling(w).mean().to_frame().fillna(method='bfill')\n",
    "        l = weightaverage(lai,reg).loc[dates[0]:dates[1]].rolling(w).mean().to_frame().fillna(method='bfill')\n",
    "        a = weightaverage(albedo,reg).loc[dates[0]:dates[1]].rolling(w).mean().to_frame().fillna(method='bfill')\n",
    "        #p = weightaverage(prec,reg).loc[dates[0]:dates[1]].rolling(w).mean().to_frame().fillna(method='bfill')\n",
    "        pp = weightaverage(preca,reg).loc[dates[0]:dates[1]].rolling(w).mean().to_frame().fillna(method='bfill')\n",
    "    dfs = [e,l,a,pp]\n",
    "    final = reduce(lambda left,right: pd.merge(left,right, how='outer',\n",
    "                                               right_index=True,left_index=True), dfs)\n",
    "    final.columns = [['ET','LAI','Albedo','Prec']]\n",
    "    final = final.interpolate('values',limit_direction='both')\n",
    "    return final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ET', 'LAI', 'Albedo', 'Prec'], dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "w = 4\n",
    "df_c = construir(cern,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_1 = construir(nd1,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_2 = construir(nd2,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_3 = construir(nd3,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_4 = construir(nd4,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_5 = construir(nd5,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_b = construir(ba1+ba2,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_p = construir(pi1+pi2,dates,w=w).resample('W').mean().loc['2002-08':'2016-12']\n",
    "df_c.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "regs = [df_1,df_2,df_3,df_4,df_5,df_c,df_b,df_p]\n",
    "regsn = ['ND1','ND2','ND3','ND4','ND5','ND6+CERN','BA1+BA2','PI1+PI2']\n",
    "#df = pd.DataFrame(columns = ['%ET','%LAI','%Albedo','%Prec'])\n",
    "df1 = pd.DataFrame(columns = ['ET','LAI','Albedo','Prec'])\n",
    "df2 = df1.copy()\n",
    "\n",
    "for i,j in zip(regs,regsn):\n",
    "    teste = i.groupby(by=[i.index.year]).aggregate(np.nanmean)\n",
    "    result = (teste.loc[2003:2017].median(axis=0)-teste.loc[2003:2011].median(axis=0))*100/teste.loc[2003:2011].median(axis=0)\n",
    "    #df.loc[j] = np.round(np.asarray(result),2)\n",
    "    df1.loc[j] = np.asarray(teste.loc[2003:2011].median(axis=0))\n",
    "    df2.loc[j] = np.asarray(teste.loc[2003:2017].median(axis=0))\n",
    "#df1['1Prec'] = df1['1Prec']*12\n",
    "#df2['2Prec'] = df2['2Prec']*12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "regs = [df_1,df_2,df_3,df_4,df_5,df_c,df_b,df_p]\n",
    "regsn = ['ND1','ND2','ND3','ND4','ND5','ND6+CERN','BA1+BA2','PI1+PI2']\n",
    "#df = pd.DataFrame(columns = ['%ET','%LAI','%Albedo','%Prec'])\n",
    "df1 = pd.DataFrame(columns = ['ET','LAI','Albedo','Prec'])\n",
    "df2 = df1.copy()\n",
    "for i,j in zip(regs,regsn):\n",
    "    #FILL THEM ALL, Precipitation is valid\n",
    "    mensal = i.groupby(by=[i.index.year,i.index.month]).aggregate(np.nanmean)\n",
    "    anual = mensal.groupby(level=0).aggregate(np.sum)\n",
    "    df1.loc[j] = np.asarray(anual.loc[2003:2011].median(axis=0))\n",
    "    df2.loc[j] = np.asarray(anual.loc[2003:2017].median(axis=0))\n",
    "    \n",
    "    #FILL ET\n",
    "    mensal = i.groupby(by=[i.index.year,i.index.month]).aggregate(np.nansum)\n",
    "    anual = mensal.groupby(level=0).aggregate(np.sum)\n",
    "    df1.loc[j].ix['ET'] = anual.loc[2003:2011].median(axis=0)[0]#0 is ET\n",
    "    df2.loc[j].ix['ET'] = anual.loc[2003:2017].median(axis=0)[0]\n",
    "    \n",
    "    #albedo and LAI\n",
    "    mensal = i.groupby(by=[i.index.year,i.index.month]).aggregate(np.nanmean)\n",
    "    anual = mensal.groupby(level=0).aggregate(np.mean)\n",
    "    df1.loc[j].ix['LAI'] = anual.loc[2003:2011].median(axis=0)[1]\n",
    "    df2.loc[j].ix['LAI'] = anual.loc[2003:2017].median(axis=0)[1]\n",
    "    \n",
    "    df1.loc[j].ix['Albedo'] = anual.loc[2003:2011].median(axis=0)[2]\n",
    "    df2.loc[j].ix['Albedo'] = anual.loc[2003:2017].median(axis=0)[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ET</th>\n",
       "      <th>LAI</th>\n",
       "      <th>Albedo</th>\n",
       "      <th>Prec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ND1</th>\n",
       "      <td>626.500572</td>\n",
       "      <td>0.171882</td>\n",
       "      <td>0.149442</td>\n",
       "      <td>1110.941944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND2</th>\n",
       "      <td>615.146697</td>\n",
       "      <td>0.173757</td>\n",
       "      <td>0.161047</td>\n",
       "      <td>788.512972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND3</th>\n",
       "      <td>470.716738</td>\n",
       "      <td>0.139301</td>\n",
       "      <td>0.165199</td>\n",
       "      <td>666.110903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND4</th>\n",
       "      <td>417.859840</td>\n",
       "      <td>0.217185</td>\n",
       "      <td>0.149031</td>\n",
       "      <td>538.488553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND5</th>\n",
       "      <td>592.623480</td>\n",
       "      <td>0.132293</td>\n",
       "      <td>0.162853</td>\n",
       "      <td>596.679231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND6+CERN</th>\n",
       "      <td>598.370741</td>\n",
       "      <td>0.174007</td>\n",
       "      <td>0.164957</td>\n",
       "      <td>802.560801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BA1+BA2</th>\n",
       "      <td>532.501691</td>\n",
       "      <td>0.151829</td>\n",
       "      <td>0.160728</td>\n",
       "      <td>592.447639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PI1+PI2</th>\n",
       "      <td>674.239938</td>\n",
       "      <td>0.190622</td>\n",
       "      <td>0.148860</td>\n",
       "      <td>853.580932</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ET       LAI    Albedo         Prec\n",
       "ND1       626.500572  0.171882  0.149442  1110.941944\n",
       "ND2       615.146697  0.173757  0.161047   788.512972\n",
       "ND3       470.716738  0.139301  0.165199   666.110903\n",
       "ND4       417.859840  0.217185  0.149031   538.488553\n",
       "ND5       592.623480  0.132293  0.162853   596.679231\n",
       "ND6+CERN  598.370741  0.174007  0.164957   802.560801\n",
       "BA1+BA2   532.501691  0.151829  0.160728   592.447639\n",
       "PI1+PI2   674.239938  0.190622  0.148860   853.580932"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ET</th>\n",
       "      <th>LAI</th>\n",
       "      <th>Albedo</th>\n",
       "      <th>Prec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ND1</th>\n",
       "      <td>631.830186</td>\n",
       "      <td>0.165459</td>\n",
       "      <td>0.150046</td>\n",
       "      <td>1039.152067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND2</th>\n",
       "      <td>472.092890</td>\n",
       "      <td>0.151243</td>\n",
       "      <td>0.160355</td>\n",
       "      <td>647.305636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND3</th>\n",
       "      <td>354.455530</td>\n",
       "      <td>0.114107</td>\n",
       "      <td>0.166246</td>\n",
       "      <td>511.968360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND4</th>\n",
       "      <td>326.458218</td>\n",
       "      <td>0.204787</td>\n",
       "      <td>0.150553</td>\n",
       "      <td>478.878853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND5</th>\n",
       "      <td>487.185419</td>\n",
       "      <td>0.116963</td>\n",
       "      <td>0.163653</td>\n",
       "      <td>497.986984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND6+CERN</th>\n",
       "      <td>440.181882</td>\n",
       "      <td>0.159715</td>\n",
       "      <td>0.167925</td>\n",
       "      <td>670.822397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BA1+BA2</th>\n",
       "      <td>508.649759</td>\n",
       "      <td>0.142248</td>\n",
       "      <td>0.161267</td>\n",
       "      <td>577.535201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PI1+PI2</th>\n",
       "      <td>585.631022</td>\n",
       "      <td>0.184154</td>\n",
       "      <td>0.148639</td>\n",
       "      <td>785.967787</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ET       LAI    Albedo         Prec\n",
       "ND1       631.830186  0.165459  0.150046  1039.152067\n",
       "ND2       472.092890  0.151243  0.160355   647.305636\n",
       "ND3       354.455530  0.114107  0.166246   511.968360\n",
       "ND4       326.458218  0.204787  0.150553   478.878853\n",
       "ND5       487.185419  0.116963  0.163653   497.986984\n",
       "ND6+CERN  440.181882  0.159715  0.167925   670.822397\n",
       "BA1+BA2   508.649759  0.142248  0.161267   577.535201\n",
       "PI1+PI2   585.631022  0.184154  0.148639   785.967787"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ET</th>\n",
       "      <th>LAI</th>\n",
       "      <th>Albedo</th>\n",
       "      <th>Prec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ND1</th>\n",
       "      <td>0.850696</td>\n",
       "      <td>-3.736846</td>\n",
       "      <td>0.403937</td>\n",
       "      <td>-6.462073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND2</th>\n",
       "      <td>-23.255234</td>\n",
       "      <td>-12.957340</td>\n",
       "      <td>-0.429756</td>\n",
       "      <td>-17.908055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND3</th>\n",
       "      <td>-24.698762</td>\n",
       "      <td>-18.086096</td>\n",
       "      <td>0.633558</td>\n",
       "      <td>-23.140673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND4</th>\n",
       "      <td>-21.873751</td>\n",
       "      <td>-5.708718</td>\n",
       "      <td>1.021385</td>\n",
       "      <td>-11.069817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND5</th>\n",
       "      <td>-17.791745</td>\n",
       "      <td>-11.588163</td>\n",
       "      <td>0.491433</td>\n",
       "      <td>-16.540252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND6+CERN</th>\n",
       "      <td>-26.436597</td>\n",
       "      <td>-8.213556</td>\n",
       "      <td>1.799029</td>\n",
       "      <td>-16.414757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BA1+BA2</th>\n",
       "      <td>-4.479222</td>\n",
       "      <td>-6.310245</td>\n",
       "      <td>0.334968</td>\n",
       "      <td>-2.517090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PI1+PI2</th>\n",
       "      <td>-13.142045</td>\n",
       "      <td>-3.393066</td>\n",
       "      <td>-0.148054</td>\n",
       "      <td>-7.921117</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ET        LAI    Albedo       Prec\n",
       "ND1        0.850696  -3.736846  0.403937  -6.462073\n",
       "ND2      -23.255234 -12.957340 -0.429756 -17.908055\n",
       "ND3      -24.698762 -18.086096  0.633558 -23.140673\n",
       "ND4      -21.873751  -5.708718  1.021385 -11.069817\n",
       "ND5      -17.791745 -11.588163  0.491433 -16.540252\n",
       "ND6+CERN -26.436597  -8.213556  1.799029 -16.414757\n",
       "BA1+BA2   -4.479222  -6.310245  0.334968  -2.517090\n",
       "PI1+PI2  -13.142045  -3.393066 -0.148054  -7.921117"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pc = (df2-df1)*100/df1\n",
    "pc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "144.45319803871149"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#PRECIPITATION\n",
    "i = df_c\n",
    "mensal = i.groupby(by=[i.index.year,i.index.month]).aggregate(np.nanmean)\n",
    "anual = mensal.groupby(level=0).aggregate(np.sum)\n",
    "anual.loc[2003:2011].median(axis=0)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ET        110.058781\n",
       "LAI         1.916584\n",
       "Albedo      2.015101\n",
       "Prec      670.822397\n",
       "dtype: float64"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "anual.loc[2003:2017].median(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ET         598.370741\n",
       "LAI          8.320581\n",
       "Albedo       7.767768\n",
       "Prec      3180.105315\n",
       "dtype: float64"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#ET\n",
    "i = df_c\n",
    "mensal = i.groupby(by=[i.index.year,i.index.month]).aggregate(np.nansum)\n",
    "anual = mensal.groupby(level=0).aggregate(np.sum)\n",
    "anual.loc[2003:2011].median(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ET         440.181882\n",
       "LAI          7.437853\n",
       "Albedo       7.917252\n",
       "Prec      2681.376146\n",
       "dtype: float64"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "anual.loc[2003:2017].median(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ET        12.037767\n",
       "LAI        0.174007\n",
       "Albedo     0.164957\n",
       "Prec      66.880067\n",
       "dtype: float64"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#albedo e lai\n",
    "i = df_c\n",
    "mensal = i.groupby(by=[i.index.year,i.index.month]).aggregate(np.nanmean)\n",
    "anual = mensal.groupby(level=0).aggregate(np.mean)\n",
    "anual.loc[2003:2011].median(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\begin{tabular}{lrrrr}\n",
      "\\toprule\n",
      "{} &    \\%ET &   \\%LAI &  \\%Albedo &  \\%Prec \\\\\n",
      "\\midrule\n",
      "ND1      &   0.66 &  -3.88 &     0.40 &  -6.91 \\\\\n",
      "ND2      & -27.43 & -14.89 &    -0.43 & -21.81 \\\\\n",
      "ND3      & -34.99 & -22.08 &     0.63 & -30.11 \\\\\n",
      "ND4      & -29.45 &  -6.05 &     1.01 & -12.45 \\\\\n",
      "ND5      & -20.99 & -13.11 &     0.49 & -19.82 \\\\\n",
      "ND6+CERN & -31.25 &  -8.95 &     1.77 & -19.64 \\\\\n",
      "BA1+BA2  &  -7.18 &  -6.74 &     0.33 &  -2.58 \\\\\n",
      "PI1+PI2  & -20.30 &  -3.51 &    -0.15 &  -8.60 \\\\\n",
      "\\bottomrule\n",
      "\\end{tabular}\n",
      "\n",
      "\n",
      "\n",
      "\\begin{tabular}{lrrrr}\n",
      "\\toprule\n",
      "{} &         1ET &       1LAI &   1Albedo &        1Prec \\\\\n",
      "\\midrule\n",
      "ND1      &  626.500572 &   8.007205 &  7.031542 &  4163.680713 \\\\\n",
      "ND2      &  615.146697 &   8.178722 &  7.537709 &  3104.902347 \\\\\n",
      "ND3      &  470.716738 &   6.509095 &  7.757965 &  2631.759818 \\\\\n",
      "ND4      &  417.859840 &  10.177150 &  7.004719 &  2048.965474 \\\\\n",
      "ND5      &  592.623480 &   6.216716 &  7.541244 &  2472.798105 \\\\\n",
      "ND6+CERN &  598.370741 &   8.320581 &  7.767768 &  3180.105315 \\\\\n",
      "BA1+BA2  &  532.501691 &   7.130668 &  7.545224 &  2308.102709 \\\\\n",
      "PI1+PI2  &  674.239938 &   8.909052 &  7.010007 &  3359.403807 \\\\\n",
      "\\bottomrule\n",
      "\\end{tabular}\n",
      "\n",
      "\n",
      "\n",
      "\\begin{tabular}{lrrrr}\n",
      "\\toprule\n",
      "{} &         2ET &      2LAI &   2Albedo &        2Prec \\\\\n",
      "\\midrule\n",
      "ND1      &  631.830186 &  7.767699 &  7.059050 &  4074.393296 \\\\\n",
      "ND2      &  472.092890 &  7.101201 &  7.501312 &  2618.911799 \\\\\n",
      "ND3      &  354.455530 &  5.437604 &  7.857557 &  1956.247137 \\\\\n",
      "ND4      &  326.458218 &  9.812034 &  7.178063 &  1907.623773 \\\\\n",
      "ND5      &  487.185419 &  5.482763 &  7.689003 &  1931.477649 \\\\\n",
      "ND6+CERN &  440.181882 &  7.437853 &  7.917252 &  2681.376146 \\\\\n",
      "BA1+BA2  &  508.649759 &  6.693008 &  7.590019 &  2240.735463 \\\\\n",
      "PI1+PI2  &  585.631022 &  8.411640 &  7.031518 &  3035.208109 \\\\\n",
      "\\bottomrule\n",
      "\\end{tabular}\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(df.to_latex(bold_rows=False))\n",
    "print('\\n')\n",
    "print(df1.to_latex(bold_rows=False))\n",
    "print('\\n')\n",
    "print(df2.to_latex(bold_rows=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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}