Importing excel files with time cells format

Question

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.

score 1 · Answer 1 · answered Dec 28 '21 at 13:57

1

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")

answered Dec 28 '21 at 13:57

Oliver

8,169
3
15
37

I have an error with the " \ " in "df <- bind_rows(lapply(file.list, \(file){ df <- read.transposed.xlsx(df) df[['id']] <- file }))" – Virginie Dec 28 '21 at 14:02
If your R version is prior to 4.1.0 you will need to replace `\(...)` with `function(...)` (arguments = `...`). Eg. `\(file)` changes to `function(file)`. – Oliver Dec 28 '21 at 14:03
It may be beneficial to add `col_types = "text"` to `read_xlsx` (untested). – Oliver Dec 28 '21 at 14:05
Sorry, what should "file.list" represent, I have an error message : Error in lapply(file.list, function(file) { : objet 'file.list' introuvable – Virginie Dec 28 '21 at 14:08
`file.list <- list.files(pattern='*.xlsx',recursive = TRUE)`. – Oliver Dec 28 '21 at 14:10
oh ok, sorry I'm not a big expert in R.... I still have an error message: Erreur : `path` must be a string – Virginie Dec 28 '21 at 14:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/240499/discussion-between-virginie-and-oliver). – Virginie Dec 28 '21 at 14:18

score 0 · Answer 2 · answered Dec 28 '21 at 13:42

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.

Importing excel files with time cells format

2 Answers2