Copy_of_HW_1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "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.6.9"
    },
    "colab": {
      "name": "Copy of HW 1.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/southjohn64/data_mine/blob/main/Copy_of_HW_1.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_fq8BHLiYCyE"
      },
      "source": [
        "# Homework Assignment 1\n",
        "### [The Art of Analyzing Big Data - The Data Scientist’s Toolbox](https://www.ise.bgu.ac.il/labs/fire/lectures.html)\n",
        "#### By Dr. Michael Fire "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jYvqtKSOYCyG"
      },
      "source": [
        "For this homework you will need to write code that analyzes real-world datasets. The code needs to be written in Python using the [sqlite3](https://docs.python.org/2/library/sqlite3.html) package. \n",
        "\n",
        "**Please note:** You need to answer only the questions that match your ID first digit."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_ehvD3Ut3cDY",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6a14aea6-4d74-4439-fec9-94519dfd49d8"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xcsVGV0L3lQ5"
      },
      "source": [
        "import json\n",
        "import os\n",
        "\n",
        "# Installing the Kaggle package\n",
        "!pip install kaggle \n",
        "\n",
        "#Important Note: complete this with your own key - after running this for the first time remmember to **remove** your API_KEY\n",
        "#api_token = {\"username\":\"<Insert Your Kaggle User Name>\",\"key\":\"<Insert Your Kaggle API key>\"}\n",
        "\n",
        "\n",
        "# creating kaggle.json file with the personal API-Key details \n",
        "# You can also put this file on your Google Drive\n",
        "#with open('/root/.kaggle/kaggle.json', 'w') as file:\n",
        "#  json.dump(api_token, file)\n",
        "!mkdir /root/.kaggle/\n",
        "!cp /content/drive/MyDrive/kaggle.json  /root/.kaggle/kaggle.json\n",
        "!chmod 600 /root/.kaggle/kaggle.json"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AML-gkV-YCyI"
      },
      "source": [
        "# 1. Babies Names Dataset (35pt)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jvkBrt7SYCyK"
      },
      "source": [
        "**Task 1 (for everyone):** Write a code that uses the  [**babies names dataset** ](https://www.kaggle.com/kaggle/us-baby-names?select=NationalNames.csv) (use NationalNames.csv) and creates a table named (Names) with the dataset data and the following columns: 'State', 'Gender', 'Name', 'Count' , and 'Year' (5pt)\n",
        "**Bonus:** Load the data using a Batch INSERT SQL Query (2pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-vrdQhaLYCyL",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5d4b35ef-0f29-45f0-e9d8-c218f063a50a"
      },
      "source": [
        "# Creating a dataset directory\n",
        "!mkdir ./datasets\n",
        "!mkdir ./datasets/us-baby-name\n",
        "\n",
        "# download the dataset from Kaggle and unzip it\n",
        "!kaggle datasets download kaggle/us-baby-names -f StateNames.csv -p ./datasets/us-baby-name/\n",
        "!chdir ./datasets/us-baby-name/\n",
        "!unzip ./datasets/us-baby-name/StateNames.csv.zip  -d ./datasets/us-baby-name"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Downloading StateNames.csv.zip to ./datasets/us-baby-name\n",
            " 79% 24.0M/30.5M [00:00<00:00, 49.3MB/s]\n",
            "100% 30.5M/30.5M [00:00<00:00, 77.1MB/s]\n",
            "/bin/bash: chdir: command not found\n",
            "Archive:  ./datasets/us-baby-name/StateNames.csv.zip\n",
            "  inflating: ./datasets/us-baby-name/StateNames.csv  \n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QEbvkQhCYCyP"
      },
      "source": [
        "**Task 2 (for everyone):** Write a query that returns the statistics for the name Mary (5pt). Use the [the timeit package](https://docs.python.org/3/library/timeit.html) to measure the time it takes the query to run (5pt). **Bonus:** [Create an index](https://www.w3schools.com/sql/sql_create_index.asp)  on the _Name_ column and use the [the timeit package](https://docs.python.org/3/library/timeit.html) to measure the time it takes the query to run with the index (5pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YBCHPAOzYCyR"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6dClZxXjYCyU"
      },
      "source": [
        "### <span style=\"color:red\"> Please answer only **one** of the following questions according to your ID number (use the formula **<YOUR_ID> mod 4 +1**) </span>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iDpqwgwQYCyV"
      },
      "source": [
        "# which question to answer - put your ID number and run the code \n",
        "your_id  = \"<fill_your_id>\"\n",
        "q = int(your_id) % 4 + 1\n",
        "print(\"You need to answer question number %s\" % q)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bGU71KQ8YCyZ"
      },
      "source": [
        "***Question 1:*** Write a function that returns how many babies were born in a given state in a given year.\n",
        "Use it to calculate the number of babies born in TX in 1986 (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0RB3GjaaYCya"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "510Wry5WYCye"
      },
      "source": [
        "***Question 2:*** Write a function that returns how many female babies were born between a given range of years.\n",
        "Use it to calculate how many babies were born between 1988 and 1995  (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "m5wOKWsBYCyf"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x721dD1NYCyi"
      },
      "source": [
        "**Question 3:** Write a function that returns the most common female name in a given state. Use it to calculate the most common female name in Wasington in 1987 (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h3t_ouDoYCyj"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mRyUoBvhYCyl"
      },
      "source": [
        "**Question 4:** Write a function that returns how many male babies named _Robert where born in a given state in a given year. Use it to find the state in which the highest number of babies _Robert_ where born in 2010 (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lz1q1mvPYCym"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "THz0vDHHYCyo"
      },
      "source": [
        "**Question (for everyone):** For the state of CA write code that calculates the second most popular female/male names in each decade (10pt). **Bonus**: Visualize it somehow using Matplotlib (5pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dVRTQGOS_3G9"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gmdQrLL9YCyp"
      },
      "source": [
        "# 2. Flavors of Cacao Dataset (15pt)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dTpNKH7UYCyq"
      },
      "source": [
        "Using the [Flavors of Cacao](https://www.kaggle.com/rombikuboktaeder/choco-flavors) dataset, answer the following questions:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NYfLEuvK4F_U"
      },
      "source": [
        "\n",
        "!kaggle datasets list -s flavors"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cgovftgF36iI",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b59050eb-b425-4acf-8af3-acb7715bc48a"
      },
      "source": [
        "# download the dataset from Kaggle and unzip it\n",
        "!kaggle datasets download abhishekpatel/flavors-of-cacaocsv  -f flavors_of_cacao.csv -p ./datasets/flavors_of_cacao/"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Downloading flavors_of_cacao.csv to ./datasets/flavors_of_cacao\n",
            "\r  0% 0.00/125k [00:00<?, ?B/s]\n",
            "\r100% 125k/125k [00:00<00:00, 48.2MB/s]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D5FWfEiaYCys"
      },
      "source": [
        "import sqlite3\n",
        "import matplotlib\n",
        "import matplotlib.pyplot as plt\n",
        "import os\n",
        "import csv\n",
        "%matplotlib inline\n",
        "DB_PATH = './datasets/flavors_of_cacao/flavors_of_cacao.csv'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GqQm3o8M5goZ"
      },
      "source": [
        "conn = sqlite3.connect('flavors_of_cacao.db')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rPHX80m05n0Z"
      },
      "source": [
        "c = conn.cursor()\n",
        "c.execute('''CREATE TABLE Cacao (Company text, Bean_Originor text, REF int, ReviewDate int, CocoaPercent real, CompanyLocation text, Rating int, BeanType text, BroadBeanOrigin text)''')\n",
        "c.close() # close the cursor"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Iqi5wXwr63ML"
      },
      "source": [
        "insert_records = \"INSERT INTO Cacao (Company, Bean_Originor, REF, ReviewDate, CocoaPercent, CompanyLocation, Rating, BeanType, BroadBeanOrigin) VALUES(?, ?,?, ?,?,?,?,?,?)\"\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CSfRbn1-7UYZ",
        "outputId": "226c742a-7cbe-4fd9-dede-e0f7e656f450"
      },
      "source": [
        "file = open(DB_PATH)\n",
        "contents = csv.reader(file)\n",
        "\n",
        "records = []\n",
        "row_num = 0\n",
        "for row in contents:\n",
        "    if row_num > 0:\n",
        "        records.append((row[0], row[1], row[2], row[3], float(row[4].strip('%')) , row[5], row[6], row[7], row[8]))\n",
        "    else:\n",
        "        print(row)\n",
        "    row_num += 1\n",
        "records[0]"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "['Company\\xa0\\n(Maker-if known)', 'Specific Bean Origin\\nor Bar Name', 'REF', 'Review\\nDate', 'Cocoa\\nPercent', 'Company\\nLocation', 'Rating', 'Bean\\nType', 'Broad Bean\\nOrigin']\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "('A. Morin',\n",
              " 'Agua Grande',\n",
              " '1876',\n",
              " '2016',\n",
              " 63.0,\n",
              " 'France',\n",
              " '3.75',\n",
              " '\\xa0',\n",
              " 'Sao Tome')"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yq9J1I3h_uRL"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XI0_Y5cd7Xo0"
      },
      "source": [
        "# load bulk\n",
        "c = conn.cursor()\n",
        "c.executemany(insert_records, records)\n",
        "#c.commit()\n",
        "c.close() # close the cursor"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wbg9gkVtYCyr"
      },
      "source": [
        "**Question 1:** Write a function that returns the number of bars manufactured where the bars' BroadBean Origin is a given country. Use the function to calculate the number of bars where BroadBean Origin is 'Fiji' (15pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lnyDKyIm6yq4",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "749773ef-b1f9-40d1-d41a-b591201d3168"
      },
      "source": [
        "def bars_by_country(broad_bean_origin):\n",
        "  c = conn.cursor()  \n",
        "  rows = c.execute(\"\"\"SELECT SUM(REF) FROM Cacao WHERE BroadBeanOrigin= \"{broad_bean_origin}\" \"\"\".format(broad_bean_origin= broad_bean_origin)).fetchone()\n",
        "  c.close()\n",
        "  return rows[0]\n",
        "bars_by_country('Fiji')\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "8898"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9GCvKxWtYCyu"
      },
      "source": [
        "**Question 2:** Write a function that returns the maximal and average cocoa percentage in a bar manufactured by a company in a specific country. Use the function to calculate the minimal and average cocoa percentage in bars manufactured by a Swiss company (15pt)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KZJXxtwWYCyv",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c00f4227-9e07-463f-cda7-261b2f952384"
      },
      "source": [
        "def cocoa_percent(country):\n",
        "  c = conn.cursor()  \n",
        "  rows = c.execute(\"\"\"SELECT Company, MIN(CocoaPercent), AVG(CocoaPercent) FROM Cacao WHERE CompanyLocation= \"{country}\" group by  Company\"\"\".format(country= country)).fetchall()\n",
        "  i = 1\n",
        "  for r in rows:\n",
        "    print(f\"{i}. {r[0]}, {str(r[1])},  {str(r[2])}\")\n",
        "    i += 1\n",
        "  c.close()\n",
        "  return rows\n",
        "cocoa_percent('Swiss')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GlYqUA1xYCyx"
      },
      "source": [
        "**Question 3:** Calculate the second most common bean type(s) and the most rare bean type(s) (15\n",
        "pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kk0qIAiwYCyx",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5f60f2e3-6497-43b8-b554-08eaf2a6bbc9"
      },
      "source": [
        "def common_bean_type():\n",
        "  c = conn.cursor()  \n",
        "  rows = c.execute(\"\"\"SELECT BeanType, SUM(REF) FROM Cacao WHERE  BeanType <> '\\xa0' group by  BeanType order by SUM(REF) DESC \"\"\").fetchall()\n",
        "  rare_index = len(rows)\n",
        "  i = 1\n",
        "  for r in rows:\n",
        "    if i ==2:\n",
        "      print(f\"second common: {r[0]}, {r[1]}\")\n",
        "    if i == rare_index:# last one is the most rare !\n",
        "      print(f\"most rare: {r[0]}, {r[1]}\")\n",
        "      \n",
        "    i += 1\n",
        "  c.close()\n",
        "  return rows\n",
        "common_bean_type();"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "second common: Criollo, 136979\n",
            "rare: Criollo (Wild), 56\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aOg-NK_lYCyz"
      },
      "source": [
        "**Question 4:** Calculate the number of reviews and the average rating in each year. Calculate the number of reviews and the average rating of each company in each year (15pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e8qwHAldYCy0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "06184149-9e3f-4347-b2d5-7d1eddcd795d"
      },
      "source": [
        "def review_avg():\n",
        "  c = conn.cursor()  \n",
        "  rows = c.execute(\"\"\"SELECT ReviewDate, Count(), AVG(Rating) FROM Cacao  group by  ReviewDate order by ReviewDate  DESC \"\"\").fetchall()\n",
        "  rare_index = len(rows)\n",
        "  i = 1\n",
        "  for r in rows:\n",
        "    print(f\"{i}. {r[0]}, {str(r[1])},  {str(r[2])}\")\n",
        "    i += 1\n",
        "  c.close()\n",
        "  #return rows\n",
        "review_avg();"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1. 2017, 24,  3.3125\n",
            "2. 2016, 219,  3.2260273972602738\n",
            "3. 2015, 285,  3.2464912280701754\n",
            "4. 2014, 247,  3.1892712550607287\n",
            "5. 2013, 184,  3.1970108695652173\n",
            "6. 2012, 195,  3.178205128205128\n",
            "7. 2011, 165,  3.256060606060606\n",
            "8. 2010, 111,  3.1486486486486487\n",
            "9. 2009, 123,  3.073170731707317\n",
            "10. 2008, 93,  2.9946236559139785\n",
            "11. 2007, 77,  3.1623376623376624\n",
            "12. 2006, 72,  3.125\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Y_WUop2bjByM",
        "outputId": "869d9324-5ac5-494d-c478-7c60718fae57"
      },
      "source": [
        "def review_avg_by_company():\n",
        "  c = conn.cursor()  \n",
        "  rows = c.execute(\"\"\"SELECT Company, ReviewDate, Count(), AVG(Rating) FROM Cacao  group by Company, ReviewDate order by Company, ReviewDate \"\"\").fetchall()\n",
        "  rare_index = len(rows)\n",
        "  i = 1\n",
        "  for r in rows:\n",
        "    print(f\"{i}. {r[0]}, {str(r[1])},  {str(r[2])}, {r[3]}\")\n",
        "    i += 1\n",
        "    if i == 10:\n",
        "      break\n",
        "  c.close()\n",
        "  #return rows\n",
        "review_avg_by_company();"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1. A. Morin, 2012,  2, 3.625\n",
            "2. A. Morin, 2013,  11, 3.3181818181818183\n",
            "3. A. Morin, 2014,  5, 3.5\n",
            "4. A. Morin, 2015,  4, 3.1875\n",
            "5. A. Morin, 2016,  1, 3.75\n",
            "6. AMMA, 2010,  4, 3.5625\n",
            "7. AMMA, 2013,  1, 3.25\n",
            "8. Acalli, 2015,  2, 3.75\n",
            "9. Adi, 2011,  4, 3.25\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eKx7_9vyYCy1"
      },
      "source": [
        "# 3. Kickstarter Projects Dataset (25pt)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5HXua9B5YCy2"
      },
      "source": [
        "Using the [Kickstarter Projects Dataset](https://www.kaggle.com/kemical/kickstarter-projects#ks-projects-201801.csv), answer the following questions:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NGgJ1dyeZiFH"
      },
      "source": [
        "**Task 1 (for everyone):** Load the dataset to SQLite DB using [PonyORM](https://ponyorm.org) (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "X2GxPCsUXNuh",
        "outputId": "c48ec4d6-dbfb-4bf0-dad1-c70312792b9b"
      },
      "source": [
        "!pip install pony"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pony\n",
            "  Downloading pony-0.7.14.tar.gz (290 kB)\n",
            "\u001b[?25l\r\u001b[K     |█▏                              | 10 kB 16.2 MB/s eta 0:00:01\r\u001b[K     |██▎                             | 20 kB 20.6 MB/s eta 0:00:01\r\u001b[K     |███▍                            | 30 kB 23.6 MB/s eta 0:00:01\r\u001b[K     |████▌                           | 40 kB 26.1 MB/s eta 0:00:01\r\u001b[K     |█████▋                          | 51 kB 9.4 MB/s eta 0:00:01\r\u001b[K     |██████▊                         | 61 kB 9.5 MB/s eta 0:00:01\r\u001b[K     |████████                        | 71 kB 8.0 MB/s eta 0:00:01\r\u001b[K     |█████████                       | 81 kB 8.8 MB/s eta 0:00:01\r\u001b[K     |██████████▏                     | 92 kB 8.5 MB/s eta 0:00:01\r\u001b[K     |███████████▎                    | 102 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |████████████▍                   | 112 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |█████████████▌                  | 122 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |██████████████▋                 | 133 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |███████████████▉                | 143 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |█████████████████               | 153 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |██████████████████              | 163 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |███████████████████▏            | 174 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |████████████████████▎           | 184 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |█████████████████████▍          | 194 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |██████████████████████▌         | 204 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |███████████████████████▊        | 215 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |████████████████████████▉       | 225 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |██████████████████████████      | 235 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |███████████████████████████     | 245 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |████████████████████████████▏   | 256 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |█████████████████████████████▎  | 266 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |██████████████████████████████▌ | 276 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |███████████████████████████████▋| 286 kB 7.6 MB/s eta 0:00:01\r\u001b[K     |████████████████████████████████| 290 kB 7.6 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pony\n",
            "  Building wheel for pony (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pony: filename=pony-0.7.14-py3-none-any.whl size=351293 sha256=f54361a283e7fcacc9024577240bbb1e0e9d027f65834f3a4d20e358f2d70121\n",
            "  Stored in directory: /root/.cache/pip/wheels/de/7a/8b/82a3b5a0135fc6bad188f62e1bf6c4843142bc22c144879014\n",
            "Successfully built pony\n",
            "Installing collected packages: pony\n",
            "Successfully installed pony-0.7.14\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "76DuBa9fWqGp",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ba0f4292-6b11-446f-8b66-10ef9a7428e3"
      },
      "source": [
        " !kaggle datasets list -s Kickstarter"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Warning: Looks like you're using an outdated API Version, please consider updating (server 1.5.12 / client 1.5.4)\n",
            "ref                                                     title                                        size  lastUpdated          downloadCount  \n",
            "------------------------------------------------------  ------------------------------------------  -----  -------------------  -------------  \n",
            "kemical/kickstarter-projects                            Kickstarter Projects                         37MB  2018-02-08 09:02:30          57159  \n",
            "socathie/kickstarter-project-statistics                 Kickstarter Project Statistics                1MB  2019-11-14 06:38:31           6187  \n",
            "codename007/funding-successful-projects                 Funding Successful Projects on Kickstarter   20MB  2017-06-20 17:37:38           2997  \n",
            "yashkantharia/kickstarter-campaigns                     Kickstarter Campaigns                        12MB  2019-03-03 13:46:08           1162  \n",
            "oscarvilla/kickstarter-nlp                              kickstarter NLP                              11MB  2018-08-09 01:38:57            449  \n",
            "toshimelonhead/400000-kickstarter-projects              400,000 Kickstarter Projects                   0B  2019-07-23 01:23:31            339  \n",
            "wood2174/mapkickstarter                                 Kickstarter                                  18MB  2018-03-07 00:01:12            257  \n",
            "parienza/kickstarter                                    kickstarter                                   4MB  2017-12-27 02:42:30            272  \n",
            "yashkantharia/kickstarter-campaigns-dataset-20          Kickstarter Campaigns Dataset 2.0            30MB  2021-01-04 11:57:44            275  \n",
            "tonyplaysguitar/steam-spy-data-from-api-request         Kickstarter videogames released on Steam      1MB  2018-01-21 23:54:08            323  \n",
            "sripaadsrinivasan/kickstarter-campaigns-dataset         Kickstarter Campaigns dataset                34MB  2021-02-08 18:27:31            113  \n",
            "tayoaki/kickstarter-dataset                             Kickstarter dataset                           2MB  2018-05-09 00:49:56            407  \n",
            "markusschmitz/kickstarter                               kickstarter                                  21MB  2019-05-23 05:34:44             60  \n",
            "iamsajanbhagat/kickstarter                              Kickstarter                                  20MB  2018-01-22 06:31:09             96  \n",
            "uysalah/archived-kickstarter-projects                   Archived Kickstarter Projects                 1MB  2019-05-10 04:33:22            231  \n",
            "antonionoca/kickstarter2018nlp                          kickstarter-2018-nlp                         35MB  2019-04-23 22:02:34            299  \n",
            "ukveteran/kickstarter                                   Kickstarter                                  20MB  2020-01-03 20:27:30             32  \n",
            "cravingsformeow/kickstarter-us-2018-cleansed            Kickstarter US 2018 Cleansed                  5MB  2020-03-24 17:36:42             29  \n",
            "bob1235/10-000-kickstarter-projects-in-cat-video-games  Kickstarter Video Games                      18MB  2021-05-21 20:17:22              5  \n",
            "officerbribe/yks-six-pack                               YKS Six Pack                                219KB  2021-08-05 21:03:58            137  \n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TLhhxMDDb2nN",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "602f847a-e6f9-46db-a00d-c8f62cde9a8a"
      },
      "source": [
        "DB_PATH = './datasets/kickstarter/ks-projects-201801.csv'\n",
        "!kaggle datasets download kemical/kickstarter-projects  -f ks-projects-201801.csv -p ./datasets/kickstarter/\n",
        "!unzip /content/datasets/kickstarter/ks-projects-201801.csv.zip -d ./datasets/kickstarter/"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Downloading ks-projects-201801.csv.zip to ./datasets/kickstarter\n",
            " 98% 20.0M/20.4M [00:00<00:00, 34.3MB/s]\n",
            "100% 20.4M/20.4M [00:00<00:00, 32.0MB/s]\n",
            "Archive:  /content/datasets/kickstarter/ks-projects-201801.csv.zip\n",
            "  inflating: ./datasets/kickstarter/ks-projects-201801.csv  \n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Id3ZYRgkXFew"
      },
      "source": [
        "#conn = sqlite3.connect('ks-projects-201612.db')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IOLa0efIXWy-"
      },
      "source": [
        "from pony.orm import *\n",
        "# Creating a new database\n",
        "db = Database()\n",
        "db.bind(provider='sqlite', filename='/content/datasets/kickstarter/kickstarter.pony.db', create_db=True) # notice we can easily use other type of databases instead of SQLite\n",
        "\n",
        "# Each class will be map to a table\n",
        "class Project(db.Entity):\n",
        "    name = Required(str)\n",
        "    category = Required(str)\n",
        "    main_category = Required(str)\n",
        "    currency = Required(str)\n",
        "    deadline = Required(str)\n",
        "    goal =  Required(float)\n",
        "    launched = Required(str)\n",
        "    pledged = Required(float)\n",
        "    state = Required(str)"
      ],
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1PuiCgqXYcOu",
        "outputId": "f4d22d44-057a-4df5-da17-89c9fb10a840"
      },
      "source": [
        "show(Project)"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "class Project(Entity):\n",
            "    id = PrimaryKey(int, auto=True)\n",
            "    name = Required(str)\n",
            "    category = Required(str)\n",
            "    main_category = Required(str)\n",
            "    currency = Required(str)\n",
            "    deadline = Required(str)\n",
            "    goal = Required(float)\n",
            "    launched = Required(str)\n",
            "    pledged = Required(float)\n",
            "    state = Required(str)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hf1-N4xWZu6f",
        "outputId": "36ad74a4-ebe6-4197-e49d-0d2038a66384"
      },
      "source": [
        "set_sql_debug(True) # helps to see what SQL commands are running\n",
        "db.generate_mapping(create_tables=True) # create tables"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "GET CONNECTION FROM THE LOCAL POOL\n",
            "PRAGMA foreign_keys = false\n",
            "BEGIN IMMEDIATE TRANSACTION\n",
            "CREATE TABLE \"Project\" (\n",
            "  \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n",
            "  \"name\" TEXT NOT NULL,\n",
            "  \"category\" TEXT NOT NULL,\n",
            "  \"main_category\" TEXT NOT NULL,\n",
            "  \"currency\" TEXT NOT NULL,\n",
            "  \"deadline\" TEXT NOT NULL,\n",
            "  \"goal\" REAL NOT NULL,\n",
            "  \"launched\" TEXT NOT NULL,\n",
            "  \"pledged\" REAL NOT NULL,\n",
            "  \"state\" TEXT NOT NULL\n",
            ")\n",
            "\n",
            "SELECT \"Project\".\"id\", \"Project\".\"name\", \"Project\".\"category\", \"Project\".\"main_category\", \"Project\".\"currency\", \"Project\".\"deadline\", \"Project\".\"goal\", \"Project\".\"launched\", \"Project\".\"pledged\", \"Project\".\"state\"\n",
            "FROM \"Project\" \"Project\"\n",
            "WHERE 0 = 1\n",
            "\n",
            "COMMIT\n",
            "PRAGMA foreign_keys = true\n",
            "CLOSE CONNECTION\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        },
        "id": "xlDaa_veZ46f",
        "outputId": "efef6c78-1b49-4558-940b-8c1ad4dfcb0b"
      },
      "source": [
        "import pandas as pd # we will talk more about pandas in our next lecture\n",
        "df = pd.read_csv('/content/datasets/kickstarter/ks-projects-201801.csv',engine ='python')\n",
        "df.head()"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>ID</th>\n",
              "      <th>name</th>\n",
              "      <th>category</th>\n",
              "      <th>main_category</th>\n",
              "      <th>currency</th>\n",
              "      <th>deadline</th>\n",
              "      <th>goal</th>\n",
              "      <th>launched</th>\n",
              "      <th>pledged</th>\n",
              "      <th>state</th>\n",
              "      <th>backers</th>\n",
              "      <th>country</th>\n",
              "      <th>usd pledged</th>\n",
              "      <th>usd_pledged_real</th>\n",
              "      <th>usd_goal_real</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1000002330</td>\n",
              "      <td>The Songs of Adelaide &amp; Abullah</td>\n",
              "      <td>Poetry</td>\n",
              "      <td>Publishing</td>\n",
              "      <td>GBP</td>\n",
              "      <td>2015-10-09</td>\n",
              "      <td>1000.0</td>\n",
              "      <td>2015-08-11 12:12:28</td>\n",
              "      <td>0.0</td>\n",
              "      <td>failed</td>\n",
              "      <td>0</td>\n",
              "      <td>GB</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1533.95</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1000003930</td>\n",
              "      <td>Greeting From Earth: ZGAC Arts Capsule For ET</td>\n",
              "      <td>Narrative Film</td>\n",
              "      <td>Film &amp; Video</td>\n",
              "      <td>USD</td>\n",
              "      <td>2017-11-01</td>\n",
              "      <td>30000.0</td>\n",
              "      <td>2017-09-02 04:43:57</td>\n",
              "      <td>2421.0</td>\n",
              "      <td>failed</td>\n",
              "      <td>15</td>\n",
              "      <td>US</td>\n",
              "      <td>100.0</td>\n",
              "      <td>2421.0</td>\n",
              "      <td>30000.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1000004038</td>\n",
              "      <td>Where is Hank?</td>\n",
              "      <td>Narrative Film</td>\n",
              "      <td>Film &amp; Video</td>\n",
              "      <td>USD</td>\n",
              "      <td>2013-02-26</td>\n",
              "      <td>45000.0</td>\n",
              "      <td>2013-01-12 00:20:50</td>\n",
              "      <td>220.0</td>\n",
              "      <td>failed</td>\n",
              "      <td>3</td>\n",
              "      <td>US</td>\n",
              "      <td>220.0</td>\n",
              "      <td>220.0</td>\n",
              "      <td>45000.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1000007540</td>\n",
              "      <td>ToshiCapital Rekordz Needs Help to Complete Album</td>\n",
              "      <td>Music</td>\n",
              "      <td>Music</td>\n",
              "      <td>USD</td>\n",
              "      <td>2012-04-16</td>\n",
              "      <td>5000.0</td>\n",
              "      <td>2012-03-17 03:24:11</td>\n",
              "      <td>1.0</td>\n",
              "      <td>failed</td>\n",
              "      <td>1</td>\n",
              "      <td>US</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>5000.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1000011046</td>\n",
              "      <td>Community Film Project: The Art of Neighborhoo...</td>\n",
              "      <td>Film &amp; Video</td>\n",
              "      <td>Film &amp; Video</td>\n",
              "      <td>USD</td>\n",
              "      <td>2015-08-29</td>\n",
              "      <td>19500.0</td>\n",
              "      <td>2015-07-04 08:35:03</td>\n",
              "      <td>1283.0</td>\n",
              "      <td>canceled</td>\n",
              "      <td>14</td>\n",
              "      <td>US</td>\n",
              "      <td>1283.0</td>\n",
              "      <td>1283.0</td>\n",
              "      <td>19500.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           ID  ... usd_goal_real\n",
              "0  1000002330  ...       1533.95\n",
              "1  1000003930  ...      30000.00\n",
              "2  1000004038  ...      45000.00\n",
              "3  1000007540  ...       5000.00\n",
              "4  1000011046  ...      19500.00\n",
              "\n",
              "[5 rows x 15 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MWqJrWmWbsrS"
      },
      "source": [
        "import random\n",
        "\n",
        "for idx, row in df.iterrows():\n",
        "    Project(name=str(row['name']),category=row['category'],main_category=row['main_category'],currency=row['currency'], deadline=row['deadline'],goal=row['goal'],\n",
        "            launched=row['launched'], pledged=row['pledged'], state=row['state'])"
      ],
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0JnETG8icPLW"
      },
      "source": [
        "commit()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W1K5VTLoYCy3"
      },
      "source": [
        "### <span style=\"color:red\"> Please answer only **one** of the following questions according to your ID number (use the formula **<YOUR_ID> mod 3 +1**) </span>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vwlOZtVHYCy3",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "541d05a7-c0e4-4c20-8a97-2b12b3ecc8cf"
      },
      "source": [
        "# which question to answer - put your ID number and run the code \n",
        "your_id  = \"036722635\"\n",
        "q = int(your_id) % 3 + 1\n",
        "print(\"You need to answer question number %s\" % q)"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "You need to answer question number 2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gkVN8OnxYCy5"
      },
      "source": [
        "**Question 1:** On average which project category received the highest number of backers? (15 pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vkhb156oYCy6"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LTk5XnNGYCy7"
      },
      "source": [
        "**Question 2:** On average which project category received the highest pledged USD? (15 pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DfiXdIneYCy8",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "0ab51c30-52df-401c-ea58-f5dc1e88a33c"
      },
      "source": [
        "res = list(select((p.category, avg(p.pledged)) for p in Project))\n",
        "print(res[0])"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "SELECT \"p\".\"category\", AVG(\"p\".\"pledged\")\n",
            "FROM \"Project\" \"p\"\n",
            "GROUP BY \"p\".\"category\"\n",
            "\n",
            "('3D Printing', 71193.3901171303)\n",
            "('Zines', 2917.61621483376)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wM5KvHE1YCy9"
      },
      "source": [
        "**Question 3:** In which month occurred the highest number of projects? (15 pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5DTIStGMYCy-"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ResaA3kxYCy_"
      },
      "source": [
        "## 4. Oscars Datasets (10pt)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WYQasQOKYCzA"
      },
      "source": [
        "Using the [Oscars Dataset](https://www.kaggle.com/theacademy/academy-awards), please answer only one of the following questions (you can chose):"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QGDmLdbKYCzA"
      },
      "source": [
        "**Question 1:** Who is the male actress with the most Oscar nominees? (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qipeHl6AYCzB"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BR33wDkbYCzD"
      },
      "source": [
        "**Question 2:** Who is the female director with the most Oscar nominees? (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2jm9HeghYCzD"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Va320K09YCzE"
      },
      "source": [
        "**Question 3:** Which top-10 movies received the highest number of Oscar nominees? (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "k9gVwnHVYCzF"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AF15wiyqYCzH"
      },
      "source": [
        "**Question 4:** Write a function that receives an actor's name and returns the actor’s number of Oscar nominees. Use the function to calculate the number of times Leonardo DiCaprio was a nominee (10pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_ZdwQJCWYCzH"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eW1N-PBiYCzJ"
      },
      "source": [
        "## 5. Select a Dataset (15pt)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Me79ek_mYCzJ"
      },
      "source": [
        "**Open Question:** Select an interesting dataset and use SQL to discover something interesting (15pt). **Bonus:** Use BigQuery (2pt)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xXIIUhgccGWw"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}