paper: 2017 Component OPR Spreadsheet

Thread created automatically to discuss a document in CD-Media.

2017 Component OPR Spreadsheet
by: Rachel Lim

Spreadsheet containing component OPRs for all teams by event, graphs of world/division ranks, and lookup interfaces for individual teams and alliances.

The 2017 version of my component OPR spreadsheet is below. Major changes include:

  • Component OPRs (obviously) changed from last year, and include overall points; auto points; rotor points, gear counts, fuel low count, and fuel high count for auto and teleop; takeoff points; and fouls committed and received
  • Calculations are now done by event, using all qualifciation matches
  • Alliance lookup compares max component OPRs of 3 teams

Like last year, data is displayed and graphed (by percentile).

Couple more notes:

  • Since fuel OPR is on average so low, a negative OPR could put you around the 30th percentile, and .1 around 80th. To make the graphs a bit more intuitive, if a team’s fuel OPR is under 2, I treated them as the 0th percentile in shooting. To edit this number of remove it completely, edit the cells that calculate rank with fuel OPR (e.g. K14, L14, etc.)
  • Gear counts are calculated from rotor points, all other data is pulled straight from TBA. More notes on this are in the spreadsheet itself
  • Division data tab is just for anyone interested in comparing data by division, you can (or should be able to) delete that without affecting the other tabs
  • Excel (.xlsx), libreoffice calc (.ods), and google sheets (linked below) versions should all be functionally identical. I couldn’t figure out how to format the google sheets graphs like I wanted, so that’s the major difference.

Google sheets version (make a copy to edit):

LibreOffice Calc (CD wouldn’t let me upload it, try downloading from here):

Let me know if you have any feedback or suggestions, or find any bugs/errors.

4/18 update - added week 7 data, updated division team lists, added lookup by alliance to excel only
4/18 update #2 - updated libreoffice version
5/2 update - corrections detailed in post below

2017_component_opr_database_excel.xlsx (1.33 MB)

Component OPR data is also linked below in csv format. I’ll add week 7 calculations into the folder and spreadsheet after this weekend.

Folder with all data: link

By event: link
By team: link
By team (max OPR only): link

By division:
Carver: link
Galileo: link
Hopper: link
Newton: link
Roebling: link
Turing: link

Nice job. However, looks like they’ve switched a few teams.

Finally had some time to fix this, and replaced the old excel sheet with a new one that should hopefully have the correct data. If there are still errors or discrepancies please let me know.

I also added in week 7 component OPR data. I’ll try update the google sheets, libreoffice calc, and csv files with the new/corrected data soon – if anyone is using those and wants the new data let me know and I’ll prioritize it.

Final change: using the preliminary match schedules, I added a lookup by match number, so you can compare both alliances in two graphs. Comparing the three ways to search the data:

Lookup by team (enter team number):

Lookup by alliance (enter three team numbers):
(palindrome alliance!)

Lookup by match (enter division name and match number)

Its likely just a type in the spreadsheet, but on the notes tab, 120 points in auto and 40 points in teleop is listed as having a value of 3 auto gears and 5 teleop gears. It should be 3 auto gears and 3 teleop gears.

Thank you for putting all this together, its been an interesting resource to look through and compare teams with.

Just a quick note as a warning to using component OPR data to make decisions - OPR, and in particular component OPR for non-linear tasks, can vary wildly from the truth. This causes any data derived from those values to also vary wildly.

Here is a sample for my team. It suggests that at vagle we had a less than one half of one teleop gear per match average. For teleop this is unbelievably far off from what actually happened (and is far off from the component OPR sum…). It also suggests that our climbs at CHS Champs only hit 80% of the time, yet we had a 100% rate across all matches that event. On top of that the component OPR data for our autonomous shows us hitting the autonomous rotor every time at chscmp - but I know for a fact we couldn’t seem to hit an auton gear while on the Blue Alliance.

Again, this isn’t an issue with this particular calculator or (my calculators/sims have similar-but-different issues). Rather, it’s an issue with relying on statistics generated from non-contextual numbers. Keep that in mind as we work with partners and attempt to estimate opponents these next two weeks.

Thanks Rachel for this and the Component OPR Google sheet calculator. We found them a great addition to our scouting tools this year. Plus they made me explore some new areas of Excel and Sheets - always a good thing!

Question on the week 7 data you added - It looks like the gear counts show zero for all week 7 events. Is there a reason you didn’t calculate this field?

Sorry I never responded to these–I was catching up on homework after Houston and missed these posts entirely. I realize it’s probably too late for this to be useful now, but for the sake of having accurate information online / in case someone does use this later, I’ve replaced the spreadsheet posted online. Also, thanks to Basel for noticing that I somehow replaced team 3319 with 3332, who no longer exists (I have no idea how that happened, and sorry to 3319 about this). Both that and the other stuff noted below has been fixed.

Since I now have the system set up, I’ll try to start the spreadsheet earlier next year. And I’ll hopefully manage to get it set up for both (half)champs.

That was a typo, although there was another issue with gear counts that I think might have caused part of the underestimation of gear abilities. I apologize for this and hope it didn’t mislead too many teams. However I completely agree OPR does not replace scouting (even correctly calculated OPR). All calculations based on match data have their limitations.

I’m still not sure what happened there. It should have been there and does now…

I really found your component OPR research in 2017 to be really interesting in tracking game trends and strategies. I hope you will continue for 2018:] :]