2

I have two simple data frames of different length. The first data frame contains a list of names and corresponding numeric user ids.

names<-c("User1", "User2", "User3", "User4")
user_id<-c(1,2,3,4)
frame1<-as.data.frame(cbind(user_id, names))

user_id  names
1        User1
2        User2
3        User3
4        User4

The second dataframe has a different length and contains a column with comments and the corresponding user_id of the person who made the comment. A user could make multiple comments resulting in multiple rows with the same user_ids in the colum frame2$comment_by

comment_by<-c(1,1,1,1,2,2,2,3,3,3,4,4)
comments<-c("comment1", "comment2","comment3","comment4","comment5","comment6","comment7","comment8","comment9","comment10","comment11","comment12")

frame2<-as.data.frame(cbind(comment_by, comments))

extracol<-c("full names")
frame2[,extracol]<-NA
frame2<-frame2[,c("comment_by", "full names", "comments")]

Resulting in a a data frame like this

comment_by   full names   comments
1            NA           comment1
1            NA           comment2
1            NA           comment3
1            NA           comment4
2            NA           comment5
2            NA           comment6
2            NA           comment7
3            NA           comment8
3            NA           comment9
3            NA           comment10
4            NA           comment11
4            NA           comment12

As you can see, I've already added an empty extra column to the second data frame ("full names"). My aim is now to assign the correct names from frame1 to the frame2$'full names' column for each comment, as the frame2$comment_by column only shows the user_id, but not the corresponding names. So it should be like this:

comment_by   full names   comments
1            User1           comment1
1            User1           comment2
1            User1           comment3
1            User1           comment4
2            User2           comment5
2            User2           comment6
2            User2           comment7
3            User3           comment8
3            User3           comment9
3            User3           comment10
4            User4           comment11
4            User4           comment12

I tried some simple for loop/ if condition combination, but could get it to work.

    x=1
    for (i in 1:length(frame2$comments)){
   if (is.na(frame2$comment_by[i])==is.na(frame1$user_id[x])) {
    frame2$`full names`[i]<-frame1$names[x]
    }else{x=x+1}
   i=i+1  }

Do I even need the for loop? I could imagine there's a simple function that already deals with that kind of problems. If so, please let me know. (Anyway, for the sake of learning I would also be glad to see if my loop can be fixed.)

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
Rasul
  • 23
  • 3

1 Answers1

3

Welcome to StackeOverflow Rasul and thank you for providing a minimal reproducible example.

You don't need to add an extra column to do what you want. You can directly use the base merge() function that allows to join two data frame based on designated columns:

names<-c("User1", "User2", "User3", "User4")
user_id<-c(1,2,3,4)
frame1<-as.data.frame(cbind(user_id, names))

comment_by<-c(1,1,1,1,2,2,2,3,3,3,4,4)
comments<-c("comment1", "comment2","comment3","comment4","comment5","comment6","comment7","comment8","comment9","comment10","comment11","comment12")

frame2<-as.data.frame(cbind(comment_by, comments))

# base R way
merge(frame2, frame1, by.x = "comment_by", by.y = "user_id")
#>    comment_by  comments names
#> 1           1  comment1 User1
#> 2           1  comment2 User1
#> 3           1  comment3 User1
#> 4           1  comment4 User1
#> 5           2  comment5 User2
#> 6           2  comment6 User2
#> 7           2  comment7 User2
#> 8           3  comment8 User3
#> 9           3  comment9 User3
#> 10          3 comment10 User3
#> 11          4 comment11 User4
#> 12          4 comment12 User4

# dplyr way
dplyr::inner_join(frame2, frame1, by = c("comment_by" = "user_id"))
#>    comment_by  comments names
#> 1           1  comment1 User1
#> 2           1  comment2 User1
#> 3           1  comment3 User1
#> 4           1  comment4 User1
#> 5           2  comment5 User2
#> 6           2  comment6 User2
#> 7           2  comment7 User2
#> 8           3  comment8 User3
#> 9           3  comment9 User3
#> 10          3 comment10 User3
#> 11          4 comment11 User4
#> 12          4 comment12 User4

Created on 2018-10-12 by the reprex package (v0.2.1)

Rekyt
  • 354
  • 1
  • 8