A1-2-StackExchange.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "T76TyM6XxfhT"
   },
   "source": [
    "# Assignment 1-2: Data Collection Using Web APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cTnqN1u1xfhV"
   },
   "source": [
    "## Objective"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lKpCoJnBxfhW"
   },
   "source": [
    "Many Websites (such as Twitter, Yelp, Spotify) provide free APIs to allow users to access their data. *API wrappers* simplify the use of these APIs by wrapping API calls into easy-to-use Python functions. At SFU, we are developing a unified API wrapper, called [DataPrep.Connector](https://docs.dataprep.ai/user_guide/connector/introduction.html#userguide-connector), which offers a unified programming interface to collect data from a variety of Web APIs.\n",
    "\n",
    "In this assignment, you will learn the following:\n",
    "\n",
    "* How to ask insightful questions about data\n",
    "* How to collect data from Web APIs using DataPrep.Connector\n",
    "\n",
    "**Requirements:**\n",
    "\n",
    "1. Please use [pandas.DataFrame](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html) rather than spark.DataFrame to manipulate data.\n",
    "\n",
    "2. Please follow the python code style (https://www.python.org/dev/peps/pep-0008/). If TA finds your code hard to read, you will lose points. This requirement will stay for the whole semester."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "07UAmtWKxfhW"
   },
   "source": [
    "## Preliminary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BtNUBLWzxfhW"
   },
   "source": [
    "DataPrep.Connector is very easy to learn. By watching this 10-min [PyData Global 2020 talk](https://www.youtube.com/watch?v=56qu-0Ka-dA), you should be able to know how to use it. \n",
    "\n",
    "If you want to know more, below are some other useful resources.\n",
    "\n",
    "* [Quick Introduction](https://github.com/sfu-db/dataprep#connector)\n",
    "* [User Guide](https://sfu-db.github.io/dataprep/user_guide/connector/connector.html) \n",
    "* [Examples](https://github.com/sfu-db/dataprep/tree/develop/examples)\n",
    "* [Fetch and analyze COVID-19 tweets using DataPrep](https://www.youtube.com/watch?v=vvypQB3Vp1o)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VdpWYtAXxfhW"
   },
   "source": [
    "## Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HQlCQ8yWxfhX"
   },
   "source": [
    "This is a **group** assignment. \n",
    "Please check your group in this [PDF file](https://coursys.sfu.ca/2022sp-cmpt-733-g1/pages/Web-API-Assignment/view).\n",
    "\n",
    "To do this assignment, your group needs to go through four steps:\n",
    "\n",
    "1. Select a new Web API that is not listed on https://github.com/sfu-db/APIConnectors. \n",
    "2. Create a configuration file for the API (see tutorials at [link1](https://github.com/sfu-db/APIConnectors/blob/develop/CONTRIBUTING.md#add-configuration-files) and [link2](https://github.com/sfu-db/EZHacks-tutorial/blob/master/2.%20Tutorial.ipynb)). \n",
    "3. Come up with four questions about the API. \n",
    "4. Write code to answer these questions one by one.\n",
    "\n",
    "For Step 3, please make sure your questions are **good**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "E-uYdYjVxfhX"
   },
   "source": [
    "## What are \"good questions\"?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TjdF3cntxfhY"
   },
   "source": [
    "Please use the following to judge whether your questions are good or not.\n",
    "\n",
    "1. Good questions need to be useful. That is, they are either common questions asked about the API or are exploring novel use cases.\n",
    "2. Good questions need to be diverse. That is, they cover different aspects of the API. \n",
    "3. Good questions have to cover different difficulty levels. That is, it consists of both easy and difficult questions,  where the difficulty can be measured by the number of lines of code or the number of input parameters.\n",
    "\n",
    "The following shows four good questions about the Yelp API. The corresponding code can be found at this [link](https://github.com/sfu-db/DataConnectorConfigs#yelp----collect-local-business-data)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cU1tNhqCxfhY"
   },
   "source": [
    "* Q1. What's the phone number of Capilano Suspension Bridge Park?\n",
    "* Q2. Which yoga store has the highest review count in Vancouver?\n",
    "* Q3. How many Starbucks stores are in Seattle and where are they?\n",
    "* Q4. What are the ratings for a list of restaurants?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lWupCCayxfhY"
   },
   "source": [
    "**Why are they useful?**\n",
    "* Q1 is useful because \"Capilano Suspension Bridge Park\" is one of the most popular tourist attractions in Vancouver.\n",
    "* Q2 is useful because a yoga fan wants to find out the most popular yoga store in Vancouver. \n",
    "* Q3 is useful because Starbucks was founded in Seattle.\n",
    "* Q4 is useful because people often rely on yelp ratings to decide which restaurant to go to."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tEDx19bmxfhY"
   },
   "source": [
    "**Why are they diverse?**\n",
    "\n",
    "This is because the [code](yelp-code.png) written to answer them has different inputs or outputs.\n",
    "* Q1 takes `term` and `location` as input and returns 1 record with attributes `name` and `phone` \n",
    "* Q2 takes `categories`, `location`, and `sort_by` as input and returns 1 record with attributes `name` and `review_count`\n",
    "* Q3 takes `term` and `location` as input and returns n records with attributes `name`, `address`, `city`, `state`, `country`, `zipcode`\n",
    "* Q4 takes a list of retarurant `names` as input and return n records with attributes `name`, `rating`, `city`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LgYV4y0ZxfhZ"
   },
   "source": [
    "**Why are they more and more difficult?**\n",
    "* Q1 (4 lines of code, 2 query parameters)\n",
    "* Q2 (4 lines of code, 3 query parameters)\n",
    "* Q3 (5 lines of code, 2 query parameters)\n",
    "* Q4 (11 lines of code, 2 query parameters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wHzQj4ylxfhZ"
   },
   "source": [
    "Please note that you have to use DataPrep.Connector to get data from the Web API. If DataPrep.Connector cannot meet your needs, please post your questions on Teams (Channel: Assignment 1). We will help you. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vZsuTBKJxfhZ"
   },
   "source": [
    "## Now, it's your turn. :) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cPUhTVWOxfhZ"
   },
   "source": [
    "Please write down your questions and the corresponding code for your assigned API. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "GPosfULzxfhZ"
   },
   "outputs": [],
   "source": [
    "## Provide your API key here for TAs to reproduce your results\n",
    "access_token = 'UFTrxukJoVW0mAEUyGwelA))'\n",
    "key = \"ya6VKb)ixz5rGXSM1vnCWA((\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "0H-HtB-OnQph"
   },
   "outputs": [],
   "source": [
    "# Connecting to DataPrep\n",
    "from mmap import PAGESIZE\n",
    "from dataprep.connector import connect\n",
    "from time import sleep\n",
    "import datetime\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "dc = connect('./config', _concurrency=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ujRWjo5G5RVd"
   },
   "source": [
    "### Q1\n",
    "\n",
    "What are 3 most popular topics in stackoverflow?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 144
    },
    "id": "5LsZQpLDxfha",
    "outputId": "1a6a7533-f359-4507-a49e-fef8bdf8b36b"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>javascript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>python</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>java</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name\n",
       "0  javascript\n",
       "1      python\n",
       "2        java"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags_df = await dc.query('tags', _count=3, _auth={'access_token': access_token}, site='stackoverflow', key=key, sort='popular')\n",
    "tags_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0yx4OsJzxfha"
   },
   "source": [
    "### Q2\n",
    "\n",
    "What percentage of the questions were not answered on Stackoverflow with the latest revision?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "KCu2O0JOxfha",
    "outputId": "c413f45e-7b67-43c3-ab28-2ee05403ac4c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>percentage</th>\n",
       "      <th>api_revision</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30.604313</td>\n",
       "      <td>2023.1.13.36624</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   percentage     api_revision\n",
       "0   30.604313  2023.1.13.36624"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = await dc.query('info', term = 'api_revision', _auth={'access_token': access_token}, key=key, site='stackoverflow')\n",
    "df['percentage'] = df['total_unanswered']/df['total_questions']*100\n",
    "df[['percentage','api_revision']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "w5DTewYDnsF8"
   },
   "source": [
    "### Q3\n",
    "\n",
    "Who are the top 3 users with highest reputation among all the users and what are their badge counts?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 144
    },
    "id": "q30LY3ICnqgE",
    "outputId": "4c6a9eef-ed7b-4b85-d683-cf442cc998c8"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gold</th>\n",
       "      <th>silver</th>\n",
       "      <th>bronze</th>\n",
       "      <th>account_id</th>\n",
       "      <th>location</th>\n",
       "      <th>display_name</th>\n",
       "      <th>reputation</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>total_badge_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>848</td>\n",
       "      <td>9033</td>\n",
       "      <td>9129</td>\n",
       "      <td>11683</td>\n",
       "      <td>Reading, United Kingdom</td>\n",
       "      <td>Jon Skeet</td>\n",
       "      <td>1380049</td>\n",
       "      <td>09/26/2008, 05:05:05</td>\n",
       "      <td>19010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>54</td>\n",
       "      <td>619</td>\n",
       "      <td>763</td>\n",
       "      <td>1165580</td>\n",
       "      <td>New York, United States</td>\n",
       "      <td>Gordon Linoff</td>\n",
       "      <td>1223134</td>\n",
       "      <td>01/11/2012, 11:53:57</td>\n",
       "      <td>1436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>505</td>\n",
       "      <td>4233</td>\n",
       "      <td>5042</td>\n",
       "      <td>4243</td>\n",
       "      <td>France</td>\n",
       "      <td>VonC</td>\n",
       "      <td>1182910</td>\n",
       "      <td>09/13/2008, 15:22:33</td>\n",
       "      <td>9780</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gold  silver  bronze  account_id                 location   display_name  \\\n",
       "0   848    9033    9129       11683  Reading, United Kingdom      Jon Skeet   \n",
       "1    54     619     763     1165580  New York, United States  Gordon Linoff   \n",
       "2   505    4233    5042        4243                   France           VonC   \n",
       "\n",
       "   reputation         creation_date  total_badge_count  \n",
       "0     1380049  09/26/2008, 05:05:05              19010  \n",
       "1     1223134  01/11/2012, 11:53:57               1436  \n",
       "2     1182910  09/13/2008, 15:22:33               9780  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_most_reputation = await dc.query('users', _count=3 , _auth={'access_token': access_token}, site='stackoverflow', key=key, \n",
    "                     sort='reputation', order='desc')\n",
    "df_most_reputation['creation_date'] = df_most_reputation['creation_date'].apply(lambda x: datetime.fromtimestamp(x).strftime(\"%m/%d/%Y, %H:%M:%S\"))\n",
    "\n",
    "df_most_reputation = df_most_reputation.drop(['badge_counts'], axis = 1)\n",
    "df_most_reputation['total_badge_count'] = df_most_reputation['gold'] + df_most_reputation['silver'] + df_most_reputation['bronze']\n",
    "df_most_reputation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jFpbWQgOxfha"
   },
   "source": [
    "### Q4\n",
    "\n",
    "How many users from Canada joined StackExchange in the month of December, 2022 and what is the average reputation for the new users until now?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 130
    },
    "id": "BH-tIPmhxfha",
    "outputId": "7811bf34-264c-4abb-a217-b4a72af0d66a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average Reputation -  404.5\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>account_id</th>\n",
       "      <th>location</th>\n",
       "      <th>display_name</th>\n",
       "      <th>reputation</th>\n",
       "      <th>creation_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11231367</td>\n",
       "      <td>Montr&amp;#233;al, QC, Canada</td>\n",
       "      <td>Ianatore</td>\n",
       "      <td>111</td>\n",
       "      <td>12/13/2022, 07:47:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354</th>\n",
       "      <td>27370501</td>\n",
       "      <td>Vancouver, BC. Canada</td>\n",
       "      <td>ricardkelly</td>\n",
       "      <td>698</td>\n",
       "      <td>12/27/2022, 20:44:51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     account_id                   location display_name  reputation  \\\n",
       "0      11231367  Montr&#233;al, QC, Canada     Ianatore         111   \n",
       "354    27370501      Vancouver, BC. Canada  ricardkelly         698   \n",
       "\n",
       "            creation_date  \n",
       "0    12/13/2022, 07:47:45  \n",
       "354  12/27/2022, 20:44:51  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date_epoch = datetime(2022, 12, 1, 0, 0, 0).strftime('%s')\n",
    "end_date_epoch = datetime(2022, 12, 31, 0, 0, 0).strftime('%s')\n",
    "\n",
    "df = await dc.query('users', _count=1000 , _auth={'access_token': access_token}, site='stackoverflow', key=key, fromdate = start_date_epoch, todate = end_date_epoch)\n",
    "\n",
    "df = df.drop(['badge_counts','gold','silver','bronze'], axis = 1)\n",
    "df['creation_date'] = df['creation_date'].apply(lambda x: datetime.fromtimestamp(x).strftime(\"%m/%d/%Y, %H:%M:%S\"))\n",
    "df = df.dropna()\n",
    "mask = df[df['location'].str.contains(\"Canada\")]\n",
    "print(\"Average Reputation - \",mask['reputation'].mean())\n",
    "mask"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Coj5HJ0yxfhb"
   },
   "source": [
    "### Q5\n",
    "\n",
    "Which topic was most discussed on 15th Jan 2023 and what was the question?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "_MWppu_Gxfhb",
    "outputId": "dab077f7-ee0f-4cf1-b157-70f4584d40c0"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The most discussed topics on 15th Jan 2023 are [reactjs, react-three-fiber] and the question was {Models in React Three Fiber flicker and glitch, but work fine in Codesandbox and also work fine in pure Three.js}\n"
     ]
    }
   ],
   "source": [
    "start_date_epoch = datetime(2023, 1, 15, 0, 0, 0).strftime('%s')\n",
    "end_date_epoch = datetime(2023, 1, 16, 0, 0, 0).strftime('%s')\n",
    "\n",
    "comments_df = await dc.query('comments', _count=10000, _auth={'access_token': access_token}, site='stackoverflow', key=key, sort='votes', fromdate=start_date_epoch, todate=end_date_epoch)\n",
    "\n",
    "aggregated_comments_df = comments_df.groupby(['post_id'])['post_id'].count() \\\n",
    "                             .reset_index(name='count') \\\n",
    "                             .sort_values(['count'], ascending=False)\n",
    "\n",
    "question_df = await dc.query('search', _count=1, _auth={'access_token': access_token}, q='inquestion:'+str(aggregated_comments_df.iloc[0]['post_id']) ,site='stackoverflow', key=key, order ='desc', sort='votes')\n",
    "tags = ', '.join(question_df.iloc[0][ \"tags\"])\n",
    "title = question_df.iloc[0][ \"title\"]\n",
    "print(\"The most discussed topics on 15th Jan 2023 are [\"+tags+ \"] and the question was {\"+title+\"}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TM7WHjlvxfhb"
   },
   "source": [
    "## Submission"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6P7z0SJPxfhb"
   },
   "source": [
    "Complete this notebook, rename it to `A1-2-[WEB API Name].ipynb`, and submit it along with your config files to the CourSys activity `Assignment 1 - Part 2`. For example, if your group works on Yelp, then **every member of your group** needs to submit the same notebook named `A1-2-Yelp.ipynb` and the config files named `config.zip`."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "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.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}