R help - Apr 2011 - indexing into a data.frame using another data.frame that also contains values for replacement

Hello all,

     I have a quandry I have been scratching my head about for a
while. I've searched the manual and the web and have not been able to
find an acceptable result, so I am hoping for some help.

     I have two data frames and I want to index into the first using
the second, and replace the specific values I have indexed with more
values from the second data.frame. I can do this using a loop, but I
wanted a quicker solution with no loops involved.

Although my data set is much larger than this, a small example of what
I am trying to do is as follows:

df1 <- data.frame(rows=c("A","B","C",
"B", "C", "A"),
columns=c("21_2", "22_2", "23_2",
"21_2", "22_2", "23_2"),
values=c(3.3, 2.5, 67.2, 44.3, 53, 66))
df2 <- data.frame(matrix(rep(NA, length(df1$values)),nrow=3, ncol=3))
names(df2) <- c("21_2", "22_2", "23_2")
row.names(df2) <- c("A", "B", "C")
> df1
  rows columns values
1    A    21_2    3.3
2    B    22_2    2.5
3    C    23_2   67.2
4    B    21_2   44.3
5    C    22_2   53.0
6    A    23_2   66.0


> df2
  21_2 22_2 23_2
A   NA   NA   NA
B   NA   NA   NA
C   NA   NA   NA


     Note that none of the same locations in df2 are specified twice
in df2, so I'm not worried about over-writing it.

    I have tried 'mapply' and 'replace', but apparently either
they do
not work well for this or I don't understand how to use them properly
for this purpose. My understanding is that 'replace' needs a vector
input and that one cannot create a vector of vectors, so I couldn't
pass my indices to 'replace'.

     When I tried mapply, the code I used was something like what follows:

df3 <- mapply('[<-' , df2, paste(as.character(df1$rows),
as.character(df1$columns), sep=', '), df1$values)

but it yields the following strange result
> df3
        21_2 22_2 23_2 <NA> <NA> <NA>
          NA   NA   NA   NA   NA   NA
          NA   NA   NA   NA   NA   NA
          NA   NA   NA   NA   NA   NA
A, 21_2  3.3  2.5 67.2 44.3   53   66


What I want to see is the following:
>df3
    21_2   22_2  23_2
A   3.3    NA    66.0
B   44.3  2.5    NA
C   NA   53.0   67.2


     I will greatly appreciate any help that can be given as I am
completely bamboozled by this problem and although I found many useful
things in my search for an answer, I did not find out how to do this.

Thanks,

Alice

Alice Wines wrote:
> 
> Hello all,
> 
>      I have a quandry I have been scratching my head about for a
> while. I've searched the manual and the web and have not been able to
> find an acceptable result, so I am hoping for some help.
> 
>      I have two data frames and I want to index into the first using
> the second, and replace the specific values I have indexed with more
> values from the second data.frame. I can do this using a loop, but I
> wanted a quicker solution with no loops involved.
> 
> Although my data set is much larger than this, a small example of what
> I am trying to do is as follows:
> 
> df1 <- data.frame(rows=c("A","B","C",
"B", "C", "A"),
> columns=c("21_2", "22_2", "23_2",
"21_2", "22_2", "23_2"),
> values=c(3.3, 2.5, 67.2, 44.3, 53, 66))
> df2 <- data.frame(matrix(rep(NA, length(df1$values)),nrow=3, ncol=3))
> names(df2) <- c("21_2", "22_2", "23_2")
> row.names(df2) <- c("A", "B", "C")
> 
>> df1
>   rows columns values
> 1    A    21_2    3.3
> 2    B    22_2    2.5
> 3    C    23_2   67.2
> 4    B    21_2   44.3
> 5    C    22_2   53.0
> 6    A    23_2   66.0
> 
> .......
> 
> What I want to see is the following:
> 
>>df3
>     21_2   22_2  23_2
> A   3.3    NA    66.0
> B   44.3  2.5    NA
> C   NA   53.0   67.2
> 
How about this (I have converted df2 into a matrix)

