CODEWITHSUNDEEP
rdplyr
user10072578
user10072578

Reputation:

Inserting duplicates and non duplicates in different columns in R

I have the following data and I hope you help me as I could not find my solution in stack overflow. 

ID	Code
1	A
1	A12
2	M
3	A
2	M12
4	A12
5	M12
6	M
7	A
6	M12

As you can see some IDs are duplicated and some not. I want to generate some columns to see each code in a specific column. The desirable outcome is :

ID	Code	A1	A121	M1	M121
1	A	A	N/A	N/A	N/A
1	A12	N/A	A12	N/A	N/A
2	M	N/A	N/A	M	N/A
3	A	A	N/A	N/A	N/A
2	M12	N/A	N/A	N/A	M12
4	A12	N/A	A12	N/A	N/A
5	M12	N/A	N/A	N/A	M12
6	M	N/A	N/A	M	N/A
7	A	A	N/A	N/A	N/A
6	M12	N/A	N/A	N/A	M12

I understand you want me to show my attempts. Unfortunately, I was unable to do it as a novice. Very much appreciated for your help.

Upvotes: 0

Views: 47

Answers (2)

FlorianBrezina
FlorianBrezina

Reputation: 103

The it should be just

library(tidyverse)

df <- tribble(~ID, ~Code,
              1, "A",
              1, "A12",
              2, "M",
              3, "A",
              2, "M12",
              4, "A12",
              5, "M12",
              6, "M",
              7, "A",
              6, "M12")

df %>%
  spread(key = Code, value = Code)

Result

# A tibble: 7 x 5
     ID A     A12   M     M12  
  <dbl> <chr> <chr> <chr> <chr>
1     1 A     A12   NA    NA   
2     2 NA    NA    M     M12  
3     3 A     NA    NA    NA   
4     4 NA    A12   NA    NA   
5     5 NA    NA    NA    M12  
6     6 NA    NA    M     M12  
7     7 A     NA    NA    NA

Many would prefer a 1/0 encoding of the columns, then the solution is

df %>%
 mutate(bin = 1) %>%
 spread(key = Code, value = bin, fill = 0)

# A tibble: 7 x 5
     ID     A   A12     M   M12
  <dbl> <dbl> <dbl> <dbl> <dbl>
1     1     1     1     0     0
2     2     0     0     1     1
3     3     1     0     0     0
4     4     0     1     0     0
5     5     0     0     0     1
6     6     0     0     1     1
7     7     1     0     0     0

Upvotes: 2

C. Braun
C. Braun

Reputation: 5191

Using dplyr and tidyr you can spread the Code column out like this:

df %>% 
    mutate(id_temp = row_number(), code_temp = Code) %>%
    spread(code_temp, code_temp) %>%
    select(-id_temp)

#    ID Code    A  A12    M  M12
# 1   1    A    A <NA> <NA> <NA>
# 2   1  A12 <NA>  A12 <NA> <NA>
# 3   2    M <NA> <NA>    M <NA>
# 4   2  M12 <NA> <NA> <NA>  M12
# 5   3    A    A <NA> <NA> <NA>
# 6   4  A12 <NA>  A12 <NA> <NA>
# 7   5  M12 <NA> <NA> <NA>  M12
# 8   6    M <NA> <NA>    M <NA>
# 9   6  M12 <NA> <NA> <NA>  M12
# 10  7    A    A <NA> <NA> <NA>

Upvotes: 1

Related Questions