notebooks/1.0_data_cleaning_cch.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I. SINGAPORE'S WEATHER DATA: 1983 - 2019 (SEPT)\n",
    "This dataset is one of the few detailed, multi-year ones that I've been able to find in the public domain in Singapore. I believe it can be useful for data science students in this part of the world who are looking to test their skills on a local dataset, or build a personal project.\n",
    "\n",
    "I'll be using this dataset for small projects on data visualisation (see notebook 2.0_visualisation_cch), time series analysis and machine learning. Ping me on LinkedIn or Twitter if you do something interesting with this set of data:\n",
    "\n",
    "Twitter: @chinhon\n",
    "\n",
    "LinkedIn: https://www.linkedin.com/in/chuachinhon/\n",
    "\n",
    "### UPDATE: \n",
    "Temperature records for July-Sept 2019 have been added. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## FILE ORGANISATION:\n",
    "The original data files, as downloaded from the [Singapore Met Office](http://www.weather.gov.sg/climate-historical-daily/) and Data.gov.sg, are in the raw folder. The files are mostly clean, save for some missing values for mean and max wind speed. I've lightly processed the files and saved the output to the data folder so that I can call them up easily for future data projects.\n",
    "\n",
    "You can make a different version of the dataset by concating the raw files over a different time frame, or with more elaborate feature engineering.\n",
    "\n",
    "What you'll find in the raw folder:\n",
    "- 438 CSV files containing daily weather data for Singapore from 1983 - 2019 (June)\n",
    "\n",
    "- a \"monthly_data\" sub-folder containing monthly average data for rainfall, maximum and mean temperatures.\n",
    "\n",
    "The files in the data folder have been processed by the code below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. DAILY WEATHER DATA "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combining the separate CSV files into one\n",
    "raw = pd.concat(\n",
    "    [pd.read_csv(f) for f in glob.glob(\"../raw/*.csv\")], ignore_index=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding a datetime col in the year-month-day format\n",
    "raw[\"Date\"] = pd.to_datetime(\n",
    "    raw[\"Year\"].astype(str)\n",
    "    + \"-\"\n",
    "    + raw[\"Month\"].astype(str)\n",
    "    + \"-\"\n",
    "    + raw[\"Day\"].astype(str)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw[\"Month_Name\"] = raw[\"Date\"].dt.month_name()\n",
    "raw[\"Quarter\"] = raw[\"Date\"].dt.quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting values in the Max/Mean Wind into numeric data type\n",
    "raw[\"Max Wind Speed (km/h)\"] = pd.to_numeric(\n",
    "    raw[\"Max Wind Speed (km/h)\"], errors=\"coerce\"\n",
    ")\n",
    "raw[\"Mean Wind Speed (km/h)\"] = pd.to_numeric(\n",
    "    raw[\"Mean Wind Speed (km/h)\"], errors=\"coerce\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13422 entries, 0 to 13421\n",
      "Data columns (total 16 columns):\n",
      "Station                          13422 non-null object\n",
      "Year                             13422 non-null int64\n",
      "Month                            13422 non-null int64\n",
      "Day                              13422 non-null int64\n",
      "Daily Rainfall Total (mm)        13422 non-null float64\n",
      "Highest 30 Min Rainfall (mm)     13422 non-null object\n",
      "Highest 60 Min Rainfall (mm)     13422 non-null object\n",
      "Highest 120 Min Rainfall (mm)    13422 non-null object\n",
      "Mean Temperature (°C)            13422 non-null float64\n",
      "Maximum Temperature (°C)         13422 non-null float64\n",
      "Minimum Temperature (°C)         13422 non-null float64\n",
      "Mean Wind Speed (km/h)           13412 non-null float64\n",
      "Max Wind Speed (km/h)            13411 non-null float64\n",
      "Date                             13422 non-null datetime64[ns]\n",
      "Month_Name                       13422 non-null object\n",
      "Quarter                          13422 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(6), int64(4), object(5)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "raw.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fill the missing entries in Mean Wind Speed and Max Wind Speed columns with mean values of both cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw[\"Max Wind Speed (km/h)\"] = raw[\"Max Wind Speed (km/h)\"].fillna(\n",
    "    raw[\"Max Wind Speed (km/h)\"].mean()\n",
    ")\n",
    "raw[\"Mean Wind Speed (km/h)\"] = raw[\"Mean Wind Speed (km/h)\"].fillna(\n",
    "    raw[\"Mean Wind Speed (km/h)\"].mean()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dropping cols that I won't need for visualisation or modelling\n",
    "raw = raw.drop(\n",
    "    columns=[\n",
    "        \"Station\",\n",
    "        \"Highest 30 Min Rainfall (mm)\",\n",
    "        \"Highest 60 Min Rainfall (mm)\",\n",
    "        \"Highest 120 Min Rainfall (mm)\",\n",
    "    ]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slight rearrangement of cols for clarity\n",
    "cols = [\n",
    "    \"Date\",\n",
    "    \"Year\",\n",
    "    \"Month\",\n",
    "    \"Month_Name\",\n",
    "    \"Quarter\",\n",
    "    \"Day\",\n",
    "    \"Daily Rainfall Total (mm)\",\n",
    "    \"Mean Temperature (°C)\",\n",
    "    \"Maximum Temperature (°C)\",\n",
    "    \"Minimum Temperature (°C)\",\n",
    "    \"Mean Wind Speed (km/h)\",\n",
    "    \"Max Wind Speed (km/h)\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather = raw[cols].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather = weather.sort_values('Date', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13422 entries, 1400 to 4385\n",
      "Data columns (total 12 columns):\n",
      "Date                         13422 non-null datetime64[ns]\n",
      "Year                         13422 non-null int64\n",
      "Month                        13422 non-null int64\n",
      "Month_Name                   13422 non-null object\n",
      "Quarter                      13422 non-null int64\n",
      "Day                          13422 non-null int64\n",
      "Daily Rainfall Total (mm)    13422 non-null float64\n",
      "Mean Temperature (°C)        13422 non-null float64\n",
      "Maximum Temperature (°C)     13422 non-null float64\n",
      "Minimum Temperature (°C)     13422 non-null float64\n",
      "Mean Wind Speed (km/h)       13422 non-null float64\n",
      "Max Wind Speed (km/h)        13422 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(6), int64(4), object(1)\n",
      "memory usage: 1.3+ MB\n"
     ]
    }
   ],
   "source": [
    "weather.info()\n",
    "# no null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'Year', 'Month', 'Month_Name', 'Quarter', 'Day',\n",
       "       'Daily Rainfall Total (mm)', 'Mean Temperature (°C)',\n",
       "       'Maximum Temperature (°C)', 'Minimum Temperature (°C)',\n",
       "       'Mean Wind Speed (km/h)', 'Max Wind Speed (km/h)'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Day</th>\n",
       "      <th>Daily Rainfall Total (mm)</th>\n",
       "      <th>Mean Temperature (°C)</th>\n",
       "      <th>Maximum Temperature (°C)</th>\n",
       "      <th>Minimum Temperature (°C)</th>\n",
       "      <th>Mean Wind Speed (km/h)</th>\n",
       "      <th>Max Wind Speed (km/h)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "      <td>13422.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2000.875950</td>\n",
       "      <td>6.492326</td>\n",
       "      <td>2.498361</td>\n",
       "      <td>15.728654</td>\n",
       "      <td>5.815281</td>\n",
       "      <td>27.667151</td>\n",
       "      <td>31.518246</td>\n",
       "      <td>24.902637</td>\n",
       "      <td>7.445743</td>\n",
       "      <td>34.056342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>10.608927</td>\n",
       "      <td>3.439891</td>\n",
       "      <td>1.114069</td>\n",
       "      <td>8.799939</td>\n",
       "      <td>14.413763</td>\n",
       "      <td>1.176099</td>\n",
       "      <td>1.571405</td>\n",
       "      <td>1.268748</td>\n",
       "      <td>3.480851</td>\n",
       "      <td>8.037197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1983.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>22.800000</td>\n",
       "      <td>23.600000</td>\n",
       "      <td>20.200000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>4.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1992.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>26.900000</td>\n",
       "      <td>30.800000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>4.800000</td>\n",
       "      <td>28.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2001.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>27.700000</td>\n",
       "      <td>31.800000</td>\n",
       "      <td>24.900000</td>\n",
       "      <td>6.800000</td>\n",
       "      <td>33.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2010.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>4.400000</td>\n",
       "      <td>28.600000</td>\n",
       "      <td>32.500000</td>\n",
       "      <td>25.800000</td>\n",
       "      <td>9.700000</td>\n",
       "      <td>38.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2019.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>216.200000</td>\n",
       "      <td>30.900000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>29.100000</td>\n",
       "      <td>22.200000</td>\n",
       "      <td>90.700000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Year         Month       Quarter           Day  \\\n",
       "count  13422.000000  13422.000000  13422.000000  13422.000000   \n",
       "mean    2000.875950      6.492326      2.498361     15.728654   \n",
       "std       10.608927      3.439891      1.114069      8.799939   \n",
       "min     1983.000000      1.000000      1.000000      1.000000   \n",
       "25%     1992.000000      4.000000      2.000000      8.000000   \n",
       "50%     2001.000000      7.000000      3.000000     16.000000   \n",
       "75%     2010.000000      9.000000      3.000000     23.000000   \n",
       "max     2019.000000     12.000000      4.000000     31.000000   \n",
       "\n",
       "       Daily Rainfall Total (mm)  Mean Temperature (°C)  \\\n",
       "count               13422.000000           13422.000000   \n",
       "mean                    5.815281              27.667151   \n",
       "std                    14.413763               1.176099   \n",
       "min                     0.000000              22.800000   \n",
       "25%                     0.000000              26.900000   \n",
       "50%                     0.000000              27.700000   \n",
       "75%                     4.400000              28.600000   \n",
       "max                   216.200000              30.900000   \n",
       "\n",
       "       Maximum Temperature (°C)  Minimum Temperature (°C)  \\\n",
       "count              13422.000000              13422.000000   \n",
       "mean                  31.518246                 24.902637   \n",
       "std                    1.571405                  1.268748   \n",
       "min                   23.600000                 20.200000   \n",
       "25%                   30.800000                 24.000000   \n",
       "50%                   31.800000                 24.900000   \n",
       "75%                   32.500000                 25.800000   \n",
       "max                   36.000000                 29.100000   \n",
       "\n",
       "       Mean Wind Speed (km/h)  Max Wind Speed (km/h)  \n",
       "count            13422.000000           13422.000000  \n",
       "mean                 7.445743              34.056342  \n",
       "std                  3.480851               8.037197  \n",
       "min                  0.200000               4.700000  \n",
       "25%                  4.800000              28.800000  \n",
       "50%                  6.800000              33.100000  \n",
       "75%                  9.700000              38.200000  \n",
       "max                 22.200000              90.700000  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.describe()\n",
    "# The Daily Rainfall cols have some obvious outliers. But let's deal with that later, as and when required"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Month_Name</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Day</th>\n",
       "      <th>Daily Rainfall Total (mm)</th>\n",
       "      <th>Mean Temperature (°C)</th>\n",
       "      <th>Maximum Temperature (°C)</th>\n",
       "      <th>Minimum Temperature (°C)</th>\n",
       "      <th>Mean Wind Speed (km/h)</th>\n",
       "      <th>Max Wind Speed (km/h)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1400</th>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>2019</td>\n",
       "      <td>9</td>\n",
       "      <td>September</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>1.4</td>\n",
       "      <td>27.5</td>\n",
       "      <td>31.3</td>\n",
       "      <td>25.1</td>\n",
       "      <td>6.8</td>\n",
       "      <td>34.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1399</th>\n",
       "      <td>2019-09-29</td>\n",
       "      <td>2019</td>\n",
       "      <td>9</td>\n",
       "      <td>September</td>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.4</td>\n",
       "      <td>33.3</td>\n",
       "      <td>26.5</td>\n",
       "      <td>6.8</td>\n",
       "      <td>41.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1398</th>\n",
       "      <td>2019-09-28</td>\n",
       "      <td>2019</td>\n",
       "      <td>9</td>\n",
       "      <td>September</td>\n",
       "      <td>3</td>\n",
       "      <td>28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.9</td>\n",
       "      <td>34.3</td>\n",
       "      <td>26.0</td>\n",
       "      <td>7.6</td>\n",
       "      <td>29.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397</th>\n",
       "      <td>2019-09-27</td>\n",
       "      <td>2019</td>\n",
       "      <td>9</td>\n",
       "      <td>September</td>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>29.2</td>\n",
       "      <td>34.1</td>\n",
       "      <td>26.8</td>\n",
       "      <td>9.7</td>\n",
       "      <td>30.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1396</th>\n",
       "      <td>2019-09-26</td>\n",
       "      <td>2019</td>\n",
       "      <td>9</td>\n",
       "      <td>September</td>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.5</td>\n",
       "      <td>33.5</td>\n",
       "      <td>25.4</td>\n",
       "      <td>9.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Date  Year  Month Month_Name  Quarter  Day  \\\n",
       "1400 2019-09-30  2019      9  September        3   30   \n",
       "1399 2019-09-29  2019      9  September        3   29   \n",
       "1398 2019-09-28  2019      9  September        3   28   \n",
       "1397 2019-09-27  2019      9  September        3   27   \n",
       "1396 2019-09-26  2019      9  September        3   26   \n",
       "\n",
       "      Daily Rainfall Total (mm)  Mean Temperature (°C)  \\\n",
       "1400                        1.4                   27.5   \n",
       "1399                        0.0                   28.4   \n",
       "1398                        0.0                   28.9   \n",
       "1397                        0.0                   29.2   \n",
       "1396                        0.0                   28.5   \n",
       "\n",
       "      Maximum Temperature (°C)  Minimum Temperature (°C)  \\\n",
       "1400                      31.3                      25.1   \n",
       "1399                      33.3                      26.5   \n",
       "1398                      34.3                      26.0   \n",
       "1397                      34.1                      26.8   \n",
       "1396                      33.5                      25.4   \n",
       "\n",
       "      Mean Wind Speed (km/h)  Max Wind Speed (km/h)  \n",
       "1400                     6.8                   34.2  \n",
       "1399                     6.8                   41.4  \n",
       "1398                     7.6                   29.5  \n",
       "1397                     9.7                   30.6  \n",
       "1396                     9.0                   31.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# weather.to_csv('../data/weather.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. MONTHLY DATA\n",
    "Here, I'll do some light processing of the monthly average data for rainfall, maximum and mean temperatures. They are in the raw folder's \"monthly_data\" sub-folder."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 MONTHLY RAINFALL RECORDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_rain = pd.read_csv('../raw/monthly_data/monthly_rain.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_rain[\"month\"] = pd.to_datetime(monthly_rain[\"month\"])\n",
    "monthly_rain[\"year\"] = monthly_rain[\"month\"].dt.year\n",
    "monthly_rain[\"month\"] = monthly_rain[\"month\"].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_rain = monthly_rain.rename(columns = {\"year\": \"Year\", \n",
    "                                              \"month\": \"Month\", \n",
    "                                              \"total_rainfall\": \"Total_Monthly_Rainfall (mm)\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For consistency with the daily records, I'll start with entries from 1983 for the monthly datasets as well \n",
    "cols_rain = [\"Total_Monthly_Rainfall (mm)\", \"Year\", \"Month\"]\n",
    "monthly_rain = monthly_rain[cols_rain].copy()\n",
    "monthly_rain = monthly_rain[monthly_rain[\"Year\"] >= 1983]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#monthly_rain.to_csv('../data/rain_monthly.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Total_Monthly_Rainfall (mm)</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>31.6</td>\n",
       "      <td>2019</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>72.2</td>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>174.8</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>69.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>173.8</td>\n",
       "      <td>2019</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Total_Monthly_Rainfall (mm)  Year  Month\n",
       "445                         31.6  2019      2\n",
       "446                         72.2  2019      3\n",
       "447                        174.8  2019      4\n",
       "448                         69.0  2019      5\n",
       "449                        173.8  2019      6"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthly_rain.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 MONTHLY MEAN TEMPERATURES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_temp = pd.read_csv('../raw/monthly_data/monthly_temp_mean.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_temp[\"month\"] = pd.to_datetime(mean_temp[\"month\"])\n",
    "mean_temp[\"year\"] = mean_temp[\"month\"].dt.year\n",
    "mean_temp[\"month\"] = mean_temp[\"month\"].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_temp = mean_temp.rename(\n",
    "    columns={\n",
    "        \"year\": \"Year\",\n",
    "        \"month\": \"Month\",\n",
    "        \"mean_temp\": \"Mean_Monthly_Temperature (°C)\",\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_temp_mean = [\"Mean_Monthly_Temperature (°C)\", \"Year\", \"Month\"]\n",
    "mean_temp = mean_temp[cols_temp_mean].copy()\n",
    "mean_temp = mean_temp[mean_temp[\"Year\"] >= 1983]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mean_temp.to_csv('../data/mean_temp_monthly.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 MONTHLY MAX TEMPERATURES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_temp = pd.read_csv('../raw/monthly_data/monthly_temp_max.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_temp[\"month\"] = pd.to_datetime(max_temp[\"month\"])\n",
    "max_temp[\"year\"] = max_temp[\"month\"].dt.year\n",
    "max_temp[\"month\"] = max_temp[\"month\"].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_temp = max_temp.rename(\n",
    "    columns={\n",
    "        \"year\": \"Year\",\n",
    "        \"month\": \"Month\",\n",
    "        \"max_temperature\": \"Max_Monthly_Temperature (°C)\",\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_temp_max = [\"Max_Monthly_Temperature (°C)\", \"Year\", \"Month\"]\n",
    "max_temp = max_temp[cols_temp_max].copy()\n",
    "max_temp = max_temp[max_temp[\"Year\"] >= 1983]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#max_temp.to_csv('../data/max_temp_monthly.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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}