1

I have the following data:

df <- data.frame(id = c(0101, 1011, 2301), flat = c(1, 11, 1), buid = c(1, 10, 23), X2019.12.31.23.59.59 = c(14.123, 12.53, 8.53), X2020.01.31.23.59.59 = c(15.03, 14.63, 9.53), X30.04.2019.23.59 = c(21.03, 17.67, 7.27))

I need to arrange the columns in order of the date that is written in the name of the columns.

GrBa
  • 381
  • 1
  • 9

1 Answers1

2

A general solution would be to separate date and non-date columns, sort the date columns according to this answer and combine them.

library(lubridate)
date_cols <- grep('X\\d+\\.\\d+\\d+', names(df), value = TRUE)
non_date_cols <- grep('X\\d+\\.\\d+\\d+', names(df), value = TRUE, invert = TRUE)

date_cols <- date_cols[order(as.Date(parse_date_time(sub('^X', '', 
                       date_cols), c('YmdHMS', 'dmyHM'))))]
df[c(non_date_cols, date_cols)]

To keep only unique date columns we can do :

cols <- as.Date(parse_date_time(sub('^X', '', date_cols), c('YmdHMS', 'dmyHM')))
df[c(non_date_cols, date_cols[!duplicated(cols)])]
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • I like the first solution, but it was not effective. I used a more general solution. After I ordered the data, it turned out that some columns (dates) are duplicated. How do you delete them to keep unique data? – GrBa Jul 13 '20 at 09:03
  • You mean you have column with same names? Perhaps, you can use `sort` and then `unique` instead of `order` to keep unique dates. – Ronak Shah Jul 13 '20 at 09:08
  • Column names may be different. For me, the date (without time) hidden in the column name is important. For example: [24] "X2019.11.30.23.59.59" [25] "X30.11.2019.23.59" – GrBa Jul 13 '20 at 09:12
  • But what if two columns with same date have different values? You'll lose information in one of the columns if you keep only one column and which column would you keep? – Ronak Shah Jul 13 '20 at 09:14
  • More interesting is the column with less NA. But the solution maintaining the first column is also very good. – GrBa Jul 13 '20 at 09:16
  • See updated answer to get the first column of each date. – Ronak Shah Jul 13 '20 at 09:56
  • Fantastic Tips. Thank you for your solution and your time. – GrBa Jul 13 '20 at 10:27