Bzdawka_Igor_assignment_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Bzdawka_Igor_assignment_2\n",
    "#### Igor Bzdawka\n",
    "#### 2023-01-05\n",
    "\n",
    "# Homework Assignment 2 - Report\n",
    "## Introduction\n",
    "This report is a summary of the homework assignment nr 2. This assignment was focused on learning the Python *pandas* package, as well as revising the *numpy* functions we learned during the semester. As the main task, five SQL queries, the same as in the first assignment, were given to be implemented using **_pandas_** structure, accompanied by **_numpy_** functions.\n",
    "\n",
    "The data that the queries read from come from the website <https://travel.stackexchange.com/>.\n",
    "The data frames given are:\n",
    "\n",
    "* Badges.csv.gz,\n",
    "* Comments.csv.gz,\n",
    "* Posts.csv.gz,\n",
    "* Users.csv.gz,\n",
    "* Votes.csv.gz."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "To be able to see the results of the functions, we first need to refer to the Python file containing the functions returning the results of the queries, as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from Bzdawka_Igor_assignment_2 import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Obviously, this Jupyter Notebook needs to be in the same directory as the Python file in order for it to be able to be seen.\n",
    "\n",
    "\n",
    "We also need to provide the *pandas* and *numpy* packages, so that the functions can use them to resolve the functions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we need to set up the database, which will allow the connections between the tables and calculating the queries.\n",
    "\n",
    "The output of the *pandas* function __*read_sql_query()*__ for each query will serve as the **reference solution**, as it simply requires pasting the SQL code as the argument. In order to evaluate it though, we first need to set up the database, which will figure as an environment which the function will use to work on.\n",
    "\n",
    "The code below provides a database and evaluates all the reference solutions for each query, each one named __*ref_n*__, where *n* stands for the number of the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing libraries for setting up the database\n",
    "import os, os.path\n",
    "import sqlite3\n",
    "import tempfile\n",
    "\n",
    "# Reading the packages and saving them as pandas data frames\n",
    "Badges = pd.read_csv(\"Badges.csv.gz\", compression=\"gzip\")\n",
    "Users = pd.read_csv(\"Users.csv.gz\", compression=\"gzip\")\n",
    "Votes = pd.read_csv(\"Votes.csv.gz\", compression=\"gzip\")\n",
    "Comments = pd.read_csv(\"Comments.csv.gz\", compression=\"gzip\")\n",
    "Posts = pd.read_csv(\"Posts.csv.gz\", compression=\"gzip\")\n",
    "\n",
    "# Setting up the database\n",
    "baza = os.path.join(tempfile.mkdtemp(), 'example.db')\n",
    "if os.path.isfile(baza):\n",
    "    os.remove(baza)\n",
    "# Creating connection\n",
    "conn = sqlite3.connect(baza)\n",
    "\n",
    "# Appending the packages via the connection to the database\n",
    "Badges.to_sql(\"Badges\", conn)\n",
    "Comments.to_sql(\"Comments\", conn)\n",
    "Posts.to_sql(\"Posts\", conn)\n",
    "Users.to_sql(\"Users\", conn)\n",
    "Votes.to_sql(\"Votes\", conn)\n",
    "\n",
    "# Evaluating the reference solutions\n",
    "ref_1 = pd.read_sql_query('''SELECT STRFTIME('%Y', CreationDate) AS Year, COUNT(*) AS TotalNumber\n",
    "FROM Posts\n",
    "GROUP BY Year''',conn)\n",
    "\n",
    "ref_2 = pd.read_sql_query('''SELECT Id, DisplayName, SUM(ViewCount) AS TotalViews\n",
    "FROM Users\n",
    "JOIN (\n",
    "SELECT OwnerUserId, ViewCount FROM Posts WHERE PostTypeId = 1\n",
    ") AS Questions\n",
    "ON Users.Id = Questions.OwnerUserId\n",
    "GROUP BY Id\n",
    "ORDER BY TotalViews DESC\n",
    "LIMIT 10''',conn)\n",
    "\n",
    "ref_3 = pd.read_sql_query('''SELECT Year, Name, MAX((Count * 1.0) / CountTotal) AS MaxPercentage\n",
    "FROM (\n",
    "SELECT BadgesNames.Year, BadgesNames.Name, BadgesNames.Count, BadgesYearly.CountTotal\n",
    "FROM (\n",
    "SELECT Name, COUNT(*) AS Count, STRFTIME('%Y', Badges.Date) AS Year\n",
    "FROM Badges\n",
    "GROUP BY Name, Year\n",
    ") AS BadgesNames\n",
    "JOIN (\n",
    "SELECT COUNT(*) AS CountTotal, STRFTIME('%Y', Badges.Date) AS Year\n",
    "FROM Badges\n",
    "GROUP BY YEAR\n",
    ") AS BadgesYearly\n",
    "ON BadgesNames.Year = BadgesYearly.Year\n",
    ")\n",
    "GROUP BY Year''',conn)\n",
    "\n",
    "ref_4 = pd.read_sql_query('''SELECT Title, CommentCount, ViewCount, CommentsTotalScore, DisplayName, Reputation, Location\n",
    "FROM (\n",
    "SELECT Posts.OwnerUserId, Posts.Title, Posts.CommentCount, Posts.ViewCount,\n",
    "CmtTotScr.CommentsTotalScore\n",
    "FROM (\n",
    "SELECT PostId, SUM(Score) AS CommentsTotalScore\n",
    "FROM Comments\n",
    "GROUP BY PostId\n",
    ") AS CmtTotScr\n",
    "JOIN Posts ON Posts.Id = CmtTotScr.PostId\n",
    "WHERE Posts.PostTypeId=1\n",
    ") AS PostsBestComments\n",
    "JOIN Users ON PostsBestComments.OwnerUserId = Users.Id\n",
    "ORDER BY CommentsTotalScore DESC\n",
    "LIMIT 10''',conn)\n",
    "\n",
    "ref_5 = pd.read_sql_query('''SELECT Posts.Title, STRFTIME('%Y-%m-%d', Posts.CreationDate) AS Date, VotesByAge.*\n",
    "FROM Posts\n",
    "JOIN (\n",
    "SELECT PostId,\n",
    "MAX(CASE WHEN VoteDate = 'before' THEN Total ELSE 0 END) BeforeCOVIDVotes,\n",
    "MAX(CASE WHEN VoteDate = 'during' THEN Total ELSE 0 END) DuringCOVIDVotes,\n",
    "MAX(CASE WHEN VoteDate = 'after' THEN Total ELSE 0 END) AfterCOVIDVotes,\n",
    "SUM(Total) AS Votes\n",
    "FROM (\n",
    "SELECT PostId,\n",
    "CASE STRFTIME('%Y', CreationDate)\n",
    "WHEN '2022' THEN 'after'\n",
    "WHEN '2021' THEN 'during'\n",
    "WHEN '2020' THEN 'during'\n",
    "WHEN '2019' THEN 'during'\n",
    "ELSE 'before'\n",
    "END VoteDate, COUNT(*) AS Total\n",
    "FROM Votes\n",
    "WHERE VoteTypeId IN (3, 4, 12)\n",
    "GROUP BY PostId, VoteDate\n",
    ") AS VotesDates\n",
    "GROUP BY VotesDates.PostId\n",
    ") AS VotesByAge ON Posts.Id = VotesByAge.PostId\n",
    "WHERE Title NOT IN ('') AND DuringCOVIDVotes > 0\n",
    "ORDER BY DuringCOVIDVotes DESC, Votes DESC\n",
    "LIMIT 20''',conn)\n",
    "\n",
    "# Closing the connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Naturally, the code above will work only provided the packages are in the same directory as this file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Solutions analysis\n",
    "\n",
    "To confirm that the result of the *pandas* function and the reference solution are the same, the pandas function __*.equals()*__ will be applied on both of them. If the output of this function is __*True*__, it means that the output of the function is equal to the reference solution, proving the correct implementation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1\n",
    "\n",
    "#### Short Summary\n",
    "The first query returns the total number of posts for each year. The output is grouped by year in ascending order.\n",
    "\n",
    "#### Output Correctness Comparison\n",
    "The variable **_sol_n_**, where *n* stands for the number of the query, will contain the result of computing the *n*-th query using *pandas* functions. Later, it shall be compared to the **_ref_n_** using __*.equals()*__ function, as below. The value __*True*__ as a result of the comparison will be a proof of the correct implementation of the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "sol_1 = solution_1(Posts)\n",
    "print(sol_1.equals(ref_1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see by the result of the code chunk above, the solution is equal to the reference solution, which proves the accuracy and correctness of the implementation of the query."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 2\n",
    "\n",
    "#### Short Summary\n",
    "This query represents the scoreboard, which displays the top 10 users which posts' sum of views was the greatest. The scoreboard includes the *ID* of the user, their *name* on the website and *total number of views* of all the user's posts.\n",
    "\n",
    "#### Output Correctness Comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "sol_2 = solution_2(Users, Posts)\n",
    "print(sol_2.equals(ref_2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, the result proves that the implementation was correct."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 3\n",
    "\n",
    "#### Short Summary\n",
    "The third query returns a table showing, for each year, the name of the badge that was most frequently given, along with its frequency fraction - the number of times it was given, divided by the number of all badges given in this year, expressed as **_MaxPercentage_**.\n",
    "\n",
    "#### Output Correctness Comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "sol_3 = solution_3(Badges)\n",
    "print(sol_3.equals(ref_3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, the result proves that the implementation was correct."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 4\n",
    "\n",
    "#### Short Summary\n",
    "The query no. 4 returns a table, which presents 10 posts from the website, which total score gained by all the comments under them was the biggest. The table provides the **_titles_** of the posts, the **_number of comments_** under each post, the **_total view count_** of each post, the **_total score of comments_** mentioned above, the **_name_** of the posting user, their **_reputation_** score and, if given, their **_location_**.\n",
    "\n",
    "\n",
    "#### Output Correctness Comparison\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "sol_4 = solution_4(Comments, Posts, Users)\n",
    "print(sol_4.equals(ref_4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, the result proves that the implementation was correct."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 5\n",
    "\n",
    "#### Short Summary\n",
    "The last query gives 20 rows of result. The output table is the top 20 posts, ordered by the number of votes they got during the **COVID pandemic**, which was a period of time between year **2019 and 2021**. The posts shown in the scoreboard were filtered, so that the only posts taken into consideration were those which **votes score** is a **positive number**, and those which **title** is **non-empty**. Additionally, the only votes taken into account were those which **VoteTypeId** was equal to either 3, 4 or 12. The table presented in output includes the __*title*__ of the post, the __*full (YYYY-MM-DD)*__ date of posting it, the post __*ID number*__, as well as __*votes scoreboards*__, divided in columns on votes given __*before, during and after*__ the pandemic. Additionally, a __*total votes score*__ for each post was provided as the last column.\n",
    "\n",
    "\n",
    "\n",
    "#### Output Correctness Comparison\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "sol_5 = solution_5(Posts, Votes)\n",
    "print(sol_5.equals(ref_5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see, the result proves that the implementation was correct."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusions\n",
    "\n",
    "The *pandas* package allows for a very smooth and intuitive processing data frames. The ability to chain commands one after another, similarly to piping in *dplyr*, provides the same instructions in smaller space, also making the code more readable and comprehensive. Moreover, fulfilling this project was a valuable experience gained in the field of Python programming."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}