Is there in R a 2-dimension object that can contain mixed data types within one column across different rows?

Question

I have a list a that I need to transform to a data frame with 17 rows and 2 columns.

a <- list("01-step" = list("41 - 41" = c(41, 41), "7 - 7" = c(7, 7), "10 - 10" = c(10, 10)), "02-step" = list("41 - 58 - 41" = c(41, 58, 41), "78 - 41 - 78" = c(78, 41, 78), "102 - 23 - 102" = c(102, 23, 102)))

I created a function to transform a to the "transposed" version of this data frame target:

Vector_LabelLengthAndCircuit <- lapply(X = names(a), FUN = function(LabelLength){
    list(list(list(list(c(LabelLength, "")))), lapply(X = names(a[[LabelLength]]), FUN = function(LabelCircuit){
        VectorCircuit <- a[[LabelLength]][[LabelCircuit]]
        CircuitAsPairs <- VectorCircuit
        VectorCircuit <- c(VectorCircuit[1], rep(VectorCircuit[-length(VectorCircuit)][-1], each = 2), VectorCircuit[length(VectorCircuit)])
        CircuitAsPairs <- unname(split(VectorCircuit, ceiling(seq_along(VectorCircuit) / 2)))
        list(list(c(LabelCircuit, "")), CircuitAsPairs)
        }))
    })
Vector_LabelLengthAndCircuit <- unlist(unlist(unlist(unlist(Vector_LabelLengthAndCircuit, recursive = FALSE), recursive = FALSE), recursive = FALSE), recursive = FALSE)
Vector_LabelLengthAndCircuit <- data.table::rbindlist(list(Vector_LabelLengthAndCircuit))

I first get (transformed list):

Vector_LabelLengthAndCircuit <- unlist(unlist(unlist(unlist(Vector_LabelLengthAndCircuit, recursive = FALSE), recursive = FALSE), recursive = FALSE), recursive = FALSE)
Vector_LabelLengthAndCircuit
[[1]]
[1] "01-step" ""       

[[2]]
[1] "41 - 41" ""       

[[3]]
[1] 41 41

[[4]]
[1] "7 - 7" ""     

[[5]]
[1] 7 7

[[6]]
[1] "10 - 10" ""       

[[7]]
[1] 10 10

[[8]]
[1] "02-step" ""       

[[9]]
[1] "41 - 58 - 41" ""            

[[10]]
[1] 41 58

[[11]]
[1] 58 41

[[12]]
[1] "78 - 41 - 78" ""            

[[13]]
[1] 78 41

[[14]]
[1] 41 78

[[15]]
[1] "102 - 23 - 102" ""              

[[16]]
[1] 102  23

[[17]]
[1]  23 102

And then I get my "transposed" data frame target:

Vector_LabelLengthAndCircuit <- data.table::rbindlist(list(Vector_LabelLengthAndCircuit))
Vector_LabelLengthAndCircuit
 V1      V2 V3    V4 V5      V6 V7      V8           V9 V10 V11          V12 V13 V14            V15 V16 V17
1: 01-step 41 - 41 41 7 - 7  7 10 - 10 10 02-step 41 - 58 - 41  41  58 78 - 41 - 78  78  41 102 - 23 - 102 102  23
2:                 41        7         10                       58  41               41  78                 23 102

Data types are preserved, everything's fine:

str(Vector_LabelLengthAndCircuit)
Classes ‘data.table’ and 'data.frame':  2 obs. of  17 variables:
 $ V1 : chr  "01-step" ""
 $ V2 : chr  "41 - 41" ""
 $ V3 : num  41 41
 $ V4 : chr  "7 - 7" ""
 $ V5 : num  7 7
 $ V6 : chr  "10 - 10" ""
 $ V7 : num  10 10
 $ V8 : chr  "02-step" ""
 $ V9 : chr  "41 - 58 - 41" ""
 $ V10: num  41 58
 $ V11: num  58 41
 $ V12: chr  "78 - 41 - 78" ""
 $ V13: num  78 41
 $ V14: num  41 78
 $ V15: chr  "102 - 23 - 102" ""
 $ V16: num  102 23
 $ V17: num  23 102
 - attr(*, ".internal.selfref")=<externalptr> 

Within a data frame, each column only accepts one single data type, and not a mix of data types (e.g. character and numeric values), so if I transpose the previous data frame, numeric values will be automatically converted to character values, which I don't want.

Hence my initial question: Is there a specific kind of data frame object that could contain mixed data types within one column across different rows?

Ultimately, why do I need in the first place to transform the list a to a data frame with 17 rows and 2 columns? Because I want to write this 'data frame' to an Excel file in 2 columns across 17 rows with the function writeData of the package openxlsx that is (apparently) not able to write the first transformed list (only objects of types vector, matrix and data frame).

Answer 1

I found a solution (or rather workaround) but that's really horrible. Basically I use writeFormula (twice instead of once) instead of writeData. For numeric data I use the Excel formula =VALUE([numeric data]). For character data I use the Excel formula ="[character data]". I post below only the relevant rows of code:

Vector_LabelLengthAndCircuit <- unlist(unlist(unlist(unlist(Vector_LabelLengthAndCircuit, recursive = FALSE), recursive = FALSE), recursive = FALSE), recursive = FALSE)
Vector_LabelLengthAndCircuit <- lapply(Vector_LabelLengthAndCircuit, as.list) |> do.call(rbind, args = _) |> as.data.frame()
Vector_LabelLengthAndCircuit$V1 <- lapply(Vector_LabelLengthAndCircuit$V1, FUN = function(x){ifelse(is.numeric(x), paste0("VALUE(", x, ")"), paste0("\"", x, "\""))})
Vector_LabelLengthAndCircuit$V2 <- lapply(Vector_LabelLengthAndCircuit$V2, FUN = function(x){ifelse(is.numeric(x), paste0("VALUE(", x, ")"), paste0("\"", x, "\""))})
writeFormula(wb = Workbook_Individual, sheet = sheetName_Temp, x = unlist(Vector_LabelLengthAndCircuit$V1), startCol = StartingColumn, startRow = StartingRow)
writeFormula(wb = Workbook_Individual, sheet = sheetName_Temp, x = unlist(Vector_LabelLengthAndCircuit$V2), startCol = StartingColumn + 1, startRow = StartingRow)

[EDIT]: OK, I wrote too quickly that I found a solution: There is now another warning message on some Excel cells: 'Inconsistent Formula'... Fortunately it's possible to deactivate these warning messages in Excel (FILE --> Options --> Formulas --> Error checking rules: "Formulas inconsistent with other formulas in the region" to untick).

Answer 2

Since data frames are essentially groups of equal-length vectors, and vectors can only have one type, I'm not aware of any kind of data frame that would allow this.

But you could write the data to excel in a loop using the transposed list.

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "mysheet")

vl <- Vector_LabelLengthAndCircuit
excel_rows = ncol(vl)
for(r in seq_len(excel_rows)) {
  writeData(wb, sheet = "mysheet", t(vl[,..r]),
            startRow = r+1, startCol = "A", colNames = FALSE)
}
saveWorkbook(wb, "test_42.xlsx", overwrite = TRUE)
  

Result