Read JSON file with an array into R dataframe

Question

I need to turn the following json file into an R dataframe.

[
  {
    "assessment": "AWS - Application Development",
    "sessions": [
      {
        "percentile": 0.0739025,
        "date_completed": "2021-10-20T20:58:33+00:00"
      },
      {
        "percentile": 0.0745573,
        "date_completed": "2021-07-14T09:58:17+00:00"
      }
    ]
  },
  {
    "assessment": "AWS - Big Data",
    "sessions": [
      {
        "percentile": 0.075069,
        "date_completed": "2021-07-14T08:58:24+00:00"
      },
      {
        "percentile": 0.426306,
        "date_completed": "2021-10-20T19:46:42+00:00"
      }
    ]
  }
]

The dataframe should have a row for each record of sessions, like so:

|assessment                   |percentile|date_completed           | 
|-----------------------------|----------|-------------------------|
|AWS - Application Development|0.0739025 |2021-10-20T20:58:33+00:00|
|AWS - Application Development|0.0745573 |2021-07-14T09:58:17+00:00|
|AWS - Big Data               |0.075069  |2021-07-14T08:58:24+00:00|
|AWS - Big Data               |0.426306  |2021-10-20T19:46:42+00:00|

How can I do this? If possible, I'd like to use the jsonlite an tidyverse libraries.

Stewart Macdonald · Accepted Answer · 2022-11-23 05:17:58Z

Does this work for you:

txt <- '[
  {
    "assessment": "AWS - Application Development",
    "sessions": [
      {
        "percentile": 0.0739025,
        "date_completed": "2021-10-20T20:58:33+00:00"
      },
      {
        "percentile": 0.0745573,
        "date_completed": "2021-07-14T09:58:17+00:00"
      }
    ]
  },
  {
    "assessment": "AWS - Big Data",
    "sessions": [
      {
        "percentile": 0.075069,
        "date_completed": "2021-07-14T08:58:24+00:00"
      },
      {
        "percentile": 0.426306,
        "date_completed": "2021-10-20T19:46:42+00:00"
      }
    ]
  }
]'


library('jsonlite')
unnest(fromJSON(txt))

# A tibble: 4 × 3
  assessment                    percentile date_completed           
  <chr>                              <dbl> <chr>                    
1 AWS - Application Development     0.0739 2021-10-20T20:58:33+00:00
2 AWS - Application Development     0.0746 2021-07-14T09:58:17+00:00
3 AWS - Big Data                    0.0751 2021-07-14T08:58:24+00:00
4 AWS - Big Data                    0.426  2021-10-20T19:46:42+00:00

Collectives™ on Stack Overflow

Read JSON file with an array into R 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