Downloadable WCA Results

[Lucas]

I'd like to request that the WCA database be offered in a downloadable CSV format, updated at least once every few months (but preferably after every database update). Stefan gets to do all sorts of fun stuff, but I'm not allowed to find out how many people have ever gotten 1:02.27 in competition (on any puzzle)?

The information is available through all the full results of competition pages, and if I need to, I'll manually parse them, but I don't think there is anything inappropriate in more conveniently providing all (at least all already publicly available) information for download. If the database is well-organized, it shouldn't be too much trouble to generate a zip file with enough information like: a subfolder with persons, one with competitions, and one with events and all results (single/avg) -I'd be able to, say, get "regions" from the list of all results and only retaining the PBs of those people with desired nationalities.
I'd be willing to agree to a few terms, but I believe there should be little restrictions beyond restraining abuse, since the information is essentially already available.

[StefanPochmann]

In a few weeks I'm done with my diploma thesis and then this is part of something I'd like to offer.

[Ron]

Hi guys,

Yes, we could offer that with a few restrictions.

Could you guys make a clear specification of what you want?
Something like all results in the following format?
WCA id, name, country, competition id, event id, round id, format id, result1, result2, result3, result4, result5, best, average, region_record_single, region_record_average

This could be put into a ZIP file and made downloadable from the website.
Updated after each competition.

Is that what you mean?

Ron

[jbcm627]

Yes, I think something like this is what people are looking for. Either in an excel file like the ones used to submit competition results, or something similar I think.

However I also have heard that some people might want to look up statistics, such as youngest solver, in an event that isn't listed. The problem with this is that not everyone would want to give out their birthdate or potentially other private imformation.

So, alternatively (or additionally?), I have a suggestion for the "statistics" page. It could be modified to display competitors ranked upon user input, ie have dropdown boxes with categories (youngest, oldest, fastest/best, most appearances in, most attempts in, etc...), then another box to select items (events, locations, etc). This would allow people to search while not necessarily giving out sensitive material.

[Lucas]

Alright, I've made a proposed format for downloadable results.
http://archive.garron.us/zip/2008/WCA.zip gives an example skeleton.

I've tried to make it complete, not too redundant, and reasonably easy to use and parse. If anyone has any improvements, feel free to suggest them. Then, Ron, would it be feasible for you to implement?

[BryanLogan]

Alright, I've made a proposed format for downloadable results.
http://archive.garron.us/zip/2008/WCA.zip gives an example skeleton.

I've tried to make it complete, not too redundant, and reasonably easy to use and parse. If anyone has any improvements, feel free to suggest them. Then, Ron, would it be feasible for you to implement?

I don't know. It seems like anyone who's really interested in this data would be putting it into a database anyways to parse the information. Why not a dump of the database with sensitive information removed? Anything regarding birthday wouldn't be able to be done, but other interesting stuff could be discovered.

[Ron]

Hi all,

The WCA database is now downloable at http://www.worldcubeassociation.org/res ... yyymm.xlsx
We will post at the end of every month, so the first file is wcadump200907.xlsx (size 6.5MB).
If we forget to post, then send an e-mail to rbruchem@worldcubeassociation.org.
Now that we have the database online please stop crawling the WCA website to update your own databases.

Looking forward to your feedback.

Thanks,

Ron

[brunson]

Alright, I've made a proposed format for downloadable results.
http://archive.garron.us/zip/2008/WCA.zip gives an example skeleton.

I've tried to make it complete, not too redundant, and reasonably easy to use and parse. If anyone has any improvements, feel free to suggest them. Then, Ron, would it be feasible for you to implement?

I don't know. It seems like anyone who's really interested in this data would be putting it into a database anyways to parse the information. Why not a dump of the database with sensitive information removed? Anything regarding birthday wouldn't be able to be done, but other interesting stuff could be discovered.

Having it in a mysqldump format would be awesome.

I know in my job I hate it when people tell me "you could just do this...", but it'd be fairly simple to script something along the lines of:

mysqldump originalSchema | mysql schemaCopy

Then run a mysql script to "alter table X drop privateColumns" for all the sensitive information and finally mysqldump the scrubbed data.

Since it looks like you're running on a *BSD system (just a guess), I'd be happy to help automate it all if you're interested in implementing something of the sort and short on time.

[Tim]

In the 200909 downloadable version, Xue Wang 2's ID is listed as 2009XUE 01 (C62400 on the "Results" sheet), while in the database it is listed as 2009XUEW01. Could you try to fix this for future versions?

Thanks,
Tim Reynolds

[Tim]

Another error in the 200910 version: Results 80920 through 80925 appear to be Caltech Fall 2009 4x4 results, but there is no competition name and the event ID is given as "time i".