Apply unique ID to events where i = j and j = i

user_9794399 0
Viewed 44

In the following table:

set.seed(5)
df <- data.frame(year=rep(c(2010:2011), each=8),
                 i=rep(c('66','66','6','6','65','65','6','6'), times=2),
                 j=rep(c('66','6','66','6','65','6','65','6'), times=2),
                 count=round(runif(8, 0, 5),0))

There are several events where 'i = j' and 'i != j', I am trying to assign unique values to each of these events. In one scenario where 'i = j' (i.e. 66 = 66 or 6 = 6) I want to assign the value as '0'. However, if 'i != j' I want to assign that event as '1' or higher depending on each unique group e.g., '66_6' or '6_66' is assigned a '1' and '65_6' or '6_65' is assigned a '2'.

So far, I've only been able to figure out how to filter this table to include where these events, such as in the case of group 1 or 2, appear.

x <- df[paste(df$i,df$j,sep = "_") != paste(sapply(strsplit(paste(df$j,df$i,sep = "_"), "\\s+"), function(x) paste(rev(x), collapse=" ")),sep=""),]

If anyone has any advice on how to proceed, I'd appreciate it.

dataframe r
2 Answers

One method is to paste (str_c - from stringr), the row wise minimum, maximum (pmin/pmax respectively) of 'i', 'j' columns, get the index of unique elements, and replace those where i is not equal to j

library(dplyr)
library(stringr)
df %>% 
    mutate(ij =str_c(pmin(i, j), pmax(i, j), sep="_"),
    new = +(i != j), 
     new = replace(new, new ==1, match(ij[new ==1],
        unique(ij[new == 1]))), ij = NULL)

-output

    year  i  j count new
1  2010 66 66     1   0
2  2010 66  6     3   1
3  2010  6 66     5   1
4  2010  6  6     1   0
5  2010 65 65     1   0
6  2010 65  6     4   2
7  2010  6 65     3   2
8  2010  6  6     4   0
9  2011 66 66     1   0
10 2011 66  6     3   1
11 2011  6 66     5   1
12 2011  6  6     1   0
13 2011 65 65     1   0
14 2011 65  6     4   2
15 2011  6 65     3   2
16 2011  6  6     4   0

or using data.table

library(data.table)
setDT(df)[, new := 0][i != j, new := match(paste(pmin(i, j),
     pmax(i, j), sep="_"), unique(paste(pmin(i, j), pmax(i, j), sep="_")))]

-output

> df
    year  i  j count new
 1: 2010 66 66     1   0
 2: 2010 66  6     3   1
 3: 2010  6 66     5   1
 4: 2010  6  6     1   0
 5: 2010 65 65     1   0
 6: 2010 65  6     4   2
 7: 2010  6 65     3   2
 8: 2010  6  6     4   0
 9: 2011 66 66     1   0
10: 2011 66  6     3   1
11: 2011  6 66     5   1
12: 2011  6  6     1   0
13: 2011 65 65     1   0
14: 2011 65  6     4   2
15: 2011  6 65     3   2
16: 2011  6  6     4   0
user_37322710

Here is another data.table option using .GRP

setDT(df)[
  , new := .GRP * (i != j),
  paste0(pmin(i, j), "_", pmax(i, j))
][
  ,
  new := as.integer(factor(new)) - 1
]

which gives

> df
    year  i  j count new
 1: 2010 66 66     1   0
 2: 2010 66  6     3   1
 3: 2010  6 66     5   1
 4: 2010  6  6     1   0
 5: 2010 65 65     1   0
 6: 2010 65  6     4   2
 7: 2010  6 65     3   2
 8: 2010  6  6     4   0
 9: 2011 66 66     1   0
10: 2011 66  6     3   1
11: 2011  6 66     5   1
12: 2011  6  6     1   0
13: 2011 65 65     1   0
14: 2011 65  6     4   2
15: 2011  6 65     3   2
16: 2011  6  6     4   0
user_121587570
Related