Skip to content
Understanding the popularity of Legos
  • AI Chat
  • Code
  • Report
  • Understanding Lego sets popularity

    📖 Background

    You recently applied to work as a data analyst intern at the famous Lego Group in Denmark. As part of the job interview process, you received the following take-home assignment:

    You are asked to use the provided dataset to understand the popularity of different Lego sets and themes. The idea is to become familiarized with the data to be ready for an interview with a business stakeholder.

    💾 The data

    You received access to a database with the following tables. You can also see above a visualization of how the tables are related to each other. (source):
    inventory_parts
    • "inventory_id" - id of the inventory the part is in (as in the inventories table)
    • "part_num" - unique id for the part (as in the parts table)
    • "color_id" - id of the color
    • "quantity" - the number of copies of the part included in the set
    • "is_spare" - whether or not it is a spare part
    parts
    • "part_num" - unique id for the part (as in the inventory_parts table)
    • "name" - name of the part
    • "part_cat_id" - part category id (as in part_catagories table)
    part_categories
    • "id" - part category id (as in parts table)
    • "name" - name of the category the part belongs to
    colors
    • "id" - id of the color (as in inventory_parts table)
    • "name" - color name
    • "rgb" - rgb code of the color
    • "is_trans" - whether or not the part is transparent/translucent
    inventories
    • "id" - id of the inventory the part is in (as in the inventory_sets and inventory_parts tables)
    • "version" - version number
    • "set_num" - set number (as in sets table)
    inventory_sets
    • "inventory_id" - id of the inventory the part is in (as in the inventories table)
    • "set_num" - set number (as in sets table)
    • "quantity" - the quantity of sets included
    sets
    • "set_num" - unique set id (as in inventory_sets and inventories tables)
    • "name" - the name of the set
    • "year" - the year the set was published
    • "theme_id" - the id of the theme the set belongs to (as in themes table)
    • num-parts - the number of parts in the set
    themes
    • "id" - the id of the theme (as in the sets table)
    • "name" - the name of the theme
    • "parent_id" - the id of the larger theme, if there is one

    Acknowledgments: Rebrickable.com

    💪 Challenge

    Create a report to summarize your findings. Include:

    1. What is the average number of Lego sets released per year?
    2. What is the average number of Lego parts per year?
    3. Create a visualization for item 2.
    4. What are the 5 most popular colors used in Lego parts?
    5. [Optional] What proportion of Lego parts are transparent?
    6. [Optional] What are the 5 rarest lego bricks?
    7. Summarize your findings.

    Imports

    import pandas as pd
    import numpy as np
    import matplotlib.pyplot as plt
    import seaborn as sns
    
    sns.set_style('whitegrid')
    inventory_sets = pd.read_csv("data/inventory_sets.csv")
    inventories = pd.read_csv("data/inventories.csv")
    sets = pd.read_csv("data/sets.csv")
    inventory_parts = pd.read_csv("data/inventory_parts.csv")
    part_categories = pd.read_csv("data/part_categories.csv")
    parts = pd.read_csv("data/parts.csv")
    colors = pd.read_csv("data/colors.csv")
    decades = [1950, 1960, 1970, 1980, 1990, 2000, 2010, 2020]
    end_year = 2023

    Q1. What is the average number of Lego sets released per year?

    df = sets[['set_num', 'year']].groupby('year')['set_num'].count().reset_index()
    df.columns = ['year', 'count']
    df = df.sort_values(by = 'year', ascending = True)
    df = df[df['year'] <= end_year]
    start_year = df['year'].min()
    year_count = end_year - start_year + 1
    total_sets = df['count'].sum()
    average_sets_released = round(total_sets / year_count,1)
    print(f"In the {year_count} years since Lego began releasing sets, an average of {average_sets_released} sets have been released each year")
    plt.figure(figsize = (16, 4))
    
    df['ma'] = df['count'].rolling(window=5).mean()
    
    sns.lineplot(data = df, x = 'year', y = 'count', label='count')
    sns.lineplot(data = df, x = 'year', y = 'ma', label='5-year moving avg')
    plt.title("Lego sets released annually")
    plt.legend()
    plt.tight_layout()
    fig, axs = plt.subplots(2, 4, figsize = (16, 6), sharey=True)
    axs = axs.flatten()
    
    i = 0
    
    data = []
    for decade in decades:
        drange = [decade, decade + 9]
        tmp = df[(df['year'] >= drange[0]) & (df['year'] <= drange[1])]
        sns.lineplot(data = tmp, x = 'year', y = 'count', ax = axs[i], label = 'count')
        sns.lineplot(data = tmp, x = 'year', y = 'ma', ax = axs[i], label = '5-year moving avg')
        axs[i].set_title(f"{drange[0]}-{drange[1]}")
        axs[i].legend(loc = 'upper left')
    
        if i != 0:
            axs[i].legend().remove()
        i += 1
    
        item = {}
        item['decade'] = drange[0]
        item['avg_sets'] = tmp['count'].mean()
        data.append(item)
    fig.suptitle("Lego Sets Released by Decade")
    plt.tight_layout()
    plt.figure(figsize = (16, 6))
    sns.barplot(data = pd.DataFrame(data), x = 'decade', y = 'avg_sets')
    plt.title("Average sets released per decade")
    plt.tight_layout()

    Q2. What is the average number of Lego parts per year?

    sets_inventories = pd.merge(inventories, sets, on = 'set_num')
    sets_inventories_inventory_parts = pd.merge(inventory_parts, sets_inventories, left_on = 'inventory_id', right_on = 'id')
    df = sets_inventories_inventory_parts[['year','part_num']].drop_duplicates()
    df = df.groupby('year')['part_num'].count().reset_index()
    df.columns = ['year', 'count']
    df = df[df['year'] <= end_year]
    start_year = df['year'].min()
    year_count = end_year - start_year + 1
    total_parts = df['count'].sum()
    average_parts_released = round(total_parts / year_count,1)
    print(f"In the {year_count} years since Lego began releasing sets, an average of {average_parts_released} parts have been released each year")