reshaping R dataframe with repeated pivot_longer or similar - representing a tree?

user_16547701 0
Viewed 84

I have a dataframe in R that I need to reshape to show paired relationships (basically building a version of two-column adjacency list - except it's a bit more complex than that). Here's the dataframe.

up_1 <- c(1, 1, 1, 1, 1, 1)
up_2 <- c(4, 3, 2, 3, 3, 3)
up_3 <- c(NA, 7, 5, 6, 6, 6)
up_4 <- c(NA, NA, 8, 9, 10, 11)

df_wide <- tibble(up_1, up_2, up_3, up_4)

df_wide

  up_1  up_2  up_3  up_4
 <dbl> <dbl> <dbl> <dbl>
     1     4    NA    NA
     1     3     7    NA
     1     2     5     8
     1     3     6     9
     1     3     6    10
     1     3     6    11

The dataframe represents ordered paths through a tree from root (1) to leaves (4, 7, 8, 9, 10, 11). Note that this is an example dataframe - the eventual application will use a much larger dataframe with variable dimensions and multiple roots.

Here is the actual example tree, in illustration:

1
├─ 2
   ├─ 5
      ├─ 8
├─ 3
   ├─ 6
      ├─ 9
      ├─ 10
      ├─ 11
   ├─ 7
├─ 4

I need to transform/pivot/spread this to two columns where the left column lists all nodes and the right column lists all "up-network" nodes, long format. So it would end up looking like this:

down  up
<dbl> <dbl>
1     4         
1     3         
1     7         
1     6         
1     9         
1     10            
1     11            
1     2         
1     5         
1     8         
4     NA            
3     7         
3     6         
3     9         
3     10            
3     11            
7     NA            
6     9         
6     10            
6     11            
9     NA            
10    NA            
11    NA            
2     5         
2     8         
5     8         
8     NA

In this example, there are 10 values "up-network" of 1 and 5 values "up-network" of 3.

Clarifying the application: grouping by the down column, a person could identify all up values up-network for any given down value (and from there incorporate and summarize other relevant attributes associated with up values).

I've had a hard time figuring this out and welcome any insight from all the stackoverflow heroes out there! Thank you in advance.

Post-script - I know this would be easier in a more easily object-oriented language (e.g. Python), but for now I'm stuck in R, given the broader analytic context.

dataframe pivot r
1 Answers

Going off of your idea to iteratively use pivot_longer, here is an idea where you subset columns (e.g. 1:4, 2:4, 3:4) before pivoting.

If you want only unique combinations, you can use unique on df_long.

library(tidyverse, warn.conflicts = FALSE)

up_1 <- c(1, 1, 1, 1, 1, 1)
up_2 <- c(4, 3, 2, 3, 3, 3)
up_3 <- c(NA, 7, 5, 6, 6, 6)
up_4 <- c(NA, NA, 8, 9, 10, 11)


df_wide <- tibble(up_1, up_2, up_3, up_4)



# iteratively select out columns and pivot longer

df_long <- map_dfr(1:3, ~df_wide %>%
                     select(.x:4) %>%
                     rename('down' = 1) %>%
                     pivot_longer(-down, values_to = 'up') %>%
                     select(-name))
#> Note: Using an external vector in selections is ambiguous.
#> i Use `all_of(.x)` instead of `.x` to silence this message.
#> i See <https://tidyselect.r-lib.org/reference/faq-external-vector.html>.
#> This message is displayed once per session.

print(df_long, n = 36)               
#> # A tibble: 36 x 2
#>     down    up
#>    <dbl> <dbl>
#>  1     1     4
#>  2     1    NA
#>  3     1    NA
#>  4     1     3
#>  5     1     7
#>  6     1    NA
#>  7     1     2
#>  8     1     5
#>  9     1     8
#> 10     1     3
#> 11     1     6
#> 12     1     9
#> 13     1     3
#> 14     1     6
#> 15     1    10
#> 16     1     3
#> 17     1     6
#> 18     1    11
#> 19     4    NA
#> 20     4    NA
#> 21     3     7
#> 22     3    NA
#> 23     2     5
#> 24     2     8
#> 25     3     6
#> 26     3     9
#> 27     3     6
#> 28     3    10
#> 29     3     6
#> 30     3    11
#> 31    NA    NA
#> 32     7    NA
#> 33     5     8
#> 34     6     9
#> 35     6    10
#> 36     6    11

Created on 2021-09-13 by the reprex package (v2.0.0)

user_124003850
Related