3cb-data

commit c4d0d0e62277de23b36edf51553d66e8c253322e
parent d456f40056d8c6464fab8352fa0e8e244595b933
Author: alex wennerberg <alex@alexwennerberg.com>
Date:   Tue, 11 Jun 2024 09:01:38 -0400

some stuff

Diffstat:
M
getdata.py
 | 
10
+++++++++-
M
sql/delete-last.sql
 | 
6
+++---
A
sql/players.sql
 | 
31
+++++++++++++++++++++++++++++++

3 files changed, 43 insertions(+), 4 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -32,6 +32,7 @@ def get_round_fileids():
     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)
+    ids.append("1aG0aDWAvV0DRPywsbFl_agOZ5-Zv4ULk0oXxH5E2Gr4") # hack
     return ids
 
 def file_to_wb(fileid):
@@ -115,7 +116,7 @@ def save_sheet(cur, sheet, n, final):
             if not final: #already done
                 cur.execute("insert into deck values (?,?,?,?,?);", (n, player, clean_card(cards[0]), clean_card(cards[1]), clean_card(cards[2])))
             for o, s in enumerate(row[start:end]):
-                if s == "?":
+                if s == "?" or s == "":
                     s = 0
                 cur.execute("insert into match values (?, ?, ?, ?, ?);", 
                             (n, group, player,players[o],int(s)));
@@ -152,8 +153,15 @@ def best_guess(card):
 # data cleaning
 def replacement(card):
     m = {
+            "Mox Pearl (The One Without Metalcraft)": "Mox Pearl",
+            "Ink Mothy Nexy": "Inkmoth Nexus",
+            "Phelia, Tail-Wagging Shepherd": "Phelia, Exuberant Shepherd",
+            "Dreams of Oil and Steel": "Dreams of Steel and Oil",
+            "Lion's Eye Diamond                                Cheatyface": "Lion's Eye Diamond",
+            "Mayor of Avarbruck": "Mayor of Avabruck // Howlpack Alpha",
             "Thallid Oh Yeah": "Thallid",
             "Forest!!!!!!": "Forest",
+            "Forest (Tempest #348)": "Forest",
             "Bayou - Not Legal": "Bayou",
             "Bottomless Depths": "Bottomless Vault",
             "Gargadon (Neither Greater Nor Lesser)": "Gargadon",
diff --git a/sql/delete-last.sql b/sql/delete-last.sql
@@ -1,3 +1,3 @@
-delete from round where id > 40;
-delete from deck where round > 40;
-delete from match where round  > 40;
+delete from round where id > 62;
+delete from deck where round > 62;
+delete from match where round  > 62;
diff --git a/sql/players.sql b/sql/players.sql
@@ -0,0 +1,31 @@
+/* TODO slightly off -- jsut sums finals and original together */
+with rank as (
+select round,player, percent_rank() over (
+  partition by round
+  order by sum(score) 
+) as rank from match
+group by 1,2 order by 1
+),
+
+card as (
+select round,player,card1 as card from deck
+union select round,player,card2 as card from deck
+union select round,player,card3 as card from deck),
+
+new as (
+select min(card.round) as rnd, card, card.player
+from match join card on match.round = card.round and match.player = card.player
+group by 2 order by 1),
+
+newcnt as (select player,count(1) as count from new group by 1),
+/* select * from newcnt; */
+/* TODO -- median percentile, not mean */
+allpl as (
+select deck.player, count(1) as cnt, printf('%.2f', avg(rank.rank)) as avg_rank,
+  printf('%.2f', (newcnt.count / 3.0) / count(1)) as avg_newness from deck
+join rank on deck.round = rank.round 
+and deck.player = rank.player
+join newcnt on deck.player = newcnt.player
+group by 1 order by 4 desc)
+
+select * from allpl;