Merge rows with same value in a column to single row in a dataframe

Question

I have the dataframe below

post<-c("BAL","DEN","ARI","ATL")
home<-c("DEN","DEN","ARI","ARI")
away<-c("BAL","BAL","ATL","ATL")
ID<-c("2015_01_BAL_DEN","2015_01_BAL_DEN","2016_01_ARI_ATL","2016_01_ARI_ATL")
NUM<-c(58,69,45,67)
PO<-c(55,65,46,65)
P1<-data.frame(post,home,away,ID,NUM,PO)


    post home away              ID NUM PO
1  BAL  DEN  BAL 2015_01_BAL_DEN  58 55
2  DEN  DEN  BAL 2015_01_BAL_DEN  69 65
3  ARI  ARI  ATL 2016_01_ARI_ATL  45 46
4  ATL  ARI  ATL 2016_01_ARI_ATL  67 65

and what I want find rows with same ID and convert them to one row while adding new columns for post ,NUM and PO:

    ID post home away NUM PO post2 NUM2 PO2
1 2015_01_BAL_DEN  BAL  DEN  BAL  58 55   DEN   69  65
2 2016_01_ARI_ATL  ARI  ARI  ATL  45 46   ATL   67  65

akrun · Answer 1 · 2020-07-26T21:00:34.633

We could create a sequence column and specify that as the names_from in pivot_wider

library(dplyr)
library(tidyr)
library(data.table)
P1 %>% 
    mutate(rn = rowid(ID)) %>%
    pivot_wider(names_from = rn, 
        values_from = c(post, home, away, NUM, PO), names_sep="")
# A tibble: 2 x 11
#  ID              post1 post2 home1 home2 away1 away2  NUM1  NUM2   PO1   PO2
#  <chr>           <chr> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
#1 2015_01_BAL_DEN BAL   DEN   DEN   DEN   BAL   BAL      58    69    55    65
#2 2016_01_ARI_ATL ARI   ATL   ARI   ARI   ATL   ATL      45    67    46    65

Or using dcast

library(data.table)
dcast(setDT(P1), ID ~ rowid(ID),
     value.var = c('post', 'home', 'away', 'NUM', 'PO'), sep="")

score 1 · Accepted Answer · answered Jul 26 '20 at 20:55

in base R you could do:

reshape(transform(P1, time = ave(NUM, ID, FUN=seq)), idvar = "ID", dir="wide", sep = "")

               ID post1 home1 away1 NUM1 PO1 post2 home2 away2 NUM2 PO2
1 2015_01_BAL_DEN   BAL   DEN   BAL   58  55   DEN   DEN   BAL   69  65

Merge rows with same value in a column to single row in a dataframe

2 Answers2