CODEWITHSUNDEEP
rtidyr
scyrt
scyrt

Reputation: 346

How to spread two column dataframe with creating a unique identifier?

Trying to spread two column data to a format where there will be some NA values.

dataframe:

df <- data.frame(Names = c("TXT","LSL","TXT","TXT","TXT","USL","LSL"), Values = c("apple",-2,"orange","banana","pear",10,-1),stringsAsFactors = F)

enter image description here

If a row includes TXT following rows that has LSL or USL will belong to that row.

For ex:

trying to create this:

enter image description here

I tried using spread with row numbers as unique identifier but that's not what I want:

df %>% group_by(Names) %>% mutate(row = row_number()) %>% spread(key = Names,value = Values)

I guess I need to create following full table with NAs then spread but couldn't figure out how.

enter image description here

Upvotes: 1

Views: 48

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388982

In data.table, we can use dcast : 

library(data.table)

dcast(setDT(df), cumsum(Names == 'TXT')~Names, value.var = 'Values')[, -1]

#    LSL    TXT  USL
#1:   -2  apple <NA>
#2: <NA> orange <NA>
#3: <NA> banana <NA>
#4:   -1   pear   10

Upvotes: 0

akrun
akrun

Reputation: 887118

We can expand the dataset with complete after creating a grouping index based on the occurence of 'TXT'

library(dplyr)
library(tidyr)
df %>% 
     group_by(grp = cumsum(Names == 'TXT')) %>%
     complete(Names = unique(.$Names)) %>%
     ungroup %>% 
     spread(Names, Values) %>%
     select(TXT, LSL, USL)
# A tibble: 4 x 3
#  TXT    LSL   USL  
#  <chr>  <chr> <chr>
#1 apple  -2    <NA> 
#2 orange <NA>  <NA> 
#3 banana <NA>  <NA> 
#4 pear   -1    10

Upvotes: 1

Related Questions