Read nested JSON in R

Question

I have a JSON object that looks like this. This is coming from the stock market as a JSON.

{
    "Meta Data": {
        "1. Information": "Daily Time Series with Splits and Dividend Events",
        "2. Symbol": "NSE:20MICRONS",
        "3. Last Refreshed": "2019-12-20",
        "4. Output Size": "Full size",
        "5. Time Zone": "US/Eastern"
    },
    "Time Series (Daily)": {
        "2019-12-20": {
            "1. open": "33.5000",
            "2. high": "33.5000",
            "3. low": "32.4000",
            "4. close": "32.4500",
            "5. adjusted close": "32.4500",
            "6. volume": "12737",
            "7. dividend amount": "0.0000",
            "8. split coefficient": "1.0000"
        },
        "2019-12-19": {
            "1. open": "32.0000",
            "2. high": "32.6500",
            "3. low": "31.7500",
            "4. close": "32.3000",
            "5. adjusted close": "32.3000",
            "6. volume": "13320",
            "7. dividend amount": "0.0000",
            "8. split coefficient": "1.0000"
        }
            }
}

I want to create a dataframe of the following format

Is there an easy way to do this?

Have you looked at [this post](https://stackoverflow.com/questions/2617600/importing-data-from-a-json-file-into-r)? — ulfelder, Dec 20 '19 at 16:58

score 2 · Accepted Answer · answered Dec 20 '19 at 17:35

There might be a more elegant method, but this should be able to handle any length of Time Series (Daily)

library(jsonlite)
# json string saved to jsonInput
jsonRough <- fromJSON(jsonInput)
jsonFlat <- lapply(seq_along(jsonRough$`Time Series (Daily)`), 
                   function(x) c(jsonRough$`Meta Data`,
                                 Date = names(jsonRough$`Time Series (Daily)`[x]),
                                 jsonRough$`Time Series (Daily)`[[x]]))
df1 <- fromJSON(toJSON(jsonFlat))
names(df1) <- sub("^\\d+\\.\\s+", "", names(df1))

> df1[,c(-1, -4, -5)]
         Symbol Last Refreshed       Date    open    high     low   close adjusted close volume dividend amount split coefficient
1 NSE:20MICRONS     2019-12-20 2019-12-20 33.5000 33.5000 32.4000 32.4500        32.4500  12737          0.0000            1.0000
2 NSE:20MICRONS     2019-12-20 2019-12-19 32.0000 32.6500 31.7500 32.3000        32.3000  13320          0.0000            1.0000

Read nested JSON in R

1 Answers1