Tibble columns of class tibble instead of class data frame

Question

What's the tidy way of having tibble columns of class tibble (instead of class list or data.frame)?

It's clearly possible to have columns of class data.frame in tibbles (see example below), but none of the "tidy ways of data manipulation" (i.e. dplyr::mutate() or purrr::map*_df()) seem to work for me when trying to cast the columns to tibble instead of data.frame

Current ouput of `jsonlite::fromJSON()`

# 'data.frame': 2 obs. of  3 variables:
#  $ labels  :List of 2
#   ..$ : chr  "label-a" "label-b"
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:'data.frame': 2 obs. of  1 variable:
#   ..$ levelTwo:'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#  $ schema  : chr  "0.0.1" "0.0.1"

Desired result

# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  3 variables:
#  $ labels  :List of 2
#   ..$ : chr  "label-a" "label-b"
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  1 variable:
#   ..$ levelTwo:Classes ‘tbl_df’, ‘tbl’ and 'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#  $ schema  : chr  "0.0.1" "0.0.1"

Why having `data.frame` columns can be very misleading

https://hendrikvanb.gitlab.io/2018/07/nested_data-json_to_tibble/

Example

Example data

library(magrittr)

json <- '[
  {
    "labels": ["label-a", "label-b"],
    "levelOne": {
      "levelTwo": {
        "levelThree": [
          {
            "x": "A",
            "y": 1,
            "z": true
          },
          {
            "x": "B",
            "y": 2,
            "z": false
          }
          ]
      }
    },
    "schema": "0.0.1"
  },
  {
    "labels": ["label-a", "label-b"],
    "levelOne": {
      "levelTwo": {
        "levelThree": [
          {
            "x": "A",
            "y": 10,
            "z": false
          },
          {
            "x": "B",
            "y": 20,
            "z": true
          }
          ]
      }
    },
    "schema": "0.0.1"
  }
]'

When visualizing this, you'll see that there's a subtle but important distinction between objects (which map to data.frames) and array (which map to lists):

Parsing JSON and converting to `tibble`

x <- json %>% 
  jsonlite::fromJSON() %>% 
  tibble::as_tibble()

x %>% str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  3 variables:
#  $ labels  :List of 2
#   ..$ : chr  "label-a" "label-b"
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:'data.frame': 2 obs. of  1 variable:
#   ..$ levelTwo:'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#  $ schema  : chr  "0.0.1" "0.0.1"

So it's clearly possible to have columns that are of class data.frame.

Casting `data.frame` to `tibble` columns: "the bad way"

But I'd like tibbles instead of data frames, so let's try the only thing I got to work: explicit re-assigning the respective list levels, or data frame/tibble columns, to be more precise:

# Make a copy so we don't mess with the initial state of `x`
y <- x

y$levelOne <- y$levelOne %>% 
  tibble::as_tibble()
y$levelOne$levelTwo <- y$levelOne$levelTwo %>% 
  tibble::as_tibble()
y$levelOne$levelTwo$levelThree <- y$levelOne$levelTwo$levelThree %>% 
  purrr::map(tibble::as_tibble)

x %>% str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  3 variables:
#  $ labels  :List of 2
#   ..$ : chr  "label-a" "label-b"
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  1 variable:
#   ..$ levelTwo:Classes ‘tbl_df’, ‘tbl’ and 'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#  $ schema  : chr  "0.0.1" "0.0.1"

That works, but is not in line with "tidy data manipulation pipes".

Casting `data.frame` to `tibble` columns: "the better way" (trying and failing)

# Yet another copy so we can compare:
z <- x

# Just to check that this works
z$levelOne %>% 
    tibble::as_tibble()
# # A tibble: 2 x 1
#   levelTwo$levelThree
#   <list>             
# 1 <df[,3] [2 × 3]>   
# 2 <df[,3] [2 × 3]>   

# Trying to get this to work with `dplzr::mutate()` fails:
z %>% 
  dplyr::mutate(levelOne = levelOne %>% 
    tibble::as_tibble()
  )
# Error: Column `levelOne` is of unsupported class data.frame

z %>% 
  dplyr::transmute(levelOne = levelOne %>% 
    tibble::as_tibble()
  )
# Error: Column `levelOne` is of unsupported class data.frame

