Extract list element from column of dataframe using R

Question

I have a dataframe where the third column is a list of lists. I wish to add a column to the existing dataframe that consists only of the element of the list where key = wb_id and the value string is what I want to be in the new column. Previously, I thought that this was always the 14th element in the list. I was wrong, it seems to move around, but it is always identified by key = wb_id.

So in the example below, there would be a new column, wb_id, added to df that consisted of 2 rows:

> df[[3]][[1]][[14]][["value"]]
[1] "test1_secret_ID"

> df[[3]][[2]][[14]][["value"]]
[1] "test2_secret_ID"

Here is the dataframe

df <- structure(list(email = list("test1@example.com", "test2@example.com"), 
                     type = list("active", "active"), fields = list(list(list(
                       key = "name", value = "", type = "TEXT"), list(key = "email", 
                                                                      value = "test1@example.com", type = "TEXT"), list(key = "company", 
                                                                                                                        value = "", type = "TEXT"), list(key = "country", value = "", 
                                                                                                                                                         type = "TEXT"), list(key = "city", value = "", type = "TEXT"), 
                       list(key = "phone", value = "", type = "TEXT"), list(
                         key = "state", value = "", type = "TEXT"), list(key = "zip", 
                                                                         value = "", type = "TEXT"), list(key = "last_name", 
                                                                                                          value = "", type = "TEXT"), list(key = "notify_pref", 
                                                                                                                                           value = "new_leader", type = "TEXT"), list(key = "your_message", 
                                                                                                                                                                                      value = "", type = "TEXT"), list(key = "selected", 
                                                                                                                                                                                                                       value = "Canadian Tire Bank,Bridgewater Bank,Motive Financial", 
                                                                                                                                                                                                                       type = "TEXT"), list(key = "confirmed_email", value = "", 
                                                                                                                                                                                                                                            type = "TEXT"), list(key = "wb_id", value = "test1_secret_ID", 
                                                                                                                                                                                                                                                                 type = "TEXT")), list(list(key = "name", value = "", 
                                                                                                                                                                                                                                                                                            type = "TEXT"), list(key = "email", value = "test2@example.com", 
                                                                                                                                                                                                                                                                                                                 type = "TEXT"), list(key = "company", value = "", type = "TEXT"), 
                                                                                                                                                                                                                                                                                       list(key = "country", value = "", type = "TEXT"), list(
                                                                                                                                                                                                                                                                                         key = "city", value = "", type = "TEXT"), list(key = "phone", 
                                                                                                                                                                                                                                                                                                                                        value = "", type = "TEXT"), list(key = "state", value = "", 
                                                                                                                                                                                                                                                                                                                                                                         type = "TEXT"), list(key = "zip", value = "", type = "TEXT"), 
                                                                                                                                                                                                                                                                                       list(key = "last_name", value = "", type = "TEXT"), list(
                                                                                                                                                                                                                                                                                         key = "notify_pref", value = "new_leader", type = "TEXT"), 
                                                                                                                                                                                                                                                                                       list(key = "your_message", value = "", type = "TEXT"), 
                                                                                                                                                                                                                                                                                       list(key = "selected", value = "Canadian Tire Bank,Bridgewater Bank,Motive Financial", 
                                                                                                                                                                                                                                                                                            type = "TEXT"), list(key = "confirmed_email", value = "", 
                                                                                                                                                                                                                                                                                                                 type = "TEXT"), list(key = "wb_id", value = "test2_secret_ID", 
                                                                                                                                                                                                                                                                                                                                      type = "TEXT"))), date_created = list("2020-10-24 01:57:10", 
                                                                                                                                                                                                                                                                                                                                                                            "2020-10-24 01:57:23")), row.names = 1:2, class = "data.frame")

akrun · Accepted Answer · 2021-05-30T03:01:37.163

If we need to use a loop (R 4.1.0), loop over the 3rd column with sapply, extract the 'value' component from the 14th element

df$new_column <- sapply(df[[3]], \(x) x[[14]]$value)
df$new_column
#[1] "test1_secret_ID" "test2_secret_ID"

If we want to extract using the 'key'

sapply(df[[3]], function(x) 
       x[sapply(x, function(y) y$key == 'wb_id')][[1]]$value)
#[1] "test1_secret_ID" "test2_secret_ID"

Or use Filter

sapply(df[[3]], \(x) Filter(\(y) y$key == "wb_id", x)[[1]]$value)
#[1] "test1_secret_ID" "test2_secret_ID"

According to R news,

R now provides a shorthand notation for creating functions, e.g. (x) x + 1 is parsed as function(x) x + 1.

Or use function(x) x for earlier versions of R

df$new_column <- sapply(df[[3]], function(x) x[[14]]$value)

Or use map from purrr

library(dplyr)
library(purrr)
df <- df %>% 
     mutate(new_column = map_chr(fields, ~keep(.x, ~ .x$key == 'wb_id') %>% 
          pluck(1, 'value')))

I like your map answer. However I just noticed that wb_id is not always the 14th element. It moves around in the list, but is always identified by key = wb_id. I have edited my original question. — ixodid, May 30 '21 at 02:37

Extract list element from column of dataframe using R

1 Answers1