1

I have problem with converting nested lists to data.frame.

First I have downloaded dataset in JSON format from Data API:

   request2 <-
  POST(
    url = "https://xxxx",
    add_headers('x-dataapi-key' = "xxxx", 'content-type' = "application/json"),
    body = list(oib = oibreq),
    encode = "json"
  )
jsonContent2 <- content(request2, type = "application/json")
json2 <-
  fromJSON(toJSON(jsonContent2, null = "null"), flatten = TRUE)

Object json2 has a form of nested lists. Here is a dataset:

> sample <- dput(json2)
structure(
  list(
    datumStanja = list(
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00",
      "2016-03-02T00:00:00+01:00"
    ),
    oib = list(
      "00045103869",
      "92680516748",
      "18527887472",
      "18527887472",
      "18527887472",
      "18527887472",
      "00045103869",
      "00045103869",
      "18527887472",
      "92680516748"
    ),
    iban = list(
      "HR0424840081101570980",
      "HR8623400091110462926",
      "HR9123400091110714260",
      "HR5124850031100201015",
      "HR4224910051100006698",
      "HR7524810001100101268",
      "HR8225000091101167416",
      "HR3223400091110156505",
      "HR6323400091110193874",
      "HR4223300031100429609"
    ),
    blokada = list(TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
                   FALSE, FALSE),
    vbdi = list(
      "2484008",
      "2340009",
      "2340009",
      "2485003",
      "2491005",
      "2481000",
      "2500009",
      "2340009",
      "2340009",
      "2330003"
    ),
    brojRacuna = list(
      "1101570980",
      "1110462926",
      "1110714260",
      "1100201015",
      "1100006698",
      "1100101268",
      "1101167416",
      "1110156505",
      "1110193874",
      "1100429609"
    ),
    banka = list(
      "RAIFFEISENBANK AUSTRIA d.d.",
      "PRIVREDNA BANKA ZAGREB d.d.",
      "PRIVREDNA BANKA ZAGREB d.d.",
      "CROATIA BANKA d.d.",
      "CREDO BANKA d.d.",
      "KREDITNA BANKA ZAGREB d.d.",
      "HYPO ALPE-ADRIA-BANK d.d.",
      "PRIVREDNA BANKA ZAGREB d.d.",
      "PRIVREDNA BANKA ZAGREB d.d.",
      "SOCIETE GENERALE - SPLITSKA BANKA d.d."
    ),
    datumOtvaranja = list(
      "2003-02-19T00:00:00+01:00",
      "2011-02-08T00:00:00+01:00",
      "2015-03-30T00:00:00+02:00",
      "2002-02-21T00:00:00+01:00",
      "2002-04-16T00:00:00+02:00",
      "2002-06-24T00:00:00+02:00",
      "2004-07-19T00:00:00+02:00",
      "2004-09-08T00:00:00+02:00",
      "2005-09-28T00:00:00+02:00",
      "2009-12-21T00:00:00+01:00"
    ),
    datumZatvaranja = list(
      NULL,
      NULL,
      NULL,
      "2009-11-06T00:00:00+01:00",
      "2009-02-17T00:00:00+01:00",
      "2009-03-18T00:00:00+01:00",
      "2008-08-14T00:00:00+02:00",
      "2009-07-13T00:00:00+02:00",
      "2013-09-18T00:00:00+02:00",
      "2013-07-09T00:00:00+02:00"
    ),
    povijestBlokada = list(
      structure(
        list(
          pocetak = list(
            "2011-08-04T00:00:00+02:00",
            "2011-09-06T00:00:00+02:00",
            "2011-11-25T00:00:00+01:00",
            "2011-12-30T00:00:00+01:00",
            "2012-02-20T00:00:00+01:00",
            "2012-03-23T00:00:00+01:00",
            "2012-05-21T00:00:00+02:00"
          ),
          kraj = list(
            "2011-08-10T00:00:00+02:00",
            "2011-09-13T00:00:00+02:00",
            "2011-12-28T00:00:00+01:00",
            "2012-01-16T00:00:00+01:00",
            "2012-03-16T00:00:00+01:00",
            "2012-05-16T00:00:00+02:00",
            NULL
          ),
          brojDana = list(6L, 7L, 33L, 17L, 25L, 54L,
                          1381L)
        ),
        .Names = c("pocetak", "kraj", "brojDana"),
        class = "data.frame",
        row.names = c(NA, 7L)
      ),
      structure(
        list(
          pocetak = list(
            "2012-05-30T00:00:00+02:00",
            "2012-06-21T00:00:00+02:00",
            "2012-06-29T00:00:00+02:00",
            "2012-09-06T00:00:00+02:00",
            "2014-06-09T00:00:00+02:00"
          ),
          kraj = list(
            "2012-06-05T00:00:00+02:00",
            "2012-06-26T00:00:00+02:00",
            "2012-07-03T00:00:00+02:00",
            "2013-03-06T00:00:00+01:00",
            NULL
          ),
          brojDana = list(6L, 5L, 4L, 181L, 632L)
        ),
        .Names = c("pocetak", "kraj",
                   "brojDana"),
        class = "data.frame",
        row.names = c(NA, 5L)
      ),

      structure(
        list(
          pocetak = list("2015-03-31T00:00:00+02:00"),
          kraj = list("2015-09-30T00:00:00+02:00"),
          brojDana = list(183L)
        ),
        .Names = c("pocetak", "kraj", "brojDana"),
        class = "data.frame",
        row.names = 1L
      ),
      structure(
        list(),
        .Names = character(0),
        row.names = integer(0),
        class = "data.frame"
      ),
      structure(
        list(),
        .Names = character(0),
        row.names = integer(0),
        class = "data.frame"
      ),
      structure(
        list(),
        .Names = character(0),
        row.names = integer(0),
        class = "data.frame"
      ),
      structure(
        list(),
        .Names = character(0),
        row.names = integer(0),
        class = "data.frame"
      ),
      structure(
        list(),
        .Names = character(0),
        row.names = integer(0),
        class = "data.frame"
      ),
      structure(
        list(),
        .Names = character(0),
        row.names = integer(0),
        class = "data.frame"
      ),
      structure(
        list(
          pocetak = list(
            "2012-05-30T00:00:00+02:00",
            "2012-06-21T00:00:00+02:00",
            "2012-06-29T00:00:00+02:00",
            "2012-09-06T00:00:00+02:00"
          ),
          kraj = list(
            "2012-06-05T00:00:00+02:00",
            "2012-06-26T00:00:00+02:00",
            "2012-07-03T00:00:00+02:00",
            "2013-03-06T00:00:00+01:00"
          ),
          brojDana = list(6L,
                          5L, 4L, 181L)
        ),
        .Names = c("pocetak", "kraj", "brojDana"),
        class = "data.frame",
        row.names = c(NA, 4L)
      )
    ),
    isActive = list(TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE,
                    FALSE, FALSE)
  ),
  .Names = c(
    "datumStanja",
    "oib",
    "iban",
    "blokada",
    "vbdi",
    "brojRacuna",
    "banka",
    "datumOtvaranja",
    "datumZatvaranja",
    "povijestBlokada",
    "isActive"
  ),
  class = "data.frame",
  row.names = c(NA,
                10L)
)