# Same goes for `{purrr}`:
z %>% 
  dplyr::mutate(levelOne = levelOne %>% 
    purrr::map_df(tibble::as_tibble)
  )
# Error: Column `levelOne` is of unsupported class data.frame

z %>% 
  tibble::add_column(levelOne = z$levelOne %>% tibble::as_tibble())
# Error: Can't add duplicate columns with `add_column()`:
# * Column `levelOne` already exists in `.data`.

# Works, but not what I want:
z %>% 
  tibble::add_column(test = z$levelOne %>% tibble::as_tibble()) %>% 
  str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  4 variables:
#  [...]
#  $ test    :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  1 variable:
#   ..$ levelTwo:'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE

The only thing that worked (is not what we want)

Wrapping tibble::as_tibble() by purrr::map() seems to work, but the result is clearly not what we want as we duplicate everything below levelOne (compare to desired output above)

# Works, but not what I want:
z_new <- z %>% 
  dplyr::mutate(levelOne = levelOne %>% 
    purrr::map(tibble::as_tibble)
  )

z_new %>% str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  3 variables:
#  $ labels  :List of 2
#   ..$ : chr  "label-a" "label-b"
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:List of 2
#   ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#   ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':  2 obs. of  1 variable:
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#  $ schema  : chr  "0.0.1" "0.0.1"

EDIT (follow-up investigation)

Got it to work with Hendrik's help!

Still, IMO this topic raises some interesting follow-up questions regarding whether or not one should - or even could - do it any other way if the primary goal is to end up with tidy nested tibbles that play nicely with tidyr::unnset() and tidyr::nest() (see comments in Hendrik's answer below).

As to the proposed approach in https://hendrikvanb.gitlab.io/2018/07/nested_data-json_to_tibble/: I might be overlooking something obvious, but I think it only works for JSON docs with a single document.

First, let's modify df_to_tibble() (see Hendrik's answer below) to only turn "leaf" data frames into tibbles while turning "branch" data frames into lists:

leaf_df_to_tibble <- function(x) {
  if (is.data.frame(x)) {
    if (!any(purrr::map_lgl(x, is.list))) { 
      # Only captures "leaf" DFs:
      tibble::as_tibble(x) 
    } else {
      as.list(x)
    }
  } else {
    x
  }
}

This would give us results that are in line with the proposed way in the blog post, but only for "single object" JSON docs as illustrated below

df <- json %>% jsonlite::fromJSON()

# Only take the first object from the parsed JSON:
df_subset <- df[1, ]

Transforming df_subset:

df_subset_tibble <- purrr::reduce(
  0:purrr::vec_depth(df_subset),
  function(x, depth) {
    purrr::modify_depth(x, depth, leaf_df_to_tibble, .ragged = TRUE)
  }, 
  .init = df_subset
) %>% 
  tibble::as_tibble()

df_subset_tibble %>% str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 1 obs. of  3 variables:
#  $ labels  :List of 1
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:List of 1
#   ..$ levelTwo:List of 1
#   .. ..$ levelThree:List of 1
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#  $ schema  : chr "0.0.1"

Transforming df:

df_tibble <- purrr::reduce(
  0:purrr::vec_depth(df),
  function(x, depth) {
    purrr::modify_depth(x, depth, leaf_df_to_tibble, .ragged = TRUE)
  }, 
  .init = df
) %>% 
  tibble::as_tibble()

df_tibble %>% str()
# Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 2 obs. of  3 variables:
#  $ labels  :List of 2
#   ..$ : chr  "label-a" "label-b"
#   ..$ : chr  "label-a" "label-b"
#  $ levelOne:List of 2
#   ..$ levelTwo:List of 1
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#   ..$ levelTwo:List of 1
#   .. ..$ levelThree:List of 2
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  1 2
#   .. .. .. ..$ z: logi  TRUE FALSE
#   .. .. ..$ :Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    2 obs. of  3 variables:
#   .. .. .. ..$ x: chr  "A" "B"
#   .. .. .. ..$ y: int  10 20
#   .. .. .. ..$ z: logi  FALSE TRUE
#  $ schema  : chr  "0.0.1" "0.0.1"

As we see, "listifying" nested JSON structures actually may results in copying the "leafs". It just doesn't jump at you as long as n = 1 (number of JSON docs), but strikes you as soon as n > 1.

