EDA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Board Game Data Model EDA\n",
    "\n",
    "## Introduction\n",
    "This notebook provides exploration of the data used for the Board Game Data Model and the resulting data manipulations to create the data pipeline required to create the model. The final data pipelines can be found in `create_tables.py` which are derived from this notebook.\n",
    "\n",
    "## Data Source\n",
    "The data used in this project is from the two sources noted below. The data is available at `/data/` and data dictionaries for data used in the Board Game Data Model can be found at `/data/README.md`.\n",
    "\n",
    "**[BoardGameGeek (BGG) API](https://boardgamegeek.com/wiki/page/BGG_XML_API)** \n",
    "The API provides XML output which is then compiled into csv format. Endpoints accessed include:\n",
    "- `https://boardgamegeek.com/xmlapi/boardgame/` provides information about a particular boardgame - saved to `/data/raw/bgg/games.csv`.\n",
    "- `https://boardgamegeek.com/xmlapi/geeklist/` which provides GeekList information - saved to `/data/raw/bgg/lists.csv`.\n",
    "\n",
    "**[Board Game Atlas](https://www.boardgameatlas.com/)**\n",
    "The API provides JSON output which is compiled into JSON format. Endpoints accessed include:\n",
    "- `https://www.boardgameatlas.com/api/docs/prices` provides price data for a game - saved to `/data/raw/atlas/prices/`.\n",
    "- `https://www.boardgameatlas.com/api/docs/users` provides a list of all Board Game Atlas users with information - saved to `/data/raw/atlas/json_user.json`.\n",
    "- `https://www.boardgameatlas.com/api/docs/reviews` provides game reviews as well as additional information on the user that made the review - saved to `/data/raw/atlas/reviews/` and `/data/raw/atlas/user/`, respectively.\n",
    "- `https://www.boardgameatlas.com/api/docs/search` provides board game details - saved to `/data/raw/atlas/games/`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import configparser\n",
    "from datetime import datetime\n",
    "import os\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import col, concat_ws, to_timestamp\n",
    "from pyspark.sql.functions import year, month, dayofmonth, hour, minute, weekofyear, dayofweek, monotonically_increasing_id\n",
    "from pyspark.sql.types import IntegerType\n",
    "\n",
    "config = configparser.ConfigParser()\n",
    "config.read('dl.cfg')\n",
    "\n",
    "os.environ['AWS_ACCESS_KEY_ID']=config['AWS']['AWS_ACCESS_KEY_ID']\n",
    "os.environ['AWS_SECRET_ACCESS_KEY']=config['AWS']['AWS_SECRET_ACCESS_KEY']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .config(\"spark.jars.packages\", \"org.apache.hadoop:hadoop-aws:2.7.0\") \\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "input_data = \"data/raw\"\n",
    "output_data = \"data/output\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## EDA and Data Pipelines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### BGGDetails Table\n",
    "\n",
    "The dimension table corresponding to the `https://boardgamegeek.com/xmlapi/boardgame/`which provides information about a particular boardgame - saved to `/data/raw/bgg/games.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath and read data\n",
    "bgg_games_data = os.path.join(input_data, \"bgg/games.csv\")\n",
    "bgg_games_df = spark.read.csv(bgg_games_data, header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- bgg_id: integer (nullable = true)\n",
      " |-- maxplayers: double (nullable = true)\n",
      " |-- maxplaytime: double (nullable = true)\n",
      " |-- age: double (nullable = true)\n",
      " |-- minplayers: double (nullable = true)\n",
      " |-- minplaytime: double (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- year_published: string (nullable = true)\n",
      " |-- artist: string (nullable = true)\n",
      " |-- category: string (nullable = true)\n",
      " |-- compilation: string (nullable = true)\n",
      " |-- designer: string (nullable = true)\n",
      " |-- family: string (nullable = true)\n",
      " |-- mechanic: string (nullable = true)\n",
      " |-- publisher: string (nullable = true)\n",
      " |-- users_rated: string (nullable = true)\n",
      " |-- average_rating: string (nullable = true)\n",
      " |-- datetime_extracted: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "bgg_games_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# convert column types\n",
    "bgg_games_df = bgg_games_df.withColumn(\"datetime_extracted\", \n",
    "                                       to_timestamp(col(\"datetime_extracted\"),format=\"dd/MM/yyyy HH:mm:ss\")\n",
    "                                      ).withColumn(\"year_published\", col(\"year_published\").cast(IntegerType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# rename columns to match BGGDetails Table\n",
    "bgg_games_df = bgg_games_df.withColumnRenamed(\"bgg_id\", \"bgg_game_id\"\n",
    "                                    ).withColumnRenamed(\"maxplayers\", \"max_players\"\n",
    "                                    ).withColumnRenamed(\"maxplaytime\", \"max_playtime\"\n",
    "                                    ).withColumnRenamed(\"age\", \"min_age\"\n",
    "                                    ).withColumnRenamed(\"minplayers\", \"min_players\"\n",
    "                                    ).withColumnRenamed(\"minplaytime\", \"min_playtime\"\n",
    "                                    ).withColumnRenamed(\"name\", \"game_name\"\n",
    "                                    ).withColumnRenamed(\"users_rated\", \"num_ratings\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop PK duplicates and missing values\n",
    "bgg_games_df = bgg_games_df.dropDuplicates([\"bgg_game_id\"]).dropna(subset=[\"bgg_game_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true
   },
   "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>bgg_game_id</th>\n",
       "      <th>max_players</th>\n",
       "      <th>max_playtime</th>\n",
       "      <th>min_age</th>\n",
       "      <th>min_players</th>\n",
       "      <th>min_playtime</th>\n",
       "      <th>game_name</th>\n",
       "      <th>year_published</th>\n",
       "      <th>artist</th>\n",
       "      <th>category</th>\n",
       "      <th>compilation</th>\n",
       "      <th>designer</th>\n",
       "      <th>family</th>\n",
       "      <th>mechanic</th>\n",
       "      <th>publisher</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>datetime_extracted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>148</td>\n",
       "      <td>6.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Die Glücksritter</td>\n",
       "      <td>1999</td>\n",
       "      <td>Nicholas Price</td>\n",
       "      <td>Bluffing,Medieval</td>\n",
       "      <td>None</td>\n",
       "      <td>Klaus Kreowski</td>\n",
       "      <td>None</td>\n",
       "      <td>Rock-Paper-Scissors,Simultaneous Action Selection</td>\n",
       "      <td>Schmidt Spiele</td>\n",
       "      <td>136</td>\n",
       "      <td>5.70074</td>\n",
       "      <td>2022-11-29 15:12:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>463</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Magic: The Gathering</td>\n",
       "      <td>1993</td>\n",
       "      <td>\"Victor Adame,Miao Aili,Randis Albion,Rob Alex...</td>\n",
       "      <td>Brian Durfee</td>\n",
       "      <td>Jeff Easley</td>\n",
       "      <td>Cole Eastburn</td>\n",
       "      <td>Robert Eggleton</td>\n",
       "      <td>Jesper Ejsing</td>\n",
       "      <td>Randy Elliott</td>\n",
       "      <td>Steve Ellis (I)</td>\n",
       "      <td>Larry Elmore</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>471</td>\n",
       "      <td>4.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>Totem</td>\n",
       "      <td>1995</td>\n",
       "      <td>François Bruel</td>\n",
       "      <td>Civilization</td>\n",
       "      <td>None</td>\n",
       "      <td>Philippe des Pallières,Patrice Pillet</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Queen Games</td>\n",
       "      <td>121</td>\n",
       "      <td>5.4876</td>\n",
       "      <td>2022-11-29 15:12:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>496</td>\n",
       "      <td>5.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>Dilemma</td>\n",
       "      <td>2000</td>\n",
       "      <td>Paul Dawiz</td>\n",
       "      <td>Action / Dexterity,Card Game</td>\n",
       "      <td>None</td>\n",
       "      <td>Alex Randolph</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>F.X. Schmid,Oliphante,OPEN'N PLAY</td>\n",
       "      <td>31</td>\n",
       "      <td>4.32581</td>\n",
       "      <td>2022-11-29 15:12:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>833</td>\n",
       "      <td>2.0</td>\n",
       "      <td>360.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>360.0</td>\n",
       "      <td>For the People</td>\n",
       "      <td>1998</td>\n",
       "      <td>Rodger B. MacGowan,Kurt Miller,Mark Simonitch</td>\n",
       "      <td>American Civil War,Civil War,Post-Napoleonic,W...</td>\n",
       "      <td>None</td>\n",
       "      <td>Mark Herman</td>\n",
       "      <td>Country: USA,Players: Two Player Only Games</td>\n",
       "      <td>Campaign / Battle Card Driven,Dice Rolling,Poi...</td>\n",
       "      <td>The Avalon Hill Game Co,Devir,GMT Games</td>\n",
       "      <td>1621</td>\n",
       "      <td>7.78134</td>\n",
       "      <td>2022-11-29 15:12:16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   bgg_game_id  max_players  max_playtime  min_age  min_players  min_playtime  \\\n",
       "0          148          6.0          60.0      8.0          3.0          60.0   \n",
       "1          463          2.0          20.0     13.0          2.0          20.0   \n",
       "2          471          4.0          90.0     10.0          2.0          90.0   \n",
       "3          496          5.0          30.0     10.0          3.0          30.0   \n",
       "4          833          2.0         360.0     12.0          2.0         360.0   \n",
       "\n",
       "              game_name  year_published  \\\n",
       "0      Die Glücksritter            1999   \n",
       "1  Magic: The Gathering            1993   \n",
       "2                 Totem            1995   \n",
       "3               Dilemma            2000   \n",
       "4        For the People            1998   \n",
       "\n",
       "                                              artist  \\\n",
       "0                                     Nicholas Price   \n",
       "1  \"Victor Adame,Miao Aili,Randis Albion,Rob Alex...   \n",
       "2                                     François Bruel   \n",
       "3                                         Paul Dawiz   \n",
       "4      Rodger B. MacGowan,Kurt Miller,Mark Simonitch   \n",
       "\n",
       "                                            category  compilation  \\\n",
       "0                                  Bluffing,Medieval         None   \n",
       "1                                       Brian Durfee  Jeff Easley   \n",
       "2                                       Civilization         None   \n",
       "3                       Action / Dexterity,Card Game         None   \n",
       "4  American Civil War,Civil War,Post-Napoleonic,W...         None   \n",
       "\n",
       "                                designer  \\\n",
       "0                         Klaus Kreowski   \n",
       "1                          Cole Eastburn   \n",
       "2  Philippe des Pallières,Patrice Pillet   \n",
       "3                          Alex Randolph   \n",
       "4                            Mark Herman   \n",
       "\n",
       "                                        family  \\\n",
       "0                                         None   \n",
       "1                              Robert Eggleton   \n",
       "2                                         None   \n",
       "3                                         None   \n",
       "4  Country: USA,Players: Two Player Only Games   \n",
       "\n",
       "                                            mechanic  \\\n",
       "0  Rock-Paper-Scissors,Simultaneous Action Selection   \n",
       "1                                      Jesper Ejsing   \n",
       "2                                               None   \n",
       "3                                               None   \n",
       "4  Campaign / Battle Card Driven,Dice Rolling,Poi...   \n",
       "\n",
       "                                 publisher      num_ratings average_rating  \\\n",
       "0                           Schmidt Spiele              136        5.70074   \n",
       "1                            Randy Elliott  Steve Ellis (I)   Larry Elmore   \n",
       "2                              Queen Games              121         5.4876   \n",
       "3        F.X. Schmid,Oliphante,OPEN'N PLAY               31        4.32581   \n",
       "4  The Avalon Hill Game Co,Devir,GMT Games             1621        7.78134   \n",
       "\n",
       "   datetime_extracted  \n",
       "0 2022-11-29 15:12:16  \n",
       "1                 NaT  \n",
       "2 2022-11-29 15:12:16  \n",
       "3 2022-11-29 15:12:16  \n",
       "4 2022-11-29 15:12:16  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bgg_games_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>bgg_game_id</th>\n",
       "      <th>max_players</th>\n",
       "      <th>max_playtime</th>\n",
       "      <th>min_age</th>\n",
       "      <th>min_players</th>\n",
       "      <th>min_playtime</th>\n",
       "      <th>game_name</th>\n",
       "      <th>year_published</th>\n",
       "      <th>artist</th>\n",
       "      <th>category</th>\n",
       "      <th>compilation</th>\n",
       "      <th>designer</th>\n",
       "      <th>family</th>\n",
       "      <th>mechanic</th>\n",
       "      <th>publisher</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>average_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>342570</td>\n",
       "      <td>140799</td>\n",
       "      <td>140799</td>\n",
       "      <td>140799</td>\n",
       "      <td>140799</td>\n",
       "      <td>140799</td>\n",
       "      <td>342570</td>\n",
       "      <td>140785</td>\n",
       "      <td>62525</td>\n",
       "      <td>138529</td>\n",
       "      <td>5755</td>\n",
       "      <td>117801</td>\n",
       "      <td>101677</td>\n",
       "      <td>120903</td>\n",
       "      <td>147390</td>\n",
       "      <td>342549</td>\n",
       "      <td>342562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>188762.9761566979</td>\n",
       "      <td>6.411863720623016</td>\n",
       "      <td>858.6511551928636</td>\n",
       "      <td>7.912776369150349</td>\n",
       "      <td>1.9429612426224618</td>\n",
       "      <td>42.850510301919755</td>\n",
       "      <td>Infinity</td>\n",
       "      <td>1858.8867990197818</td>\n",
       "      <td>1380.8</td>\n",
       "      <td>2021.0</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>170.675</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>42.0</td>\n",
       "      <td>72.37918968100736</td>\n",
       "      <td>3.298406689257453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>108539.46528272027</td>\n",
       "      <td>43.89174060587652</td>\n",
       "      <td>190916.60207645257</td>\n",
       "      <td>7.345503281725199</td>\n",
       "      <td>0.8390772539365494</td>\n",
       "      <td>490.61854217412287</td>\n",
       "      <td>NaN</td>\n",
       "      <td>522.6926523412053</td>\n",
       "      <td>909.6278045756642</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>179.46259359543424</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1036.2335970509614</td>\n",
       "      <td>25.438103701421433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>! That Bastard Is Trying To Steal Our Gold !</td>\n",
       "      <td>-3500</td>\n",
       "      <td>1616-1633\"</td>\n",
       "      <td>Bienchen summ herum\"\"\"</td>\n",
       "      <td>\"\"\"The Naturalist\"\" Scenario (fan expansion fo...</td>\n",
       "      <td>3000-500 BC</td>\n",
       "      <td>\"Donald \"\"DJ\"\" Carr</td>\n",
       "      <td>100BC-700AD</td>\n",
       "      <td>\"\"\"Let's Talk\"\" Company\"</td>\n",
       "      <td>500-100BC</td>\n",
       "      <td>(Public Domain),A. N. Myers &amp; Co,ABRA,Adolf Sa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>375608</td>\n",
       "      <td>11299.0</td>\n",
       "      <td>6.3072E7</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>157680.0</td>\n",
       "      <td>～幻想郷TRPG～</td>\n",
       "      <td>2026</td>\n",
       "      <td>齋藤　海有 (Wataru Saitou)</td>\n",
       "      <td>Łukasz Witusiński\"</td>\n",
       "      <td>파워그리드 재충전 확장: 지도 모음집 (Power Grid Recharged – M...</td>\n",
       "      <td>ｱｷﾋﾛｲﾄｵ (Akihiro Itoh),Kwaji,Daichi Okano,Kito...</td>\n",
       "      <td>Word Games: Spelling / Letters,Players: Two Pl...</td>\n",
       "      <td>Zone of Control</td>\n",
       "      <td>＠BUDAcafe (アットブダカフェ)</td>\n",
       "      <td>von Braus publishing</td>\n",
       "      <td>Winning Eleven Productions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary         bgg_game_id        max_players        max_playtime  \\\n",
       "0   count              342570             140799              140799   \n",
       "1    mean   188762.9761566979  6.411863720623016   858.6511551928636   \n",
       "2  stddev  108539.46528272027  43.89174060587652  190916.60207645257   \n",
       "3     min                   1                0.0                 0.0   \n",
       "4     max              375608            11299.0            6.3072E7   \n",
       "\n",
       "             min_age         min_players        min_playtime  \\\n",
       "0             140799              140799              140799   \n",
       "1  7.912776369150349  1.9429612426224618  42.850510301919755   \n",
       "2  7.345503281725199  0.8390772539365494  490.61854217412287   \n",
       "3                0.0                 0.0                 0.0   \n",
       "4             2017.0                50.0            157680.0   \n",
       "\n",
       "                                      game_name      year_published  \\\n",
       "0                                        342570              140785   \n",
       "1                                      Infinity  1858.8867990197818   \n",
       "2                                           NaN   522.6926523412053   \n",
       "3  ! That Bastard Is Trying To Steal Our Gold !               -3500   \n",
       "4                                     ～幻想郷TRPG～                2026   \n",
       "\n",
       "                  artist                 category  \\\n",
       "0                  62525                   138529   \n",
       "1                 1380.8                   2021.0   \n",
       "2      909.6278045756642                      NaN   \n",
       "3             1616-1633\"   Bienchen summ herum\"\"\"   \n",
       "4  齋藤　海有 (Wataru Saitou)       Łukasz Witusiński\"   \n",
       "\n",
       "                                         compilation  \\\n",
       "0                                               5755   \n",
       "1                                             1951.0   \n",
       "2                                                NaN   \n",
       "3  \"\"\"The Naturalist\"\" Scenario (fan expansion fo...   \n",
       "4  파워그리드 재충전 확장: 지도 모음집 (Power Grid Recharged – M...   \n",
       "\n",
       "                                            designer  \\\n",
       "0                                             117801   \n",
       "1                                            170.675   \n",
       "2                                 179.46259359543424   \n",
       "3                                        3000-500 BC   \n",
       "4  ｱｷﾋﾛｲﾄｵ (Akihiro Itoh),Kwaji,Daichi Okano,Kito...   \n",
       "\n",
       "                                              family         mechanic  \\\n",
       "0                                             101677           120903   \n",
       "1                                               None             None   \n",
       "2                                               None             None   \n",
       "3                                \"Donald \"\"DJ\"\" Carr      100BC-700AD   \n",
       "4  Word Games: Spelling / Letters,Players: Two Pl...  Zone of Control   \n",
       "\n",
       "                  publisher           num_ratings  \\\n",
       "0                    147390                342549   \n",
       "1                      42.0     72.37918968100736   \n",
       "2                       NaN    1036.2335970509614   \n",
       "3  \"\"\"Let's Talk\"\" Company\"             500-100BC   \n",
       "4      ＠BUDAcafe (アットブダカフェ)  von Braus publishing   \n",
       "\n",
       "                                      average_rating  \n",
       "0                                             342562  \n",
       "1                                  3.298406689257453  \n",
       "2                                 25.438103701421433  \n",
       "3  (Public Domain),A. N. Myers & Co,ABRA,Adolf Sa...  \n",
       "4                         Winning Eleven Productions  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bgg_games_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Summary of Data\n",
    "\n",
    "The details table above indicates that:\n",
    "- There are 342k rows of data\n",
    "- All rows have the bgg_id and game_name (and num_ratings and average_ratings are almost complete)\n",
    "- Approximately 50% have details such as max/min playtime, max/min players, age, and published year\n",
    "- All other columns with game descriptions (such as mechanic, artist, etc) have varying amounts of completeness\n",
    "- Looking at the min/max values for various columns, it's clear that there are some erroneous data entered (negative value as the min for published year) - it's expected that some form of data cleaning would be required after pulling from the data model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "bgg_games_df.write.parquet(os.path.join(output_data, \"bgg_games.parquet\"), partitionBy=[\"year_published\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### AtlasUsers Table\n",
    "\n",
    "The dimension table corresponding to user data from the following sources:\n",
    "- `https://www.boardgameatlas.com/api/docs/users` provides a list of all Board Game Atlas users with information - saved to `/data/raw/atlas/json_user.json`.\n",
    "- `https://www.boardgameatlas.com/api/docs/reviews` provides game reviews as well as additional information on the user that made the review - saved to `/data/raw/atlas/reviews/` and `/data/raw/atlas/user/`, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath and read data for the first data source\n",
    "atlas_users1_data = os.path.join(input_data, \"atlas/json_user.json\")\n",
    "atlas_users1_df = spark.read.json(atlas_users1_data, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- created_at: string (nullable = true)\n",
      " |-- description: string (nullable = true)\n",
      " |-- experience: long (nullable = true)\n",
      " |-- gold: long (nullable = true)\n",
      " |-- images: struct (nullable = true)\n",
      " |    |-- large: string (nullable = true)\n",
      " |    |-- medium: string (nullable = true)\n",
      " |    |-- original: string (nullable = true)\n",
      " |    |-- small: string (nullable = true)\n",
      " |    |-- thumb: string (nullable = true)\n",
      " |-- is_new: boolean (nullable = true)\n",
      " |-- is_supporter: boolean (nullable = true)\n",
      " |-- level: long (nullable = true)\n",
      " |-- level_curr_exp: long (nullable = true)\n",
      " |-- level_next_exp: long (nullable = true)\n",
      " |-- level_percent: double (nullable = true)\n",
      " |-- ringCss: string (nullable = true)\n",
      " |-- url: string (nullable = true)\n",
      " |-- username: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "atlas_users1_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# select columns of interest and rename columns to match Table\n",
    "atlas_users1_df = atlas_users1_df.select(col(\"username\").alias(\"user_name\"),\n",
    "                                         \"url\", \"description\",\n",
    "                                         col(\"gold\").alias(\"atlas_gold\"),\n",
    "                                         col(\"experience\").alias(\"atlas_exp\"),\n",
    "                                         col(\"level\").alias(\"atlas_level\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop duplicates and missing values corresponding with the table PK\n",
    "atlas_users1_df = atlas_users1_df.dropDuplicates([\"user_name\"]).dropna(subset=[\"user_name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "editable": true
   },
   "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>user_name</th>\n",
       "      <th>url</th>\n",
       "      <th>description</th>\n",
       "      <th>atlas_gold</th>\n",
       "      <th>atlas_exp</th>\n",
       "      <th>atlas_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Azmaer</td>\n",
       "      <td>https://www.boardgameatlas.com/u/Azmaer</td>\n",
       "      <td></td>\n",
       "      <td>1423</td>\n",
       "      <td>373</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BlueGiant42</td>\n",
       "      <td>https://www.boardgameatlas.com/u/BlueGiant42</td>\n",
       "      <td></td>\n",
       "      <td>191</td>\n",
       "      <td>191</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brolands</td>\n",
       "      <td>https://www.boardgameatlas.com/u/Brolands</td>\n",
       "      <td></td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carptarts</td>\n",
       "      <td>https://www.boardgameatlas.com/u/Carptarts</td>\n",
       "      <td></td>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Eleanor10</td>\n",
       "      <td>https://www.boardgameatlas.com/u/Eleanor10</td>\n",
       "      <td>Hi, I am Eleanor Mila. I have been working as ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_name                                           url  \\\n",
       "0       Azmaer       https://www.boardgameatlas.com/u/Azmaer   \n",
       "1  BlueGiant42  https://www.boardgameatlas.com/u/BlueGiant42   \n",
       "2     Brolands     https://www.boardgameatlas.com/u/Brolands   \n",
       "3    Carptarts    https://www.boardgameatlas.com/u/Carptarts   \n",
       "4    Eleanor10    https://www.boardgameatlas.com/u/Eleanor10   \n",
       "\n",
       "                                         description  atlas_gold  atlas_exp  \\\n",
       "0                                                           1423        373   \n",
       "1                                                            191        191   \n",
       "2                                                             37         37   \n",
       "3                                                             68         68   \n",
       "4  Hi, I am Eleanor Mila. I have been working as ...           0          0   \n",
       "\n",
       "   atlas_level  \n",
       "0           32  \n",
       "1           25  \n",
       "2            8  \n",
       "3           14  \n",
       "4            0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_users1_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>user_name</th>\n",
       "      <th>url</th>\n",
       "      <th>description</th>\n",
       "      <th>atlas_gold</th>\n",
       "      <th>atlas_exp</th>\n",
       "      <th>atlas_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>7734.0</td>\n",
       "      <td>None</td>\n",
       "      <td>11111.0</td>\n",
       "      <td>145.96109164669002</td>\n",
       "      <td>142.65941360870366</td>\n",
       "      <td>7.328047206343353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1605.87560991176</td>\n",
       "      <td>1647.67635507376</td>\n",
       "      <td>11.485363514849013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>-Rinser-</td>\n",
       "      <td>https://www.boardgameatlas.com/u/-Rinser-</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>انا</td>\n",
       "      <td>https://www.boardgameatlas.com/u/انا</td>\n",
       "      <td>—&gt;  This is the reduced form of the correspond...</td>\n",
       "      <td>75689</td>\n",
       "      <td>75559</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary user_name                                        url  \\\n",
       "0   count      5423                                       5423   \n",
       "1    mean    7734.0                                       None   \n",
       "2  stddev       NaN                                       None   \n",
       "3     min  -Rinser-  https://www.boardgameatlas.com/u/-Rinser-   \n",
       "4     max       انا       https://www.boardgameatlas.com/u/انا   \n",
       "\n",
       "                                         description          atlas_gold  \\\n",
       "0                                               5423                5423   \n",
       "1                                            11111.0  145.96109164669002   \n",
       "2                                                NaN    1605.87560991176   \n",
       "3                                                                      0   \n",
       "4  —>  This is the reduced form of the correspond...               75689   \n",
       "\n",
       "            atlas_exp         atlas_level  \n",
       "0                5423                5423  \n",
       "1  142.65941360870366   7.328047206343353  \n",
       "2    1647.67635507376  11.485363514849013  \n",
       "3                   0                   0  \n",
       "4               75559                  88  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_users1_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "The data shows there are 5423 users and all columns are complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath and read data for the second data source\n",
    "atlas_users2_data = os.path.join(input_data, \"atlas/users/*.json\")\n",
    "atlas_users2_df = spark.read.json(atlas_users2_data, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- created_at: string (nullable = true)\n",
      " |-- created_at_ago: string (nullable = true)\n",
      " |-- forum_email: boolean (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- imageUrl: string (nullable = true)\n",
      " |-- images: struct (nullable = true)\n",
      " |    |-- large: string (nullable = true)\n",
      " |    |-- medium: string (nullable = true)\n",
      " |    |-- original: string (nullable = true)\n",
      " |    |-- small: string (nullable = true)\n",
      " |    |-- thumb: string (nullable = true)\n",
      " |-- isOwner: boolean (nullable = true)\n",
      " |-- isVerified: boolean (nullable = true)\n",
      " |-- is_moderator: boolean (nullable = true)\n",
      " |-- is_new: boolean (nullable = true)\n",
      " |-- is_owner: boolean (nullable = true)\n",
      " |-- is_partner: boolean (nullable = true)\n",
      " |-- is_premium: boolean (nullable = true)\n",
      " |-- message_email: boolean (nullable = true)\n",
      " |-- num_followers: long (nullable = true)\n",
      " |-- pfUser: struct (nullable = true)\n",
      " |    |-- objectId: string (nullable = true)\n",
      " |-- price_email: boolean (nullable = true)\n",
      " |-- updated_at: string (nullable = true)\n",
      " |-- updated_at_ago: string (nullable = true)\n",
      " |-- url: string (nullable = true)\n",
      " |-- username: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "atlas_users2_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# only select columns of interest\n",
    "atlas_users2_df = atlas_users2_df.select(col(\"id\").alias(\"atlas_user_id\"),\n",
    "                                         col(\"is_premium\").alias(\"atlas_premium\"),\n",
    "                                         col(\"is_partner\").alias(\"atlas_partner\"),\n",
    "                                         col(\"is_moderator\").alias(\"atlas_moderator\"),\n",
    "                                         col(\"num_followers\").alias(\"atlas_followers\"),\n",
    "                                         col(\"username\").alias(\"user_name\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop duplicates and missing values for Table PK\n",
    "atlas_users2_df = atlas_users2_df.dropDuplicates([\"atlas_user_id\"]).dropna(subset=[\"atlas_user_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "editable": true
   },
   "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>atlas_user_id</th>\n",
       "      <th>atlas_premium</th>\n",
       "      <th>atlas_partner</th>\n",
       "      <th>atlas_moderator</th>\n",
       "      <th>atlas_followers</th>\n",
       "      <th>user_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3TWDgLXYfn</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>Rynn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3aQKPZmQVr</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>beardgoggles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9FzchNX38c</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>LKTrashmouth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BXzlhlqwCb</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>guzforster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JdGjsmHST0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>Xist</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  atlas_user_id  atlas_premium  atlas_partner  atlas_moderator  \\\n",
       "0    3TWDgLXYfn          False          False            False   \n",
       "1    3aQKPZmQVr          False          False            False   \n",
       "2    9FzchNX38c          False          False            False   \n",
       "3    BXzlhlqwCb          False          False            False   \n",
       "4    JdGjsmHST0          False          False            False   \n",
       "\n",
       "   atlas_followers     user_name  \n",
       "0                0          Rynn  \n",
       "1                0  beardgoggles  \n",
       "2                0  LKTrashmouth  \n",
       "3                0    guzforster  \n",
       "4                0          Xist  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_users2_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>atlas_user_id</th>\n",
       "      <th>atlas_followers</th>\n",
       "      <th>user_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>1880</td>\n",
       "      <td>1880</td>\n",
       "      <td>1880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7734.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>03pnbGwpn7</td>\n",
       "      <td>0</td>\n",
       "      <td>07734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>zwJQyVMADi</td>\n",
       "      <td>0</td>\n",
       "      <td>zxcvbn</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary atlas_user_id atlas_followers user_name\n",
       "0   count          1880            1880      1880\n",
       "1    mean          None             0.0    7734.0\n",
       "2  stddev          None             0.0       NaN\n",
       "3     min    03pnbGwpn7               0     07734\n",
       "4     max    zwJQyVMADi               0    zxcvbn"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_users2_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "The table indicates that only 1880 users are in this table - this would correspond to users that made reviews as that is how this information was extracted. It also indicates that while `atlas_followers` was provided as a field in the data, it clearly isn't populated - as such it should be dropped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop atlas followers\n",
    "atlas_users2_df = atlas_users2_df.drop(\"atlas_followers\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# The two tables need to be joined based on the user ID\n",
    "atlas_users_final_df = atlas_users1_df.join(atlas_users2_df, \"user_name\", how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>user_name</th>\n",
       "      <th>url</th>\n",
       "      <th>description</th>\n",
       "      <th>atlas_gold</th>\n",
       "      <th>atlas_exp</th>\n",
       "      <th>atlas_level</th>\n",
       "      <th>atlas_user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>5423</td>\n",
       "      <td>1880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>7734.0</td>\n",
       "      <td>None</td>\n",
       "      <td>11111.0</td>\n",
       "      <td>145.96109164669002</td>\n",
       "      <td>142.65941360870366</td>\n",
       "      <td>7.328047206343353</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1605.87560991176</td>\n",
       "      <td>1647.67635507376</td>\n",
       "      <td>11.485363514849013</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>-Rinser-</td>\n",
       "      <td>https://www.boardgameatlas.com/u/-Rinser-</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>03pnbGwpn7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>انا</td>\n",
       "      <td>https://www.boardgameatlas.com/u/انا</td>\n",
       "      <td>—&gt;  This is the reduced form of the correspond...</td>\n",
       "      <td>75689</td>\n",
       "      <td>75559</td>\n",
       "      <td>88</td>\n",
       "      <td>zwJQyVMADi</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary user_name                                        url  \\\n",
       "0   count      5423                                       5423   \n",
       "1    mean    7734.0                                       None   \n",
       "2  stddev       NaN                                       None   \n",
       "3     min  -Rinser-  https://www.boardgameatlas.com/u/-Rinser-   \n",
       "4     max       انا       https://www.boardgameatlas.com/u/انا   \n",
       "\n",
       "                                         description          atlas_gold  \\\n",
       "0                                               5423                5423   \n",
       "1                                            11111.0  145.96109164669002   \n",
       "2                                                NaN    1605.87560991176   \n",
       "3                                                                      0   \n",
       "4  —>  This is the reduced form of the correspond...               75689   \n",
       "\n",
       "            atlas_exp         atlas_level atlas_user_id  \n",
       "0                5423                5423          1880  \n",
       "1  142.65941360870366   7.328047206343353          None  \n",
       "2    1647.67635507376  11.485363514849013          None  \n",
       "3                   0                   0    03pnbGwpn7  \n",
       "4               75559                  88    zwJQyVMADi  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_users_final_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "As <50% of the atlas_user_ids are available (only atlas_user_ids are available in the data for users that made reviews. Create a UID 'user_id' for the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_users_final_df = atlas_users_final_df.withColumn(\"user_id\", monotonically_increasing_id())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Summary of Data\n",
    "\n",
    "The details table above indicates that:\n",
    "- There are 5423 users with complete information corresponding to the first df\n",
    "- 1880 users made reviews (second df) and there is additional information available for those users that have been added to the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_users_final_df.write.parquet(os.path.join(output_data, \"users.parquet\"), partitionBy=[\"atlas_level\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### AtlasDetails Table\n",
    "\n",
    "The dimension table corresponding to `https://www.boardgameatlas.com/api/docs/search` which provides board game details - saved to `/data/raw/atlas/games/`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath to read data\n",
    "atlas_games_data = os.path.join(input_data, \"atlas/games/*.json\")\n",
    "atlas_games_df = spark.read.json(atlas_games_data, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- active: boolean (nullable = true)\n",
      " |-- amazon_rank: long (nullable = true)\n",
      " |-- artists: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- availability_status: string (nullable = true)\n",
      " |-- average_learning_complexity: double (nullable = true)\n",
      " |-- average_strategy_complexity: double (nullable = true)\n",
      " |-- average_user_rating: double (nullable = true)\n",
      " |-- categories: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |-- comment_count: long (nullable = true)\n",
      " |-- commentary: string (nullable = true)\n",
      " |-- cs_rating: double (nullable = true)\n",
      " |-- datetime_extracted: string (nullable = true)\n",
      " |-- description: string (nullable = true)\n",
      " |-- description_preview: string (nullable = true)\n",
      " |-- designers: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |    |    |-- images: struct (nullable = true)\n",
      " |    |    |    |-- large: string (nullable = true)\n",
      " |    |    |    |-- medium: string (nullable = true)\n",
      " |    |    |    |-- original: string (nullable = true)\n",
      " |    |    |    |-- small: string (nullable = true)\n",
      " |    |    |    |-- thumb: string (nullable = true)\n",
      " |    |    |-- num_games: string (nullable = true)\n",
      " |    |    |-- score: long (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |-- developers: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- discount: string (nullable = true)\n",
      " |-- duplicate_of: struct (nullable = true)\n",
      " |    |-- objectId: string (nullable = true)\n",
      " |-- edit_url: string (nullable = true)\n",
      " |-- faq: string (nullable = true)\n",
      " |-- handle: string (nullable = true)\n",
      " |-- historical_low_prices: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- country: string (nullable = true)\n",
      " |    |    |-- date: string (nullable = true)\n",
      " |    |    |-- isLow: boolean (nullable = true)\n",
      " |    |    |-- price: double (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- image_url: string (nullable = true)\n",
      " |-- images: struct (nullable = true)\n",
      " |    |-- large: string (nullable = true)\n",
      " |    |-- medium: string (nullable = true)\n",
      " |    |-- original: string (nullable = true)\n",
      " |    |-- small: string (nullable = true)\n",
      " |    |-- thumb: string (nullable = true)\n",
      " |-- is_historical_low: boolean (nullable = true)\n",
      " |-- isbn: string (nullable = true)\n",
      " |-- links: long (nullable = true)\n",
      " |-- listing_clicks: long (nullable = true)\n",
      " |-- lists: long (nullable = true)\n",
      " |-- matches_specs: struct (nullable = true)\n",
      " |    |-- id: string (nullable = true)\n",
      " |-- max_players: long (nullable = true)\n",
      " |-- max_playtime: long (nullable = true)\n",
      " |-- mechanics: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |-- mentions: long (nullable = true)\n",
      " |-- min_age: long (nullable = true)\n",
      " |-- min_players: long (nullable = true)\n",
      " |-- min_playtime: long (nullable = true)\n",
      " |-- msrp: string (nullable = true)\n",
      " |-- msrp_text: string (nullable = true)\n",
      " |-- msrps: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- country: string (nullable = true)\n",
      " |    |    |-- price: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- names: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- num_distributors: long (nullable = true)\n",
      " |-- num_user_complexity_votes: long (nullable = true)\n",
      " |-- num_user_ratings: long (nullable = true)\n",
      " |-- official_url: string (nullable = true)\n",
      " |-- player_counts: array (nullable = true)\n",
      " |    |-- element: long (containsNull = true)\n",
      " |-- players: string (nullable = true)\n",
      " |-- plays: long (nullable = true)\n",
      " |-- playtime: string (nullable = true)\n",
      " |-- price: string (nullable = true)\n",
      " |-- price_au: string (nullable = true)\n",
      " |-- price_ca: string (nullable = true)\n",
      " |-- price_text: string (nullable = true)\n",
      " |-- price_uk: string (nullable = true)\n",
      " |-- primary_designer: struct (nullable = true)\n",
      " |    |-- id: string (nullable = true)\n",
      " |    |-- name: string (nullable = true)\n",
      " |    |-- url: string (nullable = true)\n",
      " |-- primary_publisher: struct (nullable = true)\n",
      " |    |-- id: string (nullable = true)\n",
      " |    |-- name: string (nullable = true)\n",
      " |    |-- url: string (nullable = true)\n",
      " |-- publishers: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |    |    |-- images: struct (nullable = true)\n",
      " |    |    |    |-- large: string (nullable = true)\n",
      " |    |    |    |-- medium: string (nullable = true)\n",
      " |    |    |    |-- original: string (nullable = true)\n",
      " |    |    |    |-- small: string (nullable = true)\n",
      " |    |    |    |-- thumb: string (nullable = true)\n",
      " |    |    |-- num_games: string (nullable = true)\n",
      " |    |    |-- score: long (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |-- rank: long (nullable = true)\n",
      " |-- related_as: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- related_to: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- artists: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- average_learning_complexity: long (nullable = true)\n",
      " |    |    |-- average_strategy_complexity: long (nullable = true)\n",
      " |    |    |-- average_user_rating: long (nullable = true)\n",
      " |    |    |-- bgg_id: string (nullable = true)\n",
      " |    |    |-- categories: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- comment_count: long (nullable = true)\n",
      " |    |    |-- commentary: string (nullable = true)\n",
      " |    |    |-- description: string (nullable = true)\n",
      " |    |    |-- description_preview: string (nullable = true)\n",
      " |    |    |-- designers: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- developers: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- discount: string (nullable = true)\n",
      " |    |    |-- edit_url: string (nullable = true)\n",
      " |    |    |-- faq: string (nullable = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |    |    |-- image_url: string (nullable = true)\n",
      " |    |    |-- images: struct (nullable = true)\n",
      " |    |    |    |-- large: string (nullable = true)\n",
      " |    |    |    |-- medium: string (nullable = true)\n",
      " |    |    |    |-- original: string (nullable = true)\n",
      " |    |    |    |-- small: string (nullable = true)\n",
      " |    |    |    |-- thumb: string (nullable = true)\n",
      " |    |    |-- is_historical_low: boolean (nullable = true)\n",
      " |    |    |-- links: long (nullable = true)\n",
      " |    |    |-- listing_clicks: long (nullable = true)\n",
      " |    |    |-- lists: long (nullable = true)\n",
      " |    |    |-- matches_specs: string (nullable = true)\n",
      " |    |    |-- max_players: string (nullable = true)\n",
      " |    |    |-- max_playtime: string (nullable = true)\n",
      " |    |    |-- mechanics: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- mentions: long (nullable = true)\n",
      " |    |    |-- min_age: string (nullable = true)\n",
      " |    |    |-- min_players: string (nullable = true)\n",
      " |    |    |-- min_playtime: string (nullable = true)\n",
      " |    |    |-- msrp: string (nullable = true)\n",
      " |    |    |-- names: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- num_distributors: long (nullable = true)\n",
      " |    |    |-- num_user_complexity_votes: long (nullable = true)\n",
      " |    |    |-- num_user_ratings: long (nullable = true)\n",
      " |    |    |-- pfGame: struct (nullable = true)\n",
      " |    |    |    |-- objectId: string (nullable = true)\n",
      " |    |    |-- plays: long (nullable = true)\n",
      " |    |    |-- price: string (nullable = true)\n",
      " |    |    |-- price_au: string (nullable = true)\n",
      " |    |    |-- price_ca: string (nullable = true)\n",
      " |    |    |-- price_text: string (nullable = true)\n",
      " |    |    |-- price_uk: string (nullable = true)\n",
      " |    |    |-- publishers: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- rank: long (nullable = true)\n",
      " |    |    |-- related_as: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- related_to: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- specs: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- tags: array (nullable = true)\n",
      " |    |    |    |-- element: string (containsNull = true)\n",
      " |    |    |-- thumb_url: string (nullable = true)\n",
      " |    |    |-- trending_rank: long (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |    |    |-- visits: long (nullable = true)\n",
      " |    |    |-- year_published: string (nullable = true)\n",
      " |-- rules_url: string (nullable = true)\n",
      " |-- sell_sheet_url: string (nullable = true)\n",
      " |-- size_depth: double (nullable = true)\n",
      " |-- size_height: double (nullable = true)\n",
      " |-- size_units: string (nullable = true)\n",
      " |-- size_width: double (nullable = true)\n",
      " |-- sku: string (nullable = true)\n",
      " |-- sku_objects: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- name: string (nullable = true)\n",
      " |    |    |-- sku: string (nullable = true)\n",
      " |-- skus: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- specs: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- id: string (nullable = true)\n",
      " |-- store_images_url: string (nullable = true)\n",
      " |-- tags: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- thumb_url: string (nullable = true)\n",
      " |-- trending_rank: long (nullable = true)\n",
      " |-- type: string (nullable = true)\n",
      " |-- upc: string (nullable = true)\n",
      " |-- url: string (nullable = true)\n",
      " |-- video_links: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- title: string (nullable = true)\n",
      " |    |    |-- url: string (nullable = true)\n",
      " |-- visits: long (nullable = true)\n",
      " |-- weight_amount: double (nullable = true)\n",
      " |-- weight_units: string (nullable = true)\n",
      " |-- year_published: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "atlas_games_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# the data available for atlas games is extensive with nested json\n",
    "# only extract the columns of interest for the data model table and rename columns to match\n",
    "atlas_games_df = atlas_games_df.select(col(\"id\").alias(\"atlas_game_id\"),\n",
    "                                       col(\"name\").alias(\"game_name\"),\n",
    "                                       \"url\", \"datetime_extracted\", \"year_published\",\n",
    "                                       \"min_players\", \"max_players\", \"min_playtime\", \"max_playtime\",\n",
    "                                       \"min_age\",\n",
    "                                       col(\"primary_publisher.name\").alias(\"primary_publisher\"),\n",
    "                                       col(\"primary_designer.name\").alias(\"primary_designer\"), \n",
    "                                       \"artists\",\n",
    "                                       col(\"num_user_ratings\").alias(\"num_ratings\"),\n",
    "                                       col(\"average_user_rating\").alias(\"average_rating\"),\n",
    "                                       col(\"num_user_complexity_votes\").alias(\"num_complexity\"),\n",
    "                                       \"average_learning_complexity\", \"average_strategy_complexity\"\n",
    "                                      )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop duplicate and missing values associated with Table PK\n",
    "atlas_games_df = atlas_games_df.dropDuplicates([\"atlas_game_id\"]).dropna(subset=[\"atlas_game_id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# convert list to string format and convert string date to timstamp\n",
    "atlas_games_df = atlas_games_df.withColumn(\"artists\", concat_ws(\",\", \"artists\")\n",
    "                    ).withColumn(\"datetime_extracted\", to_timestamp(col(\"datetime_extracted\"),format=\"dd/MM/yyyy HH:mm:ss\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "editable": true
   },
   "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>atlas_game_id</th>\n",
       "      <th>game_name</th>\n",
       "      <th>url</th>\n",
       "      <th>datetime_extracted</th>\n",
       "      <th>year_published</th>\n",
       "      <th>min_players</th>\n",
       "      <th>max_players</th>\n",
       "      <th>min_playtime</th>\n",
       "      <th>max_playtime</th>\n",
       "      <th>min_age</th>\n",
       "      <th>primary_publisher</th>\n",
       "      <th>primary_designer</th>\n",
       "      <th>artists</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>num_complexity</th>\n",
       "      <th>average_learning_complexity</th>\n",
       "      <th>average_strategy_complexity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2Acdazgmgl</td>\n",
       "      <td>Genesis</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/2Acd...</td>\n",
       "      <td>2022-12-02 11:05:31</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>10</td>\n",
       "      <td>Gigantoskop</td>\n",
       "      <td>Peter Hansson</td>\n",
       "      <td>Erik Lundström</td>\n",
       "      <td>7</td>\n",
       "      <td>2.142857</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2GAg97QP3P</td>\n",
       "      <td>Boss Monster: The Golden Dragon</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/2GAg...</td>\n",
       "      <td>2022-12-02 11:05:31</td>\n",
       "      <td>2013</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>13</td>\n",
       "      <td>Brotherwise Games</td>\n",
       "      <td>Johnny O'Neal</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6SwRxlJXHN</td>\n",
       "      <td>Trivial Pursuit X</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/6SwR...</td>\n",
       "      <td>2022-12-02 11:05:31</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>99</td>\n",
       "      <td>30</td>\n",
       "      <td>90</td>\n",
       "      <td>18</td>\n",
       "      <td>Hasbro</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6maEIahmuS</td>\n",
       "      <td>Founding Fathers: Ladies &amp; Orators</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/6maE...</td>\n",
       "      <td>2022-12-02 11:05:31</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>120</td>\n",
       "      <td>300</td>\n",
       "      <td>10</td>\n",
       "      <td>Up and Away Games</td>\n",
       "      <td>Rick Heli</td>\n",
       "      <td>Luca Cammisa</td>\n",
       "      <td>1</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6nJLBBvRMY</td>\n",
       "      <td>Cuisine a la Card</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/6nJL...</td>\n",
       "      <td>2022-12-02 11:05:31</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>45</td>\n",
       "      <td>13</td>\n",
       "      <td>InMotion</td>\n",
       "      <td>Dani Demarest</td>\n",
       "      <td>Dani Demarest,Sarah Drake</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  atlas_game_id                           game_name  \\\n",
       "0    2Acdazgmgl                             Genesis   \n",
       "1    2GAg97QP3P     Boss Monster: The Golden Dragon   \n",
       "2    6SwRxlJXHN                   Trivial Pursuit X   \n",
       "3    6maEIahmuS  Founding Fathers: Ladies & Orators   \n",
       "4    6nJLBBvRMY                   Cuisine a la Card   \n",
       "\n",
       "                                                 url  datetime_extracted  \\\n",
       "0  https://www.boardgameatlas.com/en-CA/game/2Acd... 2022-12-02 11:05:31   \n",
       "1  https://www.boardgameatlas.com/en-CA/game/2GAg... 2022-12-02 11:05:31   \n",
       "2  https://www.boardgameatlas.com/en-CA/game/6SwR... 2022-12-02 11:05:31   \n",
       "3  https://www.boardgameatlas.com/en-CA/game/6maE... 2022-12-02 11:05:31   \n",
       "4  https://www.boardgameatlas.com/en-CA/game/6nJL... 2022-12-02 11:05:31   \n",
       "\n",
       "   year_published  min_players  max_players  min_playtime  max_playtime  \\\n",
       "0            2010            2            6            60            60   \n",
       "1            2013            2            4            20            20   \n",
       "2            2017            4           99            30            90   \n",
       "3            2017            3            6           120           300   \n",
       "4            2015            2            4            15            45   \n",
       "\n",
       "   min_age  primary_publisher primary_designer                    artists  \\\n",
       "0       10        Gigantoskop    Peter Hansson             Erik Lundström   \n",
       "1       13  Brotherwise Games    Johnny O'Neal                              \n",
       "2       18             Hasbro             None                              \n",
       "3       10  Up and Away Games        Rick Heli               Luca Cammisa   \n",
       "4       13           InMotion    Dani Demarest  Dani Demarest,Sarah Drake   \n",
       "\n",
       "   num_ratings  average_rating  num_complexity  average_learning_complexity  \\\n",
       "0            7        2.142857               0                          0.0   \n",
       "1            2        3.000000               0                          0.0   \n",
       "2            1        0.000000               0                          0.0   \n",
       "3            1        3.500000               0                          0.0   \n",
       "4            2        3.000000               0                          0.0   \n",
       "\n",
       "   average_strategy_complexity  \n",
       "0                          0.0  \n",
       "1                          0.0  \n",
       "2                          0.0  \n",
       "3                          0.0  \n",
       "4                          0.0  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_games_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>game_name</th>\n",
       "      <th>url</th>\n",
       "      <th>year_published</th>\n",
       "      <th>min_players</th>\n",
       "      <th>max_players</th>\n",
       "      <th>min_playtime</th>\n",
       "      <th>max_playtime</th>\n",
       "      <th>min_age</th>\n",
       "      <th>primary_publisher</th>\n",
       "      <th>primary_designer</th>\n",
       "      <th>artists</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>num_complexity</th>\n",
       "      <th>average_learning_complexity</th>\n",
       "      <th>average_strategy_complexity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "      <td>21799</td>\n",
       "      <td>22220</td>\n",
       "      <td>22220</td>\n",
       "      <td>21344</td>\n",
       "      <td>21344</td>\n",
       "      <td>21025</td>\n",
       "      <td>21316</td>\n",
       "      <td>18701</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "      <td>23500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>None</td>\n",
       "      <td>Infinity</td>\n",
       "      <td>None</td>\n",
       "      <td>2007.5034175879628</td>\n",
       "      <td>1.954950495049505</td>\n",
       "      <td>5.685688568856886</td>\n",
       "      <td>60.615817091454275</td>\n",
       "      <td>1498.6169883808095</td>\n",
       "      <td>10.493697978596908</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>10.986382978723404</td>\n",
       "      <td>3.265780031155468</td>\n",
       "      <td>0.06876595744680851</td>\n",
       "      <td>0.09862385620220415</td>\n",
       "      <td>0.10939929285705254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>45.045947882720775</td>\n",
       "      <td>0.7166660439362199</td>\n",
       "      <td>13.199158298220713</td>\n",
       "      <td>446.91375206011537</td>\n",
       "      <td>205346.2930730879</td>\n",
       "      <td>3.1749726968690557</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>38.9148257074197</td>\n",
       "      <td>0.7851720248523404</td>\n",
       "      <td>0.46334073200174203</td>\n",
       "      <td>0.5047019610367234</td>\n",
       "      <td>0.549138484531021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>00AI3sb84B</td>\n",
       "      <td>\"La Garde recule!\"</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/00AI...</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black Monk</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>zzmsZaTDjT</td>\n",
       "      <td>🎰 Vegas Slot Machine 1973 Card Game</td>\n",
       "      <td>https://www.boardgameatlas.com/en-CA/game/zzms...</td>\n",
       "      <td>3500</td>\n",
       "      <td>10</td>\n",
       "      <td>999</td>\n",
       "      <td>60000</td>\n",
       "      <td>30000000</td>\n",
       "      <td>45</td>\n",
       "      <td>广州创游</td>\n",
       "      <td>西村裕 (Hiroshi Nishimura)</td>\n",
       "      <td>長谷川 登鯉 (Tori Hasegawa)</td>\n",
       "      <td>968</td>\n",
       "      <td>5.0</td>\n",
       "      <td>17</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary atlas_game_id                            game_name  \\\n",
       "0   count         23500                                23500   \n",
       "1    mean          None                             Infinity   \n",
       "2  stddev          None                                  NaN   \n",
       "3     min    00AI3sb84B                   \"La Garde recule!\"   \n",
       "4     max    zzmsZaTDjT  🎰 Vegas Slot Machine 1973 Card Game   \n",
       "\n",
       "                                                 url      year_published  \\\n",
       "0                                              23500               21799   \n",
       "1                                               None  2007.5034175879628   \n",
       "2                                               None  45.045947882720775   \n",
       "3  https://www.boardgameatlas.com/en-CA/game/00AI...                 100   \n",
       "4  https://www.boardgameatlas.com/en-CA/game/zzms...                3500   \n",
       "\n",
       "          min_players         max_players        min_playtime  \\\n",
       "0               22220               22220               21344   \n",
       "1   1.954950495049505   5.685688568856886  60.615817091454275   \n",
       "2  0.7166660439362199  13.199158298220713  446.91375206011537   \n",
       "3                   0                   0                   0   \n",
       "4                  10                 999               60000   \n",
       "\n",
       "         max_playtime             min_age primary_publisher  \\\n",
       "0               21344               21025             21316   \n",
       "1  1498.6169883808095  10.493697978596908              None   \n",
       "2   205346.2930730879  3.1749726968690557              None   \n",
       "3                   0                   0        Black Monk   \n",
       "4            30000000                  45              广州创游   \n",
       "\n",
       "          primary_designer                 artists         num_ratings  \\\n",
       "0                    18701                   23500               23500   \n",
       "1                     None                    None  10.986382978723404   \n",
       "2                     None                    None    38.9148257074197   \n",
       "3                                                                    0   \n",
       "4  西村裕 (Hiroshi Nishimura)  長谷川 登鯉 (Tori Hasegawa)                 968   \n",
       "\n",
       "       average_rating       num_complexity average_learning_complexity  \\\n",
       "0               23500                23500                       23500   \n",
       "1   3.265780031155468  0.06876595744680851         0.09862385620220415   \n",
       "2  0.7851720248523404  0.46334073200174203          0.5047019610367234   \n",
       "3                 0.0                    0                         0.0   \n",
       "4                 5.0                   17                         5.0   \n",
       "\n",
       "  average_strategy_complexity  \n",
       "0                       23500  \n",
       "1         0.10939929285705254  \n",
       "2           0.549138484531021  \n",
       "3                         0.0  \n",
       "4                         5.0  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_games_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Summary of Data\n",
    "\n",
    "The details table above indicates that:\n",
    "- There are 23.5k rows of data\n",
    "- All rows have the atlas_game_id, game_name, url, artists, and various rating columns\n",
    "- All other columns are +80% complete\n",
    "- The min/max values indicate that there are erroneous entered values such as minimum max_players as 0 - it is expected that some data cleaning would be required when pulling from the data model.\n",
    "\n",
    "It is interesting that this is an order of magnitude lower than the 324k of BGG games data. This is potentially as Board Game Atlas is a newer site and appears to focus on popular board games whereas BGG allows users to upload any board game."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_games_df.write.parquet(os.path.join(output_data, \"atlas_games.parquet\"), partitionBy=[\"year_published\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### AtlasReviews Table\n",
    "\n",
    "The fact table corresponding to `https://www.boardgameatlas.com/api/docs/reviews` provides game reviews - saved to `/data/raw/atlas/reviews/`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath and read data\n",
    "atlas_reviews_data = os.path.join(input_data, \"atlas/reviews/*.json\")\n",
    "atlas_reviews_df = spark.read.json(atlas_reviews_data, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- createdAt: string (nullable = true)\n",
      " |-- date: string (nullable = true)\n",
      " |-- description: string (nullable = true)\n",
      " |-- game_id: string (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- rating: double (nullable = true)\n",
      " |-- review_url: string (nullable = true)\n",
      " |-- title: string (nullable = true)\n",
      " |-- user_id: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "atlas_reviews_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# select columns of interest and rename corresponding with the data model\n",
    "atlas_reviews_df = atlas_reviews_df.select(col(\"id\").alias(\"review_id\"),\n",
    "                                           col(\"createdAt\").alias(\"review_datetime\"),\n",
    "                                           col(\"user_id\").alias(\"atlas_user_id\"),\n",
    "                                           col(\"game_id\").alias(\"atlas_game_id\"),\n",
    "                                           \"rating\", \"description\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop duplicate and missing rows corresponding with table PK and date\n",
    "atlas_reviews_df = atlas_reviews_df.dropDuplicates([\"review_id\"]).dropna(subset=[\"review_id\", \"review_datetime\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# convert string date to timestamp and add year and month columns for data partitioning for Parquet files               \n",
    "atlas_reviews_df = atlas_reviews_df.withColumn(\"review_datetime\", to_timestamp(col(\"review_datetime\"),format=\"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'\")\n",
    "                            ).withColumn(\"year\", year(\"review_datetime\")\n",
    "                            ).withColumn(\"month\", month(\"review_datetime\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "editable": true
   },
   "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>review_id</th>\n",
       "      <th>review_datetime</th>\n",
       "      <th>atlas_user_id</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>description</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01joCJZ1yh</td>\n",
       "      <td>2020-12-21 11:04:57</td>\n",
       "      <td>J99lC5QsBy</td>\n",
       "      <td>FYDls3xiob</td>\n",
       "      <td>4.00</td>\n",
       "      <td>None</td>\n",
       "      <td>2020</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02QoWBeXz4</td>\n",
       "      <td>2019-02-15 01:06:04</td>\n",
       "      <td>N6Xb3ES5d2</td>\n",
       "      <td>IjMcGwdT95</td>\n",
       "      <td>4.00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02iv770JRs</td>\n",
       "      <td>2020-01-28 20:51:48</td>\n",
       "      <td>uWAd7nP8Kj</td>\n",
       "      <td>qnheLzWlKy</td>\n",
       "      <td>3.50</td>\n",
       "      <td>None</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>073m7s7XuT</td>\n",
       "      <td>2022-02-17 13:33:19</td>\n",
       "      <td>Db8v0NdFfV</td>\n",
       "      <td>9iBOPn3lES</td>\n",
       "      <td>4.00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0AdiuuBpLF</td>\n",
       "      <td>2020-02-12 06:48:58</td>\n",
       "      <td>wAFb0Vtn4f</td>\n",
       "      <td>RW0HAoUctF</td>\n",
       "      <td>4.25</td>\n",
       "      <td>None</td>\n",
       "      <td>2020</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    review_id     review_datetime atlas_user_id atlas_game_id  rating  \\\n",
       "0  01joCJZ1yh 2020-12-21 11:04:57    J99lC5QsBy    FYDls3xiob    4.00   \n",
       "1  02QoWBeXz4 2019-02-15 01:06:04    N6Xb3ES5d2    IjMcGwdT95    4.00   \n",
       "2  02iv770JRs 2020-01-28 20:51:48    uWAd7nP8Kj    qnheLzWlKy    3.50   \n",
       "3  073m7s7XuT 2022-02-17 13:33:19    Db8v0NdFfV    9iBOPn3lES    4.00   \n",
       "4  0AdiuuBpLF 2020-02-12 06:48:58    wAFb0Vtn4f    RW0HAoUctF    4.25   \n",
       "\n",
       "  description  year  month  \n",
       "0        None  2020     12  \n",
       "1        None  2019      2  \n",
       "2        None  2020      1  \n",
       "3        None  2022      2  \n",
       "4        None  2020      2  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_reviews_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>review_id</th>\n",
       "      <th>atlas_user_id</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>description</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "      <td>56244</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3.5826648183328853</td>\n",
       "      <td>2025.9931077694237</td>\n",
       "      <td>2020.1637097733956</td>\n",
       "      <td>5.249238554068171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.8343882517801763</td>\n",
       "      <td>6136.261669160499</td>\n",
       "      <td>1.019712819459849</td>\n",
       "      <td>4.099250123035813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>0013QK6PYB</td>\n",
       "      <td>03pnbGwpn7</td>\n",
       "      <td>00AI3sb84B</td>\n",
       "      <td>0.0</td>\n",
       "      <td></td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>zzxyt2S2Bt</td>\n",
       "      <td>zwJQyVMADi</td>\n",
       "      <td>zzmsZaTDjT</td>\n",
       "      <td>5.0</td>\n",
       "      <td>⊑⟒⌰⌰⍜ ⏃⋏⎅ ⍙⟒⌰☊⍜⋔⟒ ⏁⍜ ⋔⊬ ⏚⌰⍜☌\\nNow that I've go...</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary   review_id atlas_user_id atlas_game_id              rating  \\\n",
       "0   count      226871        226871        226871              226871   \n",
       "1    mean        None          None          None  3.5826648183328853   \n",
       "2  stddev        None          None          None  0.8343882517801763   \n",
       "3     min  0013QK6PYB    03pnbGwpn7    00AI3sb84B                 0.0   \n",
       "4     max  zzxyt2S2Bt    zwJQyVMADi    zzmsZaTDjT                 5.0   \n",
       "\n",
       "                                         description                year  \\\n",
       "0                                              56244              226871   \n",
       "1                                 2025.9931077694237  2020.1637097733956   \n",
       "2                                  6136.261669160499   1.019712819459849   \n",
       "3                                                                   2018   \n",
       "4  ⊑⟒⌰⌰⍜ ⏃⋏⎅ ⍙⟒⌰☊⍜⋔⟒ ⏁⍜ ⋔⊬ ⏚⌰⍜☌\\nNow that I've go...                2022   \n",
       "\n",
       "               month  \n",
       "0             226871  \n",
       "1  5.249238554068171  \n",
       "2  4.099250123035813  \n",
       "3                  1  \n",
       "4                 12  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_reviews_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "The data indicates:\n",
    "- there are 227k reviews which all have an atlas_user_id, atlas_game_id, and rating\n",
    "- The description is only available for approximatey 20% of the rows\n",
    "\n",
    "Additional data needs to be added to the table to allow the BoardGameGeek game ID to be added to the fact table such that board game details provided by BoardGameGeek can be joined with the fact table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# add the game name to the table from AtlasDetails\n",
    "atlas_game_name = atlas_games_df.select(\"atlas_game_id\", \"game_name\")\n",
    "atlas_reviews_df = atlas_reviews_df.join(atlas_game_name, \"atlas_game_id\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# add the BoardGameGeek game ID to the table from BGGdetails\n",
    "# note that it was found that some bgg_game_id have the same game name; therefore, need to drop duplicates\n",
    "# otherwise the fact table will grow based on duplicate BGG game names\n",
    "bgg_game_name = bgg_games_df.select(\"bgg_game_id\", \"game_name\").dropDuplicates([\"game_name\"])\n",
    "atlas_reviews_df = atlas_reviews_df.join(bgg_game_name, \"game_name\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# replace the atlas_user_id with the AtlasUsers PK of user_id\n",
    "atlas_user_name = atlas_users_final_df.select(\"user_id\", \"atlas_user_id\")\n",
    "atlas_reviews_df = atlas_reviews_df.join(atlas_user_name, \"atlas_user_id\", how=\"left\")\n",
    "atlas_reviews_df = atlas_reviews_df.drop(\"atlas_user_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "editable": true
   },
   "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>game_name</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>review_datetime</th>\n",
       "      <th>rating</th>\n",
       "      <th>description</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>bgg_game_id</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Iquazu</td>\n",
       "      <td>hi0aXgk2aZ</td>\n",
       "      <td>4aA08h2hzi</td>\n",
       "      <td>2020-04-07 03:42:24</td>\n",
       "      <td>3.75</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77309411333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Akrotiri</td>\n",
       "      <td>alg1QfJagu</td>\n",
       "      <td>spITeQJtiM</td>\n",
       "      <td>2020-04-07 03:42:04</td>\n",
       "      <td>4.00</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "      <td>154458.0</td>\n",
       "      <td>77309411333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Tea Dragon Society Card Game</td>\n",
       "      <td>lS86TDfmnW</td>\n",
       "      <td>FchBgsIFzF</td>\n",
       "      <td>2020-04-07 03:36:12</td>\n",
       "      <td>3.00</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "      <td>236709.0</td>\n",
       "      <td>77309411333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sprawlopolis</td>\n",
       "      <td>uBmwnw5A4w</td>\n",
       "      <td>ENazsB5p5K</td>\n",
       "      <td>2020-04-07 03:40:31</td>\n",
       "      <td>4.00</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "      <td>251658.0</td>\n",
       "      <td>77309411333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Via Nebula</td>\n",
       "      <td>95u6OPX2ip</td>\n",
       "      <td>DuqzEA8jWp</td>\n",
       "      <td>2020-04-07 03:37:04</td>\n",
       "      <td>4.50</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "      <td>191231.0</td>\n",
       "      <td>77309411333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          game_name atlas_game_id   review_id  \\\n",
       "0                            Iquazu    hi0aXgk2aZ  4aA08h2hzi   \n",
       "1                          Akrotiri    alg1QfJagu  spITeQJtiM   \n",
       "2  The Tea Dragon Society Card Game    lS86TDfmnW  FchBgsIFzF   \n",
       "3                      Sprawlopolis    uBmwnw5A4w  ENazsB5p5K   \n",
       "4                        Via Nebula    95u6OPX2ip  DuqzEA8jWp   \n",
       "\n",
       "      review_datetime  rating description  year  month  bgg_game_id  \\\n",
       "0 2020-04-07 03:42:24    3.75              2020      4          NaN   \n",
       "1 2020-04-07 03:42:04    4.00              2020      4     154458.0   \n",
       "2 2020-04-07 03:36:12    3.00              2020      4     236709.0   \n",
       "3 2020-04-07 03:40:31    4.00              2020      4     251658.0   \n",
       "4 2020-04-07 03:37:04    4.50              2020      4     191231.0   \n",
       "\n",
       "       user_id  \n",
       "0  77309411333  \n",
       "1  77309411333  \n",
       "2  77309411333  \n",
       "3  77309411333  \n",
       "4  77309411333  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_reviews_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>game_name</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>description</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>bgg_game_id</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>226625</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "      <td>56244</td>\n",
       "      <td>226871</td>\n",
       "      <td>226871</td>\n",
       "      <td>194364</td>\n",
       "      <td>226794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>Infinity</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3.5826648183328853</td>\n",
       "      <td>2025.9931077694237</td>\n",
       "      <td>2020.1637097733956</td>\n",
       "      <td>5.249238554068171</td>\n",
       "      <td>139374.0826902101</td>\n",
       "      <td>8.047748043938763E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.8343882517801762</td>\n",
       "      <td>6136.261669160499</td>\n",
       "      <td>1.0197128194599077</td>\n",
       "      <td>4.099250123035812</td>\n",
       "      <td>93446.39469608468</td>\n",
       "      <td>4.8647687516762286E11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>\"La Garde recule!\"</td>\n",
       "      <td>00AI3sb84B</td>\n",
       "      <td>0013QK6PYB</td>\n",
       "      <td>0.0</td>\n",
       "      <td></td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>🎰 Vegas Slot Machine 1973 Card Game</td>\n",
       "      <td>zzmsZaTDjT</td>\n",
       "      <td>zzxyt2S2Bt</td>\n",
       "      <td>5.0</td>\n",
       "      <td>⊑⟒⌰⌰⍜ ⏃⋏⎅ ⍙⟒⌰☊⍜⋔⟒ ⏁⍜ ⋔⊬ ⏚⌰⍜☌\\nNow that I've go...</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>374736</td>\n",
       "      <td>1709396983821</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary                            game_name atlas_game_id   review_id  \\\n",
       "0   count                               226625        226871      226871   \n",
       "1    mean                             Infinity          None        None   \n",
       "2  stddev                                  NaN          None        None   \n",
       "3     min                   \"La Garde recule!\"    00AI3sb84B  0013QK6PYB   \n",
       "4     max  🎰 Vegas Slot Machine 1973 Card Game    zzmsZaTDjT  zzxyt2S2Bt   \n",
       "\n",
       "               rating                                        description  \\\n",
       "0              226871                                              56244   \n",
       "1  3.5826648183328853                                 2025.9931077694237   \n",
       "2  0.8343882517801762                                  6136.261669160499   \n",
       "3                 0.0                                                      \n",
       "4                 5.0  ⊑⟒⌰⌰⍜ ⏃⋏⎅ ⍙⟒⌰☊⍜⋔⟒ ⏁⍜ ⋔⊬ ⏚⌰⍜☌\\nNow that I've go...   \n",
       "\n",
       "                 year              month        bgg_game_id  \\\n",
       "0              226871             226871             194364   \n",
       "1  2020.1637097733956  5.249238554068171  139374.0826902101   \n",
       "2  1.0197128194599077  4.099250123035812  93446.39469608468   \n",
       "3                2018                  1                  1   \n",
       "4                2022                 12             374736   \n",
       "\n",
       "                 user_id  \n",
       "0                 226794  \n",
       "1   8.047748043938763E11  \n",
       "2  4.8647687516762286E11  \n",
       "3                      0  \n",
       "4          1709396983821  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_reviews_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Summary of Data\n",
    "\n",
    "The reviews table above indicates that:\n",
    "- There are ~227k reviews which all have an atlas_user_id, atlas_game_id, and rating\n",
    "- Most atlas_game_id were able to retrieve a game_name (99.9%)\n",
    "- bgg_id were able to be added to 86% of the rows based on joining with game_name\n",
    "- The above mismatch in game name between bgg_id and the game_name from the atlas_game_id could be resolved with investigation and string parsing\n",
    "\n",
    "Overall, the reviews fact table is able to join data from BoardGameGeek for the majority of games providing it with rich supporting dimensional game data from multiple sources."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_reviews_df.write.parquet(os.path.join(output_data, \"reviews.parquet\"), partitionBy=[\"year\", \"month\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### AtlasPrices Table\n",
    "\n",
    "The fact table corresponding to `https://www.boardgameatlas.com/api/docs/prices` which provides price data for a game - saved to `/data/raw/atlas/prices/`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath and read data\n",
    "atlas_prices_data = os.path.join(input_data, \"atlas/prices/*/*.json\")\n",
    "atlas_prices_df = spark.read.json(atlas_prices_data, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- condition: string (nullable = true)\n",
      " |-- country: string (nullable = true)\n",
      " |-- currency: string (nullable = true)\n",
      " |-- free_shipping_at: long (nullable = true)\n",
      " |-- free_shipping_text: string (nullable = true)\n",
      " |-- id: string (nullable = true)\n",
      " |-- in_stock: boolean (nullable = true)\n",
      " |-- is_sponsor: boolean (nullable = true)\n",
      " |-- msrp: double (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- price: double (nullable = true)\n",
      " |-- price_category: string (nullable = true)\n",
      " |-- price_text: string (nullable = true)\n",
      " |-- reviewsIframeUrl: string (nullable = true)\n",
      " |-- shipping: double (nullable = true)\n",
      " |-- storeNameSearch: string (nullable = true)\n",
      " |-- store_name: string (nullable = true)\n",
      " |-- updated_at: string (nullable = true)\n",
      " |-- updated_at_ago: string (nullable = true)\n",
      " |-- url: string (nullable = true)\n",
      " |-- variant: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "atlas_prices_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# extract columns of interest and rename corresponding to the data model\n",
    "atlas_prices_df = atlas_prices_df.select(col(\"id\").alias(\"atlas_price_id\"),\n",
    "                                         col(\"updated_at\").alias(\"price_datetime\"),\n",
    "                                         col(\"name\").alias(\"game_name\"),\n",
    "                                         \"price\", \"currency\", \"msrp\", \"url\", \"store_name\", \n",
    "                                         \"country\", \"price_category\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# drop missing or duplicates rows associated with the timestamp or the table PK\n",
    "atlas_prices_df = atlas_prices_df.dropDuplicates([\"atlas_price_id\"]).dropna(subset=[\"atlas_price_id\", \"price_datetime\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# convert string date to timestamp and add year and month columns for data partioning for Parquet files\n",
    "atlas_prices_df = atlas_prices_df.withColumn(\"price_datetime\", to_timestamp(col(\"price_datetime\"),format=\"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'\")\n",
    "                            ).withColumn(\"year\", year(\"price_datetime\")\n",
    "                            ).withColumn(\"month\", month(\"price_datetime\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Follow the same approach as the reviews table to add the atlas_game_id and bgg_id to the fact table based on the game_name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_game_name = atlas_game_name.dropDuplicates([\"game_name\"])\n",
    "atlas_prices_df = atlas_prices_df.join(atlas_game_name, \"game_name\", how='left')\n",
    "bgg_game_name = bgg_games_df.select(\"bgg_game_id\", \"game_name\").dropDuplicates([\"game_name\"])\n",
    "atlas_prices_df = atlas_prices_df.join(bgg_game_name, \"game_name\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "editable": true
   },
   "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>game_name</th>\n",
       "      <th>atlas_price_id</th>\n",
       "      <th>price_datetime</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "      <th>msrp</th>\n",
       "      <th>url</th>\n",
       "      <th>store_name</th>\n",
       "      <th>country</th>\n",
       "      <th>price_category</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>bgg_game_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>The Grimm Masquerade</td>\n",
       "      <td>02nRJDZdMX</td>\n",
       "      <td>2022-11-30 21:19:43</td>\n",
       "      <td>19.99</td>\n",
       "      <td>USD</td>\n",
       "      <td>25.00</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>Card Haus</td>\n",
       "      <td>US</td>\n",
       "      <td>us</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>iJRipsPFO4</td>\n",
       "      <td>254513.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Quodd Heroes (NM condition)</td>\n",
       "      <td>0BbElagazD</td>\n",
       "      <td>2022-12-01 16:04:21</td>\n",
       "      <td>0.00</td>\n",
       "      <td>USD</td>\n",
       "      <td>100.00</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>Noble Knight Games</td>\n",
       "      <td>US</td>\n",
       "      <td>used</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Colors of Paris</td>\n",
       "      <td>0DDH6l1StJ</td>\n",
       "      <td>2022-11-28 11:15:02</td>\n",
       "      <td>39.99</td>\n",
       "      <td>USD</td>\n",
       "      <td>49.99</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>Boardlandia</td>\n",
       "      <td>US</td>\n",
       "      <td>us</td>\n",
       "      <td>2022</td>\n",
       "      <td>11</td>\n",
       "      <td>MDx0rG1VlF</td>\n",
       "      <td>269160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carcassonne: Big Box (2022) (PREORDER)</td>\n",
       "      <td>0QOSK8DLSm</td>\n",
       "      <td>2022-12-02 21:01:50</td>\n",
       "      <td>134.95</td>\n",
       "      <td>AUD</td>\n",
       "      <td>165.00</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>Advent Games</td>\n",
       "      <td>US</td>\n",
       "      <td>au</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Chicken Caesar Game</td>\n",
       "      <td>0UXHZ6j2ez</td>\n",
       "      <td>2022-12-01 04:06:24</td>\n",
       "      <td>174.95</td>\n",
       "      <td>USD</td>\n",
       "      <td>0.00</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>US</td>\n",
       "      <td>us</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>etDV6aPHaG</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                game_name atlas_price_id      price_datetime  \\\n",
       "0                    The Grimm Masquerade     02nRJDZdMX 2022-11-30 21:19:43   \n",
       "1             Quodd Heroes (NM condition)     0BbElagazD 2022-12-01 16:04:21   \n",
       "2                         Colors of Paris     0DDH6l1StJ 2022-11-28 11:15:02   \n",
       "3  Carcassonne: Big Box (2022) (PREORDER)     0QOSK8DLSm 2022-12-02 21:01:50   \n",
       "4                     Chicken Caesar Game     0UXHZ6j2ez 2022-12-01 04:06:24   \n",
       "\n",
       "    price currency    msrp                                                url  \\\n",
       "0   19.99      USD   25.00  https://www.boardgameatlas.com/api/price?clien...   \n",
       "1    0.00      USD  100.00  https://www.boardgameatlas.com/api/price?clien...   \n",
       "2   39.99      USD   49.99  https://www.boardgameatlas.com/api/price?clien...   \n",
       "3  134.95      AUD  165.00  https://www.boardgameatlas.com/api/price?clien...   \n",
       "4  174.95      USD    0.00  https://www.boardgameatlas.com/api/price?clien...   \n",
       "\n",
       "           store_name country price_category  year  month atlas_game_id  \\\n",
       "0           Card Haus      US             us  2022     11    iJRipsPFO4   \n",
       "1  Noble Knight Games      US           used  2022     12          None   \n",
       "2         Boardlandia      US             us  2022     11    MDx0rG1VlF   \n",
       "3        Advent Games      US             au  2022     12          None   \n",
       "4              Amazon      US             us  2022     12    etDV6aPHaG   \n",
       "\n",
       "   bgg_game_id  \n",
       "0     254513.0  \n",
       "1          NaN  \n",
       "2     269160.0  \n",
       "3          NaN  \n",
       "4          NaN  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_prices_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>game_name</th>\n",
       "      <th>atlas_price_id</th>\n",
       "      <th>price</th>\n",
       "      <th>currency</th>\n",
       "      <th>msrp</th>\n",
       "      <th>url</th>\n",
       "      <th>store_name</th>\n",
       "      <th>country</th>\n",
       "      <th>price_category</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>atlas_game_id</th>\n",
       "      <th>bgg_game_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>186791</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>186806</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>186807</td>\n",
       "      <td>62904</td>\n",
       "      <td>65315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>3104.5227272727275</td>\n",
       "      <td>None</td>\n",
       "      <td>35.18313232373548</td>\n",
       "      <td>None</td>\n",
       "      <td>44.194532324805856</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2021.9999946468815</td>\n",
       "      <td>11.584191170566413</td>\n",
       "      <td>None</td>\n",
       "      <td>196534.2750822935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>2300.8320485751515</td>\n",
       "      <td>None</td>\n",
       "      <td>40.77147556557906</td>\n",
       "      <td>None</td>\n",
       "      <td>1439.7071935266918</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.002313680716771292</td>\n",
       "      <td>0.8240806918473281</td>\n",
       "      <td>None</td>\n",
       "      <td>90583.78751244425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>Bicycle Po-Ke-No Game</td>\n",
       "      <td>000yvkFTuZ</td>\n",
       "      <td>0.0</td>\n",
       "      <td>AUD</td>\n",
       "      <td>0.0</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>217 Comics Cards &amp; Games</td>\n",
       "      <td>CA</td>\n",
       "      <td>au</td>\n",
       "      <td>2021</td>\n",
       "      <td>5</td>\n",
       "      <td>00ypZiDeXF</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>🔥 Ethnos Board Game / CMON / Brand New &amp; Sealed 🔥</td>\n",
       "      <td>zzzKvCYeQi</td>\n",
       "      <td>2152.8</td>\n",
       "      <td>USD</td>\n",
       "      <td>449500.0</td>\n",
       "      <td>https://www.boardgameatlas.com/api/price?clien...</td>\n",
       "      <td>gameknight.ca</td>\n",
       "      <td>US</td>\n",
       "      <td>used</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>zzmsZaTDjT</td>\n",
       "      <td>375438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary                                          game_name atlas_price_id  \\\n",
       "0   count                                             186791         186807   \n",
       "1    mean                                 3104.5227272727275           None   \n",
       "2  stddev                                 2300.8320485751515           None   \n",
       "3     min                Bicycle Po-Ke-No Game                   000yvkFTuZ   \n",
       "4     max  🔥 Ethnos Board Game / CMON / Brand New & Sealed 🔥     zzzKvCYeQi   \n",
       "\n",
       "               price currency                msrp  \\\n",
       "0             186807   186807              186807   \n",
       "1  35.18313232373548     None  44.194532324805856   \n",
       "2  40.77147556557906     None  1439.7071935266918   \n",
       "3                0.0      AUD                 0.0   \n",
       "4             2152.8      USD            449500.0   \n",
       "\n",
       "                                                 url  \\\n",
       "0                                             186807   \n",
       "1                                               None   \n",
       "2                                               None   \n",
       "3  https://www.boardgameatlas.com/api/price?clien...   \n",
       "4  https://www.boardgameatlas.com/api/price?clien...   \n",
       "\n",
       "                 store_name country price_category                  year  \\\n",
       "0                    186806  186807         186807                186807   \n",
       "1                      None    None           None    2021.9999946468815   \n",
       "2                      None    None           None  0.002313680716771292   \n",
       "3  217 Comics Cards & Games      CA             au                  2021   \n",
       "4             gameknight.ca      US           used                  2022   \n",
       "\n",
       "                month atlas_game_id        bgg_game_id  \n",
       "0              186807         62904              65315  \n",
       "1  11.584191170566413          None  196534.2750822935  \n",
       "2  0.8240806918473281          None  90583.78751244425  \n",
       "3                   5    00ypZiDeXF                  1  \n",
       "4                  12    zzmsZaTDjT             375438  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atlas_prices_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Summary of Data\n",
    "\n",
    "The prices table above indicates that:\n",
    "- There are ~187k prices which all have an atlas_price_id, currency, msrp, url, store_name, country, and price category. Almost all rows have a game_name.\n",
    "- However, only approximately 35% of atlas_game_id and bgg_game_id were able to be joined with the table based on the game name.\n",
    "- From inspecting the sample of game_name in the above table, it can be seen that emojis and general descriptions may be present in the game_name. This would be the issue for joining on game_name.\n",
    "\n",
    "Overall, the price fact table is limited based on the game_name description with how many rows can be joined with the board game detail dimensional tables. However, this still provides useful information for a large number of pricing data. A future step would involve a method to clean-up the game_name provided in the price data. This would not be trivial as the task isn't limited to removing emojis but also text such as 'Brand New & Sealed'. Something along the lines of parsing through the strings for an accepted set of board game names would likely be required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_prices_df.write.parquet(os.path.join(output_data, \"prices.parquet\"), partitionBy=[\"year\", \"month\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### BGGLists Table\n",
    "\n",
    "The fact table corresponding to `https://boardgamegeek.com/xmlapi/geeklist/` which provides GeekList information - saved to `/data/raw/bgg/lists.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# get filepath and read data\n",
    "bgg_lists_data = os.path.join(input_data, \"bgg/lists.csv\")\n",
    "bgg_lists_df = spark.read.csv(bgg_lists_data, header=True, inferSchema=True, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- geeklist_id: string (nullable = true)\n",
      " |-- objtype: string (nullable = true)\n",
      " |-- game_id: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- user: string (nullable = true)\n",
      " |-- postdate: string (nullable = true)\n",
      " |-- bodytext: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "bgg_lists_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# select columns and rename corresponding to the data model\n",
    "bgg_lists_df = bgg_lists_df.withColumnRenamed(\"geeklist\", \"geeklist_id\"\n",
    "                                    ).withColumnRenamed(\"game_id\", \"bgg_game_id\"\n",
    "                                    ).withColumnRenamed(\"name\", \"game_name\"\n",
    "                                    ).withColumnRenamed(\"user\", \"bgg_user_name\"\n",
    "                                    ).withColumnRenamed(\"postdate\", \"post_datetime\"\n",
    "                                    ).withColumnRenamed(\"bodytext\", \"description\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# convert column types\n",
    "bgg_lists_df = bgg_lists_df.withColumn(\"post_datetime\", \n",
    "                                       to_timestamp(col(\"post_datetime\"),format=\"EEE, dd MMM yyyy HH:mm:ss +SSSS\")\n",
    "                                        ).withColumn(\"geeklist_id\", col(\"geeklist_id\").cast(IntegerType())\n",
    "                                        ).withColumn(\"bgg_game_id\", col(\"bgg_game_id\").cast(IntegerType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# add UID to Table and drop any missing geeklist_id or timestamp\n",
    "bgg_lists_df = bgg_lists_df.withColumn(\"list_item_id\", monotonically_increasing_id()\n",
    "                              ).dropna(subset=[\"geeklist_id\", \"post_datetime\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# add year and month columns for data partitioning for Parquet files\n",
    "bgg_lists_df = bgg_lists_df.withColumn(\"year\", year(\"post_datetime\")).withColumn(\"month\", month(\"post_datetime\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Need to add the atlas_game_id to the fact table based on the game_name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "atlas_game_name = atlas_game_name.dropDuplicates([\"game_name\"])\n",
    "bgg_lists_df = bgg_lists_df.join(atlas_game_name, \"game_name\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "editable": true
   },
   "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>game_name</th>\n",
       "      <th>geeklist_id</th>\n",
       "      <th>objtype</th>\n",
       "      <th>bgg_game_id</th>\n",
       "      <th>bgg_user_name</th>\n",
       "      <th>post_datetime</th>\n",
       "      <th>description</th>\n",
       "      <th>list_item_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>atlas_game_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Assembly</td>\n",
       "      <td>307798</td>\n",
       "      <td>boardgame</td>\n",
       "      <td>217353</td>\n",
       "      <td>evrenoz</td>\n",
       "      <td>2022-12-03 08:19:02</td>\n",
       "      <td>Status: Shortlisted\\nFiles: Not downloaded\\nCo...</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>6jML0WjvPQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Destination Earth</td>\n",
       "      <td>307798</td>\n",
       "      <td>boardgame</td>\n",
       "      <td>255468</td>\n",
       "      <td>evrenoz</td>\n",
       "      <td>2022-12-03 08:44:27</td>\n",
       "      <td>Status: Shortlisted\\nFiles: Downloaded https:/...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[Contest Ready] Creeping Crud (2018 Mint Tin D...</td>\n",
       "      <td>307798</td>\n",
       "      <td>None</td>\n",
       "      <td>2095892</td>\n",
       "      <td>evrenoz</td>\n",
       "      <td>2022-12-03 09:46:27</td>\n",
       "      <td>Status: Shortlisted\\nFiles: Downloaded \\nhttps...</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WordCraft</td>\n",
       "      <td>307798</td>\n",
       "      <td>boardgame</td>\n",
       "      <td>366463</td>\n",
       "      <td>evrenoz</td>\n",
       "      <td>2022-12-03 09:53:04</td>\n",
       "      <td>Status: Shortlisted\\nFiles: Not available \\nCo...</td>\n",
       "      <td>3</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Varuna</td>\n",
       "      <td>307798</td>\n",
       "      <td>boardgame</td>\n",
       "      <td>330664</td>\n",
       "      <td>evrenoz</td>\n",
       "      <td>2022-12-03 09:56:06</td>\n",
       "      <td>Status: Shortlisted\\nFiles: Not available \\nCo...</td>\n",
       "      <td>4</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           game_name  geeklist_id    objtype  \\\n",
       "0                                           Assembly       307798  boardgame   \n",
       "1                                  Destination Earth       307798  boardgame   \n",
       "2  [Contest Ready] Creeping Crud (2018 Mint Tin D...       307798       None   \n",
       "3                                          WordCraft       307798  boardgame   \n",
       "4                                             Varuna       307798  boardgame   \n",
       "\n",
       "   bgg_game_id bgg_user_name       post_datetime  \\\n",
       "0       217353       evrenoz 2022-12-03 08:19:02   \n",
       "1       255468       evrenoz 2022-12-03 08:44:27   \n",
       "2      2095892       evrenoz 2022-12-03 09:46:27   \n",
       "3       366463       evrenoz 2022-12-03 09:53:04   \n",
       "4       330664       evrenoz 2022-12-03 09:56:06   \n",
       "\n",
       "                                         description  list_item_id  year  \\\n",
       "0  Status: Shortlisted\\nFiles: Not downloaded\\nCo...             0  2022   \n",
       "1  Status: Shortlisted\\nFiles: Downloaded https:/...             1  2022   \n",
       "2  Status: Shortlisted\\nFiles: Downloaded \\nhttps...             2  2022   \n",
       "3  Status: Shortlisted\\nFiles: Not available \\nCo...             3  2022   \n",
       "4  Status: Shortlisted\\nFiles: Not available \\nCo...             4  2022   \n",
       "\n",
       "   month atlas_game_id  \n",
       "0     12    6jML0WjvPQ  \n",
       "1     12          None  \n",
       "2     12          None  \n",
       "3     12          None  \n",
       "4     12          None  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bgg_lists_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "editable": true
   },
   "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>summary</th>\n",
       "      <th>game_name</th>\n",
       "      <th>geeklist_id</th>\n",
       "      <th>objtype</th>\n",
       "      <th>bgg_game_id</th>\n",
       "      <th>bgg_user_name</th>\n",
       "      <th>description</th>\n",
       "      <th>list_item_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>atlas_game_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>350842</td>\n",
       "      <td>350852</td>\n",
       "      <td>348433</td>\n",
       "      <td>350852</td>\n",
       "      <td>350852</td>\n",
       "      <td>281051</td>\n",
       "      <td>350852</td>\n",
       "      <td>350852</td>\n",
       "      <td>350852</td>\n",
       "      <td>243688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>Infinity</td>\n",
       "      <td>298986.55653950956</td>\n",
       "      <td>None</td>\n",
       "      <td>191288.6108843615</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0134228187919463E99</td>\n",
       "      <td>242160.24634888786</td>\n",
       "      <td>2021.8843700477694</td>\n",
       "      <td>6.644208954202912</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4262.71530020785</td>\n",
       "      <td>None</td>\n",
       "      <td>172050.40068223744</td>\n",
       "      <td>None</td>\n",
       "      <td>4.484103503261552E100</td>\n",
       "      <td>136581.69250412448</td>\n",
       "      <td>0.3197811092572016</td>\n",
       "      <td>3.2305308221227884</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>top 20 solo games 2022 edition</td>\n",
       "      <td>290003</td>\n",
       "      <td>boardgame</td>\n",
       "      <td>1</td>\n",
       "      <td>08thMS</td>\n",
       "      <td>\\t\\t\\t\\t\\t\\t\\t\\t\\t\\nName: \\tUpTurn\\t\\t\\t\\t\\t\\t...</td>\n",
       "      <td>0</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>00AI3sb84B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>신묘한 사다리 (Mysterious Ladder)</td>\n",
       "      <td>307798</td>\n",
       "      <td>videogameseries</td>\n",
       "      <td>2981144</td>\n",
       "      <td>zzzzz9</td>\n",
       "      <td>🦗🦗🦗\\n\\n[size=7]Those are crickets (meaning no ...</td>\n",
       "      <td>485696</td>\n",
       "      <td>2022</td>\n",
       "      <td>12</td>\n",
       "      <td>zzmsZaTDjT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary                         game_name         geeklist_id  \\\n",
       "0   count                            350842              350852   \n",
       "1    mean                          Infinity  298986.55653950956   \n",
       "2  stddev                               NaN    4262.71530020785   \n",
       "3     min    top 20 solo games 2022 edition              290003   \n",
       "4     max       신묘한 사다리 (Mysterious Ladder)              307798   \n",
       "\n",
       "           objtype         bgg_game_id bgg_user_name  \\\n",
       "0           348433              350852        350852   \n",
       "1             None   191288.6108843615          None   \n",
       "2             None  172050.40068223744          None   \n",
       "3        boardgame                   1        08thMS   \n",
       "4  videogameseries             2981144        zzzzz9   \n",
       "\n",
       "                                         description        list_item_id  \\\n",
       "0                                             281051              350852   \n",
       "1                              2.0134228187919463E99  242160.24634888786   \n",
       "2                              4.484103503261552E100  136581.69250412448   \n",
       "3  \\t\\t\\t\\t\\t\\t\\t\\t\\t\\nName: \\tUpTurn\\t\\t\\t\\t\\t\\t...                   0   \n",
       "4  🦗🦗🦗\\n\\n[size=7]Those are crickets (meaning no ...              485696   \n",
       "\n",
       "                 year               month atlas_game_id  \n",
       "0              350852              350852        243688  \n",
       "1  2021.8843700477694   6.644208954202912          None  \n",
       "2  0.3197811092572016  3.2305308221227884          None  \n",
       "3                2021                   1    00AI3sb84B  \n",
       "4                2022                  12    zzmsZaTDjT  "
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bgg_lists_df.describe().toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Summary of Data\n",
    "\n",
    "The lists table above indicates that:\n",
    "- There are ~351k rows of data which all have a geeklist_id.\n",
    "- Most rows >99.9% also have the bgg_game_id and game_name.\n",
    "- Approximately 70% of the games were able to have the atlas_game_id joined. \n",
    "\n",
    "The 70% join of atlas_game_id makes sense as 85% of the bgg_game_id was able to be joined for the AtlasReviews Table and there is an order of magnitude higher number of bgg_game_id compared with atlas_game_id. This still provides the majority of the rows in the table with the ability to aggregate both BGG and Atlas game data from the dimensional tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "bgg_lists_df.write.parquet(os.path.join(output_data, \"lists.parquet\"), partitionBy=[\"year\", \"month\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Time Table\n",
    "\n",
    "The following dimensional table was created to allow easy extraction of datetime information without the need to run functions on the datetime data. The table is created to include all possible datetime information in the three fact tables such that the single dimensional table can be used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# collect all datetimes\n",
    "all_datetime_df = atlas_reviews_df.select(col(\"review_datetime\").alias(\"datetime\")\n",
    "                                    ).union(\n",
    "                                        atlas_prices_df.select(col(\"price_datetime\").alias(\"datetime\"))\n",
    "                                    ).union(\n",
    "                                        bgg_lists_df.select(col(\"post_datetime\").alias(\"datetime\"))\n",
    "                                    ).dropDuplicates([\"datetime\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# extract columns of interest matching the data model\n",
    "time_df = all_datetime_df.select(\n",
    "    \"datetime\",\n",
    "    minute(\"datetime\").alias(\"minute\"),\n",
    "    hour(\"datetime\").alias(\"hour\"),\n",
    "    dayofmonth(\"datetime\").alias(\"day\"),\n",
    "    weekofyear(\"datetime\").alias(\"week\"),\n",
    "    month(\"datetime\").alias(\"month\"),\n",
    "    year(\"datetime\").alias(\"year\"),\n",
    "    dayofweek(\"datetime\").alias(\"weekday\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "editable": true
   },
   "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>datetime</th>\n",
       "      <th>minute</th>\n",
       "      <th>hour</th>\n",
       "      <th>day</th>\n",
       "      <th>week</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-24 18:54:40</td>\n",
       "      <td>54</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-04 03:31:28</td>\n",
       "      <td>31</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-03-16 11:07:31</td>\n",
       "      <td>7</td>\n",
       "      <td>11</td>\n",
       "      <td>16</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-04-01 19:41:44</td>\n",
       "      <td>41</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-07-24 05:20:49</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>24</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>2020</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             datetime  minute  hour  day  week  month  year  weekday\n",
       "0 2020-03-24 18:54:40      54    18   24    13      3  2020        3\n",
       "1 2020-01-04 03:31:28      31     3    4     1      1  2020        7\n",
       "2 2021-03-16 11:07:31       7    11   16    11      3  2021        3\n",
       "3 2020-04-01 19:41:44      41    19    1    14      4  2020        4\n",
       "4 2020-07-24 05:20:49      20     5   24    30      7  2020        6"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "time_df.write.parquet(os.path.join(output_data, \"time.parquet\"), partitionBy=[\"year\", \"month\"], mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Quality Checks\n",
    "The following quality checks are performed for each Table. These checks will be included prior to writing data to Parquet format:\n",
    "- confirm that the number of columns matches the expected number\n",
    "- confirm that the DataFrame contains data\n",
    "- confirm that any columns required as Not Null do not contain any missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "check_tables = {\n",
    "    \"BGGDetails Table\": \n",
    "    {\"file\": \"bgg_games.parquet\",\n",
    "     \"columns\": [\"bgg_game_id\"],\n",
    "     \"count\": 18},\n",
    "    \"AtlasUsers Table\": \n",
    "    {\"file\": \"users.parquet\",\n",
    "     \"columns\": [\"user_id\", \"user_name\"],\n",
    "     \"count\": 11},\n",
    "    \"AtlasDetails Table\": \n",
    "    {\"file\": \"atlas_games.parquet\",\n",
    "     \"columns\": [\"atlas_game_id\"],\n",
    "     \"count\": 18},\n",
    "    \"AtlasReviews Table\":\n",
    "    {\"file\": \"reviews.parquet\",\n",
    "     \"columns\": [\"review_id\", \"review_datetime\"],\n",
    "     \"count\": 10},\n",
    "    \"AtlasPrices Table\":\n",
    "    {\"file\": \"prices.parquet\",\n",
    "     \"columns\": [\"atlas_price_id\", \"price_datetime\"],\n",
    "     \"count\": 14},\n",
    "    \"BGGLists Table\":\n",
    "    {\"file\": \"lists.parquet\",\n",
    "     \"columns\": [\"list_item_id\", \"post_datetime\"],\n",
    "     \"count\": 11},\n",
    "    \"Time Table\":\n",
    "    {\"file\": \"time.parquet\",\n",
    "     \"columns\": [\"datetime\"],\n",
    "     \"count\": 8},\n",
    "    \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row check passed: BGGDetails Table has 342570 rows\n",
      "Column check passed: BGGDetails Table has 18 columns\n",
      "Null check passed for bgg_game_id column in BGGDetails Table\n",
      "Row check passed: AtlasUsers Table has 5423 rows\n",
      "Column check passed: AtlasUsers Table has 11 columns\n",
      "Null check passed for user_id column in AtlasUsers Table\n",
      "Null check passed for user_name column in AtlasUsers Table\n",
      "Row check passed: AtlasDetails Table has 23500 rows\n",
      "Column check passed: AtlasDetails Table has 18 columns\n",
      "Null check passed for atlas_game_id column in AtlasDetails Table\n",
      "Row check passed: AtlasReviews Table has 226871 rows\n",
      "Column check passed: AtlasReviews Table has 10 columns\n",
      "Null check passed for review_id column in AtlasReviews Table\n",
      "Null check passed for review_datetime column in AtlasReviews Table\n",
      "Row check passed: AtlasPrices Table has 186807 rows\n",
      "Column check passed: AtlasPrices Table has 14 columns\n",
      "Null check passed for atlas_price_id column in AtlasPrices Table\n",
      "Null check passed for price_datetime column in AtlasPrices Table\n",
      "Row check passed: BGGLists Table has 350852 rows\n",
      "Column check passed: BGGLists Table has 11 columns\n",
      "Null check passed for list_item_id column in BGGLists Table\n",
      "Null check passed for post_datetime column in BGGLists Table\n",
      "Row check passed: Time Table has 513692 rows\n",
      "Column check passed: Time Table has 8 columns\n",
      "Null check passed for datetime column in Time Table\n"
     ]
    }
   ],
   "source": [
    "for table, params in check_tables.items():\n",
    "    file = params[\"file\"]\n",
    "    data = spark.read.parquet(os.path.join(output_data, file))\n",
    "    col_count = len(data.columns)\n",
    "    row_count = data.count()\n",
    "    \n",
    "    if row_count == 0:\n",
    "        print(f\"Row check failed: {table} has no rows\")\n",
    "    else:\n",
    "        print(f\"Row check passed: {table} has {row_count} rows\")\n",
    "        \n",
    "    expect_col = params[\"count\"]\n",
    "    if col_count != expect_col:\n",
    "        print(f\"Column check failed: {table} has {col_count} instead of {expect_col} columns\")\n",
    "    else:\n",
    "        print(f\"Column check passed: {table} has {col_count} columns\")\n",
    "    \n",
    "    for check in params[\"columns\"]:\n",
    "        check_null = data.filter(col(check).isNull()).count()\n",
    "        if check_null > 0:\n",
    "            print(f\"Null check failed for {check} column in {table}, there are {check_null} values\")\n",
    "        else:\n",
    "            print(f\"Null check passed for {check} column in {table}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "## Summary\n",
    "\n",
    "The EDA and associated data wrangling to create the pipelines is completed in this notebook resulting in the three fact tables using the same associated dimensional tables. The AtlasReviews and BGGLists tables were able to successfully aggregate both Atlas and BGG board game data for the majority of rows. However, the AtlasPrices tables were able to join with less board game data as discussed due to the game_name provided with the prices.\n",
    "\n",
    "Overall, the data models provides rich datasets that could be used to support an application understanding any or all of game prices, reviews, and popularity."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "boardgame",
   "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.15 | packaged by conda-forge | (main, Nov 22 2022, 15:55:03) \n[GCC 10.4.0]"
  },
  "vscode": {
   "interpreter": {
    "hash": "0aebc4ece209268a392c563d0c583cfcff08395e07b8cc3215fcbfbf14e385ce"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}