All combinations of a 20x2 df

[gregmansio]

Hello everyone,

I am trying to perform a "simple" task but am struggling to find a way to do it smartly.
I have a 20 by 2 table, with a character vector (colors) and a numeric vector (values).
In plain words, each color has a value attributed to it.

My goal is to create all the combinations of colors possible (Green | Blue, Red | Blue | Yellow, etc...), store that information in the first column vector, and store the average of their associated values in the second column.

So, the resulting table should be a Xby2 table, with string combinations in the first vector, and the result of a mathematical operation (the average) in the second vector.

Do you have any idea please ?

[mara]

Here's a reprex that should get you started, and I think you'll be able to take it from here (you can get the average using a [mutate()](https://dplyr.tidyverse.org/reference/mutate), and then select the columns you want in your final data frame (what you're calling a table) using select().

I've gotten all of the combination of colours using tidyr's expand_grid(). But, in my opinion, you're better off keeping the original colours around in their own columns for the purposes of joining the values later (hence my use of remove = FALSE in the unite() function).

library(tidyverse)
ex_df <- tibble::tribble(
   ~colour, ~value,
    "blue",     8L,
   "green",     4L,
     "red",     3L,
  "yellow",    11L,
    "pink",     2L,
  "violet",     6L
  )

col_cross <- expand_grid(col1 = ex_df$colour, col2 = ex_df$colour)
col_cross
#> # A tibble: 36 x 2
#>    col1  col2  
#>    <chr> <chr> 
#>  1 blue  blue  
#>  2 blue  green 
#>  3 blue  red   
#>  4 blue  yellow
#>  5 blue  pink  
#>  6 blue  violet
#>  7 green blue  
#>  8 green green 
#>  9 green red   
#> 10 green yellow
#> # … with 26 more rows

col_cross %>%
  unite("col_combo", col1, col2, sep = ", ", remove = FALSE) %>%
  left_join(ex_df, by = c("col1" = "colour")) %>% # get value for first colour
  left_join(ex_df, by = c("col2" = "colour")) # get value for second colour
#> # A tibble: 36 x 5
#>    col_combo     col1  col2   value.x value.y
#>    <chr>         <chr> <chr>    <int>   <int>
#>  1 blue, blue    blue  blue         8       8
#>  2 blue, green   blue  green        8       4
#>  3 blue, red     blue  red          8       3
#>  4 blue, yellow  blue  yellow       8      11
#>  5 blue, pink    blue  pink         8       2
#>  6 blue, violet  blue  violet       8       6
#>  7 green, blue   green blue         4       8
#>  8 green, green  green green        4       4
#>  9 green, red    green red          4       3
#> 10 green, yellow green yellow       4      11
#> # … with 26 more rows

^{Created on 2021-04-05 by the reprex package (v1.0.0)}

I'm sure there's a shorter way to do this, but I thought it would be useful to see all of the steps.

For future reference, providing us with a reprex (a self-contained reproducible example) makes it easier for us to help you!

[pleiby]

Mara's answer is quite good.

As a small variant, one can also use tidyr::expand,
which I believe is better suited in a tidyverse series of pipelined commands.

So, a bit more succinct. By the way, if the following is unclear, you can just incrementally add and execute each of the pipelined commands one at a time to see what they do. I do this by putting a comment symbol # before the pipe symbol %>% to interrupt the chained execution at some point.

Following Mara:

# make an example dataframe
library(tidyverse)
ex_df <- tibble::tribble(
   ~colour, ~value,
    "blue",     8L,
   "green",     4L,
     "red",     3L,
  "yellow",    11L,
    "pink",     2L,
  "violet",     6L
  )

# see help("expand")

col_cross <- ex_df %>%
 mutate(colour2 = colour) %>% # duplicate the colours, so they can be "crossed"
 expand(colour, colour2) %>% # generates all combination of the specified variables
 left_join(ex_df, by=c("colour")) %>% # add/join values for first colour
 left_join(ex_df, by=c("colour2" = "colour")) %>% # add/join values for colour2
 unite(comb_col, c(colour, colour2)) %>% # combine the colours into one col
 mutate(ave = (value.x + value.y)/2) # compute ave

col_cross

[gregmansio]

Thanks a lot Mara and pleiby

I eventually used another method to perform the desired output (which was beyond this specific crossing task), but this expand function is very nice to know!

[mara]

If your question's been answered (even if by you), would you mind choosing a solution/marking it as solved? (See FAQ below for how).

Having questions checked as resolved makes it a bit easier to navigate the site visually and see which threads still need help.

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …

Thanks