Formating xts date & time X2020.01.06.06.00.00

Question

I have an xts table all.transactions showing:

structure(list(Quantity = c(0, 162000, 149000, -149000)), row.names = c("X2020.01.06.06.00.00", 
"X2020.01.10.15.00.00", "X2020.02.03.15.00.00", "X2020.02.03.15.00.00.1"
), class = "data.frame")

I noticed that the index of XTS table would tend to change from 2020-01-06 06:00:00 to X2020.01.06.06.00.00 when there are two record with the same time.

Is there a quick way to format/convert it back to normal (2020-01-06 06:00:00) please?

reproducible example please. How do you get to the xts table? aka what is the input? — phiver, Mar 16 '20 at 10:38
Can you post sample data in `dput` format? Please edit **the question** with the output of `dput(all.transactions)`. Or, if it is too big with the output of `dput(head(all.transactions, 20))`. — Rui Barradas, Mar 16 '20 at 10:44
Hi all, apologies, I didnt know about that dput tool, just discovered, thank you. — Rene Chan, Mar 16 '20 at 10:58
Duplicate times in time series don't make sense, what do you want to happen with duplicates? — jay.sf, Mar 16 '20 at 11:21

jay.sf · Accepted Answer · 2020-03-16T11:34:05.850

The row names are stored in the attributes of tsx named "row.names" and accessible with attr(). So one way is to format these attributes as time format.

attr(tsx, "row.names") <- as.character(strptime(attr(tsx, "row.names"), 
                                                format="X%Y.%m.%d.%H.%M.%S"))

The problem, though, is that time series with duplicate row names are not valid. But maybe this solution is applicable to your real data.

Data

tsx <- structure(list(Quantity = c(0, 162000, 149000, -149000)), row.names = c("X2020.01.06.06.00.00", 
"X2020.01.10.15.00.00", "X2020.02.03.15.00.00", "X2020.02.03.15.00.00.1"
), class = "data.frame")

Formating xts date & time X2020.01.06.06.00.00

1 Answers1