[R] Populate one data frame with values from another dataframe for rows that match

Sat Oct 14 19:20:34 CEST 2017

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 <KWamae at kemri-wellcome.org>
wrote:

> Dear @Bert Gunter<mailto:bgunter.4567 at gmail.com>, I tried merge and I
> faced many challenges. @Rui Barradas<mailto:ruipbarradas at sapo.pt>
> solution is working.
>
> From: Bert Gunter <bgunter.4567 at gmail.com>
> Date: Friday, 13 October 2017 at 22:44
> To: Kevin Wamae <KWamae at kemri-wellcome.org>
> Cc: R-help <R-help at 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" <KWamae at kemri-wellcome.org<mailto:
> KWamae at 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 at r-project.org<mailto:R-help at 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 at 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]]