df2 <- matrix(rep(NA, length(df1$values)),nrow=3, ncol=3)
colnames(df2) <- c("21_2", "22_2", "23_2")
rownames(df2) <- c("A", "B", "C")

df2[cbind(df1$rows,df1$columns)] <- df1$values

# convert df2 to a data.frame

Berend--
View this message in context:
http://r.789695.n4.nabble.com/indexing-into-a-data-frame-using-another-data-frame-that-also-contains-values-for-replacement-tp3487147p3487234.html
Sent from the R help mailing list archive at Nabble.com.

Hi:

Here are two possibilities:

 df1 <- data.frame(rows=c("A","B","C",
"B", "C", "A"),
 columns=c("21_2", "22_2", "23_2",
"21_2", "22_2", "23_2"),
 values=c(3.3, 2.5, 67.2, 44.3, 53, 66))

with(df1, xtabs(values ~ rows + columns))

    columns
rows 21_2 22_2 23_2
   A  3.3  0.0 66.0
   B 44.3  2.5  0.0
   C  0.0 53.0 67.2

library(reshape2)
dcast(df1, rows ~ columns)

Using values as value column: use value_var to override.
  rows 21_2 22_2 23_2
1    A  3.3   NA 66.0
2    B 44.3  2.5   NA
3    C   NA 53.0 67.2

HTH,
Dennis

On Sat, Apr 30, 2011 at 4:18 PM, Alice Wines <aabroadh at ncsu.edu>
wrote:
> Hello all,
>
> ? ? I have a quandry I have been scratching my head about for a
> while. I've searched the manual and the web and have not been able to
> find an acceptable result, so I am hoping for some help.
>
> ? ? I have two data frames and I want to index into the first using
> the second, and replace the specific values I have indexed with more
> values from the second data.frame. I can do this using a loop, but I
> wanted a quicker solution with no loops involved.
>
> Although my data set is much larger than this, a small example of what
> I am trying to do is as follows:
>
> df1 <- data.frame(rows=c("A","B","C",
"B", "C", "A"),
> columns=c("21_2", "22_2", "23_2",
"21_2", "22_2", "23_2"),
> values=c(3.3, 2.5, 67.2, 44.3, 53, 66))
> df2 <- data.frame(matrix(rep(NA, length(df1$values)),nrow=3, ncol=3))
> names(df2) <- c("21_2", "22_2", "23_2")
> row.names(df2) <- c("A", "B", "C")
>
>> df1
> ?rows columns values
> 1 ? ?A ? ?21_2 ? ?3.3
> 2 ? ?B ? ?22_2 ? ?2.5
> 3 ? ?C ? ?23_2 ? 67.2
> 4 ? ?B ? ?21_2 ? 44.3
> 5 ? ?C ? ?22_2 ? 53.0
> 6 ? ?A ? ?23_2 ? 66.0
>
>
>
>> df2
> ?21_2 22_2 23_2
> A ? NA ? NA ? NA
> B ? NA ? NA ? NA
> C ? NA ? NA ? NA
>
>
> ? ? Note that none of the same locations in df2 are specified twice
> in df2, so I'm not worried about over-writing it.
>
> ? ?I have tried 'mapply' and 'replace', but apparently
either they do
> not work well for this or I don't understand how to use them properly
> for this purpose. My understanding is that 'replace' needs a vector
> input and that one cannot create a vector of vectors, so I couldn't
> pass my indices to 'replace'.
>
> ? ? When I tried mapply, the code I used was something like what follows:
>
> df3 <- mapply('[<-' , df2, paste(as.character(df1$rows),
> as.character(df1$columns), sep=', '), df1$values)
>
> but it yields the following strange result
>
>> df3
> ? ? ? ?21_2 22_2 23_2 <NA> <NA> <NA>
> ? ? ? ? ?NA ? NA ? NA ? NA ? NA ? NA
> ? ? ? ? ?NA ? NA ? NA ? NA ? NA ? NA
> ? ? ? ? ?NA ? NA ? NA ? NA ? NA ? NA
> A, 21_2 ?3.3 ?2.5 67.2 44.3 ? 53 ? 66
>
>
> What I want to see is the following:
>
>>df3
> ? ?21_2 ? 22_2 ?23_2
> A ? 3.3 ? ?NA ? ?66.0
> B ? 44.3 ?2.5 ? ?NA
> C ? NA ? 53.0 ? 67.2
>
>
> ? ? I will greatly appreciate any help that can be given as I am
> completely bamboozled by this problem and although I found many useful
> things in my search for an answer, I did not find out how to do this.
>
> Thanks,
>
> Alice
>
> ______________________________________________
> 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.
>

