R transposing repeat records

Question

I have a data table that repeats records. I would like to transpose the table but into the unique record names.

Below is a sample of the Data table:

V1  V2  id
ClientID    29  1
CheckID 201 1
PaymentAmount   256 1
Gross   301 1
Net 256 1
Invested    130 1
Invested    53  1
Invested    118 1
ClientID    31  2
CheckID 222 2
PaymentAmount   41  2
Gross   46  2
Net 41  2
Invested    46  2
ClientID    43  3
CheckID 310 3
PaymentAmount   41  3
Gross   46  3
Net 41  3
Invested    46  3

You can see from the table above that the record in X1 called "Investment" can occur more than once for a single ClientID. I'd like to transpose the data so that it looks as such:

ClientID    CheckID PaymentAmount   Gross   Net Invested    ID
29  201 256 301 256 130 1
29  201 256 301 256 53  1
29  201 256 301 256 118 1
31  222 41  46  41  46  2
43  310 41  46  41  46  3
43  310 41  46  41  48  3

any support is greatly appreciated!

Answer 1

We can create a sequence column grouped by the "V1", "id" column using data.table, then convert from 'long' to 'wide' format with dcast and replace the NA with the non-NA preceding values using na.locf from zoo.

library(data.table)
library(zoo)
setDT(df1)[, N:= 1:.N , by =  .(V1, id)]
dcast(df1, id+N~V1, value.var="V2")[, lapply(.SD, na.locf),
                         by = id, .SDcols = CheckID:PaymentAmount]
#   id CheckID ClientID Gross Invested Net PaymentAmount
#1:  1     201       29   301      130 256           256
#2:  1     201       29   301       53 256           256
#3:  1     201       29   301      118 256           256
#4:  2     222       31    46       46  41            41
#5:  3     310       43    46       46  41            41

data

df1 <- structure(list(V1 = c("ClientID", "CheckID", "PaymentAmount", 
"Gross", "Net", "Invested", "Invested", "Invested", "ClientID", 
"CheckID", "PaymentAmount", "Gross", "Net", "Invested", "ClientID", 
"CheckID", "PaymentAmount", "Gross", "Net", "Invested"), V2 = c(29L, 
201L, 256L, 301L, 256L, 130L, 53L, 118L, 31L, 222L, 41L, 46L, 
41L, 46L, 43L, 310L, 41L, 46L, 41L, 46L), id = c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 
3L)), .Names = c("V1", "V2", "id"), class = "data.frame", 
row.names = c(NA, -20L))