EC1_1__case.intro_to_sql.review_module_fellow.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "8975b2e5dbf4cf7f99f6f49aead1a7fa",
     "grade": false,
     "grade_id": "cell-3382eccab4f96f1b",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Review module"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "3315028a47179bf5813f22883267c47c",
     "grade": false,
     "grade_id": "cell-32a2a3d5089fbc41",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "**Instructions**\n",
    "\n",
    "In order to complete this review module, we recommend you follow these instructions:\n",
    "\n",
    "1. Complete the functions provided to you in this notebook, but do **not** change the name of the function or the name(s) of the argument(s). If you do that, the autograder will fail and you will not receive any points.\n",
    "2. Run all the function-definition cells before you run the testing cells. The functions must exist before they are graded!\n",
    "3. Read the function docstrings carefully. They contain additional information about how the code should look (a [docstring](https://www.datacamp.com/community/tutorials/docstrings-python) is the stuff that comes between the triple quotes).\n",
    "4. Some functions may require several outputs (the docstrings tell you which ones). Make sure they are returned in the right order.\n",
    "5. Remove from each function the code `raise NotImplementedError()` and replace it with your implementation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "52fd24493512209102196af36373a225",
     "grade": false,
     "grade_id": "cell-f08959077e8ac3af",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "For this review module, we will be using the same database we introduced in the main lecture. This database consists of three tables: `agent` (a list of the call center agents), `customer` (a list of the customers and their demographic data), and `call` (all the calls that the agents made and whether they resulted in a purchase or not).\n",
    "\n",
    "Run the cells below to load the SQLite database and see the names of the columns of each table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "36b94ef1ca12ec2aff3c4e97605b74d7",
     "grade": false,
     "grade_id": "cell-96a825feec3e4bfa",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install ipython-sql sqlalchemy pandas\n",
    "import sqlalchemy\n",
    "sqlalchemy.create_engine(\"sqlite:///call_center_database.db\")\n",
    "%load_ext sql\n",
    "%sql sqlite:///call_center_database.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///call_center_database.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>AgentID</th>\n",
       "        <th>Name</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from agent limit 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///call_center_database.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CustomerID</th>\n",
       "        <th>Name</th>\n",
       "        <th>Occupation</th>\n",
       "        <th>Email</th>\n",
       "        <th>Company</th>\n",
       "        <th>PhoneNumber</th>\n",
       "        <th>Age</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from customer limit 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///call_center_database.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CallID</th>\n",
       "        <th>AgentID</th>\n",
       "        <th>CustomerID</th>\n",
       "        <th>PickedUp</th>\n",
       "        <th>Duration</th>\n",
       "        <th>ProductSold</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from call limit 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "a48eb2026e79c478d0aca13a23034d29",
     "grade": false,
     "grade_id": "cell-00133bbacde63413",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    },
    "tags": [
     "5_min"
    ]
   },
   "source": [
    "### Exercise 1\n",
    "\n",
    "Calculate the average age across all customers. The result of your query should have only one column (called `average_age`) and one row (the actual average numeric age)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "c3aefcc1c2ec45f72d7b92dbc5c1e21e",
     "grade": false,
     "grade_id": "cell-026e6c3bc0f1decd",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def age_customers():\n",
    "    \"\"\"\n",
    "    Calculate the mean age of customers.\n",
    "    \n",
    "    Arguments: None\n",
    "    \n",
    "    Output:\n",
    "    query: The SQL query. A string (please refer to the example below)\n",
    "    \n",
    "    Example:\n",
    "    In this function, you only need to write the query inside quotes.\n",
    "    For instance, if you wanted to see the first 5 rows of the `call` table, this\n",
    "    should be your code:\n",
    "    \n",
    "    query = \"SELECT * FROM call LIMIT 5\"\n",
    "    \n",
    "    If you need to write your query in more than one line (ie. with line breaks),\n",
    "    you can use triple quotes: https://www.tutorialspoint.com/triple-quotes-in-python\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    query = \"SELECT AVG(age) AS average_age FROM customer\"\n",
    "\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "aa7590629ea0ac2f88dd5aa409da9db5",
     "grade": false,
     "grade_id": "cell-dc3079b3b0903d19",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    },
    "tags": [
     "10_min"
    ]
   },
   "source": [
    "### Exercise 2\n",
    "\n",
    "Calculate the average age of customers who actually made a purchase as a result of a call. The result of your query should have only one column (called `average_age_purchased`) and one row (the actual average numeric age).\n",
    "\n",
    "**Hint:** Join the `customer` and `call` tables, keep only the calls that resulted in a sale, and then compute the average age. It is true that a single customer may appear several times in the result (which would mean they were contacted more than once and made a purchase more than once). Ignore this and simply average the ages across all sales."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "c2991346a88041e971eae3c9a64412f3",
     "grade": false,
     "grade_id": "cell-ba8e8490f72820e1",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def age_customers_purchased():\n",
    "    \"\"\"\n",
    "    Calculate the mean age of customers who made purchases.\n",
    "    \n",
    "    Arguments: None\n",
    "    \n",
    "    Output:\n",
    "    query: The SQL query. A string (please refer to the example below)\n",
    "    \n",
    "    Example:\n",
    "    In this function, you only need to write the query inside quotes.\n",
    "    For instance, if you wanted to see the first 5 rows of the `call` table, this\n",
    "    should be your code:\n",
    "    \n",
    "    query = \"SELECT * FROM call LIMIT 5\"\n",
    "    \n",
    "    If you need to write your query in more than one line (ie. with line breaks),\n",
    "    you can use triple quotes: https://www.tutorialspoint.com/triple-quotes-in-python\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    query = \"\"\"SELECT AVG(age) AS average_age_purchased\n",
    "    FROM customer\n",
    "    WHERE CustomerID IN (\n",
    "      SELECT CustomerID\n",
    "      FROM call\n",
    "      WHERE result = 'sale')\"\"\"\n",
    "\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "8161fd86460b27a1d4261d1e46748b69",
     "grade": false,
     "grade_id": "cell-de85ccbf3884c95a",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    },
    "tags": [
     "10_min"
    ]
   },
   "source": [
    "### Exercise 3\n",
    "\n",
    "Determine the 6 companies which are most highly represented among the customers. Your result should have two columns (`Company` and `number_customers`) and one row.\n",
    "\n",
    "**Hint:** Count the number of customers who have links to each company (group by company), sort the result in descending order, and finally retrieve only the first 6 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "06455a674b4bc9e825d8958cea4fd584",
     "grade": false,
     "grade_id": "cell-0436ea8f08193ac3",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def most_frequent_companies():\n",
    "    \"\"\"\n",
    "    Find which 6 companies are the most frequent among customers.\n",
    "    \n",
    "    Arguments: None\n",
    "    \n",
    "    Output:\n",
    "    query: The SQL query. A string (please refer to the example below)\n",
    "    \n",
    "    Example:\n",
    "    In this function, you only need to write the query inside quotes.\n",
    "    For instance, if you wanted to see the first 5 rows of the `call` table, this\n",
    "    should be your code:\n",
    "    \n",
    "    query = \"SELECT * FROM call LIMIT 5\"\n",
    "    \n",
    "    If you need to write your query in more than one line (ie. with line breaks),\n",
    "    you can use triple quotes: https://www.tutorialspoint.com/triple-quotes-in-python\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    query =  \"\"\"SELECT company AS Company, COUNT(*) AS number_customers \n",
    "    FROM customer \n",
    "    GROUP BY company \n",
    "    ORDER BY number_customers DESC\n",
    "    LIMIT 6 \"\"\"\n",
    "\n",
    "\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "32f04daa36ac5544c7f7510c171986a1",
     "grade": false,
     "grade_id": "cell-ffea65ed46156549",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    },
    "tags": [
     "8_min"
    ]
   },
   "source": [
    "### Exercise 4\n",
    "\n",
    "Retrieve all the customers that have Gmail email accounts. Your result must have one column (`Email`) and 79 rows (the 79 customers who have Gmail accounts)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "dac557e62d292c4812c37961e54ab0bc",
     "grade": false,
     "grade_id": "cell-bfb5d155c3cd0ab3",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "def gmail_accounts():\n",
    "    \"\"\"\n",
    "    Retrieve all the customers who have Gmail accounts.\n",
    "    \n",
    "    Arguments: None\n",
    "    \n",
    "    Output:\n",
    "    query: The SQL query. A string (please refer to the example below)\n",
    "    \n",
    "    Example:\n",
    "    In this function, you only need to write the query inside quotes.\n",
    "    For instance, if you wanted to see the first 5 rows of the `call` table, this\n",
    "    should be your code:\n",
    "    \n",
    "    query = \"SELECT * FROM call LIMIT 5\"\n",
    "    \n",
    "    If you need to write your query in more than one line (ie. with line breaks),\n",
    "    you can use triple quotes: https://www.tutorialspoint.com/triple-quotes-in-python\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    # YOUR CODE HERE\n",
    "    query = \"SELECT email AS Email FROM customer WHERE email LIKE '%@gmail.com'\"\n",
    "    \n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "d962da07412df94354d3712c03089436",
     "grade": false,
     "grade_id": "cell-cd1bd37ff2b84bc2",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "## Testing Cells\n",
    "\n",
    "Run the below cells to check your answers. Make sure you run your solution cells first before running the cells below, otherwise you will get a `NameError` when checking your answers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "e44416a7cad2ffdc10537e4afbc21704",
     "grade": true,
     "grade_id": "cell-b84426fd8d40e4d9",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Exercise 1 seems correct!\n"
     ]
    }
   ],
   "source": [
    "# Ex 1\n",
    "import pandas as pd\n",
    "ava = pd.read_sql(age_customers(), con=\"sqlite:///call_center_database.db\")\n",
    "assert ava.columns == [\"average_age\"], \"Ex. 1 - Your result should only have one column, and it should be named 'average_age'!\"\n",
    "assert len(ava) == 1, \"Ex. 1 - Your result should have only one row!\"\n",
    "assert round(ava[\"average_age\"][0],3) == round(24.435, 3), \"Ex. 1 - Please check the aggregation function you used. Did you calculate the average age of all customers?\"\n",
    "print(\"Exercise 1 seems correct!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "b8e9b7ee04362164c5ce1f21f24337df",
     "grade": true,
     "grade_id": "cell-e1facf20efd6838d",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    },
    "scrolled": false
   },
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(sqlite3.OperationalError) no such column: result\n[SQL: SELECT AVG(age) AS average_age_purchased\n    FROM customer\n    WHERE CustomerID IN (\n      SELECT CustomerID\n      FROM call\n      WHERE result = 'sale')]\n(Background on this error at: http://sqlalche.me/e/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1233\u001b[0m                     self.dialect.do_execute_no_params(\n\u001b[0;32m-> 1234\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1235\u001b[0m                     )\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute_no_params\u001b[0;34m(self, cursor, statement, context)\u001b[0m\n\u001b[1;32m    554\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 555\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    556\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: no such column: result",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-68-ad4563526aad>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Ex 2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mavap\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mage_customers_purchased\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"sqlite:///call_center_database.db\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m \u001b[0;32massert\u001b[0m \u001b[0mavap\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m\"average_age_purchased\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Ex. 2 - Your result should only have one column, and it should be named 'average_age_purchased'!\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32massert\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mavap\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Ex. 2 - Your result should have only one row!\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m    513\u001b[0m             \u001b[0mcoerce_float\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    514\u001b[0m             \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 515\u001b[0;31m             \u001b[0mchunksize\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    516\u001b[0m         )\n\u001b[1;32m    517\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize)\u001b[0m\n\u001b[1;32m   1293\u001b[0m         \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1294\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1295\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1296\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1297\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1160\u001b[0m         \u001b[0;34m\"\"\"Simple passthrough to SQLAlchemy connectable\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1161\u001b[0m         return self.connectable.execution_options(no_parameters=True).execute(\n\u001b[0;32m-> 1162\u001b[0;31m             \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1163\u001b[0m         )\n\u001b[1;32m   1164\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, statement, *multiparams, **params)\u001b[0m\n\u001b[1;32m   2164\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2165\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_contextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2166\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2167\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2168\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mscalar\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[1;32m    980\u001b[0m         \"\"\"\n\u001b[1;32m    981\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstring_types\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 982\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_text\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    983\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    984\u001b[0m             \u001b[0mmeth\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mobject_\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_text\u001b[0;34m(self, statement, multiparams, params)\u001b[0m\n\u001b[1;32m   1153\u001b[0m             \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1154\u001b[0m             \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1155\u001b[0;31m             \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1156\u001b[0m         )\n\u001b[1;32m   1157\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1246\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1247\u001b[0m             self._handle_dbapi_exception(\n\u001b[0;32m-> 1248\u001b[0;31m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1249\u001b[0m             )\n\u001b[1;32m   1250\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1464\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1465\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1466\u001b[0;31m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1467\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1468\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    381\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    382\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 383\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    384\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    385\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb, cause)\u001b[0m\n\u001b[1;32m    126\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    127\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 128\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    129\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    130\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1232\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1233\u001b[0m                     self.dialect.do_execute_no_params(\n\u001b[0;32m-> 1234\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1235\u001b[0m                     )\n\u001b[1;32m   1236\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mdo_execute_no_params\u001b[0;34m(self, cursor, statement, context)\u001b[0m\n\u001b[1;32m    553\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    554\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 555\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    556\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    557\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mis_disconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mOperationalError\u001b[0m: (sqlite3.OperationalError) no such column: result\n[SQL: SELECT AVG(age) AS average_age_purchased\n    FROM customer\n    WHERE CustomerID IN (\n      SELECT CustomerID\n      FROM call\n      WHERE result = 'sale')]\n(Background on this error at: http://sqlalche.me/e/e3q8)"
     ]
    }
   ],
   "source": [
    "# Ex 2\n",
    "import pandas as pd\n",
    "avap = pd.read_sql(age_customers_purchased(), con=\"sqlite:///call_center_database.db\")\n",
    "assert avap.columns == [\"average_age_purchased\"], \"Ex. 2 - Your result should only have one column, and it should be named 'average_age_purchased'!\"\n",
    "assert len(avap) == 1, \"Ex. 2 - Your result should have only one row!\"\n",
    "assert round(avap[\"average_age_purchased\"][0],3) == round(24.708253358925145, 3), \"Ex. 2 - Please check the aggregation function you used. Did you calculate the average age of all customers who made a purchase?\"\n",
    "print(\"Exercise 2 seems correct!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "d43d9b809c717b888e8ee6dbef1d7341",
     "grade": true,
     "grade_id": "cell-1d1772c4392c70bc",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Exercise 3 seems correct!\n"
     ]
    }
   ],
   "source": [
    "# Ex 3\n",
    "import pandas as pd\n",
    "mcc = pd.read_sql(most_frequent_companies(), con=\"sqlite:///call_center_database.db\")\n",
    "assert set(mcc.columns) == set([\"Company\", \"number_customers\"]), \"Ex. 3 - Your result should have two columns, named 'Customer' and 'number_customers'!\"\n",
    "assert len(mcc) == 6, \"Ex. 3 - Your result should have 6 rows! Remember to group by company and limit your results to only 6 companies!\"\n",
    "assert set([\"Romero and Sons\",\"Mitchell and Sons\",\"Miller Group\",\"Kelly Inc\",\"Jones PLC\",\"Hernandez and Sons\"]) == set(mcc[\"Company\"].to_list()), \"Ex. 3 - Your result does not include all the companies in the top 6! Did you sort the results in descending order?\"\n",
    "print(\"Exercise 3 seems correct!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "723b385bb145b0d5aee905f3115487af",
     "grade": true,
     "grade_id": "cell-ea82fd1a9691a0fc",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Exercise 4 seems correct!\n"
     ]
    }
   ],
   "source": [
    "# Ex 4\n",
    "import pandas as pd\n",
    "gm = pd.read_sql(gmail_accounts(), con=\"sqlite:///call_center_database.db\")\n",
    "assert set(gm.columns) == set([\"Email\"]), \"Ex. 4 - Your result should have only one column, 'Email'!\"\n",
    "assert len(gm) == 79, \"Ex. 4 - Your result should have 79 rows! Remember to filter your results using WHERE and the LIKE operator!\"\n",
    "assert gm[\"Email\"].str.count(\"gmail\").sum() == 79, \"Ex. 4 - Your result contains some customers whose Email is not Gmail!\"\n",
    "print(\"Exercise 4 seems correct!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "c1_recart": "7.45.0-57c20131aabc1dc2a8c675852d80a7da",
  "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}