R Populate a datetime column starting from last row-CodePudding

I have a dataframe that I need to add a column of datetime to. It is recording water levels every hour for 2 years. The original data frame has the wrong dates and times. i.e. the dates say 2015 instead of 2020. The date and month are also wrong. I do not know the original start date and time. However, I know the date and time of the very last recording (28-03-2022 14:00:00). I need to calculate a column from the bottom to the top to figure out the original start date.

Current Code

I have this code which populates the dates from a known start date (i.e. top down), but I want to population the data from down up. Is these a way to alter this or another solution??

# recalculate date to correct date

# set start dates
startDate5 <- as.POSIXct("2020-03-05 17:00:00")
startDateMere <- as.POSIXct("2020-07-06 17:00:00")

# find length of dataframe to populate required rows.
len5 <- max(dataList$`HMB 5`$Rec)
lenMere <- max(dataList$`HM SSSI 4`$Rec)

# calculate new date column
dataList$`HMB 5`$DateTimeNew <- seq(startDate5, by='hour', length.out=len5)
dataList$`HM SSSI 4`$DateTimeNew <-seq(startDateMere, by='hour', length.out=lenMere)

Current dataframe - top 10 rows

structure(list(Rec = 1:10, DateTime = structure(c(1436202000, 
1436205600, 1436209200, 1436212800, 1436216400, 1436220000, 1436223600, 
1436227200, 1436230800, 1436234400), class = c("POSIXct", "POSIXt"
), tzone = "GMT"), Temperature = c(16.59, 16.49, 16.74, 17.14, 
17.47, 17.71, 18.43, 18.78, 19.06, 19.18), Pressure = c(1050.64, 
1050.86, 1051.28, 1051.56, 1051.48, 1051.2, 1051.12, 1050.83, 
1050.83, 1050.76), DateTimeNew = structure(c(1594051200L, 1594054800L, 
1594058400L, 1594062000L, 1594065600L, 1594069200L, 1594072800L, 
1594076400L, 1594080000L, 1594083600L), class = c("POSIXct", 
"POSIXt"), tzone = "")), row.names = c(NA, 10L), class = "data.frame")

Desired Output

This is what the desired output looks like: The date I know is correct for example is '2020-07-07 02:00:00' (e.g. value in 10th row, final column). And I need to figure out the rest of the column from this value.

NB: I do not actually know what the original start date is (2020-07-06 17:00:00) should be. Its just illustrative.

CodePudding user response：

Here's a sequence method:

startDateMere <- as.POSIXct("2020-07-06 17:00:00")
new_date = seq(startDateMere, length.out = nrow(data), by = "-1 hour")
data$result = rev(new_date)
data
#    Rec            DateTime Temperature Pressure         DateTimeNew              result
# 1    1 2015-07-06 17:00:00       16.59  1050.64 2020-07-06 12:00:00 2020-07-06 08:00:00
# 2    2 2015-07-06 18:00:00       16.49  1050.86 2020-07-06 13:00:00 2020-07-06 09:00:00
# 3    3 2015-07-06 19:00:00       16.74  1051.28 2020-07-06 14:00:00 2020-07-06 10:00:00
# 4    4 2015-07-06 20:00:00       17.14  1051.56 2020-07-06 15:00:00 2020-07-06 11:00:00
# 5    5 2015-07-06 21:00:00       17.47  1051.48 2020-07-06 16:00:00 2020-07-06 12:00:00
# 6    6 2015-07-06 22:00:00       17.71  1051.20 2020-07-06 17:00:00 2020-07-06 13:00:00
# 7    7 2015-07-06 23:00:00       18.43  1051.12 2020-07-06 18:00:00 2020-07-06 14:00:00
# 8    8 2015-07-07 00:00:00       18.78  1050.83 2020-07-06 19:00:00 2020-07-06 15:00:00
# 9    9 2015-07-07 01:00:00       19.06  1050.83 2020-07-06 20:00:00 2020-07-06 16:00:00
# 10  10 2015-07-07 02:00:00       19.18  1050.76 2020-07-06 21:00:00 2020-07-06 17:00:00