WCA Statistics Tools

Stefan · Aug 27, 2014

I improved my tool that I've been using to prepare WCA statistics for forum posts such as [post=1011290]Female Podiums[/post] or [post=1009075]FMC statistics per year[/post] and published it now:
https://github.com/pochmann/wca-statistics-tools

It automatically keeps a local database up to date (checking/downloading/importing the newest WCA export) and turns the queries you write in *.in textfiles into *.out textfiles that contain the results nicely formatted and documented, ready to copy&paste into the forum.

Would make me happy if it's useful for others as well and I'd appreciate feedback and suggestions for improvement (both for the tool as well as for its documentation).

Edit: I renamed it to "WCA Statistics Tools".

Lucas Garron · Aug 27, 2014

Rename it to something less confusing? I thought this was about/for the WCA forum at first.

kinch2002 · Aug 27, 2014

Nice idea!
I'll try to set it up for myself later...I'm not too good at this sort of thing, I'm used to being put in front of a computer with ms sql server and the databases already set up for me

But assuming I can set it up I'll be keen to use this.

Laura O · Aug 27, 2014

Nice tool, thank you for sharing.

I have my database running in MAMP. I will adapt your script to work with this setup later on.

Stefan · Aug 27, 2014

Lucas Garron said:
Rename it to something less confusing? I thought this was about/for the WCA forum at first.

Yeah I'm not happy with the name, either, and already considered renaming it. How about "WCA statistics forum tool" or "WCA-statistics forum-tool"? I'm also open for suggestions.

(I hope to eventually get around to make it a module useful for other programs and websites and then drop the "forum" from the name. And maybe add general cubing stuff and then change "WCA" into "Cubing".)

MaeLSTRoM · Aug 27, 2014

Stefan said:
Yeah I'm not happy with the name, either, and already considered renaming it. How about "WCA statistics forum tool" or "WCA-statistics forum-tool"? I'm also open for suggestions.

WCA-Statistics forum post tool?

Anyway seems pretty cool, maybe I'll actually start doing some stats of my own now heh. (instead of just bugging Kinch2002 until he does it for me

)

Lucas Garron · Aug 27, 2014

Stefan said:
How about "WCA statistics forum tool" or "WCA-statistics forum-tool"? I'm also open for suggestions.

Sounds great to me.

cubizh · Aug 27, 2014

Thanks for sharing!

A while ago I did something similar to automatically download and apply the new export only if a new version is available, checking first, to reduce bandwidth, but your code seems a lot better.
I ended up not really using it as I don't really use the sql data.

A slight improvement to your program would be to directly use the defined variables on top, something like:

Code:

 commandline='mysql --default-character-set=utf8 --host='+ host + ' --user=' + user + " --password=' + password + ' ' + database + '< WCA_export.sql'
 subprocess.call(commandline, shell=True)

Stefan · Aug 27, 2014

cubizh said:
automatically download and apply the new export only if a new version is available, checking first, to reduce bandwidth

Actually I don't worry about bandwidth so much, I just don't want to spend 40 seconds on a useless download and import.

cubizh said:
I ended up not really using it as I don't really use the sql data.

Do you use it for tsv?

For the longest time, my auto-updater was for tsv (part of another project). For sql and this tool I went a cumbersome way until I got fed up with that last night. I intend to post my tsv version as well, along with more helper functions.

cubizh said:
A slight improvement to your program would be to directly use the defined variables on top, something like:

Ouch, yeah. Being able to reuse those values there was the point of putting them into variables, and then I forgot. Thanks.

cubizh · Aug 28, 2014

Stefan said:
Do you use it for tsv?

I used to, but since I don't do stats that often anymore, I end up just doing it manually most of the times.

Stefan · Sep 21, 2014

I renamed and improved it a bit. And here's a better demonstration of what it does:

You write a query file like this (current single record and record holders for each event):

Code:

