Convert tibble to a list of lists

[xvalda]

Hi there,

I have an issue converting a tibble to a nested list. Apologies for the long description but I preferred to show the full context in case you would suggest a different approach.

General context: I need to send a collection of JSON files to translators that are more comfortable with Excel, then convert their translations back into JSON format.

My approach: import JSON (step 1) --> convert list to tibble (step 2) --> export as Excel, send to translators (step 3) --> import translation as tibble (step 4) --> convert tibble back to list format (step 5) --> convert list back to JSON (step 6)

I’m struggling with step 5 as I need a list with some items being nested lists.

pacman::p_load(jsonlite, tidyverse, janitor)

#Step 1 (not run) - read json file
## source_json_as_list <- fromJSON("source_doc.json")

#Step 1 bis - reprex data
(source_json_as_list <- list(Appointment = "Appointment", 
                            AuthError = list(AccessDenied = "The specified user is denied access to the restricted resource", 
                                             WrongContext = "The supplied context is incorrect", 
                                             WrongDomain = "The supplied domain is incorrect", 
                                             WrongUsernameOrPassword = "The supplied username or password is incorrect"),
                            Author = "Author"))
#> $Appointment
#> [1] "Appointment"
#> 
#> $AuthError
#> $AuthError$AccessDenied
#> [1] "The specified user is denied access to the restricted resource"
#> 
#> $AuthError$WrongContext
#> [1] "The supplied context is incorrect"
#> 
#> $AuthError$WrongDomain
#> [1] "The supplied domain is incorrect"
#> 
#> $AuthError$WrongUsernameOrPassword
#> [1] "The supplied username or password is incorrect"
#> 
#> 
#> $Author
#> [1] "Author"

#Step 2 - transform into tibble (two options)
(source_tibble_option1 <- source_json_as_list %>% unlist() %>% enframe())
#> # A tibble: 6 × 2
#>   name                              value                                       
#>   <chr>                             <chr>                                       
#> 1 Appointment                       Appointment                                 
#> 2 AuthError.AccessDenied            The specified user is denied access to the …
#> 3 AuthError.WrongContext            The supplied context is incorrect           
#> 4 AuthError.WrongDomain             The supplied domain is incorrect            
#> 5 AuthError.WrongUsernameOrPassword The supplied username or password is incorr…
#> 6 Author                            Author
source_tibble_option2 <- source_json_as_list %>% unlist() %>% enframe() %>% 
  separate(name, sep = "\\.", into = c(paste0("x", 1:10)), fill = "right") %>% remove_empty("cols")

#Step 3 (not shown) - export as excel & send to translator

#Step 4 - read excel received from translator - reprex data below with each of the 2 format options from step 2
(translated_tibble_option1 <- structure(list(name = c("Appointment", "AuthError.AccessDenied", 
                                             "AuthError.WrongContext", "AuthError.WrongDomain", "AuthError.WrongUsernameOrPassword", 
                                             "Author"), value = c("translation_1", "translation_2", 
                                                                  "translation_3", "translation_4", 
                                                                  "translation_5", "translation_6")), 
                               class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -6L)))
#> # A tibble: 6 × 2
#>   name                              value        
#>   <chr>                             <chr>        
#> 1 Appointment                       translation_1
#> 2 AuthError.AccessDenied            translation_2
#> 3 AuthError.WrongContext            translation_3
#> 4 AuthError.WrongDomain             translation_4
#> 5 AuthError.WrongUsernameOrPassword translation_5
#> 6 Author                            translation_6

(translated_tibble_option2 <- translated_tibble_option1 %>% 
  separate(name, sep = "\\.", into = c(paste0("x", 1:10)), fill = "right") %>% 
  remove_empty("cols"))
#> # A tibble: 6 × 3
#>   x1          x2                      value        
#>   <chr>       <chr>                   <chr>        
#> 1 Appointment <NA>                    translation_1
#> 2 AuthError   AccessDenied            translation_2
#> 3 AuthError   WrongContext            translation_3
#> 4 AuthError   WrongDomain             translation_4
#> 5 AuthError   WrongUsernameOrPassword translation_5
#> 6 Author      <NA>                    translation_6

#Step 5 - prepare list of lists format in view to export to JSON format
#this is part of what  I tried 
(translated_list_wrong_option1 <- translated_tibble_option1 %>% deframe() %>% as.list())
#> $Appointment
#> [1] "translation_1"
#> 
#> $AuthError.AccessDenied
#> [1] "translation_2"
#> 
#> $AuthError.WrongContext
#> [1] "translation_3"
#> 
#> $AuthError.WrongDomain
#> [1] "translation_4"
#> 
#> $AuthError.WrongUsernameOrPassword
#> [1] "translation_5"
#> 
#> $Author
#> [1] "translation_6"
#I also tried with translated_tibble_option2, many combinations of nest(), map(), map2() in combination with split() ...
#none of it worked 

#and this is what I want:
(translated_list_correct <- list(Appointment = "translation_1", 
                            AuthError = list(AccessDenied = "translation_2", 
                                             WrongContext = "translation_3", 
                                             WrongDomain = "translation_4", 
                                             WrongUsernameOrPassword = "translation_5"),
                            Author = "translation_6"))
#> $Appointment
#> [1] "translation_1"
#> 
#> $AuthError
#> $AuthError$AccessDenied
#> [1] "translation_2"
#> 
#> $AuthError$WrongContext
#> [1] "translation_3"
#> 
#> $AuthError$WrongDomain
#> [1] "translation_4"
#> 
#> $AuthError$WrongUsernameOrPassword
#> [1] "translation_5"
#> 
#> 
#> $Author
#> [1] "translation_6"

#step 6 - transform list to JSON, works fine providing that I have the correct object (translated_list_correct)
target_json <- toJSON(translated_list_correct, pretty = TRUE, auto_unbox = TRUE)

Many thanks for your help.

[nirgrahamuk]

my attempt

library(tidyverse)
library(rlang)

(split_1 <- translated_tibble_option2 %>% group_by(x1) %>% group_split())

outtry <- map(split_1,~{
  if(nrow(.x)==1){
    return(list2("{.x$x1}":=.x$value))
  } else {
    return(list2("{unique(.x$x1)}":= flatten(map2(.x = .x$x2,.y = .x$value,~{list2("{.x}":=.y)}))))
  }
}) %>% flatten()

waldo::compare(outtry,translated_list_correct)

[xvalda]

Brilliant, thanks a lot for the solution @nirgrahamuk !
It works on my sample data (just a few issues on my real JSON files, but nothing I won't be able to fix with your solution).
Cheers

[xvalda]

FYI @nirgrahamuk , issues fixed, minor ones only:

• my complete json wasn't sorted alphabetically whereas the code returns a list sorted alphabetically, I just added this at step 1: source_json_as_list <- source_json_as_list[order(names(source_json_as_list))]
• empty value strings ("") were transformed into NAs

Now I get what I wanted:

waldo::compare(outtry,source_json_as_list)
#> waldo::compare(outtry,source_json_as_list)
#> ✔ No differences

Many thanks again for this solution, it really saved me a lot of time.
Cheers!