Converting data frame into nested list of lists with some elements being named, and some unnamed

user_2725773 0
Viewed 56

I'm trying to convert my data frame into a probably complicated list of lists, but I'm struggling with finding the right commands, because some list parts have to be named, some have to be unnamed, some should be piped as a character vector, some as lists. This is all necessary to feed the data into an API.

So here's my input data:

df <- data.frame(id = c("xyz", "abc"),
                 country = c("DE", "UK"),
                 info = c("QC4_combined test", "QC4_combined test"),
                 QC4A_DE = c("test 1", NA),
                 QC4A_UK = c(NA, "test4"))

# which gives
#    id country              info QC4A_DE QC4A_UK
# 1 xyz      DE QC4_combined_test  test 1    <NA>
# 2 abc      UK QC4_combined_test    <NA>   test4

Now, I need to reshape the data so that it gives me the following:

rows = list(list(answers           = list(list(text     = c("test 1"),
                                               question = c("QC4A_DE")),
                                          list(text     = c(""),
                                               question = c("QC4A_UK"))),
                 auxiliary_columns = c("xyz", "DE", "QC4_combined test")),
            list(answers           = list(list(text     = c(""),
                                               question = c("QC4A_DE")),
                                          list(text     = c("test4"),
                                               question = c("QC4A_UK"))),
                 auxiliary_columns = c("abc", "UK", "QC4_combined test")))

# which gives
[[1]]
[[1]]$answers
[[1]]$answers[[1]]
[[1]]$answers[[1]]$text
[1] "test 1"

[[1]]$answers[[1]]$question
[1] "QC4A_DE"


[[1]]$answers[[2]]
[[1]]$answers[[2]]$text
[1] ""

[[1]]$answers[[2]]$question
[1] "QC4A_UK"



[[1]]$auxiliary_columns
[1] "xyz"               "DE"                "QC4_combined test"


[[2]]
[[2]]$answers
[[2]]$answers[[1]]
[[2]]$answers[[1]]$text
[1] ""

[[2]]$answers[[1]]$question
[1] "QC4A_DE"


[[2]]$answers[[2]]
[[2]]$answers[[2]]$text
[1] "test4"

[[2]]$answers[[2]]$question
[1] "QC4A_UK"



[[2]]$auxiliary_columns
[1] "abc"               "UK"                "QC4_combined test"

I've tried different things, among them the following. It seems to give me some list of lists, but again, it has not the right structure

df %>%
  mutate(across(all_of(input_names_1), function(x) if_else(is.na(x), "", x))) %>%
  pivot_longer(cols      = all_of(input_names_1),
               names_to  = "question",
               values_to = "text") %>%
  mutate(answers_raw = apply(across(c(question, text)), 1, as.list)) %>%
  select(-text) %>%
  pivot_wider(names_from  = question,
              values_from = answers_raw) %>%
  mutate(answers           = apply(across(input_names_1), 1, function(x) c(x)),
         auxiliary_columns = apply(across(all_of(input_names_2)), 1, function(x) list(x)),
         rows = apply(across(c(answers, auxiliary_columns)), 1, list))

My main problems are that I don't know when I need to use list and when c and how I can set or delete list names (dynamically).

The example of course is only a simplification. In my real-life case I have several thousands of rows in my data frame, so I can't build it up manually anymore like I did above with the rows object

tidyverse r list
2 Answers

There probably is a tidy way of doing that (I did not try), but what about some good old fashion R?

df <- data.frame(id = c("xyz", "abc"),
                 country = c("DE", "UK"),
                 info = c("QC4_combined_test", "QC4_combined_test"),
                 QC4A_DE = c("test 1", NA),
                 QC4A_UK = c(NA, "test4"))
df
#>    id country              info QC4A_DE QC4A_UK
#> 1 xyz      DE QC4_combined_test  test 1    <NA>
#> 2 abc      UK QC4_combined_test    <NA>   test4


process_row <- function(aux_cols, data, row) {
  
  process_col <- function(col) list(text = ifelse(is.na(col[[1]]), "", col[[1]]),
                                    question = colnames(col))
  
  d <- data[row, -c(aux_cols), drop = FALSE]
  
  output <- list()
  for (j in seq_len(ncol(d))) {
    output[[j]] <- process_col(d[, j, drop = FALSE])
  }
  list(answers = output,
       auxiliary_columns = unname(as.character(data[row, aux_cols])))
}

process_all <- function(aux_cols, data) {
  lapply(seq_len(nrow(data)), function(row) process_row(aux_cols = aux_cols, data = df, row))
}

process_all(1:3, df)
#> [[1]]
#> [[1]]$answers
#> [[1]]$answers[[1]]
#> [[1]]$answers[[1]]$text
#> [1] "test 1"
#> 
#> [[1]]$answers[[1]]$question
#> [1] "QC4A_DE"
#> 
#> 
#> [[1]]$answers[[2]]
#> [[1]]$answers[[2]]$text
#> [1] ""
#> 
#> [[1]]$answers[[2]]$question
#> [1] "QC4A_UK"
#> 
#> 
#> 
#> [[1]]$auxiliary_columns
#> [1] "xyz"               "DE"                "QC4_combined_test"
#> 
#> 
#> [[2]]
#> [[2]]$answers
#> [[2]]$answers[[1]]
#> [[2]]$answers[[1]]$text
#> [1] ""
#> 
#> [[2]]$answers[[1]]$question
#> [1] "QC4A_DE"
#> 
#> 
#> [[2]]$answers[[2]]
#> [[2]]$answers[[2]]$text
#> [1] "test4"
#> 
#> [[2]]$answers[[2]]$question
#> [1] "QC4A_UK"
#> 
#> 
#> 
#> [[2]]$auxiliary_columns
#> [1] "abc"               "UK"                "QC4_combined_test"

Created on 2021-04-13 by the reprex package (v1.0.0)

user_127412340

Thanks for the reply. Works fine, but looking at your code with several custom functions I at least wasn't wrong in assuming it's a more compliacted reshaping.

With some inspiration from above I finally also managed to do it differently with a tidyverse approach.

input_names_1 = c("QC4A_DE", "QC4A_UK")
input_names_2 = c("id", "country", "info")

df2 <- df %>%
  mutate(across(all_of(input_names_1), function(x) if_else(is.na(x), "", x))) %>%
  pivot_longer(cols      = all_of(input_names_1),
               names_to  = "question",
               values_to = "text") %>%
  mutate(answers_raw = apply(across(c(text, question)), 1, as.list)) %>%
  select(-text) %>%
  pivot_wider(names_from  = question,
              values_from = answers_raw) %>%
  mutate(answers = apply(across(all_of(input_names_1)), 1, function(x) list(unname(x))),
         answers = unlist(answers, recursive = FALSE)) %>%
  rowwise() %>%
  mutate(auxiliary_columns = list(c_across(all_of(input_names_2)))) %>%
  ungroup() %>%
  mutate(rows = apply(across(c(answers, auxiliary_columns)), 1, list),
         rows = unlist(rows, recursive = FALSE))

rows_new <- df2$rows

identical(rows, rows_new)
# [1] TRUE
user_27257730
Related