Add Every Other Row to new Column - R

user_12855673 0
Viewed 89

I am looking for an easy way to add every other row to a new column in R. I have NCAA basketball teams that are playing each other on separate, consecutive rows. Example below: St. Joes is playing La Salle, and Connecticut is playing Seton Hall, etc. I want each "game" to be on the same line. I have messed around with lead/lag to solve this but that has left me with errors either with the first or last row, depending which one I use. Here is an example of my data:

# current data

Team       spread   price
St. Joes     -3     -105
La Salle      3     -115
Connecticut  -1.5   -105
Seton Hall    1.5   -115
Minnesota     5.5   -110
Penn State   -5.5   -110


# desired output below

Team1       spread1  price1  Team2        spread2  price2
St. Joes    -3       -105    La Salle     3        -115
Connecticut -1.5     -105    Seton Hall   1.5      -115
Minnesota    5.5     -110    Penn State  -5.5      -110
gather dplyr data-manipulation r
3 Answers

Create a group of every two rows and assign row number as new column. Cast the data to wide format using pivot_wider.

library(dplyr)
library(tidyr)

df %>%
  group_by(grp = ceiling(row_number()/2)) %>%
  mutate(row  =row_number()) %>%
  pivot_wider(names_from = row, values_from = Team:price) %>%
  ungroup %>%
  select(-grp)

#  Team_1      Team_2    spread_1 spread_2 price_1 price_2
#  <chr>       <chr>        <dbl>    <dbl>   <int>   <int>
#1 St.Joes     LaSalle       -3        3      -105    -115
#2 Connecticut SetonHall     -1.5      1.5    -105    -115
#3 Minnesota   PennState      5.5     -5.5    -110    -110

data

df <- structure(list(Team = c("St.Joes", "LaSalle", "Connecticut", 
"SetonHall", "Minnesota", "PennState"), spread = c(-3, 3, -1.5, 
1.5, 5.5, -5.5), price = c(-105L, -115L, -105L, -115L, -110L, 
-110L)), class = "data.frame", row.names = c(NA, -6L))
user_39629140

Using dcast from data.table

library(data.table)
dcast(setDT(df)[, grp := gl(.N, 2, .N)], grp ~ rowid(grp),
      value.var = setdiff(names(df), 'grp'))[, grp := NULL][]
#        Team_1    Team_2 spread_1 spread_2 price_1 price_2
#1:     St.Joes   LaSalle     -3.0      3.0    -105    -115
#2: Connecticut SetonHall     -1.5      1.5    -105    -115
#3:   Minnesota PennState      5.5     -5.5    -110    -110

data

df <- structure(list(Team = c("St.Joes", "LaSalle", "Connecticut", 
"SetonHall", "Minnesota", "PennState"), spread = c(-3, 3, -1.5, 
1.5, 5.5, -5.5), price = c(-105L, -115L, -105L, -115L, -110L, 
-110L)), class = "data.frame", row.names = c(NA, -6L))
user_37322710

A base R option using reshape

reshape(
  cbind(df, p = rep_len(1:2, nrow(df)), q = ceiling(seq(nrow(df)) / 2)),
  direction = "wide",
  idvar = "q",
  timevar = "p"
)

gives

  q      Team.1 spread.1 price.1    Team.2 spread.2 price.2
1 1     St.Joes     -3.0    -105   LaSalle      3.0    -115
3 2 Connecticut     -1.5    -105 SetonHall      1.5    -115
5 3   Minnesota      5.5    -110 PennState     -5.5    -110
user_121587570
Related