CODEWITHSUNDEEP
rtidyrtidy
bodega18
bodega18

Reputation: 654

Gathering multiple columns

I am trying to gather multiple columns in R. An example is some recent fantasy football data:

homeId homePlayerName homePosition homeRosterPts awayId awayPlayerName awayPosition awayRosterPts
5      Nick Chubb           RB         23.3         1   Josh Jacobs         RB           8.9
4      Tyreek Hill          WR         24.6         10   Patrick Mahomes    QB           18.5
7      Davante Adams        WR         21.0         2   Michael Thomas      WR           12.4

I want there to just be one column each for Id, Player Name, Points, and the Positions columns so I can tidy the data and bind the rows together - instead of being duplicated for home and away. Do I need to use the gather function here?

Upvotes: 0

Views: 49

Answers (2)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193507

For fun/pain/education, here's how you can do it with reshape from base R:

reshape(setNames(df, gsub("(home|away)(.*)", "\\2.\\1", names(df))), 
        direction = "long", varying = 1:ncol(df))
#        time Id     PlayerName Position RosterPts id
# 1.home home  5      NickChubb       RB      23.3  1
# 2.home home  4     TyreekHill       WR      24.6  2
# 3.home home  7   DavanteAdams       WR      21.0  3
# 1.away away  1     JoshJacobs       RB       8.9  1
# 2.away away 10 PatrickMahomes       QB      18.5  2
# 3.away away  2  MichaelThomas       WR      12.4  3

You can also use melt from "data.table" like this:

library(data.table)
nam <- unique(sub("home|away", "", names(df)))
melt(as.data.table(df), measure = patterns(nam), value.name = nam)
#    variable Id     PlayerName Position RosterPts
# 1:        1  5      NickChubb       RB      23.3
# 2:        1  4     TyreekHill       WR      24.6
# 3:        1  7   DavanteAdams       WR      21.0
# 4:        2  1     JoshJacobs       RB       8.9
# 5:        2 10 PatrickMahomes       QB      18.5
# 6:        2  2  MichaelThomas       WR      12.4

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 388817

gather has been retired, use pivot_longer which makes this easy :

tidyr::pivot_longer(df, 
                    cols = everything(), 
                    names_to = c('game', '.value'),
                    names_pattern = '(home|away)(.*)')

#  game     Id PlayerName     Position RosterPts
#  <chr> <int> <chr>          <chr>        <dbl>
#1 home      5 NickChubb      RB            23.3
#2 away      1 JoshJacobs     RB             8.9
#3 home      4 TyreekHill     WR            24.6
#4 away     10 PatrickMahomes QB            18.5
#5 home      7 DavanteAdams   WR            21  
#6 away      2 MichaelThomas  WR            12.4

data

df <- structure(list(homeId = c(5L, 4L, 7L), homePlayerName = c("NickChubb", 
"TyreekHill", "DavanteAdams"), homePosition = c("RB", "WR", "WR"
), homeRosterPts = c(23.3, 24.6, 21), awayId = c(1L, 10L, 2L), 
    awayPlayerName = c("JoshJacobs", "PatrickMahomes", "MichaelThomas"
    ), awayPosition = c("RB", "QB", "WR"), awayRosterPts = c(8.9, 
    18.5, 12.4)), class = "data.frame", row.names = c(NA, -3L))

Upvotes: 1

Related Questions