TimestampValue.add() create new Timestamp column from offset in float seconds

[jogam]

Hi Ibis Users / Team,

I have a very simple problem : I have a table column TS with timestamp values and a offset column with second values in float (xyz.abcdef, ex. : 0.024571) - how would I mutate my table to create a new column TS2 = TS +/- offset for all table rows? From my silly attempts it appears TimestampValue.add() only allows static delta values?

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

import ibis
ibis.options.interactive=True

td_list = [timedelta(milliseconds=v/10).total_seconds() for v in np.random.randint(5000, 30000, 60)]
timestamps = pd.date_range(start="2021-01-01 00:00:00", periods=60, freq="180s")

tbl = ibis.memtable(pd.DataFrame({
        'timestamp': timestamps,
        'offset_s': td_list}))
# tbl.mutate(tsagg=_.timestamp.add(_.offset_s)) # AttributeError: 'NotImplementedType' object has no attribute 'name'
# tbl.mutate(tsagg=_.timestamp.add(ibis.interval(_.offset_s/10**6, unit='microseconds'))) # TypeError: value must be an integer or timedelta

Thanks,
Gabriel

[cpcloud]

There are a couple mistakes in your example:

1. You didn't import _ from Ibis
2. You're dividing by 10**6 to convert seconds to microseconds when you should be multiplying.

There's also a bunch of unnecessary timedelta -> float conversion and list creation. You can do all that with numpy.

Finally, the solution to your actual problem is to cast to an interval with desired units.

Here's a cleaned up script that shows how to achieve what you want:

import numpy as np
import pandas as pd

from ibis.interactive import *

td_list = np.random.randint(5000, 30000, 60) / 10_000
timestamps = pd.date_range(start="2021-01-01 00:00:00", periods=60, freq="180s")

tbl = ibis.memtable({"timestamp": timestamps, "offset_s": td_list})
res = tbl.mutate(tsagg=_.timestamp + (_.offset_s * 1e6).cast("interval('us')"))
print(res)

Running that with python script.py:

❯ python script.py
┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ timestamp           ┃ offset_s ┃ tsagg                      ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │ float64  │ timestamp                  │
├─────────────────────┼──────────┼────────────────────────────┤
│ 2021-01-01 00:00:00 │   0.6446 │ 2021-01-01 00:00:00.644600 │
│ 2021-01-01 00:03:00 │   1.0700 │ 2021-01-01 00:03:01.070000 │
│ 2021-01-01 00:06:00 │   1.6959 │ 2021-01-01 00:06:01.695900 │
│ 2021-01-01 00:09:00 │   1.6631 │ 2021-01-01 00:09:01.663100 │
│ 2021-01-01 00:12:00 │   0.8724 │ 2021-01-01 00:12:00.872400 │
│ 2021-01-01 00:15:00 │   1.8296 │ 2021-01-01 00:15:01.829600 │
│ 2021-01-01 00:18:00 │   2.7116 │ 2021-01-01 00:18:02.711600 │
│ 2021-01-01 00:21:00 │   1.5969 │ 2021-01-01 00:21:01.596900 │
│ 2021-01-01 00:24:00 │   0.8388 │ 2021-01-01 00:24:00.838800 │
│ 2021-01-01 00:27:00 │   0.7018 │ 2021-01-01 00:27:00.701800 │
│ …                   │        … │ …                          │
└─────────────────────┴──────────┴────────────────────────────┘

[jogam]

@cpcloud -- thanks a ton for detailed answer! cast to an interval with desired units. does the trick :)