Assignment_clean_solution.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assignment weather data solution\n",
    "\n",
    "In this assignment we will work with weatherdata from the KNMI. A subset of weatherdata is for you available in the file: `KNMI_20181231.csv`. The data consist of several stations with daily weather data of several years. Your task is to make a tidy dataframe ready for further processing. \n",
    "\n",
    "\n",
    "Learning outcomes\n",
    "\n",
    "- load, inspect and clean a dataset \n",
    "\n",
    "The assignment consists of 6 parts:\n",
    "\n",
    "- [part 1: load the data](#0)\n",
    "- [part 2: clean the data](#1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='0'></a>\n",
    "## Part 1: Load the data\n",
    "\n",
    "Either load the dataset `KNMI_20181231.csv` or `KNMI_20181231.txt.tsv`. The dataheaders contain spaces and are not very self explainable. Change this into more readable ones. Select data from station 270. Select only the mean, minimum and maximum temperature. The data should look something like this:\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "        station      Date  Tmean   Tmin   Tmax\n",
    "245205      270  19510101      0    -40     24\n",
    "245206      270  19510102      9     -5     19\n",
    "245207      270  19510103      5     -8     17\n",
    "245208      270  19510104      5    -15     17\n",
    "245209      270  19510105     26      6     46"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>station</th>\n",
       "      <th>Date</th>\n",
       "      <th>Tmean</th>\n",
       "      <th>Tmin</th>\n",
       "      <th>Tmax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97641</th>\n",
       "      <td>270</td>\n",
       "      <td>20000101</td>\n",
       "      <td>42</td>\n",
       "      <td>-4</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97642</th>\n",
       "      <td>270</td>\n",
       "      <td>20000102</td>\n",
       "      <td>55</td>\n",
       "      <td>33</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97643</th>\n",
       "      <td>270</td>\n",
       "      <td>20000103</td>\n",
       "      <td>74</td>\n",
       "      <td>49</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97644</th>\n",
       "      <td>270</td>\n",
       "      <td>20000104</td>\n",
       "      <td>46</td>\n",
       "      <td>22</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97645</th>\n",
       "      <td>270</td>\n",
       "      <td>20000105</td>\n",
       "      <td>41</td>\n",
       "      <td>14</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104576</th>\n",
       "      <td>270</td>\n",
       "      <td>20181227</td>\n",
       "      <td>57</td>\n",
       "      <td>53</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104577</th>\n",
       "      <td>270</td>\n",
       "      <td>20181228</td>\n",
       "      <td>71</td>\n",
       "      <td>58</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104578</th>\n",
       "      <td>270</td>\n",
       "      <td>20181229</td>\n",
       "      <td>85</td>\n",
       "      <td>69</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104579</th>\n",
       "      <td>270</td>\n",
       "      <td>20181230</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104580</th>\n",
       "      <td>270</td>\n",
       "      <td>20181231</td>\n",
       "      <td>87</td>\n",
       "      <td>82</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6940 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        station      Date  Tmean   Tmin   Tmax\n",
       "97641       270  20000101     42     -4     79\n",
       "97642       270  20000102     55     33     74\n",
       "97643       270  20000103     74     49     89\n",
       "97644       270  20000104     46     22     75\n",
       "97645       270  20000105     41     14     56\n",
       "...         ...       ...    ...    ...    ...\n",
       "104576      270  20181227     57     53     62\n",
       "104577      270  20181228     71     58     81\n",
       "104578      270  20181229     85     69    102\n",
       "104579      270  20181230     80     68     90\n",
       "104580      270  20181231     87     82     97\n",
       "\n",
       "[6940 rows x 5 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv(\"../data/KNMI_20181231.txt.tsv\",\n",
    "                 sep=\",\", \n",
    "                 usecols=[0, 1, 2, 3, 4], \n",
    "                 names=[\"station\", \"Date\", \"Tmean\", \"Tmin\", \"Tmax\"], \n",
    "                 comment=\"#\", \n",
    "                 low_memory=False)\n",
    "df = df[df.station == 270]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a name='1'></a>\n",
    "## Part 2: Clean the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data ia not clean. There are empty cells in the dataframe which needs to be replaced with NaN's and the temperature is in centidegrees which needs to be transformed into degrees. The date field needs a datetime format. For visualization convience we would like to remove the leap year. Conduct the cleaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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>station</th>\n",
       "      <th>Date</th>\n",
       "      <th>Tmean</th>\n",
       "      <th>Tmin</th>\n",
       "      <th>Tmax</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>97641</th>\n",
       "      <td>270</td>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>4.2</td>\n",
       "      <td>-0.4</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97642</th>\n",
       "      <td>270</td>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>5.5</td>\n",
       "      <td>3.3</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97643</th>\n",
       "      <td>270</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>7.4</td>\n",
       "      <td>4.9</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97644</th>\n",
       "      <td>270</td>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>4.6</td>\n",
       "      <td>2.2</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97645</th>\n",
       "      <td>270</td>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.4</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104576</th>\n",
       "      <td>270</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.3</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104577</th>\n",
       "      <td>270</td>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>7.1</td>\n",
       "      <td>5.8</td>\n",
       "      <td>8.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104578</th>\n",
       "      <td>270</td>\n",
       "      <td>2018-12-29</td>\n",
       "      <td>8.5</td>\n",
       "      <td>6.9</td>\n",
       "      <td>10.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104579</th>\n",
       "      <td>270</td>\n",
       "      <td>2018-12-30</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.8</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104580</th>\n",
       "      <td>270</td>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.2</td>\n",
       "      <td>9.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6935 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        station       Date  Tmean  Tmin  Tmax\n",
       "97641       270 2000-01-01    4.2  -0.4   7.9\n",
       "97642       270 2000-01-02    5.5   3.3   7.4\n",
       "97643       270 2000-01-03    7.4   4.9   8.9\n",
       "97644       270 2000-01-04    4.6   2.2   7.5\n",
       "97645       270 2000-01-05    4.1   1.4   5.6\n",
       "...         ...        ...    ...   ...   ...\n",
       "104576      270 2018-12-27    5.7   5.3   6.2\n",
       "104577      270 2018-12-28    7.1   5.8   8.1\n",
       "104578      270 2018-12-29    8.5   6.9  10.2\n",
       "104579      270 2018-12-30    8.0   6.8   9.0\n",
       "104580      270 2018-12-31    8.7   8.2   9.7\n",
       "\n",
       "[6935 rows x 5 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#replace cells with spaces to NaN\n",
    "df = df.replace(r\"^\\s*$\", np.nan, regex=True)\n",
    "#change data formats\n",
    "df.Date = pd.to_datetime(df['Date'].astype(str), format='%Y%m%d')\n",
    "#change temperatures to celcius degrees\n",
    "df.Tmean = pd.to_numeric(df.Tmean, errors='coerce') / 10\n",
    "df.Tmin = pd.to_numeric(df.Tmin, errors='coerce') / 10\n",
    "df.Tmax = pd.to_numeric(df.Tmax, errors='coerce') / 10\n",
    "#remove leap year\n",
    "df = df[~((df.Date.dt.month == 2) & (df.Date.dt.day == 29))]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>    \n",
    "<summary>\n",
    "    <font size=\"3\" color=\"darkgreen\"><b>Hints</b></font>\n",
    "</summary>\n",
    "<ul><li>pd.to_datetime(df['Date'].astype(str), format='%Y%m%d')</li>\n",
    "    <li>regex for empty cells = `^\\s*$` </li>\n",
    "    <li>remove month == 2 & day == 29</li> \n",
    "</ul>\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Expected outcome"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "DatetimeIndex: 24820 entries, 1951-01-01 to 2018-12-31\n",
    "Data columns (total 5 columns):\n",
    " #   Column   Non-Null Count  Dtype         \n",
    "---  ------   --------------  -----         \n",
    " 0   station  24820 non-null  int64         \n",
    " 1   Date     24820 non-null  datetime64[ns]\n",
    " 2   Tmean    24819 non-null  float64       \n",
    " 3   Tmin     24819 non-null  float64       \n",
    " 4   Tmax     24819 non-null  float64       \n",
    "dtypes: datetime64[ns](1), float64(3), int64(1)\n",
    "memory usage: 1.1 MB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ITANN",
   "language": "python",
   "name": "itann"
  },
  "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}