Use child columns of a tibble column for joins

[vorpalvorpal]

If I have a tibble containing a tibble column, it would be useful to be able to join based on the contents of a column within the tibble column.

df <- tibble(id = 1:5, tibbleCol = tibble(id = 2:6, value = 6:2), value = 3:7)
df2 <- tibble(id = 5:1, value = 8:4)
left_join(df, df2, by = c("tibbleCol$id" = "id"))
# Error in `left_join()`:
# ! Join columns in `x` must be present in the data.
# ✖ Problem with `tibbleCol$id`.

The above currently fails, but there is no obvious reason that it should do so, since the columns within a tibble column (unlike list columns or nested columns) are inherently the same length as the parent tibble.

[pstils]

Does a mutate do what you want?

df |> 
    mutate(tibbleCol = left_join(tibbleCol, df2, by = c(id = "id")))

[vorpalvorpal]

That's not quite what I was getting at. That joins columns onto the second level dataframe, I was talking about joining on the top level data frame.

> df |> 
+     mutate(tibbleCol = left_join(tibbleCol, df2, by = c(id = "id")))
# A tibble: 5 × 3
     id tibbleCol$id $value.x $value.y value
  <int>        <int>    <int>    <int> <int>
1     1            2        6        5     3
2     2            3        5        6     4
3     3            4        4        7     5
4     4            5        3        8     6
5     5            6        2       NA     7

# What I'm aiming for the equivalent of:
> df |> 
+     tidyr::unpack(cols = tibbleCol, names_sep = "_") |>
+     left_join(df2, by = c("tibbleCol_id" = "id")) |> 
+     tidyr::pack(tibbleCol = starts_with("tibbleCol"), .names_sep = "_")
# A tibble: 5 × 4
     id value.x value.y tibbleCol$id $value
  <int>   <int>   <int>        <int>  <int>
1     1       3       5            2      6
2     2       4       6            3      5
3     3       5       7            4      4
4     4       6       8            5      3
5     5       7      NA            6      2

I'm saying it would be nice to be able to treat a column embedded within a tibble column as if it were in the main data frame. In my use case I am basically using tibble columns to organise what is essentially a very, very, very wide data frame to make it more manageable and intelligible.

[DavisVaughan]

This is a nice idea, but I think it's one that we are going to have to say no to because of the complexity that would be involved and the fact that it would not translate nicely to other backends. I think you'll just have to unpack at least the columns you want to join on to accomplish this.