Converting json to csv/xls error

General
, ,
1

Hi I am trying to transpose json files into csv/xls/something flat.

My code is as follows:

read code into R object

history_1 <- fromJSON('/Users/uuuuuuu/fr24/history_1.json', flatten=TRUE)

*** This works **

flatten the json format ready to export

history_1A <- as.data.frame(history_1)

*** this code gives following error **
Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 1, 0

There are 130 of these files. When I try it on the last file it works fine...

history_119 <- fromJSON('/Users/uuuuuuu/fr24/history_119.json', flatten=TRUE)
history_119A <- as.data.frame(history_119)

So, I know the issue is caused by the differences in the json files but I don't understand how to solve the issue.

Any help appreciated!

2

Hi!

To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

3

Hi, ok here's my attempt...

  1. The first file where the problem exists looks like this...
    history_1 <- fromJSON('/Users/andrewwhittam/fr24/history_1.json', flatten=TRUE)

head(history_1)..

*** from console window ***
$now
[1] 1570313314

$messages
[1] 0

$aircraft
list()

history_1A <- as.data.frame(history_1)

*** from console window ***
Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 1, 0

  1. The second file, which works, as follows...
    history_119 <- fromJSON('/Users/andrewwhittam/fr24/history_119.json', flatten=TRUE)

head(history_119)
*** from console window ***
$now
[1] 1570114234

$messages
[1] 469

*** there is no error message in the console.
When I run...
print(history_119A)
*** .. get the following from the console window which is what I hoped to get from history_1A ***

     now messages aircraft.hex aircraft.altitude aircraft.messages aircraft.seen aircraft.rssi

1 1570114234 469 407600 11125 8 298 -33.7
$aircraft
hex altitude messages seen rssi
1 407600 11125 8 298 -33.7

Hope that makes sense and is useful.

Andrew

4

That is still not a reproducible example, so I can't give you any specific advice about your code, I can only give you some general tips, you could try something like this

raw_content <- httr::content(json_object)
content_as_df <- enframe(unlist(raw_content))
5

If you've got two results of fromJSON and one works and the other doesn't, you might get some insight by calling dput on each of them. It'll emit R code that could be run to recreate the object. Sometimes that shows where the data structure you have differs from what you expect. Also, it can be useful in creating reproducible examples that someone else can copy/paste and run on their own machine to see your problem first hand.

6

Thanks both for the replies. I tried both your suggestions but it didn't put me any further ahead. I think the issue is that I am an R newbie so far from proficient.

Am afraid the concept of reproducible examples doesn't really mean much to me.

Would I be ok to post a link to the json files and the code I'm using?

Thanks

Andrew

7

A reproducible example is a chunk of code everyone else can copy-paste and see the problem for themselves in their own development environment. Linking to some json files and your code might work, but it is also likely to have a lot more than is required to address this problem. That makes it more overhead for any reader.

E.g. A reproducible example

library(jsonlite)
json_txt <- '{"foo": [1,2,3], "bar": [1,2,3,4]}'
myvar <- fromJSON(json_txt, flatten=T)
myframe <- as.data.frame(myvar)
#Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE,  : 
#  arguments imply differing number of rows: 3, 4

Which gives the same error message you're seeing, includes a small sample of the data, and is self contained. Aside from installing jsonlite, a reader would not need to do anything other than copy-paste the example into their own development environment. Also, in making a reproducible example you might discover insight into your own problem.

Mismatched number of items in arrays

Incidentally, making the example of a reproducible example led to a discovery that might help you. It's likely that your data has arrays that aren't multiples of each other's lengths. This may be what's causing the conversion to data.frame to fail for you. In the above example if the lengths of foo and bar are multiples of each other, the conversion works. E.g. try the above with "foo":[1,2] in the json text.

8

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