How to recode <NULL> cells to nested NA (<lgl [1]>) in a tibble's list-column?

user_6105259 0
Viewed 270

In a tibble with list-columns, how could I replace <NULL> entries with nested NA (which will take the nested form of <lgl [1]>)?

library(tibble)

tbl_with_null <-
  tibble(letter =  letters[1:10],
       value_1 = list(1, 2, 4, data.frame(a = 1, 2, 3), NULL, 6, 7, c(8, 11, 25), NULL, 10),
       value_2 = list("A", "B", "C", "D", NULL, NULL, NULL, list("H", "B", list(data.frame(id = 1:3))), "I", "J"))

> tbl_with_null
 
## # A tibble: 10 x 3
##    letter value_1          value_2   
##    <chr>  <list>           <list>    
##  1 a      <dbl [1]>        <chr [1]> 
##  2 b      <dbl [1]>        <chr [1]> 
##  3 c      <dbl [1]>        <chr [1]> 
##  4 d      <df[,3] [1 x 3]> <chr [1]> 
##  5 e      <NULL>           <NULL>    
##  6 f      <dbl [1]>        <NULL>    
##  7 g      <dbl [1]>        <NULL>    
##  8 h      <dbl [3]>        <list [3]>
##  9 i      <NULL>           <chr [1]> 
## 10 j      <dbl [1]>        <chr [1]>

Is there a way to act on the entire tbl_with_null to replace <NULL> with NA to get:

## # A tibble: 10 x 3
##    letter value_1                 value_2   
##    <chr>  <list>                  <list>    
##  1 a      <dbl [1]>               <chr [1]> 
##  2 b      <dbl [1]>               <chr [1]> 
##  3 c      <dbl [1]>               <chr [1]> 
##  4 d      <df[,3] [1 x 3]>        <chr [1]> 
##  5 e      <lgl [1]> <- NA         <lgl [1]>  # <- NA
##  6 f      <dbl [1]>               <lgl [1]>  # <- NA
##  7 g      <dbl [1]>               <lgl [1]>  # <- NA
##  8 h      <dbl [3]>               <list [3]>
##  9 i      <lgl [1]> <- NA         <chr [1]> 
## 10 j      <dbl [1]>               <chr [1]>

UPDATE

I made some progress based on this solution:

tbl_with_null %>%
  mutate(across(c(value_1, value_2), ~replace(., !lengths(.), list(NA))))

## # A tibble: 10 x 3
##    letter value_1          value_2   
##    <chr>  <list>           <list>    
##  1 a      <dbl [1]>        <chr [1]> 
##  2 b      <dbl [1]>        <chr [1]> 
##  3 c      <dbl [1]>        <chr [1]> 
##  4 d      <df[,3] [1 x 3]> <chr [1]> 
##  5 e      <lgl [1]>        <lgl [1]> 
##  6 f      <dbl [1]>        <lgl [1]> 
##  7 g      <dbl [1]>        <lgl [1]> 
##  8 h      <dbl [3]>        <list [3]>
##  9 i      <lgl [1]>        <chr [1]> 
## 10 j      <dbl [1]>        <chr [1]>

However, this is insufficient because I'm looking for a solution that would blindly replace NULL with NA across the entire dataframe. And if we go with mutate(across(everything(), ~replace(., !lengths(.), list(NA)))) we get that the letters column became a list-column too, which is unintended.

## # A tibble: 10 x 3
##    letter    value_1          value_2   
##    <list>    <list>           <list>    
##  1 <chr [1]> <dbl [1]>        <chr [1]> 
##  2 <chr [1]> <dbl [1]>        <chr [1]> 
##  3 <chr [1]> <dbl [1]>        <chr [1]> 
##  4 <chr [1]> <df[,3] [1 x 3]> <chr [1]> 
##  5 <chr [1]> <lgl [1]>        <lgl [1]> 
##  6 <chr [1]> <dbl [1]>        <lgl [1]> 
##  7 <chr [1]> <dbl [1]>        <lgl [1]> 
##  8 <chr [1]> <dbl [3]>        <list [3]>
##  9 <chr [1]> <lgl [1]>        <chr [1]> 
## 10 <chr [1]> <dbl [1]>        <chr [1]>

UPDATE 2

I thought that I got it done with

mutate(across(everything(), ~simplify(replace(., !lengths(.), list(NA)))))

But unfortunately this fails in some cases such as this data:

tbl_with_no_null <-
  tbl_with_null %>%
  slice(8) %>%
  select(letter, value_1)

## # A tibble: 1 x 2
##   letter value_1  
##   <chr>  <list>   
## 1 h      <dbl [3]>

While I was expecting that

tbl_with_no_null %>%
  mutate(across(everything(), ~simplify(replace(., !lengths(.), list(NA)))))

would return just the same tbl_with_no_null (because no <NULL> to replace):

## # A tibble: 1 x 2
##   letter value_1  
##   <chr>  <list>   
## 1 h      <dbl [3]>

But instead I got the error:

Error: Problem with `mutate()` input `..1`.
x Input `..1` can't be recycled to size 1.
i Input `..1` is `(function (.cols = everything(), .fns = NULL, ..., .names = NULL) ...`.
i Input `..1` must be size 1, not 3.

Bottom line

I'm looking for a way to replace <NULL> with NA in list columns, and naturally, if there's no <NULL> to replace, then return the input as-is.

tibble purrr dplyr r
4 Answers

base::rapply doesn't recurse through NULL, but you could use rrapply which allows this, and is quite efficient:

library(rrapply)
rrapply::rrapply(tbl_with_null, function(x) NA, how = "replace", condition = is.null)

# A tibble: 10 x 3
   letter value_1          value_2   
   <chr>  <list>           <list>    
 1 a      <dbl [1]>        <chr [1]> 
 2 b      <dbl [1]>        <chr [1]> 
 3 c      <dbl [1]>        <chr [1]> 
 4 d      <df[,3] [1 x 3]> <chr [1]> 
 5 e      <lgl [1]>        <lgl [1]> 
 6 f      <dbl [1]>        <lgl [1]> 
 7 g      <dbl [1]>        <lgl [1]> 
 8 h      <dbl [3]>        <list [3]>
 9 i      <lgl [1]>        <chr [1]> 
10 j      <dbl [1]>        <chr [1]>

Or as suggested by @JorisC. in comments, use the class argument which seems to be up to 25% faster on large lists:

rrapply(tbl_with_null, classes = "NULL", how = "replace", f = function(x) NA)

And just for fun :

eval(parse(text=gsub("NULL","NA",capture.output(dput(tbl_with_null)))))

# A tibble: 10 x 3
   letter value_1          value_2   
   <chr>  <list>           <list>    
 1 a      <dbl [1]>        <chr [1]> 
 2 b      <dbl [1]>        <chr [1]> 
 3 c      <dbl [1]>        <chr [1]> 
 4 d      <df[,3] [1 x 3]> <chr [1]> 
 5 e      <lgl [1]>        <lgl [1]> 
 6 f      <dbl [1]>        <lgl [1]> 
 7 g      <dbl [1]>        <lgl [1]> 
 8 h      <dbl [3]>        <list [3]>
 9 i      <lgl [1]>        <chr [1]> 
10 j      <dbl [1]>        <chr [1]> 

fortunes::fortune(106)

# If the answer is parse() you should usually rethink the question.
#   -- Thomas Lumley
#      R-help (February 2005)

Speed comparison is surprising, I would have expected parse to be the slowest solution :

microbenchmark::microbenchmark(
  rrapply = rrapply::rrapply(tbl_with_null, function(x) NA, how = "replace", condition = is.null),
  parse = eval(parse(text=gsub("NULL","NA",capture.output(dput(tbl_with_null))))),
  dplyr = mutate(tbl_with_null,across(where(is.list), .fns = map_if, .p = is.null, .f = function(x) NA)))
Unit: microseconds
    expr      min       lq       mean    median        uq      max neval cld
 rrapply   25.401   31.801   60.92102   51.2510   58.3010 1053.502   100 a  
   parse  225.001  269.701  327.31600  329.1005  362.4505  687.800   100  b 
   dplyr 2942.501 3207.301 3604.63105 3500.0005 3766.1510 6541.402   100   c
user_135133280

I would suggest the following approach.

# packages
library(tibble)
library(purrr)
library(dplyr)

# data
tbl_with_null <-
  tibble(
    letter = letters[1:10],
    value_1 = list(1, 2, 4, data.frame(a = 1, 2, 3), NULL, 6, 7, c(8, 11, 25), NULL, 10),
    value_2 = list("A", "B", "C", "D", NULL, NULL, NULL, list("H", "B", list(data.frame(id = 1:3))), "I", "J")
  )

# replace all NULL in list format with NA
tbl_with_null %>% 
  mutate(across(where(is.list), .fns = map_if, .p = is.null, .f = function(x) NA))
#> # A tibble: 10 x 3
#>    letter value_1          value_2   
#>    <chr>  <list>           <list>    
#>  1 a      <dbl [1]>        <chr [1]> 
#>  2 b      <dbl [1]>        <chr [1]> 
#>  3 c      <dbl [1]>        <chr [1]> 
#>  4 d      <df[,3] [1 x 3]> <chr [1]> 
#>  5 e      <lgl [1]>        <lgl [1]> 
#>  6 f      <dbl [1]>        <lgl [1]> 
#>  7 g      <dbl [1]>        <lgl [1]> 
#>  8 h      <dbl [3]>        <list [3]>
#>  9 i      <lgl [1]>        <chr [1]> 
#> 10 j      <dbl [1]>        <chr [1]>

