I am learning R and trying to import some stock market data. The sample data is
{"2017-12-07 13:07:00": {"1. open": "169.7800", "2. high": "169.9100", "3. low": "169.7800", "4. close": "169.8500", "5. volume": "20659"}, "2017-12-07 13:06:00": {"1. open": "169.7920", "2. high": "169.8300", "3. low": "169.7400", "4. close": "169.7700", "5. volume": "17485"}, "2017-12-07 13:05:00": {"1. open": "169.8600", "2. high": "169.8600", "3. low": "169.7350", "4. close": "169.7924", "5. volume": "19789"}, "2017-12-07 13:04:00": {"1. open": "169.8000", "2. high": "169.8800", "3. low": "169.7600", "4. close": "169.8600", "5. volume": "25589"}, "2017-12-07 13:03:00": {"1. open": "169.7800", "2. high": "169.8100", "3. low": "169.7100", "4. close": "169.8100", "5. volume": "19679"}, "2017-12-07 13:02:00": {"1. open": "169.9400", "2. high": "169.9400", "3. low": "169.7700", "4. close": "169.7799", "5. volume": "46347"}, "2017-12-07 13:01:00": {"1. open": "169.9540", "2. high": "170.0200", "3. low": "169.9400", "4. close": "169.9500", "5. volume": "66847"}, "2017-12-07 13:00:00": {"1. open": "169.9400", "2. high": "169.9439", "3. low": "169.9100", "4. close": "169.9400", "5. volume": "8546"}}
I am trying to load it in a way I can have timestamp as well as values. I have as of now, tried this
tmp <- fromJSON(readLines(i))
data <- data.frame(matrix(unlist(tmp),ncol=5,byrow = TRUE))
This code is letting me have the values but I am unable to retain the timestamp. Please guide me on how can I have the values as well as timestamps.
library(jsonlite)
library(dplyr)
library(tidyr)
lst = fromJSON(txt)
transform(stack(lst), status=c(sapply(lst, names))) %>%
spread(status, values)
Output is:
ind 1. open 2. high 3. low 4. close 5. volume
1 2017-12-07 13:07:00 169.7800 169.9100 169.7800 169.8500 20659
2 2017-12-07 13:06:00 169.7920 169.8300 169.7400 169.7700 17485
3 2017-12-07 13:05:00 169.8600 169.8600 169.7350 169.7924 19789
4 2017-12-07 13:04:00 169.8000 169.8800 169.7600 169.8600 25589
5 2017-12-07 13:03:00 169.7800 169.8100 169.7100 169.8100 19679
6 2017-12-07 13:02:00 169.9400 169.9400 169.7700 169.7799 46347
7 2017-12-07 13:01:00 169.9540 170.0200 169.9400 169.9500 66847
8 2017-12-07 13:00:00 169.9400 169.9439 169.9100 169.9400 8546
Sample data:
txt <- '{"2017-12-07 13:07:00": {"1. open": "169.7800", "2. high": "169.9100", "3. low": "169.7800", "4. close": "169.8500", "5. volume": "20659"}, "2017-12-07 13:06:00": {"1. open": "169.7920", "2. high": "169.8300", "3. low": "169.7400", "4. close": "169.7700", "5. volume": "17485"}, "2017-12-07 13:05:00": {"1. open": "169.8600", "2. high": "169.8600", "3. low": "169.7350", "4. close": "169.7924", "5. volume": "19789"}, "2017-12-07 13:04:00": {"1. open": "169.8000", "2. high": "169.8800", "3. low": "169.7600", "4. close": "169.8600", "5. volume": "25589"}, "2017-12-07 13:03:00": {"1. open": "169.7800", "2. high": "169.8100", "3. low": "169.7100", "4. close": "169.8100", "5. volume": "19679"}, "2017-12-07 13:02:00": {"1. open": "169.9400", "2. high": "169.9400", "3. low": "169.7700", "4. close": "169.7799", "5. volume": "46347"}, "2017-12-07 13:01:00": {"1. open": "169.9540", "2. high": "170.0200", "3. low": "169.9400", "4. close": "169.9500", "5. volume": "66847"}, "2017-12-07 13:00:00": {"1. open": "169.9400", "2. high": "169.9439", "3. low": "169.9100", "4. close": "169.9400", "5. volume": "8546"}}'