CODEWITHSUNDEEP
r
Ysabelle Boo
Ysabelle Boo

Reputation: 29

R: How do transpose value into column but two columns' values?

I am trying to change this table to

ID       Disease1   disease2
1        CVD        HBP
2        CVD        NA
3        HBP        Asthma
4        NA         NA
5        Asthma     CVD
6        CVD        NA
7        HBP        NA

this table

ID       CVD   HBP    Asthma    Total
1        1     1      0          2
2        1     0      0          1
3        0     1      1          2
4        0     0      0          0
5        1     0      1          2
6        1     0      0          1
7        0     1      0          1

How should I code this? I knew how to transpose one column value section to binary but not as two (because survey allows to select more than 1 value, so the dataset has disease 1, 2, 3.... like this)

Upvotes: 0

Views: 53

Answers (2)

akrun
akrun

Reputation: 887851

We may do this in base R with table and addmargins

cbind(df1['ID'], as.data.frame.matrix(addmargins(table(rep(df1$ID,
        2), unlist(df1[-1])), 2)))
  ID Asthma CVD HBP Sum
1  1      0   1   1   2
2  2      0   1   0   1
3  3      1   0   1   2
4  4      0   0   0   0
5  5      1   1   0   2
6  6      0   1   0   1
7  7      0   0   1   1

data

df1 <- structure(list(ID = 1:7, Disease1 = c("CVD", "CVD", "HBP", NA, 
"Asthma", "CVD", "HBP"), disease2 = c("HBP", NA, "Asthma", NA, 
"CVD", NA, NA)), class = "data.frame", row.names = c(NA, -7L))

Upvotes: 1

Onyambu
Onyambu

Reputation: 79338

df %>%
  reshape2::recast(ID~value, id.var = 'ID', fun.aggregate = length) %>%
  select(-`NA`) %>%
  mutate(Total = rowSums(across(-ID)))

  ID Asthma CVD HBP Total
1  1      0   1   1     2
2  2      0   1   0     1
3  3      1   0   1     2
4  4      0   0   0     0
5  5      1   1   0     2
6  6      0   1   0     1
7  7      0   0   1     1

Upvotes: 2

Related Questions