CODEWITHSUNDEEP
r
mamatv
mamatv

Reputation: 3661

Finding matching pairs of identifiers in a data frame

I have a dataframe df:

id1 id2 action
  1   2     10
  1   3     11
  1   4     21
  2   1      6
...

It means, the user id1 do something (10) to user id2, and id2 do something (6) to id1.

Now I want to create a new column, called partner_action, which basically records what did the partner do. So it will look like:

id1 id2 action partner_action
  1   2     10              6
  2   1      6             10
  1   3     11              9
  3   1      9             11

I tried:

df$partner_action = df[df$id2 == df$id1,]$action

But of course, it does not work.

I thought about make a copy of df, called df_copy then:

df$partner_action = df_copy[df_copy$id1 == df$id2,]$action

But is there a better way to do it?

Upvotes: 0

Views: 755

Answers (2)

Burry Xie
Burry Xie

Reputation: 95

for(i in 1:nrow(df))
{df[i,4]<-df[which(df$id1==df[i,2]&df$id2==df[i,1]),3]}

Upvotes: 0

josliber
josliber

Reputation: 44310

Basically you want to merge df with itself, matching pairs of (id2, id1) with pairs of (id1, id2). You can do this in R either with merge or match:

df$partner_action <- with(df, action[match(paste(id2, id1), paste(id1, id2))])
df
#   id1 id2 action partner_action
# 1   1   2     10              6
# 2   2   1      6             10
# 3   1   3     11              9
# 4   3   1      9             11

Data:

(df <- data.frame(id1=c(1, 2, 1, 3), id2=c(2, 1, 3, 1), action=c(10, 6, 11, 9)))
#   id1 id2 action
# 1   1   2     10
# 2   2   1      6
# 3   1   3     11
# 4   3   1      9

Upvotes: 1

Related Questions