exercise-getting-started-with-sql-and-bigquery.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b9820a78",
   "metadata": {
    "papermill": {
     "duration": 0.018365,
     "end_time": "2022-01-14T14:14:45.329385",
     "exception": false,
     "start_time": "2022-01-14T14:14:45.311020",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "**This notebook is an exercise in the [SQL](https://www.kaggle.com/learn/intro-to-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/dansbecker/getting-started-with-sql-and-bigquery).**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b2340d2",
   "metadata": {
    "papermill": {
     "duration": 0.017232,
     "end_time": "2022-01-14T14:14:45.364292",
     "exception": false,
     "start_time": "2022-01-14T14:14:45.347060",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "The first test of your new data exploration skills uses data describing crime in the city of Chicago.\n",
    "\n",
    "Before you get started, run the following cell. It sets up the automated feedback system to review your answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e864c35a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:45.402982Z",
     "iopub.status.busy": "2022-01-14T14:14:45.401676Z",
     "iopub.status.idle": "2022-01-14T14:14:46.085682Z",
     "shell.execute_reply": "2022-01-14T14:14:46.086341Z",
     "shell.execute_reply.started": "2022-01-14T13:43:27.889657Z"
    },
    "papermill": {
     "duration": 0.705429,
     "end_time": "2022-01-14T14:14:46.086661",
     "exception": false,
     "start_time": "2022-01-14T14:14:45.381232",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n",
      "Setup Complete\n"
     ]
    }
   ],
   "source": [
    "# Set up feedack system\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.sql.ex1 import *\n",
    "print(\"Setup Complete\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1eeb34ae",
   "metadata": {
    "papermill": {
     "duration": 0.01765,
     "end_time": "2022-01-14T14:14:46.123745",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.106095",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Use the next code cell to fetch the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0808bbd3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:46.162382Z",
     "iopub.status.busy": "2022-01-14T14:14:46.161707Z",
     "iopub.status.idle": "2022-01-14T14:14:46.452423Z",
     "shell.execute_reply": "2022-01-14T14:14:46.451697Z",
     "shell.execute_reply.started": "2022-01-14T13:43:28.938669Z"
    },
    "papermill": {
     "duration": 0.311238,
     "end_time": "2022-01-14T14:14:46.452580",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.141342",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    }
   ],
   "source": [
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"chicago_crime\" dataset\n",
    "dataset_ref = client.dataset(\"chicago_crime\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b242c8db",
   "metadata": {
    "papermill": {
     "duration": 0.01765,
     "end_time": "2022-01-14T14:14:46.488700",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.471050",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Exercises\n",
    "\n",
    "### 1) Count tables in the dataset\n",
    "\n",
    "How many tables are in the Chicago Crime dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5e1667c3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:46.535875Z",
     "iopub.status.busy": "2022-01-14T14:14:46.534819Z",
     "iopub.status.idle": "2022-01-14T14:14:46.690468Z",
     "shell.execute_reply": "2022-01-14T14:14:46.689758Z",
     "shell.execute_reply.started": "2022-01-14T13:46:36.408235Z"
    },
    "papermill": {
     "duration": 0.182041,
     "end_time": "2022-01-14T14:14:46.690612",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.508571",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[<google.cloud.bigquery.table.TableListItem object at 0x7ff919a2cc50>]\n"
     ]
    }
   ],
   "source": [
    "# Write the code you need here to figure out the answer\n",
    "print(list(client.list_tables(dataset)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "69e4409c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:46.734119Z",
     "iopub.status.busy": "2022-01-14T14:14:46.733223Z",
     "iopub.status.idle": "2022-01-14T14:14:46.742803Z",
     "shell.execute_reply": "2022-01-14T14:14:46.743368Z",
     "shell.execute_reply.started": "2022-01-14T13:46:08.550012Z"
    },
    "papermill": {
     "duration": 0.034339,
     "end_time": "2022-01-14T14:14:46.743563",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.709224",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"1_CountTables\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "num_tables = 1  # Store the answer as num_tables and then run this cell\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9674d86",
   "metadata": {
    "papermill": {
     "duration": 0.019469,
     "end_time": "2022-01-14T14:14:46.784363",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.764894",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "For a hint or the solution, uncomment the appropriate line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ca976e92",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:46.831158Z",
     "iopub.status.busy": "2022-01-14T14:14:46.830280Z",
     "iopub.status.idle": "2022-01-14T14:14:46.835868Z",
     "shell.execute_reply": "2022-01-14T14:14:46.835328Z",
     "shell.execute_reply.started": "2022-01-14T13:48:18.024511Z"
    },
    "papermill": {
     "duration": 0.031296,
     "end_time": "2022-01-14T14:14:46.836023",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.804727",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 1, \"questionId\": \"1_CountTables\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "\n",
       "# List all the tables in the \"chicago_crime\" dataset\n",
       "tables = list(client.list_tables(dataset))\n",
       "\n",
       "# Print number of tables in the dataset\n",
       "print(len(tables))\n",
       "\n",
       "num_tables = 1\n",
       "\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "\n",
       "# List all the tables in the \"chicago_crime\" dataset\n",
       "tables = list(client.list_tables(dataset))\n",
       "\n",
       "# Print number of tables in the dataset\n",
       "print(len(tables))\n",
       "\n",
       "num_tables = 1\n",
       "\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#q_1.hint()\n",
    "q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c5f75a1",
   "metadata": {
    "papermill": {
     "duration": 0.020323,
     "end_time": "2022-01-14T14:14:46.878521",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.858198",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### 2) Explore the table schema\n",
    "\n",
    "How many columns in the `crime` table have `TIMESTAMP` data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "19426f15",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:46.928936Z",
     "iopub.status.busy": "2022-01-14T14:14:46.928208Z",
     "iopub.status.idle": "2022-01-14T14:14:47.238175Z",
     "shell.execute_reply": "2022-01-14T14:14:47.237581Z",
     "shell.execute_reply.started": "2022-01-14T13:53:19.557107Z"
    },
    "papermill": {
     "duration": 0.33798,
     "end_time": "2022-01-14T14:14:47.238341",
     "exception": false,
     "start_time": "2022-01-14T14:14:46.900361",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SchemaField('unique_key', 'INTEGER', 'REQUIRED', None, (), None),\n",
       " SchemaField('case_number', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('date', 'TIMESTAMP', 'NULLABLE', None, (), None),\n",
       " SchemaField('block', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('iucr', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('primary_type', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('description', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('location_description', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('arrest', 'BOOLEAN', 'NULLABLE', None, (), None),\n",
       " SchemaField('domestic', 'BOOLEAN', 'NULLABLE', None, (), None),\n",
       " SchemaField('beat', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('district', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('ward', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('community_area', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('fbi_code', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('x_coordinate', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('y_coordinate', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('year', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('updated_on', 'TIMESTAMP', 'NULLABLE', None, (), None),\n",
       " SchemaField('latitude', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('longitude', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('location', 'STRING', 'NULLABLE', None, (), None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write the code to figure out the answer\n",
    "tables = list(client.list_tables(dataset))\n",
    "\n",
    "crime_table = client.get_table(tables[0])\n",
    "crime_table.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7b2e0e3b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.290319Z",
     "iopub.status.busy": "2022-01-14T14:14:47.289579Z",
     "iopub.status.idle": "2022-01-14T14:14:47.293113Z",
     "shell.execute_reply": "2022-01-14T14:14:47.293735Z",
     "shell.execute_reply.started": "2022-01-14T14:02:39.598998Z"
    },
    "papermill": {
     "duration": 0.032479,
     "end_time": "2022-01-14T14:14:47.293943",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.261464",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'list'>\n",
      "SchemaField('unique_key', 'INTEGER', 'REQUIRED', None, (), None)\n"
     ]
    }
   ],
   "source": [
    "sch = crime_table.schema\n",
    "print(type(sch))\n",
    "print(sch[0])\n",
    "# print(sch[0][1]) # TypeError: 'SchemaField' object is not subscriptable\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f69cc86c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.343811Z",
     "iopub.status.busy": "2022-01-14T14:14:47.343069Z",
     "iopub.status.idle": "2022-01-14T14:14:47.347773Z",
     "shell.execute_reply": "2022-01-14T14:14:47.348418Z",
     "shell.execute_reply.started": "2022-01-14T14:06:02.428259Z"
    },
    "papermill": {
     "duration": 0.033683,
     "end_time": "2022-01-14T14:14:47.348719",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.315036",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"2_CountTimestampFields\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "num_timestamp_fields = 2 # Put your answer here\n",
    "\n",
    "# Check your answer\n",
    "q_2.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1acf751c",
   "metadata": {
    "papermill": {
     "duration": 0.021706,
     "end_time": "2022-01-14T14:14:47.392671",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.370965",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "For a hint or the solution, uncomment the appropriate line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d72bb8e3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.444217Z",
     "iopub.status.busy": "2022-01-14T14:14:47.443443Z",
     "iopub.status.idle": "2022-01-14T14:14:47.448636Z",
     "shell.execute_reply": "2022-01-14T14:14:47.449159Z",
     "shell.execute_reply.started": "2022-01-14T14:06:08.929166Z"
    },
    "papermill": {
     "duration": 0.033817,
     "end_time": "2022-01-14T14:14:47.449351",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.415534",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 1, \"questionId\": \"2_CountTimestampFields\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "\n",
       "# Construct a reference to the \"crime\" table\n",
       "table_ref = dataset_ref.table(\"crime\")\n",
       "\n",
       "# API request - fetch the table\n",
       "table = client.get_table(table_ref)\n",
       "\n",
       "# Print information on all the columns in the \"crime\" table in the \"chicago_crime\" dataset\n",
       "print(table.schema)\n",
       "\n",
       "num_timestamp_fields = 2\n",
       "\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "\n",
       "# Construct a reference to the \"crime\" table\n",
       "table_ref = dataset_ref.table(\"crime\")\n",
       "\n",
       "# API request - fetch the table\n",
       "table = client.get_table(table_ref)\n",
       "\n",
       "# Print information on all the columns in the \"crime\" table in the \"chicago_crime\" dataset\n",
       "print(table.schema)\n",
       "\n",
       "num_timestamp_fields = 2\n",
       "\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#q_2.hint()\n",
    "q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d04d548",
   "metadata": {
    "papermill": {
     "duration": 0.024406,
     "end_time": "2022-01-14T14:14:47.497319",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.472913",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### 3) Create a crime map\n",
    "\n",
    "If you wanted to create a map with a dot at the location of each crime, what are the names of the two fields you likely need to pull out of the `crime` table to plot the crimes on a map?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "02b202bb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.551252Z",
     "iopub.status.busy": "2022-01-14T14:14:47.550465Z",
     "iopub.status.idle": "2022-01-14T14:14:47.555185Z",
     "shell.execute_reply": "2022-01-14T14:14:47.555701Z",
     "shell.execute_reply.started": "2022-01-14T14:08:24.082998Z"
    },
    "papermill": {
     "duration": 0.032517,
     "end_time": "2022-01-14T14:14:47.555903",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.523386",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SchemaField('unique_key', 'INTEGER', 'REQUIRED', None, (), None),\n",
       " SchemaField('case_number', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('date', 'TIMESTAMP', 'NULLABLE', None, (), None),\n",
       " SchemaField('block', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('iucr', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('primary_type', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('description', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('location_description', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('arrest', 'BOOLEAN', 'NULLABLE', None, (), None),\n",
       " SchemaField('domestic', 'BOOLEAN', 'NULLABLE', None, (), None),\n",
       " SchemaField('beat', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('district', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('ward', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('community_area', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('fbi_code', 'STRING', 'NULLABLE', None, (), None),\n",
       " SchemaField('x_coordinate', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('y_coordinate', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('year', 'INTEGER', 'NULLABLE', None, (), None),\n",
       " SchemaField('updated_on', 'TIMESTAMP', 'NULLABLE', None, (), None),\n",
       " SchemaField('latitude', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('longitude', 'FLOAT', 'NULLABLE', None, (), None),\n",
       " SchemaField('location', 'STRING', 'NULLABLE', None, (), None)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write the code here to explore the data so you can find the answer\n",
    "sch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4bf39a91",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.611748Z",
     "iopub.status.busy": "2022-01-14T14:14:47.610742Z",
     "iopub.status.idle": "2022-01-14T14:14:47.617549Z",
     "shell.execute_reply": "2022-01-14T14:14:47.616909Z",
     "shell.execute_reply.started": "2022-01-14T14:08:53.43606Z"
    },
    "papermill": {
     "duration": 0.036481,
     "end_time": "2022-01-14T14:14:47.617713",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.581232",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_IdentifyFieldsForPlotting\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fields_for_plotting = [\"latitude\", \"longitude\"] # Put your answers here\n",
    "\n",
    "# Check your answer\n",
    "q_3.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb71ebc5",
   "metadata": {
    "papermill": {
     "duration": 0.024249,
     "end_time": "2022-01-14T14:14:47.667291",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.643042",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "For a hint or the solution, uncomment the appropriate line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3eb83056",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.723817Z",
     "iopub.status.busy": "2022-01-14T14:14:47.723128Z",
     "iopub.status.idle": "2022-01-14T14:14:47.726914Z",
     "shell.execute_reply": "2022-01-14T14:14:47.727520Z",
     "shell.execute_reply.started": "2022-01-14T14:09:02.760498Z"
    },
    "papermill": {
     "duration": 0.034997,
     "end_time": "2022-01-14T14:14:47.727689",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.692692",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"3_IdentifyFieldsForPlotting\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "fields_for_plotting = ['latitude', 'longitude']\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "fields_for_plotting = ['latitude', 'longitude']\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#q_3.hint()\n",
    "q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09ad5832",
   "metadata": {
    "papermill": {
     "duration": 0.024979,
     "end_time": "2022-01-14T14:14:47.778926",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.753947",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Thinking about the question above, there are a few columns that appear to have geographic data. Look at a few values (with the `list_rows()` command) to see if you can determine their relationship.  Two columns will still be hard to interpret. But it should be obvious how the `location` column relates to `latitude` and `longitude`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6695dd80",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-01-14T14:14:47.834102Z",
     "iopub.status.busy": "2022-01-14T14:14:47.833361Z",
     "iopub.status.idle": "2022-01-14T14:14:48.225041Z",
     "shell.execute_reply": "2022-01-14T14:14:48.225570Z",
     "shell.execute_reply.started": "2022-01-14T14:13:17.619752Z"
    },
    "papermill": {
     "duration": 0.420714,
     "end_time": "2022-01-14T14:14:48.225744",
     "exception": false,
     "start_time": "2022-01-14T14:14:47.805030",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:2: UserWarning: Cannot use bqstorage_client if max_results is set, reverting to fetching data with the tabledata.list endpoint.\n",
      "  \n"
     ]
    },
    {
     "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>unique_key</th>\n",
       "      <th>case_number</th>\n",
       "      <th>date</th>\n",
       "      <th>block</th>\n",
       "      <th>iucr</th>\n",
       "      <th>primary_type</th>\n",
       "      <th>description</th>\n",
       "      <th>location_description</th>\n",
       "      <th>arrest</th>\n",
       "      <th>domestic</th>\n",
       "      <th>...</th>\n",
       "      <th>ward</th>\n",
       "      <th>community_area</th>\n",
       "      <th>fbi_code</th>\n",
       "      <th>x_coordinate</th>\n",
       "      <th>y_coordinate</th>\n",
       "      <th>year</th>\n",
       "      <th>updated_on</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7788396</td>\n",
       "      <td>HS588138</td>\n",
       "      <td>2010-10-29 01:53:00+00:00</td>\n",
       "      <td>063XX S LEAMINGTON AVE</td>\n",
       "      <td>0313</td>\n",
       "      <td>ROBBERY</td>\n",
       "      <td>ARMED: OTHER DANGEROUS WEAPON</td>\n",
       "      <td>STREET</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>64</td>\n",
       "      <td>03</td>\n",
       "      <td>1143144.0</td>\n",
       "      <td>1862253.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.778057</td>\n",
       "      <td>-87.750783</td>\n",
       "      <td>(41.778057402, -87.75078283)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7641160</td>\n",
       "      <td>HS445318</td>\n",
       "      <td>2010-08-04 01:15:00+00:00</td>\n",
       "      <td>048XX N LONG AVE</td>\n",
       "      <td>1570</td>\n",
       "      <td>SEX OFFENSE</td>\n",
       "      <td>PUBLIC INDECENCY</td>\n",
       "      <td>PARK PROPERTY</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>1139465.0</td>\n",
       "      <td>1931490.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.968121</td>\n",
       "      <td>-87.762583</td>\n",
       "      <td>(41.968120549, -87.762582634)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7667608</td>\n",
       "      <td>HS472146</td>\n",
       "      <td>2010-08-19 12:00:00+00:00</td>\n",
       "      <td>111XX S AVENUE E</td>\n",
       "      <td>1305</td>\n",
       "      <td>CRIMINAL DAMAGE</td>\n",
       "      <td>CRIMINAL DEFACEMENT</td>\n",
       "      <td>RESIDENCE-GARAGE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>52</td>\n",
       "      <td>14</td>\n",
       "      <td>1203800.0</td>\n",
       "      <td>1831759.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.693037</td>\n",
       "      <td>-87.529463</td>\n",
       "      <td>(41.693037147, -87.529463069)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7670676</td>\n",
       "      <td>HS476118</td>\n",
       "      <td>2010-08-22 08:00:00+00:00</td>\n",
       "      <td>065XX N AVONDALE AVE</td>\n",
       "      <td>2890</td>\n",
       "      <td>PUBLIC PEACE VIOLATION</td>\n",
       "      <td>OTHER VIOLATION</td>\n",
       "      <td>STREET</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>41</td>\n",
       "      <td>9</td>\n",
       "      <td>26</td>\n",
       "      <td>1125156.0</td>\n",
       "      <td>1942906.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.999697</td>\n",
       "      <td>-87.814943</td>\n",
       "      <td>(41.999696749, -87.814942902)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7710534</td>\n",
       "      <td>HS491896</td>\n",
       "      <td>2010-08-31 02:25:00+00:00</td>\n",
       "      <td>050XX S SHIELDS AVE</td>\n",
       "      <td>2014</td>\n",
       "      <td>NARCOTICS</td>\n",
       "      <td>MANU/DELIVER: HEROIN (WHITE)</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>18</td>\n",
       "      <td>1174817.0</td>\n",
       "      <td>1871534.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.802878</td>\n",
       "      <td>-87.634392</td>\n",
       "      <td>(41.802877971, -87.634391647)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7721474</td>\n",
       "      <td>HS529000</td>\n",
       "      <td>2010-09-22 07:15:00+00:00</td>\n",
       "      <td>097XX S AVENUE L</td>\n",
       "      <td>1365</td>\n",
       "      <td>CRIMINAL TRESPASS</td>\n",
       "      <td>TO RESIDENCE</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>52</td>\n",
       "      <td>26</td>\n",
       "      <td>1201792.0</td>\n",
       "      <td>1841197.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.718987</td>\n",
       "      <td>-87.536495</td>\n",
       "      <td>(41.718986995, -87.536494827)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7724959</td>\n",
       "      <td>HS532366</td>\n",
       "      <td>2010-09-24 09:20:00+00:00</td>\n",
       "      <td>031XX S ARCHER AVE</td>\n",
       "      <td>1305</td>\n",
       "      <td>CRIMINAL DAMAGE</td>\n",
       "      <td>CRIMINAL DEFACEMENT</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>11</td>\n",
       "      <td>59</td>\n",
       "      <td>14</td>\n",
       "      <td>1165716.0</td>\n",
       "      <td>1883771.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.836656</td>\n",
       "      <td>-87.667421</td>\n",
       "      <td>(41.836655868, -87.667421303)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7738138</td>\n",
       "      <td>HS545335</td>\n",
       "      <td>2010-10-02 05:30:00+00:00</td>\n",
       "      <td>056XX N MASON AVE</td>\n",
       "      <td>2230</td>\n",
       "      <td>LIQUOR LAW VIOLATION</td>\n",
       "      <td>ILLEGAL CONSUMPTION BY MINOR</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>22</td>\n",
       "      <td>1135559.0</td>\n",
       "      <td>1937052.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>2018-02-10 03:50:01+00:00</td>\n",
       "      <td>41.983454</td>\n",
       "      <td>-87.776812</td>\n",
       "      <td>(41.983453682, -87.776812102)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7905012</td>\n",
       "      <td>HT134626</td>\n",
       "      <td>2011-01-25 10:45:00+00:00</td>\n",
       "      <td>062XX W NORWOOD ST</td>\n",
       "      <td>0630</td>\n",
       "      <td>BURGLARY</td>\n",
       "      <td>ATTEMPT FORCIBLE ENTRY</td>\n",
       "      <td>RESIDENCE</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>45</td>\n",
       "      <td>10</td>\n",
       "      <td>05</td>\n",
       "      <td>1133720.0</td>\n",
       "      <td>1939760.0</td>\n",
       "      <td>2011</td>\n",
       "      <td>2016-02-04 06:33:39+00:00</td>\n",
       "      <td>41.990917</td>\n",
       "      <td>-87.783512</td>\n",
       "      <td>(41.990917262, -87.783511785)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7909946</td>\n",
       "      <td>HT139476</td>\n",
       "      <td>2011-01-28 11:50:00+00:00</td>\n",
       "      <td>027XX N NORDICA AVE</td>\n",
       "      <td>1210</td>\n",
       "      <td>DECEPTIVE PRACTICE</td>\n",
       "      <td>THEFT OF LABOR/SERVICES</td>\n",
       "      <td>TAXICAB</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>36</td>\n",
       "      <td>18</td>\n",
       "      <td>11</td>\n",
       "      <td>1128621.0</td>\n",
       "      <td>1917411.0</td>\n",
       "      <td>2011</td>\n",
       "      <td>2016-02-04 06:33:39+00:00</td>\n",
       "      <td>41.929677</td>\n",
       "      <td>-87.802777</td>\n",
       "      <td>(41.929677462, -87.80277652)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_key case_number                      date                   block  \\\n",
       "0     7788396    HS588138 2010-10-29 01:53:00+00:00  063XX S LEAMINGTON AVE   \n",
       "1     7641160    HS445318 2010-08-04 01:15:00+00:00        048XX N LONG AVE   \n",
       "2     7667608    HS472146 2010-08-19 12:00:00+00:00        111XX S AVENUE E   \n",
       "3     7670676    HS476118 2010-08-22 08:00:00+00:00    065XX N AVONDALE AVE   \n",
       "4     7710534    HS491896 2010-08-31 02:25:00+00:00     050XX S SHIELDS AVE   \n",
       "5     7721474    HS529000 2010-09-22 07:15:00+00:00        097XX S AVENUE L   \n",
       "6     7724959    HS532366 2010-09-24 09:20:00+00:00      031XX S ARCHER AVE   \n",
       "7     7738138    HS545335 2010-10-02 05:30:00+00:00       056XX N MASON AVE   \n",
       "8     7905012    HT134626 2011-01-25 10:45:00+00:00      062XX W NORWOOD ST   \n",
       "9     7909946    HT139476 2011-01-28 11:50:00+00:00     027XX N NORDICA AVE   \n",
       "\n",
       "   iucr            primary_type                    description  \\\n",
       "0  0313                 ROBBERY  ARMED: OTHER DANGEROUS WEAPON   \n",
       "1  1570             SEX OFFENSE               PUBLIC INDECENCY   \n",
       "2  1305         CRIMINAL DAMAGE            CRIMINAL DEFACEMENT   \n",
       "3  2890  PUBLIC PEACE VIOLATION                OTHER VIOLATION   \n",
       "4  2014               NARCOTICS   MANU/DELIVER: HEROIN (WHITE)   \n",
       "5  1365       CRIMINAL TRESPASS                   TO RESIDENCE   \n",
       "6  1305         CRIMINAL DAMAGE            CRIMINAL DEFACEMENT   \n",
       "7  2230    LIQUOR LAW VIOLATION   ILLEGAL CONSUMPTION BY MINOR   \n",
       "8  0630                BURGLARY         ATTEMPT FORCIBLE ENTRY   \n",
       "9  1210      DECEPTIVE PRACTICE        THEFT OF LABOR/SERVICES   \n",
       "\n",
       "  location_description  arrest  domestic  ...  ward  community_area  fbi_code  \\\n",
       "0               STREET   False     False  ...    13              64        03   \n",
       "1        PARK PROPERTY    True     False  ...    45              11        17   \n",
       "2     RESIDENCE-GARAGE   False     False  ...    10              52        14   \n",
       "3               STREET   False     False  ...    41               9        26   \n",
       "4            RESIDENCE    True     False  ...     3              37        18   \n",
       "5             SIDEWALK    True     False  ...    10              52        26   \n",
       "6            RESIDENCE   False     False  ...    11              59        14   \n",
       "7            RESIDENCE    True     False  ...    45              11        22   \n",
       "8            RESIDENCE   False     False  ...    45              10        05   \n",
       "9              TAXICAB   False     False  ...    36              18        11   \n",
       "\n",
       "   x_coordinate y_coordinate  year                updated_on   latitude  \\\n",
       "0     1143144.0    1862253.0  2010 2018-02-10 03:50:01+00:00  41.778057   \n",
       "1     1139465.0    1931490.0  2010 2018-02-10 03:50:01+00:00  41.968121   \n",
       "2     1203800.0    1831759.0  2010 2018-02-10 03:50:01+00:00  41.693037   \n",
       "3     1125156.0    1942906.0  2010 2018-02-10 03:50:01+00:00  41.999697   \n",
       "4     1174817.0    1871534.0  2010 2018-02-10 03:50:01+00:00  41.802878   \n",
       "5     1201792.0    1841197.0  2010 2018-02-10 03:50:01+00:00  41.718987   \n",
       "6     1165716.0    1883771.0  2010 2018-02-10 03:50:01+00:00  41.836656   \n",
       "7     1135559.0    1937052.0  2010 2018-02-10 03:50:01+00:00  41.983454   \n",
       "8     1133720.0    1939760.0  2011 2016-02-04 06:33:39+00:00  41.990917   \n",
       "9     1128621.0    1917411.0  2011 2016-02-04 06:33:39+00:00  41.929677   \n",
       "\n",
       "   longitude                       location  \n",
       "0 -87.750783   (41.778057402, -87.75078283)  \n",
       "1 -87.762583  (41.968120549, -87.762582634)  \n",
       "2 -87.529463  (41.693037147, -87.529463069)  \n",
       "3 -87.814943  (41.999696749, -87.814942902)  \n",
       "4 -87.634392  (41.802877971, -87.634391647)  \n",
       "5 -87.536495  (41.718986995, -87.536494827)  \n",
       "6 -87.667421  (41.836655868, -87.667421303)  \n",
       "7 -87.776812  (41.983453682, -87.776812102)  \n",
       "8 -87.783512  (41.990917262, -87.783511785)  \n",
       "9 -87.802777   (41.929677462, -87.80277652)  \n",
       "\n",
       "[10 rows x 22 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Scratch space for your code\n",
    "df = client.list_rows(crime_table, max_results=10).to_dataframe()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "202e04ff",
   "metadata": {
    "papermill": {
     "duration": 0.026112,
     "end_time": "2022-01-14T14:14:48.278454",
     "exception": false,
     "start_time": "2022-01-14T14:14:48.252342",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Keep going\n",
    "\n",
    "You've looked at the schema, but you haven't yet done anything exciting with the data itself. Things get more interesting when you get to the data, so keep going to **[write your first SQL query](https://www.kaggle.com/dansbecker/select-from-where).**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a46b0d19",
   "metadata": {
    "papermill": {
     "duration": 0.027746,
     "end_time": "2022-01-14T14:14:48.334044",
     "exception": false,
     "start_time": "2022-01-14T14:14:48.306298",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/intro-to-sql/discussion) to chat with other learners.*"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 14.840306,
   "end_time": "2022-01-14T14:14:48.972143",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2022-01-14T14:14:34.131837",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}