Convert irregular nested list into dataframe

Question

I have a nested list as follows:

    mylist <- list(
      list(
        id = 1234,
        attributes = list(
             list(
               typeId = 11,
               type = 'Main',
               date = '2018-01-01', 
               attributes= list(
                 list(
                   team = 'team1',
                   values = list(
                     value1 = 1, 
                     value2 = 999)),
                 list(
                   team = 'team2',
                   values = list(
                     value1 = 2, 
                     value2 = 888))
                 )
               ),
             list(
               typeId = 12,
               type = 'Extra',
               date = '2018-01-02', 
               attributes= list(
                 list(
                   team = 'team1',
                   values = list(
                     value1 = 3, 
                     value2 = 1234)),
                 list(
                   team = 'team2',
                   values = list(
                     value1 = 4, 
                     value2 = 9876))
               )
             )
          )
        )
      )

which I want to convert into a dataframe where each child entry is in a row alongside all its parent entries. So I would end up with a dataframe which looks like

    id type_id  type       date  team value1 value2
1 1234      11  Main 2018-08-01 team1      1    999
2 1234      11  Main 2018-08-01 team2      2    888
3 1234      12 Extra 2018-08-02 team1      3   1234
4 1234      12 Extra 2018-08-02 team2      4   9876

I do not always know the names within my list, so would need a generic way of doing this without specifying column names

EDIT

I have an answer to my initial question, but in response to Parfaits comment "If you post original JSON and your R import code a simpler solution maybe available".

My get my original JSON from a url using R code:

httr::GET(
    feed_url,
    authenticate(username, password)
  ) %>%
    httr::content()

Within the url the JSON would look like:

[{"id":[1234],"attributes":[{"typeId":[11],"type":["Main"],"date":["2018-01-01"],"attributes":[{"team":["team1"],"values":{"value1":[1],"value2":[999]}},{"team":["team2"],"values":{"value1":[2],"value2":[888]}}]},{"typeId":[12],"type":["Extra"],"date":["2018-01-02"],"attributes":[{"team":["team1"],"values":{"value1":[3],"value2":[1234]}},{"team":["team2"],"values":{"value1":[4],"value2":[9876]}}]}]}]

How did you get such a deeply nested list? Did you import JSON or XML? We can help with this step. — Parfait
– Parfait, Commented Mar 20, 2019 at 17:12
Imported from JSON. Have a function to convert the list now though thank you, see answer below — user1165199
– user1165199, Commented Mar 21, 2019 at 9:30
If you post original JSON and your R import code a simpler solution maybe available. — Parfait
– Parfait, Commented Mar 21, 2019 at 12:23

user1165199 · Accepted Answer · 2019-03-21 09:30:08Z

Have got a function for doing this now:

flattenList <- function(input) {

    output <- NULL

    ## Check which elements of the current list are also lists.
    isList <- sapply(input, class) == "list"

    ## Any non-list elements are added to the output data frame.
    if (any(!isList)) {

        ## Determine the number of rows in the output.
        maxRows <- max(sapply(input[!isList], length))

        output <-
            ## Initialise the output data frame with a dummy variable.
            data.frame(dummy = rep(NA, maxRows)) %>%

            ## Append the new columns.
            add_column(!!! input[!isList]) %>%

            ## Delete the dummy variable.
            select(- dummy)
    }

    ## If some elemenets of the current list are also lists, we apply the function again.
    if (any(isList)) {

        ## Apply the function to every sub-list, then bind the new output as rows.
        newOutput <- lapply(input[isList], flattenList) %>% bind_rows()

        ## Check if the current output is NULL.
        if (is.null(output)) {

            output <- newOutput

        } else {

            ## If the current output has fewer rows than the new output, we recycle it.
            if (nrow(output) < nrow(newOutput)) {
                output <- slice(output, rep(1:n(), times = nrow(newOutput) / n()))
            }


            ## Append the columns of the new output.
            output <- add_column(output, !!! newOutput)
        }
    }

    return(output)
}

> flattenList(mylist)
    id typeId  type       date  team priority value1 value2
1 1234     11  Main 2018-01-01 team1        1      1    999
2 1234     11  Main 2018-01-01 team2        1      2    888
3 1234     12 Extra 2018-01-02 team1        1      3   1234
4 1234     12 Extra 2018-01-02 team2        1      4   9876

Collectives™ on Stack Overflow

Convert irregular nested list into dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related