-1

I am new to R and have been at this problem for hours. I can't seem to find the right answer online and therefore turn to you guys. Other posts that I have seen use 3 columns instead of two.

I have a df with two columns: (company) name and id. Example df:

Name  id
1 NameA 111
2 NameB 222
3 NameC 111
4 NameD 444
5 NameA 555

I want to turn this column into a two-mode (incidence) matrix with the row names being the names of the companies and the column names being the id number. Example desired result:

        111 222 333 444 555
1 NameA  1   0   0   0   1
2 NameB  0   1   0   0   0
3 NameC  1   0   0   0   0
4 NameD  0   0   0   1   0

I have tried multiple forms of as.matrix and data.matrix but it doesn't give me the right format. Perhaps I'm doing them wrong. Can someone help me?

(As a bonus: If possible, I want to turn this two mode matrix into a one mode matrix next. I will be eternally grateful if someone knows how to do this too.)

Data in dput format

df1 <-
structure(list(Name = c("NameA", "NameB", "NameC", 
"NameD", "NameA"), id = c(111L, 222L, 111L, 444L, 555L)), 
class = "data.frame", row.names = c("1", "2", "3", "4", "5"))
Daniel
  • 1
  • 1

1 Answers1

-1

You can use pivot_wider() from tidyr

df%>%
        pivot_wider(names_from = id, values_from=id)%>%
        mutate(across(where(is.numeric), ~ifelse(is.na(.), 0, 1)))%>%
        select(where(is.numeric))%>%
        as.matrix

     111 222 444 555
[1,]   1   0   0   1
[2,]   0   1   0   0
[3,]   1   0   0   0
[4,]   0   0   1   0

If you are ok with a dataframe, you can omit the last two lines of code:

df%>%
        pivot_wider(names_from = id, values_from=id)%>%
        mutate(across(where(is.numeric), ~ifelse(is.na(.), 0, 1)))

# A tibble: 4 x 5
  name  `111` `222` `444` `555`
  <fct> <dbl> <dbl> <dbl> <dbl>
1 A         1     0     0     1
2 B         0     1     0     0
3 C         1     0     0     0
4 D         0     0     1     0
GuedesBF
  • 8,409
  • 5
  • 19
  • 37
  • Thank you for your help. When I use your method I get the following error: Error in View : 'names' attribute [1] must be the same length as the vector [0]. Do you know how to fix the names? – Daniel May 28 '21 at 14:53
  • i can not reproduce this issue with the data you provided – GuedesBF May 28 '21 at 15:35
  • maybe this can help: https://stackoverflow.com/questions/33152715/paste-colums-of-a-dataframe-and-then-view-them – GuedesBF May 28 '21 at 15:41