CODEWITHSUNDEEP
rdplyrspread
Jake
Jake

Reputation: 474

Trying to make data wide 4 columns at once

I am trying to make my data wider than it already is. I tried using spread but I would like to spread 4 variables at one time. A sample dataset is: 

  df <- data.frame(Year <- c("2017","2018"),
                           ID <- c(1,1),
                           Score <- c("21","32"),
                           Score2 <- c("24","20"),
                           Score3 <- c("33", "26"),
                           Score4 <- c("25","32"))

     Year ID Score Score2 Score3 Score4
   1 2017  1    21     24     33     25
   2 2018  1    32     20     26     32

I would like to make it wide so that all scores from both years are on 1 row like this: 

  Year Score Score2 Score3 Score4 Year2 Score18 Score218 Score318 Score418
1 2017    21     24     33     25  2018      32       20       26       32

The "Year2" column is not totally necessary but I would like some way to decipher between 2017 and 2018.

Any help or guidance would be appreciated! Thanks!

Upvotes: 1

Views: 41

Answers (2)

Prem
Prem

Reputation: 11955

Another approach could be

library(tidyverse)
library(splitstackshape)

df %>%
  group_by(ID) %>%
  summarise_all(funs(toString)) %>%
  cSplit(names(.)[-1], ",")

Output is:

   ID Year_1 Year_2 Score_1 Score_2 Score2_1 Score2_2 Score3_1 Score3_2 Score4_1 Score4_2
1:  1   2017   2018      21      32       24       20       33       26       25       32

Sample data:

df <- data.frame(Year = c("2017","2018"),
                 ID = c(1,1),
                 Score = c("21","32"),
                 Score2 = c("24","20"),
                 Score3 = c("33", "26"),
                 Score4 = c("25","32"))

Upvotes: 1

akrun
akrun

Reputation: 887118

We can use dcast from data.table

library(data.table)
dcast(setDT(df), ID ~ rowid(ID), value.var = setdiff(names(df), 'ID'), 
          sep="")[, ID :=  NULL][]
#  Year1 Year2 Score1 Score2 Score21 Score22 Score31 Score32 Score41 Score42
#1:  2017  2018     21     32      24      20      33      26      25      32

Or with reshape from base R

reshape(transform(df, tvar = seq_len(nrow(df))), 
        idvar = 'ID', direction = 'wide', timevar = 'tvar')[-1]
#   Year.1 Score.1 Score2.1 Score3.1 Score4.1 Year.2 Score.2 Score2.2 Score3.2 Score4.2
#1   2017      21       24       33       25   2018      32       20       26       32

data

df <- data.frame(Year = c(2017, 2018),
                           ID = c(1,1),
                           Score = c(21,32),
                           Score2 = c(24,20),
                           Score3 =  c(33, 26),
                           Score4= c(25, 32))

Upvotes: 2

Related Questions