It would be helpful if you included the expected output for your example, but I
think the following does what you want by using merge() for each lookup:
f0 <- function(inputDF, lookupDF)
{
tmp1 <- merge(inputDF, lookupDF, by.x="Left", by.y="input",all.x=TRUE)
tmp2 <- merge(tmp1, lookupDF, by.x="Right", by.y="input", all.x=TRUE)
with(tmp2, data.frame(ID=ID, Right=output.x, Left=output.y)[order(ID), ])
}
# Your example data with an ID column added to track where the output rows came
from
myInputDF <- data.frame(
ID = 1:10,
Left = c(9, 4, 2, 6, 3, 4, 3, 4, 10, 9),
Right = c(8, 3, 1, 5, 1, 1, 2, 2, 8, 10))
myLookupDF <- data.frame(
input = c(5, 10, 4, 8, 6, 5, 7, 2, 9, 10, 2),
output = c(1, 1, 2, 3, 5, 6, 6, 7, 7, 7, 8))
f0(myInputDF, myLookupDF)
# ID Right Left
# 12 1 7 3
# 9 2 2 NA
# 1 3 7 NA
# 2 3 8 NA
# 10 4 5 6
# 11 4 5 1
# 3 5 NA NA
# 4 6 2 NA
# 5 7 NA 7
# 6 7 NA 8
# 7 8 2 7
# 8 8 2 8
# 13 9 1 3
# 14 9 7 3
# 15 10 7 1
# 16 10 7 7
Bill Dunlap
Spotfire, TIBCO Software
wdunlap tibco.com
> -----Original Message-----
> From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On
> Behalf
> Of Juan Antonio Balbuena
> Sent: Thursday, July 25, 2013 8:13 AM
> To: r-help@r-project.org
> Subject: [R] transform dataframe with look-up table
>
>
> Hello
> I hope that there is a simple solution to this apparently complex problem.
> Any help will be much appreciated:
> I have a dataframe with Left and Right readings (that is, elements in each
> row are paired). For instance,
> Left Right
> [1] 9 8
> [2] 4 3
> [3] 2 1
> [4] 6 5
> [5] 3 1
> [6] 4 1
> [7] 3 2
> [8] 4 2
> [9] 10 8
> [10] 9 10
> I need to produce a new data frame where the values are transformed
> according to a look-up table such as
> input output
> [1] 5 1
> [2] 10 1
> [3] 4 2
> [4] 8 3
> [5] 6 5
> [6] 5 6
> [7] 7 6
> [8] 2 7
> [9] 9 7
> [10] 10 7
> [11] 2 8
> So [1, ] in the new dataframe would be 7 3. Quite simple so far, but what
> makes things complicated is the multiple outputs for a single input. In
> this
> example, 10 corresponds to 1 and 7 so [9, ] in the input dataframe must
> yield two rows in its output counterpart: 1 3 and 7 3. Likewise the output
> for [10, ] should be 7 1 and 7 7. In addition, given that 3 and 1 are
> missing as inputs the output for [5, ] should be NA NA.
> Thank you very much for your time.
> Juan Antonio Balbuena
>
> --
>
> Dr. Juan A. Balbuena
> Marine Zoology Unit
> Cavanilles Institute of Biodiversity and Evolutionary Biology
> University of
> Valencia
> [1]http://www.uv.es/~balbuena
> P.O. Box 22085
> [2]http://www.uv.es/cavanilles/zoomarin/index.htm
> 46071 Valencia, Spain
> [3]http://cetus.uv.es/mullpardb/index.html
> e-mail: [4]j.a.balbu...@uv.es tel. +34 963 543 658 fax +34 963 543
> 733
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> NOTE! For shipments by EXPRESS COURIER use the following street address:
> C/ Catedrático José Beltrán 2, 46980 Paterna (Valencia), Spain.
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> References
>
> 1. http://www.uv.es/%7Ebalbuena
> 2. http://www.uv.es/cavanilles/zoomarin/index.htm
> 3. http://cetus.uv.es/mullpardb/index.html
> 4. mailto:j.a.balbu...@uv.es
> ______________________________________________
> R-help@r-project.org mailing list
> 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.
______________________________________________
R-help@r-project.org mailing list
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.
