Pivot function misalign data

InfluxDB 2
1

pivot function misalign data. Here is the python code:

from influxdb_client import influxDBClient
client = InfluxDBClient(url="xxx", token="", org="")
query_str = f"""
from(bucket: "bucket1")
  |> range(start: 2024-12-10T00:00:00.000Z, stop: 2024-12-10T00:48:17.000Z )
  |> filter(fn: (r) => r._field == "ctx" or r._field == "client_id")
  |> filter(fn: (r) => r["_measurement"] == "order_data"  and  r.ak=="bg_3bf59d15809e6400f1af62b0bfc7c55e")
  |> keep(columns: ["_time", "_value", "_field", "ak"])
"""
query_api = client.query_api()
t = query_api.query_data_frame(query_str)

t1 = t[t._field == 'client_id']
t2 = t[t._field == 'ctx']

here is t1

image
image2738×730 153 KB

here is t2 (sorry, new users can only post one media, so I can only paste the raw text not image, you can image it as image of dataframe like previous, :slight_smile: )

42,_result,1,2024-12-10 00:00:22.155203+00:00,8279410769665879372,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
43,_result,1,2024-12-10 00:00:22.166170+00:00,8279410769665879372,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
44,_result,1,2024-12-10 00:00:22.286811+00:00,8279410769665879372,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
45,_result,1,2024-12-10 00:03:45.560302+00:00,None,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
46,_result,1,2024-12-10 00:03:45.563056+00:00,None,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
47,_result,1,2024-12-10 00:03:45.687210+00:00,None,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
48,_result,1,2024-12-10 00:08:06.618153+00:00,4015429181656187936,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
49,_result,1,2024-12-10 00:08:06.618988+00:00,4015429181656187936,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
50,_result,1,2024-12-10 00:08:06.916183+00:00,4015429181656187936,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e
51,_result,1,2024-12-10 00:08:06.969446+00:00,13503745336594746610,ctx,bg_3bf59d15809e6400f1af62b0bfc7c55e

However, when I pivot the data with

"""
from(bucket: "bucket1")
  |> range(start: 2024-12-10T00:00:00.000Z, stop: 2024-12-10T00:48:17.000Z )
  |>filter(fn: (r) => r._field == "ctx" or r._field == "client_id")
  |> filter(fn: (r) => r["_measurement"] == "order_data" and  r.ak=="bg_3bf59d15809e6400f1af62b0bfc7c55e")
//   |> keep(columns: ["_time", "_value", "_field", "ak"])
  |>pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |>group()
  |> filter(fn: (r) => r.client_id == "2805969341284463009")"""

I got this

_result,0,2024-12-10 00:00:00+00:00,2024-12-10 00:48:17+00:00,2024-12-10 00:03:45.560302+00:00,bg_3bf59d15809e6400f1af62b0bfc7c55e,2805969341284463009,None
_result,0,2024-12-10 00:00:00+00:00,2024-12-10 00:48:17+00:00,2024-12-10 00:03:45.563056+00:00,bg_3bf59d15809e6400f1af62b0bfc7c55e,2805969341284463009,None
_result,0,2024-12-10 00:00:00+00:00,2024-12-10 00:48:17+00:00,2024-12-10 00:03:45.687210+00:00,bg_3bf59d15809e6400f1af62b0bfc7c55e,2805969341284463009,4015429181656187936 (the value should be None, value in the next row got here incorrectly)

and every client_id below got misaligned and shifted 1 row forward, It feels like row No. 45 (or 46 or 47) in t2 is just missing

0,_result,0,2024-12-10 00:00:00+00:00,2024-12-10 00:48:17+00:00,2024-12-10 00:08:06.618153+00:00,bg_3bf59d15809e6400f1af62b0bfc7c55e,3469571159178598031,4015429181656187936
1,_result,0,2024-12-10 00:00:00+00:00,2024-12-10 00:48:17+00:00,2024-12-10 00:08:06.618988+00:00,bg_3bf59d15809e6400f1af62b0bfc7c55e,3469571159178598031,4015429181656187936
2,_result,0,2024-12-10 00:00:00+00:00,2024-12-10 00:48:17+00:00,2024-12-10 00:08:06.916183+00:00,bg_3bf59d15809e6400f1af62b0bfc7c55e,3469571159178598031,13503745336594746610(the correct value is 4015429181656187936,  13503745336594746610 is the ctx value in the next row)

Did I missing something? please help