3cb-data

commit 798c0844527f67d2fd97b8ae6469d478aea78a40
parent 938ce7623f93c739336c54e36a320432e2cc88e4
Author: alex wennerberg <alex@alexwennerberg.com>
Date:   Fri, 27 Sep 2024 23:42:03 -0400

stuff

Diffstat:
D
README
 | 
19
-------------------
M
getdata.py
 | 
7
+++++--
M
sql/export.sql
 | 
3
++-
M
sql/players.sql
 | 
13
++++---------

4 files changed, 11 insertions(+), 31 deletions(-)
diff --git a/README b/README
@@ -1,19 +0,0 @@
-3 Card Blind data analysis
-==========================
-
-Utilities for pulling data from 3 card blind metashape:
-
-https://sites.google.com/view/3cb-metashape/home
-
-Dependencies:
-  - python
-  - sqlite
-
-Pull all cards from mtgjson https://mtgjson.com/downloads/all-files/
-
-
-  wget https://mtgjson.com/api/v5/AtomicCards.json
-  cat AtomicCards.json  | jq -r '.data | keys[]' > allcards.txt
-
-Then:
-  python3 getdata.py
diff --git a/getdata.py b/getdata.py
@@ -2,7 +2,6 @@
 # all rounds available at https://sites.google.com/view/3cb-metashape/pairings-results/past-results?authuser=0
 # depends on requests and stdlib
 # writes to 3cmdata.csv
-# TODO upload the csv, print out the round msg
 
 import requests, os, csv, re, io, sqlite3, titlecase, string, openpyxl, urllib
 from Levenshtein import distance
@@ -23,7 +22,7 @@ def main():
         if file in cache:
             continue
         if file not in cache and file != "1LGHvTrQz2zhBjz1PhlW61ZYmwRWJyWkEHj-png7ZKdw": # misc file
-            print(f"analyzing round {n+1}")
+            print(f"analyzing round {n+1}...")
             save_round(n+1, file)
 
 # scrape site to list rounds
@@ -151,6 +150,7 @@ def best_guess(card):
             m = d
             outcard = c
     if outcard:
+        print(f"replacing {card} to {outcard}")
         return outcard
     return card
 
@@ -158,6 +158,9 @@ def best_guess(card):
 # data cleaning
 def replacement(card):
     m = {
+            "Phelps, Exuberant Swimmer (Phelia)": "Phelia, Exuberant Shepherd",
+            "Island [KLD #395]": "Island",
+            "Forest 🌲": "Forest",
             "Boseiju, Who Destroys Target Artifact, Enchantment, or Nonbasic Land (Who Endures)": "Boseiju, Who Endures",
             "Mox Pearl (The One Without Metalcraft)": "Mox Pearl",
             "Ink Mothy Nexy": "Inkmoth Nexus",
diff --git a/sql/export.sql b/sql/export.sql
@@ -1,5 +1,6 @@
 /* best-per-round */
-/* .headers on */
+/* .headers off */
+/* .mode csv */
 /* .mode list */
 /* .separator "\t" */
 
diff --git a/sql/players.sql b/sql/players.sql
@@ -7,11 +7,6 @@ select round,player, percent_rank() over (
 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
@@ -22,10 +17,10 @@ newcnt as (select player,count(1) as count from new group by 1),
 /* 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
+group_concat(printf('%.2f', rank.rank), ',') as ranks
+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)
+group by 1 order by 3 desc)
 
-select * from allpl;
+select * from allpl where cnt > 10;