CODEWITHSUNDEEP
rdatetimetimestampformat
Hawky
Hawky

Reputation: 59

How to change a timestamp from character to datetime in R and add missing timestamps

I have got a data frame with different columns. One column is called "TIMESTAMP". Click on the column, I see it is a character, down below you see the content:

TIMESTAMP             Price
2003-06-20 09:19:00   5.25
2003-06-20 09:21:00   5.34
2003-06-20 09:22:00   5.43
2003-06-20 09:23:00   5.32

I'd like to convert the complete "TIMESTAMP"-column into as.POSIXct The reason for this is, that I want to afterwards add the missing minutes in the column as you see from row 1 to 2 there is the timestamp missing with 09:20:00. I want to add the missing minutes for 09:00:00 to 17:30:00, of course with the correct date too.

Let's call the dataframe data. I tried as.POSIXct(data$TIMESTAMP, format="%Y-%m-%d %H:%M:%S"), but I m unsure if it was succesfull, because the data in the dataframe didn't change.

Is there also a hint how to add the missing timestamps after getting the correct format?

Thanks for your help!

Upvotes: 0

Views: 764

Answers (1)

Alexandre Léonard
Alexandre Léonard

Reputation: 406

What you tried is correct as long as you assign the result back to the column of your data frame. This is what you should do:

> data$TIMESTAMP <- as.POSIXct(data$TIMESTAMP, format="%Y-%m-%d %H:%M:%S")

After that, the TIMESTAMP column will have the desired class:

> class(data$TIMESTAMP)
[1] "POSIXct" "POSIXt"

For completing your data frame with missing lines, you can first build a new data.frame with all the expected times and then merge it to your initial data. Bellow I'm using min and max to find the range of date-time, then I'm using seq.POSIXt by minute to generate the full set of date-time. The merge will then use the already existing price values from your initial data frame:

> data_full <- data.frame(TIMESTAMP = seq.POSIXt(from=min(data$TIMESTAMP), to=max(data$TIMESTAMP), by='min'))
> data_complete <- merge(data_full, data, all.x = T)

Upvotes: 2

Related Questions