Skip to content

JSON data in data frame

7 messages · jeremiah rounds, Archit Soni, William Dunlap +1 more

Archit Soni
# 
Hi All,

Warm greetings, I am stuck at an issue to convert incoming json response to
data frame.

I am using below code to get the data

library(jsonlite)
d1 <- fromJSON('
http://api.openweathermap.org/data/2.5/group?id=524901,703448,2643743&units=metric&appid=ec0313a918fa729d4372555ada5fb1f8
')

d2 <- as.data.frame(d1)
?
typeof(d2)
list

can you please guide me how can i get this data into pure data.frame
format. The list in d1 has nested data.frame objects.

Note: If you are unable to get data from api then can use below json string
to test it out:

JSON: {"cnt":3,"list":[{"coord":{"lon":37.62,"lat":55.75},"sys":{"type":1,"id":7323,"message":0.193,"country":"RU","sunrise":1484286631,"sunset":1484313983},"weather":[{"id":600,"main":"Snow","description":"light
snow","icon":"13d"}],"main":{"temp":-3.75,"pressure":1005,"humidity":86,"temp_min":-4,"temp_max":-3},"visibility":8000,"wind":{"speed":4,"deg":170},"clouds":{"all":90},"dt":1484290800,"id":524901,"name":"Moscow"},{"coord":{"lon":30.52,"lat":50.43},"sys":{"type":1,"id":7358,"message":0.1885,"country":"UA","sunrise":1484286787,"sunset":1484317236},"weather":[{"id":804,"main":"Clouds","description":"overcast
clouds","icon":"04d"}],"main":{"temp":-2,"pressure":1009,"humidity":92,"temp_min":-2,"temp_max":-2},"visibility":9000,"wind":{"speed":4,"deg":250,"var_beg":210,"var_end":270},"clouds":{"all":90},"dt":1484290800,"id":703448,"name":"Kiev"},{"coord":{"lon":-0.13,"lat":51.51},"sys":{"type":1,"id":5187,"message":0.1973,"country":"GB","sunrise":1484294413,"sunset":1484324321},"weather":[{"id":802,"main":"Clouds","description":"scattered
clouds","icon":"03n"}],"main":{"temp":0.7,"pressure":1002,"temp_min":0,"temp_max":2,"humidity":98},"visibility":10000,"wind":{"speed":6.2,"deg":270},"clouds":{"all":40},"dt":1484290200,"id":2643743,"name":"London"}]}

Any help is appreciated.
jeremiah rounds
# 
I TAd a course in R computing and the first thing I told students was
"inspect. inspect. inspect."
d1 <- fromJSON('
http://api.openweathermap.org/data/2.5/group?id=524901,703448,2643743&units=metric&appid=ec0313a918fa729d4372555ada5fb1f8
')
names(d1)
str(d1)
d1
d1$list
your_data = d1$list

On Fri, Jan 13, 2017 at 1:12 AM, Archit Soni <soni.archit1989 at gmail.com>
wrote:

  
  


  


      

    

    

      
      

        


  

        

      Archit Soni
    

    

      
      #
    

  


  

      
Thanks Jeremiah,  I'll try this.

On Jan 13, 2017 11:23 PM, "jeremiah rounds" <roundsjeremiah at gmail.com>
wrote:

I TAd a course in R computing and the first thing I told students was
"inspect. inspect. inspect."
d1 <- fromJSON('http://api.openweathermap.org/data/2.5/
group?id=524901,703448,2643743&units=metric&appid=
ec0313a918fa729d4372555ada5fb1f8')
names(d1)
str(d1)
d1
d1$list
your_data = d1$list

On Fri, Jan 13, 2017 at 1:12 AM, Archit Soni <soni.archit1989 at gmail.com>
wrote:

            

      
      
      
    

        

      
      
    

        

  
  


  


      

    

    

      
      

        


  

        

      Archit Soni
    

    

      
      #
    

  


  

      
Hi Jermiah,

When i ran this code in Spotfire, my aim is to get output as a data table.
I am getting the same error:

TIBCO Enterprise Runtime for R returned an error: 'Error in
.cleanDataForExport(value, output.name) : Output data 'tab$coord' has
illegal type: 'data.frame''.


Code that I used:

library(jsonlite)

dat<- fromJSON('
http://api.openweathermap.org/data/2.5/group?id=524901,703448,2643743&units=metric&appid=ec0313a918fa729d4372555ada5fb1f8
')


tab <- dat$list

tab is my output variable that will give me results in table format.

Could you please suggest what we can do to resolve this error.

Many Thanks,
Archit

On Fri, Jan 13, 2017 at 11:23 PM, jeremiah rounds <roundsjeremiah at gmail.com>
wrote:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      William Dunlap
    

    

      
      #
    

  


  

      
This is a question concerning the interface between the TIBCO products
Spotfire and TERR so most people on this mailing list won't have a
clue.  You will have better luck with TIBCO support or asking in the
Q&A section of https://community.tibco.com.

It does sound like you might have a data.frame nested within a
data.frame on the R/TERR side and Spotfire cannot deal with such a
structure - its data table columns must be simple vectors.  Try
unpacking the columns of the inner data frame and putting them one by
one into the outer one.

(I cannot say for sure because that URL gives me a 502 error.)

Bill Dunlap
TIBCO Software
wdunlap tibco.com

        
On Sat, Jan 14, 2017 at 1:25 AM, Archit Soni <soni.archit1989 at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      David Winsemius
    

    

      
      #
    

  


  

          

      
      
      
    

        
The R as.dataframe includes that embeded dataframe as:

                                 list.weather
1           620, Snow, light shower snow, 13n
2 520, Rain, light intensity shower rain, 09n
3               800, Clear, Sky is Clear, 01n


An R solution for extraction might be:

 do.call(rbind, d1$list$weather)
#---------
   id  main                 description icon
1 620  Snow           light shower snow  13n
2 520  Rain light intensity shower rain  09n
3 800 Clear                Sky is Clear  01n


cbind( as.data.frame( d1$list[ !names(d1$list) %in% "weather"]), do.call(rbind, d1$list$weather) )
#----------
  coord.lon coord.lat sys.type sys.id sys.message sys.country sys.sunrise
1     37.62     55.75        1   7323      0.2075          RU  1484372967
2     30.52     50.43        1   7358      0.1982          UA  1484373141
3     -0.13     51.51        1   5091      0.2218          GB  1484380764
  sys.sunset main.temp main.pressure main.humidity main.temp_min main.temp_max
1 1484400490     -1.50          1009            80            -2            -1
2 1484403724      2.66           999            93             2             3
3 1484410813      2.46          1021            80             1             5
  visibility wind.speed wind.deg wind.var_beg wind.var_end all         dt      id
1       9000        6.0      150          120          190  90 1484416800  524901
2       9000        4.0      190           90          230  90 1484416800  703448
3      10000        5.1      280           NA           NA   0 1484418000 2643743
    name  id  main                 description icon
1 Moscow 620  Snow           light shower snow  13n
2   Kiev 520  Rain light intensity shower rain  09n
3 London 800 Clear                Sky is Clear  01n

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Archit Soni
    

    

      
      #
    

  


  

      
Hey David,

A big big thank you..!!

Your code worked like a charm and with little tweaks i extracted each item
from other nested data frames. Now i got a single data frame


My final code: dat2 <- cbind(as.data.frame(dat1$list[!names(dat1$list) %in%
c("coord","weather","sys","main","wind","clouds")]), do.call(cbind,
dat1$list$coord),do.call(rbind, dat1$list$weather),do.call(cbind,
dat1$list$sys),do.call(cbind,
dat1$list$main),do.call(cbind,dat1$list$wind),do.call(cbind,dat1$list$clouds))

Spotfire is accepting it too.. :)

Thanks again,
Archit

On Sun, Jan 15, 2017 at 12:35 AM, David Winsemius <dwinsemius at comcast.net>
wrote: