CODEWITHSUNDEEP
rtidy
Banjo
Banjo

Reputation: 1251

split row values into columns

I have some data that looks like this:

samp
# A tibble: 5 x 2
     ID Source        
  <dbl> <chr>         
1 34221 75            
2 33861 75            
3 59741 126,123       
4 56561 111,105       
5 55836 36,34,34,36,22

Of any of the distinct values, I want to make a new column. If the value exists in a row I want to impute an "x" otherwise no value should be imputed.

Example (pseudo code) of the expected result:

ID      75  126 123 111 105 36 34 22         
1 34221 x            
2 33861 x            
3 59741     x   x       
4 56561             x   x    
5 55836                     x  x  x

I tried it by the separtate function of the tydr package. Like this for the start. 

into = unique(unlist(strsplit(samp$Source, ",")))

samp %>% separate(col = "Source", into = into, sep = ",")

However, this doesn´t work, because if there are more then one value in a row the values will not be assigned to the respective column (e.g. for the ID 59741 the value 126 is in column 75 and not in the column 126). 

 A tibble: 5 x 9
     ID `75`  `126` `123` `111` `105` `36`  `34`  `22` 
  <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 34221 75    NA    NA    NA    NA    NA    NA    NA   
2 33861 75    NA    NA    NA    NA    NA    NA    NA   
3 59741 126   123   NA    NA    NA    NA    NA    NA   
4 56561 111   105   NA    NA    NA    NA    NA    NA   
5 55836 36    34    34    36    22    NA    NA    NA

Here is a dput:

structure(list(ID = c(34221, 33861, 59741, 56561, 55836), Source = c("75", 
"75", "126,123", "111,105", "36,34,34,36,22")), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

Upvotes: 0

Views: 67

Answers (3)

A. Suliman
A. Suliman

Reputation: 13135

Another option is using tidyr::separate_rows

library(dplyr)
library(tidyr)
df %>% separate_rows(Source,sep=',') %>% distinct() %>% 
       mutate(dummy='X') %>% spread(Source,dummy)

     ID  105  111  123  126   22   34   36   75
1 33861 <NA> <NA> <NA> <NA> <NA> <NA> <NA>    X
2 34221 <NA> <NA> <NA> <NA> <NA> <NA> <NA>    X
3 55836 <NA> <NA> <NA> <NA>    X    X    X <NA>
4 56561    X    X <NA> <NA> <NA> <NA> <NA> <NA>
5 59741 <NA> <NA>    X    X <NA> <NA> <NA> <NA>

Upvotes: 2

arg0naut91
arg0naut91

Reputation: 14774

Could also do:

library(tidyverse)

df %>%
  mutate(Source = strsplit(Source, ","),
         dummy = "x") %>% 
  unnest() %>% distinct() %>%
  spread(Source, dummy)

Output:

     ID `105` `111` `123` `126` `22`  `34`  `36`  `75` 
  <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 33861 NA    NA    NA    NA    NA    NA    NA    x    
2 34221 NA    NA    NA    NA    NA    NA    NA    x    
3 55836 NA    NA    NA    NA    x     x     x     NA   
4 56561 x     x     NA    NA    NA    NA    NA    NA   
5 59741 NA    NA    x     x     NA    NA    NA    NA

Upvotes: 2

Sotos
Sotos

Reputation: 51592

The package splitstackshape is very handy for such operations, i.e.

library(splitstackshape)

cSplit_e(df, "Source", mode = "binary", type = "character", fill = 0, drop = TRUE)

which gives,

     ID Source_105 Source_111 Source_123 Source_126 Source_22 Source_34 Source_36 Source_75
1 34221          0          0          0          0         0         0         0         1
2 33861          0          0          0          0         0         0         0         1
3 59741          0          0          1          1         0         0         0         0
4 56561          1          1          0          0         0         0         0         0
5 55836          0          0          0          0         1         1         1         0

Upvotes: 2

Related Questions