Transposing specific duplicated column data into rows given ID #

Question

I have a data frame that consists of ID #'s in the first column and multiple transactions for that given ID. For example:

ID  Transaction
1111   $13
1111   $55
1111   $4
1112   $27
1112   $40
1113   $12
1114   $100
1114   $60
1114   $55

What I am trying to do is to have only one line of the Customer ID and then creating multiple transactions for each instance. For example:

ID  Transaction1  Transaction2  Transaction3
1111         $13           $55            $4
1112         $27           $40
1113         $12
1114        $100           $60           $55

Any help would be appreciated. I've been trying to use for loops and what not but I keep getting lost in my work and have been looking for an easier way to do this.

score 3 · Accepted Answer · answered Jul 27 '18 at 13:15

Using data.table:

library(data.table)
dcast(data, ID ~ paste0("Transaction", rowid(ID)), value.var = "Transaction")


     ID Transaction1 Transaction2 Transaction3
1: 1111          $13          $55           $4
2: 1112          $27          $40         <NA>
3: 1113          $12         <NA>         <NA>
4: 1114         $100          $60          $55

Where

data <- fread("ID  Transaction
1111   $13
1111   $55
1111   $4
1112   $27
1112   $40
1113   $12
1114   $100
1114   $60
1114   $55")

score 3 · Answer 2 · answered Jul 27 '18 at 13:18

A tidyverse approach

library(tidyverse)
df %>%
    group_by(ID) %>%
    mutate(n = paste0("Transaction", 1:n())) %>%
    spread(n, Transaction)
#    ID Transaction1 Transaction2 Transaction3
# <int> <fct>        <fct>        <fct>
#1  1111 $13          $55          $4
#2  1112 $27          $40          NA
#3  1113 $12          NA           NA
#4  1114 $100         $60          $55

Sample data

df <- read.table(text =
    "ID  Transaction
1111   $13
1111   $55
1111   $4
1112   $27
1112   $40
1113   $12
1114   $100
1114   $60
1114   $55", header = T)

score 0 · Answer 3 · answered Jul 27 '18 at 13:21

library(plyr)
library(reshape2)
x <- read.table(header = TRUE, text = "
           ID  Transaction
           1111   $13
           1111   $55
           1111   $4
           1112   $27
           1112   $40
           1113   $12
           1114   $100
           1114   $60
           1114   $55
           ")

x<- ddply(x, "ID", transform, 
      castTransaction = paste0("Transaction", seq(length(Transaction))))


dcast(ID ~ castTransaction, value.var = "Transaction", data = x)

Transposing specific duplicated column data into rows given ID #

3 Answers3

Sample data