Create an overlap matrix from group1, group2, overlap_count?

user_11726505 0
Viewed 244

I have a dataframe with g1, g2 representing two groups, and a val column, representing a count of how many items from g1 can also be found in g2.

## Input dataframe
data.frame(
  g1 = c('a','a','a','b','b','b','c','c','c','d'),
  g2 = c('a','b','c','a','b','c','a','b','c','d'),
  val = c(10,4,1,4,5,0,1,0,3,4),
  stringsAsFactors = FALSE
)

I'm having trouble formatting the dataframe as below. I can create an empty, named matrix with distinct column/row names from g1/g2, and iterate over each row in the input dataframe, writing it's value to the matching g1: row-id g2: column-id combination, but that seems inefficient; I was wondering if any of the libraries provided a method to automate it?

## Output overlap matrix
data.frame(a = c(10,4,1,0),
           b = c(4,5,0,0),
           c = c(1,0,3,0),
           d = c(0,0,0,4),
           row.names = c('a','b','c','d'))

A similar question to overlap between groups has been asked before, but there- we have a list of groups, and items in it, and want to find how many items overlap between groups.

Here, I know how many items overlap between groups, but am having trouble formatting it the correct way.

data.table tidyr reshape dataframe r
4 Answers

This is similar to How to reshape data from long to wide format with a slight difference that id column should be preserved as row names. For that we can use reshape2::acast.

reshape2::acast(Input_dataframe, formula = g1 ~ g2, fill = 0)

 # >    a b c d
 # > a 10 4 1 0
 # > b  4 5 0 0
 # > c  1 0 3 0
 # > d  0 0 0 4
user_64614620

If you (or someone else) is looking for a tidyverse-solution, this can help

tidyr::pivot_wider(df, g1, names_from = g2, values_from = val, values_fill = 0) %>% 
  tibble::column_to_rownames("g1")
#    a b c d
# a 10 4 1 0
# b  4 5 0 0
# c  1 0 3 0
# d  0 0 0 4
user_74654620

In base R, we can use xtabs

xtabs(val ~ g1 + g2, df1)
#   g2
#g1   a  b  c  d
#  a 10  4  1  0
#  b  4  5  0  0
#  c  1  0  3  0
#  d  0  0  0  4
user_37322710

This solution uses the data.table library.

dcast(setDT(df), g1 ~ g2, value.var = "val", fill = 0)
user_117265050
Related