Search code examples
rdplyrtidyrjsonliterjson

Read nested JSON in R


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

enter image description here

Is there an easy way to do this?


Solution

  • 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