Here's an approach that seems to work. I added an 11th case to
your data since you did not have a case where both Left and
Right had multiple values in the lookup table. This creates an
id value so that we can merge left and right separately and
then merge them back together:
# Create test data frames
Left <- c(9, 4, 2, 6, 3, 4, 3, 4, 10, 9, 2)
Right <- c(8, 3, 1, 5, 1, 1, 2, 2, 8, 10, 5)
ID <- 1:11
Pair <- data.frame(ID, Left, Right)
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)
Lookup <- data.frame(input, output)
# Merges
Lout <- merge(Pair, Lookup, by.x="Left", by.y="input",
all.x=TRUE)[,c("ID", "Left", "output")]
Rout <- merge(Pair, Lookup, by.x="Right", by.y="input",
all.x=TRUE)[, c("ID", "Right", "output")]
names(Rout)[3] <- "outputR"
names(Lout)[3] <- "outputL"
merge(Lout, Rout, all=TRUE)[,c(1, 2, 4, 3, 5)]
ID Left Right outputL outputR
1 1 9 8 7 3
2 2 4 3 2 NA
3 3 2 1 7 NA
4 3 2 1 8 NA
5 4 6 5 5 6
6 4 6 5 5 1
7 5 3 1 NA NA
8 6 4 1 2 NA
9 7 3 2 NA 7
10 7 3 2 NA 8
11 8 4 2 2 8
12 8 4 2 2 7
13 9 10 8 1 3
14 9 10 8 7 3
15 10 9 10 7 1
16 10 9 10 7 7
17 11 2 5 7 6
18 11 2 5 7 1
19 11 2 5 8 6
20 11 2 5 8 1
-------------------------------------
David L Carlson
Associate Professor of Anthropology
Texas A&M University
College Station, TX 77840-4352
-----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 10: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.
