create new column based on delayed datetime data-CodePudding

I have a data table of the value of a product at each time stamp (interval 1 min). I would like to create a third column that returns the value after 2 min. May I know how to do it? Thanks

                  dateTime           value
0      2021-06-01 00:00:00  37253.82000000 
1      2021-06-01 00:01:00  37124.60000000
2      2021-06-01 00:02:00  37191.60000000
3      2021-06-01 00:03:00  37451.48000000
4      2021-06-01 00:04:00  37598.43000000

Expected table :

                  dateTime           value        dateTime(2min)       value(2min)
0      2021-06-01 00:00:00  37253.82000000   2021-06-01 00:02:00    37191.60000000
1      2021-06-01 00:01:00  37124.60000000   2021-06-01 00:03:00    37451.48000000
2      2021-06-01 00:02:00  37191.60000000   2021-06-01 00:04:00    37598.43000000
3      2021-06-01 00:03:00  37451.48000000   2021-06-01 00:05:00
4      2021-06-01 00:04:00  37598.43000000   2021-06-01 00:06:00

CodePudding user response：

If your dateTime column is evenly spaced, you can use:

out = df.join(df.set_index('dateTime').shift(freq='2T').shift(-2) 
                .reset_index().add_suffix('(2min)'))
print(out)

# Output:
             dateTime     value      dateTime(2min)  value(2min)
0 2021-06-01 00:00:00  37253.82 2021-06-01 00:02:00     37191.60
1 2021-06-01 00:01:00  37124.60 2021-06-01 00:03:00     37451.48
2 2021-06-01 00:02:00  37191.60 2021-06-01 00:04:00     37598.43
3 2021-06-01 00:03:00  37451.48 2021-06-01 00:05:00          NaN
4 2021-06-01 00:04:00  37598.43 2021-06-01 00:06:00          NaN