Calculate "group characteristics" without ddply and merge

Question

I wonder whether there is a more straighforward way to calculate a certain type of variables than the approach i normally take....

The example below probably explains it best. I have a dataframe with 2 columns (fruit and whether the fruit is rotten or not). I would like to, for each row, add e.g. the percentage of fruit of the same category that is rotten. For example, there are 4 entries for apples, 2 of them are rotten, so each row for apple should read 0.5. The target values (purely as illustration) are included in the "desired outcome" column.

I have previously approached this problem by * using the "ddply" command on the fruit variable (with sum/lenght as function), creating a new 3*2 dataframe * use the "merge" command to link these values back into the old dataframe.

This feels like a roundabout way, and I was wondering whether there are better/faster way of doing this! ideallly a generic approach, that is easily adjusted if one instead of the percentage needs to determine whether e.g. all fruits are rotten, any fruits are rotten, etc. etc. etc....

Many thanks in advance,

W

    Fruit Rotten Desired_Outcome_PercRotten
1   Apple      1                        0.5
2   Apple      1                        0.5
3   Apple      0                        0.5
4   Apple      0                        0.5
5    Pear      1                       0.75
6    Pear      1                       0.75
7    Pear      1                       0.75
8    Pear      0                       0.75
9  Cherry      0                          0
10 Cherry      0                          0
11 Cherry      0                          0

#create example datagram; desired outcome columns are purely inserted as illustrative of target outcomes
Fruit=c(rep("Apple",4),rep("Pear",4),rep("Cherry",3))
Rotten=c(1,1,0,0,1,1,1,0,0,0,0)
Desired_Outcome_PercRotten=c(0.5,0.5,0.5,0.5,0.75,0.75,0.75,0.75,0,0,0)
df=as.data.frame(cbind(Fruit,Rotten,Desired_Outcome_PercRotten))        
df

Related discussion on the first part of your question: http://stackoverflow.com/q/11562656/636656 . Answers below are nicer because they combine the split-apply-combine operation with the merging in a single step. — Ari B. Friedman, Mar 18 '13 at 00:59
user1885116, use `df <- data.frame(Fruit, Rotten, Desired_Outcome_PercRotten)` to create a `data.frame` from scratch instead of `as.data.frame` with `cbind`. It gets the column `Rotten` as factor, which is undesirable. — Arun, Mar 18 '13 at 13:06

Arun · Accepted Answer · 2013-03-18T14:35:11.410

11

You can do this with just ddply and mutate:

# changed summarise to transform on joran's suggestion
# changed transform to mutate on mnel's suggestion :)
ddply(df, .(Fruit), mutate, Perc = sum(Rotten)/length(Rotten))

#     Fruit Rotten Perc
# 1   Apple      1 0.50
# 2   Apple      1 0.50
# 3   Apple      0 0.50
# 4   Apple      0 0.50
# 5  Cherry      0 0.00
# 6  Cherry      0 0.00
# 7  Cherry      0 0.00
# 8    Pear      1 0.75
# 9    Pear      1 0.75
# 10   Pear      1 0.75
# 11   Pear      0 0.75

edited Mar 18 '13 at 14:35

answered Mar 17 '13 at 23:14

Arun

116,683
26
284
387

4

I would also suggest `mutate` (the `plyr` implementation of `transform` which allows you to refer to created columns eg `ddply(df ,.(Fruit), mutate, percR = sum(Rotten) / length(Rotten), pp = Rotten *percR)` compared to `ddply(dd ,.(Fruit), transform, percR = sum(Rotten) / length(Rotten), pp = Rotten *percR)` – mnel Mar 18 '13 at 00:45

score 10 · Answer 2 · edited Mar 17 '13 at 23:47

data.table is super fast as it updates by reference. What about using it?

library(data.table)

dt=data.table(Fruit,Rotten,Desired_Outcome_PercRotten)

dt[,test:=sum(Rotten)/.N,by="Fruit"]
#dt
#     Fruit Rotten Desired_Outcome_PercRotten test
# 1:  Apple      1                       0.50 0.50
# 2:  Apple      1                       0.50 0.50
# 3:  Apple      0                       0.50 0.50
# 4:  Apple      0                       0.50 0.50
# 5:   Pear      1                       0.75 0.75
# 6:   Pear      1                       0.75 0.75
# 7:   Pear      1                       0.75 0.75
# 8:   Pear      0                       0.75 0.75
# 9: Cherry      0                       0.00 0.00
#10: Cherry      0                       0.00 0.00
#11: Cherry      0                       0.00 0.00

score 5 · Answer 3 · edited Mar 18 '13 at 05:14

One solution in base R is to use ave.

within(df, {
  ## Because of how you've created your data.frame
  ##   Rotten is actually a factor. So, we need to
  ##   convert it to numeric before we can use mean
  Rotten <- as.numeric(as.character(Rotten))
  NewCol <- ave(Rotten, Fruit)
})
    Fruit Rotten Desired_Outcome_PercRotten NewCol
1   Apple      1                        0.5   0.50
2   Apple      1                        0.5   0.50
3   Apple      0                        0.5   0.50
4   Apple      0                        0.5   0.50
5    Pear      1                       0.75   0.75
6    Pear      1                       0.75   0.75
7    Pear      1                       0.75   0.75
8    Pear      0                       0.75   0.75
9  Cherry      0                          0   0.00
10 Cherry      0                          0   0.00

or shorter:

transform(df, desired = ave(Rotten == 1, Fruit))

The default function applied with ave is mean, hence I have not included it here. However, you could specify a different function by appending FUN = some-function-here if you wanted to do something different.

score 2 · Answer 4 · answered Mar 18 '13 at 13:24

As ave is already out, let me add one solution using my base R function of choice: aggregate.

You can get the desired data simply with:

aggregate(as.numeric(as.character(Rotten)) ~ Fruit, df, mean)

However, you will need to still merge it afterwards (or in one piece):

merge(df, aggregate(as.numeric(as.character(Rotten)) ~ Fruit, df, mean))

score 0 · Answer 5 · answered Nov 30 '22 at 07:08

Using dplyr -

For dplyr version 1.1.0 and above -

library(dplyr)

df %>% mutate(Result = sum(Rotten)/n(), .by = Fruit)

#    Fruit Rotten Desired_Outcome_PercRotten Result
#1   Apple      1                       0.50   0.50
#2   Apple      1                       0.50   0.50
#3   Apple      0                       0.50   0.50
#4   Apple      0                       0.50   0.50
#5    Pear      1                       0.75   0.75
#6    Pear      1                       0.75   0.75
#7    Pear      1                       0.75   0.75
#8    Pear      0                       0.75   0.75
#9  Cherry      0                       0.00   0.00
#10 Cherry      0                       0.00   0.00
#11 Cherry      0                       0.00   0.00

Using .by argument provides temporary grouping and avoids the use of group_by and ungroup statements.

For dplyr version < 1.1.0

df %>%
  group_by(Fruit) %>%
  mutate(Result = sum(Rotten)/n()) %>%
  ungroup

data

df <-  data.frame(Fruit,Rotten,Desired_Outcome_PercRotten)

Calculate "group characteristics" without ddply and merge

5 Answers5

Linked