rcompareexport-to-excel

How to export values from R to Excel, from a table I generated comparing 2 data frames using arsenal package?


I am trying to build a report with comparisons from 2 data frames. I am using the comparedf from the arsenal package. The problem is that when I export the results I generate from this package, the excel file with my results are reported as missing.

Here is the example, suppose I have the following data frames:

df1 <- data.frame(id = c("a", "b", "c", "d","e"),
                  var = c(1, 2, 3, 4, 5),
                  var2 = c(1,2,3,4,5))
df2 <- data.frame(id = c("a", "b", "c", "d","e"),
                  var =c(1,3,4,2,5),
                  var2 = c(1,2,4,3,5))

Then using comparedf I am generating another data frame with the comparison of non-matching values:

summary(comparedf(df1, df2, by="id"))

Table: Differences detected

var.x   var.y   id   values.x   values.y    row.x   row.y
------  ------  ---  ---------  ---------  ------  ------
var     var     b    2          3               2       2
var     var     c    3          4               3       3
var     var     d    4          2               4       4
var2    var2    c    3          4               3       3
var2    var2    d    4          3               4       4

I capture this table with the following codes:

vec1 <- summary(comparedf(df1, df2, by="id"))
diftable <- as.data.frame(vec1$diffs.table)

And I export the table as an excel file:

library(writexl)
write_xlsx(diftable, "example.xlsx")

However, this is how the values appear in the excel file:

enter image description here

I don't understand why is this happening? Why is the export turning values into NAs?


Solution

  • The df you retrieved from vec1$diffs.table has two varaibles that are in fact nested lists. With {tidyr}, try:

    library(tidyr)
    diftable <- vec1$diffs.table |> unnest(cols = c(values.x, values.y))