Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make groups in a data.frame equal length?

Tags:

dataframe

r

I have this data.frame:

df <- data.frame(id=c('A','A','B','B','B','C'), amount=c(45,66,99,34,71,22))

id | amount 
-----------
A  |   45   
A  |   66   
B  |   99
B  |   34 
B  |   71
C  |   22

which I need to expand so that each by group in the data.frame is of equal length (filling it out with zeroes), like so:

id | amount 
-----------
A  |   45   
A  |   66  
A  |   0     <- added 
B  |   99
B  |   34 
B  |   71
C  |   22
C  |   0     <- added 
C  |   0     <- added 

What is the most efficient way of doing this?

NOTE

Benchmarking the some of the solutions provided with my actual 1 million row data.frame I got:

             plyr   | data.table  |  unstack
          -----------------------------------
Elapsed:   139.87s  |    0.09s    |   2.00s
like image 274
jenswirf Avatar asked Jan 31 '13 08:01

jenswirf


3 Answers

One way using data.table

df <- structure(list(V1 = structure(c(1L, 1L, 2L, 2L, 2L, 3L), 
          .Label = c("A  ", "B  ", "C  "), class = "factor"), 
          V2 = c(45, 66, 99, 34, 71, 22)), 
          .Names = c("V1", "V2"), 
          class = "data.frame", row.names = c(NA, -6L))

require(data.table)
dt <- data.table(df, key="V1")

# get maximum index
idx <- max(dt[, .N, by=V1]$N)

# get final result
dt[, list(V2 = c(V2, rep(0, idx-length(V2)))), by=V1]

#     V1 V2
# 1: A   45
# 2: A   66
# 3: A    0
# 4: B   99
# 5: B   34
# 6: B   71
# 7: C   22
# 8: C    0
# 9: C    0
like image 165
Arun Avatar answered Sep 23 '22 03:09

Arun


I'm sure there is a base R solution, but here is one that uses ddply in the plyr package

library(plyr)
##N: How many values should be in each group
N = 3
ddply(df, "id", summarize, 
      amount = c(amount, rep(0, N-length(amount))))

gives:

  id amount
1  A     45
2  A     66
3  A      0
4  B     99
5  B     34
6  B     71
7  C     22
8  C      0
9  C      0
like image 23
csgillespie Avatar answered Sep 21 '22 03:09

csgillespie


Here's another way in base R using unstack and stack.

# ensure character id col
df <- transform(df, id=as.character(id))
# break into a list by id
u <- unstack(df, amount ~ id)
# get max length
max.len <- max(sapply(u, length))
# pad the short ones with 0s
filled <- lapply(u, function(x) c(x, numeric(max.len - length(x))))
# recombine into data.frame
stack(filled)

#   values ind
# 1     45   A
# 2     66   A
# 3      0   A
# 4     99   B
# 5     34   B
# 6     71   B
# 7     22   C
# 8      0   C
# 9      0   C
like image 38
Matthew Plourde Avatar answered Sep 20 '22 03:09

Matthew Plourde