Categorical_Value_Processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:47.676873Z",
     "iopub.status.busy": "2021-03-25T16:44:47.676631Z",
     "iopub.status.idle": "2021-03-25T16:44:47.770028Z",
     "shell.execute_reply": "2021-03-25T16:44:47.769396Z",
     "shell.execute_reply.started": "2021-03-25T16:44:47.676847Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>Description</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Direction</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Pressure</th>\n",
       "      <th>Wind Speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-10-01 13:00:00</td>\n",
       "      <td>overcast clouds</td>\n",
       "      <td>93.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>285.830000</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-10-01 14:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>91.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>285.834650</td>\n",
       "      <td>986.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-10-01 15:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>87.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>285.847790</td>\n",
       "      <td>945.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-10-01 16:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>84.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>285.860929</td>\n",
       "      <td>904.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-10-01 17:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>80.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>285.874069</td>\n",
       "      <td>863.0</td>\n",
       "      <td>3.0</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>45247</th>\n",
       "      <td>2017-11-29 20:00:00</td>\n",
       "      <td>broken clouds</td>\n",
       "      <td>64.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>275.130000</td>\n",
       "      <td>1021.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45248</th>\n",
       "      <td>2017-11-29 21:00:00</td>\n",
       "      <td>scattered clouds</td>\n",
       "      <td>59.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>274.130000</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45249</th>\n",
       "      <td>2017-11-29 22:00:00</td>\n",
       "      <td>few clouds</td>\n",
       "      <td>66.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>273.480000</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45250</th>\n",
       "      <td>2017-11-29 23:00:00</td>\n",
       "      <td>few clouds</td>\n",
       "      <td>58.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>272.480000</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45251</th>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>few clouds</td>\n",
       "      <td>58.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>271.800000</td>\n",
       "      <td>1027.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45252 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  datetime       Description  Humidity  Wind Direction  \\\n",
       "0      2012-10-01 13:00:00   overcast clouds      93.0           230.0   \n",
       "1      2012-10-01 14:00:00      sky is clear      91.0           230.0   \n",
       "2      2012-10-01 15:00:00      sky is clear      87.0           231.0   \n",
       "3      2012-10-01 16:00:00      sky is clear      84.0           233.0   \n",
       "4      2012-10-01 17:00:00      sky is clear      80.0           234.0   \n",
       "...                    ...               ...       ...             ...   \n",
       "45247  2017-11-29 20:00:00     broken clouds      64.0           300.0   \n",
       "45248  2017-11-29 21:00:00  scattered clouds      59.0           300.0   \n",
       "45249  2017-11-29 22:00:00        few clouds      66.0           290.0   \n",
       "45250  2017-11-29 23:00:00        few clouds      58.0           290.0   \n",
       "45251  2017-11-30 00:00:00        few clouds      58.0           300.0   \n",
       "\n",
       "       Temperature  Pressure  Wind Speed  \n",
       "0       285.830000    1001.0         4.0  \n",
       "1       285.834650     986.0         4.0  \n",
       "2       285.847790     945.0         4.0  \n",
       "3       285.860929     904.0         4.0  \n",
       "4       285.874069     863.0         3.0  \n",
       "...            ...       ...         ...  \n",
       "45247   275.130000    1021.0        10.0  \n",
       "45248   274.130000    1023.0         7.0  \n",
       "45249   273.480000    1024.0         7.0  \n",
       "45250   272.480000    1026.0         4.0  \n",
       "45251   271.800000    1027.0         4.0  \n",
       "\n",
       "[45252 rows x 7 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df=pd.read_csv(\"montreal.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:48.523762Z",
     "iopub.status.busy": "2021-03-25T16:44:48.523478Z",
     "iopub.status.idle": "2021-03-25T16:44:48.534496Z",
     "shell.execute_reply": "2021-03-25T16:44:48.533863Z",
     "shell.execute_reply.started": "2021-03-25T16:44:48.523728Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "broken clouds                   8151\n",
       "sky is clear                    7299\n",
       "overcast clouds                 6637\n",
       "few clouds                      6317\n",
       "light rain                      4015\n",
       "scattered clouds                3407\n",
       "mist                            3196\n",
       "light snow                      1796\n",
       "light intensity shower rain     1067\n",
       "moderate rain                    770\n",
       "heavy snow                       536\n",
       "fog                              456\n",
       "light shower snow                331\n",
       "proximity shower rain            281\n",
       "snow                             175\n",
       "haze                             171\n",
       "heavy intensity rain             168\n",
       "heavy shower snow                119\n",
       "light intensity drizzle          117\n",
       "thunderstorm with light rain      69\n",
       "proximity thunderstorm            39\n",
       "thunderstorm                      31\n",
       "smoke                             18\n",
       "thunderstorm with rain            16\n",
       "light intensity drizzle rain      14\n",
       "shower rain                       11\n",
       "light shower sleet                 9\n",
       "light rain and snow                7\n",
       "thunderstorm with heavy rain       6\n",
       "very heavy rain                    5\n",
       "shower snow                        5\n",
       "freezing rain                      4\n",
       "drizzle                            4\n",
       "sand                               2\n",
       "shower drizzle                     1\n",
       "dust                               1\n",
       "heavy intensity shower rain        1\n",
       "Name: Description, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#raw data - there are 38 unique instances...\n",
    "df[\"Description\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:48.874412Z",
     "iopub.status.busy": "2021-03-25T16:44:48.874106Z",
     "iopub.status.idle": "2021-03-25T16:44:49.312340Z",
     "shell.execute_reply": "2021-03-25T16:44:49.311623Z",
     "shell.execute_reply.started": "2021-03-25T16:44:48.874377Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "moderate  clouds           11558\n",
       "sky is clear                7299\n",
       "heavy clouds                6637\n",
       "light  clouds               6317\n",
       "light rain                  4150\n",
       "mist                        3196\n",
       "light snow                  1796\n",
       "light shower rain           1067\n",
       "moderate rain                770\n",
       "heavy snow                   536\n",
       "fog                          456\n",
       "light shower snow            340\n",
       "shower rain                  292\n",
       "snow                         175\n",
       "heavy rain                   173\n",
       "haze                         171\n",
       "heavy shower snow            119\n",
       "thunderstorm                  70\n",
       "thunderstorm light rain       69\n",
       "other                         21\n",
       "thunderstorm rain             16\n",
       "light rain snow                7\n",
       "thunderstorm heavy rain        6\n",
       "shower snow                    5\n",
       "snow rain                      4\n",
       "shower light rain              1\n",
       "heavy shower rain              1\n",
       "Name: Description, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#too much detail, simplifying a bit and cleaning/standarizing word notation\n",
    "df['Description'] = df['Description'].str.replace(' with ', ' ')\n",
    "df['Description'] = df['Description'].str.replace(' and ', ' ')\n",
    "df['Description'] = df['Description'].str.replace('proximity ', '')\n",
    "df['Description'] = df['Description'].str.replace('light intensity', 'light')\n",
    "df['Description'] = df['Description'].str.replace('heavy intensity', 'heavy')\n",
    "df['Description'] = df['Description'].str.replace('very heavy', 'heavy')\n",
    "\n",
    "#exceptions / categories that the same\n",
    "df['Description'] = df['Description'].str.replace('light drizzle rain', 'light rain')\n",
    "df['Description'] = df['Description'].str.replace('light drizzle', 'light rain')\n",
    "df['Description'] = df['Description'].str.replace('drizzle', 'light rain')\n",
    "df['Description'] = df['Description'].str.replace('sleet', 'snow')\n",
    "df['Description'] = df['Description'].str.replace('freezing', 'snow')\n",
    "df['Description'] = df['Description'].str.replace('sand', 'other')\n",
    "df['Description'] = df['Description'].str.replace('dust', 'other')\n",
    "df['Description'] = df['Description'].str.replace('smoke', 'other')\n",
    "\n",
    "#standarizing intensity values\n",
    "df['Description'] = df['Description'].str.replace('few', 'light ')\n",
    "df['Description'] = df['Description'].str.replace('broken', 'moderate ')\n",
    "df['Description'] = df['Description'].str.replace('scattered', 'moderate ')\n",
    "df['Description'] = df['Description'].str.replace('overcast ', 'heavy ')\n",
    "\n",
    "df[\"Description\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:49.314159Z",
     "iopub.status.busy": "2021-03-25T16:44:49.313927Z",
     "iopub.status.idle": "2021-03-25T16:44:49.338473Z",
     "shell.execute_reply": "2021-03-25T16:44:49.337909Z",
     "shell.execute_reply.started": "2021-03-25T16:44:49.314135Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: Description, dtype: int64)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#a filter to check values\n",
    "df[df['Description'].str.contains(\"drizzle\")]['Description'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:49.340279Z",
     "iopub.status.busy": "2021-03-25T16:44:49.340087Z",
     "iopub.status.idle": "2021-03-25T16:44:49.642024Z",
     "shell.execute_reply": "2021-03-25T16:44:49.640917Z",
     "shell.execute_reply.started": "2021-03-25T16:44:49.340254Z"
    }
   },
   "outputs": [],
   "source": [
    "#multi-categorical dummification\n",
    "tags = ['clouds','rain','mist','snow','shower','thunderstorm','fog','other']\n",
    "\n",
    "for i in range(len(tags)):\n",
    "    df[tags[i]]=0\n",
    "    df.loc[df['Description'].str.contains(pat=tags[i])==True, tags[i]] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:49.643898Z",
     "iopub.status.busy": "2021-03-25T16:44:49.643653Z",
     "iopub.status.idle": "2021-03-25T16:44:49.733226Z",
     "shell.execute_reply": "2021-03-25T16:44:49.732539Z",
     "shell.execute_reply.started": "2021-03-25T16:44:49.643874Z"
    }
   },
   "outputs": [],
   "source": [
    "#creating a weather intensity column\n",
    "intensity_values=['sky is clear','light','moderate','heavy']    \n",
    "\n",
    "for i in range(len(intensity_values)):\n",
    "    df.loc[df['Description'].str.contains(pat =intensity_values[i])==True, 'Intensity'] = i\n",
    "    \n",
    "    \n",
    "#fill in the blanks with moderate\n",
    "df['Intensity']=df['Intensity'].fillna(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:49.734631Z",
     "iopub.status.busy": "2021-03-25T16:44:49.734445Z",
     "iopub.status.idle": "2021-03-25T16:44:50.050629Z",
     "shell.execute_reply": "2021-03-25T16:44:50.050047Z",
     "shell.execute_reply.started": "2021-03-25T16:44:49.734608Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>Description</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Direction</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Pressure</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>clouds</th>\n",
       "      <th>rain</th>\n",
       "      <th>mist</th>\n",
       "      <th>snow</th>\n",
       "      <th>shower</th>\n",
       "      <th>thunderstorm</th>\n",
       "      <th>fog</th>\n",
       "      <th>other</th>\n",
       "      <th>Intensity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-10-01 13:00:00</td>\n",
       "      <td>heavy clouds</td>\n",
       "      <td>93.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>285.830000</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-10-01 14:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>91.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>285.834650</td>\n",
       "      <td>986.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-10-01 15:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>87.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>285.847790</td>\n",
       "      <td>945.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-10-01 16:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>84.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>285.860929</td>\n",
       "      <td>904.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-10-01 17:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>80.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>285.874069</td>\n",
       "      <td>863.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\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>45247</th>\n",
       "      <td>2017-11-29 20:00:00</td>\n",
       "      <td>moderate  clouds</td>\n",
       "      <td>64.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>275.130000</td>\n",
       "      <td>1021.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45248</th>\n",
       "      <td>2017-11-29 21:00:00</td>\n",
       "      <td>moderate  clouds</td>\n",
       "      <td>59.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>274.130000</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45249</th>\n",
       "      <td>2017-11-29 22:00:00</td>\n",
       "      <td>light  clouds</td>\n",
       "      <td>66.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>273.480000</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45250</th>\n",
       "      <td>2017-11-29 23:00:00</td>\n",
       "      <td>light  clouds</td>\n",
       "      <td>58.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>272.480000</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45251</th>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>light  clouds</td>\n",
       "      <td>58.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>271.800000</td>\n",
       "      <td>1027.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45252 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  datetime       Description  Humidity  Wind Direction  \\\n",
       "0      2012-10-01 13:00:00      heavy clouds      93.0           230.0   \n",
       "1      2012-10-01 14:00:00      sky is clear      91.0           230.0   \n",
       "2      2012-10-01 15:00:00      sky is clear      87.0           231.0   \n",
       "3      2012-10-01 16:00:00      sky is clear      84.0           233.0   \n",
       "4      2012-10-01 17:00:00      sky is clear      80.0           234.0   \n",
       "...                    ...               ...       ...             ...   \n",
       "45247  2017-11-29 20:00:00  moderate  clouds      64.0           300.0   \n",
       "45248  2017-11-29 21:00:00  moderate  clouds      59.0           300.0   \n",
       "45249  2017-11-29 22:00:00     light  clouds      66.0           290.0   \n",
       "45250  2017-11-29 23:00:00     light  clouds      58.0           290.0   \n",
       "45251  2017-11-30 00:00:00     light  clouds      58.0           300.0   \n",
       "\n",
       "       Temperature  Pressure  Wind Speed  clouds  rain  mist  snow  shower  \\\n",
       "0       285.830000    1001.0         4.0       1     0     0     0       0   \n",
       "1       285.834650     986.0         4.0       0     0     0     0       0   \n",
       "2       285.847790     945.0         4.0       0     0     0     0       0   \n",
       "3       285.860929     904.0         4.0       0     0     0     0       0   \n",
       "4       285.874069     863.0         3.0       0     0     0     0       0   \n",
       "...            ...       ...         ...     ...   ...   ...   ...     ...   \n",
       "45247   275.130000    1021.0        10.0       1     0     0     0       0   \n",
       "45248   274.130000    1023.0         7.0       1     0     0     0       0   \n",
       "45249   273.480000    1024.0         7.0       1     0     0     0       0   \n",
       "45250   272.480000    1026.0         4.0       1     0     0     0       0   \n",
       "45251   271.800000    1027.0         4.0       1     0     0     0       0   \n",
       "\n",
       "       thunderstorm  fog  other  Intensity  \n",
       "0                 0    0      0        3.0  \n",
       "1                 0    0      0        0.0  \n",
       "2                 0    0      0        0.0  \n",
       "3                 0    0      0        0.0  \n",
       "4                 0    0      0        0.0  \n",
       "...             ...  ...    ...        ...  \n",
       "45247             0    0      0        2.0  \n",
       "45248             0    0      0        2.0  \n",
       "45249             0    0      0        1.0  \n",
       "45250             0    0      0        1.0  \n",
       "45251             0    0      0        1.0  \n",
       "\n",
       "[45252 rows x 16 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "display(df)\n",
    "\n",
    "df.to_csv(\"weather_desc_clean.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:50.052124Z",
     "iopub.status.busy": "2021-03-25T16:44:50.051933Z",
     "iopub.status.idle": "2021-03-25T16:44:50.062242Z",
     "shell.execute_reply": "2021-03-25T16:44:50.061472Z",
     "shell.execute_reply.started": "2021-03-25T16:44:50.052100Z"
    }
   },
   "outputs": [],
   "source": [
    "#here is the result in a custom function\n",
    "def clean_description(df):\n",
    "    #too much detail, simplifying a bit and cleaning/standarizing word notation\n",
    "    df['Description'] = df['Description'].str.replace(' with ', ' ')\n",
    "    df['Description'] = df['Description'].str.replace(' and ', ' ')\n",
    "    df['Description'] = df['Description'].str.replace('proximity ', '')\n",
    "    df['Description'] = df['Description'].str.replace('light intensity', 'light')\n",
    "    df['Description'] = df['Description'].str.replace('heavy intensity', 'heavy')\n",
    "    df['Description'] = df['Description'].str.replace('very heavy', 'heavy')\n",
    "\n",
    "    #exceptions / categories that the same\n",
    "    df['Description'] = df['Description'].str.replace('light drizzle rain', 'light rain')\n",
    "    df['Description'] = df['Description'].str.replace('light drizzle', 'light rain')\n",
    "    df['Description'] = df['Description'].str.replace('drizzle', 'light rain')\n",
    "    df['Description'] = df['Description'].str.replace('sleet', 'snow')\n",
    "    df['Description'] = df['Description'].str.replace('freezing', 'snow')\n",
    "    df['Description'] = df['Description'].str.replace('sand', 'other')\n",
    "    df['Description'] = df['Description'].str.replace('dust', 'other')\n",
    "    df['Description'] = df['Description'].str.replace('smoke', 'other')\n",
    "\n",
    "    #standarizing intensity values\n",
    "    df['Description'] = df['Description'].str.replace('few', 'light ')\n",
    "    df['Description'] = df['Description'].str.replace('broken', 'moderate ')\n",
    "    df['Description'] = df['Description'].str.replace('scattered', 'moderate ')\n",
    "    df['Description'] = df['Description'].str.replace('overcast ', 'heavy ')\n",
    "    \n",
    "    #multi-categorical dummification\n",
    "    tags = ['clouds','rain','mist','snow','shower','thunderstorm','fog','other']\n",
    "\n",
    "    for i in range(len(tags)):\n",
    "        df[tags[i]]=0\n",
    "        df.loc[df['Description'].str.contains(pat=tags[i])==True, tags[i]] = 1\n",
    "        \n",
    "    #creating a weather intensity column\n",
    "    intensity_values=['sky is clear','light','moderate','heavy']    \n",
    "\n",
    "    for i in range(len(intensity_values)):\n",
    "        df.loc[df['Description'].str.contains(pat =intensity_values[i])==True, 'Intensity'] = i\n",
    "\n",
    "\n",
    "    #fill in the blanks with moderate\n",
    "    df['Intensity']=df['Intensity'].fillna(2)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-03-25T16:44:50.510986Z",
     "iopub.status.busy": "2021-03-25T16:44:50.510752Z",
     "iopub.status.idle": "2021-03-25T16:44:51.183577Z",
     "shell.execute_reply": "2021-03-25T16:44:51.182936Z",
     "shell.execute_reply.started": "2021-03-25T16:44:50.510955Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>Description</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Wind Direction</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Pressure</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>clouds</th>\n",
       "      <th>rain</th>\n",
       "      <th>mist</th>\n",
       "      <th>snow</th>\n",
       "      <th>shower</th>\n",
       "      <th>thunderstorm</th>\n",
       "      <th>fog</th>\n",
       "      <th>other</th>\n",
       "      <th>Intensity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-10-01 13:00:00</td>\n",
       "      <td>heavy clouds</td>\n",
       "      <td>93.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>285.830000</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-10-01 14:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>91.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>285.834650</td>\n",
       "      <td>986.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-10-01 15:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>87.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>285.847790</td>\n",
       "      <td>945.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-10-01 16:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>84.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>285.860929</td>\n",
       "      <td>904.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-10-01 17:00:00</td>\n",
       "      <td>sky is clear</td>\n",
       "      <td>80.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>285.874069</td>\n",
       "      <td>863.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\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>45247</th>\n",
       "      <td>2017-11-29 20:00:00</td>\n",
       "      <td>moderate  clouds</td>\n",
       "      <td>64.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>275.130000</td>\n",
       "      <td>1021.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45248</th>\n",
       "      <td>2017-11-29 21:00:00</td>\n",
       "      <td>moderate  clouds</td>\n",
       "      <td>59.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>274.130000</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45249</th>\n",
       "      <td>2017-11-29 22:00:00</td>\n",
       "      <td>light  clouds</td>\n",
       "      <td>66.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>273.480000</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45250</th>\n",
       "      <td>2017-11-29 23:00:00</td>\n",
       "      <td>light  clouds</td>\n",
       "      <td>58.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>272.480000</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45251</th>\n",
       "      <td>2017-11-30 00:00:00</td>\n",
       "      <td>light  clouds</td>\n",
       "      <td>58.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>271.800000</td>\n",
       "      <td>1027.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45252 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  datetime       Description  Humidity  Wind Direction  \\\n",
       "0      2012-10-01 13:00:00      heavy clouds      93.0           230.0   \n",
       "1      2012-10-01 14:00:00      sky is clear      91.0           230.0   \n",
       "2      2012-10-01 15:00:00      sky is clear      87.0           231.0   \n",
       "3      2012-10-01 16:00:00      sky is clear      84.0           233.0   \n",
       "4      2012-10-01 17:00:00      sky is clear      80.0           234.0   \n",
       "...                    ...               ...       ...             ...   \n",
       "45247  2017-11-29 20:00:00  moderate  clouds      64.0           300.0   \n",
       "45248  2017-11-29 21:00:00  moderate  clouds      59.0           300.0   \n",
       "45249  2017-11-29 22:00:00     light  clouds      66.0           290.0   \n",
       "45250  2017-11-29 23:00:00     light  clouds      58.0           290.0   \n",
       "45251  2017-11-30 00:00:00     light  clouds      58.0           300.0   \n",
       "\n",
       "       Temperature  Pressure  Wind Speed  clouds  rain  mist  snow  shower  \\\n",
       "0       285.830000    1001.0         4.0       1     0     0     0       0   \n",
       "1       285.834650     986.0         4.0       0     0     0     0       0   \n",
       "2       285.847790     945.0         4.0       0     0     0     0       0   \n",
       "3       285.860929     904.0         4.0       0     0     0     0       0   \n",
       "4       285.874069     863.0         3.0       0     0     0     0       0   \n",
       "...            ...       ...         ...     ...   ...   ...   ...     ...   \n",
       "45247   275.130000    1021.0        10.0       1     0     0     0       0   \n",
       "45248   274.130000    1023.0         7.0       1     0     0     0       0   \n",
       "45249   273.480000    1024.0         7.0       1     0     0     0       0   \n",
       "45250   272.480000    1026.0         4.0       1     0     0     0       0   \n",
       "45251   271.800000    1027.0         4.0       1     0     0     0       0   \n",
       "\n",
       "       thunderstorm  fog  other  Intensity  \n",
       "0                 0    0      0        3.0  \n",
       "1                 0    0      0        0.0  \n",
       "2                 0    0      0        0.0  \n",
       "3                 0    0      0        0.0  \n",
       "4                 0    0      0        0.0  \n",
       "...             ...  ...    ...        ...  \n",
       "45247             0    0      0        2.0  \n",
       "45248             0    0      0        2.0  \n",
       "45249             0    0      0        1.0  \n",
       "45250             0    0      0        1.0  \n",
       "45251             0    0      0        1.0  \n",
       "\n",
       "[45252 rows x 16 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_description(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}