Wow. That’s a lot of code for what sounds like a simple solution to the title question. I’m pretty sure tibbles handle columns that are lists better than data frames do. — IRTFM, Jan 14 '20 at 11:56
@42: I agree that lists would be the preferred "entry point" for nested structures. But what do you make of the parsed result of nested JSONs that do have nested documents (mapping to DFs/tibbles) as opposed to only arrays (which would map to lists). I'm trying to capture the structure that's given to me (as does `jsonlite::fromJSON()`, but it unfortunately maps to `data.frame` instead of `tibble` columns) — Rappster, Jan 14 '20 at 12:03
@42: Also a very good blog post about why having `data.frame` columns can be very misleading: https://hendrikvanb.gitlab.io/2018/07/nested_data-json_to_tibble/ — Rappster, Jan 14 '20 at 12:07
I've also updated the post to better visualize the different JSON components (documents vs. arrays). That's the main point why we can't use "lists only" — Rappster, Jan 14 '20 at 12:14

score 1 · Accepted Answer · answered Jan 14 '20 at 15:19

Background

The comments above raise some valid points. Still, I do believe there is a way to achieve what you're after (whether or not this is a particularly good idea is less clear) by leveraging three functions from the purrr package in combination:

purrr::vec_depth allows us to get the (nesting) depth of a given list,
purrr::modify_depth allows us to apply a function to an list at the specified level of depth, and
purrr::reduce allows us to iteratively apply a function and have the result of each iteration be passed as the input to the subsequent iteration.

General approach

In essence, we want to convert any data.frame found at any level in the list to a tibble. This can easily be achieved using several rounds of purrr::modify_depth where we simply alter the depth depending on the level of the list we wish to target. Crucially, however, we want to do this in a way so that changes to level 1, for example, are retained when we move on to targeting level 2; changes to level 1 and 2 are retained when we move on to level 3; and so on. This is where purrr::reduce comes in: each time we apply purrr::modify_depth to convert a data.frame to a tibble, we'll ensure that the resultant output gets passed as the input to the next iteration. This is illustrated in the MWE below

MWE

Start with the basic setup of data structures and libraries

#> Load libraries ----
library(tidyverse)

json <- '[
  {
    "labels": ["label-a", "label-b"],
    "levelOne": {
      "levelTwo": {
        "levelThree": [
          {
            "x": "A",
            "y": 1,
            "z": true
          },
          {
            "x": "B",
            "y": 2,
            "z": false
          }
          ]
      }
    },
    "schema": "0.0.1"
  },
  {
    "labels": ["label-a", "label-b"],
    "levelOne": {
      "levelTwo": {
        "levelThree": [
          {
            "x": "A",
            "y": 10,
            "z": false
          },
          {
            "x": "B",
            "y": 20,
            "z": true
          }
          ]
      }
    },
    "schema": "0.0.1"
  }
]'  

# convert json to a nested data.frame
df <- jsonlite::fromJSON(json)

Now we'll create a simple helper function that can conditionally convert data.frame to tibble

# define a simple function to convert data.frame to tibble
df_to_tibble <- function(x) {
  if (is.data.frame(x)) as_tibble(x) else x
}

Now for the crucial routine: Taking df as the initial starting point (.init = df), apply the df_to_tibble function at each level of df (0:purrr::vec_depth(df)) using purrr::modify_depth. Use purrr::reduce to ensure that the results from each individual iteration gets passed as the input to the subsequent iteration.

# create df_tibble by reducing the result of applying df_to_tibble to each level
# of df via purrr's modify_depth function %>% lastly, ensure that the top level
# data.frame is also converted to a tibble
df_tibble <- purrr::reduce(
  0:purrr::vec_depth(df),
  function(x, depth) {
    purrr::modify_depth(x, depth, df_to_tibble, .ragged = TRUE)
  }, 
  .init = df
) %>% 
  as_tibble()

