我现在有一个看起来像这样的json:
[{"id":"123","symbol":"A","price":[5,3,1,2],"Value":[23,2,4,30]}, {"id":"334","symbol":"B","price":[6,2,2,2],"Value":[2,21,42,3]}]
如何将其转换为包含两行的数据框,其中包含如下列:
id | symbol | price1 | price2 | price3 | price4 | value1 | value2 | value3 | value4
I have tried using fromJSON(jsondata)
但它会给我一个列表,在绑定列表后,我得到例如一列价格,每个不同的价格都作为不同的行返回。
我们可以使用jsonlite
中的fromJSON
转换为data.frame
,然后将list
元素unnest
为"long"格式。 最好将其保留为"长"格式
library(jsonlite)
library(tidyverse)
fromJSON(str1) %>%
unnest(c(price, Value)) %>%
group_by(id, symbol) %>%
mutate(ind = row_number()) %>%
pivot_wider(names_from = ind, values_from = c(price, Value))
# A tibble: 2 x 10
# id symbol price_1 price_2 price_3 price_4 Value_1 Value_2 Value_3 Value_4
# <chr> <chr> <int> <int> <int> <int> <int> <int> <int> <int>
#1 123 A 5 3 1 2 23 2 4 30
#2 334 B 6 2 2 2 2 21 42 3
注意:pivot_wider
来自devel
版本的tidyr
(‘0.8.3.9000’
(
或者另一种选择是从data.table
dcast
df1 <- fromJSON(str1) %>%
unnest(c(price, Value))
library(data.table)
dcast(setDT(df1), id + symbol ~rowid(id, symbol), value.var = c('price', 'Value'))
# id symbol price_1 price_2 price_3 price_4 Value_1 Value_2 Value_3 Value_4
#1: 123 A 5 3 1 2 23 2 4 30
#2: 334 B 6 2 2 2 2 21 42 3
数据
str1 <- '[{"id":"123","symbol":"A","price":[5,3,1,2],"Value":[23,2,4,30]}, {"id":"334","symbol":"B","price":[6,2,2,2],"Value":[2,21,42,3]}]'