CODEWITHSUNDEEP
rstatistics
Carol.Kar
Carol.Kar

Reputation: 5345

Aggregate Function - Keep NAs in data.frame

I want to use the aggregation function of R to aggregate a Price on several fields. However, I also have NAs in my data, which I would like to keep.

Tried:

> dput(df)
structure(list(ID = c(1L, 2L, 3L, 4L, 4L, 1L, 2L, 3L, 4L, 1L, 
2L, 3L, 4L, 3L, 2L, 1L), REFERENCE = c("TEST1", "TEST2", "TEST3", 
"TEST4", "TEST1", "TEST2", "TEST3", "TEST4", "TEST1", "TEST2", 
"TEST3", "TEST4", "TEST1", "TEST2", "", "TEST2"), ISS = c(1234L, 
1234L, 1111L, 1111L, 1234L, 1111L, 1234L, 1111L, 1234L, NA, 1234L, 
1111L, 1234L, 1111L, 1234L, NA), Price = c(10L, NA, 20L, NA, 
10L, 12L, NA, 99L, 100L, NA, 100L, 12L, NA, 11L, 0L, 12L)), .Names = c("ID", 
"REFERENCE", "ISS", "Price"), row.names = c(NA, -16L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x0000000000100788>)
> 
> df <- aggregate(df$Price, by=list(ID=df$ID, REFERENCE=df$REFERENCE, ISS=df$ISS), FUN=sum)

Setting na.action = na.pass, gives me:7

Error in aggregate.data.frame(as.data.frame(x), ...) : 
  no rows to aggregate

As a result I would like to have:

enter image description here

Hence, I would like to keep my NA Data in my df.

Any recommendation how to implement that?

I appreciate your replies!

Upvotes: 0

Views: 725

Answers (1)

akrun
akrun

Reputation: 886948

Instead of using aggregate on a "data.table", we can use the data.table methods. We get the sum of Price (sum(Price, na.rm=TRUE)) after grouping by "ID/REFERENCE/ISS" (by=list(ID, REFERENCE, ISS)]. Order the output by "ID", "REFERENCE" (if needed) 

 library(data.table)
 df[, sum(Price, na.rm=TRUE), by = list(ID, REFERENCE, ISS)][
                                     order(ID, REFERENCE)]
 #   ID REFERENCE  ISS  V1
 #1:  1     TEST1 1234  10
 #2:  1     TEST2 1111  12
 #3:  1     TEST2   NA  12
 #4:  2           1234   0
 #5:  2     TEST2 1234   0
 #6:  2     TEST3 1234 100
 #7:  3     TEST2 1111  11
 #8:  3     TEST3 1111  20
 #9:  3     TEST4 1111 111
 #10:  4     TEST1 1234 110
 #11:  4     TEST4 1111   0

Upvotes: 2

Related Questions