DataCheckpoint_Group015_WI24.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**If you lost points on the last checkpoint you can get them back by responding to TA/IA feedback**  \n",
    "\n",
    "Update/change the relevant sections where you lost those points, make sure you respond on GitHub Issues to your TA/IA to call their attention to the changes you made here.\n",
    "\n",
    "Please update your Timeline... no battle plan survives contact with the enemy, so make sure we understand how your plans have changed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COGS 108 - Data Checkpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Names\n",
    "\n",
    "- Leila Andrade\n",
    "- Suhaib Chowdhury\n",
    "- Yuang Cui\n",
    "- Noah Kim\n",
    "- Yvonne Ng"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Research Question"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What characteristics of any given Lego set contributes the most, the least or varying degree to its popularity amongst a population of online consumers worldwide? How accurately can these relationships derived from analyzing the characteristics amongst existing Lego sets predict the popularity of future sets? Some of the characteristics that we would like to explore further in the following sub-questions:\n",
    "\n",
    "- What LEGO sets have been inspired by movies that contribute to set popularity?\n",
    "- How do varying number of pieces amongst sets influence their popularity?\n",
    "- Does the color variability contribute to set popularity?\n",
    "- Does price variability have a significant impact on set popularity?\n",
    "- What impact do consumer ratings and reviews have on set popularity?\n",
    "- How does the presence of LEGO Minifigures affect the set popularity?\n",
    "- Does the designer of the LEGO set have any influence on its popularity?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background and Prior Work"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "LEGO® Group was built brick by brick to become the powerhouse that it is today, reaching millions of consumers and accumulating billions in revenue each year. Increases in global market share and profit margins reflect their rise in popularity. In 2022, the LEGO Group experienced a whopping 12 percent growth in consumer sales which can be attributed to introduction of sets from themes such as LEGO® City, LEGO® Icons and LEGO® Technic™, as well as themes with intellectual property partners such as LEGO® *Star Wars*™ and LEGO® Harry Potter™ <a name=\"cite_ref-1\"></a>[<sup>1</sup>](#cite_note-1). All these LEGO sets have one thing in common - the brick - which comes in different shapes, sizes, and colors.\n",
    "LEGO®s were not always synonymous with the versatile plastic pieces meant for play, exploration, art or more. Ole Kirk Kristiansen, a master carpenter from Billund, Denmark, began crafting wooden toys, and combined the Danish words “Leg Godt” for “play well” to form the renowned “LEGO®” in 1936 <a name=\"cite_ref-2\"></a>[<sup>2</sup>](#cite_note-2). Ole also launched the LEGO® bricks predecessors, Automatic Binding Bricks, however it was Godtfred Kirk Christiansen, his son, that catapulted LEGO® Group to new heights in the 1950s by introducing LEGO® System in Play and re-designing the LEGO® brick <a name=\"cite_ref-2\"></a>[<sup>2</sup>](#cite_note-2). The next few decades witness an explosion in available LEGO® sets due to the introduction of new pieces, themes, and entertainment as well as construction of stores, factories and theme parks. \n",
    "\n",
    "A 2016 analysis conducted by Mode data scientist Joel Carron, revealed some interesting findings from a 67-year timeline of LEGO® sets. For instance, LEGO® bricks are graying and large sets with more pieces continue to grow while small sets are relatively the same <a name=\"cite_ref-3\"></a>[<sup>3</sup>](#cite_note-3). Thus, we find it critical to analyze the colors and number of pieces across all types of sets. Another notable trend is that the number of sets released each year have increased except when the LEGO® group was battling against bankruptcy between the years of 2004 and 2009 <a name=\"cite_ref-3\"></a>[<sup>3</sup>](#cite_note-3). This is not surprising as the whole world was experiencing a financial crisis and many companies struggled to stay afloat. Hence, this period of financial uncertainty could have an impact on our data, and we need to be cognizant of the fact. \n",
    "\n",
    "Subsequently, a 2021 analysis by data enthusiast Yvonne Zhang that was inspired by the previous study disclosed more updated findings. For example, 9,625 sets out of 16,691 unique sets have themes <a name=\"cite_ref-4\"></a>[<sup>4</sup>](#cite_note-4). Simply put, themed LEGO® sets make-up 58 percent of all sets and could indicate a greater influence than generic sets. Zhang also emphasized that majority of sets were derived from LEGO® System theme before 1970, then shifted to Legoland theme in the 1970s and onto other themes such as Duplo, Universal Building, Bionicle from the 1980s to the 2010s <a name=\"cite_ref-4\"></a>[<sup>4</sup>](#cite_note-4). This goes to show that themes can come and go, but the bricks are here forever. LEGO® Group needs to continue developing and releasing new themes to keep capture the heart of their fans and the wallets of their consumers. Zhang reaffirms the upward trend for number of sets and number of pieces per set <a name=\"cite_ref-4\"></a>[<sup>4</sup>](#cite_note-4). This further indicates that the two quantitative characteristics could have a significant impact on measuring the popularity of future sets. Lastly, Zhang elaborated on how the bricks are graying by showing that black, white, light bluish gray, dark bluish gray, and red are the dominant colors in the past 72 years <a name=\"cite_ref-4\"></a>[<sup>4</sup>](#cite_note-4). When color tastes changes in society, then color palettes on products do the same. As a result, color cannot be overlooked when attempting to predict the popularity of a future set. \n",
    "\n",
    "A recent 2023 analysis by data analyst Tyran Christian provided the most extensive and eye-opening look into LEGO sets by using a data set from Rebrickable. Star Wars and Technic sets have the highest number of parts and have longevity amongst other favorites including Duplo, City, Creator, Ninjago with Marvel Super Heros being a rising theme <a name=\"cite_ref-5\"></a>[<sup>5</sup>](#cite_note-5). Here we can see that older themes remain strong in the market, yet new themes are able to breach the rankings. Another relationship to unpack would be the popularity of classic LEGO themes in comparison to licensed themes. Christian demonstrated that classic themes like Western, Vikings or Adventurers increased in value after their end-of-life similar to licensed themes such as Batman, Spider-Man and Indiana Jones despite assumption that licensed themes would perform better <a name=\"cite_ref-5\"></a>[<sup>5</sup>](#cite_note-5). Christian even recommended two ways to further his analysis: implement a predictive machine learning model and add LEGO Minifigures data <a name=\"cite_ref-5\"></a>[<sup>5</sup>](#cite_note-5). We plan to take his recommendations into account and develop these pathways.  \n",
    "\n",
    "This preliminary research helped us better understand the current trends and what has already been done. Our project aims to update these previous projects with 2024 data by performing similar analyses and to expand by analyzing other variables that have not been considered. For instance, we plan on looking into LEGO Minifigures, consumer ratings/reviews, the designers of the LEGO sets, and associations with popular entertainment. Moreover, the in-depth analysis of the LEGO datasets will equip us with knowledge to create a predictive model to determine the popularity of hypothetical LEGO sets which has not been accomplished by prior studies. We hope that the predictive model can be used as a tool for creators to test whether their sets could become a viable product. \n",
    "\n",
    "1. <a name=\"cite_note-1\"></a>[^](#cite_ref-1) LEGO Group. \"LEGO Group 2022 Annual Results.\" LEGO Official Website, March 2023, https://www.lego.com/en-us/aboutus/news/2023/march/2022-annual-results \n",
    "2. <a name=\"cite_note-2\"></a>[^](#cite_ref-2) LEGO Group. \"The LEGO Group History.\" LEGO Official Website, https://www.lego.com/en-us/aboutus/lego-group/the-lego-group-history \n",
    "3. <a name=\"cite_note-3\"></a>[^](#cite_ref-3) Carron, Joel. \"67 Years of Lego Sets.\" Mode Blog, 21 July 2016, https://mode.com/blog/lego-data-analysis \n",
    "4. <a name=\"cite_note-4\"></a>[^](#cite_ref-4) Zhang, Yvonne. \"Exploratory Analysis on LEGO Dataset.\" Medium, 28 Feb. 2021, https://medium.com/analytics-vidhya/exploratory-analysis-on-lego-dataset-8967e37fc5dd \n",
    "5. <a name=\"cite_note-5\"></a>[^](#cite_ref-5) Christian, Tyran. \"Exploring the Rebrickable LEGO Dataset (Python).\" Medium, 7 Feb. 2023, https://medium.com/@beananalytics_/exploring-the-rebrickable-lego-dataset-python-9f95144be8f1 \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hypothesis\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We believe that specific settings of our chosen variables for analyzing a LEGO set such as the presence of Minifigures, increased positive ratings/reviews, and existence of visual media for popular entertainment significantly drives its popularity upwards. Simultaneously, we suspect that other attributes like price variability, color variability, number of pieces and the set designer do not have a meaningful impact on the popularity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data overview (Timeline Between 1949 - Present)\n",
    "\n",
    "- Dataset #1\n",
    "  - Dataset Name: BrickLink Color Guide\n",
    "  - Link to the dataset: https://www.bricklink.com/catalogColors.asp\n",
    "  - Number of observations: Total of 150 colors including categories such as Solid, Transparent, Chrome, Pearl, Satin, Metallic, Milky, Glitter, and Speckle\n",
    "  - Number of variables: 6\n",
    "- Dataset #2\n",
    "  - Dataset Name: Rebrickable\n",
    "  - Link to the dataset: https://rebrickable.com/downloads/\n",
    "  - Number of observations: Rebrickable stores data in different files for different categories, but the breakdown is as follows for colors-265, sets-22333, themes-460, Minifigures-14143\n",
    "  - Number of variables: 7\n",
    "- Dataset #3\n",
    "  - Dataset Name: BrickSet\n",
    "  - Link to the dataset: https://brickset.com\n",
    "  - Number of observations: BrickSet focuses on variation in piece counts, minifigure numbers, prices, and ratings, showing diverse offerings in themes\n",
    "  - Number of variables: 22\n",
    "\n",
    "BrickLink Color Guide provides comprehensive information regarding colors in various categories of LEGO bricks. The online BrickLink community is a population of over 1 million registered users in 70 countries. Through their collective input, we have access to the number of parts of a certain color and its prevalence across sets. Additionally, we have access to the numerical demand and supply of a specific color as well as the length of its existence. All these variables are necessary for conducting our analysis to find an answer to our research question. We will transform the raw data by dropping columns storing irrelevant information and removing odd colors to achieve a tidy DataFrame, then combine with color information from Rebrickable. \n", 
     "\n",
    "Rebrickable is a free online LEGO database supplying extensive and current information on LEGOs as it is updated daily. We extracted data pertaining to sets, themes, parts, colors, and Minifigures from an online, worldwide community with 940,114 registered users. It is important to note that since we pulled our dataset the third week of February 2024 that there has been a slight change in number of observations for each category. Raw data from Rebrickable will be organized by removing irrelevant columns and merging related datasets together to form a useful DataFrame.\n",
     "\n",
    "Brickset.com was launched in 1997 to catalog the growing number of promotional sets. Since then it has evolved significantly becoming the foremost online resource for LEGO sets, offering comprehensive coverage and information.\n",
    "We were able to obtain most of the data from the website in order to look through which sets are high in demand and if there are any correlation between ratings and/or reviews to number of wants. The raw data will be cleaned by dropping irrelevant columns as well as separating the launch and exit dates column for easy reference and navigation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BrickLink and Rebrickable Datasets #1 and #2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "from urllib.request import Request, urlopen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Colors\n",
    "We will combine colors data from two sources: Rebrickable and BrickLink\n",
    "We will first handle BrickLink. Since the data is taken from a webpage that uses strange HTML, we will need to do a lot of wrangling to get the raw data into a usable form. We will not be using Modulex colors since Modulex is a separate thing,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Parts</th>\n",
       "      <th>In Sets</th>\n",
       "      <th>Wanted</th>\n",
       "      <th>For Sale</th>\n",
       "      <th>First Year Active</th>\n",
       "      <th>Recent Year Active</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>White</td>\n",
       "      <td>15304</td>\n",
       "      <td>11287</td>\n",
       "      <td>22492</td>\n",
       "      <td>15610</td>\n",
       "      <td>1949</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Very Light Gray</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>1427</td>\n",
       "      <td>63</td>\n",
       "      <td>1997</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Very Light Bluish Gray</td>\n",
       "      <td>53</td>\n",
       "      <td>49</td>\n",
       "      <td>1846</td>\n",
       "      <td>103</td>\n",
       "      <td>2004</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Light Bluish Gray</td>\n",
       "      <td>4779</td>\n",
       "      <td>7577</td>\n",
       "      <td>8797</td>\n",
       "      <td>5388</td>\n",
       "      <td>2003</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Light Gray</td>\n",
       "      <td>1866</td>\n",
       "      <td>3204</td>\n",
       "      <td>5864</td>\n",
       "      <td>2494</td>\n",
       "      <td>1954</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0                     Name  Parts In Sets Wanted For Sale First Year Active  \\\n",
       "ID                                                                            \n",
       "1                    White  15304   11287  22492    15610              1949   \n",
       "49         Very Light Gray     21      24   1427       63              1997   \n",
       "99  Very Light Bluish Gray     53      49   1846      103              2004   \n",
       "86       Light Bluish Gray   4779    7577   8797     5388              2003   \n",
       "9               Light Gray   1866    3204   5864     2494              1954   \n",
       "\n",
       "0  Recent Year Active  \n",
       "ID                     \n",
       "1                2024  \n",
       "49               2004  \n",
       "99               2013  \n",
       "86               2024  \n",
       "9                2008  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pull color data\n",
    "req = Request(\n",
    "    url='https://www.bricklink.com/catalogColors.asp?v=0&itemType=P&itemNo=', \n",
    "    headers={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:122.0) Gecko/20100101 Firefox/122.0'}\n",
    ")\n",
    "color_bricklink_tables = pd.read_html(urlopen(req))\n",
    "\n",
    "# Helper functions\n",
    "def extract_first_date(string):\n",
    "    string = string.replace('\\xa0','')\n",
    "    match = re.match(r'(\\d+)-(\\d+)', string)\n",
    "    if match == None:\n",
    "        return np.nan\n",
    "    else:\n",
    "        return match.group(1)\n",
    "\n",
    "def extract_last_date(string):\n",
    "    string = string.replace('\\xa0','')\n",
    "    match = re.match(r'(\\d+)-(\\d+)', string)\n",
    "    if match == None:\n",
    "        return np.nan\n",
    "    else:\n",
    "        return match.group(2)\n",
    "\n",
    "def bricklink_fix(df):\n",
    "    df = df.dropna(axis=1, how='all')\n",
    "    df.columns = df.iloc[0]\n",
    "    df = df.drop(df.index[0])\n",
    "    #print(df.head())\n",
    "    df = df.set_index('ID')\n",
    "    #df['Color Timeline'].apply(extract_dates)\n",
    "    df['First Year Active'] = df['Color Timeline'].apply(extract_first_date)\n",
    "    df['Recent Year Active'] = df['Color Timeline'].apply(extract_last_date)\n",
    "    df = df.drop(\"Color Timeline\", axis=1)\n",
    "    return df    \n",
    "\n",
    "# Fix all of the tables and combine them\n",
    "bricklink_tables = []\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[4]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[7]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[10]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[13]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[16]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[19]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[22]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[25]))\n",
    "bricklink_tables.append(bricklink_fix(color_bricklink_tables[28]))\n",
    "bricklink = pd.concat(bricklink_tables)\n",
    "bricklink = bricklink.dropna()\n",
    "bricklink.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now get the data from Rebrickable. We will drop obscure or unreleased colors from the set. Since we did Bricklink, we will use the BrickLink id for the index. We then merge the two datasets into our final color DataFrame "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "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>RGB</th>\n",
       "      <th>Num Parts</th>\n",
       "      <th>Num Sets</th>\n",
       "      <th>LEGO</th>\n",
       "      <th>LDraw</th>\n",
       "      <th>BrickLink</th>\n",
       "      <th>BrickOwl</th>\n",
       "      <th>Name</th>\n",
       "      <th>Parts</th>\n",
       "      <th>In Sets</th>\n",
       "      <th>Wanted</th>\n",
       "      <th>For Sale</th>\n",
       "      <th>First Year Active</th>\n",
       "      <th>Recent Year Active</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BrickLink ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>421686</td>\n",
       "      <td>123247</td>\n",
       "      <td>1 ['White']</td>\n",
       "      <td>15 ['White']</td>\n",
       "      <td>1 ['White']</td>\n",
       "      <td>92 ['White']</td>\n",
       "      <td>White</td>\n",
       "      <td>15304</td>\n",
       "      <td>11287</td>\n",
       "      <td>22492</td>\n",
       "      <td>15610</td>\n",
       "      <td>1949</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6D6E5C</td>\n",
       "      <td>22319</td>\n",
       "      <td>7750</td>\n",
       "      <td>27 ['Dark grey', 'DK.GREY']</td>\n",
       "      <td>8 ['Dark_Gray']</td>\n",
       "      <td>10 ['Dark Gray']</td>\n",
       "      <td>53 ['Dark Gray']</td>\n",
       "      <td>Dark Gray</td>\n",
       "      <td>1055</td>\n",
       "      <td>1706</td>\n",
       "      <td>4179</td>\n",
       "      <td>1505</td>\n",
       "      <td>1961</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>DF6695</td>\n",
       "      <td>233</td>\n",
       "      <td>49</td>\n",
       "      <td>114 ['Tr. Medium Reddish-Violet w. Glitter 2%'...</td>\n",
       "      <td>114 ['Glitter_Trans_Dark_Pink']</td>\n",
       "      <td>100 ['Glitter Trans-Dark Pink']</td>\n",
       "      <td>132 ['Transparent Pink Glitter']</td>\n",
       "      <td>Glitter Trans-Dark Pink</td>\n",
       "      <td>12</td>\n",
       "      <td>40</td>\n",
       "      <td>157</td>\n",
       "      <td>17</td>\n",
       "      <td>1999</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>233</td>\n",
       "      <td>41</td>\n",
       "      <td>117 ['Transparent Glitter', 'TR.W.GLITTER'] 12...</td>\n",
       "      <td>117 ['Glitter_Trans_Clear']</td>\n",
       "      <td>101 ['Glitter Trans-Clear']</td>\n",
       "      <td>131 ['Transparent Glitter']</td>\n",
       "      <td>Glitter Trans-Clear</td>\n",
       "      <td>16</td>\n",
       "      <td>33</td>\n",
       "      <td>182</td>\n",
       "      <td>22</td>\n",
       "      <td>1999</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>A5A5CB</td>\n",
       "      <td>140</td>\n",
       "      <td>67</td>\n",
       "      <td>129 ['Tr. Bright Bluish Violet w. Glitter 2%',...</td>\n",
       "      <td>129 ['Glitter_Trans_Purple']</td>\n",
       "      <td>102 ['Glitter Trans-Purple']</td>\n",
       "      <td>22 ['Transparent Purple Glitter']</td>\n",
       "      <td>Glitter Trans-Purple</td>\n",
       "      <td>10</td>\n",
       "      <td>60</td>\n",
       "      <td>179</td>\n",
       "      <td>12</td>\n",
       "      <td>2000</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 RGB  Num Parts  Num Sets  \\\n",
       "BrickLink ID                                \n",
       "1             FFFFFF     421686    123247   \n",
       "10            6D6E5C      22319      7750   \n",
       "100           DF6695        233        49   \n",
       "101           FFFFFF        233        41   \n",
       "102           A5A5CB        140        67   \n",
       "\n",
       "                                                           LEGO  \\\n",
       "BrickLink ID                                                      \n",
       "1                                                   1 ['White']   \n",
       "10                                  27 ['Dark grey', 'DK.GREY']   \n",
       "100           114 ['Tr. Medium Reddish-Violet w. Glitter 2%'...   \n",
       "101           117 ['Transparent Glitter', 'TR.W.GLITTER'] 12...   \n",
       "102           129 ['Tr. Bright Bluish Violet w. Glitter 2%',...   \n",
       "\n",
       "                                        LDraw  \\\n",
       "BrickLink ID                                    \n",
       "1                                15 ['White']   \n",
       "10                            8 ['Dark_Gray']   \n",
       "100           114 ['Glitter_Trans_Dark_Pink']   \n",
       "101               117 ['Glitter_Trans_Clear']   \n",
       "102              129 ['Glitter_Trans_Purple']   \n",
       "\n",
       "                                    BrickLink  \\\n",
       "BrickLink ID                                    \n",
       "1                                 1 ['White']   \n",
       "10                           10 ['Dark Gray']   \n",
       "100           100 ['Glitter Trans-Dark Pink']   \n",
       "101               101 ['Glitter Trans-Clear']   \n",
       "102              102 ['Glitter Trans-Purple']   \n",
       "\n",
       "                                       BrickOwl                     Name  \\\n",
       "BrickLink ID                                                               \n",
       "1                                  92 ['White']                    White   \n",
       "10                             53 ['Dark Gray']                Dark Gray   \n",
       "100            132 ['Transparent Pink Glitter']  Glitter Trans-Dark Pink   \n",
       "101                 131 ['Transparent Glitter']      Glitter Trans-Clear   \n",
       "102           22 ['Transparent Purple Glitter']     Glitter Trans-Purple   \n",
       "\n",
       "              Parts In Sets Wanted For Sale First Year Active  \\\n",
       "BrickLink ID                                                    \n",
       "1             15304   11287  22492    15610              1949   \n",
       "10             1055    1706   4179     1505              1961   \n",
       "100              12      40    157       17              1999   \n",
       "101              16      33    182       22              1999   \n",
       "102              10      60    179       12              2000   \n",
       "\n",
       "             Recent Year Active  \n",
       "BrickLink ID                     \n",
       "1                          2024  \n",
       "10                         2006  \n",
       "100                        2024  \n",
       "101                        2023  \n",
       "102                        2024  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_bricklink_id(string):\n",
    "    string = string.strip()\n",
    "    match = re.match(r'(\\d+)', string)\n",
    "    return match.group(1)\n",
    "\n",
    "colors_detailed = pd.read_csv('rebrickable/colors_updated.csv')\n",
    "# These colors do not have official names or have any dates. These colors are extremely rare so we probably dont' need them.\n",
    "colors_detailed = colors_detailed.dropna(axis=0, subset=['LEGO', 'First Year', 'BrickLink'])\n",
    "colors_detailed = colors_detailed.drop(['Img', 'Unnamed: 0', 'ID', 'First Year', 'Last Year', 'Name'], axis=1)\n",
    "colors_detailed['BrickLink ID'] = colors_detailed['BrickLink'].apply(get_bricklink_id)\n",
    "colors_detailed = colors_detailed.set_index('BrickLink ID')\n",
    "colors_detailed\n",
    "\n",
    "colors_df = pd.merge(colors_detailed, bricklink, left_index=True, right_index=True)\n",
    "colors_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sets, Themes, and Minifigures\n",
    "Here we will combine set, minifigure, and theme data from Rebrickable.We first cleaned up the set information, dropping products that are not sets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "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>set_num</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme</th>\n",
       "      <th>subtheme</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001-1</td>\n",
       "      <td>Gears</td>\n",
       "      <td>1965</td>\n",
       "      <td>43</td>\n",
       "      <td>System</td>\n",
       "      <td>Samsonite</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0011-2</td>\n",
       "      <td>Town Mini-Figures</td>\n",
       "      <td>1979</td>\n",
       "      <td>12</td>\n",
       "      <td>Town</td>\n",
       "      <td>Classic Town</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0012-1</td>\n",
       "      <td>Space Mini-Figures</td>\n",
       "      <td>1979</td>\n",
       "      <td>12</td>\n",
       "      <td>Space</td>\n",
       "      <td>Supplemental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0013-1</td>\n",
       "      <td>Space Mini-Figures</td>\n",
       "      <td>1979</td>\n",
       "      <td>12</td>\n",
       "      <td>Space</td>\n",
       "      <td>Supplemental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0014-1</td>\n",
       "      <td>Space Mini-Figures</td>\n",
       "      <td>1979</td>\n",
       "      <td>2</td>\n",
       "      <td>Space</td>\n",
       "      <td>Supplemental</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  set_num                name  year  num_parts   theme      subtheme\n",
       "1   001-1               Gears  1965         43  System     Samsonite\n",
       "2  0011-2   Town Mini-Figures  1979         12    Town  Classic Town\n",
       "4  0012-1  Space Mini-Figures  1979         12   Space  Supplemental\n",
       "5  0013-1  Space Mini-Figures  1979         12   Space  Supplemental\n",
       "6  0014-1  Space Mini-Figures  1979          2   Space  Supplemental"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "themes = pd.read_csv('rebrickable/themes.csv')\n",
    "themes = themes.set_index('id')\n",
    "\n",
    "themes['parent_id'] = themes['parent_id'].apply(lambda num: int(num) if not pd.isna(num) else np.nan)\n",
    "\n",
    "sets = pd.read_csv('rebrickable/sets.csv')\n",
    "sets = sets.drop('img_url', axis=1)\n",
    "# Most, if not all, of these are either not lego sets (eg books or video games), bundles of other sets, or single replacement parts.\n",
    "sets = sets.drop(sets[sets.num_parts==0].index, axis = 0)\n",
    "sets = sets.drop(sets[sets.num_parts==1].index, axis = 0)\n",
    "# Replaces the Theme ID with the theme name and subtheme (if there is one)\n",
    "sets['theme'] = sets['theme_id'].apply(lambda string: themes['name'].loc[string] if np.isnan(themes['parent_id'].loc[string]) else themes['name'].loc[themes['parent_id'].loc[string]])\n",
    "sets['subtheme'] = sets['theme_id'].apply(lambda string: np.nan if np.isnan(themes['parent_id'].loc[string]) else themes['name'].loc[string] )\n",
    "sets = sets.drop('theme_id', axis = 1)\n",
    "\n",
    "# Drop things that are not sets\n",
    "sets = sets.drop(sets[sets.theme=='Books'].index, axis=0)\n",
    "sets = sets.drop(sets[sets.theme=='Gear'].index, axis=0)\n",
    "sets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then started the process of combining the datasets. Rebrickable's dataset is based around inventories. Each inventory corresponds to a Lego product. Each inventory can contain parts, sets, or minifigures. These are separated into three csv files, linking each part/set/minifigure to its inventory ID. There is another csv that contains all inventory IDs and the set it belongs to.\n",
    "\n",
    "Inventories that contain sets are usually collections of sets, so we can drop them. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "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>set_num</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7922-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3931-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6942-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5158-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>903-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   set_num\n",
       "id        \n",
       "1   7922-1\n",
       "3   3931-1\n",
       "4   6942-1\n",
       "15  5158-1\n",
       "16   903-1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the files and drop irrelevant columns\n",
    "inventories = pd.read_csv('rebrickable/inventories.csv')\n",
    "inventories = inventories.drop('version', axis = 1)\n",
    "\n",
    "inventory_minifigs = pd.read_csv('rebrickable/inventory_minifigs.csv')\n",
    "\n",
    "minifigs = pd.read_csv('rebrickable/minifigs.csv')\n",
    "minifigs = minifigs.drop('img_url', axis=1)\n",
    "\n",
    "inventory_sets =  pd.read_csv('rebrickable/inventory_sets.csv')\n",
    "inventory_sets = inventory_sets.set_index('inventory_id')\n",
    "\n",
    "inventories = inventories.set_index('id')\n",
    "# drop collections of sets.\n",
    "inventories = inventories.drop(inventory_sets.index, errors='ignore')\n",
    "inventories.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then converted the the inventory of minifigures into lists of figure numbers and quantity pairs and then added this to `inventories`. We then merged the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "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>set_num</th>\n",
       "      <th>fig_info</th>\n",
       "      <th>name</th>\n",
       "      <th>year</th>\n",
       "      <th>num_parts</th>\n",
       "      <th>theme</th>\n",
       "      <th>subtheme</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7922-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>McDonald's Sports Set Number 6 - Orange Vest S...</td>\n",
       "      <td>2004</td>\n",
       "      <td>5</td>\n",
       "      <td>Sports</td>\n",
       "      <td>Gravity Games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3931-1</td>\n",
       "      <td>[(fig-001549, 1)]</td>\n",
       "      <td>Emma's Splash Pool</td>\n",
       "      <td>2012</td>\n",
       "      <td>43</td>\n",
       "      <td>Friends</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6942-1</td>\n",
       "      <td>[(fig-000764, 1)]</td>\n",
       "      <td>Zo Weevil</td>\n",
       "      <td>1999</td>\n",
       "      <td>20</td>\n",
       "      <td>Space</td>\n",
       "      <td>Insectoids</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5158-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T-Junction, Circle Plates</td>\n",
       "      <td>1990</td>\n",
       "      <td>2</td>\n",
       "      <td>Service Packs</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>903-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Train Wheels and Couplers</td>\n",
       "      <td>1969</td>\n",
       "      <td>10</td>\n",
       "      <td>System</td>\n",
       "      <td>Supplemental</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  set_num           fig_info  \\\n",
       "0  7922-1                NaN   \n",
       "1  3931-1  [(fig-001549, 1)]   \n",
       "2  6942-1  [(fig-000764, 1)]   \n",
       "3  5158-1                NaN   \n",
       "4   903-1                NaN   \n",
       "\n",
       "                                                name  year  num_parts  \\\n",
       "0  McDonald's Sports Set Number 6 - Orange Vest S...  2004          5   \n",
       "1                                 Emma's Splash Pool  2012         43   \n",
       "2                                          Zo Weevil  1999         20   \n",
       "3                          T-Junction, Circle Plates  1990          2   \n",
       "4                          Train Wheels and Couplers  1969         10   \n",
       "\n",
       "           theme       subtheme  \n",
       "0         Sports  Gravity Games  \n",
       "1        Friends            NaN  \n",
       "2          Space     Insectoids  \n",
       "3  Service Packs            NaN  \n",
       "4         System   Supplemental  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This will collect all minifigures in the same inventory and put them into a list of (fig_num, quantity) tuples\n",
    "minifig_dict = dict()\n",
    "\n",
    "for index, row in inventory_minifigs.iterrows():\n",
    "    if row['inventory_id'] not in minifig_dict:\n",
    "        minifig_dict[row['inventory_id']] = []\n",
    "    minifig_dict[row['inventory_id']].append((row['fig_num'], row['quantity']))\n",
    "\n",
    "inventories['fig_info'] = pd.Series(minifig_dict)\n",
    "\n",
    "# merge everything\n",
    "inventory_df = pd.merge(inventories, sets, on='set_num')\n",
    "inventory_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BrickSet Dataset #3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "64aa36ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c4d349a",
   "metadata": {},
   "source": [
    "We will import our BrickSet csv dataset and look through what our current data is showing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fb4ebcd3",
   "metadata": {},
   "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>set_number</th>\n",
       "      <th>name</th>\n",
       "      <th>theme</th>\n",
       "      <th>subtheme</th>\n",
       "      <th>year</th>\n",
       "      <th>pieces</th>\n",
       "      <th>no_of_minifigs</th>\n",
       "      <th>minifig_list</th>\n",
       "      <th>rrp</th>\n",
       "      <th>ppp</th>\n",
       "      <th>...</th>\n",
       "      <th>value_new</th>\n",
       "      <th>value_used</th>\n",
       "      <th>notes</th>\n",
       "      <th>no_owned</th>\n",
       "      <th>no_want</th>\n",
       "      <th>rating</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>num_reviews</th>\n",
       "      <th>tags</th>\n",
       "      <th>designer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8630-1</td>\n",
       "      <td>Gold Hunt</td>\n",
       "      <td>Agents</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008</td>\n",
       "      <td>352.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>['agt007', 'agt008', 'agt009']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.5c</td>\n",
       "      <td>...</td>\n",
       "      <td>~$87.35</td>\n",
       "      <td>~$22.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3182.0</td>\n",
       "      <td>1188</td>\n",
       "      <td>4.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>['Agent Fuse', 'Gold Tooth', 'Henchman', '4X4'...</td>\n",
       "      <td>['Mark Stafford']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8631-1</td>\n",
       "      <td>Jetpack Pursuit</td>\n",
       "      <td>Agents</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>['agt004', 'agt005']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.4c</td>\n",
       "      <td>...</td>\n",
       "      <td>~$36.18</td>\n",
       "      <td>~$10.11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4534.0</td>\n",
       "      <td>960</td>\n",
       "      <td>3.9</td>\n",
       "      <td>124.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>['Agent Chase', 'Saw Fist', 'Aircraft', 'Dr In...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8632-1</td>\n",
       "      <td>Swamp Raid</td>\n",
       "      <td>Agents</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008</td>\n",
       "      <td>231.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>['agt003', 'agt006']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.7c</td>\n",
       "      <td>...</td>\n",
       "      <td>~$51.25</td>\n",
       "      <td>~$17.15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3564.0</td>\n",
       "      <td>1085</td>\n",
       "      <td>3.9</td>\n",
       "      <td>106.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>['Agent Charge', 'Break Jaw', 'Boat', 'Crimina...</td>\n",
       "      <td>['Mark Stafford']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8633-1</td>\n",
       "      <td>Speedboat Rescue</td>\n",
       "      <td>Agents</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008</td>\n",
       "      <td>340.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>['agt001a', 'agt002', 'agt003']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.8c</td>\n",
       "      <td>...</td>\n",
       "      <td>~$90.56</td>\n",
       "      <td>~$35.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>1239</td>\n",
       "      <td>3.9</td>\n",
       "      <td>75.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>['Agent Chase', 'Agent Trace', 'Break Jaw', 'B...</td>\n",
       "      <td>['Raphael Pretesacque']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8634-1</td>\n",
       "      <td>Turbocar Chase</td>\n",
       "      <td>Agents</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008</td>\n",
       "      <td>498.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>['agt001', 'agt008', 'agt015']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0c</td>\n",
       "      <td>...</td>\n",
       "      <td>~$220.00</td>\n",
       "      <td>~$52.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2439.0</td>\n",
       "      <td>1433</td>\n",
       "      <td>4.3</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>['Agent Chase', 'Henchman', 'Spy Clops', 'Airc...</td>\n",
       "      <td>['Raphael Pretesacque']</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  set_number               name   theme subtheme  year  pieces  \\\n",
       "0     8630-1          Gold Hunt  Agents      NaN  2008   352.0   \n",
       "1     8631-1    Jetpack Pursuit  Agents      NaN  2008    88.0   \n",
       "2     8632-1         Swamp Raid  Agents      NaN  2008   231.0   \n",
       "3     8633-1   Speedboat Rescue  Agents      NaN  2008   340.0   \n",
       "4     8634-1     Turbocar Chase  Agents      NaN  2008   498.0   \n",
       "\n",
       "   no_of_minifigs                     minifig_list  rrp    ppp  ... value_new  \\\n",
       "0             3.0   ['agt007', 'agt008', 'agt009']  NaN   8.5c  ...   ~$87.35   \n",
       "1             2.0             ['agt004', 'agt005']  NaN  11.4c  ...   ~$36.18   \n",
       "2             2.0             ['agt003', 'agt006']  NaN   8.7c  ...   ~$51.25   \n",
       "3             3.0  ['agt001a', 'agt002', 'agt003']  NaN  11.8c  ...   ~$90.56   \n",
       "4             3.0   ['agt001', 'agt008', 'agt015']  NaN  10.0c  ...  ~$220.00   \n",
       "\n",
       "  value_used notes no_owned no_want  rating  num_ratings  num_reviews  \\\n",
       "0    ~$22.75   NaN   3182.0    1188     4.0         95.0         11.0   \n",
       "1    ~$10.11   NaN   4534.0     960     3.9        124.0         27.0   \n",
       "2    ~$17.15   NaN   3564.0    1085     3.9        106.0         18.0   \n",
       "3    ~$35.70   NaN   2490.0    1239     3.9         75.0          9.0   \n",
       "4    ~$52.70   NaN   2439.0    1433     4.3         76.0          9.0   \n",
       "\n",
       "                                                tags                 designer  \n",
       "0  ['Agent Fuse', 'Gold Tooth', 'Henchman', '4X4'...        ['Mark Stafford']  \n",
       "1  ['Agent Chase', 'Saw Fist', 'Aircraft', 'Dr In...                      NaN  \n",
       "2  ['Agent Charge', 'Break Jaw', 'Boat', 'Crimina...        ['Mark Stafford']  \n",
       "3  ['Agent Chase', 'Agent Trace', 'Break Jaw', 'B...  ['Raphael Pretesacque']  \n",
       "4  ['Agent Chase', 'Henchman', 'Spy Clops', 'Airc...  ['Raphael Pretesacque']  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brickset = pd.read_csv('https://raw.githubusercontent.com/nk0305/COGS108_Repo/main/brickset.csv')\n",
    "brickset.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a087c3e",
   "metadata": {},
   "source": [
    "We will remove unnecessary columns:'packaging', 'subtheme', 'rrp', 'ppp', 'notes'. These columns were removed because they were either ambiguous, or irrelevant for the purpose of our analysis. We will also rename each column for more clarity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6f626936",
   "metadata": {},
   "outputs": [],
   "source": [
    "brickset = brickset.drop(columns = ['packaging', 'subtheme', 'rrp', 'ppp', 'notes'])\n",
    "brickset.columns = ['Set_number', 'Name', 'Theme','Year','Pieces','No_of_minifigs','Minifig_list','Launch_and_Exit','newValue','usedValue','No_owned','No_want','Rating','Num_ratings','Num_reviews','Tags','Designer']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a273409",
   "metadata": {},
   "source": [
    "We will define a function to clean Launch_and_Exit column and apply it to the designated column.\n",
    " \n",
    "We will also separate the Launch_and_Exit column by splitting it into two new columns based on separated dates. Then we will delete the original Launch_and_Exit column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "db4105d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Define a function to clean 'Launch_and_Exit' column \n",
    "\n",
    "def clean_LnE(string):\n",
    "\n",
    "    new_string = re.sub(r'\\([^)]*\\)', '', string)\n",
    "\n",
    "    return new_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f1614639",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Apply the function to designated column \n",
    "\n",
    "brickset['Launch_and_Exit'] = brickset['Launch_and_Exit'].astype('str')\n",
    "brickset['Launch_and_Exit'] = brickset['Launch_and_Exit'].apply(clean_LnE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "09ecd69f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Separate launch and exit date \n",
    "\n",
    "for date in brickset['Launch_and_Exit']:\n",
    "  sep_string = re.split(\" - \", date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "49b823c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create two new columns based on separated dates. Then, delete original \"Launch_and_Exit\" column\n",
    "\n",
    "brickset['Launch_Date'] = sep_string[0]\n",
    "\n",
    "brickset['Exit_Date'] = sep_string[1]\n",
    "\n",
    "brickset = brickset.drop(columns = 'Launch_and_Exit')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bb47ba4",
   "metadata": {},
   "source": [
    "Lastly we will review the updated dataset and check for the new changes that we added."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cd670b72",
   "metadata": {},
   "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>Set_number</th>\n",
       "      <th>Name</th>\n",
       "      <th>Theme</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pieces</th>\n",
       "      <th>No_of_minifigs</th>\n",
       "      <th>Minifig_list</th>\n",
       "      <th>newValue</th>\n",
       "      <th>usedValue</th>\n",
       "      <th>No_owned</th>\n",
       "      <th>No_want</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Num_ratings</th>\n",
       "      <th>Num_reviews</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Designer</th>\n",
       "      <th>Launch_Date</th>\n",
       "      <th>Exit_Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8630-1</td>\n",
       "      <td>Gold Hunt</td>\n",
       "      <td>Agents</td>\n",
       "      <td>2008</td>\n",
       "      <td>352.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>['agt007', 'agt008', 'agt009']</td>\n",
       "      <td>~$87.35</td>\n",
       "      <td>~$22.75</td>\n",
       "      <td>3182.0</td>\n",
       "      <td>1188</td>\n",
       "      <td>4.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>['Agent Fuse', 'Gold Tooth', 'Henchman', '4X4'...</td>\n",
       "      <td>['Mark Stafford']</td>\n",
       "      <td>1 Jul 2002</td>\n",
       "      <td>31 Dec 2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8631-1</td>\n",
       "      <td>Jetpack Pursuit</td>\n",
       "      <td>Agents</td>\n",
       "      <td>2008</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>['agt004', 'agt005']</td>\n",
       "      <td>~$36.18</td>\n",
       "      <td>~$10.11</td>\n",
       "      <td>4534.0</td>\n",
       "      <td>960</td>\n",
       "      <td>3.9</td>\n",
       "      <td>124.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>['Agent Chase', 'Saw Fist', 'Aircraft', 'Dr In...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 Jul 2002</td>\n",
       "      <td>31 Dec 2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8632-1</td>\n",
       "      <td>Swamp Raid</td>\n",
       "      <td>Agents</td>\n",
       "      <td>2008</td>\n",
       "      <td>231.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>['agt003', 'agt006']</td>\n",
       "      <td>~$51.25</td>\n",
       "      <td>~$17.15</td>\n",
       "      <td>3564.0</td>\n",
       "      <td>1085</td>\n",
       "      <td>3.9</td>\n",
       "      <td>106.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>['Agent Charge', 'Break Jaw', 'Boat', 'Crimina...</td>\n",
       "      <td>['Mark Stafford']</td>\n",
       "      <td>1 Jul 2002</td>\n",
       "      <td>31 Dec 2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8633-1</td>\n",
       "      <td>Speedboat Rescue</td>\n",
       "      <td>Agents</td>\n",
       "      <td>2008</td>\n",
       "      <td>340.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>['agt001a', 'agt002', 'agt003']</td>\n",
       "      <td>~$90.56</td>\n",
       "      <td>~$35.70</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>1239</td>\n",
       "      <td>3.9</td>\n",
       "      <td>75.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>['Agent Chase', 'Agent Trace', 'Break Jaw', 'B...</td>\n",
       "      <td>['Raphael Pretesacque']</td>\n",
       "      <td>1 Jul 2002</td>\n",
       "      <td>31 Dec 2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8634-1</td>\n",
       "      <td>Turbocar Chase</td>\n",
       "      <td>Agents</td>\n",
       "      <td>2008</td>\n",
       "      <td>498.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>['agt001', 'agt008', 'agt015']</td>\n",
       "      <td>~$220.00</td>\n",
       "      <td>~$52.70</td>\n",
       "      <td>2439.0</td>\n",
       "      <td>1433</td>\n",
       "      <td>4.3</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>['Agent Chase', 'Henchman', 'Spy Clops', 'Airc...</td>\n",
       "      <td>['Raphael Pretesacque']</td>\n",
       "      <td>1 Jul 2002</td>\n",
       "      <td>31 Dec 2003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Set_number               Name   Theme  Year  Pieces  No_of_minifigs  \\\n",
       "0     8630-1          Gold Hunt  Agents  2008   352.0             3.0   \n",
       "1     8631-1    Jetpack Pursuit  Agents  2008    88.0             2.0   \n",
       "2     8632-1         Swamp Raid  Agents  2008   231.0             2.0   \n",
       "3     8633-1   Speedboat Rescue  Agents  2008   340.0             3.0   \n",
       "4     8634-1     Turbocar Chase  Agents  2008   498.0             3.0   \n",
       "\n",
       "                      Minifig_list  newValue usedValue  No_owned  No_want  \\\n",
       "0   ['agt007', 'agt008', 'agt009']   ~$87.35   ~$22.75    3182.0     1188   \n",
       "1             ['agt004', 'agt005']   ~$36.18   ~$10.11    4534.0      960   \n",
       "2             ['agt003', 'agt006']   ~$51.25   ~$17.15    3564.0     1085   \n",
       "3  ['agt001a', 'agt002', 'agt003']   ~$90.56   ~$35.70    2490.0     1239   \n",
       "4   ['agt001', 'agt008', 'agt015']  ~$220.00   ~$52.70    2439.0     1433   \n",
       "\n",
       "   Rating  Num_ratings  Num_reviews  \\\n",
       "0     4.0         95.0         11.0   \n",
       "1     3.9        124.0         27.0   \n",
       "2     3.9        106.0         18.0   \n",
       "3     3.9         75.0          9.0   \n",
       "4     4.3         76.0          9.0   \n",
       "\n",
       "                                                Tags                 Designer  \\\n",
       "0  ['Agent Fuse', 'Gold Tooth', 'Henchman', '4X4'...        ['Mark Stafford']   \n",
       "1  ['Agent Chase', 'Saw Fist', 'Aircraft', 'Dr In...                      NaN   \n",
       "2  ['Agent Charge', 'Break Jaw', 'Boat', 'Crimina...        ['Mark Stafford']   \n",
       "3  ['Agent Chase', 'Agent Trace', 'Break Jaw', 'B...  ['Raphael Pretesacque']   \n",
       "4  ['Agent Chase', 'Henchman', 'Spy Clops', 'Airc...  ['Raphael Pretesacque']   \n",
       "\n",
       "  Launch_Date     Exit_Date  \n",
       "0  1 Jul 2002  31 Dec 2003   \n",
       "1  1 Jul 2002  31 Dec 2003   \n",
       "2  1 Jul 2002  31 Dec 2003   \n",
       "3  1 Jul 2002  31 Dec 2003   \n",
       "4  1 Jul 2002  31 Dec 2003   "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Finally, review the updated dataset \n",
    "\n",
    "brickset.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ethics & Privacy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our project necessitates careful consideration of ethical and privacy concerns throughout the data science lifecycle, from question formulation to data collection, analysis, and dissemination of results. One significant concern is the potential for sampling bias, where data may overrepresent LEGO enthusiasts and underrepresent casual consumers. Additionally, our data is likely to exhibit age bias, as the primary audience for LEGO sets (children) may not directly contribute data, skewing our dataset towards adult perspectives. Time bias is another factor, with older sets possibly having more accumulated data than newer ones.\n",
    "\n",
    "We must also navigate privacy and ethical issues related to using third-party data, particularly regarding user-generated content on platforms like Brickset, BrickEconomy, and others. These concerns include ensuring compliance with data privacy laws and the ethical treatment of information, especially when it pertains to minors. Our approach includes anonymizing any personal data and relying on aggregated or anonymized datasets provided by third-party sites, assuming they adhere to ethical standards and privacy laws.\n",
    "\n",
    "To mitigate biases and ethical risks, we plan to incorporate a diverse range of data sources, aiming for a dataset that as accurately as possible reflects the global LEGO set market. We will also transparently communicate the limitations and potential biases of our analysis to users, highlighting that our findings may not fully represent the broader consumer base. Ensuring data privacy, especially for children, will be paramount, requiring strict adherence to legal and ethical guidelines for data handling and analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Team Expectations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our team has reviewed the COGS108 Team Policies and have set forth the following expectations to ensure the successful completion of our project:\n",
    "\n",
    "- All members should respond to messages relevant to the project within 12 hours through iMessage, acknowledging the message with a response or at minimum, a 'thumbs up'.\n",
    "- Members anticipating delays in their contributions must inform the group at least 3 days in advance, though earlier communication is encouraged.\n",
    "- We will utilize Notion for organizing our group work and scheduling, and Zoom for our meetings, unless a need for flexibility necessitates a different platform.\n",
    "- Should any group member have concerns or questions, they are encouraged to communicate openly with the group to discuss and resolve the issue."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project Timeline Proposal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is our team's specific project timeline, detailing our plan from initial discussions through to the final submission. This timeline incorporates both our collective efforts on the project and individual responsibilities for various components. Our aim is to follow this schedule closely, adjusting as necessary to ensure the timely and successful completion of our project.\n",
    "\n",
    "| Meeting Date | Meeting Time | Completed Before Meeting | Discuss at Meeting |\n",
    "|---|---|---|---|\n",
    "| 2/7 | 5 PM | Begin initial research and divide sections of the Project Proposal among team members (Shawn/Suhaib - data, Leena - Ethics/Privacy, Leila/Yvonne - RQ, Hypothesis, Background & Prior Work, Project Timeline) | Share and discuss initial research; suggest edits; set goals for the final draft of the project proposal |\n",
    "| 2/10 | 3 PM | Prepare final draft sections of the project proposal incorporating feedback from the previous meeting | Review and finalize proposal sections; submit the final proposal; plan next steps and assign responsibilities |\n",
    "| 2/17 | 3 PM | Import and wrangle data; begin drafting other project sections | Update the team on data wrangling progress; review work on other sections; adjust project plan as needed |\n",
    "| 2/24 | 3 PM | Sub-Team 1: Leila, Noah, Shawn are working with BrickSet, BrickEconomy and BrickLink datasets. Sub-Team 2: Suhaib & Yvonne: working on Rebrickable and LEGO datasets. Both sub-teams wrangle data & write-up; plan for EDA | Review and finalize data wrangling; discuss initial EDA findings; plan for comprehensive EDA and analysis |\n",
    "| 3/2 | 3 PM | Sub-teams work and finalize EDA for their datasets; cross-communicate and begin working on analysis | Finalize EDA; review analysis progress; discuss preliminary results and draft conclusions |\n",
    "| 3/9 | 3 PM | Complete full draft of project; start planning & working on the video presentation | Discuss the complete draft; identify areas for improvement; finalize analysis and conclusions |\n",
    "| 3/17 | 3 PM | Finalize all edits for project & presentation | Review final project; submit the completed project |"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}