# Pivot function misalign data

**URL:** <https://community.influxdata.com/t/pivot-function-misalign-data/39788>\
**Category:** InfluxDB 2\
**Tags:** flux\
**Created:** [December 11, 2024, 9:39am UTC](https://community.influxdata.com/t/pivot-function-misalign-data/39788 "2024-12-11T09:39:36Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ynx940214](https://avatars.discourse-cdn.com/v4/letter/y/90ced4/32.png) [@ynx940214](https://community.influxdata.com/u/ynx940214)\
**Post date:** [December 11, 2024, 9:39am UTC](https://community.influxdata.com/t/pivot-function-misalign-data/39788/1 "2024-12-11T09:39:36Z")

</div>

`pivot` function misalign data. Here is the python code:

```auto
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](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/3X/7/e/7e824dbe2f3af948ce0cee819e18e019e478cba5.png)

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, 🙂 )

```auto
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

```auto
"""
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

```auto
_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

```auto
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