CODEWITHSUNDEEP
rdataframetidyverse
eok
eok

Reputation: 151

Spread Many Columns

If I have a dataframe such as 

    Inj Name Injection ID   Time
116   1    B         6130  3.007
117   1  CBZ         6130  7.397
118   1    A         6130  7.486
119   1    C         6130  7.761
120   1    D         6130 10.175
121   1    F         6130 10.511

structure(list(Inj = c(1, 1, 1, 1, 1, 1), Name = c("B", "CBZ", 
"A", "C", "D", "F"), `Injection ID` = c(6130, 6130, 6130, 6130, 
6130, 6130), Time = c(3.007, 7.397, 7.486, 7.761, 10.175, 10.511
)), .Names = c("Inj", "Name", "Injection ID", "Time"), class = "data.frame", row.names = 116:121)

I would (since I have many different Inj values, for this value to turn it into a single row such as

  Inj Injection.ID Name.1 Name.2 Name.3 Name.4 Name.5 Name.6 Time.1 Time.2 Time.3 Time.4 Time.5 Time.6
1   1         6130      B    CBZ      A      C      D      F  3.007  7.397  7.486  7.761 10.175 10.511

Unfortunately really not sure how to do this using spread... or anything else for that matter.

Upvotes: 0

Views: 44

Answers (2)

s_baldur
s_baldur

Reputation: 33753

With melt()/dcast() one could do something like (using pipes from magrittr (that dplyr loads anyway)):

library(data.table)

setDT(df) %>% 
  .[, lapply(.SD, as.character)] %>% # being explicit and avoid warning
  melt(id.vars = c("Inj", "Injection ID"), variable.factor = FALSE) %>%
  dcast(Inj + `Injection ID` ~ paste0(variable, ".", 1:nrow(df)))

   Inj Injection ID Name.1 Name.2 Name.3 Name.4 Name.5 Name.6 Time.1 Time.2 Time.3
1:   1         6130      B    CBZ      A      C      D      F  3.007  7.397  7.486
   Time.4 Time.5 Time.6
1:  7.761 10.175 10.511

Upvotes: 1

A. Suliman
A. Suliman

Reputation: 13135

library(dplyr)
df %>% group_by(`Injection ID`) %>% mutate(row = row_number()) %>% 
       gather(key, value, -row,  -Inj, -`Injection ID`) %>% 
       mutate(id= paste0(key,".",row)) %>% 
       select(-row, -key) %>% spread(id, value)

# A tibble: 1 x 14
# Groups:   Injection ID [1]
    Inj `Injection ID` Name.1 Name.2 Name.3 Name.4 Name.5 Name.6 Time.1 Time.2 Time.3 Time.4 Time.5 Time.6
    <dbl>          <dbl> <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
1     1           6130 B      CBZ    A      C      D      F      3.007  7.397  7.486  7.761  10.175 10.511

Upvotes: 2

Related Questions