preprocessing/pandasql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandasql\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>maxpressurem</th>\n",
       "      <th>maxdewptm</th>\n",
       "      <th>maxpressurei</th>\n",
       "      <th>maxdewpti</th>\n",
       "      <th>since1julheatingdegreedaysnormal</th>\n",
       "      <th>heatingdegreedaysnormal</th>\n",
       "      <th>since1sepcoolingdegreedaysnormal</th>\n",
       "      <th>hail</th>\n",
       "      <th>since1julsnowfallm</th>\n",
       "      <th>...</th>\n",
       "      <th>precipi</th>\n",
       "      <th>snowfalli</th>\n",
       "      <th>since1jancoolingdegreedaysnormal</th>\n",
       "      <th>precipm</th>\n",
       "      <th>snowfallm</th>\n",
       "      <th>thunder</th>\n",
       "      <th>monthtodateheatingdegreedays</th>\n",
       "      <th>meantempi</th>\n",
       "      <th>maxvism</th>\n",
       "      <th>meantempm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-05-01</td>\n",
       "      <td>1026</td>\n",
       "      <td>6</td>\n",
       "      <td>30.31</td>\n",
       "      <td>42</td>\n",
       "      <td>4646</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>157.23</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-05-02</td>\n",
       "      <td>1026</td>\n",
       "      <td>10</td>\n",
       "      <td>30.31</td>\n",
       "      <td>50</td>\n",
       "      <td>4653</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>157.23</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>57</td>\n",
       "      <td>16</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-05-03</td>\n",
       "      <td>1021</td>\n",
       "      <td>15</td>\n",
       "      <td>30.14</td>\n",
       "      <td>59</td>\n",
       "      <td>4660</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>157.23</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>65</td>\n",
       "      <td>16</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-05-04</td>\n",
       "      <td>1017</td>\n",
       "      <td>14</td>\n",
       "      <td>30.03</td>\n",
       "      <td>57</td>\n",
       "      <td>4667</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>157.23</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16</td>\n",
       "      <td>12.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>55</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-05-05</td>\n",
       "      <td>1016</td>\n",
       "      <td>4</td>\n",
       "      <td>30.01</td>\n",
       "      <td>39</td>\n",
       "      <td>4673</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>157.23</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>56</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 70 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  maxpressurem  maxdewptm  maxpressurei  maxdewpti  \\\n",
       "0  2011-05-01          1026          6         30.31         42   \n",
       "1  2011-05-02          1026         10         30.31         50   \n",
       "2  2011-05-03          1021         15         30.14         59   \n",
       "3  2011-05-04          1017         14         30.03         57   \n",
       "4  2011-05-05          1016          4         30.01         39   \n",
       "\n",
       "   since1julheatingdegreedaysnormal  heatingdegreedaysnormal  \\\n",
       "0                              4646                        8   \n",
       "1                              4653                        7   \n",
       "2                              4660                        7   \n",
       "3                              4667                        7   \n",
       "4                              4673                        6   \n",
       "\n",
       "   since1sepcoolingdegreedaysnormal  hail  since1julsnowfallm    ...      \\\n",
       "0                               NaN     0              157.23    ...       \n",
       "1                               NaN     0              157.23    ...       \n",
       "2                               NaN     0              157.23    ...       \n",
       "3                               NaN     0              157.23    ...       \n",
       "4                               NaN     0              157.23    ...       \n",
       "\n",
       "   precipi  snowfalli  since1jancoolingdegreedaysnormal  precipm  snowfallm  \\\n",
       "0      0.0        0.0                                13      0.0        0.0   \n",
       "1      0.0        0.0                                14      0.0        0.0   \n",
       "2      0.0        0.0                                15      0.0        0.0   \n",
       "3      0.5        0.0                                16     12.7        0.0   \n",
       "4      0.0        0.0                                17      0.0        0.0   \n",
       "\n",
       "   thunder  monthtodateheatingdegreedays  meantempi  maxvism  meantempm  \n",
       "0        0                             5         60       16         16  \n",
       "1        0                            13         57       16         14  \n",
       "2        0                            13         65       16         18  \n",
       "3        0                            23         55       16         13  \n",
       "4        0                            32         56       16         13  \n",
       "\n",
       "[5 rows x 70 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"datasets/weather_underground.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30, 70)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "    SELECT SUM(CASE WHEN rain = 1 THEN 1 ELSE 0 END) AS rainy_days FROM df;\n",
    "    \"\"\"\n",
    "rainy_days = pandasql.sqldf(q.lower(), locals())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rainy_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rainy_days\n",
       "0          10"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rainy_days"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}