Clash Royale CLAN TAG

Transposing specific duplicated column data into rows given ID #

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.

2 Answers
2

Using data.table:

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")


A tidyverse approach

tidyverse

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


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)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.