2018 Google Sheets Component OPR Calculator

The 2018 version of my google sheets OPR calculator is linked below, and like my 2017 calculator, it pulls match breakdowns from the blue alliance’s API, calculates various component OPRs and averages, and all runs completely within a google spreadsheet. Wondering if OPR means anything in this game? So am I! Hopefully week 1 events will show if any of this is useful data.

Link: https://goo.gl/D7HnE7

Features:

  • Pulls all score breakdowns provided by FIRST, and plate assignment strings as done by TBA
  • Calculates auto run, climbing, levitate, and parking averages (as tracked by autoRobot1-3 and endgameRobot1-3)
  • Calculates 13 component OPRs
  • Runs on any device that can open a google spreadsheet and that has internet*
  • You should never have to do anything except edit boxes with dropdown arrows and click blue buttons**

*events can only be added/removed on desktop, but once set up the calculator can be run from any device
**if you only do this, in theory, nothing will break. I did some weird stuff in scripts to make this possible, so I recommend not changing anything else without looking through the functions first

Instructions on using the sheet are on the first page, but if you have any questions or find bugs in please let me know. Also let me know if there are other calculations you think would be useful, or any you think I should remove. I think everything is working, but I only have week 0 data to test it on so far so I’ll be checking it all again once week 1 events start.

1 Like

This was my favorite last year! Thanks for making one for this year!

Rachel,

Do we need to be aware of how far in the future the event takes place? I picked a week 3 event…got lots of security warnings, then this:

Document 1Yh96O3IXsCVTogLDepKQeFDFlSSODU1bbEqLp2NQeDE is missing (perhaps it was deleted?)

And no sheets created… ?

(Thanks for putting this together! I sounds like it will be very useful!)

Matt

Thanks to everyone who let me know about this issue, I think it’s fixed now but please let me know if it’s still not working for you. Turns out I forgot to share the spreadsheet it’s trying to copy the pages from, so it was working for me as long as I was using the same account.

I haven’t yet figured out a way around the verification issue, so the first time you try and run the sheet, the “authorization required box” will come up, click continue, and then in the “app isn’t verified” box select advanced > go to 2018 OPR calculator (unsafe) > allow. This should only happen the first time you run a script (i.e. the first time you try and add an event).

I think CCWM is going to be useful this year, potentially even better than OPR, but I doubt it. If you wanted to add some other “margin” categories those could also potentially be useful. The biggest problem with margin categories though is that they only provide one equation per match, unlike OPR which provides two. This means that overfitting is often a really big concern, and at an event with n teams, no unique solution will exist until at least n matches have been played.

You should also really have a climb or endame points OPR. The rates are great for teams that do solo climbs, but you won’t be able to capture how good ramp-bots or multi-climbers are with rates alone. Likewise, since the levitate bonus is assigned randomly, consider splitting its points between all non-climbing teams. Without some adjustment like this, your levitate rate is mostly just describing the teams lucky enough to get assigned the levitate by the field.

Looks great though! Keep it up.

??

I believe he is referring to the fact that OPR has two equations:

OPR_R1 + OPR_R2 + OPR_R3 = redScore
and
OPR_B1 + OPR_B2 + OPR_B3 = blueScore

whereas CCWM refers to win margin, so it’s only

(CCWM_R1 + CCWM_R2 + CCWM_R3) - (CCWM_B1 + CCWM_R2 + CCWM_B3) = winMargin
^ assuming red wins.

R1 is red 1, B3 is blue 3 etc.

For a given match, the OPR equations we get are:
red1_OPR + red2_OPR + red3_OPR = red_score
blue1_OPR + blue2_OPR + blue3_OPR = blue_score

and the CCWM equation is :
red1_CCWM + red2_CCWM + red3_CCWM - blue1_CCWM - blue2_CCWM - blue3_CCWM = red_score - blue_score

So it takes twice as many matches for CCWM to provide a unique solution as OPR. Am I misunderstanding CCWM? It’s certainly possible since I haven’t looked into it nearly as much as OPR. I have always thought CCWM was inferior to OPR because it was so much more overfit.

Your equation above is equation#1 of EPR

This is CCWM:

red1 + red2 + red3 = red_score - blue_score;

blu1 + blu2 + blu3 = blu_score - red_score;

I have this nagging feeling that I tried calculating CCWM with both your and my equations a few years back and found that they gave equivalent ratings. Although that could very well just be my imagination. I’ll compare both methods this weekend if no one else does before then.

The CCWM equations are not mine. They are Ed Law’s. See his paper introducing the idea.

