Convert nested list to dataframe when reading json file-CodePudding

I am trying to import data from two sensors (many more actually but this is just a test case). The final product should be a data frame or table that has 4 columns - two for each sensor. One is the time of the data collection, the other is the return data. I can't include the url that accesses the data because it includes keys for access but the start looks like this https://api.ecowitt.net/api/v3/device/history The following code runs fine

response <- httr::GET(url_complete_noDates, query = list(start_date = "2022-07-01 00:00:00", end_date = "2022-07-01 02:00:00"))

jsonRespText <- content(response, as =  "text") 
j <- fromJSON(jsonRespText)

But I can't figure out how to parse this into a data frame. This gets close

j_data <- j$data
df_json1 <- as_tibble(j_data, validate = F) 
temp_unnest <- df_json1 %>% unnest(cols = names(df_json1))

Here is the dput output for j. The time value is the time of the data download in seconds from 1970-01-01-00:00 MDT. Each element like this - 1656655200 = "38" is the time/data point that should go in a row of the the data frame.

list(code = 0L, msg = "success", time = "1673535777", data = list(
    soil_ch1 = list(soilmoisture = list(unit = "%", list = list(
        `1656655200` = "38", `1656657000` = "38", `1656658800` = "38", 
        `1656660600` = "38", `1656662400` = "38"))), soil_ch2 = list(
        soilmoisture = list(unit = "%", list = list(`1656655200` = "48", 
            `1656657000` = "48", `1656658800` = "48", `1656660600` = "48", 
            `1656662400` = "48")))))

CodePudding user response：

You can transform the nested list into a data.frame, reshape it to the name-value format with pivot_longer, and separate the name column into multiple columns with the separators "\\.".

library(tidyr)

as.data.frame(j$data) %>%
  pivot_longer(everything()) %>%
  separate(name, paste0("col", 1:4), sep = '\\.', fill = 'right')

# # A tibble: 12 × 5
#    col1     col2         col3  col4       value
#    <chr>    <chr>        <chr> <chr>      <chr>
#  1 soil_ch1 soilmoisture unit  NA         %    
#  2 soil_ch1 soilmoisture list  1656655200 38   
#  3 soil_ch1 soilmoisture list  1656657000 38   
#  4 soil_ch1 soilmoisture list  1656658800 38   
#  5 soil_ch1 soilmoisture list  1656660600 38   
#  6 soil_ch1 soilmoisture list  1656662400 38   
#  7 soil_ch2 soilmoisture unit  NA         %    
#  8 soil_ch2 soilmoisture list  1656655200 48   
#  9 soil_ch2 soilmoisture list  1656657000 48   
# 10 soil_ch2 soilmoisture list  1656658800 48   
# 11 soil_ch2 soilmoisture list  1656660600 48   
# 12 soil_ch2 soilmoisture list  1656662400 48