'R: Converting date/time format 0000-00-00T00:00:00Z to POSIXct

I have a dataset I exported as .csv from a dataframe in R. The dataset contains a date+time column, for which the format was set as POSIXct before exporting it as a .csv file. When I import the saved .csv file back into R, the format of the date+time column has changed into a character and looks as follows: 000-00-00T00:00:00Z (see below the head of part of my data).

ID  Timestamp               X_mean      X_min   X_max
1   2021-06-30T08:00:00Z    -0.4313333  -5.914  4.129
2   2021-06-30T08:00:01Z    -0.3817667  -5.641  4.082
3   2021-06-30T08:00:02Z    -0.2770667  -0.484  -0.109
4   2021-06-30T08:00:03Z    -0.3686667  -0.863  -0.148
5   2021-06-30T08:00:04Z    -0.2696667  -0.41   -0.102
6   2021-06-30T08:00:05Z    -0.4150000  -0.734  -0.145

I would really like to get the date+time back to a POSIXct variable if possible, so it looks like this again: YYYY-MM-DD HH:MM:SS, without the 'T' and the 'Z'.

I have tried to apply the solutions given here and here, but they do not seem to work or I am doing something wrong in changing the code to have it work for my dataframe. I only end up with NA's.

Is there a way I can change the format back to POSIXct?

rdatetimeformatposixct


Solution 1:[1]

We can use ymd_hms from lubridate

df1$Timestamp <- lubridate::ymd_hms(df1$Timestamp)

Solution 2:[2]

The anytime function of the anytime package was made for this: reliable and easy parsing of any (reasonable) time format whatever the input data type, with requiring a format. We can demo it:

Code

## re-create your data
dat <- read.table(text="ID  Timestamp               X_mean      X_min   X_max
1   2021-06-30T08:00:00Z    -0.4313333  -5.914  4.129
2   2021-06-30T08:00:01Z    -0.3817667  -5.641  4.082
3   2021-06-30T08:00:02Z    -0.2770667  -0.484  -0.109
4   2021-06-30T08:00:03Z    -0.3686667  -0.863  -0.148
5   2021-06-30T08:00:04Z    -0.2696667  -0.41   -0.102
6   2021-06-30T08:00:05Z    -0.4150000  -0.734  -0.145", header=TRUE)

## add a Datetime column
dat$datetime <- anytime::anytime(dat$Timestamp)

## look at dat
dat

Output

> dat <- read.table(text="ID  Timestamp               X_mean      X_min   X_max
+ 1   2021-06-30T08:00:00Z    -0.4313333  -5.914  4.129
+ 2   2021-06-30T08:00:01Z    -0.3817667  -5.641  4.082
+ 3   2021-06-30T08:00:02Z    -0.2770667  -0.484  -0.109
+ 4   2021-06-30T08:00:03Z    -0.3686667  -0.863  -0.148
+ 5   2021-06-30T08:00:04Z    -0.2696667  -0.41   -0.102
+ 6   2021-06-30T08:00:05Z    -0.4150000  -0.734  -0.145", header=TRUE)
> dat$datetime <- anytime::anytime(dat$Timestamp)
> dat
  ID            Timestamp    X_mean  X_min  X_max            datetime
1  1 2021-06-30T08:00:00Z -0.431333 -5.914  4.129 2021-06-30 08:00:00
2  2 2021-06-30T08:00:01Z -0.381767 -5.641  4.082 2021-06-30 08:00:01
3  3 2021-06-30T08:00:02Z -0.277067 -0.484 -0.109 2021-06-30 08:00:02
4  4 2021-06-30T08:00:03Z -0.368667 -0.863 -0.148 2021-06-30 08:00:03
5  5 2021-06-30T08:00:04Z -0.269667 -0.410 -0.102 2021-06-30 08:00:04
6  6 2021-06-30T08:00:05Z -0.415000 -0.734 -0.145 2021-06-30 08:00:05
>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 akrun
Solution 2 Dirk Eddelbuettel

Related Questions