On Apr 30, 2011, at 4:18 PM, Alice Wines wrote:
> Hello all,
>
>     I have a quandry I have been scratching my head about for a
> while. I've searched the manual and the web and have not been able to
> find an acceptable result, so I am hoping for some help.
>
>     I have two data frames and I want to index into the first using
> the second, and replace the specific values I have indexed with more
> values from the second data.frame. I can do this using a loop, but I
> wanted a quicker solution with no loops involved.
>
> Although my data set is much larger than this, a small example of what
> I am trying to do is as follows:
>
> df1 <- data.frame(rows=c("A","B","C",
"B", "C", "A"),
> columns=c("21_2", "22_2", "23_2",
"21_2", "22_2", "23_2"),
> values=c(3.3, 2.5, 67.2, 44.3, 53, 66))
> df2 <- data.frame(matrix(rep(NA, length(df1$values)),nrow=3, ncol=3))
> names(df2) <- c("21_2", "22_2", "23_2")
> row.names(df2) <- c("A", "B", "C")
>
>> df1
>  rows columns values
> 1    A    21_2    3.3
> 2    B    22_2    2.5
> 3    C    23_2   67.2
> 4    B    21_2   44.3
> 5    C    22_2   53.0
> 6    A    23_2   66.0
>
 > require(Matrix)
 > xtabs(values~rows+columns, data=df1, sparse=TRUE)
3 x 3 sparse Matrix of class "dgCMatrix"
   21_2 22_2 23_2
A  3.3  .   66.0
B 44.3  2.5  .
C  .   53.0 67.2
>
>
>> df2
>  21_2 22_2 23_2
> A   NA   NA   NA
> B   NA   NA   NA
> C   NA   NA   NA
>
>
>     Note that none of the same locations in df2 are specified twice
> in df2, so I'm not worried about over-writing it.
>
>    I have tried 'mapply' and 'replace', but apparently
either they do
> not work well for this or I don't understand how to use them properly
> for this purpose. My understanding is that 'replace' needs a vector
> input and that one cannot create a vector of vectors, so I couldn't
> pass my indices to 'replace'.
>
>     When I tried mapply, the code I used was something like what  
> follows:
>
> df3 <- mapply('[<-' , df2, paste(as.character(df1$rows),
> as.character(df1$columns), sep=', '), df1$values)
>
> but it yields the following strange result
>
>> df3
>        21_2 22_2 23_2 <NA> <NA> <NA>
>          NA   NA   NA   NA   NA   NA
>          NA   NA   NA   NA   NA   NA
>          NA   NA   NA   NA   NA   NA
> A, 21_2  3.3  2.5 67.2 44.3   53   66
>
>
> What I want to see is the following:
>
>> df3
>    21_2   22_2  23_2
> A   3.3    NA    66.0
> B   44.3  2.5    NA
> C   NA   53.0   67.2

-- 
David Winsemius, MD
Heritage Laboratories
West Hartford, CT