2

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.

Kasitru
  • 45
  • 4

4 Answers4

3

We can use fastDummies::dummy_cols()

library(fastDummies)

dummy_cols(a, select_columns = 'B')
GuedesBF
  • 8,409
  • 5
  • 19
  • 37
2

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 S
  • 11,348
  • 2
  • 11
  • 25
  • Thanks a lot. At first this version has a top result. But with my real statistical data there is an error "Error in `pivot_wider_spec()`: ! Can't convert `fill` to ." So i tried the solution with fastDummies and it worked also for my real Data. Thanks a lot to you guys. – Kasitru Sep 22 '22 at 07:13
  • I usually use the argument `values_fn = ~ 1` in `pivot_wider` in order to avoid creating the temoprary `n` variable. – GuedesBF Sep 22 '22 at 21:45
2

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.sf
  • 60,139
  • 8
  • 53
  • 110
1

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
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81