CODEWITHSUNDEEP
rdplyrcount
Silent_bliss
Silent_bliss

Reputation: 307

How to count the number of common values in a particular group?

I have a data frame df.

df <- data.frame(shop = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                 product = c(1,2,3,4,5,1,5,2,4))

For every pair of shops i.e AB, AC and BC, I wish to count the number of common product.

The expected output is as follows

pair common_product
AB   1        # Because 1 is common
AC   1        # Because 2 is common
BC   2        # Because 5 and 4 is common

Upvotes: 2

Views: 94

Answers (3)

iHermes
iHermes

Reputation: 339

M1=matrix(0,length(unique(df$shop)),5)
for(i in 1:length(unique(df$shop))){
  M1[i,1]="A"
  M1[i,2]=dim(d[(df$shop)=="A",])[1]
  M1[i,3]=unique(df$shop)[i]
  M1[i,4]=dim(df[df$shop==unique(d$V1)[i],])[1]
  M1[i,5]=sum(df[(df$shop)=="A",2] %in% df[df$shop==unique(df$shop)[i],2])
}

It will return more than you want. Counts number of objects in "A","B" and "C"

Upvotes: 0

Yuriy Saraykin
Yuriy Saraykin

Reputation: 8880

use tidyverse

df %>% 
  group_by(product) %>% 
  summarise(pair = str_c(shop, collapse = "")) %>% 
  count(pair)

# A tibble: 4 x 2
  pair      n
  <chr> <int>
1 A         1
2 AB        1
3 AC        1
4 BC        2

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388817

A base R option would be :

pair <- combn(unique(df$shop), 2, paste0, collapse = "")
commmon_product <- combn(unique(df$shop), 2, function(x) 
      with(df, length(intersect(product[shop == x[1]], product[shop == x[2]]))))

data.frame(pair, commmon_product)
#  pair commmon_product
#1   AB               1
#2   AC               1
#3   BC               2

For every combination of shop values we find out how many product intersect using combn.

Upvotes: 4

Related Questions