Here is the structure of the first raw as an example:

str(json2[1,])
'data.frame':   1 obs. of  11 variables:
 $ datumStanja    :List of 1
  ..$ : chr "2016-03-02T00:00:00+01:00"
 $ oib            :List of 1
  ..$ : chr "00045103869"
 $ iban           :List of 1
  ..$ : chr "HR0424840081101570980"
 $ blokada        :List of 1
  ..$ : logi TRUE
 $ vbdi           :List of 1
  ..$ : chr "2484008"
 $ brojRacuna     :List of 1
  ..$ : chr "1101570980"
 $ banka          :List of 1
  ..$ : chr "RAIFFEISENBANK AUSTRIA d.d."
 $ datumOtvaranja :List of 1
  ..$ : chr "2003-02-19T00:00:00+01:00"
 $ datumZatvaranja:List of 1
  ..$ : NULL
 $ povijestBlokada:List of 1
  ..$ :'data.frame':    7 obs. of  3 variables:
  .. ..$ pocetak :List of 7
  .. .. ..$ : chr "2011-08-04T00:00:00+02:00"
  .. .. ..$ : chr "2011-09-06T00:00:00+02:00"
  .. .. ..$ : chr "2011-11-25T00:00:00+01:00"
  .. .. ..$ : chr "2011-12-30T00:00:00+01:00"
  .. .. ..$ : chr "2012-02-20T00:00:00+01:00"
  .. .. ..$ : chr "2012-03-23T00:00:00+01:00"
  .. .. ..$ : chr "2012-05-21T00:00:00+02:00"
  .. ..$ kraj    :List of 7
  .. .. ..$ : chr "2011-08-10T00:00:00+02:00"
  .. .. ..$ : chr "2011-09-13T00:00:00+02:00"
  .. .. ..$ : chr "2011-12-28T00:00:00+01:00"
  .. .. ..$ : chr "2012-01-16T00:00:00+01:00"
  .. .. ..$ : chr "2012-03-16T00:00:00+01:00"
  .. .. ..$ : chr "2012-05-16T00:00:00+02:00"
  .. .. ..$ : NULL
  .. ..$ brojDana:List of 7
  .. .. ..$ : int 6
  .. .. ..$ : int 7
  .. .. ..$ : int 33
  .. .. ..$ : int 17
  .. .. ..$ : int 25
  .. .. ..$ : int 54
  .. .. ..$ : int 1381
 $ isActive       :List of 1
  ..$ : logi TRUE

