Read Consistent json data into R dataframe

Question

I want to read some json data into a dataframe. I know that is not always possible, but my json is appears to be quite well structured and consistent.

dput(test)

list(`0` = list(Code = "A", Name = "Agilent Technologies Inc", 
    StartDate = "2000-06-05", EndDate = NULL, IsActiveNow = 1, 
    IsDelisted = 0), `1` = list(Code = "AAL", Name = "American Airlines Group", 
    StartDate = "2015-03-23", EndDate = "2024-09-23", IsActiveNow = 0, 
    IsDelisted = 0), `2` = list(Code = "AAP", Name = "Advance Auto Parts Inc", 
    StartDate = "2015-07-09", EndDate = "2023-08-25", IsActiveNow = 0, 
    IsDelisted = 0), `3` = list(Code = "AAPL", Name = "Apple Inc", 
    StartDate = "1982-11-30", EndDate = NULL, IsActiveNow = 1, 
    IsDelisted = 0), `4` = list(Code = "ABBV", Name = "AbbVie Inc", 
    StartDate = "2013-01-02", EndDate = NULL, IsActiveNow = 1, 
    IsDelisted = 0))

I was able to get this into a dataframe of sorts by using:

df <- data.frame(Reduce(rbind, test))

However, this just creates a dataframe with a bunch of lists inside:

str(df)
> str(df)

'data.frame':   5 obs. of  6 variables:
 $ Code       :List of 5
  ..$ init: chr "A"
  ..$     : chr "AAL"
  ..$     : chr "AAP"
  ..$     : chr "AAPL"
  ..$     : chr "ABBV"
 $ Name       :List of 5
  ..$ init: chr "Agilent Technologies Inc"
  ..$     : chr "American Airlines Group"
  ..$     : chr "Advance Auto Parts Inc"
  ..$     : chr "Apple Inc"
  ..$     : chr "AbbVie Inc"
 $ StartDate  :List of 5
  ..$ init: chr "2000-06-05"
  ..$     : chr "2015-03-23"

  ..$     : chr "2015-07-09"
  ..$     : chr "1982-11-30"
  ..$     : chr "2013-01-02"
 $ EndDate    :List of 5
  ..$ init: NULL
  ..$     : chr "2024-09-23"
  ..$     : chr "2023-08-25"
  ..$     : NULL
  ..$     : NULL
 $ IsActiveNow:List of 5
  ..$ init: num 1
  ..$     : num 0
  ..$     : num 0
  ..$     : num 1
  ..$     : num 1
 $ IsDelisted :List of 5
  ..$ init: num 0
  ..$     : num 0
  ..$     : num 0
  ..$     : num 0
  ..$     : num 0

I can’t find a way to unlist the lists into a traditional dataframe.

I have looked at the similar questions on SO but I can’t make them work or understand what the functions are doing.

I wonder if your original data was actually JSON, and whether something like jsonlite::fromJSON() would do what you want directly? E.g., if the original data looked something like jsonlite::toJSON(unname(test)) then read it as jsonlite::fromJSON(). — Martin Morgan
– Martin Morgan, Commented Feb 7 at 13:12

L Tyrone · Accepted Answer · 2025-02-07 03:03:21Z

0

Using dplyr:

library(dplyr)

df <- bind_rows(test) %>%
  select(names(test[[1]]))

df
# # A tibble: 5 × 6
#   Code  Name                     StartDate  EndDate    IsActiveNow IsDelisted
#   <chr> <chr>                    <chr>      <chr>            <dbl>      <dbl>
# 1 A     Agilent Technologies Inc 2000-06-05 NA                   1          0
# 2 AAL   American Airlines Group  2015-03-23 2024-09-23           0          0
# 3 AAP   Advance Auto Parts Inc   2015-07-09 2023-08-25           0          0
# 4 AAPL  Apple Inc                1982-11-30 NA                   1          0
# 5 ABBV  AbbVie Inc               2013-01-02 NA                   1          0

or base R:

df <- do.call(rbind, lapply(test, function(x) {
  
  x[sapply(x, is.null)] <- NA
  as.data.frame(x)
  
}))

rownames(df) <- NULL

answered Feb 7 at 3:03

L Tyrone

8,37123 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JohnS Feb 7 at 3:13

Just for anyone who comes looking at this in the future, the base R solution works fine, but when I use the dplyr solution I get an error: > df <- bind_rows(test) %>% + select(names(test[[1]])) Error in select(., names(test[[1]])) : unused argument (names(test[[1]]))

JohnS Feb 7 at 3:31

So I just removed the last part and used df <- data.frame(bind_rows(test)) and that worked fine.

L Tyrone Feb 7 at 3:38

@JohnS - That's odd. What version of R and dplyr are you using? df <- bind_rows(test) %>% select(names(test[[1]])) worked for me using R_4.4.2 and dplyr_1.1.4 on a Win10 machine. The select() is not strictly necessary, but it preserves the original list order.

JohnS Feb 7 at 4:28

I agree that it is an odd thing to have happen I am running R 4.3.3 and dplyr 1.1.4. Looks like maybe I need to update to the latest version. Also, let me try one more thing.

Collectives™ on Stack Overflow

Read Consistent json data into R dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related