add a datetime element between two other datetime elements

user_5264552 0
Viewed 122

I have a df with a bit low sampling rate. I want to interpolate the NaN values in value column. My problem is the 'ts' column. My samples are not evenly spaced, and I have trouble trying to use timedelta to create the 'ts' value between the samplings.

Example, df.head(4):

  'ts'                             'value'
0 PS157 2019-12-17 09:59:20.917000 74.778099 
1 PS157 2019-12-17 00:00:00 NaN 
2 PS157 2019-12-17 09:59:31.918000 176.458374
3 PS157 2019-12-17 00:00:00 NaN

I have tried to loop through and do like this:

a= PS157['ts'].iloc[i]
b= PS157['ts'].iloc[i+2]
c= (b-a).total_seconds()
d= c/2.0
PS157['ts'].iloc[i+1]= PS157['ts'].iloc[i] + np.timedelta64(d, 's')

But this results in:

ValueError: Could not convert object to NumPy timedelta

I first tried with a 0 value, but then dtypes said 'ts' column was object, not dateime64. So I created a generic 'ts' value for all zeros, and the ran pd.to_datetime. dyptes then say dateime64, but I still get 'could not convert object to NumPy timedelta.

I read somewhere you can't interpolate datetime, so I thought I had to solve it with this workaround, but no luck.

Any good ways to solve this? Thanks!

pandas datetime python
1 Answers

The error is as simple as passing the correct arguments to the constructor. The docs https://numpy.org/doc/stable/reference/arrays.datetime.html#datetime-units specify "The length of the span is the range of a 64-bit integer times the length of the date or unit." You gave it a float where it expected an integer.

Or looking at the source for inspiration, we notice it can take an integer, or a datetime object:

class timedelta64(generic):
    def __init__(
        self,
        __value: Union[None, int, _CharLike, dt.timedelta, timedelta64] = ...,
        __format: Union[_CharLike, Tuple[_CharLike, _IntLike]] = ...,
    ) -> None: ...

Since your data has something close to milliseconds, I'll go with that:

import numpy as np
seconds = (31.918000 - 20.917000) / 2.0
np_diff1 = np.timedelta64(int(seconds * 1000000), 'ms')

Here it is with a datetime.timedelta, which does accept a float:

import numpy as np
from datetime import timedelta
seconds = (31.918000 - 20.917000) / 2.0
np_diff2 = np.timedelta64(timedelta(seconds=seconds))
user_17665440
Related