As you can see, variable "povijestBlokada" have lists inside list. My goal is to convert this nested list object to data.frame with one column per distinct type value

. I tried with data.tree package, but I couldn't convert ii with as.Node function. Do you have any suggestions?

I am adding original JSON data (I have used dput, sure there is better way to do is but I don't know how):

structure("[{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"00045103869\"],\"iban\":[\"HR0424840081101570980\"],\"blokada\":[true],\"vbdi\":[\"2484008\"],\"brojRacuna\":[\"1101570980\"],\"banka\":[\"RAIFFEISENBANK AUSTRIA d.d.\"],\"datumOtvaranja\":[\"2003-02-19T00:00:00+01:00\"],\"datumZatvaranja\":null,\"povijestBlokada\":[{\"pocetak\":[\"2011-08-04T00:00:00+02:00\"],\"kraj\":[\"2011-08-10T00:00:00+02:00\"],\"brojDana\":[6]},{\"pocetak\":[\"2011-09-06T00:00:00+02:00\"],\"kraj\":[\"2011-09-13T00:00:00+02:00\"],\"brojDana\":[7]},{\"pocetak\":[\"2011-11-25T00:00:00+01:00\"],\"kraj\":[\"2011-12-28T00:00:00+01:00\"],\"brojDana\":[33]},{\"pocetak\":[\"2011-12-30T00:00:00+01:00\"],\"kraj\":[\"2012-01-16T00:00:00+01:00\"],\"brojDana\":[17]},{\"pocetak\":[\"2012-02-20T00:00:00+01:00\"],\"kraj\":[\"2012-03-16T00:00:00+01:00\"],\"brojDana\":[25]},{\"pocetak\":[\"2012-03-23T00:00:00+01:00\"],\"kraj\":[\"2012-05-16T00:00:00+02:00\"],\"brojDana\":[54]},{\"pocetak\":[\"2012-05-21T00:00:00+02:00\"],\"kraj\":null,\"brojDana\":[1389]}],\"isActive\":[true]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"92680516748\"],\"iban\":[\"HR8623400091110462926\"],\"blokada\":[true],\"vbdi\":[\"2340009\"],\"brojRacuna\":[\"1110462926\"],\"banka\":[\"PRIVREDNA BANKA ZAGREB d.d.\"],\"datumOtvaranja\":[\"2011-02-08T00:00:00+01:00\"],\"datumZatvaranja\":null,\"povijestBlokada\":[{\"pocetak\":[\"2012-05-30T00:00:00+02:00\"],\"kraj\":[\"2012-06-05T00:00:00+02:00\"],\"brojDana\":[6]},{\"pocetak\":[\"2012-06-21T00:00:00+02:00\"],\"kraj\":[\"2012-06-26T00:00:00+02:00\"],\"brojDana\":[5]},{\"pocetak\":[\"2012-06-29T00:00:00+02:00\"],\"kraj\":[\"2012-07-03T00:00:00+02:00\"],\"brojDana\":[4]},{\"pocetak\":[\"2012-09-06T00:00:00+02:00\"],\"kraj\":[\"2013-03-06T00:00:00+01:00\"],\"brojDana\":[181]},{\"pocetak\":[\"2014-06-09T00:00:00+02:00\"],\"kraj\":null,\"brojDana\":[640]}],\"isActive\":[true]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"18527887472\"],\"iban\":[\"HR9123400091110714260\"],\"blokada\":[false],\"vbdi\":[\"2340009\"],\"brojRacuna\":[\"1110714260\"],\"banka\":[\"PRIVREDNA BANKA ZAGREB d.d.\"],\"datumOtvaranja\":[\"2015-03-30T00:00:00+02:00\"],\"datumZatvaranja\":null,\"povijestBlokada\":[{\"pocetak\":[\"2015-03-31T00:00:00+02:00\"],\"kraj\":[\"2015-09-30T00:00:00+02:00\"],\"brojDana\":[183]}],\"isActive\":[true]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"18527887472\"],\"iban\":[\"HR5124850031100201015\"],\"blokada\":[false],\"vbdi\":[\"2485003\"],\"brojRacuna\":[\"1100201015\"],\"banka\":[\"CROATIA BANKA d.d.\"],\"datumOtvaranja\":[\"2002-02-21T00:00:00+01:00\"],\"datumZatvaranja\":[\"2009-11-06T00:00:00+01:00\"],\"povijestBlokada\":[],\"isActive\":[false]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"18527887472\"],\"iban\":[\"HR4224910051100006698\"],\"blokada\":[false],\"vbdi\":[\"2491005\"],\"brojRacuna\":[\"1100006698\"],\"banka\":[\"CREDO BANKA d.d.\"],\"datumOtvaranja\":[\"2002-04-16T00:00:00+02:00\"],\"datumZatvaranja\":[\"2009-02-17T00:00:00+01:00\"],\"povijestBlokada\":[],\"isActive\":[false]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"18527887472\"],\"iban\":[\"HR7524810001100101268\"],\"blokada\":[false],\"vbdi\":[\"2481000\"],\"brojRacuna\":[\"1100101268\"],\"banka\":[\"KREDITNA BANKA ZAGREB d.d.\"],\"datumOtvaranja\":[\"2002-06-24T00:00:00+02:00\"],\"datumZatvaranja\":[\"2009-03-18T00:00:00+01:00\"],\"povijestBlokada\":[],\"isActive\":[false]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"00045103869\"],\"iban\":[\"HR8225000091101167416\"],\"blokada\":[false],\"vbdi\":[\"2500009\"],\"brojRacuna\":[\"1101167416\"],\"banka\":[\"HYPO ALPE-ADRIA-BANK d.d.\"],\"datumOtvaranja\":[\"2004-07-19T00:00:00+02:00\"],\"datumZatvaranja\":[\"2008-08-14T00:00:00+02:00\"],\"povijestBlokada\":[],\"isActive\":[false]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"00045103869\"],\"iban\":[\"HR3223400091110156505\"],\"blokada\":[false],\"vbdi\":[\"2340009\"],\"brojRacuna\":[\"1110156505\"],\"banka\":[\"PRIVREDNA BANKA ZAGREB d.d.\"],\"datumOtvaranja\":[\"2004-09-08T00:00:00+02:00\"],\"datumZatvaranja\":[\"2009-07-13T00:00:00+02:00\"],\"povijestBlokada\":[],\"isActive\":[false]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"18527887472\"],\"iban\":[\"HR6323400091110193874\"],\"blokada\":[false],\"vbdi\":[\"2340009\"],\"brojRacuna\":[\"1110193874\"],\"banka\":[\"PRIVREDNA BANKA ZAGREB d.d.\"],\"datumOtvaranja\":[\"2005-09-28T00:00:00+02:00\"],\"datumZatvaranja\":[\"2013-09-18T00:00:00+02:00\"],\"povijestBlokada\":[],\"isActive\":[false]},{\"datumStanja\":[\"2016-03-10T00:00:00+01:00\"],\"oib\":[\"92680516748\"],\"iban\":[\"HR4223300031100429609\"],\"blokada\":[false],\"vbdi\":[\"2330003\"],\"brojRacuna\":[\"1100429609\"],\"banka\":[\"SOCIETE GENERALE - SPLITSKA BANKA d.d.\"],\"datumOtvaranja\":[\"2009-12-21T00:00:00+01:00\"],\"datumZatvaranja\":[\"2013-07-09T00:00:00+02:00\"],\"povijestBlokada\":[{\"pocetak\":[\"2012-05-30T00:00:00+02:00\"],\"kraj\":[\"2012-06-05T00:00:00+02:00\"],\"brojDana\":[6]},{\"pocetak\":[\"2012-06-21T00:00:00+02:00\"],\"kraj\":[\"2012-06-26T00:00:00+02:00\"],\"brojDana\":[5]},{\"pocetak\":[\"2012-06-29T00:00:00+02:00\"],\"kraj\":[\"2012-07-03T00:00:00+02:00\"],\"brojDana\":[4]},{\"pocetak\":[\"2012-09-06T00:00:00+02:00\"],\"kraj\":[\"2013-03-06T00:00:00+01:00\"],\"brojDana\":[181]}],\"isActive\":[false]}]", class = "json")
Improve this question
3
  • Have you seen this post? The starting data structure seems a bit different, but maybe one of the answers there would point you in the right direction. Commented Mar 3, 2016 at 16:17
  • ananda mahto i saw that answer. i would like to have somethimg like panel data. for different valuset of nested lists there will be same values of other clomuns Commented Mar 3, 2016 at 18:00
  • Can you provide the original json? Commented Mar 7, 2016 at 21:13

1 Answer 1

Reset to default
0

I manage to unnest json2$povijestBlokada with library dplyr:

  • Convert list to as tbl_df with as_data_frame
  • Select your nested element povijestBlokada and unnest it.
  • Problem is your NULL values in some columns but replacing them with "NA" do the trick. If you had NA in the first place, I think you can unnest a second time
library(dplyr)
DT <- as_data_frame(json2)
DT1 <- DT %>% select(povijestBlokada) %>% unnest()
DT1 %>% rowwise() %>% mutate_each(funs(replace(., is.null(.), "NA")))
#> Source: local data frame [17 x 3]
#> Groups: <by row>
#> 
#>                      pocetak                      kraj brojDana
#>                        (chr)                     (chr)    (chr)
#> 1  2011-08-04T00:00:00+02:00 2011-08-10T00:00:00+02:00        6
#> 2  2011-09-06T00:00:00+02:00 2011-09-13T00:00:00+02:00        7
#> 3  2011-11-25T00:00:00+01:00 2011-12-28T00:00:00+01:00       33
#> 4  2011-12-30T00:00:00+01:00 2012-01-16T00:00:00+01:00       17
#> 5  2012-02-20T00:00:00+01:00 2012-03-16T00:00:00+01:00       25
#> 6  2012-03-23T00:00:00+01:00 2012-05-16T00:00:00+02:00       54
#> 7  2012-05-21T00:00:00+02:00                        NA     1381
#> 8  2012-05-30T00:00:00+02:00 2012-06-05T00:00:00+02:00        6
#> 9  2012-06-21T00:00:00+02:00 2012-06-26T00:00:00+02:00        5
#> 10 2012-06-29T00:00:00+02:00 2012-07-03T00:00:00+02:00        4
#> 11 2012-09-06T00:00:00+02:00 2013-03-06T00:00:00+01:00      181
#> 12 2014-06-09T00:00:00+02:00                        NA      632
#> 13 2015-03-31T00:00:00+02:00 2015-09-30T00:00:00+02:00      183
#> 14 2012-05-30T00:00:00+02:00 2012-06-05T00:00:00+02:00        6
#> 15 2012-06-21T00:00:00+02:00 2012-06-26T00:00:00+02:00        5
#> 16 2012-06-29T00:00:00+02:00 2012-07-03T00:00:00+02:00        4
#> 17 2012-09-06T00:00:00+02:00 2013-03-06T00:00:00+01:00      181
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I am not sure this helps. First, every element is a list inside list. For example first element of DT[['brojDana']] is [[1]] [[1]] 6. Second, I would like this table to be part of the "parent table" with repeated values of colones for rows where pocetak, kraj or brojDana is repeated. Seems hard problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.