CODEWITHSUNDEEP
pythonpandasdatetime
Artem Trunov
Artem Trunov

Reputation: 1415

pandas shift and extrapolate datetime column

I have a dataframe with a 1 hour periodic DateTime column ts_start and a shifted by one column ts_end:

import pandas as pd
from datetime import datetime, timedelta
now = datetime.now()
d = pd.date_range(now, now + timedelta(hours=7), freq='h')

np.random.seed(seed=1111)
df = pd.DataFrame({'ts_start': d, 'col2': range(len(d))})
df = df.assign(ts_end=df['ts_start'].shift(-1))
print(df)

                    ts_start  col2                     ts_end
0 2019-05-17 16:51:23.630583     0 2019-05-17 17:51:23.630583
1 2019-05-17 17:51:23.630583     1 2019-05-17 18:51:23.630583
2 2019-05-17 18:51:23.630583     2 2019-05-17 19:51:23.630583
3 2019-05-17 19:51:23.630583     3 2019-05-17 20:51:23.630583
4 2019-05-17 20:51:23.630583     4 2019-05-17 21:51:23.630583
5 2019-05-17 21:51:23.630583     5 2019-05-17 22:51:23.630583
6 2019-05-17 22:51:23.630583     6 2019-05-17 23:51:23.630583
7 2019-05-17 23:51:23.630583     7                        NaT

and I'd like to fill NaT with the next hour value, i.e. 2019-05-18 00:51:23.630583

interpolate() or interpolate(method='time') do not do anything,

shift(-1, freq='h') produces:

NotImplementedError: Not supported for type RangeIndex

I am pretty sure there must be something simple to extend a datetime range further.

Upvotes: 1

Views: 1048

Answers (2)

Julia
Julia

Reputation: 317

Try this function: 

def fill_in_nat(row):

    if pd.isnull(row['ts_end']) == True:
        row['ts_end'] = row['ts_start']+timedelta(hours=1)
    else:
        pass
    return row

And then apply it to the DataFrame:

df = df.apply(lambda x: fill_in_nat(x), axis=1)

Upvotes: 1

piRSquared
piRSquared

Reputation: 294258

Add an offset to the shifted column

df.ts_end.fillna(df.ts_end.shift() + pd.offsets.Hour(1))

0   2019-05-17 08:10:39.380197
1   2019-05-17 09:10:39.380197
2   2019-05-17 10:10:39.380197
3   2019-05-17 11:10:39.380197
4   2019-05-17 12:10:39.380197
5   2019-05-17 13:10:39.380197
6   2019-05-17 14:10:39.380197
7   2019-05-17 15:10:39.380197
Name: ts_end, dtype: datetime64[ns]

Upvotes: 2

Related Questions