Jsonlite Array turn into list / object

New to Rstudio in general so some of my concepts are not clear. Trying to view data from OpenWeatherMap, on JSON files. They look something like this (shortened the data to view here).

[{"city_name":"X-Land"  
    "lon": -10.10,  
    "lat": 10.10  
  ,"main":{  
"temp":19,  
"temp_min":18,  
"temp_max":20 }  
  "weather": [{  
      "id": 800,  
      "main": "Clear",  
      "description": "clear sky",  
      "icon": "01d"  
    }],  
  "base": "stations", }]

Using jsonlite almost all the information display correctly

library(jsonlite)  
jsonData = fromJSON ("weatherfile.json")

The problem is the weather, it's an array of objects? and when converted it all remain together in a list / object??.

How it looks

I tried to use jsonlite flatten and pretty and either I didn't do it right or it didn't work for this case.
The question is, how do I separate weather into different columns (working with +15000 obs).

EDIT:
Adding to it, tried tidyr, unnest and separate. Extra info. This is what one of the rows looks like.

list(id = 800. main = "Clear". description = "sky is clear". icon = "01n")

Was trying to use separate to get the number and strings between "" into columns.

Thank you.

Could you edit the .json file and confirm it parses correctly?

Judging from the output this looks like a list of lists. The last snippet is malformed, also. Should be

    list(id = 800, main = "Clear", description = "sky is clear", icon = "01n")

The general outline of the approach is given in this post. In the terms discussed there, x is jsonData, y is possibly a data frame with columns id, main, description and icon. f is the function (probably composite) to be applied to x to get there.

please do:

library(jsonlite)  
jsonData = fromJSON ("weatherfile.json")
dput(head(jsonData))

and share the result, and I'll take a look


library(tidyverse)
#because I prefer how tibbles print to how dataframes do
jd2 <- as_tibble(jsonData10)
# just omitting some variables for ease of seeing the structure, can skip this later when happy
(jd3 <- jd2%>% select(city_name,lat,lon,weather))

unnest(jd3,cols = weather,  names_repair = "universal")
# A tibble: 6 x 7
city_name          lat   lon    id main  description  icon 
<chr>            <dbl> <dbl> <int> <chr> <chr>        <chr>
1 Laguna del Sauce -34.8 -55.1   800 Clear sky is clear 01n  
2 Laguna del Sauce -34.8 -55.1   800 Clear sky is clear 01n  
3 Laguna del Sauce -34.8 -55.1   800 Clear sky is clear 01n  
4 Laguna del Sauce -34.8 -55.1   800 Clear sky is clear 01n  
5 Laguna del Sauce -34.8 -55.1   800 Clear sky is clear 01n  
6 Laguna del Sauce -34.8 -55.1   800 Clear sky is clear 01n

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

Can't modify the file, this is a group work, we can't modify the original file.

We could technically still use the list as is, but it's just hard to see. And really wish there was a way to turn it into columns. Like I can unlist it, but it only shows it, not create columns and add them to the data frame.

Friend, can you picture me typing that out?
Perhaps you can share as text....

Sorry about that

library(jsonlite)
jsonData10 = fromJSON ("weatherfile.json")
dput(head(jsonData10))
structure(list(city_name = c("Laguna del Sauce", "Laguna del Sauce",
"Laguna del Sauce", "Laguna del Sauce", "Laguna del Sauce", "Laguna del Sauce"
), lat = c(-34.825285, -34.825285, -34.825285, -34.825285, -34.825285,
-34.825285), lon = c(-55.060363, -55.060363, -55.060363, -55.060363,
-55.060363, -55.060363), main = structure(list(temp = c(20.07,
19.32, 19.24, 19.1, 18.39, 18.27), temp_min = c(20, 19, 18.82,
18.57, 18, 17.85), temp_max = c(20.33, 20.21, 20.07, 19.93, 19.77,
19.6), feels_like = c(13.04, 12.7, 12.6, 14.25, 13.53, 13.45),
pressure = c(1014L, 1015L, 1015L, 1015L, 1015L, 1014L), humidity = c(45L,
48L, 48L, 48L, 55L, 51L)), row.names = c(NA, 6L), class = "data.frame"),
wind = structure(list(speed = c(9.3, 8.8, 8.8, 6.2, 6.7,
6.2), deg = c(70L, 80L, 50L, 60L, 40L, 30L)), row.names = c(NA,
6L), class = "data.frame"), clouds = structure(list(all = c(0L,
3L, 2L, 2L, 3L, 3L)), row.names = c(NA, 6L), class = "data.frame"),
weather = list(structure(list(id = 800L, main = "Clear",
description = "sky is clear", icon = "01n"), class = "data.frame", row.names = 1L),
structure(list(id = 800L, main = "Clear", description = "sky is clear",
icon = "01n"), class = "data.frame", row.names = 1L),
structure(list(id = 800L, main = "Clear", description = "sky is clear",
icon = "01n"), class = "data.frame", row.names = 1L),
structure(list(id = 800L, main = "Clear", description = "sky is clear",
icon = "01n"), class = "data.frame", row.names = 1L),
structure(list(id = 800L, main = "Clear", description = "sky is clear",
icon = "01n"), class = "data.frame", row.names = 1L),
structure(list(id = 800L, main = "Clear", description = "sky is clear",
icon = "01n"), class = "data.frame", row.names = 1L)),
dt = c(1546300800L, 1546304400L, 1546308000L, 1546311600L,
1546315200L, 1546318800L), dt_iso = c("2019-01-01 00:00:00 +0000 UTC",
"2019-01-01 01:00:00 +0000 UTC", "2019-01-01 02:00:00 +0000 UTC",
"2019-01-01 03:00:00 +0000 UTC", "2019-01-01 04:00:00 +0000 UTC",
"2019-01-01 05:00:00 +0000 UTC"), timezone = c(-10800L, -10800L,
-10800L, -10800L, -10800L, -10800L), rain = structure(list(
1h = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_)), row.names = c(NA, 6L), class = "data.frame")), row.names = c(NA,
6L), class = "data.frame")