1

I have a data frame which has unique id's and categorical variables. I need to collapse all unique ids into a single row and all applicable categorical variables into distinct vectors so that I end with a matrix to be used for some regression analysis. ex:

id    cat
1     a
2     b
1     b
3     c
4     a
2     a
4     c
3     c

output:
id   cat.a   cat.b   cat.c
1    1       1       0
2    1       1       0
3    0       0       2
4    1       0       1

I've looked into the build.x function in the useful package but can't get past the issue of collapsing into a single id

Connor M
  • 182
  • 2
  • 12

2 Answers2

2

This just looks like reshaping data

library(reshape2)
dcast(df, id ~ cat)

# Using cat as value column: use value.var to override.
# Aggregation function missing: defaulting to length
#   id a b c
# 1  1 1 1 0
# 2  2 1 1 0
# 3  3 0 0 2
# 4  4 1 0 1

Though this is probably overkill for such a simple problem. As @Seth pointed out in comments, you can just use table.

with(df, table(id, cat))
#    cat
# id  a b c
#   1 1 1 0
#   2 1 1 0
#   3 0 0 2
#   4 1 0 1

(Using this data:)

df = structure(list(id = c(1L, 2L, 1L, 3L, 4L, 2L, 4L, 3L), cat = structure(c(1L, 
2L, 2L, 3L, 1L, 1L, 3L, 3L), .Label = c("a", "b", "c"), class = "factor")), .Names = c("id", 
"cat"), class = "data.frame", row.names = c(NA, -8L))
Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
0

I think this accomplishes what you're looking for without using any required libraries -- though it does use two nested loops so it might be slow. 

## setting up the data you gave as an example in your question
dat=matrix(c(1,2,1,3,4,2,4,3,'a','b','b','c','a','a','c','c'),ncol=2)
data=data.frame(dat)

## determine the categories as defined by your data 
cats <- levels(data$X2)

## create a blank matrix
out=matrix(0,nrow=length(levels(data$X1)),ncol=length(levels(data$X2)))

## what is the lowest value of your first column
i=min(as.numeric(data$X1))

## j will serve as a counter for the rows in the out matrix
j=1
while(i<=max(as.numeric(data$X1)))
    {
        ## find the unique values associated with the first 'i' 
        idi <- which(as.numeric(data$X1)==i)
        ## set up a counter that corresponds to the columns of your out matrix
        k=1
        while(k<= length(cats)) {
            ## determine the values associated with the particular category 
            out[j,k] <- length(which(data[idi,2]==cats[k]))
            k=k+1
        }
        i=i+1
        j=j+1
    }
Alex Witsil
  • 855
  • 8
  • 22