Write JSON with multiple inputs in R

Question

I have a tibble and a list which I would like to write to a json file.

# A tibble: 2 x 12
  i     n             c                  x      
  <chr> <chr>         <chr>              <chr>  
1 NYC   New York City United States      LON,271;BOS,201
2 LON   London        United Kingdom     NYC,270

I would like to replace the 'x' column with a list.

When I try to merge by the 'i' column with the element of the list, a lot of data is duplicated... :/

sample list:

$NYC
     d   p
1: LON 271
2: BOS 201

$LON
     d   p
1: NYC 270

I would like to end up with something that looks like this:

[
  {
    "i": "NYC",
    "n": "New York City",
    "c": "United States",
    "C": "US",
    "r": "Northern America",
    "F": 66.256,
    "L": -166.063,
    "b": 94.42,
    "s": 0.752,
    "q": 4417,
    "t": "0,0,0,0,0",
    "x": [{
              "d": "LON",
              "p": 271
          },
          {
              "d": "BOS",
              "p": 201
          }]
}
  ...
]

I'm thinking there should be a way to write the json file without merging the list and the tibble, or maybe there is a way to merge them in a ragged way ?

ah. I just had another idea. maybe I can convert my dataframe to a list then use Reduce to combine the lists...

http://www.sharecsv.com/s/2e1dc764430c6fe746d2299f71879c2e/routes-before-split.csv

http://www.sharecsv.com/s/b114e2cc6236bd22b23298035fb7e042/tibble.csv

Could you add `dput` of your tibble and list? Also there seem to be inconsistencies: values of column `i`, `x`, the list, and `x` in your expected output basically have nothing in common. — Julius Vainora, Dec 02 '18 at 18:51
I've added csvs of the first 100 rows. I think I was getting close by splitting both into lists `d1_split <- split(d1, d1$i)` but I'm having trouble combining them — jaksco, Dec 02 '18 at 19:36
for example, `$ZVK` in the list should match up with the row in the `i` column which contains `ZVK`. I want to delete the `x` column in the tibble and replace it with the contents of the list to create a nested list so I can use toJSON to save the output — jaksco, Dec 02 '18 at 20:42
First I suggest to finish correcting and explaining (in the question) your example: how come the expected output has three pairs of `d` and `p`? As I understand your sample list being about `ZVK` and `ZYL` isn't for this particular example... — Julius Vainora, Dec 03 '18 at 20:52
@JuliusVainora oh okay. hopefully it is a little more clear now — jaksco, Dec 04 '18 at 04:05

Julius Vainora · Accepted Answer · 2018-12-04T21:00:49.470

We may do the following:

tbl
# A tibble: 1 x 13
#       X i     n      c             C     r                 F      L     b     s     q t        x               
#   <int> <fct> <fct>  <fct>         <fct> <fct>         <dbl>  <dbl> <dbl> <dbl> <int> <fct>    <fct>           
# 1     1 LON   London United Kingd… GB    Northern Eur…  51.5 -0.127  55.4  1.25  2088 0,0,1,3… AAL,15;AAR,15;A…

require(tidyverse)
tbl$x <- map(tbl$x, ~ strsplit(., ";|,")[[1]] %>%
             {data.frame(d = .[c(T, F)], p = as.numeric(.[c(F, T)]))})

The latter two lines are a shortened version of this base R equivalent:

tbl$x <- lapply(tbl$x, function(r) {
  tmp <- strsplit(r, ";|,")[[1]]
  data.frame(d = tmp[seq(1, length(tmp), 2)],
             p = as.numeric(tmp[seq(2, length(tmp), 2)]))
})

We go over the x column, split its elements by ; and , whenever possible, and then use the fact that the resulting odd elements will correspond do the d column in the desired outcome, and the even elements to the p column.

Output:

toJSON(tbl, pretty = TRUE)
[
  {
    "X": 1,
    "i": "LON",
    "n": "London",
    "c": "United Kingdom",
    "C": "GB",
    "r": "Northern Europe",
    "F": 51.508,
    "L": -0.127,
    "b": 55.43,
    "s": 1.25,
    "q": 2088,
    "t": "0,0,1,3,1",
    "x": [
      {
        "d": "AAL",
        "p": 15
      },
      {
        "d": "AAR",
        "p": 15
      },
      {
        "d": "ABZ",
        "p": 48
      }
    ]
  }
]

This solution works great!! immensely helpful Julius. Thank you for also including the base R equivalent so I can wrap my head around how this works. — jaksco, Dec 04 '18 at 20:58

Write JSON with multiple inputs in R

1 Answers1

Linked