Write lists with different lengths to XLSX

[dg.aragones]

Hello everyone,

I have lists of different lengths generated using R. As a simple example:

h25=seq(663.35,664.03,0.01);
h05=seq(663.49,664.05,0.01);

What I want to do is to write both lists to a XLSX file in the same sheet, but in two different columns.

I have tried using openxlsx::write.xlsx(...,append=TRUE), but it overwrites the file, not keeping the first column. I have tried also to generate a data frame, but I haven't been able as the lists have different lengths.

Any idea how could I do this?

Thank you in advance for any help!

[FJCC]

This seems to work:

library(openxlsx)
h25=seq(663.35,664.03,0.01);
h05=seq(663.49,664.05,0.01)
wb <- createWorkbook()

addWorksheet(wb, sheetName = "Sheet1")
writeData(wb, "Sheet1", h25, startCol = 1, startRow = 1,  
          colNames = FALSE, rowNames = FALSE)
writeData(wb, "Sheet1", h05, startCol = 2, startRow = 1,  
          colNames = FALSE, rowNames = FALSE)
saveWorkbook(wb, "Dummy.xlsx", overwrite = TRUE)

[andresrcs]

You could also match the length of both vectors by filling the shortest with NA and merge them as a data frame

h25=seq(663.35,664.03,0.01)
h05=seq(663.49,664.05,0.01)
length(h05) <- length(h25)
cbind(h25,h05)
#>          h25    h05
#>  [1,] 663.35 663.49
#>  [2,] 663.36 663.50
#>  [3,] 663.37 663.51
#>  [4,] 663.38 663.52
#>  [5,] 663.39 663.53
#>  [6,] 663.40 663.54
#>  [7,] 663.41 663.55
#>  [8,] 663.42 663.56
#>  [9,] 663.43 663.57
#> [10,] 663.44 663.58
#> [11,] 663.45 663.59
#> [12,] 663.46 663.60
#> [13,] 663.47 663.61
#> [14,] 663.48 663.62
#> [15,] 663.49 663.63
#> [16,] 663.50 663.64
#> [17,] 663.51 663.65
#> [18,] 663.52 663.66
#> [19,] 663.53 663.67
#> [20,] 663.54 663.68
#> [21,] 663.55 663.69
#> [22,] 663.56 663.70
#> [23,] 663.57 663.71
#> [24,] 663.58 663.72
#> [25,] 663.59 663.73
#> [26,] 663.60 663.74
#> [27,] 663.61 663.75
#> [28,] 663.62 663.76
#> [29,] 663.63 663.77
#> [30,] 663.64 663.78
#> [31,] 663.65 663.79
#> [32,] 663.66 663.80
#> [33,] 663.67 663.81
#> [34,] 663.68 663.82
#> [35,] 663.69 663.83
#> [36,] 663.70 663.84
#> [37,] 663.71 663.85
#> [38,] 663.72 663.86
#> [39,] 663.73 663.87
#> [40,] 663.74 663.88
#> [41,] 663.75 663.89
#> [42,] 663.76 663.90
#> [43,] 663.77 663.91
#> [44,] 663.78 663.92
#> [45,] 663.79 663.93
#> [46,] 663.80 663.94
#> [47,] 663.81 663.95
#> [48,] 663.82 663.96
#> [49,] 663.83 663.97
#> [50,] 663.84 663.98
#> [51,] 663.85 663.99
#> [52,] 663.86 664.00
#> [53,] 663.87 664.01
#> [54,] 663.88 664.02
#> [55,] 663.89 664.03
#> [56,] 663.90 664.04
#> [57,] 663.91 664.05
#> [58,] 663.92     NA
#> [59,] 663.93     NA
#> [60,] 663.94     NA
#> [61,] 663.95     NA
#> [62,] 663.96     NA
#> [63,] 663.97     NA
#> [64,] 663.98     NA
#> [65,] 663.99     NA
#> [66,] 664.00     NA
#> [67,] 664.01     NA
#> [68,] 664.02     NA
#> [69,] 664.03     NA

^{Created on 2019-11-13 by the reprex package (v0.3.0.9000)}

[dg.aragones]

Thank you for your solution! Nice trick.

In my case, I have 12 different lists of data. Any way to automatically find the longest one and equal the other lengths?

[andresrcs]

Very likely there is a more elegant solution but this works

library(tidyverse)

h25 <- seq(663.35,664.03,0.01)
h05 <- seq(663.49,664.05,0.01)

vector_list <- ls(pattern = "^h") %>% set_names()

longest <- max(map_int(vector_list, ~length(eval(as.name(.x)))))

map_dfc(vector_list, ~{
    temp <- eval(as.name(.x))
    length(temp) <- longest
    temp
})
#> # A tibble: 69 x 2
#>      h05   h25
#>    <dbl> <dbl>
#>  1  663.  663.
#>  2  664.  663.
#>  3  664.  663.
#>  4  664.  663.
#>  5  664.  663.
#>  6  664.  663.
#>  7  664.  663.
#>  8  664.  663.
#>  9  664.  663.
#> 10  664.  663.
#> # … with 59 more rows