ropenxlsx

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

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

Solution

  • The "solution" I posted some months ago to write plain numbers as formulas with the Excel formula =VALUE([numeric data]) was really ugly.

    In the meantime I found it's possible to write plain numbers as formulas in a clean way and simply via writeData by specifying the class formula.

    A related openxlsx2's Github discussion can be found here (it's related to openxlsx2 but it works the same for the two sister packages openxlsx and openxlsx2): https://github.com/JanMarvin/openxlsx2/discussions/1274

    Full code:

    Vector_LabelLengthAndCircuit <- unlist(lapply(X = names(AllCircuits[[Transaction.Type]][["Circuits"]]), FUN = function(LabelLength){
    c(paste0('"', LabelLength, '"'), '', unlist(lapply(X = names(AllCircuits[[Transaction.Type]][["Circuits"]][[LabelLength]]), FUN = function(LabelCircuit){
        VectorCircuit <- AllCircuits[[Transaction.Type]][["Circuits"]][[LabelLength]][[LabelCircuit]]
        VectorCircuit <- rep(VectorCircuit, each = 2)
        VectorCircuit <- VectorCircuit[-1]
        VectorCircuit <- VectorCircuit[-length(VectorCircuit)]
        c(paste0('"', LabelCircuit, '"'), '', VectorCircuit)
        })))
}))

Matrix_LabelLengthAndCircuit <- as.data.frame(matrix(data = Vector_LabelLengthAndCircuit, ncol = 2, byrow = TRUE))
lapply(X = seq_along(colnames(Matrix_LabelLengthAndCircuit)), function(col){class(Matrix_LabelLengthAndCircuit[, col]) <<- c(class(Matrix_LabelLengthAndCircuit[, col]), "formula")})
writeData(wb = Workbook_Individual, sheet = sheetName_Temp, x = Matrix_LabelLengthAndCircuit, startRow = StartingRow, startCol = StartingColumn, colNames = FALSE)