CODEWITHSUNDEEP
rtidyr
fleetmack
fleetmack

Reputation: 378

R dynamic columns names based on another column

I have a table as such:

types <- c("ENR","ENR","ENR","ENR","ENR","ENR")
records <- c(1,1,1,1,2,2)
occur <- c(1,2,3,4,1,2)
myval <- c("ABC|123","DEF|456","GHI|789","JKL|123","MNO|456","PQR|789")

mydf <- data.frame(types, records, occur, myval)


type   record   occur    myval
ENR    1        1        ABC|123
ENR    1        2        DEF|456
ENR    1        3        GHI|789
ENR    1        4        JKL|123
ENR    2        1        MNO|456
ENR    2        2        PQR|789

I am parsing out the myval column so that the delimited fields get their own columns, here's what I've used so far

library(tidyr)
mydf <- mydf %>% separate(myval, c("letters","numbers"),"\\|")

This essentially works, it creates this:

  types records occur letters numbers
1   ENR       1     1     ABC     123
2   ENR       1     2     DEF     456
3   ENR       1     3     GHI     789
4   ENR       1     4     JKL     123
5   ENR       2     1     MNO     456
6   ENR       2     2     PQR     789

.... However, I'd like the column names to be dynamic based on the occur#, so I'd ideally like this:

 types records occur letters1 numbers1  letters2  numbers2  letters3 numbers3 letters4 numbers4
 ENR         1     1      ABC      123
 ENR         1     2                         DEF       456
 ENR         1     3                                             GHI      789
 ENR         1     4                                                              JKL      123
 ENR         2     1      MNO      456
 ENR         2     2                         DEF       456

any idea how to accomplish this? I'm thinking if I can just dynamically name the columns that'd work perhaps?

Upvotes: 2

Views: 423

Answers (2)

akrun
akrun

Reputation: 887118

We can use dcast from data.table which can take multiple value.var columns

library(data.table)
dcast(setDT(mydf), types + records + occur ~ occur, value.var = c("letters", "numbers"), fill="")
#   types records occur letters_1 letters_2 letters_3 letters_4 numbers_1 numbers_2 numbers_3 numbers_4
#1:   ENR       1     1       ABC                                     123                              
#2:   ENR       1     2                 DEF                                     456                    
#3:   ENR       1     3                           GHI                                     789          
#4:   ENR       1     4                                     JKL                                     123
#5:   ENR       2     1       MNO                                     456                              
#6:   ENR       2     2                 PQR                                     789

Upvotes: 1

Tom&#225;s Barcellos
Tom&#225;s Barcellos

Reputation: 824

You can use tidyr::spread()

mydf %>% dplyr::mutate(letters_ = occur, numbers_ = occur) %>%
  spread(letters_, letters, fill = "", sep = "") %>%
  spread(numbers_, numbers, fill = "", sep = "")

In order to keep the orignal occur variable I tripled it and then, using spread() function, pivoted the values of letters and numbers according to the occur copies values.

Note that using the sep argument pastes the key and value in the new variable names. The fill argument is just used to get the desired output.

  types records occur letters_1 letters_2 letters_3 letters_4 numbers_1 numbers_2 numbers_3 numbers_4
1   ENR       1     1       ABC                                     123                              
2   ENR       1     2                 DEF                                     456                    
3   ENR       1     3                           GHI                                     789          
4   ENR       1     4                                     JKL                                     123
5   ENR       2     1       MNO                                     456                              
6   ENR       2     2                 PQR                                     789

Upvotes: 1

Related Questions