# show the structure of df_tibble
str(df_tibble)
#> Classes 'tbl_df', 'tbl' and 'data.frame':    2 obs. of  3 variables:
#>  $ labels  :List of 2
#>   ..$ : chr  "label-a" "label-b"
#>   ..$ : chr  "label-a" "label-b"
#>  $ levelOne:Classes 'tbl_df', 'tbl' and 'data.frame':    2 obs. of  1 variable:
#>   ..$ levelTwo:Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of  1 variable:
#>   .. ..$ levelThree:List of 2
#>   .. .. ..$ :Classes 'tbl_df', 'tbl' and 'data.frame':   2 obs. of  3 variables:
#>   .. .. .. ..$ x: chr  "A" "B"
#>   .. .. .. ..$ y: int  1 2
#>   .. .. .. ..$ z: logi  TRUE FALSE
#>   .. .. ..$ :Classes 'tbl_df', 'tbl' and 'data.frame':   2 obs. of  3 variables:
#>   .. .. .. ..$ x: chr  "A" "B"
#>   .. .. .. ..$ y: int  10 20
#>   .. .. .. ..$ z: logi  FALSE TRUE
#>  $ schema  : chr  "0.0.1" "0.0.1"

Thank you so much, that is **exactly** what I was looking for, down to to the actual implementation via `{purrr}`! — Rappster, Jan 15 '20 at 14:06
Curious to know, though: why do you think swapping `data.frame` for `tibble` is a bad idea when being confronted with nested JSON objects? Would `data.frame` be the better choice (as implemented by `jsonlite::fromJSON()`) and why so? Or are you advocating that nested JSON objects should rather be mapped to columns with `list(data.frame(...))` or `list(tibble())` instead of `data.frame(data.frame(...))` or `tibble(tibble(...))` in general? If the latter, then wouldn't that completely change the structure which in turn would result in a different JSON when parsing back out? — Rappster, Jan 15 '20 at 14:20
I'm not sure what the advantages are to simply swapping `data.frame` out for `tibble`, but as I try to show in this blog post (https://hendrikvanb.gitlab.io/2018/07/nested_data-json_to_tibble/), there are good reasons to want to ensure that `data.frame` objects included in other `data.frame` objects are included as `list` columns - TL;DR: it helps to ensure that the nested `data.frame` can be unnested into a tidy, rectangular/flat `data.frame` for the purposes of wrangling/analysis. I'm all for nested `tibbles`, but only if they are actually easy to navigate (i.e. can be unnested if needed). — hendrikvanb, Jan 15 '20 at 14:25
Okay, got it. I guess as a general approach, I also favor "staying tidy" by having `list(data.frame(...))` columns. But when you need to preserve the overall structure of a MongoDB/JSON doc, then I at least don't see a direct way of ensuring tidy structures. Of course one could write some data mappers for I/O to decouple the data structure for data analysis from the actual one in the DB. Would you tackle use cases as mine that way? — Rappster, Jan 15 '20 at 14:37
One thing I noticed when trying out the "JSON > list > tibble" approach in your blog post: I don't see how it's applicable when you have more than one top-level JSON object that you want to bring into a tidy format of a nested tibble. As soon as you call `as.list()` on a branch level data frame, don't you inevitably duplicate all the leafs? See the edit in my answer for details — Rappster, Jan 15 '20 at 15:36

Tibble columns of class tibble instead of class data frame

Current ouput of `jsonlite::fromJSON()`

Desired result

Why having `data.frame` columns can be very misleading

Related

Example

Example data

Parsing JSON and converting to `tibble`

Casting `data.frame` to `tibble` columns: "the bad way"

Casting `data.frame` to `tibble` columns: "the better way" (trying and failing)

The only thing that worked (is not what we want)

EDIT (follow-up investigation)

1 Answers1

Background

General approach

MWE

Tibble columns of class tibble instead of class data frame

Current ouput of jsonlite::fromJSON()

Desired result

Why having data.frame columns can be very misleading

Related

Example

Example data

Parsing JSON and converting to tibble

Casting data.frame to tibble columns: "the bad way"

Casting data.frame to tibble columns: "the better way" (trying and failing)

The only thing that worked (is not what we want)

EDIT (follow-up investigation)

1 Answers1

Background

General approach

MWE

Current ouput of `jsonlite::fromJSON()`

Why having `data.frame` columns can be very misleading

Parsing JSON and converting to `tibble`

Casting `data.frame` to `tibble` columns: "the bad way"

Casting `data.frame` to `tibble` columns: "the better way" (trying and failing)