distributing data.frame data across multiple rows in r

Question

I'm using R 3.5.1

I think this is a simple issue, but I'm not super familiar with R.

I have a data.frame object that looks like this

COL1  COL2  COL3
A     blah  3
A     abc   4
A     def   42
B     xyz   10
B     aaa   3
C     pdq   19

I want to transform the data.table to look like this

COLA  COLACount  COLB  COLBCount  COLC COLCCount 
blah  3          xyz   10         pdq  19   
abc   4          aaa   3
def   42

I'm not sure where to begin with this (or what to call it). I have considered doing the following:

get all unique values in COL1
make a separate data.table for each unique value in COL1 using the contents of COL2 and COL3
cbind each data.table into a single "wide" table.

But I have a feeling that there might be an r package/method that simplifies this procedure.

Thank you for any suggestions.

dcast(dt, rowid(COL1) ~ COL1, value.var = c("COL2", "COL3")) Though I'm not convinced this a useful format for any reasonable analysis. Usually, one would want to do the other way around transformation. — David Arenburg
@DavidArenburg Thank you. You are correct. It is not a format for any reasonable analysis, but I have someone who wants to see the data presented in that format. — pdanese

Andre Elrico Andre Elrico · Accepted Answer · 2018-09-05T14:20:01

cbind.fill <- function(...){
    nm <- list(...)
    nm <- lapply(nm, as.matrix)
    n <- max(sapply(nm, nrow))
    do.call(cbind, lapply(nm, function (x)
        rbind(x, matrix(, n-nrow(x), ncol(x)))))
} #code from package rowr

do.call(cbind.fill, split(dt, dt$COL1))

distributing data.frame data across multiple rows in r

1 Answers