ABertoG
September 2, 2020, 3:41pm
1
Hi all,
I have been recently started using data imported by API which is usually in the JSON format.
For basic JSON this method has been working well
ULR <- paste("https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=max",sep="")
RawData <- GET(ULR)
Data<-fromJSON(rawToChar(RawData$content))
I'm now trying to extract more complex API without success
With the same method
ULR<-"https://protocol-api.aave.com/data/users/liquidations-all/"
RawData <- GET(ULR)
Data<-fromJSON(rawToChar(RawData$content))
str(Data)
This Data is an unreadable large character but there is the text in there. Read the tibble package but it returns an empty table
FlatData <- flatten(as_tibble(Data))
Also found this alternative method but doesn't manage to do the last operation
library(httr)
library(dplyr)
jsonResponse <-GET("https://protocol-api.aave.com/data/users/liquidations-all/")
jsonResponseParsed <- content(jsonResponse, as="parsed")
modJson<-jsonResponseParsed$raw_data
What am I missing? Is it possible to read multi level JSON with R?
Thanks for your help
AlexisW
September 9, 2020, 12:22am
2
I think the problem here is that there was two rounds of escaping characters. If you have a quotation mark " it would get escaped so that it can be used in a string enclosed with quotation marks: \". But I think there was a second escaping, making it into \\\".
Anyway, I can't totally explain why that works, but it does seem to work:
jsonResponseParsed <- content(RawData, as="parsed")
parsed <- jsonlite::fromJSON(jsonResponseParsed)
class(parsed[[1]])
#[1] "data.frame"
as_tibble(parsed[[1]])
# A tibble: 4,534 x 7
principalBorrows currentBorrows currentBorrowsE~ currentBorrowsU~ reserve$id
<chr> <chr> <chr> <chr> <chr>
1 131576.533018 131614.546163 390.500358465621 131861.34582415~ 0xa0b8699~
2 10000.372989348~ 10001.7315681~ 29.818975204302~ 10069.0565739454 0x0000000~
3 0.00019724 0.00019727 0.0059529091959~ 2.0101354611 0x2260fac~
4 1.0004040365183~ 1.00090281723~ 0.0348833526366~ 11.7791590348 0x5149107~
5 0.0785061166229~ 0.07980286849~ 0.0009983338848~ 0.3371101889 0xc011a73~
6 72675.032514 72680.072674 215.569095551084 72791.8180898765 0xdac17f9~
7 2143.746664 2143.8454 6.3586454564 2147.1415565252 0xdac17f9~
8 7.8285168306033~ 7.82900595075~ 7.8290059507575~ 2643.642288034 0xeeeeeee~
9 43.190035521271~ 43.4626428891~ 0.0095870328109~ 3.2372801241 0x0f5d2fb~
10 1.1322448086271~ 1.13437965714~ 1.1343796571435~ 383.0491445749 0xeeeeeee~
# ... with 4,524 more rows, and 22 more variables: $underlyingAsset <chr>,
# $symbol <chr>, $decimals <int>, $`__typename` <chr>, user$id <chr>,
# $reservesData <list>, $totalLiquidityETH <chr>, $totalLiquidityUSD <chr>,
# $totalCollateralETH <chr>, $totalCollateralUSD <chr>, $totalFeesETH <chr>,
# $totalFeesUSD <chr>, $totalBorrowsETH <chr>, $totalBorrowsUSD <chr>,
# $totalBorrowsWithFeesETH <chr>, $totalBorrowsWithFeesUSD <chr>,
# $availableBorrowsETH <chr>, $currentLoanToValue <chr>,
# $currentLiquidationThreshold <chr>, $maxAmountToWithdrawInEth <chr>,
# $healthFactor <chr>, id <chr>
Just note that's not a usual problem, normally fromJSON works directly (and multilevel is not a problem), here I think something went wrong with the preprocessing before you got the data.
