Your example used one distinct studyno in DF1 and one distinct pf_mcl in
DF2. I think that makes it hard to see what is going on, but maybe I
completely misunderstand the problem. In any case, let's redefine myDF1
and myDF2. Note that myDF1 contains a studyno not in myDF2 and vice versa.
myDF1 <- structure(list(studyno = c("J1000/9", "J895/7", "J931/6", "J666/6",
"J1000/9", "J1000/9"), date = structure(c(17123, 17127, 17135,
17144, 17148, 17155), class = "Date"), pf_mcl = c(NA_integer_,
2L, 3L, 4L, 5L, NA_integer_
), year = c(2016, 2016, 2016, 2016, 2016, 2016)), .Names = c("studyno",
"date", "pf_mcl", "year"), row.names = c(NA, 6L), class = "data.frame")
myDF2 <- structure(list(studyno = c("J740/4", "J1000/9", "J895/7", "J931/6",
"J609/1", "J941/3"), pf_mcl = c(101L, 102L, 103L, 104L, 105L, 106L)),
.Names = c("studyno", "pf_mcl"), row.names = c(NA, 6L), class =
"data.frame")
m <- merge(myDF1, myDF2, by="studyno", all.x=TRUE, all.y=FALSE,
suffixes=c(".raw", ".curated"))
The results are:
> myDF1
studyno date pf_mcl year
1 J1000/9 2016-11-18 NA 2016
2 J895/7 2016-11-22 2 2016
3 J931/6 2016-11-30 3 2016
4 J666/6 2016-12-09 4 2016
5 J1000/9 2016-12-13 5 2016
6 J1000/9 2016-12-20 NA 2016
> myDF2
studyno pf_mcl
1 J740/4 101
2 J1000/9 102
3 J895/7 103
4 J931/6 104
5 J609/1 105
6 J941/3 106
> m
studyno date pf_mcl.raw year pf_mcl.curated
1 J1000/9 2016-11-18 NA 2016 102
2 J1000/9 2016-12-13 5 2016 102
3 J1000/9 2016-12-20 NA 2016 102
4 J666/6 2016-12-09 4 2016 NA
5 J895/7 2016-11-22 2 2016 103
6 J931/6 2016-11-30 3 2016 104
Now your problem is to combine the columns pf_mcl.raw and pf_mcl.curated in
the way you want. ifelse() may be useful for that.
Bill Dunlap
TIBCO Software
wdunlap tibco.com
On Fri, Oct 13, 2017 at 10:48 PM, Kevin Wamae <kwa...@kemri-wellcome.org>
wrote:
> Dear @Bert Gunter<mailto:bgunter.4...@gmail.com>, I tried merge and I
> faced many challenges. @Rui Barradas<mailto:ruipbarra...@sapo.pt>
> solution is working.
>
> From: Bert Gunter <bgunter.4...@gmail.com>
> Date: Friday, 13 October 2017 at 22:44
> To: Kevin Wamae <kwa...@kemri-wellcome.org>
> Cc: R-help <R-help@r-project.org>
> Subject: Re: [R] Populate one data frame with values from another
> dataframe for rows that match
>
> ?merge
>
> Bert
>
> On Oct 13, 2017 12:09 PM, "Kevin Wamae" <kwa...@kemri-wellcome.org<mailto:
> kwa...@kemri-wellcome.org>> wrote:
> I'm trying to populate the column “pf_mcl” in myDF1 with values from
> myDF2, where rows match based on column "studyno" but the solutions I have
> found so far don't seem to be giving me the desired output.
>
> Below is a snapshot of the data.frames.
>
> myDF1 <- structure(list(studyno = c("J1000/9", "J1000/9", "J1000/9",
> "J1000/9",
> "J1000/9", "J1000/9"), date = structure(c(17123, 17127, 17135,
> 17144, 17148, 17155), class = "Date"), pf_mcl = c(NA_integer_,
> NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
> ), year = c(2016, 2016, 2016, 2016, 2016, 2016)), .Names = c("studyno",
> "date", "pf_mcl", "year"), row.names = c(NA, 6L), class = "data.frame")
>
> myDF2 <- structure(list(studyno = c("J740/4", "J1000/9", "J895/7",
> "J931/6",
> "J609/1", "J941/3"), pf_mcl = c(0L, 0L, 0L, 0L, 0L, 0L)), .Names =
> c("studyno",
> "pf_mcl"), row.names = c(NA, 6L), class = "data.frame")
>
> myDF2 is a well curated subset of myDF1. Some rows in the two datasets
> match based on "studyno", one may find that values are missing in
> myDF1$pf_mcl or the values are wrong.
>
> All I want to do is identify a matching row in myDF2 and populate
> myDF1$pf_mcl with the value in myDF2$pf_mcl. If a row does not match based
> on “studyno”, the value should remain the same.
>
> It's probably worth mentioning, the two data frames have other columns...I
> have selected a few for example purposes.
>
>
>
> ______________________________________________________________________
>
> This e-mail contains information which is confidential. It is intended
> only for the use of the named recipient. If you have received this e-mail
> in error, please let us know by replying to the sender, and immediately
> delete it from your system. Please note, that in these circumstances, the
> use, disclosure, distribution or copying of this information is strictly
> prohibited. KEMRI-Wellcome Trust Programme cannot accept any responsibility
> for the accuracy or completeness of this message as it has been
> transmitted over a public network. Although the Programme has taken
> reasonable precautions to ensure no viruses are present in emails, it
> cannot accept responsibility for any loss or damage arising from the use of
> the email or attachments. Any views expressed in this message are those of
> the individual sender, except where the sender specifically states them to
> be the views of KEMRI-Wellcome Trust Programme.
> ______________________________________________________________________
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org<mailto:R-help@r-project.org> mailing list -- To
> UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
> ______________________________________________________________________
>
> This e-mail contains information which is confidential. It is intended
> only for the use of the named recipient. If you have received this e-mail
> in error, please let us know by replying to the sender, and immediately
> delete it from your system. Please note, that in these circumstances, the
> use, disclosure, distribution or copying of this information is strictly
> prohibited. KEMRI-Wellcome Trust Programme cannot accept any responsibility
> for the accuracy or completeness of this message as it has been
> transmitted over a public network. Although the Programme has taken
> reasonable precautions to ensure no viruses are present in emails, it
> cannot accept responsibility for any loss or damage arising from the use of
> the email or attachments. Any views expressed in this message are those of
> the individual sender, except where the sender specifically states them to
> be the views of KEMRI-Wellcome Trust Programme.
> ______________________________________________________________________
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
