Transpose, spread or pivot a table with 0 or 1 depending on the value of a column

Kasitru 0
Viewed 60

i have a data table like this one:

a <- data.table::data.table(A = seq(1:5), B = c("V1","V2","V1","V2","V3"))

and i need a result with new columns V1, V2, V3 (there are some more possible values then 3) with 0 or 1 (true or false) depending on the value in B. So the result should look like this one:

   A  B V1 V2 V3
1: 1 V1  1  0  0
2: 2 V2  0  1  0
3: 3 V1  1  0  0
4: 4 V2  0  1  0
5: 5 V3  0  0  1

I have tried pivot_wider with no success. I have tried spread with diag function with no success.

Does anyone has a solution. Thank you very much.

pivot data.table r
4 Answers

We can use fastDummies::dummy_cols()

library(fastDummies)

dummy_cols(a, select_columns = 'B')
GuedesBF0

Using tidyverse:

a %>% mutate(n = 1, B1 = B) %>% 
                 pivot_wider(names_from = B1, values_from = n, values_fill = 0)
# A tibble: 5 × 5
      A B        V1    V2    V3
  <int> <chr> <dbl> <dbl> <dbl>
1     1 V1        1     0     0
2     2 V2        0     1     0
3     3 V1        1     0     0
4     4 V2        0     1     0
5     5 V3        0     0     1
Karthik S0

cbind the model.matrix.

cbind(a, model.matrix(~ 0 + B, a))
#    A  B BV1 BV2 BV3
# 1: 1 V1   1   0   0
# 2: 2 V2   0   1   0
# 3: 3 V1   1   0   0
# 4: 4 V2   0   1   0
# 5: 5 V3   0   0   1
jay.sf0

I guess dcast would be a good starter

> dcast(a, A + B ~ B, fun.aggregate = length)
   A  B V1 V2 V3
1: 1 V1  1  0  0
2: 2 V2  0  1  0
3: 3 V1  1  0  0
4: 4 V2  0  1  0
5: 5 V3  0  0  1

Or, we can try table + cbind like below

> cbind(a,as.data.frame.matrix(table(a)))
   A  B V1 V2 V3
1: 1 V1  1  0  0
2: 2 V2  0  1  0
3: 3 V1  1  0  0
4: 4 V2  0  1  0
5: 5 V3  0  0  1
ThomasIsCoding0
Related