sql-equivalents.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tutorial will teach you how to convert sql to pandas. (And vice versa).\n",
    "This uses two csv files, which are in this repo:\n",
    "\n",
    "* buildings.csv [list of tallest building](https://en.wikipedia.org/wiki/List_of_tallest_buildings_in_the_world) taken from wikipedia. \n",
    "* continents.csv: A mapping of countries to continents\n",
    "\n",
    "We also have a buildings.db which is a sqlite file which has the same data as csv files. We will show a sql statement, and then a pandas expression to get the same result. The sql statement can be used with the buildings.db. The required libraries can be installed with `pip install -r requirements.txt`\n",
    "\n",
    "Let's start by getting the data in a dataframe. `.head` gives us the first 5 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Burj Khalifa</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>828</td>\n",
       "      <td>163</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shanghai Tower</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>China</td>\n",
       "      <td>632</td>\n",
       "      <td>123</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abraj Al-Bait Clock Tower</td>\n",
       "      <td>Mecca</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>601</td>\n",
       "      <td>120</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ping An Finance Center</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>China</td>\n",
       "      <td>599</td>\n",
       "      <td>115</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>One World Trade Center</td>\n",
       "      <td>New York City</td>\n",
       "      <td>United States</td>\n",
       "      <td>541</td>\n",
       "      <td>104</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Building           City        Country  Height  Floors  \\\n",
       "0               Burj Khalifa          Dubai            UAE     828     163   \n",
       "1             Shanghai Tower       Shanghai          China     632     123   \n",
       "2  Abraj Al-Bait Clock Tower          Mecca   Saudi Arabia     601     120   \n",
       "3     Ping An Finance Center       Shenzhen          China     599     115   \n",
       "4     One World Trade Center  New York City  United States     541     104   \n",
       "\n",
       "   Built  \n",
       "0   2010  \n",
       "1   2015  \n",
       "2   2012  \n",
       "3   2015  \n",
       "4   2014  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"./buildings.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "`.describe` Gives us some info about the dataframe. Not terribly useful, but lets try it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>121.000000</td>\n",
       "      <td>121.000000</td>\n",
       "      <td>121.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>358.165289</td>\n",
       "      <td>77.264463</td>\n",
       "      <td>2006.735537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>79.653034</td>\n",
       "      <td>17.799236</td>\n",
       "      <td>13.691097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>300.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>1930.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>308.000000</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>2007.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>329.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>2011.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>380.000000</td>\n",
       "      <td>85.000000</td>\n",
       "      <td>2015.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>828.000000</td>\n",
       "      <td>163.000000</td>\n",
       "      <td>2015.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Height      Floors        Built\n",
       "count  121.000000  121.000000   121.000000\n",
       "mean   358.165289   77.264463  2006.735537\n",
       "std     79.653034   17.799236    13.691097\n",
       "min    300.000000   36.000000  1930.000000\n",
       "25%    308.000000   68.000000  2007.000000\n",
       "50%    329.000000   75.000000  2011.000000\n",
       "75%    380.000000   85.000000  2015.000000\n",
       "max    828.000000  163.000000  2015.000000"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to get the first 10 results   \n",
    "`SELECT * FROM buildings LIMIT 10`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Burj Khalifa</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>828</td>\n",
       "      <td>163</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shanghai Tower</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>China</td>\n",
       "      <td>632</td>\n",
       "      <td>123</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abraj Al-Bait Clock Tower</td>\n",
       "      <td>Mecca</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>601</td>\n",
       "      <td>120</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ping An Finance Center</td>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>China</td>\n",
       "      <td>599</td>\n",
       "      <td>115</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>One World Trade Center</td>\n",
       "      <td>New York City</td>\n",
       "      <td>United States</td>\n",
       "      <td>541</td>\n",
       "      <td>104</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CTF Finance Centre</td>\n",
       "      <td>Guangzhou</td>\n",
       "      <td>China</td>\n",
       "      <td>530</td>\n",
       "      <td>111</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Taipei 101</td>\n",
       "      <td>Taipei</td>\n",
       "      <td>Republic of China</td>\n",
       "      <td>509</td>\n",
       "      <td>101</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Shanghai World Financial Center</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>China</td>\n",
       "      <td>492</td>\n",
       "      <td>101</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>International Commerce Centre The Ritz-Carlton...</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>China</td>\n",
       "      <td>484</td>\n",
       "      <td>118</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Petronas Tower 1</td>\n",
       "      <td>Kuala Lumpur</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>452</td>\n",
       "      <td>88</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Building           City  \\\n",
       "0                                       Burj Khalifa          Dubai   \n",
       "1                                     Shanghai Tower       Shanghai   \n",
       "2                          Abraj Al-Bait Clock Tower          Mecca   \n",
       "3                             Ping An Finance Center       Shenzhen   \n",
       "4                             One World Trade Center  New York City   \n",
       "5                                 CTF Finance Centre      Guangzhou   \n",
       "6                                         Taipei 101         Taipei   \n",
       "7                    Shanghai World Financial Center       Shanghai   \n",
       "8  International Commerce Centre The Ritz-Carlton...      Hong Kong   \n",
       "9                                   Petronas Tower 1   Kuala Lumpur   \n",
       "\n",
       "             Country  Height  Floors  Built  \n",
       "0                UAE     828     163   2010  \n",
       "1              China     632     123   2015  \n",
       "2       Saudi Arabia     601     120   2012  \n",
       "3              China     599     115   2015  \n",
       "4      United States     541     104   2014  \n",
       "5              China     530     111   2015  \n",
       "6  Republic of China     509     101   2004  \n",
       "7              China     492     101   2008  \n",
       "8              China     484     118   2010  \n",
       "9           Malaysia     452      88   1998  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get only the City and Building name   \n",
    "\n",
    "    SELECT city, building FROM buildings LIMIT 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Building</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dubai</td>\n",
       "      <td>Burj Khalifa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shanghai</td>\n",
       "      <td>Shanghai Tower</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mecca</td>\n",
       "      <td>Abraj Al-Bait Clock Tower</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       City                   Building\n",
       "0     Dubai               Burj Khalifa\n",
       "1  Shanghai             Shanghai Tower\n",
       "2     Mecca  Abraj Al-Bait Clock Tower"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " df[[\"City\", \"Building\"]][:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the three oldest buildings in our data  \n",
    "\n",
    "    SELECT city, building, built FROM buildings ORDER BY built LIMIT 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Building</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>New York City</td>\n",
       "      <td>Chrysler Building</td>\n",
       "      <td>1930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New York City</td>\n",
       "      <td>Empire State Building</td>\n",
       "      <td>1931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>John Hancock Center</td>\n",
       "      <td>1969</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             City               Building  Built\n",
       "74  New York City      Chrysler Building   1930\n",
       "29  New York City  Empire State Building   1931\n",
       "46        Chicago    John Hancock Center   1969"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort(\"Built\")[[\"City\", \"Building\", \"Built\"]][:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What the the three highest buildings?   \n",
    "\n",
    "    SELECT city, building, height FROM buildings ORDER BY height desc LIMIT 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Building</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Dubai</td>\n",
       "      <td>Burj Khalifa</td>\n",
       "      <td>828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shanghai</td>\n",
       "      <td>Shanghai Tower</td>\n",
       "      <td>632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mecca</td>\n",
       "      <td>Abraj Al-Bait Clock Tower</td>\n",
       "      <td>601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       City                   Building  Height\n",
       "0     Dubai               Burj Khalifa     828\n",
       "1  Shanghai             Shanghai Tower     632\n",
       "2     Mecca  Abraj Al-Bait Clock Tower     601"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort(\"Height\", ascending=False)[[\"City\", \"Building\", \"Height\"]][:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the three tallest buildings in Dubai?   \n",
    "\n",
    "    SELECT city, building, height FROM buildings \n",
    "    WHERE city='Dubai' ORDER BY height desc LIMIT 3;\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Burj Khalifa</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>828</td>\n",
       "      <td>163</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Marina 101</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>426</td>\n",
       "      <td>101</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Princess Tower</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>414</td>\n",
       "      <td>101</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Building   City Country  Height  Floors  Built\n",
       "0     Burj Khalifa  Dubai     UAE     828     163   2010\n",
       "15      Marina 101  Dubai     UAE     426     101   2015\n",
       "19  Princess Tower  Dubai     UAE     414     101   2012"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"City\"]=='Dubai'].sort(\"Height\", ascending=False)[:3]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the five tallest buildings in Dubai or Chicago?\n",
    "\n",
    "    SELECT * FROM buildings WHERE city='Dubai' \n",
    "    or city=='Chicago' ORDER BY height desc LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Burj Khalifa</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>828</td>\n",
       "      <td>163</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Willis Tower (formerly the Sears Tower)</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>United States</td>\n",
       "      <td>442</td>\n",
       "      <td>108</td>\n",
       "      <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Marina 101</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>426</td>\n",
       "      <td>101</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Trump International Hotel and Towe</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>United States</td>\n",
       "      <td>423</td>\n",
       "      <td>98</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Princess Tower</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>414</td>\n",
       "      <td>101</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Building     City        Country  Height  \\\n",
       "0                              Burj Khalifa    Dubai            UAE     828   \n",
       "12  Willis Tower (formerly the Sears Tower)  Chicago  United States     442   \n",
       "15                               Marina 101    Dubai            UAE     426   \n",
       "17       Trump International Hotel and Towe  Chicago  United States     423   \n",
       "19                           Princess Tower    Dubai            UAE     414   \n",
       "\n",
       "    Floors  Built  \n",
       "0      163   2010  \n",
       "12     108   1974  \n",
       "15     101   2015  \n",
       "17      98   2009  \n",
       "19     101   2012  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df[\"City\"]=='Dubai') | (df[\"City\"]==\"Chicago\")].sort(\"Height\", ascending=False)[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Among the buildings built in 20th century, which has the most floors?\n",
    "\n",
    "    SELECT * FROM buildings WHERE built < 2000 \n",
    "    ORDER BY floors DESC LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Willis Tower (formerly the Sears Tower)</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>United States</td>\n",
       "      <td>442</td>\n",
       "      <td>108</td>\n",
       "      <td>1974</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Building     City        Country  Height  \\\n",
       "12  Willis Tower (formerly the Sears Tower)  Chicago  United States     442   \n",
       "\n",
       "    Floors  Built  \n",
       "12     108   1974  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"Built\"]<2000].sort(\"Floors\", ascending=False)[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Among the buildings built in 20th century, which is the tallest?\n",
    "\n",
    "    SELECT * FROM buildings WHERE built < 2000\n",
    "    ORDER BY height DESC LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Petronas Tower 1</td>\n",
       "      <td>Kuala Lumpur</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>452</td>\n",
       "      <td>88</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Building          City   Country  Height  Floors  Built\n",
       "9  Petronas Tower 1  Kuala Lumpur  Malaysia     452      88   1998"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"Built\"]<2000].sort(\"Height\", ascending=False)[:1]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many different countries and cities do we have in our data?\n",
    "\n",
    "    SELECT COUNT(DISTINCT(country)) FROM buildings;\n",
    "    SELECT COUNT(DISTINCT(city)) FROM buildings;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[\"Country\"].unique())\n",
    "len(df[\"City\"].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which cities make an appearance in top 20 tallest building list?\n",
    "\n",
    "    SELECT DISTINCT(city) from \n",
    "    (SELECT * FROM buildings ORDER BY height DESC LIMIT 20);\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Dubai', 'Shanghai', 'Mecca', 'Shenzhen', 'New York City',\n",
       "       'Guangzhou', 'Taipei', 'Hong Kong', 'Kuala Lumpur', 'Nanjing',\n",
       "       'Chicago'], dtype=object)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " df[:20].sort(\"Height\", ascending=False)[\"City\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many appearances does each country make in a top 100 list?\n",
    "\n",
    "    SELECT country, COUNT(country) AS building_count \n",
    "    FROM \n",
    "    (select * from buildings order by height limit 100) \n",
    "    GROUP BY country \n",
    "    ORDER BY building_count DESC;\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "China                44\n",
       "UAE                  24\n",
       "United States        15\n",
       "Russia                4\n",
       "Saudi Arabia          3\n",
       "Malaysia              3\n",
       "Republic of China     2\n",
       "United Kingdom        1\n",
       "North Korea           1\n",
       "Australia             1\n",
       "Kuwait                1\n",
       "Vietnam               1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[:100][\"Country\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the average floors and average height, per country.\n",
    "   \n",
    "    SELECT country, AVG(floors), AVG(height) from \n",
    "        (SELECT * FROM buildings ORDER BY height DESC LIMIT 100)\n",
    "        GROUP BY country;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Floors</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>80.000000</td>\n",
       "      <td>323.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chile</th>\n",
       "      <td>62.000000</td>\n",
       "      <td>300.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>69.886364</td>\n",
       "      <td>330.840909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>60.000000</td>\n",
       "      <td>300.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kuwait</th>\n",
       "      <td>60.000000</td>\n",
       "      <td>300.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malaysia</th>\n",
       "      <td>55.000000</td>\n",
       "      <td>310.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Korea</th>\n",
       "      <td>105.000000</td>\n",
       "      <td>330.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Qatar</th>\n",
       "      <td>36.000000</td>\n",
       "      <td>300.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Republic of China</th>\n",
       "      <td>85.000000</td>\n",
       "      <td>348.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>80.600000</td>\n",
       "      <td>335.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saudi Arabia</th>\n",
       "      <td>63.250000</td>\n",
       "      <td>324.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Korea</th>\n",
       "      <td>74.000000</td>\n",
       "      <td>303.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thailand</th>\n",
       "      <td>85.000000</td>\n",
       "      <td>304.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UAE</th>\n",
       "      <td>74.714286</td>\n",
       "      <td>338.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>309.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>72.384615</td>\n",
       "      <td>325.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vietnam</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>329.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Floors      Height\n",
       "Country                                  \n",
       "Australia           80.000000  323.000000\n",
       "Chile               62.000000  300.000000\n",
       "China               69.886364  330.840909\n",
       "Japan               60.000000  300.000000\n",
       "Kuwait              60.000000  300.000000\n",
       "Malaysia            55.000000  310.000000\n",
       "North Korea        105.000000  330.000000\n",
       "Qatar               36.000000  300.000000\n",
       "Republic of China   85.000000  348.000000\n",
       "Russia              80.600000  335.600000\n",
       "Saudi Arabia        63.250000  324.500000\n",
       "South Korea         74.000000  303.000000\n",
       "Thailand            85.000000  304.000000\n",
       "UAE                 74.714286  338.333333\n",
       "United Kingdom      72.000000  309.000000\n",
       "United States       72.384615  325.000000\n",
       "Vietnam             72.000000  329.000000"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort(\"Height\")[:100].groupby(\"Country\").mean()[[\"Floors\", \"Height\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the average floors and average height, per country, sorted by height\n",
    "\n",
    "    SELECT country, AVG(floors) as avg_floor, AVG(height) as avg_height from \n",
    "        (SELECT * FROM buildings ORDER BY height DESC LIMIT 100)\n",
    "        GROUP BY country\n",
    "        ORDER by avg_height DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Floors</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Saudi Arabia</th>\n",
       "      <td>88.333333</td>\n",
       "      <td>431.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Republic of China</th>\n",
       "      <td>93.000000</td>\n",
       "      <td>428.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kuwait</th>\n",
       "      <td>80.000000</td>\n",
       "      <td>413.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malaysia</th>\n",
       "      <td>77.000000</td>\n",
       "      <td>404.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>78.181818</td>\n",
       "      <td>370.886364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UAE</th>\n",
       "      <td>80.583333</td>\n",
       "      <td>365.541667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>80.266667</td>\n",
       "      <td>363.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>81.750000</td>\n",
       "      <td>344.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Korea</th>\n",
       "      <td>105.000000</td>\n",
       "      <td>330.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vietnam</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>329.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>80.000000</td>\n",
       "      <td>323.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>309.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Floors      Height\n",
       "Country                                  \n",
       "Saudi Arabia        88.333333  431.333333\n",
       "Republic of China   93.000000  428.500000\n",
       "Kuwait              80.000000  413.000000\n",
       "Malaysia            77.000000  404.666667\n",
       "China               78.181818  370.886364\n",
       "UAE                 80.583333  365.541667\n",
       "United States       80.266667  363.466667\n",
       "Russia              81.750000  344.000000\n",
       "North Korea        105.000000  330.000000\n",
       "Vietnam             72.000000  329.000000\n",
       "Australia           80.000000  323.000000\n",
       "United Kingdom      72.000000  309.000000"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[:100].groupby(\"Country\").mean()[[\"Floors\", \"Height\"]].sort(\"Height\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What years were the buildings completed?\n",
    "\n",
    "    SELECT built, count(built) FROM buildings GROUP BY built;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Built\n",
       "1930     1\n",
       "1931     1\n",
       "1969     1\n",
       "1973     1\n",
       "1974     1\n",
       "1982     1\n",
       "1983     1\n",
       "1989     2\n",
       "1990     2\n",
       "1992     2\n",
       "1996     1\n",
       "1997     3\n",
       "1998     3\n",
       "1999     2\n",
       "2000     2\n",
       "2001     1\n",
       "2002     1\n",
       "2003     1\n",
       "2004     1\n",
       "2005     2\n",
       "2007     4\n",
       "2008     3\n",
       "2009     5\n",
       "2010    11\n",
       "2011     9\n",
       "2012    13\n",
       "2013     6\n",
       "2014     8\n",
       "2015    32\n",
       "Name: Building, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(\"Built\").count()[\"Building\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets do some joins\n",
    "\n",
    "    SELECT * from BUILDING LEFT JOIN continents \n",
    "    ON buildings.country=continents.country LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Building</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Height</th>\n",
       "      <th>Floors</th>\n",
       "      <th>Built</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Continent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Burj Khalifa</td>\n",
       "      <td>Dubai</td>\n",
       "      <td>UAE</td>\n",
       "      <td>828</td>\n",
       "      <td>163</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Building   City Country  Height  Floors  Built  Unnamed: 0 Continent\n",
       "0  Burj Khalifa  Dubai     UAE     828     163   2010           1      Asia"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"./buildings.csv\")\n",
    "continents_df = pd.read_csv(\"./continents.csv\")\n",
    "df.merge(continents_df, on=\"Country\")[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "What is the average floor count and height on an each continent basis?\n",
    "\n",
    "    SELECT continent, avg(floors), avg(height) FROM buildings LEFT JOIN continents \n",
    "    ON buildings.country=continents.country \n",
    "    GROUP by continent;\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Floors</th>\n",
       "      <th>Height</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Asia</th>\n",
       "      <td>77.010417</td>\n",
       "      <td>361.156250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>80.000000</td>\n",
       "      <td>323.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>79.166667</td>\n",
       "      <td>331.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North America</th>\n",
       "      <td>78.764706</td>\n",
       "      <td>356.294118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South America</th>\n",
       "      <td>62.000000</td>\n",
       "      <td>300.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Floors      Height\n",
       "Continent                           \n",
       "Asia           77.010417  361.156250\n",
       "Australia      80.000000  323.000000\n",
       "Europe         79.166667  331.166667\n",
       "North America  78.764706  356.294118\n",
       "South America  62.000000  300.000000"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "continents_df = pd.read_csv(\"./continents.csv\")\n",
    "df.merge(continents_df, on=\"Country\").groupby(\"Continent\").mean()[[\"Floors\", \"Height\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Thank you. If you *think in sql*, I hope this tutorial allows you to transform your queries to pandas equivalents."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}