commit c1ec08e486dc384113c450a3a4c91bc90b0672e7
parent d8d299dd12cad4a29d7e40317396e2a8197f1066
Author: alex wennerberg <awennerb@twitch.tv>
Date: Sun, 29 Oct 2023 15:11:10 -0400
Add bans and other data analysis
Diffstat:
5 files changed, 402 insertions(+), 76 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -9,6 +9,30 @@ from Levenshtein import distance
con = sqlite3.connect("3cb.db")
allcards = set(open('allcards.txt','r').read().splitlines())
+# cache
+def fileids_in_db():
+ return [a[0] for a in con.cursor().execute("select fileid from round").fetchall()]
+
+def bans_from_db():
+ return [a[0] for a in con.cursor().execute("select name from bans").fetchall()]
+
+def main():
+ cache = fileids_in_db()
+ for n, file in enumerate(get_round_fileids()):
+ if file in cache:
+ continue
+ if file not in cache:
+ print(f"analyzing round {n+1}")
+ save_round(n+1, file)
+
+# scrape site to list rounds
+def get_round_fileids():
+ allrounds = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results/past-results")
+ sheetre = re.compile("spreadsheets/d/(.*?)/")
+ curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
+ ids = sheetre.findall(allrounds.text + curr_round.text)
+ return ids
+
def file_to_wb(fileid):
params = {"mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}
headers = {"x-goog-api-key": os.environ.get("GOOG_KEY")}
@@ -21,14 +45,82 @@ def file_to_wb(fileid):
os.exit(1)
return openpyxl.load_workbook(io.BytesIO(res.content), data_only=True)
-# scrape site to list rounds
-def get_round_fileids():
- allrounds = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results/past-results")
- sheetre = re.compile("spreadsheets/d/(.*?)/")
- curr_round = requests.get("https://sites.google.com/view/3cb-metashape/pairings-results")
- ids = sheetre.findall(curr_round.text)
- return ids
+def update_bans():
+ sheet = file_to_wb("1NZuROOCctbq4p4-CAHE-jOC0675QQjuqVnUqdXFCVD8")["banlist"]
+ cards = [row[1].value for row in [*sheet.rows][3:92]]
+ cur = con.cursor()
+ cur.execute("drop table if exists ban")
+ cur.execute("create table ban (name text)")
+ for card in cards:
+ if card:
+ cur.execute("insert into ban values (?)", (clean_card(card),))
+ con.commit()
+
+# not really normalized well
+schema = ["""
+create table if not exists round (
+ id integer primary key,
+ fileid text not null
+);
+""",
+"""
+create table if not exists card (
+ round integer not null,
+ player varchar not null,
+ name text
+);""","""
+create table if not exists match (
+ round integer,
+ group_name text,
+ player text,
+ opp_player text,
+ score integer
+);"""]
+
+def save_sheet(cur, sheet, n, final):
+ rows = [[cell.value for cell in row if cell.value != None] for row in sheet.rows]
+ group = ""
+ i = 0
+ while i < len(rows):
+ row = rows[i]
+ i += 1
+ if len(row) < 3:
+ continue
+ group = row[0].partition(" ")[2]
+ if final:
+ group = "final"
+ try:
+ start = row.index("VS") + 1
+ end = row.index("Score")
+ except ValueError:
+ start = 2
+ end = len(row)
+ players = row[start:end]
+ for _ in range(len(players)):
+ row = rows[i]
+ i += 1
+ player = row[0]
+ cards = row[1].split("\n")
+ for card in cards:
+ if not final: #already done
+ cur.execute("insert into card values (?,?,?);", (n, player, clean_card(card)))
+ for o, s in enumerate(row[start:end]):
+ cur.execute("insert into match values (?, ?, ?, ?, ?);",
+ (n, group, player,players[o],s));
+def save_round(n, fileid):
+ wb = file_to_wb(fileid)
+ try:
+ groups = wb["Groups"]
+ except KeyError:
+ groups = wb["Group"]
+ cur = con.cursor()
+ save_sheet(cur, groups, n, False)
+ if len(wb._sheets) > 1:
+ final = wb["Final Group"]
+ save_sheet(cur, final, n, True)
+ cur.execute("insert into round values (?, ?);", (n, fileid));
+ con.commit()
# fixing some data issues
def best_guess(card):
m = 6
@@ -65,7 +157,7 @@ def replacement(card):
"Dark Ritual (BTD 21)": "Dark Ritual",
"Swamp (BRB 18)": "Swamp",
"Monty Python and the Holy Grail Black Knight (Oathsworn Knight)": "Oathsworn Knight",
- "Red Sun's Zenith (Again) ": "Red Sun's Zenith",
+ "Red Sun's Zenith (Again)": "Red Sun's Zenith",
"Dwarven Hold (Again)": "Dwarven Hold",
}
if card in m:
@@ -81,76 +173,9 @@ def clean_card(card_name):
return best_guess(clean)
return clean
-# not really normalized well
-schema = ["""
-create table if not exists round (
- id integer primary key,
- fileid text not null
-);
-""",
-"""
-create table if not exists card (
- round integer not null,
- player varchar not null,
- name text
-);""","""
-create table if not exists match (
- round integer,
- group_name text,
- player text,
- opp_player text,
- score integer
-);"""]
-
-def save_sheet(cur, sheet, n):
- rows = [[cell.value for cell in row if cell.value != None] for row in sheet.rows]
- group = ""
- i = 0
- while i < len(rows):
- row = rows[i]
- i += 1
- if len(row) == 0:
- continue
- group = row[0].partition(" ")[2]
- players = row[2:-1]
- print("analysinzg group", players)
- for _ in range(len(players)-1):
- row = rows[i]
- i += 1
- print(row)
- player = row[0]
- cards = row[1].split("\n")
- for card in cards:
- cur.execute("insert into card values (?,?,?);", (n, player, clean_card(card)))
- for o, s in enumerate(row[2:-1]):
- cur.execute("insert into match values (?, ?, ?, ?, ?);",
- (n, group, player,players[o],s));
-
-def save_round(n, fileid):
- wb = file_to_wb(fileid)
- groups = wb["Groups"]
- cur = con.cursor()
- save_sheet(cur, groups, n)
- if len(wb) > 1:
- final = wb["Final Group"]
- save_sheet(cur, final, n)
- cur.execute("insert into round values (?, ?);", (n, fileid));
- con.commit()
-
-# cache
-def fileids_in_db():
- return con.cursor().execute("select fileid from round").fetchall()
-
-def main():
- cache = fileids_in_db()
- for n, file in enumerate(get_round_fileids()):
- n = n +len(cache)
- if file not in cache:
- print(f"analyzing round {n+1}")
- save_round(n+1, file)
-
if __name__ == "__main__":
for table in schema:
con.execute(table)
+ update_bans()
main()
diff --git a/requirements.txt b/requirements.txt
@@ -1,3 +1,4 @@
requests
titlecase
openpyxl
+Levenshtein
diff --git a/sql/export.sql b/sql/export.sql
@@ -0,0 +1,7 @@
+select match.round,group_name,match.player,deck,sum(score) as score
+from match
+join (select round, player, group_concat(name, ';') as deck
+from (select * from card order by round,player,name)
+group by 1,2) as deck
+on deck.round = match.round and deck.player = match.player
+group by 1,2,3
diff --git a/sql/topdecks.sql b/sql/topdecks.sql
@@ -0,0 +1,17 @@
+with deck as (select round, player, group_concat(name, ';') as deck
+from (
+ select round,player,card.name from card
+ left join ban on card.name = ban.name
+ where ban.name is null
+ order by round,player,card.name
+)
+group by 1,2
+having count(name) == 3
+)
+select deck.deck, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
+deck
+join match on deck.round = match.round and deck.player = match.player
+join deck d2 on deck.round = d2.round and match.opp_player = d2.player where d2.player is not null
+group by 1
+having count(1) > 10
+order by 2 desc;
diff --git a/sql/workbook.md b/sql/workbook.md
@@ -0,0 +1,276 @@
+### Most popular cards (top 50)
+```sql
+select name,count(1) from card group by 1 order by 2 desc limit 50;
+```
+```
++-------------------------+----------+
+| name | count(1) |
++-------------------------+----------+
+| Black Lotus | 104 |
+| Chancellor of the Forge | 74 |
+| City of Traitors | 73 |
+| Chancellor of the Annex | 70 |
+| Mox Pearl | 64 |
+| Memnite | 64 |
+| Thassa's Oracle | 50 |
+| Wasteland | 44 |
+| Swamp | 43 |
+| Icatian Store | 42 |
+| Mox Sapphire | 41 |
+| Lion's Eye Diamond | 39 |
+| Karakas | 39 |
+| Mental Misstep | 34 |
+| Hickory Woodlot | 34 |
+| Leyline of Anticipation | 33 |
+| Force of Will | 30 |
+| Crystal Vein | 30 |
+| Mana Crypt | 29 |
+| Cavern of Souls | 29 |
+| Emrakul, the Aeons Torn | 28 |
+| Dryad Arbor | 28 |
+| Channel | 28 |
+| Dwarven Hold | 27 |
+| Crashing Footfalls | 27 |
+| Chronomaton | 27 |
+| Mountain | 26 |
+| Lotus Bloom | 26 |
+| Mishra's Workshop | 24 |
+| Mishra's Factory | 24 |
+| Forest | 24 |
+| Strip Mine | 23 |
+| Death's Shadow | 23 |
+| Student of Warfare | 22 |
+| Saprazzan Cove | 22 |
+| Force of Negation | 22 |
+| Plains | 21 |
+| Hexdrinker | 21 |
+| Thoughtseize | 19 |
+| Leyline of Singularity | 19 |
+| Elite Spellbinder | 19 |
+| Chalice of the Void | 19 |
+| Blackmail | 19 |
+| Undiscovered Paradise | 18 |
+| Steel Overseer | 18 |
+| Shadowgrange Archfiend | 18 |
+| Sand Silos | 18 |
+| Cenn's Tactician | 18 |
+| Burning Inquiry | 18 |
+| The Rack | 17 |
++-------------------------+----------+
+```
+### Best decks, played in at least 30 games
+```sql
+select deck, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
+(select round, player, group_concat(name, ';') as deck
+from (select * from card order by round,player,name)
+group by 1,2) as deck
+join match on deck.round = match.round and deck.player = match.player
+group by 1
+having count(1) > 30
+order by 2 desc;
+```
+```
++------------------------------------------------------------+----------+----------+
+| deck | avgscore | count(1) |
++------------------------------------------------------------+----------+----------+
+| Cenn's Tactician;Karakas;Leyline of Singularity | 4.77 | 36 |
+| Crystal Vein;Mox Emerald;Words of Wilding | 4.31 | 35 |
+| Isochron Scepter;Mana Crypt;Verdant Command | 3.93 | 32 |
+| Death's Shadow;Tarnished Citadel;Thoughtseize | 3.86 | 83 |
+| Mana Crypt;Phyrexian Dreadnought;The Mycosynth Gardens | 3.74 | 35 |
+| Mox Pearl;Rishadan Port;Skrelv's Hive | 3.7 | 54 |
+| Chancellor of the Annex;Chancellor of the Forge;Strip Mine | 3.66 | 62 |
+| Chancellor of the Annex;Icatian Store;Strip Mine | 3.44 | 79 |
+| Cenn's Tactician;Curse of Silence;Plains | 3.44 | 54 |
+| City of Traitors;Magus of the Moon;Mox Ruby | 3.4 | 47 |
+| Black Lotus;Black Lotus;Minsc & Boo, Timeless Heroes | 3.4 | 52 |
+| Crashing Footfalls;Dismember;Forest | 3.37 | 45 |
+| Disrupting Shoal;Evermind;Memnite | 3.3 | 36 |
+| Autonomous Assembler;Mishra's Factory;Mox Pearl | 3.25 | 36 |
+| Chalice of the Void;City of Traitors;Hangarback Walker | 3.2 | 64 |
+| Emrakul, the Aeons Torn;Lion's Eye Diamond;Shelldock Isle | 3.17 | 93 |
+| Chancellor of the Tangle;Crashing Footfalls;Force of Vigor | 3.17 | 67 |
+| Mana Crypt;Trinisphere;Urza's Saga | 3.16 | 36 |
+| Force of Despair;Memnite;Unmask | 3.14 | 49 |
+| Barren Glory;Lotus Bloom;Lotus Bloom | 3.14 | 85 |
+| Black Lotus;Black Lotus;Steel Seraph | 3.13 | 46 |
+| Retrofitter Foundry;Sol Ring;Wasteland | 3.11 | 69 |
+| Cenn's Tactician;Concealed Courtyard;Thoughtseize | 3.11 | 36 |
+| Island;Laboratory Maniac;Mana Crypt | 3.08 | 35 |
+| Dark Depths;Urborg, Tomb of Yawgmoth;Vampire Hexmage | 3.05 | 73 |
++------------------------------------------------------------+----------+----------+
+```
+### Best decks, played in at least 30 games, filter bans
+
+### Avg card scores, played in at least 100 games
+
+```sql
+select name, cast(avg(score) * 100 as int) / 100.0 as score, count(1) from card
+join match on card.round = match.round and card.player = match.player
+group by 1
+having count(1) > 100
+order by 2 desc
+```
+
+```
++-------------------------------------+-------+----------+
+| name | score | count(1) |
++-------------------------------------+-------+----------+
+| Wild Magic Surge | 3.63 | 102 |
+| Mayor of Avabruck // Howlpack Alpha | 3.58 | 116 |
+| Ratchet Bomb | 3.54 | 135 |
+| Rishadan Port | 3.41 | 217 |
+| Barren Glory | 3.4 | 198 |
+| Curse of Silence | 3.39 | 190 |
+| Minsc & Boo, Timeless Heroes | 3.37 | 131 |
+| Thoughtseize | 3.35 | 515 |
+| Cenn's Tactician | 3.34 | 378 |
+| Strip Mine | 3.28 | 398 |
+| Retrofitter Foundry | 3.28 | 285 |
+| The Rack | 3.26 | 387 |
+| Tarnished Citadel | 3.26 | 336 |
+| Stromkirk Noble | 3.24 | 254 |
+| Steel Overseer | 3.21 | 464 |
+| Luminarch Aspirant | 3.19 | 121 |
+| Gisa's Bidding | 3.19 | 168 |
+| Angel of Sanctions | 3.19 | 151 |
+| Phyrexian Dreadnought | 3.16 | 139 |
+| Skrelv's Hive | 3.15 | 164 |
+| Unmask | 3.14 | 122 |
+| Force of Despair | 3.11 | 185 |
+| Chalice of the Void | 3.11 | 335 |
+| Dark Depths | 3.1 | 187 |
+| Sandstone Needle | 3.09 | 166 |
+| Sol Ring | 3.08 | 382 |
+| Disrupting Shoal | 3.08 | 149 |
+| Death's Shadow | 3.08 | 517 |
+| Mox Emerald | 3.07 | 248 |
+| Boseiju, Who Endures | 3.07 | 198 |
+| Vampire Hexmage | 3.06 | 123 |
+| Solitude | 3.05 | 130 |
+| Noxious Revival | 3.05 | 187 |
+| Chancellor of the Annex | 3.04 | 1005 |
+| Bayou | 3.04 | 120 |
+| Isochron Scepter | 3.03 | 111 |
+| Mana Crypt | 3.02 | 634 |
+| Lotus Bloom | 3.02 | 601 |
+| Hangarback Walker | 3.02 | 433 |
+| Show and Tell | 3.01 | 186 |
+| Overgrown Tomb | 3.0 | 177 |
+| Magus of the Moon | 3.0 | 244 |
+| Daze | 3.0 | 120 |
+| Crashing Footfalls | 2.98 | 662 |
+| Shrieking Affliction | 2.97 | 113 |
+| Anointed Peacekeeper | 2.97 | 174 |
+| Sheltered Valley | 2.95 | 133 |
+| Grief | 2.95 | 172 |
+| Crystal Vein | 2.95 | 568 |
+| Thassa's Oracle | 2.94 | 742 |
+| Blackmail | 2.94 | 386 |
+| Inkmoth Nexus | 2.93 | 298 |
+| Tropical Island | 2.92 | 194 |
+| Funeral Charm | 2.92 | 118 |
+| Mishra's Factory | 2.9 | 508 |
+| Leyline of Singularity | 2.9 | 340 |
+| City of Traitors | 2.9 | 1413 |
+| Cavern of Souls | 2.9 | 460 |
+| Alpine Moon | 2.9 | 275 |
+| Mind Swords | 2.89 | 283 |
+| Force of Vigor | 2.89 | 144 |
+| Dismember | 2.89 | 157 |
+| Trinisphere | 2.88 | 194 |
+| Autonomous Assembler | 2.88 | 233 |
+| Snapback | 2.87 | 213 |
+| Piracy Charm | 2.87 | 110 |
+| Phyrexian Revoker | 2.87 | 107 |
+| Urza's Saga | 2.86 | 304 |
+| Ulamog, the Infinite Gyre | 2.86 | 301 |
+| Swarm Shambler | 2.86 | 303 |
+| Shelldock Isle | 2.86 | 156 |
+| Oboro, Palace in the Clouds | 2.86 | 264 |
+| Dwarven Hold | 2.85 | 405 |
+| Mox Pearl | 2.83 | 1512 |
+| Undiscovered Paradise | 2.82 | 462 |
+| Mana Vault | 2.82 | 168 |
+| Swamp | 2.81 | 795 |
+| Oko, Thief of Crowns | 2.81 | 315 |
+| Interplanar Beacon | 2.8 | 237 |
+| Icatian Store | 2.8 | 757 |
+| Urborg, Tomb of Yawgmoth | 2.79 | 143 |
+| Sand Silos | 2.79 | 340 |
+| Blooming Marsh | 2.79 | 260 |
+| Mutavault | 2.78 | 121 |
+| Meddling Mage | 2.78 | 144 |
+| Cabal Therapy | 2.78 | 296 |
+| Flash | 2.77 | 134 |
+| Cathedral of War | 2.77 | 203 |
+| The Tabernacle at Pendrell Vale | 2.76 | 123 |
+| Dark Ritual | 2.75 | 182 |
+| Mox Jet | 2.74 | 322 |
+| Hexdrinker | 2.74 | 513 |
+| Saprazzan Skerry | 2.73 | 215 |
+| Laboratory Maniac | 2.73 | 167 |
+| Emrakul, the Aeons Torn | 2.73 | 492 |
+| Island Sanctuary | 2.72 | 110 |
+| Student of Warfare | 2.71 | 510 |
+| Mox Sapphire | 2.7 | 775 |
+| Black Lotus | 2.7 | 1419 |
+| Balance | 2.7 | 271 |
+| Force of Will | 2.69 | 354 |
+| Burning Inquiry | 2.69 | 314 |
+| Memnite | 2.68 | 1139 |
+| Lion's Eye Diamond | 2.68 | 757 |
+| Chancellor of the Forge | 2.68 | 1172 |
+| Sphere of Resistance | 2.67 | 115 |
+| Island | 2.67 | 213 |
+| Chancellor of the Tangle | 2.67 | 256 |
+| Fury | 2.66 | 271 |
+| Chronomaton | 2.66 | 552 |
+| Misdirection | 2.65 | 127 |
+| Mind Rake | 2.65 | 138 |
+| Mishra's Workshop | 2.63 | 384 |
+| Maze of Ith | 2.63 | 258 |
+| Fountain of Cho | 2.63 | 206 |
+| Simian Spirit Guide | 2.62 | 283 |
+| Young Wolf | 2.6 | 170 |
+| Mountain | 2.6 | 421 |
+| Karakas | 2.6 | 580 |
+| Nether Spirit | 2.59 | 321 |
+| Hickory Woodlot | 2.59 | 612 |
+| Ghost Quarter | 2.59 | 257 |
+| Force of Negation | 2.59 | 361 |
+| Elite Spellbinder | 2.59 | 386 |
+| Wasteland | 2.58 | 847 |
+| Dryad Arbor | 2.56 | 364 |
+| Dunes of the Dead | 2.55 | 173 |
+| Underground River | 2.53 | 105 |
+| Plains | 2.52 | 376 |
+| Energy Field | 2.52 | 238 |
+| Saprazzan Cove | 2.51 | 266 |
+| Jace, Wielder of Mysteries | 2.5 | 124 |
+| Bottomless Vault | 2.49 | 153 |
+| Nullhide Ferox | 2.45 | 232 |
+| Remote Farm | 2.44 | 157 |
+| Forest | 2.44 | 418 |
+| Darksteel Citadel | 2.44 | 181 |
+| Karn Liberated | 2.42 | 106 |
+| Shadowgrange Archfiend | 2.4 | 367 |
+| Mox Ruby | 2.38 | 110 |
+| Channel | 2.38 | 435 |
+| Sea's Claim | 2.37 | 182 |
+| Old-Growth Dryads | 2.34 | 330 |
+| Smallpox | 2.33 | 104 |
+| Hollow Trees | 2.32 | 246 |
+| Thalia, Guardian of Thraben | 2.3 | 156 |
+| Lavinia, Azorius Renegade | 2.29 | 127 |
+| Blazing Rootwalla | 2.29 | 163 |
+| Treetop Village | 2.27 | 120 |
+| Swords to Plowshares | 2.27 | 159 |
+| Pyrokinesis | 2.22 | 116 |
+| Mental Misstep | 2.13 | 616 |
+| Leyline of Anticipation | 2.09 | 413 |
+| Bitterblossom | 2.06 | 149 |
+| Peat Bog | 1.8 | 139 |
+| Leyline of Sanctity | 1.47 | 183 |
++-------------------------------------+-------+----------+
+```