SELECT eventId Event,
       best 'Record[R]',
       group_concat(personId separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
GROUP BY Event
ORDER BY rank;

My tool first makes sure your database is up to date (automatically updating if necessary) and then produces an output file like this:

Code:

[noparse][SPOILER="World Records"]Using data from [url=https://www.worldcubeassociation.org/results/misc/export.html]WCA_export473_20140916[/url] and Stefan's [url=https://github.com/pochmann/wca-statistics-tools/]WCA Statistics Tools[/url].

[TABLE="class:grid,align:left"]
[TR][TD][B]Event[/B][/TD][TD][B]Record[/B][/TD][TD][B]Record Holders[/B][/TD][/TR]
[TR][TD]Rubik's Cube[/TD][TD="align:right"]5.55[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2007VALK01]Mats Valk[/url][/TD][/TR]
[TR][TD]4x4 Cube[/TD][TD="align:right"]21.97[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2010WEYE02]Sebastian Weyer[/url][/TD][/TR]
[TR][TD]5x5 Cube[/TD][TD="align:right"]48.42[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009ZEMD01]Feliks Zemdegs[/url][/TD][/TR]
[TR][TD]2x2 Cube[/TD][TD="align:right"]0.69[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009KASE02]Christian Kaserer[/url][/TD][/TR]
[TR][TD]3x3 blindfolded[/TD][TD="align:right"]23.19[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2011KOWA01]Marcin Kowalczyk[/url][/TD][/TR]
[TR][TD]3x3 one-handed[/TD][TD="align:right"]9.03[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009ZEMD01]Feliks Zemdegs[/url][/TD][/TR]
[TR][TD]3x3 fewest moves[/TD][TD="align:right"]20[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009OKAY01]Tomoaki Okayama (岡山友昭)[/url][/TD][/TR]
[TR][TD]3x3 with feet[/TD][TD="align:right"]25.14[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2012CAMP03]Gabriel Pereira Campanha[/url][/TD][/TR]
[TR][TD]Megaminx[/TD][TD="align:right"]39.57[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2010CORM02]Louis Cormier[/url][/TD][/TR]
[TR][TD]Pyraminx[/TD][TD="align:right"]1.36[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2008ANDE02]Oscar Roth Andersen[/url][/TD][/TR]
[TR][TD]Square-1[/TD][TD="align:right"]6.96[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2008LIBI01]Bingliang Li (李炳良)[/url][/TD][/TR]
[TR][TD]Rubik's Clock[/TD][TD="align:right"]5.27[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009WANG19]Sam Zhixiao Wang (王志骁)[/url][/TD][/TR]
[TR][TD]Skewb[/TD][TD="align:right"]2.19[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009HARN01]Brandon Harnish[/url], [url=https://www.worldcubeassociation.org/results/p.php?i=2013KOSK01]Jonatan Kłosko[/url][/TD][/TR]
[TR][TD]6x6 Cube[/TD][TD="align:right"]1:40.86[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009HAYS01]Kevin Hays[/url][/TD][/TR]
[TR][TD]7x7 Cube[/TD][TD="align:right"]2:39.41[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2010CHEN20]Lin Chen (陈霖)[/url][/TD][/TR]
[TR][TD]4x4 blindfolded[/TD][TD="align:right"]2:25.27[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2012FROS01]Oliver Frost[/url][/TD][/TR]
[TR][TD]5x5 blindfolded[/TD][TD="align:right"]5:39.10[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2012JALO01]Grzegorz Jałocha[/url][/TD][/TR]
[TR][TD]3x3 multi blind[/TD][TD="align:right"]41/41 ([SIZE=1]54:14[/SIZE])[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2011KOWA01]Marcin Kowalczyk[/url][/TD][/TR]
[TR][TD]Rubik's Magic[/TD][TD="align:right"]0.69[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009WANG13]Yuxuan Wang (王宇轩)[/url][/TD][/TR]
[TR][TD]Master Magic[/TD][TD="align:right"]1.66[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009WANG13]Yuxuan Wang (王宇轩)[/url][/TD][/TR]
[TR][TD]3x3 multi blind old[/TD][TD="align:right"]24/24 ([SIZE=1]2:15:57[/SIZE])[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2007HABE01]Tim Habermaas[/url][/TD][/TR]
[/TABLE]

[SPOILER="SQL"][CODE]SELECT eventId Event,
       best 'Record[R]',
       group_concat(personId separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
GROUP BY Event
ORDER BY rank;

[/SPOILER][/noparse][/code][/spoiler]

You can then copy&paste that output into your forum post, and it will look like this:

Using data from WCA_export473_20140916 and Stefan's WCA Statistics Tools.

[TABLE="class:grid,align:left"]
[TR][TD]Event[/TD][TD]Record[/TD][TD]Record Holders[/TD][/TR]
[TR][TD]Rubik's Cube[/TD][TD="align:right"]5.55[/TD][TD]Mats Valk[/TD][/TR]
[TR][TD]4x4 Cube[/TD][TD="align:right"]21.97[/TD][TD]Sebastian Weyer[/TD][/TR]
[TR][TD]5x5 Cube[/TD][TD="align:right"]48.42[/TD][TD]Feliks Zemdegs[/TD][/TR]
[TR][TD]2x2 Cube[/TD][TD="align:right"]0.69[/TD][TD]Christian Kaserer[/TD][/TR]
[TR][TD]3x3 blindfolded[/TD][TD="align:right"]23.19[/TD][TD]Marcin Kowalczyk[/TD][/TR]
[TR][TD]3x3 one-handed[/TD][TD="align:right"]9.03[/TD][TD]Feliks Zemdegs[/TD][/TR]
[TR][TD]3x3 fewest moves[/TD][TD="align:right"]20[/TD][TD]Tomoaki Okayama (岡山友昭)[/TD][/TR]
[TR][TD]3x3 with feet[/TD][TD="align:right"]25.14[/TD][TD]Gabriel Pereira Campanha[/TD][/TR]
[TR][TD]Megaminx[/TD][TD="align:right"]39.57[/TD][TD]Louis Cormier[/TD][/TR]
[TR][TD]Pyraminx[/TD][TD="align:right"]1.36[/TD][TD]Oscar Roth Andersen[/TD][/TR]
[TR][TD]Square-1[/TD][TD="align:right"]6.96[/TD][TD]Bingliang Li (李炳良)[/TD][/TR]
[TR][TD]Rubik's Clock[/TD][TD="align:right"]5.27[/TD][TD]Sam Zhixiao Wang (王志骁)[/TD][/TR]
[TR][TD]Skewb[/TD][TD="align:right"]2.19[/TD][TD]Brandon Harnish, Jonatan Kłosko[/TD][/TR]
[TR][TD]6x6 Cube[/TD][TD="align:right"]1:40.86[/TD][TD]Kevin Hays[/TD][/TR]
[TR][TD]7x7 Cube[/TD][TD="align:right"]2:39.41[/TD][TD]Lin Chen (陈霖)[/TD][/TR]
[TR][TD]4x4 blindfolded[/TD][TD="align:right"]2:25.27[/TD][TD]Oliver Frost[/TD][/TR]
[TR][TD]5x5 blindfolded[/TD][TD="align:right"]5:39.10[/TD][TD]Grzegorz Jałocha[/TD][/TR]
[TR][TD]3x3 multi blind[/TD][TD="align:right"]41/41 (54:14)[/TD][TD]Marcin Kowalczyk[/TD][/TR]
[TR][TD]Rubik's Magic[/TD][TD="align:right"]0.69[/TD][TD]Yuxuan Wang (王宇轩)[/TD][/TR]
[TR][TD]Master Magic[/TD][TD="align:right"]1.66[/TD][TD]Yuxuan Wang (王宇轩)[/TD][/TR]
[TR][TD]3x3 multi blind old[/TD][TD="align:right"]24/24 (2:15:57)[/TD][TD]Tim Habermaas[/TD][/TR]
[/TABLE]

Code:

[NOPARSE]SELECT eventId Event,
       best 'Record[R]',
       group_concat(personId separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
GROUP BY Event
ORDER BY rank;[/NOPARSE]

For comparison: You could run the query for example in the mysql command line and post it here in a [noparse]

Code:

...

[/noparse] tag, which would look like this:

Code:

mysql> SELECT eventId Event,
    ->        best 'Record[R]',
    ->        group_concat(personId separator ', ') 'Record Holders'
    -> FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
    ->      natural join Results
    ->      join Events on Events.id = eventId
    -> GROUP BY Event
    -> ORDER BY rank;
+--------+-----------+------------------------+
| Event  | Record[R] | Record Holders         |
+--------+-----------+------------------------+
| 333    |       555 | 2007VALK01             |
| 444    |      2197 | 2010WEYE02             |
| 555    |      4842 | 2009ZEMD01             |
| 222    |        69 | 2009KASE02             |
| 333bf  |      2319 | 2011KOWA01             |
| 333oh  |       903 | 2009ZEMD01             |
| 333fm  |        20 | 2009OKAY01             |
| 333ft  |      2514 | 2012CAMP03             |
| minx   |      3957 | 2010CORM02             |
| pyram  |       136 | 2008ANDE02             |
| sq1    |       696 | 2008LIBI01             |
| clock  |       527 | 2009WANG19             |
| skewb  |       219 | 2009HARN01, 2013KOSK01 |
| 666    |     10086 | 2009HAYS01             |
| 777    |     15941 | 2010CHEN20             |
| 444bf  |     14527 | 2012FROS01             |
| 555bf  |     33910 | 2012JALO01             |
| 333mbf | 580325400 | 2011KOWA01             |
| magic  |        69 | 2009WANG13             |
| mmagic |       166 | 2009WANG13             |
| 333mbo | 750815700 | 2007HABE01             |
+--------+-----------+------------------------+

Benefits of my tool:

Automatically keeps your database up to date.
Adds some database indexes to speed up queries.
Formats the query results for nice copy&paste-ing here.
- Builds a [noparse]
  
  ...
  [/noparse] from the data.
- Turns IDs into names or even links.
- Formats results (e.g., 750815700 becomes "24/24 (2:15:57)").
- Right-aligns numbers and formatted numbers.
- Creates a titled spoiler (nice because most of our statistics are long).
- Mentions the WCA export that was used.
- Includes the query under the results (so others can check it, learn from it, use it).
Recompute an old statistic with current data simply by deleting the .out file and running the tool.

The mysql command line tool also does tables and right-align, though not as good. I don't know what other tools (e.g., Excel using the .tsv export) can do for you, as I prefer to work with SQL (and Python, more on that in future versions).

Stefan · Sep 21, 2014

Using these two files (I did it with files because the mysql command line window turned non-Latin characters into question marks):

mysql.bat:

Code:

mysql.exe -t --default-character-set=utf8 -hlocalhost -uwca_export -pXXX wca_export < mysql.in > mysql.out

mysql.in:

Code:

SELECT [COLOR="#FF0000"]cellName[/COLOR] Event,
       best 'Record[R]',
       group_concat([COLOR="#FF0000"]Persons.name[/COLOR] separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
     [COLOR="#FF0000"]join Persons on Persons.id = personId and subId = 1[/COLOR]
GROUP BY Event
ORDER BY rank;

Now it shows the event and person names, although at the cost of a longer and in this case significantly slower query. Also, the non-Latin characters mess up the table lines.

mysql.out:

Code:

+---------------------+-----------+----------------------------------+
| Event               | Record[R] | Record Holders                   |
+---------------------+-----------+----------------------------------+
| Rubik's Cube        |       555 | Mats Valk                        |
| 4x4 Cube            |      2197 | Sebastian Weyer                  |
| 5x5 Cube            |      4842 | Feliks Zemdegs                   |
| 2x2 Cube            |        69 | Christian Kaserer                |
| 3x3 blindfolded     |      2319 | Marcin Kowalczyk                 |
| 3x3 one-handed      |       903 | Feliks Zemdegs                   |
| 3x3 fewest moves    |        20 | Tomoaki Okayama (岡山友昭)       |
| 3x3 with feet       |      2514 | Gabriel Pereira Campanha         |
| Megaminx            |      3957 | Louis Cormier                    |
| Pyraminx            |       136 | Oscar Roth Andersen              |
| Square-1            |       696 | Bingliang Li (李炳良)            |
| Rubik's Clock       |       527 | Sam Zhixiao Wang (王志骁)        |
| Skewb               |       219 | Brandon Harnish, Jonatan Kłosko  |
| 6x6 Cube            |     10086 | Kevin Hays                       |
| 7x7 Cube            |     15941 | Lin Chen (陈霖)                  |
| 4x4 blindfolded     |     14527 | Oliver Frost                     |
| 5x5 blindfolded     |     33910 | Grzegorz Jałocha                 |
| 3x3 multi blind     | 580325400 | Marcin Kowalczyk                 |
| Rubik's Magic       |        69 | Yuxuan Wang (王宇轩)             |
| Master Magic        |       166 | Yuxuan Wang (王宇轩)             |
| 3x3 multi blind old | 750815700 | Tim Habermaas                    |
+---------------------+-----------+----------------------------------+

WCA Statistics Tools

Stefan

Legend

Lucas Garron

Premium Member

kinch2002

Premium Member

Laura O

Veteran

Stefan

Legend

MaeLSTRoM

Veteran

Lucas Garron

Premium Member

cubizh

Veteran

Stefan

Legend

cubizh

Veteran

Stefan

Legend

Stefan

Legend

Similar threads

Forum statistics

Share this page