Chicken Noodle Cube (Magic The Gathering)

This is an analysis of the Chicken Noodle Cube. This is a collection of Magic the Gathering cards I use to play with my friends. While this is a fufilling creative and social endeavor, it also presents me with a rich dataset.

I compiled the database ahead of time. See the shell scripts in the github repo.

Now, onto the analysis! cards is the main table we’re working with

import duckdb
import pandas as pd
import plotnine as pn
import seaborn as sns
from types import SimpleNamespace

cube = duckdb.connect("cube.duckdb")
cube.query("select * from cards limit 1").to_df().iloc[0]

name                       Basri, Tomorrow's Champion
cmc                                                 1
card_type           Legendary Creature - Human Knight
color                                               W
set                                               dft
collector_number                                    3
rarity                                           rare
color_category                                  White
status                                          Owned
maybeboard_ind                                  False
notes                                            None
Name: 0, dtype: object

style = SimpleNamespace()
style.cmc_scale = {
    "Black": "black",
    "Blue": "blue",
    "Colorless": "grey",
    "Green": "green",
    "Red": "red",
    "White": "white",
    "Multicolored": "yellow"
}

Mana curves:

(
    cube.query("""
    select color_category, cmc as cmc
    from cards 
    where color_category != 'Lands'
    """).to_df()
    .pipe(pn.ggplot, pn.aes(x="cmc", color="color_category")) 
    + pn.stat_ecdf()
    + pn.facet_wrap("color_category")
    + pn.scale_color_manual(style.cmc_scale)
    + pn.theme_minimal()
)

Cards I own:

cube.query("select status, count(*) as num from cards where maybeboard_ind=False group by status")

┌───────────┬───────┐
│  status   │  num  │
│  varchar  │ int64 │
├───────────┼───────┤
│ Not Owned │     5 │
│ Owned     │   367 │
└───────────┴───────┘

Keywords

(
    cube.query(""" 
    select 
        color_category
        , sum(COLUMNS(kw.* EXCLUDE (id)))
    from cards_with_info
    inner join keyword_counts as kw using(id)
    where 1=1
        and maybeboard_ind=False
        and cmc > 0
    group by color_category
    """).to_df().set_index("color_category").T
    # sort by most common
    .assign(total=lambda df: df.sum(axis=1))
    .sort_values("total", ascending=False)
    # show top 10
    .head(10).astype(int)
)

color_category	White	Black	Blue	Colorless	Red	Green	Multicolored	total
flying	8	5	15	0	4	0	11	43
cycling	6	1	8	0	5	5	0	25
kicker	5	1	4	0	4	4	0	18
raid	1	2	1	0	6	0	2	12
flash	4	0	4	0	0	2	0	10
haste	0	0	0	0	6	0	3	9
mill	0	4	2	0	0	1	2	9
trample	0	1	0	0	5	3	0	9
deathtouch	0	3	0	0	0	3	2	8
enchant	2	0	4	0	1	1	0	8

Appendix

Table descriptions

cube.query("show tables")

┌─────────────────┐
│      name       │
│     varchar     │
├─────────────────┤
│ cards           │
│ cards_with_info │
│ keyword_counts  │
│ lands           │
│ scryfall        │
│ spells          │
└─────────────────┘

cube.query("describe cards")

┌──────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│   column_name    │ column_type │  null   │   key   │ default │  extra  │
│     varchar      │   varchar   │ varchar │ varchar │ varchar │ varchar │
├──────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ name             │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ cmc              │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ card_type        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ color            │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ set              │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ collector_number │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ rarity           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ color_category   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ status           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ maybeboard_ind   │ BOOLEAN     │ YES     │ NULL    │ NULL    │ NULL    │
│ notes            │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
├──────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 11 rows                                                      6 columns │
└────────────────────────────────────────────────────────────────────────┘

cube.query("describe scryfall")

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ name        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ id          │ UUID        │ YES     │ NULL    │ NULL    │ NULL    │
│ type_line   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ oracle_text │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ power       │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ toughness   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ keywords    │ VARCHAR[]   │ YES     │ NULL    │ NULL    │ NULL    │
│ price_usd   │ FLOAT       │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