Create a data frame with as.data.frame with differing numbers of rows in R

Question

I want to extract data from an API with some specifications. I have a json file as a list of length two. I want to generate a data frame with the function as.data.frame for the whole data set to be able to export it to Excel. Therefore I used the following code:

Interest <- GET("https://api.aleth.io/v0/defi/history?protocols=maker,compound,aave,dydx,ddex&assets=dai,usdc,usdt,tusd&metrics=earn_apr,borrow_apr&before=1601424000&after=1577836800")
Interest_content <- content(Interest, as="text", encoding = "UTF-8")
Interest_data <- Interest_content
Interest_jsondata <- jsonlite::fromJSON(Interest_data)
Interest_dataFrame <- as.data.frame(Interest_jsondata) 

When calling the function as.data.frame on the json data, the following error is generated:

Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, : arguments imply differing number of rows: 1, 4, 2, 5

When looking into the Interest_jsondata, it occurs that the number of items (the number of rows) in "points" are differing. I guess that´s why the error in the as.data.frame function is generated.

How can that error be solved?

Answer 1

Let's look a the structure of Interest_jsondata .

str(Interest_jsondata)
List of 2
 $ data:'data.frame':   22 obs. of  4 variables:
  ..$ asset   : chr [1:22] "usdc" "usdc" "dai" "dai" ...
  ..$ metric  : chr [1:22] "earn_apr" "borrow_apr" "earn_apr" "borrow_apr" ...
  ..$ points  :List of 22
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:266, 1:2] "1578528000" "1578614400" "1578700800" "1578787200" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  .. ..$ : chr [1:274, 1:2] "1577836800" "1577923200" "1578009600" "1578096000" ...
  ..$ protocol: chr [1:22] "compound" "compound" "compound" "compound" ...
 $ meta:List of 1
  ..$ params:List of 6
  .. ..$ after      : int 1577836800
  .. ..$ assets     : chr [1:4] "tusd" "usdc" "usdt" "dai"
  .. ..$ before     : int 1601424000
  .. ..$ granularity: int 24
  .. ..$ metrics    : chr [1:2] "earn_apr" "borrow_apr"
  .. ..$ protocols  : chr [1:5] "compound" "aave" "ddex" "dydx" ...

The first item is the list is a data.frame! The second item is another list. Maybe all you need to do is extract the first item?

Interest_dataFrame <- Interest_jsondata$data

This works without error. The resulting data.frame has a list column in it (points), and each item in points looks like a matrix. The numbers of points in each matrix is not consistent, and how you deal with them is going to depend on your ultimate goal.

Here are some dplyr functions to help visalize the data.frame better:

library(dplyr)
glimpse(Interest_dataFrame)
Rows: 22
Columns: 4
$ asset    <chr> "usdc", "usdc", "dai", "dai", "tusd", "tusd", "usdc", "usdc",...
$ metric   <chr> "earn_apr", "borrow_apr", "earn_apr", "borrow_apr", "earn_apr...
$ points   <list> [<"1577836800", "1577923200", "1578009600", "1578096000", "1...
$ protocol <chr> "compound", "compound", "compound", "compound", "aave", "aave...

as_tibble(Interest_dataFrame)
# A tibble: 22 x 4
   asset metric     points              protocol
   <chr> <chr>      <list>              <chr>   
 1 usdc  earn_apr   <chr[,2] [274 x 2]> compound
 2 usdc  borrow_apr <chr[,2] [274 x 2]> compound
 3 dai   earn_apr   <chr[,2] [274 x 2]> compound
 4 dai   borrow_apr <chr[,2] [274 x 2]> compound
 5 tusd  earn_apr   <chr[,2] [266 x 2]> aave    
 6 tusd  borrow_apr <chr[,2] [266 x 2]> aave    
 7 usdc  earn_apr   <chr[,2] [266 x 2]> aave    
 8 usdc  borrow_apr <chr[,2] [266 x 2]> aave    
 9 usdt  earn_apr   <chr[,2] [266 x 2]> aave    
10 usdt  borrow_apr <chr[,2] [266 x 2]> aave    
# ... with 12 more rows

The second element in your original json list gives some parameters for the data: initial and final values, the levels of factors, etc.