CODEWITHSUNDEEP
r
Laura
Laura

Reputation: 113

Count occurence of values for every possible pair

I have a list of ids and places where these ids have been. Now I want to find pairs of ids that have most places in common.

My data frame looks like this:

  id        place           
  Dave      Paris
  Dave      Moscow
  Dave      New York
  Joe       New York
  Joe       Tokyo
  Stuart    Paris
  Stuart    Moscow
  Stuart    New York
  Stuart    Tokyo

The results should look like this:

pair1   pair2       count
Dave    Joe         1
Dave    Stuart      3
Joe     Stuart      2

I tried split to divide the data:

temp = split(df$name, df$place)

So I now have the places grouped, but I didn't get further.

The original dataset has about 100.000 unique ids.

Can anybody help me to find a good and fast solution? Thanks!

Upvotes: 2

Views: 451

Answers (3)

bergant
bergant

Reputation: 7232

Or... 

library(dplyr)

df1 %>%
  left_join(df1, by = "place") %>%
  filter(id.x < id.y) %>%
  group_by(id.x, id.y) %>%
  summarise(count = n())

EDIT: If IDs are factors operator < won't work. Conversion adds another line to the solution (credits to Steven Beaupré):

df1 %>%
  left_join(df1, by = "place") %>%
  mutate_each(funs(as.character(.))) %>%
  filter(id.x < id.y) %>%
  group_by(id.x, id.y) %>%
  summarise(count = n())

Upvotes: 3

Steven Beaupr&#233;
Steven Beaupr&#233;

Reputation: 21641

For a dplyr-esque solution,

You could do:

left_join(df, df, by = "place") %>%
  rename(pair1 = id.x, pair2 = id.y) %>%
  filter(!pair1 == pair2, !duplicated(t(apply(., 1, sort))) == TRUE) %>% 
  count(pair1, pair2)

Upvotes: 1

akrun
akrun

Reputation: 887881

You may try

library(reshape2)
tbl <-  crossprod(table(df1[2:1]))
tbl[upper.tri(tbl, diag=TRUE)] <- 0
res <- subset(melt(tbl), value!=0)
colnames(res) <- c(paste0('pair',1:2), 'count')
row.names(res) <- NULL
res
#   pair1 pair2 count
#1    Joe  Dave     1
#2 Stuart  Dave     3
#3 Stuart   Joe     2

Or another option is

Subdf <- subset(merge(df1, df1, by.x='place',
               by.y='place'), id.x!=id.y)
Subdf[-1] <- t(apply(Subdf[-1], 1, sort))
aggregate(place~., unique(Subdf), FUN=length)
#  id.x   id.y place
#1 Dave    Joe     1
#2 Dave Stuart     3
#3  Joe Stuart     2

Upvotes: 3

Related Questions