Extracting two columns from a dataframe contained within a nested list

Question

I've pulled data from an API and converted it from JSON and have ended up with a dataframe ("Participants") of information on tournaments from a game that contains an identifier number ('id') identifying the participant in a tournament and then a deeply nested list containing information on the game the participant played. Within that nested list is a dataframe containing columns I'm interested in extracting. This is an image of the relevant columns from my dataframe.

Image of Dataframe

And these are the top two items in the list for example.

[[1]]
[[1]]$description
[1] ""

[[1]]$faction
[1] "galacticempire"

[[1]]$name
[1] "Unnamed Squadron"

[[1]]$pilots
          id       name points           ship upgrades.force-power   upgrades.sensor upgrades.modification upgrades.talent upgrades.gunner
1 darthvader darthvader     84  tieadvancedx1                 hate firecontrolsystem          afterburners            NULL            NULL
2 soontirfel soontirfel     54 tieinterceptor                 NULL              NULL                  NULL        predator            NULL
3 puresabacc puresabacc     62   tieskstriker                 NULL              NULL                  NULL     outmaneuver    fifthbrother

[[1]]$points
[1] 200

[[1]]$vendor
[[1]]$vendor$yasb
[[1]]$vendor$yasb$builder
[1] "Yet Another Squad Builder 2.0"

[[1]]$vendor$yasb$builder_url
[1] "https://raithos.github.io/"

[[1]]$vendor$yasb$link
[1] "https://raithos.github.io/?f=Galactic%20Empire&d=v5!s!173:204,113,-1,105:;179:127,-1,-1:;210:126,82,-1,-1:&sn=Unnamed%20Squadron&obs="



[[1]]$version
[1] "2.0.0"


[[2]]
[[2]]$name
[1] "Adam"

[[2]]$faction
[1] "scumandvillainy"

[[2]]$favourite
[1] TRUE

[[2]]$pilots
            ship upgrades.talent upgrades.crew upgrades.sensor upgrades.title        id points
1    fangfighter        fearless          NULL            NULL           NULL   fennrau     71
2    fangfighter        fearless          NULL            NULL           NULL oldteroch     59
3 g1astarfighter       trickshot           000 advancedsensors     misthunter      4lom     63

[[2]]$format
[1] "Extended"

[[2]]$version
[1] "2.3.5"

[[2]]$points
[1] 193

For each item in this list, I'm interested in extracting the 'id' column and the 'ship' column from the $pilots item in the list, appending the 'id' from my intial dataframe to these columns, and binding it into a new dataframe. I'd then do some additional manipulation on this dataframe.

I have figured out how to extract other items from my list. For example, I know that the code below extracts the 'points' item for each item in the list of lists.

lapply(participants$lists, "[[", 'points')

I also know that the following code would extract the 'id' column from the 'pilots' dataframe in the first item of the list.

lists[[1]][['pilots']]['id']

However, I'm not sure how to implement that subsetting as a function over the entire list, and I'm not sure how to also append the identifier from the "Participants" dataframe to those items.

Pulling out the entire 'pilots' dataframe from each item in the list and binding it together doesn't work because the dataframes have differing numbers of columns. I've also tried flattening the list, but this hasn't seemed to get me where I want to go, but maybe I've just done it incorrectly.

do.call("rbind", lapply(participants$lists, "[[", 'pilots'))

Thanks for any assistance you can provide.

user_3732271 · Answer

We extract the "pilots' element from the list and subset by selecting the 'id', 'ship' columns

do.call(rbind, lapply(participants$lists, function(x) x$pilots[c("id", "ship")]))

If there are elements in the 'pilots' that doesn't have both columns, and want to remove those elements, then using an if condition, we can do

do.call(rbind, lapply(participants$lists, function(x) {
              x1 <- x$pilots
              if(all(c("id", "ship") %in% names(x1))) {
                x1[c("id", "ship")]
               }
             }))

If we want to add the 'id' from participants

lst1 <- lapply(participants$lists, function(x) {
              x1 <- x$pilots
              if(all(c("id", "ship") %in% names(x1))) {
                x1[c("id", "ship")]
               }
             })
i1 <- sapply(lst1, NROW) > 0
lst1[i1] <- Map(cbind, id2 = participants$id[i1], lst1[i1])
out <- do.call(rbind, lst1)

Extracting two columns from a dataframe contained within a nested list

1 Answers