CODEWITHSUNDEEP
rdplyrtidyverse
arteteco
arteteco

Reputation: 79

How add a column with a value based on the value of a field in a row?

let's say I have this kind of dataframe:

df <- tribble(
  ~ID,~Reply_to_ID, ~author,
  14,  NA,      "John",
  16,  14,      "Jack",
  18,  14,      "John",
  20,  22,      "Annie",
  22,  24,      "Peter",
  24,  16,      "Annie"
)

Every ID is unique for a message Every message can be a reply to another message

What I'd like is an extra column with the name of the author of the message replied to. For example, Jack replies to message 14, which was written by "John", then John also replies to his message, so I'd have something like 

  ~ID,~Reply_to_ID, ~author, ~Replied_to_author,
  14,  NA,      "John", NA,
  16,  14,      "Jack", "John",
  18,  14,      "John", "John",
  20,  22,      "Annie", "Peter",
  22,  24,      "Peter", "Annie",
  24,  16,      "Annie", "Jack"
)

Possibly, I'd like to use tidyverse to deal with the problem.

What I've tried so far has been something like

newDF <- mutate(
  df,
  Reply_to_author = filter(df, .data$id == .data$reply_to_message_id)
)

And stuff among the line. I know I can get the value by using something like filter(df, id == 14)$author , but I can't seem to be able to use mutate to create a new column for that value.

I hope I've been clear enough.

Upvotes: 2

Views: 45

Answers (2)

akrun
akrun

Reputation: 887971

We can use data.table

library(data.table)
setDT(df)[, Replied_to_author := author[match(Reply_to_ID, ID)]][]

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389325

We can match``Reply_to_ID to ID to get corresponding author.

In base R, we can do this using transform

transform(df, Replied_to_author = author[match(Reply_to_ID, ID)])

To fit this into tidyverse we can use mutate : 

library(dplyr)
df %>% mutate(Replied_to_author = author[match(Reply_to_ID, ID)])

#     ID Reply_to_ID author Replied_to_author
#  <dbl>       <dbl> <chr>  <chr>            
#1    14          NA John   NA               
#2    16          14 Jack   John             
#3    18          14 John   John             
#4    20          22 Annie  Peter            
#5    22          24 Peter  Annie            
#6    24          16 Annie  Jack

Upvotes: 3

Related Questions