CODEWITHSUNDEEP
rdataframereshape
Cybernetic
Cybernetic

Reputation: 13334

convert data frame so that each unique transaction listed becomes a single row

I have a data frame like this:

        trans_id   product_id
1          1          456
2          4          223
3          1          778
4          1          774
5          5          999
6          4          123

I need to convert it so that all trans_id are listed as a single row like this:

trans_id      V1       V2     V3
1            456      778   774
4            223      123
5            999

Upvotes: 3

Views: 94

Answers (3)

dimitris_ps
dimitris_ps

Reputation: 5951

With tidyr

library(tidyr)
t(df %>% spread(trans_id, product_id))

+1 @Ananda Mahto answer for tidy and dplyr

Upvotes: 1

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193527

You should add a secondary ID column. That's easy with getanID from my "splitstackshape" package. Since "splitstackshape" also loads "data.table", it's easy to then convert to a wide format using dcast.data.table:

library(splitstackshape)
dcast.data.table(
  getanID(mydf, "trans_id"), 
  trans_id ~ .id, value.var = "product_id")
#    trans_id   1   2   3
# 1:        1 456 778 774
# 2:        4 223 123  NA
# 3:        5 999  NA  NA

The equivalent "dplyr"+"tidyr" approach would be something like:

library(dplyr)
library(tidyr)

mydf %>%
  group_by(trans_id) %>%
  mutate(id = sequence(n())) %>%
  spread(id, product_id)

Upvotes: 5

akrun
akrun

Reputation: 887168

A base R option would be

reshape(transform(df, N= ave(trans_id, trans_id, FUN=seq_along)), 
               idvar='trans_id', timevar='N', direction='wide')
#   trans_id product_id.1 product_id.2 product_id.3
#1        1          456          778          774
#2        4          223          123           NA
#5        5          999           NA           NA

Upvotes: 3

Related Questions