JSON解析数据框中的单个列 [英] JSON Parsing a single column within a data frame

问题描述

我正在处理如下所示的数据.设备列表及其JSON格式的交易记录.

I'm working with data that looks like the following. A list of devices, and a record of their transactions in JSON format.

device_id              |                net_revenue_map 
1984691C-1EC1-4743-8DC5-55D882388C29 | {"2016-12-11":3.66}
56132A1A-ACEF-4073-878B-98E62E84FDB5 | {"2016-12-10":3.493}
036DF381-72DE-4523-9576-D79FFDB33820 | {"2016-12-10":3.493}
D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 | {"2016-12-11":1.543,"2016-12-10":1.543,"2016-12-12":1.543} 
E70CA4A8-D5F5-42A1-ADC4-392A2930B779 | {"2016-12-11":3.685}
E7A508A8-3517-4F5A-9876-5B7704ABD7FD | {"2016-12-11":1.393} 
43BE8905-CDDF-440C-A705-C80C06D448E2 | {"2016-12-11":1.393} 
CCACC621-05A9-4727-B214-730B56E49FC9 | {"2016-12-12":27.728} 

我正在尝试解析JSON，以便将其转换为如下所示的内容:

I'm attempting to parse the JSON so that this transforms into something like the following:

              device_id              | transaction_date | Transaction_Amt
1984691C-1EC1-4743-8DC5-55D882388C29 |   2016-12-11 | 3.66  
56132A1A-ACEF-4073-878B-98E62E84FDB5 | 2016-12-10 | 3.493 
036DF381-72DE-4523-9576-D79FFDB33820 | 2016-12-10 | 3.493 
D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 | 2016-12-11 | 1.543 
D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 | 2016-12-10 | 1.543 
D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 | 2016-12-12 | 1.543 
E70CA4A8-D5F5-42A1-ADC4-392A2930B779 | 2016-12-11 | 3.685 
E7A508A8-3517-4F5A-9876-5B7704ABD7FD | 2016-12-11 | 1.393 
43BE8905-CDDF-440C-A705-C80C06D448E2 | 2016-12-11 | 1.393 
CCACC621-05A9-4727-B214-730B56E49FC9 | 2016-12-12 | 27.728 

尝试以下代码时，出现错误

when trying the following code, I'm getting an error

library(jsonlite)
parse <- fromJSON(record_test[,2])
Error: parse error: trailing garbage
                   {"2016-12-11":3.66} {"2016-12-10":3.493} {"2016-12-
                     (right here) ------^

我是否需要在每列末尾添加逗号?我看到的大多数其他解析JSON的答案都是纯JSON数据框，但这在数据框内仅包含一列JSON，所以这就是我遇到的问题.

Do I need to append a comma to the end of each column? Most other answers for parsing JSON that I'm seeing are a purely JSON data frame, but this contains only one column of JSON within the data frame, so that's where I'm running into issues.

推荐答案

这是一种简单的方法:

library(tidyverse)

df %>% 
           # read each entry into list column
    mutate(json = map(net_revenue_map, jsonlite::fromJSON), 
           date = map(json, names),    # extract dates from list names
           date = map(date, as.Date),    # convert to proper dates
           amount = map(json, simplify)) %>%    # simplify list to values
    unnest(date, amount) %>%    # expand list columns
    select(-net_revenue_map)    # clean up

## # A tibble: 10 × 3
##                               device_id       date amount
##                                   <chr>     <date>  <dbl>
## 1  1984691C-1EC1-4743-8DC5-55D882388C29 2016-12-11  3.660
## 2  56132A1A-ACEF-4073-878B-98E62E84FDB5 2016-12-10  3.493
## 3  036DF381-72DE-4523-9576-D79FFDB33820 2016-12-10  3.493
## 4  D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 2016-12-11  1.543
## 5  D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 2016-12-10  1.543
## 6  D622F3F8-BEC0-4B42-AE99-B10527DFA8B0 2016-12-12  1.543
## 7  E70CA4A8-D5F5-42A1-ADC4-392A2930B779 2016-12-11  3.685
## 8  E7A508A8-3517-4F5A-9876-5B7704ABD7FD 2016-12-11  1.393
## 9  43BE8905-CDDF-440C-A705-C80C06D448E2 2016-12-11  1.393
## 10 CCACC621-05A9-4727-B214-730B56E49FC9 2016-12-12 27.728

或等效的基数R(显然，保存jsonlite::fromJSON)

Or equivalent base R (save jsonlite::fromJSON, obviously)

df$amount <- lapply(df$net_revenue_map, jsonlite::fromJSON)
df$date <- lapply(df$amount, function(x){as.Date(names(x))})
df$amount <- lapply(df$amount, unlist)
df$net_revenue_map <- NULL

df <- do.call(rbind, apply(df, 1, data.frame))
rownames(df) <- NULL

df
##                               device_id amount       date
## 1  1984691C-1EC1-4743-8DC5-55D882388C29  3.660 2016-12-11
## 2  56132A1A-ACEF-4073-878B-98E62E84FDB5  3.493 2016-12-10
## 3  036DF381-72DE-4523-9576-D79FFDB33820  3.493 2016-12-10
## 4  D622F3F8-BEC0-4B42-AE99-B10527DFA8B0  1.543 2016-12-11
## 5  D622F3F8-BEC0-4B42-AE99-B10527DFA8B0  1.543 2016-12-10
## 6  D622F3F8-BEC0-4B42-AE99-B10527DFA8B0  1.543 2016-12-12
## 7  E70CA4A8-D5F5-42A1-ADC4-392A2930B779  3.685 2016-12-11
## 8  E7A508A8-3517-4F5A-9876-5B7704ABD7FD  1.393 2016-12-11
## 9  43BE8905-CDDF-440C-A705-C80C06D448E2  1.393 2016-12-11
## 10 CCACC621-05A9-4727-B214-730B56E49FC9 27.728 2016-12-12

---

数据

df <- structure(list(device_id = c("1984691C-1EC1-4743-8DC5-55D882388C29", 
    "56132A1A-ACEF-4073-878B-98E62E84FDB5", "036DF381-72DE-4523-9576-D79FFDB33820", 
    "D622F3F8-BEC0-4B42-AE99-B10527DFA8B0", "E70CA4A8-D5F5-42A1-ADC4-392A2930B779", 
    "E7A508A8-3517-4F5A-9876-5B7704ABD7FD", "43BE8905-CDDF-440C-A705-C80C06D448E2", 
    "CCACC621-05A9-4727-B214-730B56E49FC9"), net_revenue_map = c("{\"2016-12-11\":3.66}", 
    "{\"2016-12-10\":3.493}", "{\"2016-12-10\":3.493}", "{\"2016-12-11\":1.543,\"2016-12-10\":1.543,\"2016-12-12\":1.543}", 
    "{\"2016-12-11\":3.685}", "{\"2016-12-11\":1.393}", "{\"2016-12-11\":1.393}", 
    "{\"2016-12-12\":27.728}")), .Names = c("device_id", "net_revenue_map"
    ), class = "data.frame", row.names = c(NA, -8L))

这篇关于JSON解析数据框中的单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！