CODEWITHSUNDEEP
rtidyr
jntrcs
jntrcs

Reputation: 607

Pivoting in R with multiple columns of different types

I have two data.frames and I would like to be able to use pivot_wider and pivot_longer to go back and forth between the two. I understand how pivoting works with a single column, but when we're trying to maintain the relationship of home team and home score while separating it from away team and away score I get confused. 

data.frame(GameID=1:2, Home_Team=c("Jazz", "Rockets"), Home_Score=c(129, 94), Away_Team=c("Wizards", "Warriors"), Away_Score=c(100, 98))

data.frame(GameID=c(1,2,1,2),Location=c("Home", "Home", "Away", "Away"), Team=c("Jazz", "Rockets", "Wizards", "Warriors"), Score=c(129,94,100,98))

Upvotes: 1

Views: 125

Answers (1)

akrun
akrun

Reputation: 886938

If we need to change from first dataset to second, use pivot_longer

library(dplyr)
library(tidyr)
library(stringr)
df1 %>%      
  pivot_longer(cols = -GameID, names_to = c("Location", ".value"), names_sep="_")
# A tibble: 4 x 4
#  GameID Location Team     Score
#   <int> <chr>    <fct>    <dbl>
#1      1 Home     Jazz       129
#2      1 Away     Wizards    100
#3      2 Home     Rockets     94
#4      2 Away     Warriors    98

From the second dataset, conversion can be done with pivot_wider

df2 %>%
   pivot_wider(names_from = Location, values_from = c(Team, Score)) %>%
   rename_at(-1, ~ str_replace(., "(.*)_(.*)", "\\2_\\1"))
# A tibble: 2 x 5
#  GameID Home_Team Away_Team Home_Score Away_Score
#   <dbl> <fct>     <fct>          <dbl>      <dbl>
#1      1 Jazz      Wizards          129        100
#2      2 Rockets   Warriors          94         98

data

df1 <- data.frame(GameID=1:2, Home_Team=c("Jazz", "Rockets"), 
   Home_Score=c(129, 94), Away_Team=c("Wizards", "Warriors"), Away_Score=c(100, 98))



df2 <- data.frame(GameID=c(1,2,1,2),Location=c("Home", "Home", "Away", 
   "Away"), Team=c("Jazz", "Rockets", "Wizards", "Warriors"), Score=c(129,94,100,98))

Upvotes: 1

Related Questions