0

Without using a join or merge I would like to add a mean(metric) column to this table, which averages the metric by sector

  symbol       sector      date_bom     recommendation   metric
   A       Strip Center    20XX-08-01     BUY             0.01
   B       Office Center   20XX-09-01     BUY             0.02
   C       Strip Center    20XX-07-01     SELL           -0.01

I've tried a couple things in dplyr but it seems like I want/need a group-by within the summarise clause, and that is not allowed.

user2723494
  • 1,168
  • 2
  • 15
  • 26
  • 2
    Try `library(dplyr);df1 %>% group_by(sector) %>% mutate(Mean = mean(metric))` or with `base R`, `df1$Mean <- with(df1, ave(metric, sector))` – akrun Jun 06 '18 at 17:59
  • mutate for the win. Thanks. If you post as answer, I'll checkmark – user2723494 Jun 06 '18 at 18:10

1 Answers1

0

If we are going to create a column, use the mutate instead of summarise

library(dplyr)
df1 %>% 
   group_by(sector) %>%
   mutate(Mean = mean(metric))

Though, it is possible to create a list column in summarise and then unnest, but that is not needed here. It is useful in situations, where the output column length is not 1 or not the same the number of rows of each group. Besides, summarise will only get you the grouping column and the summarised column leaving behind all other columns

In base R, we use ave for this kind of operations

df1$Mean <- with(df1, ave(metric, sector))

Note that there is a FUN argument for ave, but by default it gets the mean. So, here it is not needed

akrun
  • 874,273
  • 37
  • 540
  • 662