1

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.

Akshay Nagpal
  • 97
  • 8

1 Answers1

1
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"}}'
Nimantha
  • 6,405
  • 6
  • 28
  • 69
Prem
  • 11,775
  • 1
  • 19
  • 33