0

I´m new to R ... maybe you can help me with this. When I import my dataset ba_data from Excel into R, the columns STARTED and LASTDATA show values that don´t exist in my excel sheet. R reads those columns as characters, but what is shown in the table in R doesn´t even make sense and doesn´t look like the numbers from excel. In excel they are shown as dates: y/m/d /h/min/sec. I tried to convert them into dates with the following code. If I run the code, everything turns into NA.

#I used this to import my datafile               
ba_data <- read_excel(choose.files(), na = "-9")

#tried to convert STARTED and LASTDATA to date
ba_data$STARTED <- anytime::anydate(ba_data$STARTED)
ba_data$LASTDATA <- anytime::anydate(ba_data$LASTDATA)
Anna
  • 1
  • 3
    `y/m/d /h/min/sec` isn't a date. It's a datetime. You say you get data in R that wasn't in yourExcel file. But you don't show us either. It's going to be hard to help you without that sort of information. But welcome to SO all the same. You maximise your chance of getting a useful answer if you provide a minimal reproducible example. [This post](https://stackoverflow.com/help/minimal-reproducible-example) may help. – Limey Jul 23 '22 at 10:35

1 Answers1

0
ba_data[c("STARTED","LASTDATA")] <- 
  lapply(ba_data[c("STARTED","LASTDATA")], as.POSIXct,
         format = "%Y/%m/%d /%H/%M/%S")

will convert both columns to POSIXct (timestamp).

If you need them to be Dates (losing the time information), then

  1. follow the above with

    ba_data[c("STARTED","LASTDATA")] <- lapply(ba_data[c("STARTED","LASTDATA")], as.Date)

  2. replace the above with

    ba_data[c("STARTED","LASTDATA")] <-
  lapply(ba_data[c("STARTED","LASTDATA")],
         function(z) as.Date(as.POSIXct(x, format = "%Y/%m/%d /%H/%M/%S")))

  3. truncate the string before convertion, replacing the above with

    ba_data[c("STARTED","LASTDATA")] <-
  lapply(ba_data[c("STARTED","LASTDATA")],
         function(z) as.Date(sub(" .*", "", z)))

I'm inferring by y/m/d you mean 4-digit years. If it's 2-digit years, change all %Y above to lower-case %y.

Untested.

r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Thanks a lot! Yes, by y/m/d I mean 4-digit years. However, both ways didn´t work. Maybe something went wrong when importing the data? – Anna Jul 23 '22 at 11:42
  • 1
    By the way, welcome to SO! If you have not already, please take the [tour] (suggested by a non-reproducible question, though this is an above-average first-question :-). To get more-targeted help, as suggested in the tour and in https://stackoverflow.com/q/5963269, [mcve], and https://stackoverflow.com/tags/r/info, sample data is really really helpful; please read about using `dput` or similar for sharing sample data. Thanks! – r2evans Jul 23 '22 at 11:48