CODEWITHSUNDEEP
rjson
piokol
piokol

Reputation: 121

Parse nested parent-child json in R

I have a JSON file like this:

{
    "result":
    {
        "id": "123456789012345",
        "name": "Example data",
    },
    "parents":
    {
        "p1":
        {
            "parentGender": "M",
            "parentName": "George",
            "kids":
            {
                "1":
                {
                    "name": "Alan",
                    "gender": "M",
                    "age": "10"
                },
                "2":
                {
                    "name": "Adam",
                    "gender": "M",
                    "age": "15"
                }
            }

        },
        "p2":
        {
            "parentGender": "F",
            "parentName": "Samantha",
            "kids":
            {
                "1":
                {
                    "name": "James",
                    "gender": "M",
                    "age": "9"
                }
            }
        },
        "p3":
        {
            "parentGender": "M",
            "parentName": "John",
            "kids":
            {
                "1":
                {
                    "name": "Beatrice",
                    "gender": "F",
                    "age": "11"
                },
                "2":
                {
                    "name": "Ben",
                    "gender": "M",
                    "age": "8"
                },
                "3":
                {
                    "name": "Dorothy",
                    "gender": "F",
                    "age": "12"
                }
            }

        }
    }
}

I would like to retrieve all kids, and their names (and parents names) like this:

name,gender, age, parentGender, parentName,id
Alan,M,10, M,George,p1
Adam,M,15, M,George,p1
James,M,9, F,Samantha,p1
...

I know how to get particular/ single item like:

library(jsonlite)
es<-fromJSON("./json_example2.json" )
es$parents$p1$kids$`1`$name

But how to make a loop thru kids while we have different counts in collections? Finally I would like to create a code based on these data like: If (Gender is "M" and age >10) do bar chart of X If (Gender is "F" and age <10) do pie chart of Y...

When I will have it in table like below this should be easier, but not sure which approach is better.

Regards, Piotr

Upvotes: 2

Views: 322

Answers (2)

tk3
tk3

Reputation: 988

If your JSON has different counts in collections, a "for in" loop is enough. The following code should do the trick:

library(jsonlite)
es<-fromJSON("./json_example2.json" )

parentId <- names(es$parents)
df <- data.frame()
for(i in 1:length(parentId)) {
  p <- es$parents[[i]]
  id <- parentId[i]
  parentGender <- as.character(p$parentGender)
  parentName <- as.character(p$parentName)
  for(k in p$kids) {
    name <- as.character(k$name)
    gender <- as.character(k$gender)
    age <- as.numeric(k$age)
    df <- rbind(df, data.frame(name, gender, age, parentGender, parentName, id))
  }
}

print(df)

To filter the age and gender:

barChartData <- df[which(df$age > 10 & df$gender == "M"), ]

For the sake of completeness, if your JSON has more complex depths (not the exemplified case), then you may use a recursive solution or stack (similar to iterate over a tree) .

Hope it helps! :)

Upvotes: 1

JasonAizkalns
JasonAizkalns

Reputation: 20463

If you want a purrr functional approach, you could do:

library(tidyverse)

parents <- es$parents %>% enframe('parentID', 'parentInfo')

result_df <-
parents %>%
  mutate(parent_gender = parentInfo %>% map_chr("parentGender"),
         parent_name = parentInfo %>% map_chr("parentName"),
         kid_info = parentInfo %>% map("kids") %>% 
           map(. %>% map_df(`[`, c("name", "gender", "age")))) %>%
  select(-parentInfo) %>%
  unnest()

# A tibble: 6 x 6
  parentID parent_gender parent_name name     gender age  
  <chr>    <chr>         <chr>       <chr>    <chr>  <chr>
1 p1       M             George      Alan     M      10   
2 p1       M             George      Adam     M      15   
3 p2       F             Samantha    James    M      9    
4 p3       M             John        Beatrice F      11   
5 p3       M             John        Ben      M      8    
6 p3       M             John        Dorothy  F      12

Upvotes: 2

Related Questions