# slice 
tbl_with_null %>% 
  slice(8) %>% 
  mutate(across(where(is.list), .fns = map_if, .p = is.null, .f = function(x) NA))
#> # A tibble: 1 x 3
#>   letter value_1   value_2   
#>   <chr>  <list>    <list>    
#> 1 h      <dbl [3]> <list [3]>

Created on 2021-03-14 by the reprex package (v1.0.0)

Check the help pages of the corresponding functions for more details (or add a comment here!)

user_102804110

You were very close to solving it! If you want to only replace NULLs within nested columns, rather than applying the mutate to everything, just apply it to those columns where the values are typed as lists using where(is.list) instead of everything() as aglia showed above. While you can keep the simplify, it doesn't seem to be necessary in my testing.

library(tidyverse)

tbl_with_null <-
  tibble(letter =  letters[1:10],
         value_1 = list(1, 2, 4, data.frame(a = 1, 2, 3), NULL, 6, 7, c(8, 11, 25), NULL, 10),
         value_2 = list("A", "B", "C", "D", NULL, NULL, NULL, list("H", "B", list(data.frame(id = 1:3))), "I", "J"))

tbl_with_null %>% 
  mutate(across(where(is.list), ~replace(., !lengths(.), list(NA))))

This solution marginally faster than agila's on my computer while sticking with the tidyverse, though if you're willing to use an additional package, clearly, rrapply is the faster solution.

  > microbenchmark::microbenchmark(
+   rrapply = rrapply::rrapply(tbl_with_null, function(x) NA, how = "replace", condition = is.null),
+   parse = eval(parse(text=gsub("NULL","NA",capture.output(dput(tbl_with_null))))),
+   dplyr1 = mutate(tbl_with_null,across(where(is.list), .fns = map_if, .p = is.null, .f = function(x) NA)),
+   dplyr2 = mutate(tbl_with_null, across(where(is.list), ~simplify(replace(., !lengths(.), list(NA))))),
+   dplyr3 = mutate(tbl_with_null, across(where(is.list), ~replace(., !lengths(.), list(NA))))
+ )  
Unit: microseconds
    expr      min        lq       mean    median       uq      max neval
 rrapply   27.795   42.4015   49.85706   45.9475   49.935  508.133   100
   parse  354.237  371.6450  400.97961  391.9885  425.434  598.792   100
  dplyr1 2472.218 2526.7575 2625.90951 2578.0390 2667.312 3086.635   100
  dplyr2 2270.130 2338.4955 2529.54983 2380.3345 2491.390 7513.478   100
  dplyr3 2243.784 2291.5100 2525.00431 2346.0720 2439.517 7318.504   100
user_90236680

Here are some data.table based solutions, with rrapply is slightly faster, the more tratidional lapply approach is slower:


dt <- as.data.table( tbl_with_null )
dt.worker <- function(x) {
    if( identical( x, list(NULL) ) )
        return(list(NA))
    return(x)
}

dt[, lapply( .SD, dt.worker ), by = letter ]

rrapply( dt, function(x) NA, how = "replace", condition = is.null)

microbenchmark(
    rrapply = rrapply::rrapply(tbl_with_null, function(x) NA, how = "replace", condition = is.null),
    parse = eval(parse(text=gsub("NULL","NA",capture.output(dput(tbl_with_null))))),
    dplyr1 = mutate(tbl_with_null,across(where(is.list), .fns = map_if, .p = is.null, .f = function(x) NA)),
    dplyr2 = mutate(tbl_with_null, across(where(is.list), ~simplify(replace(., !lengths(.), list(NA))))),
    dplyr3 = mutate(tbl_with_null, across(where(is.list), ~replace(., !lengths(.), list(NA)))),
    dt.lapply = dt[, lapply( .SD, dt.worker ), by = letter ],
    dt.rrapply = rrapply( dt, function(x) NA, how = "replace", condition = is.null)
)



Unit: microseconds
       expr      min        lq       mean    median        uq      max neval  cld
    rrapply   22.592   28.2730   37.91673   35.0210   36.3885  460.414   100 a   
      parse  213.831  242.7650  255.37595  254.2365  267.8920  308.278   100  b  
     dplyr1 1986.615 2028.5695 2197.87663 2061.2655 2082.5410 8258.728   100    d
     dplyr2 1803.212 1836.4240 1934.95871 1861.9965 1895.8655 8053.553   100   c 
     dplyr3 1779.537 1814.3925 1848.84501 1835.6575 1866.9810 2203.042   100   c 
  dt.lapply  287.349  321.2775  349.15118  338.7005  377.2070  446.948   100  b  
 dt.rrapply   16.962   26.1245   32.82651   29.5205   32.3605  425.738   100 a

running the dplyr::mutate solutions on a data.table seems to be slightly faster than their tibble equivalents, but they're still as expected magnitudes slower.

user_21659930
Related