Want to constrain solution to tidyverse packages. Struggling with converting a nested JSON into a one row dataframe. It's a feed from kafka that i need to process for an intra day trading scenario.
The objective is one row: EventTime in col1, rest of the columns are the TICKER names with price. Time, AAPL, DBC, JPM... are the column labels and then price as the row. I get close but..
I get stuck trying to access the nested list.
raw data:
from_kafka = '{"eventTime":1547831236883,"ticker":{"AAPL":{"lastTrade":146.92,"totalVolume":309985.0},"DBC":{"lastTrade":14.82,"totalVolume":7366.0},"JPM":{"lastTrade":99.56,"totalVolume":63695.0},"JNJ":{"lastTrade":127.21,"totalVolume":24515.0},"MRK":{"lastTrade":75.52,"totalVolume":41928.0},"PFE":{"lastTrade":42.57,"totalVolume":87949.0}}}'
Comes in fine using the jsonlite package
row_raw <- fromJSON(from_kafka)
str(row_raw)
List of 2
$ eventTime: num 1547831236883
$ ticker :List of 6
..$ AAPL:List of 2
.. ..$ lastTrade : num 147
.. ..$ totalVolume: num 309985
..$ DBC :List of 2
.. ..$ lastTrade : num 14.8
.. ..$ totalVolume: num 7366
..$ JPM :List of 2
.. ..$ lastTrade : num 99.6
.. ..$ totalVolume: num 63695
..$ JNJ :List of 2
.. ..$ lastTrade : num 127
.. ..$ totalVolume: num 24515
..$ MRK :List of 2
.. ..$ lastTrade : num 75.5
.. ..$ totalVolume: num 41928
..$ PFE :List of 2
.. ..$ lastTrade : num 42.6
.. ..$ totalVolume: num 87949
okay now i am stuck..
row_incoming_convert <-row_raw %>% purrr::flatten() %>%
map_if(is_list,as_tibble) %>% map_if(is_tibble,list) %>% bind_cols()
I get one row, but each row is the darn list. I can't figure out how to access this list, remove totalVolume variable and just show lastTrade price only. Purrr seems to be made for this.
> row_incoming_convert
> str(row_incoming_convert)
Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 7 variables:
$ eventTime: num 1547831236883
$ AAPL :List of 1
..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 2
variables:
.. ..$ lastTrade : num 147
.. ..$ totalVolume: num 309985
$ DBC :List of 1
..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 2
variables:
.. ..$ lastTrade : num 14.8
.. ..$ totalVolume: num 7366
$ JPM :List of 1
..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 2
variables:
.. ..$ lastTrade : num 99.6
.. ..$ totalVolume: num 63695
$ JNJ :List of 1
..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 2
variables:
.. ..$ lastTrade : num 127
.. ..$ totalVolume: num 24515
$ MRK :List of 1
..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 2
variables:
.. ..$ lastTrade : num 75.5
.. ..$ totalVolume: num 41928
$ PFE :List of 1
..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of 2
variables:
.. ..$ lastTrade : num 42.6
.. ..$ totalVolume: num 87949
> row_incoming_convert
# A tibble: 1 x 7
eventTime AAPL DBC JPM JNJ
MRK PFE
<dbl> <list> <list> <list> <list>
<list> <list>
1 1.55e12 <tibble [1 × 2]> <tibble [1 × 2]> <tibble [1 × 2]>
<tibble [1 × 2]> <tibble [1 × 2]> <tibble [1 × 2]>
