Data cleaning in R: creating "duplicates"

Question

I got a data set in the following format:

DF1 <- data.frame(
  TakerId = c(1,4,7,10,13),
  GiverId = c(3,2,11,4,10),
  Col1= c(24,28,26,20,23),
  Col2= c(37,31,38,35,39),
  Col3= c(44,48,43,45,41)
)
DF1

 TakerId GiverId Col1 Col2 Col3
       1       3   24   37   44
       4       2   28   31   48
       7      11   26   38   43
      10       4   20   35   45
      13      10   23   39   41

and I need to merge the first two columns in one single column that has to be called UserId (keeping duplicates).

Then, a new column should be created to indicated whether each UserId is a Taker or a Giver.

Output wanted:

 UserId  Role Col1 Col2 Col3
      1 Taker   24   37   44
      4 Taker   28   31   48
      7 Taker   26   38   43
     10 Taker   20   35   45
     13 Taker   23   39   41
      3 Giver   24   37   44
      2 Giver   28   31   48
     11 Giver   26   38   43
      4 Giver   20   35   45
     10 Giver   23   39   41

score 3 · Accepted Answer · answered Feb 10 '18 at 17:31

We can gather the 'Id' columns into 'long' format, then strip off the 'Id' from the 'Role' and select the columns in the order wanted

library(tidyverse)
DF1 %>%
   gather(Role, UserId, TakerId:GiverId) %>%
   mutate(Role = str_sub(Role, 1, 5)) %>% 
   select(UserId, Role, Col1:Col3)
#   UserId  Role Col1 Col2 Col3
#1       1 Taker   24   37   44
#2       4 Taker   28   31   48
#3       7 Taker   26   38   43
#4      10 Taker   20   35   45
#5      13 Taker   23   39   41
#6       3 Giver   24   37   44
#7       2 Giver   28   31   48
#8      11 Giver   26   38   43
#9       4 Giver   20   35   45
#10     10 Giver   23   39   41

Data cleaning in R: creating "duplicates"

1 Answers1