Add specific values from a dataframe to a specific location in a matrix

Question

I would like to add values I have stored in a dataframe, to the place in the matrix where they belong.

This is the data:

dataframe:

  df <- read.table(text=' A B C
   name1  add1  1       
   name2  add1  2 
   name3  add1  3
   name1  add2  1       
   name2  add2  2 ',   header=TRUE)
    > df
      A    B C
1 name1 add1 1
2 name2 add1 2
3 name3 add1 3
4 name1 add2 1
5 name2 add2 2

Matrix:

ma <- matrix(NA, ncol=2, nrow=3)
colnames(ma)<-c('add1', 'add2')
rownames(ma)<-c('name1', 'name2', 'name3')
> ma
      add1 add2
name1   NA   NA
name2   NA   NA
name3   NA   NA

So the unique entries in df$A are the rows of ma The unique entries in df$B are the column of ma

The resulting matrix would look like this:

> ma
      add1 add2
name1    1    1
name2    2    2
name3    3    NA

Note that the dataframe could be in any order, and some combinations of name and add may not exist (so there should still be an NA in ma)

So far, I was not able to produce working code to take the values out of df and write them into ma

I would be grateful for your suggestions.

Thank you!

Possible duplicate of [How to reshape data from long to wide format?](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) — acylam, Nov 20 '17 at 16:17
@KevinRoth. Please try not to edit your sample data and expected output after there are already answers (and an accepted answer). To those viewing the post for the first time it makes those answers look incorrect. — Mike H., Nov 20 '17 at 16:25

score 4 · Accepted Answer · answered Nov 20 '17 at 16:06

4

If I understand your problem correctly it is simply about turning data from "long format" to "wide format". This can easily be achieved by using the package reshape2.

library(reshape2)

dcast(df, A ~ B)
#        A add1 add2
# 1 name1    1    1
# 2 name2    2    2
# 3 name3    3    3

answered Nov 20 '17 at 16:06

Manuel Bickel

2,156
2
11
22

Thank you for this. This would work fine, however I will run into problems if combinations of name and add do not exist. The question is not clear in this regard, I will amend it. – Kevin Roth Nov 20 '17 at 16:08
It actually creates NAs where data is missing. Very simple solution, thank you! – Kevin Roth Nov 20 '17 at 16:13
1

Please also have a look at the answer of @useR or @Matt W.. The `reshape2` package is a precedent of the `tidyr` package, see [here](http://tidyr.tidyverse.org/). In simple cases `tidyr` is probably better, only in more complicated cases you might have to turn turn to `reshape2`. I am just used to that one :-). – Manuel Bickel Nov 20 '17 at 16:17
Also note that `dcast` and `melt` has good integration with `data.table` as it is imported into the package. – acylam Nov 20 '17 at 16:21

score 2 · Answer 2 · answered Nov 20 '17 at 16:11

There is no need to create an empty matrix, then fill in the values, just reshape your original df. The following will work even if you have NA combinations:

library(tidyr)

spread(df, B, C)

Result:

      A add1 add2
1 name1    1   NA
2 name2   NA    2
3 name3    3    3

Or if you prefer a matrix:

library(tidyverse)

df %>%
  spread(B, C) %>%
  remove_rownames() %>%
  column_to_rownames("A") %>%
  as.matrix()

Result:

      add1 add2
name1    1   NA
name2   NA    2
name3    3    3

Data:

df <- read.table(text=' A B C
                 name1  add1  1       
                 name2  add1  NA 
                 name3  add1  3
                 name1  add2  NA       
                 name2  add2  2 
                 name3  add2  3',   header=TRUE)

Thank you! @Manuel Bickel already provided a working solution, but having more options is great :) — Kevin Roth, Nov 20 '17 at 16:14

score 1 · Answer 3 · answered Nov 20 '17 at 16:10

1

You can do this using the spread function from tidyr

library(tidyr)

spread(df, B, C)

answered Nov 20 '17 at 16:10

Matt W.

3,692
2
23
46

Add specific values from a dataframe to a specific location in a matrix

3 Answers3