kaggle_injest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Injesting Global Surface Temperature Data into BigQuery**\n",
    "This procedure will load the global surface temperature data in the five .csv files into BigQuery. The files are presently already uploaded into GCP but need to be split into tables through the following process. The data was obtained from Kaggle (https://www.kaggle.com/berkeleyearth/climate-change-earth-surface-temperature-data#GlobalTemperatures.csv)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#name the dataset\n",
    "dataset_id = \"kaggle_staging\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset 'electric-spark-266716:kaggle_staging' successfully created.\n"
     ]
    }
   ],
   "source": [
    "#create the actual dataset using bq cli\n",
    "!bq --location=US mk --dataset {dataset_id}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Global Land Temperatures by City**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r3e827a8b38fe96a1_000001700e2c0c9d_1 ... (65s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "#load the city data and create the table\n",
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Global_Land_Temperatures_by_City\\\n",
    "\"gs://global_surface_temperatures/global_surface_temperatures_dataset/GlobalLandTemperaturesByCity.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check the contents of the table by drawing 10 sample records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1846-11-01</td>\n",
       "      <td>18.032</td>\n",
       "      <td>2.435</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1853-03-01</td>\n",
       "      <td>18.621</td>\n",
       "      <td>1.895</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1856-02-01</td>\n",
       "      <td>21.789</td>\n",
       "      <td>2.249</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1867-08-01</td>\n",
       "      <td>11.281</td>\n",
       "      <td>1.130</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1874-07-01</td>\n",
       "      <td>8.758</td>\n",
       "      <td>0.616</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1904-04-01</td>\n",
       "      <td>18.426</td>\n",
       "      <td>0.351</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1910-02-01</td>\n",
       "      <td>23.400</td>\n",
       "      <td>0.843</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1928-06-01</td>\n",
       "      <td>10.649</td>\n",
       "      <td>0.228</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1939-02-01</td>\n",
       "      <td>22.783</td>\n",
       "      <td>0.356</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1942-12-01</td>\n",
       "      <td>21.319</td>\n",
       "      <td>0.540</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>34.56S</td>\n",
       "      <td>138.16E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty      City  \\\n",
       "0  1846-11-01              18.032                          2.435  Adelaide   \n",
       "1  1853-03-01              18.621                          1.895  Adelaide   \n",
       "2  1856-02-01              21.789                          2.249  Adelaide   \n",
       "3  1867-08-01              11.281                          1.130  Adelaide   \n",
       "4  1874-07-01               8.758                          0.616  Adelaide   \n",
       "5  1904-04-01              18.426                          0.351  Adelaide   \n",
       "6  1910-02-01              23.400                          0.843  Adelaide   \n",
       "7  1928-06-01              10.649                          0.228  Adelaide   \n",
       "8  1939-02-01              22.783                          0.356  Adelaide   \n",
       "9  1942-12-01              21.319                          0.540  Adelaide   \n",
       "\n",
       "     Country Latitude Longitude  \n",
       "0  Australia   34.56S   138.16E  \n",
       "1  Australia   34.56S   138.16E  \n",
       "2  Australia   34.56S   138.16E  \n",
       "3  Australia   34.56S   138.16E  \n",
       "4  Australia   34.56S   138.16E  \n",
       "5  Australia   34.56S   138.16E  \n",
       "6  Australia   34.56S   138.16E  \n",
       "7  Australia   34.56S   138.16E  \n",
       "8  Australia   34.56S   138.16E  \n",
       "9  Australia   34.56S   138.16E  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM kaggle_staging.Global_Land_Temperatures_by_City limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Query\n",
    "#### This query generates the temperature average across the entire time period for cities in the US, Canada, and Mexico and lists them as those from the coldest to hottest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>TotalAvg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>United States</td>\n",
       "      <td>-2.301646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Winnipeg</td>\n",
       "      <td>Canada</td>\n",
       "      <td>1.077861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Quebec</td>\n",
       "      <td>Canada</td>\n",
       "      <td>1.093973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Saskatoon</td>\n",
       "      <td>Canada</td>\n",
       "      <td>1.240279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Edmonton</td>\n",
       "      <td>Canada</td>\n",
       "      <td>1.368643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>Campeche</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>26.052384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>Carmen</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>26.129721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>Acapulco</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>26.162964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>Chetumal</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>26.609823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>Manzanillo</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>26.654316</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>370 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           City        Country   TotalAvg\n",
       "0     Anchorage  United States  -2.301646\n",
       "1      Winnipeg         Canada   1.077861\n",
       "2        Quebec         Canada   1.093973\n",
       "3     Saskatoon         Canada   1.240279\n",
       "4      Edmonton         Canada   1.368643\n",
       "..          ...            ...        ...\n",
       "365    Campeche         Mexico  26.052384\n",
       "366      Carmen         Mexico  26.129721\n",
       "367    Acapulco         Mexico  26.162964\n",
       "368    Chetumal         Mexico  26.609823\n",
       "369  Manzanillo         Mexico  26.654316\n",
       "\n",
       "[370 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT City,Country,AVG(AverageTemperature) as TotalAvg\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_City\n",
    "WHERE Country = \"United States\" or Country = \"Mexico\" or Country = \"Canada\"\n",
    "GROUP BY City,Country\n",
    "ORDER BY AVG(AverageTemperature)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Global Land Temperatures by Country**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r7d1503b383b1b062_000001700e2e861d_1 ... (8s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "#load the country data and create the table\n",
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Global_Land_Temperatures_by_Country\\\n",
    "\"gs://global_surface_temperatures/global_surface_temperatures_dataset/GlobalLandTemperaturesByCountry.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check the contents of the table by drawing 10 sample records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1818-03-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1819-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1820-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>9.478</td>\n",
       "      <td>4.000</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1949-10-01</td>\n",
       "      <td>8.773</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1966-04-01</td>\n",
       "      <td>7.564</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1975-12-01</td>\n",
       "      <td>-5.090</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1978-03-01</td>\n",
       "      <td>1.804</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1979-11-01</td>\n",
       "      <td>0.206</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty Country\n",
       "0  1818-03-01                 NaN                            NaN    Asia\n",
       "1  1819-05-01                 NaN                            NaN    Asia\n",
       "2  1820-01-01                 NaN                            NaN    Asia\n",
       "3  2013-09-01                 NaN                            NaN    Asia\n",
       "4  1818-04-01               9.478                          4.000    Asia\n",
       "5  1949-10-01               8.773                          0.125    Asia\n",
       "6  1966-04-01               7.564                          0.125    Asia\n",
       "7  1975-12-01              -5.090                          0.125    Asia\n",
       "8  1978-03-01               1.804                          0.125    Asia\n",
       "9  1979-11-01               0.206                          0.125    Asia"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM kaggle_staging.Global_Land_Temperatures_by_Country limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Queries\n",
    "#### This finds the number of countries represented within the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_countries</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_countries\n",
       "0            243"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(DISTINCT country) as num_countries \n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The below query lists the date, country, and average temperatures all above 15 degrees, ordered by hottest locations to coolest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>Country</th>\n",
       "      <th>AverageTemperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-07-01</td>\n",
       "      <td>Kuwait</td>\n",
       "      <td>38.842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000-07-01</td>\n",
       "      <td>Kuwait</td>\n",
       "      <td>38.705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-07-01</td>\n",
       "      <td>Kuwait</td>\n",
       "      <td>38.495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1998-08-01</td>\n",
       "      <td>Kuwait</td>\n",
       "      <td>38.436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-08-01</td>\n",
       "      <td>Kuwait</td>\n",
       "      <td>38.315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352749</th>\n",
       "      <td>1946-08-01</td>\n",
       "      <td>North America</td>\n",
       "      <td>15.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352750</th>\n",
       "      <td>2005-09-01</td>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>15.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352751</th>\n",
       "      <td>1853-02-01</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>15.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352752</th>\n",
       "      <td>1909-06-01</td>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>15.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352753</th>\n",
       "      <td>1852-09-01</td>\n",
       "      <td>North Korea</td>\n",
       "      <td>15.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>352754 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                dt         Country  AverageTemperature\n",
       "0       2012-07-01          Kuwait              38.842\n",
       "1       2000-07-01          Kuwait              38.705\n",
       "2       2010-07-01          Kuwait              38.495\n",
       "3       1998-08-01          Kuwait              38.436\n",
       "4       2000-08-01          Kuwait              38.315\n",
       "...            ...             ...                 ...\n",
       "352749  1946-08-01   North America              15.001\n",
       "352750  2005-09-01  Czech Republic              15.001\n",
       "352751  1853-02-01          Taiwan              15.001\n",
       "352752  1909-06-01  Czech Republic              15.001\n",
       "352753  1852-09-01     North Korea              15.000\n",
       "\n",
       "[352754 rows x 3 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT dt, Country, AverageTemperature\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Country\n",
    "WHERE AverageTemperature > 15\n",
    "ORDER BY AverageTemperature DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Global Land Temperatures by Major City**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r99046d38ac7f8bc_000001700e30285a_1 ... (5s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "#load the major city data and create the table\n",
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Global_Land_Temperatures_by_Major_City\\\n",
    "\"gs://global_surface_temperatures/global_surface_temperatures_dataset/GlobalLandTemperaturesByMajorCity.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check the contents of the table by drawing 10 sample records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1743-11-01</td>\n",
       "      <td>7.541</td>\n",
       "      <td>1.753</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1744-04-01</td>\n",
       "      <td>8.296</td>\n",
       "      <td>2.501</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1744-05-01</td>\n",
       "      <td>10.966</td>\n",
       "      <td>1.471</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1744-06-01</td>\n",
       "      <td>14.522</td>\n",
       "      <td>1.552</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1744-07-01</td>\n",
       "      <td>15.964</td>\n",
       "      <td>1.646</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1744-09-01</td>\n",
       "      <td>13.064</td>\n",
       "      <td>1.628</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1744-10-01</td>\n",
       "      <td>9.597</td>\n",
       "      <td>1.651</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1744-11-01</td>\n",
       "      <td>6.550</td>\n",
       "      <td>1.526</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1744-12-01</td>\n",
       "      <td>3.752</td>\n",
       "      <td>1.840</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1745-01-01</td>\n",
       "      <td>2.548</td>\n",
       "      <td>1.843</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>52.24N</td>\n",
       "      <td>0.00W</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty    City  \\\n",
       "0  1743-11-01               7.541                          1.753  London   \n",
       "1  1744-04-01               8.296                          2.501  London   \n",
       "2  1744-05-01              10.966                          1.471  London   \n",
       "3  1744-06-01              14.522                          1.552  London   \n",
       "4  1744-07-01              15.964                          1.646  London   \n",
       "5  1744-09-01              13.064                          1.628  London   \n",
       "6  1744-10-01               9.597                          1.651  London   \n",
       "7  1744-11-01               6.550                          1.526  London   \n",
       "8  1744-12-01               3.752                          1.840  London   \n",
       "9  1745-01-01               2.548                          1.843  London   \n",
       "\n",
       "          Country Latitude Longitude  \n",
       "0  United Kingdom   52.24N     0.00W  \n",
       "1  United Kingdom   52.24N     0.00W  \n",
       "2  United Kingdom   52.24N     0.00W  \n",
       "3  United Kingdom   52.24N     0.00W  \n",
       "4  United Kingdom   52.24N     0.00W  \n",
       "5  United Kingdom   52.24N     0.00W  \n",
       "6  United Kingdom   52.24N     0.00W  \n",
       "7  United Kingdom   52.24N     0.00W  \n",
       "8  United Kingdom   52.24N     0.00W  \n",
       "9  United Kingdom   52.24N     0.00W  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM kaggle_staging.Global_Land_Temperatures_by_Major_City limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Queries\n",
    "#### This query returns a list of cities and dates with an average temperature uncertainty of greater than 1 degree Celsius, ordered from greatest to least uncertainty."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1770-04-01</td>\n",
       "      <td>14.037</td>\n",
       "      <td>Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1768-01-01</td>\n",
       "      <td>13.971</td>\n",
       "      <td>Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1768-01-01</td>\n",
       "      <td>13.560</td>\n",
       "      <td>London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1768-01-01</td>\n",
       "      <td>13.224</td>\n",
       "      <td>Paris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1758-10-01</td>\n",
       "      <td>13.170</td>\n",
       "      <td>Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76363</th>\n",
       "      <td>1859-08-01</td>\n",
       "      <td>1.001</td>\n",
       "      <td>Nanjing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76364</th>\n",
       "      <td>1875-08-01</td>\n",
       "      <td>1.001</td>\n",
       "      <td>Changchun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76365</th>\n",
       "      <td>1884-03-01</td>\n",
       "      <td>1.001</td>\n",
       "      <td>Changchun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76366</th>\n",
       "      <td>1920-01-01</td>\n",
       "      <td>1.001</td>\n",
       "      <td>Seoul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76367</th>\n",
       "      <td>1880-09-01</td>\n",
       "      <td>1.001</td>\n",
       "      <td>Nagoya</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>76368 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               dt  AverageTemperatureUncertainty       City\n",
       "0      1770-04-01                         14.037     Berlin\n",
       "1      1768-01-01                         13.971     Berlin\n",
       "2      1768-01-01                         13.560     London\n",
       "3      1768-01-01                         13.224      Paris\n",
       "4      1758-10-01                         13.170     Berlin\n",
       "...           ...                            ...        ...\n",
       "76363  1859-08-01                          1.001    Nanjing\n",
       "76364  1875-08-01                          1.001  Changchun\n",
       "76365  1884-03-01                          1.001  Changchun\n",
       "76366  1920-01-01                          1.001      Seoul\n",
       "76367  1880-09-01                          1.001     Nagoya\n",
       "\n",
       "[76368 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT dt, AverageTemperatureUncertainty, City\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Major_City\n",
    "WHERE AverageTemperatureUncertainty > 1\n",
    "ORDER BY AverageTemperatureUncertainty DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Global Land Temperatures by State**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r436befc018cab104_000001700e31f39c_1 ... (15s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "#load the state data and create the table\n",
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Global_Land_Temperatures_by_State\\\n",
    "\"gs://global_surface_temperatures/global_surface_temperatures_dataset/GlobalLandTemperaturesByState.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check the contents of the table by drawing 10 sample records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1841-01-01</td>\n",
       "      <td>0.581</td>\n",
       "      <td>2.745</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1841-02-01</td>\n",
       "      <td>2.716</td>\n",
       "      <td>1.832</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1841-03-01</td>\n",
       "      <td>6.871</td>\n",
       "      <td>1.852</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1841-04-01</td>\n",
       "      <td>13.748</td>\n",
       "      <td>2.200</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1841-05-01</td>\n",
       "      <td>19.662</td>\n",
       "      <td>1.580</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1841-06-01</td>\n",
       "      <td>23.152</td>\n",
       "      <td>3.565</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1841-07-01</td>\n",
       "      <td>27.278</td>\n",
       "      <td>2.381</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1841-08-01</td>\n",
       "      <td>26.070</td>\n",
       "      <td>2.379</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1841-09-01</td>\n",
       "      <td>21.177</td>\n",
       "      <td>1.802</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1841-10-01</td>\n",
       "      <td>15.574</td>\n",
       "      <td>1.906</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty  State  \\\n",
       "0  1841-01-01               0.581                          2.745  Anhui   \n",
       "1  1841-02-01               2.716                          1.832  Anhui   \n",
       "2  1841-03-01               6.871                          1.852  Anhui   \n",
       "3  1841-04-01              13.748                          2.200  Anhui   \n",
       "4  1841-05-01              19.662                          1.580  Anhui   \n",
       "5  1841-06-01              23.152                          3.565  Anhui   \n",
       "6  1841-07-01              27.278                          2.381  Anhui   \n",
       "7  1841-08-01              26.070                          2.379  Anhui   \n",
       "8  1841-09-01              21.177                          1.802  Anhui   \n",
       "9  1841-10-01              15.574                          1.906  Anhui   \n",
       "\n",
       "  Country  \n",
       "0   China  \n",
       "1   China  \n",
       "2   China  \n",
       "3   China  \n",
       "4   China  \n",
       "5   China  \n",
       "6   China  \n",
       "7   China  \n",
       "8   China  \n",
       "9   China  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM kaggle_staging.Global_Land_Temperatures_by_State limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Queries\n",
    "#### This query computes the average temperature uncertainty of each state in the United States (and Washington, D.C.) across the entire time period and orders them from lowest to highest average temperature uncertainty."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TemperatureUncertainty</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.403861</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.579960</td>\n",
       "      <td>California</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.725007</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.735509</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.761608</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.794785</td>\n",
       "      <td>Texas</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.796113</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.810993</td>\n",
       "      <td>Utah</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.824798</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.825624</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.842171</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.878982</td>\n",
       "      <td>Idaho</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.969862</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.999244</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1.017800</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1.055524</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1.145671</td>\n",
       "      <td>South Dakota</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1.247329</td>\n",
       "      <td>Montana</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1.248401</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1.298521</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1.320205</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1.335562</td>\n",
       "      <td>New York</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1.344586</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1.361598</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1.370274</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1.373849</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1.376834</td>\n",
       "      <td>Florida</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1.377165</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1.377648</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1.385785</td>\n",
       "      <td>District Of Columbia</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1.385901</td>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1.386798</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1.390408</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1.395165</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1.398981</td>\n",
       "      <td>Maine</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1.406046</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1.410464</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1.422035</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1.427524</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1.439645</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1.442874</td>\n",
       "      <td>Georgia (State)</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1.454018</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1.456996</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1.474638</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1.476709</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1.519635</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1.541077</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1.544702</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1.637794</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1.671122</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1.742728</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    TemperatureUncertainty                 State        Country\n",
       "0                 0.403861                Hawaii  United States\n",
       "1                 0.579960            California  United States\n",
       "2                 0.725007               Arizona  United States\n",
       "3                 0.735509            Washington  United States\n",
       "4                 0.761608                Oregon  United States\n",
       "5                 0.794785                 Texas  United States\n",
       "6                 0.796113                Nevada  United States\n",
       "7                 0.810993                  Utah  United States\n",
       "8                 0.824798              Oklahoma  United States\n",
       "9                 0.825624            New Mexico  United States\n",
       "10                0.842171              Colorado  United States\n",
       "11                0.878982                 Idaho  United States\n",
       "12                0.969862             Louisiana  United States\n",
       "13                0.999244                Kansas  United States\n",
       "14                1.017800               Wyoming  United States\n",
       "15                1.055524              Nebraska  United States\n",
       "16                1.145671          South Dakota  United States\n",
       "17                1.247329               Montana  United States\n",
       "18                1.248401          North Dakota  United States\n",
       "19                1.298521           Mississippi  United States\n",
       "20                1.320205              Arkansas  United States\n",
       "21                1.335562              New York  United States\n",
       "22                1.344586          Pennsylvania  United States\n",
       "23                1.361598            New Jersey  United States\n",
       "24                1.370274              Maryland  United States\n",
       "25                1.373849           Connecticut  United States\n",
       "26                1.376834               Florida  United States\n",
       "27                1.377165               Vermont  United States\n",
       "28                1.377648          Rhode Island  United States\n",
       "29                1.385785  District Of Columbia  United States\n",
       "30                1.385901         New Hampshire  United States\n",
       "31                1.386798         Massachusetts  United States\n",
       "32                1.390408              Virginia  United States\n",
       "33                1.395165              Michigan  United States\n",
       "34                1.398981                 Maine  United States\n",
       "35                1.406046              Delaware  United States\n",
       "36                1.410464        North Carolina  United States\n",
       "37                1.422035         West Virginia  United States\n",
       "38                1.427524                  Ohio  United States\n",
       "39                1.439645               Alabama  United States\n",
       "40                1.442874       Georgia (State)  United States\n",
       "41                1.454018        South Carolina  United States\n",
       "42                1.456996             Tennessee  United States\n",
       "43                1.474638              Kentucky  United States\n",
       "44                1.476709               Indiana  United States\n",
       "45                1.519635              Illinois  United States\n",
       "46                1.541077              Missouri  United States\n",
       "47                1.544702             Wisconsin  United States\n",
       "48                1.637794                  Iowa  United States\n",
       "49                1.671122                Alaska  United States\n",
       "50                1.742728             Minnesota  United States"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT AVG(AverageTemperatureUncertainty) as TemperatureUncertainty, State, Country\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_State\n",
    "WHERE Country = \"United States\"\n",
    "GROUP BY State, Country\n",
    "ORDER BY AVG(AverageTemperatureUncertainty)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query checks to make sure that the 50 states and Washington, D.C. are within the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>District Of Columbia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Georgia (State)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Hawaii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Idaho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Illinois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Kentucky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Louisiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Montana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Nebraska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New Hampshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>North Carolina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>North Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Oklahoma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Rhode Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>South Carolina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Vermont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>West Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wisconsin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Wyoming</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   State\n",
       "0                Alabama\n",
       "1                 Alaska\n",
       "2                Arizona\n",
       "3               Arkansas\n",
       "4             California\n",
       "5               Colorado\n",
       "6            Connecticut\n",
       "7               Delaware\n",
       "8   District Of Columbia\n",
       "9                Florida\n",
       "10       Georgia (State)\n",
       "11                Hawaii\n",
       "12                 Idaho\n",
       "13              Illinois\n",
       "14               Indiana\n",
       "15                  Iowa\n",
       "16                Kansas\n",
       "17              Kentucky\n",
       "18             Louisiana\n",
       "19                 Maine\n",
       "20              Maryland\n",
       "21         Massachusetts\n",
       "22              Michigan\n",
       "23             Minnesota\n",
       "24           Mississippi\n",
       "25              Missouri\n",
       "26               Montana\n",
       "27              Nebraska\n",
       "28                Nevada\n",
       "29         New Hampshire\n",
       "30            New Jersey\n",
       "31            New Mexico\n",
       "32              New York\n",
       "33        North Carolina\n",
       "34          North Dakota\n",
       "35                  Ohio\n",
       "36              Oklahoma\n",
       "37                Oregon\n",
       "38          Pennsylvania\n",
       "39          Rhode Island\n",
       "40        South Carolina\n",
       "41          South Dakota\n",
       "42             Tennessee\n",
       "43                 Texas\n",
       "44                  Utah\n",
       "45               Vermont\n",
       "46              Virginia\n",
       "47            Washington\n",
       "48         West Virginia\n",
       "49             Wisconsin\n",
       "50               Wyoming"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT DISTINCT State\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_State\n",
    "WHERE Country = \"United States\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Global Temperatures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r7d52ae5a7127b4ae_000001700e352663_1 ... (3s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "#load the global temperatures data and create the table\n",
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Global_Temperatures\\\n",
    "\"gs://global_surface_temperatures/global_surface_temperatures_dataset/GlobalTemperatures.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check the contents of the table by drawing 10 sample records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>LandAverageTemperature</th>\n",
       "      <th>LandAverageTemperatureUncertainty</th>\n",
       "      <th>LandMaxTemperature</th>\n",
       "      <th>LandMaxTemperatureUncertainty</th>\n",
       "      <th>LandMinTemperature</th>\n",
       "      <th>LandMinTemperatureUncertainty</th>\n",
       "      <th>LandAndOceanAverageTemperature</th>\n",
       "      <th>LandAndOceanAverageTemperatureUncertainty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>3.034</td>\n",
       "      <td>3.574</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1750-02-01</td>\n",
       "      <td>3.083</td>\n",
       "      <td>3.702</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1750-03-01</td>\n",
       "      <td>5.626</td>\n",
       "      <td>3.076</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1750-04-01</td>\n",
       "      <td>8.490</td>\n",
       "      <td>2.451</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1750-05-01</td>\n",
       "      <td>11.573</td>\n",
       "      <td>2.072</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1750-06-01</td>\n",
       "      <td>12.937</td>\n",
       "      <td>1.724</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1750-07-01</td>\n",
       "      <td>15.868</td>\n",
       "      <td>1.911</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1750-08-01</td>\n",
       "      <td>14.750</td>\n",
       "      <td>2.231</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1750-09-01</td>\n",
       "      <td>11.413</td>\n",
       "      <td>2.637</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1750-10-01</td>\n",
       "      <td>6.367</td>\n",
       "      <td>2.668</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  LandAverageTemperature  LandAverageTemperatureUncertainty  \\\n",
       "0  1750-01-01                   3.034                              3.574   \n",
       "1  1750-02-01                   3.083                              3.702   \n",
       "2  1750-03-01                   5.626                              3.076   \n",
       "3  1750-04-01                   8.490                              2.451   \n",
       "4  1750-05-01                  11.573                              2.072   \n",
       "5  1750-06-01                  12.937                              1.724   \n",
       "6  1750-07-01                  15.868                              1.911   \n",
       "7  1750-08-01                  14.750                              2.231   \n",
       "8  1750-09-01                  11.413                              2.637   \n",
       "9  1750-10-01                   6.367                              2.668   \n",
       "\n",
       "  LandMaxTemperature LandMaxTemperatureUncertainty LandMinTemperature  \\\n",
       "0               None                          None               None   \n",
       "1               None                          None               None   \n",
       "2               None                          None               None   \n",
       "3               None                          None               None   \n",
       "4               None                          None               None   \n",
       "5               None                          None               None   \n",
       "6               None                          None               None   \n",
       "7               None                          None               None   \n",
       "8               None                          None               None   \n",
       "9               None                          None               None   \n",
       "\n",
       "  LandMinTemperatureUncertainty LandAndOceanAverageTemperature  \\\n",
       "0                          None                           None   \n",
       "1                          None                           None   \n",
       "2                          None                           None   \n",
       "3                          None                           None   \n",
       "4                          None                           None   \n",
       "5                          None                           None   \n",
       "6                          None                           None   \n",
       "7                          None                           None   \n",
       "8                          None                           None   \n",
       "9                          None                           None   \n",
       "\n",
       "  LandAndOceanAverageTemperatureUncertainty  \n",
       "0                                      None  \n",
       "1                                      None  \n",
       "2                                      None  \n",
       "3                                      None  \n",
       "4                                      None  \n",
       "5                                      None  \n",
       "6                                      None  \n",
       "7                                      None  \n",
       "8                                      None  \n",
       "9                                      None  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM kaggle_staging.Global_Temperatures limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exploratory Queries\n",
    "#### This query will list the date, LandAverageTemperature, and LandAndOceanAverageTemperature and order them in ascending order by the LandAverageTemperature for dates from 1830 onwards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>LandAverageTemperature</th>\n",
       "      <th>LandAndOceanAverageTemperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1838-01-01</td>\n",
       "      <td>-0.557</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1834-01-01</td>\n",
       "      <td>0.334</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1861-01-01</td>\n",
       "      <td>0.404</td>\n",
       "      <td>12.475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>0.500</td>\n",
       "      <td>12.702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1848-01-01</td>\n",
       "      <td>0.510</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2227</th>\n",
       "      <td>2007-07-01</td>\n",
       "      <td>15.230</td>\n",
       "      <td>17.485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2228</th>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>15.231</td>\n",
       "      <td>17.578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2229</th>\n",
       "      <td>1998-07-01</td>\n",
       "      <td>15.340</td>\n",
       "      <td>17.609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2230</th>\n",
       "      <td>2002-07-01</td>\n",
       "      <td>15.354</td>\n",
       "      <td>17.487000000000002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2231</th>\n",
       "      <td>2011-07-01</td>\n",
       "      <td>15.482</td>\n",
       "      <td>17.567999999999998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2232 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              dt  LandAverageTemperature LandAndOceanAverageTemperature\n",
       "0     1838-01-01                  -0.557                           None\n",
       "1     1834-01-01                   0.334                           None\n",
       "2     1861-01-01                   0.404                         12.475\n",
       "3     1893-01-01                   0.500                         12.702\n",
       "4     1848-01-01                   0.510                           None\n",
       "...          ...                     ...                            ...\n",
       "2227  2007-07-01                  15.230                         17.485\n",
       "2228  2009-07-01                  15.231                         17.578\n",
       "2229  1998-07-01                  15.340                         17.609\n",
       "2230  2002-07-01                  15.354             17.487000000000002\n",
       "2231  2011-07-01                  15.482             17.567999999999998\n",
       "\n",
       "[2232 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT dt, LandAverageTemperature, LandAndOceanAverageTemperature\n",
    "FROM kaggle_staging.Global_Temperatures\n",
    "WHERE dt >= \"1830-01-01\"\n",
    "ORDER BY LandAverageTemperature \n"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}