How to create Matrix from a Dataframe?

user_11942282 0
Viewed 130

I have the following table and would like to have a new column in R for every unique ID

MeetingID <- c("01", "02","03","02","04","03") 
Attendee <- c("Alex","Bob","Tim","Xavier","Ana","Bob")
AttendedMonth <- c("Jan","Apr","Feb","Apri","Feb","Feb")

df <- data.frame(MeetingID, Attendee,AttendedMonth)
MeetingID Attendeee AttendedMonth
01 Alex Jan
02 Bob Apr
03 Tim Feb
02 Xavier Apr
04 Ana Feb
03 Bob Feb

I intend to create a dataframe similar to a matrix where for each MeetingID, i want to label (0) if the the attendee did not attend the meeting and (1) if the attendee attended the meeting

Attendee 01 02 03 04
Alex 1 0 0 0
Bob 0 1 1 0
Tim 0 0 1 0
Xavier 0 1 0 0
Ana 0 0 0 1
tidyr dplyr dataframe r
4 Answers

You could use tidyr::pivot_wider:

library(tidyr)
library(dplyr)

df %>% mutate(attended = 1) %>% 
  select(-AttendedMonth) %>%
  pivot_wider(names_from=MeetingID,values_from = attended,) %>%
  mutate(across(everything(),replace_na, replace = 0))

# A tibble: 5 x 5
  Attendee  `01`  `02`  `03`  `04`
  <chr>    <dbl> <dbl> <dbl> <dbl>
1 Alex         1     0     0     0
2 Bob          0     1     1     0
3 Tim          0     0     1     0
4 Xavier       0     1     0     0
5 Ana          0     0     0     1
user_135133280

I recommend janitor for these kinda situations always

df %>% janitor::tabyl(Attendee, MeetingID)

 Attendee 01 02 03 04
     Alex  1  0  0  0
      Ana  0  0  0  1
      Bob  0  1  1  0
      Tim  0  0  1  0
   Xavier  0  1  0  0

You can have nice row/col totals too

df %>% tabyl(Attendee, MeetingID) %>%
  adorn_totals(c("row", "col"))

 Attendee 01 02 03 04 Total
     Alex  1  0  0  0     1
      Ana  0  0  0  1     1
      Bob  0  1  1  0     2
      Tim  0  0  1  0     1
   Xavier  0  1  0  0     1
    Total  1  2  2  1     6
user_28848590

Base R table :

table(rev(df[-3]))

#       MeetingID
#Attendee 01 02 03 04
#  Alex    1  0  0  0
#  Ana     0  0  0  1
#  Bob     0  1  1  0
#  Tim     0  0  1  0
#  Xavier  0  1  0  0
user_39629140

We can use xtabs like below

> xtabs(~ Attendee + MeetingID, df)
        MeetingID
Attendee 01 02 03 04
  Alex    1  0  0  0
  Ana     0  0  0  1
  Bob     0  1  1  0
  Tim     0  0  1  0
  Xavier  0  1  0  0
user_121587570
Related