1

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.

pdanese
  • 2,187
  • 4
  • 15
  • 21
  • 3
    `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 Sep 05 '18 at 13:19
  • @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 Sep 05 '18 at 13:22

1 Answers1

0
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))
Andre Elrico
  • 10,956
  • 6
  • 50
  • 69