How to identify similar rows in a data frame?

Question

I am trying to find out how to compare elements of my df1 with df2 and count their frequency. My df1 and df2 are like this:

var1 = c(1, 2, 3, 4, 5, 6, 7) 
var2 = c(1, 1, 2, 3, 4, 5, 6) 
value = c(0, 0.75, 0.51, 0.42, 0.31, 0.22, 0.11)
freq = c(1,1,1,1,1,1,1) 
df1 = data.frame(var1, var2, value, freq)

var1 = c(1, 2, 3, 4, 5, 6, 7) 
var2 = c(1, 2, 3, 5, 4, 6, 8) 
value = c(0, 0.75, 0.42, 0.41, 0.31, 0, 0)
freq = c(1,1,1,1,1,1,1) 
df2 = data.frame(var1, var2, value, freq)

so I would like a df3 with rows that are similar in df1 and df2

From the above example df3 would be:

var1=c(1,5)
var2=c(1,4)
value=c(0,0.31) 
freq=c(1,1)
df3=data.frame(var1, var2, value, freq)

@IceCreamToucan good question! I updated my example - but the freq do not need to match just var1, var2 and value, The freq in df3= freq [df1] + freq[df2] — Rstudent, Aug 28 '19 at 21:12
If it should be (freq in df1) + (freq in df2) then your desired output (df3) should have 2 as the frequency instead of 1 right? — IceCreamToucan, Aug 28 '19 at 21:14

IceCreamToucan · Answer 1 · 2019-08-28T21:18:43.277

Without the frequency part this is just a merge with default settings (i.e. inner join on all variables). To get the frequency part you can use count after grouping by all variables, then inner_join (dplyr merge equivalent) and add the individual frequencies.

I modified df1 just to check that the count part works as intended.

merge(df1, df2)
#    var1 var2 value
# 1:    1    1  0.00
# 2:    5    4  0.31

library(dplyr)

df1 <- df1[c(1, 1, seq(nrow(df1))),]

df1 %>% 
  group_by_all %>% 
  count(name = 'n1') %>% 
  inner_join(
    df2 %>% 
      group_by_all %>% 
      count(name = 'n2')
  ) %>% 
  mutate(n = n1 + n2) %>% 
  select(-n1, -n2)

# # A tibble: 2 x 4
# # Groups:   var1, var2, value [2]
#    var1  var2 value     n
#   <dbl> <dbl> <dbl> <int>
# 1     1     1  0        4
# 2     5     4  0.31     2

many thanks for your help -apologise for this - but I am struggling with https://stackoverflow.com/questions/61187493/identify-consecutive-sequences-based-on-a-given-variable question. I have no clue how to indetify consecutive sequences based on a given variable. Any help is welcomed. many thanks — Rstudent, Apr 13 '20 at 16:11

score 0 · Accepted Answer · answered Aug 28 '19 at 21:06

0

like this?

library(dplyr)

df3 = df1[apply(df1 == df2, 1, all), ]
df3 %>% group_by_all() %>% summarise(freq= n())

answered Aug 28 '19 at 21:06

tickly potato

122
7

How to identify similar rows in a data frame?

2 Answers2