Parsing JSON data works when isolating one row but not using apply to iterate over rows of a dataframe R

user_13325186 0
Viewed 26

Here is an example of my dataset:

test <- data.frame(index = rep(0, each = 10),
                   id = runif(10, min=1, max=100),
                   creation_date = random_datetime(10, st = "2015/01/01", et = "2015/12/31"),
                   json_data = c("{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}",
                                 "{'alldata': {'value': {'set': {'minA': 60, 'maxA': 190}, 'values': [[0, 0, 0, 0, 0, 0, 0, 56, 80, 95, 120, 140, 160, 156, 100, 77, 90, 89, 100, 112, 111, 89, 78, 178, 90, 80]], 'optimal': {'minB': 40, 'maxB': 190}, 'feature': 'postprandial after 3pm', 'type': {'timerange': 'Yes 3PM-7PM', 'hrs': '4Hr'}}, 'name': 'insight.A'}}")
)

Here is the function I'm trying to apply to each row of the dataset:

preprocess <- function (x) {
  
  one_row <- as.data.frame(x)
  
  processed <-
    rrapply::rrapply(fromJSON(gsub("'", '"', one_row$json_data),
                              flatten = TRUE), f = unlist, how = 'flatten') %>%
    append(one_row[-length(one_row)], .) %>%
    map(as.vector) %>%
    stack %>%
    mutate(rn = rowid(ind)) %>%
    pivot_wider(names_from = ind, values_from = values) %>%
    select(-rn) %>%
    fill(everything()) %>%
    type.convert(as.is = TRUE)
  
  lower_range <- 70
  upper_range <- 180
  SG <- processed$values
  
  processed$TIR_pre <- sum(SG > lower_range & SG < upper_range)/length(SG)
  processed$TAB_pre <- sum(SG > upper_range)/length(SG)
  processed$TBR_pre <- sum(SG < lower_range)/length(SG)
  
  result <- processed %>%
    select(-values) %>% 
    distinct()
  
  print(result)
}

When I select one row, the function works:

preprocess(test[1,])

Output:

# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature           timerange   hrs   name    TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>             <chr>       <chr> <chr>     <dbl>   <dbl>   <dbl>
1     0  11.9   1421724981.    60   190    40   190 postprandial aft~ Yes 3PM-7PM 4Hr   insigh~   0.692       0   0.308

But when I try to iterate the function over each row of the dataset, it doesn't work:

apply(test, 1, preprocess)

Error:

 Error: lexical error: invalid char in json text.
                                       NA
                     (right here) ------^

Any suggestions as to why would be much appreciated. Thanks in advance.

r json
1 Answers

Consider using either lapply

lapply(seq_len(nrow(test)), function(i) preprocess(test[i, ]))
# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature                timerange   hrs   name      TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>                  <chr>       <chr> <chr>       <dbl>   <dbl>   <dbl>
1     0  59.6   1420788079.    60   190    40   190 postprandial after 3pm Yes 3PM-7PM 4Hr   insight.A   0.692       0   0.308
# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature                timerange   hrs   name      TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>                  <chr>       <chr> <chr>       <dbl>   <dbl>   <dbl>
1     0  12.9   1423099725.    60   190    40   190 postprandial after 3pm Yes 3PM-7PM 4Hr   insight.A   0.692       0   0.308
# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature                timerange   hrs   name      TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>                  <chr>       <chr> <chr>       <dbl>   <dbl>   <dbl>
1     0  63.2   1425300550.    60   190    40   190 postprandial after 3pm Yes 3PM-7PM 4Hr   insight.A   0.692       0   0.308
# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature                timerange   hrs   name      TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>                  <chr>       <chr> <chr>       <dbl>   <dbl>   <dbl>
1     0  13.5   1429542735.    60   190    40   190 postprandial after 3pm Yes 3PM-7PM 4Hr   insight.A   0.692       0   0.308
# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature                timerange   hrs   name      TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>                  <chr>       <chr> <chr>       <dbl>   <dbl>   <dbl>
1     0  3.36   1429611375.    60   190    40   190 postprandial after 3pm Yes 3PM-7PM 4Hr   insight.A   0.692       0   0.308
# A tibble: 1 x 14
  index    id creation_date  minA  maxA  minB  maxB feature                timerange   hrs   name      TIR_pre TAB_pre TBR_pre
  <int> <dbl>         <dbl> <int> <int> <int> <int> <chr>                  <chr>       <chr> <chr>       <dbl>   <dbl>   <dbl>
1     0  83.9   1429865895.    60   190    40   190 postprandial after 3pm Yes 3PM-7PM 4Hr   insight.A   0.692       0   0.308
...

To create a single dataset, rbind the list elements

out <- do.call(rbind, lapply(seq_len(nrow(test)), 
    function(i) preprocess(test[i, ])))

or even rowwise

library(dplyr)
test %>% 
    rowwise %>% 
    transmute(out = list(preprocess(cur_data()))) %>% 
    pull(out) %>%
    bind_rows %>%
    bind_cols(test, .)

The issue with apply is that it returns a matrix and matrix can have only single class i.e. if there is any character element, it converts the whole to character class

user_37322710
Related