…a few years back and found that they gave equivalent ratings.

I very highly doubt that. If you find otherwise, please post a link to your raw source data so I can run the computation.

This program does not seem to be working for me.

Same, I’m getting this when running ndgf:
“TypeError: Cannot read property “blue” from null.”

week0 still seems to work fine for me.

Thanks for the feedback! I added a CCWM component using Ether’s formula, since it was pretty easy to implement, while the other equation (equation 1 of EPR) requires a different sparse array, if I understand the discussion correctly. I also added an endgame_opr component, and changed levitate_avg to count the percentage of matches a team played in where levitate was activated. I debated leaving teams that climbed out, but decided playing the 3 levitate cubes and climbing were not exclusive.

I’m very sorry about this, turns out testing on week 0 data meant I didn’t check to make sure it would work on in-progress events. It should be fixed now, but everyone will have to make a new copy of the spreadsheet or replace the code in Tools > Script Editor with the code in the text file below. Apologies for the messy code, I’ll try to clean it up later but wanted to get the fix out as soon as I could.

opr_calculator_3_2_update.txt (18.8 KB)

If you generate your arrays properly, you can easily compute any of the metrics.

From the raw score data, generate these 5 arrays:

Ar, Ab, br, bb, and T

Ar and Ab are the red and and blue binary design matrices , respectively.

br and bb are the red and blue alliance scores matrices, respectively.

T is the vertical column vector of Team numbers.

To compute OPR, find the min L2 norm solution to this linear system:

[Ar;Ab][x] = [br;bb]

To compute CCWM, find the min L2 norm solution to this linear system:

[Ar;Ab][x] = [br-bb;bb-br]

To compute EPR, find the min L2 norm solution to this linear system:

[Ar-Ab;Ar;Ab][x] = [br-bb;br;bb]

If your dataset is very large (like World OPR late in the season), use sparse matrix technology for Ar & Ab.

See here for one of the more technical “OPR” threads on CD.

I’ll be choosing to handle levitate differently in my metrics, but your method of handling levitate is totally fine. Certainly far better than what you had previously.

I’m very sorry about this, turns out testing on week 0 data meant I didn’t check to make sure it would work on in-progress events. It should be fixed now, but everyone will have to make a new copy of the spreadsheet or replace the code in Tools > Script Editor with the code in the text file below. Apologies for the messy code, I’ll try to clean it up later but wanted to get the fix out as soon as I could.

No problem, I know how rough it is to get a useful tool out before week 1.

I’m noticing some slight differences between your metrics and mine for incomplete events. For ndgf as of match 58, TBA’s top 15 OPRs are this, my top 15 are this, and your top 15 are this. I didn’t notice any differences between the three of us for the week 0 competition. It is possible that TBA and I both have the same error, but since we are independent of each other I think it’s more likely you have a small error somewhere (perhaps missing or adding one match).

On a related note, it would be very nice if the OPR sheets had filters. I tried adding my own but something seems to be updating to cause filters to not work properly.

Bug report. When trying to calculate OPR: “The coordinates or dimensions of the range are invalid.”

Updating data seems to be fine so that’s fixed.

That thread confirms that I was thinking of the wrong equations for CCWM. The metric I was describing above is equivalent to the WMPR metric described in that thread, or equation 1 of EPR. That thread came out a few months before I started getting heavily involved in FRC statistics, I remember reading it when it came out and much of it went over my head. I’ll have to go through it all again sometime, especially wgardner’s paper at the end.

I think both these issues should be fixed now, turns it all traced back to some matches having a null actual_time even though we were played, and the fact that I was using that field to check which matches had already been completed. It didn’t affect every event, and it turned out that the couple I had tested that change on didn’t have an issue, but it should now work on all of them.

I ran this update on a variety of week 1 events and didn’t run into any more issues, but if something else comes up please let me know which event you were trying to run, since variations in event data have caused a few of these problems and google sheets is really bad at giving useful error messages.

Again, the fix available by making a new copy of the spreadsheet or replacing the code in Tools > Script editor with the code attached below.

I think you’re talking about being able to sort columns from max to min values or similar actions, I changed it so the OPR data is now all printed out as numbers and not as equations, and it’s included with the other fixes. Let me know if that’s not what you were referring to.

opr_calculator_3_4_update.txt (19.2 KB)

I think you’re talking about being able to sort columns from max to min values or similar actions, I changed it so the OPR data is now all printed out as numbers and not as equations, and it’s included with the other fixes. Let me know if that’s not what you were referring to.

Yes, that is what I was interested in. It works great now, thanks.