[R] transform dataframe with look-up table

[William Dunlap]

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-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf
> Of Juan Antonio Balbuena
> Sent: Thursday, July 25, 2013 8:13 AM
> To: r-help at 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.balbuena at 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.balbuena at uv.es
> ______________________________________________
> R-help at 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.