Struggling to create a unique DatetimeIndex (is_unique_asi8)

[jambudipa]

I have a time index in my dataframe, but I have been getting non-unique problems on this index.

The time comes from the database in UNIX Epoch with nanosecond precision, so I am artificially adjusting it like this:

db = Database()
df, last_trade_time = db.fetch_trades()

# Convert the time column to nanoseconds and add a sequence number for trades
df['time_ns'] = pd.to_datetime(df['time'], unit='s').values.astype(np.int64) + \
                np.arange(len(df)) % (10 ** 9)
df.set_index('time_ns', inplace=True)

# Convert the time_ns column to a DatetimeIndex with nanosecond precision
df.index = pd.to_datetime(df.index, unit='ns')

dataset = PandasDataset(df, target="price")

This has got me over the unique hurdle, but now in /pandas/tseries/frequencies.py, the following returns None, which is causing the frequency calculation to fall over:

if not self.is_unique_asi8:
    return None

I've only been doing Python a few weeks 🤣 so I'm not sure which bit I should be addressing. Should I be working to get this to be True, or am I thinking about creating this PandasDataset in the wrong way?

[jambudipa]

I should point out that the df index is now unique and, having read around .is_unique_asi8, it seems this is also an indicator of uniqueness. So why would one be unique and not the other?

More generally, why does the time series not support non-unique values for the index, which is a time index? It is perfectly possible (and valid it seems to me) for the time index to occur in different series in the dataset. Of course if it occurs more than once in the same series, that might be a problem.

[jambudipa]

I have posted about this issue here and here on stackoverflow. Pandas cannot determine a unique index in this code:

# pandas/tseries/frequencies.py
if not self.is_monotonic or not self.index._is_unique:
    return None

delta = self.deltas[0]
ppd = periods_per_day(self._creso)
if delta and _is_multiple(delta, ppd):
    return self._infer_daily_rule()

# Business hourly, maybe. 17: one day / 65: one weekend
if self.hour_deltas in ([1, 17], [1, 65], [1, 17, 65]):
    return "BH"

# Possibly intraday frequency.  Here we use the
# original .asi8 values as the modified values
# will not work around DST transitions.  See #8772
if not self.is_unique_asi8:
    return None

The first test, self.index._is_unique, passes fine; the second, not self.is_unique_asi8, fails, and returns None.