View Full Version : 2016 match data
For the benefit of those who would like to analyze the scoring data but don't know how to access the API and/or parse the XML/JSON responses, I've posted Excel CSV files here (http://www.chiefdelphi.com/media/papers/3243), so you don't have to "scrape" web pages to get the data you need.
Cothron Theiss
13-03-2016, 23:06
You're a gift, thank you.
antillies85
14-03-2016, 14:49
This is amazing! But I have a very newbie question for you. So looking at say MIKE's qualification match data, how would you use data to build a profile of which matches a team was involved with and the scores from that particular match? Would it be a pretty complicated set of vlookups to build that profile?
Thanks so much for compiling this! And we've been trying to look for ways to learn how to interact with the API but the tutorials are pretty thin. If you have any suggestions, that would be awesome.
So looking at say MIKE's qualification match data, how would you use data to build a profile of which matches a team was involved with and the scores from that particular match?
In the qual match CSV, Columns E thru J contain the Team Numbers r1,r2,r3,b1,b2,b3.
Say you want to find all of Team 469's matches in Week2 MIKE2 event.
Insert a new column right after Column J. Now you will have a new blank column K.
Put the heading "Team469" in cell K1.
Put the following formula in cell K2:
=IF(COUNTIF(E2:J2,"469"),1,0)
Copy cell K2 all the way down.
Now each cell in column K contains the value "1" if 469 played in that match, or a "0" if they did not.
Select all the cells in the spreadsheet, and sort by column K descending. All the matches that 469 played in will be displayed at the top of the spreadsheet.
Is that what you were asking?
There was an error in the original "Week1 Matches raw data". That file attachment has been removed, and a corrected version uploaded. Sorry for any confusion or inconvenience.
antillies85
14-03-2016, 16:06
Thank you! Yes, my brother mentioned doing that as well. Thank you for confirming that this is a good way to go about this. I'm trying to use Tableau to compile data and visualize it all.
Thank you for your help!
Thank you! Yes, my brother mentioned doing that as well. Thank you for confirming that this is a good way to go about this.
I'm not so sure it's a "good" way, but it's an quick-n-easy way.
In Excel, a better way would be to write a simple macro to pick each match that 469 played in, and copy it to a new sheet.
I'm trying to use Tableau to compile data and visualize it all.
I've never used Tableau. Does it have a macro language? If so, you might want to investigate that.
Thank you for your help!
You're welcome. Feel free to ask questions any time.
I should have mentioned in the OP:
Hat tip to Phil Lopreiato and Alex Herreid for their patient help.
Firgreen
14-03-2016, 17:30
I'm trying to use Tableau to compile data and visualize it all
There's no equivalent of macro calculations to VBA in Excel, though it does have some kind of data blending between different CSV sheets, as well as some flexibility in their table calculations.
But I've found the best way is to blend the data in Excel and then import into Tableau, which does require calculations and possibly macros.
Caleb Sykes
14-03-2016, 17:43
For the benefit of those who would like to analyze the scoring data but don't know how to access the API and/or parse the XML/JSON responses, I've posted Excel CSV files here (http://www.chiefdelphi.com/media/papers/3243), so you don't have to "scrape" web pages to get the data you need.
Thank you very much.
plnyyanks
14-03-2016, 20:38
For the benefit of those who would like to analyze the scoring data but don't know how to access the API and/or parse the XML/JSON responses, I've posted Excel CSV files here (http://www.chiefdelphi.com/media/papers/3243), so you don't have to "scrape" web pages to get the data you need.
Nice results! I've always appreciated your love of data and am always a fan of more numbers
I should have mentioned in the OP:
Hat tip to Phil Lopreiato and Alex Herreid for their patient help.
Not a problem - I'm always happy to help! Glad you were able to find a solution that worked for you!
Just to let you all know:
I will be posting the complete Week3 raw data* in CSV format within 18 hours after the last playoff match has been completed.
*qual match schedule with surrogates; qual match final, foul, and auto scores; qual match scoring details; qual match team rankings with wins, losses, ties, and DQ; alliance selection with substitutions; playoff match schedule; playoff match scoring details; and awards
I wrote a script to scan through all the Scoring Details data (component scores) and pick out the high value in each category for week1 and week2. See attached.
I've not manually vetted this yet but I thought I'd post it for review by anyone interested.
Just to let you all know:
I will be posting the complete Week3 raw data in CSV format within 18 hours after the last playoff match has been completed.
Week3 Raw Data CSV files have been compiled and posted here:
http://www.chiefdelphi.com/media/papers/3243
EDIT: attached is a compilation of hi component scores for week3.
Just posted Week3 District Rankings CSV files here (http://www.chiefdelphi.com/media/papers/3243).
Week4 raw data CSV files, District Rankings CSV files, and High Scores (http://www.chiefdelphi.com/media/papers/3243#views) were uploaded late yesterday evening.
Joe Ross
27-03-2016, 17:28
In the high scores for towerEndStrength, the smallest number might be more interesting then the largest, which is just who got the most technical fouls without weakening the tower. It would also be interesting to see how many times a high score was acheived. For example, breach of 20 and capture of 25 has probably been obtained hundreds, while teleopBoulder points of 76 has probably only happened once.
In the high scores for towerEndStrength, the smallest number might be more interesting then the largest...It would also be interesting to see how many times a high score was achieved
Thanks Joe. Done. (revA posted)
http://www.chiefdelphi.com/media/papers/3243
Week4 raw data CSV files, District Rankings CSV files, and High Scores (http://www.chiefdelphi.com/media/papers/3243#views) were uploaded late yesterday evening.
I just added attachments for cumulative Weeks 1 through 4 Qual and Playoff High Scores.
http://www.chiefdelphi.com/media/papers/3243
Thanks for providing these. I had quickly developed some capabilities for scouting using the Ranking data between our first and second (last) regional.
I started directly connecting Ranking data to Tableau using a web data connector. The particular connector uses import.io, which would allow even more complex extractions. I had hoped to use this to make everything work in a standalone Tableau workbook, but time and my inexperience made me switch later to more familiar tools.
For scouting, Tableau was pretty powerful and simple to use. Direct from the Ranking data, I plotted a 2x2 graph with Defense and Goal on y-axis and Scale and Auto on x-axis. In a second worksheet, I plotted individual bar charts with team numbers on the x-axis. I liked this worksheet better because hovering over the y-axis allowed for me to sort, showing the top teams easily. I also plotted some calculated fields here, like the sum of Defense and Goal points. Filtering was really simple too, and useful to exclude teams with bad auto scores, etc.
There is some scripting in Tableau, with integration with R. Using it I ran a clustering algorithm, only using four parameters. In the 2x2 plots, I set colors based on cluster number. Here is an example: http://imgur.com/a/QDWpy . I also wrote more on this: https://goo.gl/DTnwjH
From there I wanted to instead calculate team-based scores from the alliance-based scores (basically OPR). I expect this could be done in Tableau with scripting, but I ended up doing this in Excel, building the matrix (by copy/pasting the 3 team alliances for each permutation (1&1,2&2,3&3,1&2,2&1,1&3,3&1,2&3,3&2), and then using a series of countifs to fill the matrix). And then solving Ax=b in Excel for each. I then exported a CSV and opened this in Tableau. This data was useful. Selection seems to follow it rather closely, with a little greater emphasis to goal scoring teams. So in that way it was good, although it doesn't give much tactical information and it does undervalue low goal scorers and their contribution to tower captures.
I only started looking at match data a few days before our regional. Not having the API/JSON experience, I did have a student begin to compile match data manually. However, by lunch Friday I knew we didn't have resources to even make that work. For anyone else in that predicament, here is a Google Sheet I have hastily put together today (https://docs.google.com/spreadsheets/d/1wg6XTXfaHswEGhfSj9wWlNl1Dt7xKTWQhL3I0LLVUVU/edit?usp=sharing). Altering in for other competition, mostly involves changing "code2016" to the correct name for the competition on TBA. I'd planned to use pivot tables to sum the data per team, and then exporting to Tableau. Before that I would fix team numbers (I forgot to account for numbers <1000), and clean the data (e.g. making each defense a header and the value be pts from crossing, and using only numeric or boolean data), likely with a macro.
During the offseason, I want to teach some of this to the team members, all the way from the API to the visualization. It is nice to have these files already compiled so that we'll be able to jump directly into using the data and exploring different ideas. There is a lot of potential to use this data for scouting and also for the team members to learn how to work with programming/analysis tools.
Thanks for providing these.
I'm glad you've found them to be useful.
@all_readers: if there is any additional available raw data that you would like to analyze and which isn't included in the CSVs I am posting, let me know and I'll see if I can add it.
I wanted to instead calculate team-based scores from the alliance-based scores (basically OPR). I expect this could be done in Tableau with scripting, but I ended up doing this in Excel, building the matrix (by copy/pasting the 3 team alliances for each permutation (1&1,2&2,3&3,1&2,2&1,1&3,3&1,2&3,3&2), and then using a series of countifs to fill the matrix). And then solving Ax=b in Excel for each. I then exported a CSV
There's a much simpler way to get OPRs. Caleb Sykes posts a large XLSX spreadsheet here (http://www.chiefdelphi.com/media/papers/3248). And Team 2834 maintains an XLXM scouting database here (http://www.chiefdelphi.com/media/papers/3242).
Or with just a little bit of effort using AWK (or Python) and Octave (or Matlab) you can easily create a CSV of OPR values for any raw data that has match-by-match scores:
Here's a complete AWK script (http://www.chiefdelphi.com/forums/attachment.php?attachmentid=20489&stc=1&d=1459370907) that reads an 8-column whitespace-separated plaintext file that contains the fields red1 red2 red 3 blue1 blue2 blue3 redscore bluescore, and outputs the team list column vector T, the alliance scores column vector b, and the sparse binary 2MxN design matrix A (M is number of matches, N is number of teams).
Here's an Octave script (http://www.chiefdelphi.com/forums/attachment.php?attachmentid=20490&stc=1&d=1459370907) that reads the AWK output and computes OPR.
I only started looking at match data a few days before our regional. Not having the API/JSON experience, I did have a student begin to compile match data manually. However, by lunch Friday I knew we didn't have resources to even make that work.
What match data are you referring to, and what were you trying to compile manually? I post all the raw match data as CSV files.
I will post Week5 raw data sometime late evening Apr3 or early morning Apr4. It will not include Western Canada Regional, which does not finish until Apr6.
I will post updated Week5 data, including Western Canada, sometime late evening Apr6 or early morning Apr7.
I will post Week5 raw data sometime late evening Apr3 or early morning Apr4. It will not include Western Canada Regional, which does not finish until Apr6.
Posted.
http://www.chiefdelphi.com/media/papers/3243#views
Week5 District Rankings CSV Files have been posted (http://www.chiefdelphi.com/media/papers/3243#views)
CrazyMohawk
03-04-2016, 22:49
Week5 District Rankings CSV Files have been posted (http://www.chiefdelphi.com/media/papers/3243#views)
not all of the points teams earned at Auburn are accounted for in the rankings
not all of the points teams earned at Auburn are accounted for in the rankings
Perhaps there was an internet issue at that venue. It happens. I'll download the rankings again tomorrow and compare them.
Perhaps there was an internet issue at that venue. It happens. I'll download the rankings again tomorrow and compare them.
TBA just updated PNW District Rankings for Auburn event. Updated CSV files have been posted.
I will post Week5 raw data sometime late evening Apr3 or early morning Apr4. It will not include Western Canada Regional, which does not finish until Apr6.
Posted.
http://www.chiefdelphi.com/media/papers/3243#views
PNW Auburn (WAAHS) Awards was not in the dataset at the time it was retrieved. I have added an attachment for it (http://www.chiefdelphi.com/media/papers/3243#views).
Western Canada Regional (ABCA) Raw Data CSV files have been posted (http://www.chiefdelphi.com/media/papers/3243#views).
I will be posting Week6 raw data CSV files as follows:
the morning of April 10th for those events ending on the 9th
the morning of April 11th for those events ending on the 10th
http://www.chiefdelphi.com/media/papers/3243#views
I will be posting Week6 raw data CSV files as follows:
the morning of April 10th for those events ending on the 9th
the morning of April 11th for those events ending on the 10th
Change of plans. Unless someone needs the April 9th data right away, I'm going to wait until the April 10th events are complete and then post all the week6 data.
http://www.chiefdelphi.com/media/papers/3243#views
ZIP attachment for Week6 Raw Data CSV files (http://www.chiefdelphi.com/media/papers/3243#views) (all 15 Week6 events) has been posted.
ZIP attachment for Week6 Raw Data CSV files (http://www.chiefdelphi.com/media/papers/3243#views) (all 15 Week6 events) has been posted.
ZIP attachment for Week6 District Rankings CSV files has been posted.
ZIP attachment for Week7 Raw Data CSV files has been posted
http://www.chiefdelphi.com/media/papers/3243#views
ZIP attachment for Week7 Raw Data CSV files has been posted
http://www.chiefdelphi.com/media/papers/3243#views
ZIP attachment for Week7 District Rankings CSV files has been posted.
http://www.chiefdelphi.com/media/papers/3243#views
ZIP attachment for Week7 Raw Data CSV files has been posted
http://www.chiefdelphi.com/media/papers/3243#views
ZIP attachment for Week7 District Rankings CSV files has been posted.
http://www.chiefdelphi.com/media/papers/3243#views
Week7 Playoff and Qual High Scores attachments have been posted
http://www.chiefdelphi.com/media/papers/3243#views
lamiet01
19-04-2016, 23:14
Wow! According to this, New England states are actually Great Lakes states. Who knew :D
Week8 CMP raw data CSV files have been posted.
http://www.chiefdelphi.com/media/papers/3243#views
Caleb Sykes
02-05-2016, 11:41
Ether, in the #CMP-CARSON_Qsched file, the alliances for qualification match 115 on Carson appear to be incorrect. The alliance colors are all flipped and the teams are out of order. I have not yet noticed errors in any of the other files.
Caleb Sykes
02-05-2016, 11:51
Ether, in the #CMP-CARSON_Qsched file, the alliances for qualification match 115 on Carson appear to be incorrect. The alliance colors are all flipped and the teams are out of order. I have not yet noticed errors in any of the other files.
Actually, not all of the alliance colors are flipped. It seems that 319 was moved from blue 3 to red 1, and 51 was moved from red 3 to blue 1. The other participants in this match were shifted right one alliance station.
Ether, in the #CMP-CARSON_Qsched file, the alliances for qualification match 115 on Carson appear to be incorrect. The alliance colors are all flipped and the teams are out of order. I have not yet noticed errors in any of the other files.
"PSA New Carson Schedule"
http://www.chiefdelphi.com/forums/showpost.php?p=1475060&postcount=1
5931 replaced with 1625
http://www.chiefdelphi.com/forums/showpost.php?p=1579670&postcount=27
http://www.chiefdelphi.com/forums/showpost.php?p=1579709&postcount=28
http://www.chiefdelphi.com/forums/showpost.php?p=1579858&postcount=31
http://www.chiefdelphi.com/forums/showpost.php?p=1579903&postcount=41
New Carson schedule:
http://www.chiefdelphi.com/forums/showpost.php?p=1475059&postcount=96
Use the Qmatch CSV files for the actual schedule.
Use the Qmatch CSV files for the actual schedule.
I just re-downloaded the Carson Qsched from FIRST's API. It differs significantly from the one I downloaded May1 at 8:15am. But FIRST neglected to update the "Last Modified" header.
The Qmatch CSV should have the actual (correct) alliances schedule.
Do you need the corrected Qsched file for your database spreadsheet? I can process it if not having it creates problems for you.
Caleb Sykes
02-05-2016, 13:06
I just re-downloaded the Carson Qsched from FIRST's API. It differs significantly from the one I downloaded May1 at 8:15am. But FIRST neglected to update the "Last Modified" header.
The Qmatch CSV should have the actual (correct) alliances schedule.
Do you need the corrected Qsched file for your database spreadsheet? I can process it if not having it creates problems for you.
I'm fine without it as long as that one match is the only difference, which I'm pretty sure it is. I can just manually change the Qsched file for that match and I should be fine.
Thanks though
vBulletin® v3.6.4, Copyright ©2000-2017, Jelsoft Enterprises Ltd.