Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

r - ifelse merge and merge without duplicate columns

I have the following data frames

#Data to be matched
Data <- data.frame(
  Register = c(141 ,565, 1411, 141, 230, 230, 230, 423, 423,423,561),
  Name = c("Steve","Steve",  "Steve", "Steve" ,"Robin", "bin", "Robi", "Her", "Her", "Hero","Hero"))

#Mapping table
Map = data.frame(
  Register = c(141,230,423),
  Name = c("Steve","Robin","Hero"),
  Class = c("3A", "5B", "6D"),
  Gender = c("M", "F", "M"))

What I would like to do is find out the class and gender of the students using the mapping table based on their Register and Name.

#Merge using Register first
Joined = merge(x = Data,y = Map[,c("Class", "Register", "Gender")],by.x = "Register", by.y = "Register", all.x = TRUE)

#Output
   Register  Name Class Gender
1       141 Steve    3A      M
2       141 Steve    3A      M
3       230 Robin    5B      F
4       230   bin    5B      F
5       230  Robi    5B      F
6       423   Her    6D      M
7       423   Her    6D      M
8       423  Hero    6D      M
9       561  Hero  <NA>   <NA>
10      565 Steve  <NA>   <NA>
11     1411 Steve  <NA>   <NA>

Here comes the problem; I would like to do a Merge again but this time using the Name. I did not merge using the Name initially because the Name could be different from Mapping table.

This is what I would like to do; If the Class/Gender is NA, do another merge but using Name this time round. I tried to do an ifelse statement that goes like "If "Class" is NA, merge using the name, else, stay the same".

Joined = ifelse(Joined[is.na(Joined$Class),], merge(Data, Map,by.x = "Name", by.y = "Name", all.x = TRUE), Joined)

Another way for me to solve this issue is for me to split the dataframe into 2 parts; rows without NA in "Class" and rows with NA in "Class". But this will make my script look very untidy and very hard to work with.

edit: Added the "Gender" column in the dataframe


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You can use match to fill up the missing Class from Map.

i <- is.na(Joined$Class)
j <- c("Class","Gender")
Joined[i,j] <- Map[match(Joined$Name[i], Map$Name), j]
Joined
#   Register  Name Class Gender
#1       141 Steve    3A      M
#2       141 Steve    3A      M
#3       230 Robin    5B      F
#4       230   bin    5B      F
#5       230  Robi    5B      F
#6       423   Her    6D      M
#7       423   Her    6D      M
#8       423  Hero    6D      M
#9       561  Hero    6D      M
#10      565 Steve    3A      M
#11     1411 Steve    3A      M

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...