CODEWITHSUNDEEP
rjsonbindingjsonlitefromjson
Andrea
Andrea

Reputation: 11

How to turn JSON file into dataframe ('tibble')

I have downloaded some data from a platform and would like to convert it into a tibble I can manipulate in R. I have tried doing this, but continue coming up with errors. The structure of my JSON file is as follows

List of 7
 $ TimeSeries     :'data.frame':    3 obs. of  7 variables:
  ..$ UniqueId          : chr [1:3] "0a87d824b33b45a594a29968549c0a07" "959696fb9f5f44d98cf463c96fb7225d" "ea45700060f84899928cfc7b49348795"
  ..$ Identifier        : chr [1:3] "O2 (Dis).ODO Conc@GRDA/Pensacola Dam REW" "O2 (Dis).ODO Conc@GRDA/Pensacola Dam LEW" "O2 (Dis).ODO Conc@GRDA/Pensacola Dam CEN"
  ..$ Parameter         : chr [1:3] "O2 (Dis)" "O2 (Dis)" "O2 (Dis)"
  ..$ Label             : chr [1:3] "ODO Conc" "ODO Conc" "ODO Conc"
  ..$ Unit              : chr [1:3] "mg/l" "mg/l" "mg/l"
  ..$ LocationIdentifier: chr [1:3] "GRDA/Pensacola Dam REW" "GRDA/Pensacola Dam LEW" "GRDA/Pensacola Dam CEN"
  ..$ InterpolationType : chr [1:3] "InstantaneousValues" "InstantaneousValues" "InstantaneousValues"
 $ TimeRange      :List of 2
  ..$ StartTime: chr "2021-01-01T00:00:00.0000000+00:00"
  ..$ EndTime  : chr "2022-01-01T00:00:00.0000001+00:00"
 $ NumPoints      : int 0
 $ Points         : list()
 $ ResponseVersion: int 1
 $ ResponseTime   : chr "2023-07-05T16:26:47.9875745+00:00"
 $ Summary        : chr "Produced by AQUARIUS Time-Series Software.\r\nThe accuracy and reliability of this data is not guaranteed.  Aqu"| __truncated__

I would like to come out with a df that has the Date, Timestamp, and Value associated with each of the three locations in one dataframe.

Something like this.

enter image description here

I've tried using 'purrr' and 'tidyjson', unnesting the json file, and EVERYTHING on stackoverflow, but continue to get either parsing errors or character string errors.

The code I have originally is this

source("timeseries_client.R")
timeseries$connect("https://owrbaqts.aquaticinformatics.net","myusername","mypassword")

json<- timeseries$getTimeSeriesData(c("O2 (Dis).ODO Conc@GRDA/Pensacola Dam REW",
                                      "O2 (Dis).ODO Conc@GRDA/Pensacola Dam LEW",
                                      "O2 (Dis).ODO Conc@GRDA/Pensacola Dam CEN"),
                                        queryFrom = "2021-01-01T00:00:00Z",
                                        queryTo = "2022-01-01T00:00:00Z")

str(json)
jsondf<-as.data.frame(json)

Upvotes: 0

Views: 122

Answers (0)

Related Questions