CODEWITHSUNDEEP
jsonr
Jason J
Jason J

Reputation: 120

JSON Parsing a single column within a data frame

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}

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) ------^

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.

Upvotes: 2

Views: 2569

Answers (2)

alistaire
alistaire

Reputation: 43344

Here's a tidyverse approach:

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

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

Data

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))

Upvotes: 4

hrbrmstr
hrbrmstr

Reputation: 78802

Alternate tidyverse approach:

library(jsonlite)
library(dplyr)

rowwise(df) %>% 
  do(data_frame(device_id=.$device_id,
                amount=unlist(fromJSON(.$net_revenue_map)),
                date=names(fromJSON(.$net_revenue_map))))

Upvotes: 1

Related Questions