4_merge_climate_and_hydro_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "544fffbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "04c10bb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set periode of study\n",
    "start_date = '1981-06-01'\n",
    "end_date = '2010-12-31'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "32c15141",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load climate data\n",
    "pr = pd.read_csv('./climate_data/pr_fieldmean.csv')\n",
    "tmax = pd.read_csv('./climate_data/tmax_fieldmean.csv')\n",
    "tmin = pd.read_csv('./climate_data/tmin_fieldmean.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e95a4391",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename colums\n",
    "pr = pr.rename(columns={'time':'Date', '0': 'pr'})\n",
    "tmax = tmax.rename(columns={'time':'Date', '0': 'tmax'})\n",
    "tmin = tmin.rename(columns={'time':'Date', '0': 'tmin'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d941cfb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge pr, tmax and tmin by date column\n",
    "climate_data = pr.merge(tmax, on='Date').merge(tmin, on='Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4949b507",
   "metadata": {},
   "outputs": [],
   "source": [
    "# select time periode between 19810601 and 20101231\n",
    "climate_data = climate_data.loc[(climate_data['Date'] >= 19810601) & (climate_data['Date'] <= 20101231)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4985ee5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert time to datetime\n",
    "climate_data['Date'] = pd.to_datetime(climate_data['Date'], format='%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "812784ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reset index of climate data\n",
    "climate_data = climate_data.reset_index().drop('index', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "59f32a65",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load hydro data\n",
    "ansongo = pd.read_csv('./hydro_data/interpolate/ansongo.csv')\n",
    "kandadji = pd.read_csv('./hydro_data/interpolate/kandadji.csv')\n",
    "niamey = pd.read_csv('./hydro_data/interpolate/niamey.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "23450341",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge ansongo, kandadji and niamey by date column\n",
    "hydro_data = ansongo.merge(kandadji, on='Date').merge(niamey, on='Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1b5929b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define datetime of hydro data\n",
    "hydro_data['Date'] = pd.to_datetime(hydro_data['Date'], infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5ab52db2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge climate data and hydro by time column\n",
    "model_data = climate_data.merge(hydro_data, on='Date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2a3d0ecc",
   "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>Date</th>\n",
       "      <th>pr</th>\n",
       "      <th>tmax</th>\n",
       "      <th>tmin</th>\n",
       "      <th>Ansongo</th>\n",
       "      <th>Kandadji</th>\n",
       "      <th>Niamey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1981-06-01</td>\n",
       "      <td>0.525346</td>\n",
       "      <td>38.824417</td>\n",
       "      <td>27.352827</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.400000</td>\n",
       "      <td>5.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1981-06-02</td>\n",
       "      <td>0.732719</td>\n",
       "      <td>39.789036</td>\n",
       "      <td>27.593908</td>\n",
       "      <td>13.0</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>4.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1981-06-03</td>\n",
       "      <td>0.341014</td>\n",
       "      <td>39.171715</td>\n",
       "      <td>26.769451</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.600000</td>\n",
       "      <td>4.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1981-06-04</td>\n",
       "      <td>1.986175</td>\n",
       "      <td>39.452515</td>\n",
       "      <td>28.201244</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.100000</td>\n",
       "      <td>3.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1981-06-05</td>\n",
       "      <td>0.271889</td>\n",
       "      <td>39.426506</td>\n",
       "      <td>26.821142</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.900000</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10801</th>\n",
       "      <td>2010-12-27</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>33.876694</td>\n",
       "      <td>19.051850</td>\n",
       "      <td>1751.0</td>\n",
       "      <td>1841.915039</td>\n",
       "      <td>1669.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10802</th>\n",
       "      <td>2010-12-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>33.088223</td>\n",
       "      <td>18.405252</td>\n",
       "      <td>1753.0</td>\n",
       "      <td>1851.276978</td>\n",
       "      <td>1677.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10803</th>\n",
       "      <td>2010-12-29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>30.650715</td>\n",
       "      <td>17.136805</td>\n",
       "      <td>1752.0</td>\n",
       "      <td>1851.276978</td>\n",
       "      <td>1684.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10804</th>\n",
       "      <td>2010-12-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>29.456808</td>\n",
       "      <td>16.068770</td>\n",
       "      <td>1749.0</td>\n",
       "      <td>1851.276978</td>\n",
       "      <td>1684.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10805</th>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>29.198921</td>\n",
       "      <td>16.231855</td>\n",
       "      <td>1743.0</td>\n",
       "      <td>1860.637939</td>\n",
       "      <td>1684.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10806 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date        pr       tmax       tmin  Ansongo     Kandadji  \\\n",
       "0     1981-06-01  0.525346  38.824417  27.352827     14.0    20.400000   \n",
       "1     1981-06-02  0.732719  39.789036  27.593908     13.0    17.000000   \n",
       "2     1981-06-03  0.341014  39.171715  26.769451     13.0    14.600000   \n",
       "3     1981-06-04  1.986175  39.452515  28.201244     13.0    14.100000   \n",
       "4     1981-06-05  0.271889  39.426506  26.821142     13.0    12.900000   \n",
       "...          ...       ...        ...        ...      ...          ...   \n",
       "10801 2010-12-27  0.000000  33.876694  19.051850   1751.0  1841.915039   \n",
       "10802 2010-12-28  0.000000  33.088223  18.405252   1753.0  1851.276978   \n",
       "10803 2010-12-29  0.000000  30.650715  17.136805   1752.0  1851.276978   \n",
       "10804 2010-12-30  0.000000  29.456808  16.068770   1749.0  1851.276978   \n",
       "10805 2010-12-31  0.000000  29.198921  16.231855   1743.0  1860.637939   \n",
       "\n",
       "        Niamey  \n",
       "0         5.03  \n",
       "1         4.61  \n",
       "2         4.14  \n",
       "3         3.68  \n",
       "4         2.99  \n",
       "...        ...  \n",
       "10801  1669.00  \n",
       "10802  1677.00  \n",
       "10803  1684.00  \n",
       "10804  1684.00  \n",
       "10805  1684.00  \n",
       "\n",
       "[10806 rows x 7 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bc1e0cfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save data to csv\n",
    "model_data.to_csv('./model_data/model_data_interpolated.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.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}