Search code examples
rxlconnect

Convert list of 1-row 1-column data frames to list of lists

Context

I'm using XLConnect to get a bunch of Excel named ranges from a spreadsheet. When doing this, XLConnect returns a list of data.frames, even when the named range is a single cell.

So I have a list of data frames like so, each containing a single row and single column, with values like Mike.Snow - 25/02/2013 2:44:00 PM:

enter image description here

        data <- structure(list(BW_SPUserNameTime = structure(list(Col1 = "Mike.Snow - 25/02/2013 2:44:00 PM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateAMCars = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:40:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateMJCars = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:39:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateOtherCD2CTrains = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:36:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateSAP = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:34:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateTrainsMineToPort = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:38:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateWACLATrains = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:36:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame"), UserNameDateYACLATrains = structure(list(
    Col1 = "John.Doe  -  25/02/2013 10:36:00 AM"), .Names = "Col1", row.names = c(NA, 
-1L), class = "data.frame")), .Names = c("BW_SPUserNameTime", 
"UserNameDateAMCars", "UserNameDateMJCars", "UserNameDateOtherCD2CTrains", 
"UserNameDateSAP", "UserNameDateTrainsMineToPort", "UserNameDateWACLATrains", 
"UserNameDateYACLATrains"))

Question

How do I turn this into a list of lists? (With the following properties)

  1. Inner list is a named list where Mike.Snow - 25/02/2013 2:44:00 PM is strsplit into Mike.Snow and 2013-02-25 14:44:00, with names like User and Time.
  2. The date portion is of class POSIXlt

In other words, the result should look like this...

> data.list <- list(BW_SPUserNameTime  = list(
+                                           User = c("Mike.Snow"), 
+                                           Time = as.POSIXlt("2013-02-25 14:44:00")),
+                    UserNameDateAMCars = list(
+                                           User = c("John.Doe") , 
+                                           Time = as.POSIXlt("2013-02-25 10:40:00"))
+                   )
> data.list
$BW_SPUserNameTime
$BW_SPUserNameTime$User
[1] "Mike.Snow"

$BW_SPUserNameTime$Time
[1] "2013-02-25 14:44:00"


$UserNameDateAMCars
$UserNameDateAMCars$User
[1] "John.Doe"

$UserNameDateAMCars$Time
[1] "2013-02-25 10:40:00"


> sapply(data.list[[1]], class)
$User
[1] "character"

$Time
[1] "POSIXlt" "POSIXt"
Solution

  • Here is an approach using lapply and a self written function to do your processing

    process <- function(x, format = '%d/%m/%Y %I:%M:%S %p'){
  foo <- as.list(unlist(strsplit(unlist(x), split = '\\s+-\\s+')))
  foo[[2]] <- as.POSIXlt(foo[[2]], format = format)
  names(foo) <- c('user', 'time')
  foo
}

zz <- lapply(data,process)
str(zz)
# List of 8
# $ BW_SPUserNameTime           :List of 2
# ..$ user: chr "Mike.Snow"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 14:44:00"
# $ UserNameDateAMCars          :List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:40:00"
# $ UserNameDateMJCars          :List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:39:00"
# $ UserNameDateOtherCD2CTrains :List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:36:00"
# $ UserNameDateSAP             :List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:34:00"
# $ UserNameDateTrainsMineToPort:List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:38:00"
# $ UserNameDateWACLATrains     :List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:36:00"
# $ UserNameDateYACLATrains     :List of 2
# ..$ user: chr "John.Doe"
# ..$ time: POSIXlt[1:1], format: "2013-02-25 10:36:00"