How to turn a JSON list and convert it to table in R-CodePudding

Hi I used the httr library to get some data from an API. Using this code

library(httr)
library(stringr)
library(tidyverse)
library(bigrquery)
#install.packages("rrapply")

library(rrapply)

    URL <- "https://api.maropost.com/accounts/2116/campaigns/4503/open_report.json?unique=true&auth_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&from=2021-12-01&to=2021-12-31"
    
    data <- GET(URL)
    
    response <- content(data)
    
    my_data <- rrapply(response, how = "melt")

and I am trying to convert the response, a json list into a dataframe

but with the code I have I get a dataframe with this structure

> my_data
    L1          L2                                 value
1    1  account_id                                  2116
2    1 campaign_id                                  4503
3    1  contact_id                                287529
4    1       email                     [email protected]
5    1     browser                               Unknown
6    1  open_count                                     1
7    1 recorded_at         2021-12-06T21:51:03.000-05:00
8    1 total_pages                                    13
9    1         uid                                  NULL
10   2  account_id                                  2116
11   2 campaign_id                                  4503
12   2  contact_id                                362856
13   2       email              [email protected]
14   2     browser                               Unknown
15   2  open_count                                     1
16   2 recorded_at         2021-12-04T11:40:51.000-05:00
17   2 total_pages                                    13
18   2         uid                                  NULL

this is close to what I need but that each L2 value is a column, and that L1 value of 1 will be one row and so on, and the values of each row are the values of the value column, so on that sample in reality is only 2 rows

is there a way to that from what I have?

thanks

CodePudding user response：

with your suggestion I used the jsonlite package and it was really easy

this game the results I needed

library(httr)
library(stringr)
library(tidyverse)
library(bigrquery)
#install.packages("rrapply")

library(rrapply)

URL <- "https://api.maropost.com/accounts/2116/campaigns/4503/open_report.json?unique=true&auth_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-ezmOaC95_Xsi_G03CGJXeg&from=2021-12-01&to=2021-12-31&per=50000"



library(jsonlite)

data3 <- fromJSON(URL, flatten = TRUE)

write.csv(as.data.frame(data3),"results.csv", col.names = TRUE)

CodePudding user response：

Something like this should work.

response_1 <- fromJSON(httr::content(response , as = "text", encoding = "UTF-8"))

list_cols_you_want <- c("","")

response_with_selected_cols <- lapply(response_1 , function(x) x[names(x) %in% list_cols_you_want ])

response_with_selected_cols %>%
  map(data.table::as.data.table) %>%
  data.table::rbindlist(fill = TRUE)