Importing excel files with time cells format-CodePudding

I have a problem when I import my excel file in R. It convert the time cells in another format and I don't know what to do to change that. Here is my excel file:

And here is what I obtain in R:

This is the code I used to import my files:

file.list <- list.files(pattern='*.xlsx',recursive = TRUE)
file.list <- setNames(file.list, file.list)
df.list <- lapply(file.list, read_xlsx, skip=20)
Actibrut <- bind_rows(df.list, .id = "id")

Do you know what is wrong? Thank you.

CodePudding user response：

Your data is transposed in excel. This is a problem as data.frames are column-major. Using this answer we can fix this

read.transposed.xlsx <- function(file, sheetIndex, as.is = TRUE) {
  df <- read_xlsx(file, sheet = sheetIndex, col_names = FALSE)
  dft <- as.data.frame(t(df[-1]), stringsAsFactors = FALSE) 
  names(dft) <- df[[1]] 
  rownames(dft) <- NULL
  dft <- as.data.frame(lapply(dft, type.convert, as.is = as.is))
  return(dft)            
}
df <- bind_rows(lapply(file.list, \(file){
  df <- read.transposed.xlsx(df)
  df[['id']] <- file
}))

Afterwards you'll have to convert the columns appropriately, for example (note origin may depend on your machine):

df$"Woke up" <- as.POSIXct(df$"Woke up", origin = '1899-12-31')
# If it comes in as "hh:mm:ss" use
library(lubridate)
df$"Woke up" <- hms(df$"Woke up")

CodePudding user response：

There are a couple of things you need to do. First, it appears that your data is transposed. Meaning, that your first row looks like variable names and columns contain data. You can easily transpose your data before you import into Rstudio. This will address the (..1, ..2) variable names you see when you import the data.

Secondly, import the date columns as strings.

The command: df.list <- lapply(file.list, read_xlsx, skip=20) uses read_xlsx function. I think you need to explicitly specify the column variable type or import them as string.

Then you can use stringr package (or any other package) to convert strings to date variables. Also consider providing code that you have used.