CODEWITHSUNDEEP
jsonrdplyr
Md Sirajus Salayhin
Md Sirajus Salayhin

Reputation: 5144

Convert Json array key's as csv column name and values

I am parsing a json data to write a csv file. I am using tidyjson package to do this work.

In some point I need to print all the subjects value below in a separate columns and score as a value. Meaning Physics, Mathematics will be a column name and score will be there value. 

{
  "results": {
    "subjects": [
      {
        "subject": {
          "name": "Physics",
          "code": "PHY"
        },
        "score": 70
      },
      {
        "subject": {
          "name": "Mathematics",
          "code": "MATH"
        },
        "score": 50
      }
    ]
  }
}

I have tried as below:

json_data %>%
  as.tbl_json %>%
  gather_array %>%
  spread_values(user_id = jstring("user_id")) %>%
  enter_object("results") %>%
  enter_object("subjects") %>%
  gather_array("subjects") %>%
  spread_values(score = jstring("score")) %>%
  enter_object("subject") %>%
  spread_values(subject = jstring("subject")) %>%
  mutate(Physics = case_when(.$name == "Physics" ~ score)) %>%
  mutate(Mathematics = case_when(.$name == "Mathematics" ~ score))

But this shows multiple rows for one student. I need to show single row with each subject and score as a column value.

Upvotes: 2

Views: 733

Answers (1)

Emu
Emu

Reputation: 5905

But this shows multiple rows for one student. I need to show single row with each subject and score as a column value.

That means, your need a unique row based on subject name? In that case you can use aggregate

if you have a data frame named df like,

subject <- c("phy", "math", "phy", "math")
Score <- c(10, NA, NA, 20)
df <- data.frame(subject, Score)

then,

aggregate(x=df[c("Score")], by=list(subjectName=df$subject), max, na.rm = TRUE)

output

subjectName Score
    phy       10
    math      20

Upvotes: 1

Related Questions