Sum values of series with same tag-values

Fluxlang
, , ,
1

I’m working on some data from my Fantasy NBA league. I gathered the data using Python and have a measurement of the following form:

'measurement': 'fantasy_playerstats',
'tags': {
    'fantasy_team': fantasy_team,
    'injury_status': player.injuryStatus,
    'is_on_ir': (player.lineupSlot == "IR"),
    'score_type': score_type,
    'acquisitionType': player.acquisitionType,
    'player': player.name
},
"time": datetime.datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%SZ'),
"fields": {
    "score": score
}

So basically this is collecting the score of each individual player.

Now in Grafana I’d like to show the summed up score of each fantasy team to generate a ranking (using a table) and how team-score changed over time (using a graph).

I’m using the following Flux Query:

from(bucket: "HomeBucket")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "fantasy_playerstats")
  |> filter(fn: (r) => r.score_type == "2023_last_7")
  |> filter(fn: (r) => r.is_on_ir == "False")
  |> drop(columns: ["acquisitionType", "injury_status", "is_on_ir", "score_type"])

Which results in series for each different player like so:

  • score {fantasy_team="Team A", player="Anthony Edwards"}
  • score {fantasy_team="Team A", player="Ayo Dosunmu"}
  • score {fantasy_team="Team B", player="Jusuf Nurkic"}
  • score {fantasy_team="Team B", player="Klay Thompson"}

Now I’m stuck with merging these series by the tag fantasy_team and summing up all the values. I played around a lot with grouping and ungrouping, join, pivot, union and what not, but cannot figure out what’s the right thing to do right now. I’m sure the answer is pretty simple once I get my head around it.

Can someone provide some pointers on how to continue?

2

Hi @monsdar,
Great use case! so you were right about grouping. essentially this is what you would do:

from(bucket: "HomeBucket")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "fantasy_playerstats")
  |> filter(fn: (r) => r.score_type == "2023_last_7")
  |> filter(fn: (r) => r.is_on_ir == "False")
  |> group(columns: ["fantasy_team"], mode:"by")  
|> sum()
3

In the end I was able to tackle it with the following query:

from(bucket: "HomeBucket")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "fantasy_playerstats")
  |> filter(fn: (r) => r.score_type == "2023_last_30")
  |> filter(fn: (r) => r.is_on_ir == "False")
  |> group(columns: ["_time", "fantasy_team"])
  |> sum()
  |> group(columns: ["fantasy_team"])
  |> sort(columns: ["_time"])

I needed to keep _time in so that I could show the value in a graph:

image
image1823×463 83.8 KB

1 Like