Styjun

Can a network vulnerability be exploited locally?

What to do about my 1-month-old boy peeing through diapers?

How did medieval manors handle population growth? Were there room for more fields to be ploughed?

What does GDPR mean to myself regarding my own data?

Normalized Malbolge to Malbolge translator

The meaning of asynchronous vs synchronous

Was the six engine Boeing-747 ever seriously considered by Boeing?

Why can't you say don't instead of won't?

Coupling two 15 Amp circuit breaker for 20 Amp

Why is there no Disney logo in MCU movies?

What's the point of fighting monsters in Zelda BotW?

Why does AM radio react to IR remote?

Drawing probabilities on a simplex in TikZ

Why does glibc's strlen need to be so complicated to run quickly?

Is it unusual for a math department not to have a mail/web server?

How to handle inventory and story of a player leaving

What should be done with the carbon when using magic to get oxygen from carbon dioxide?

Why does `buck` mean `step-down`?

Get contents before a colon

Can I lend a small amount of my own money to a bank at the federal funds rate?

Heat output from a 200W electric radiator?

Is the internet in Madagascar faster than in UK?

Are spot colors limited and why CMYK mix is not treated same as spot color mix?

Why is 3/4 a simple meter while 6/8 is a compound meter?

Conditional Mean Similar to AVERAGEIFS in Excel

Summarize with conditions in dplyrCalculate group mean while excluding current observation using dplyrGraphing percent of whole based on multiple criteriaHow to plot weighted means by group?R - Using data.table to efficiently test rolling conditions across multiple rows and columnsCustomizing points in RConditional filtering of data.frame with preceeding and tailing NA observationsLooping over multiple columns in a dataframe in RR coding: How to keep records with only 4 complete quarters of data and how to take a conditional sum with multiple conditionsR coding: How to take a conditional sum/mean with multiple conditions in a dataframeComparing Strings for Similarity - Based on Word ContentsHow to group or subset a data frame by two conditions in R

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I would like to write code that functions similar to that of an AVERAGEIFS function in Excel. Essentially, I would like to take the average of a variable given certain conditions are met. I have not been successful in finding a similar problem online given my situation calls for a unique condition that excludes certain instances.

What I am trying to accomplish is calculating the mean of total revenue (TOTALREV) for each vessel ID (VESSEL_ID) in all survey years (SURVEY_YEAR)--Excluding the current survey year. I have tried creating an additional column called YEAR to create a conditional "not equal" as well as what my code currently depicts.

CPTWG <- CEEDCdatEXP[,c("VESSEL_ID","SURVEY_YEAR","TOTALREV")] %>%
 group_by(VESSEL_ID) %>%
 summarize(AVERAGEREV = mean(TOTALREV[SURVEY_YEAR != SURVEY_YEAR]))

This code should create my ideal outcome:

VESSEL_ID <- c(1,2,3,1,2,3,1,2,3)
SURVEY_YEAR <- c(2009,2009,2009,2010,2010,2010,2011,2011,2011)
TOTALREV <- c(2718,9939,4014,7019,2016,2025,3218,7727,7252)
AVERAGEREV <- c(5118.5,4871.5,4638.5,2968,8833,5633,4868.5,5977.5,3019.5)
mydata <- data.frame(VESSEL_ID,SURVEY_YEAR,TOTALREV,AVERAGEREV)

r

share|improve this question

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Extremely slick answer to this here. For your purposes...mydata <- group_by(VESSEL_ID) %>% mutate(AVERAGEREV = (sum(TOTALREV) - TOTALREV) / (n() - 1))
  
  – Nick Criswell
  Mar 27 at 21:04
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @NickCriswell--This is great! Thank you for digging this up as I was unable to do... In addition, I thought this would take me home, but to make things slightly more complex, I have one more column flagging each observation as an outlier or not. Any suggestions on how I might alter the mutate to incorporate only those data where outlier == 0 ?
  
  – Gary Eaton
  Mar 27 at 22:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I was going to say that you could add some kind of dummy column that would take on NA when your outliers happen. Then you could change your sum to work on that column with an na.rm = TRUE argument. A couple of other options (including one from the author of dplyr) are linked here. You would probably have to tweak the n() - 1 part, too, since your would likely have a lower denominator in your average.
  
  – Nick Criswell
  Mar 27 at 23:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @NickCriswell!
  
  – Gary Eaton
  Mar 27 at 23:22
  

  
  
  
  

add a comment | 

2

I would like to write code that functions similar to that of an AVERAGEIFS function in Excel. Essentially, I would like to take the average of a variable given certain conditions are met. I have not been successful in finding a similar problem online given my situation calls for a unique condition that excludes certain instances.

What I am trying to accomplish is calculating the mean of total revenue (TOTALREV) for each vessel ID (VESSEL_ID) in all survey years (SURVEY_YEAR)--Excluding the current survey year. I have tried creating an additional column called YEAR to create a conditional "not equal" as well as what my code currently depicts.

CPTWG <- CEEDCdatEXP[,c("VESSEL_ID","SURVEY_YEAR","TOTALREV")] %>%
 group_by(VESSEL_ID) %>%
 summarize(AVERAGEREV = mean(TOTALREV[SURVEY_YEAR != SURVEY_YEAR]))

This code should create my ideal outcome:

VESSEL_ID <- c(1,2,3,1,2,3,1,2,3)
SURVEY_YEAR <- c(2009,2009,2009,2010,2010,2010,2011,2011,2011)
TOTALREV <- c(2718,9939,4014,7019,2016,2025,3218,7727,7252)
AVERAGEREV <- c(5118.5,4871.5,4638.5,2968,8833,5633,4868.5,5977.5,3019.5)
mydata <- data.frame(VESSEL_ID,SURVEY_YEAR,TOTALREV,AVERAGEREV)

r

share|improve this question

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Extremely slick answer to this here. For your purposes...mydata <- group_by(VESSEL_ID) %>% mutate(AVERAGEREV = (sum(TOTALREV) - TOTALREV) / (n() - 1))
  
  – Nick Criswell
  Mar 27 at 21:04
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @NickCriswell--This is great! Thank you for digging this up as I was unable to do... In addition, I thought this would take me home, but to make things slightly more complex, I have one more column flagging each observation as an outlier or not. Any suggestions on how I might alter the mutate to incorporate only those data where outlier == 0 ?
  
  – Gary Eaton
  Mar 27 at 22:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I was going to say that you could add some kind of dummy column that would take on NA when your outliers happen. Then you could change your sum to work on that column with an na.rm = TRUE argument. A couple of other options (including one from the author of dplyr) are linked here. You would probably have to tweak the n() - 1 part, too, since your would likely have a lower denominator in your average.
  
  – Nick Criswell
  Mar 27 at 23:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks @NickCriswell!
  
  – Gary Eaton
  Mar 27 at 23:22
  

  
  
  
  

add a comment | 

I would like to write code that functions similar to that of an AVERAGEIFS function in Excel. Essentially, I would like to take the average of a variable given certain conditions are met. I have not been successful in finding a similar problem online given my situation calls for a unique condition that excludes certain instances.

What I am trying to accomplish is calculating the mean of total revenue (TOTALREV) for each vessel ID (VESSEL_ID) in all survey years (SURVEY_YEAR)--Excluding the current survey year. I have tried creating an additional column called YEAR to create a conditional "not equal" as well as what my code currently depicts.

CPTWG <- CEEDCdatEXP[,c("VESSEL_ID","SURVEY_YEAR","TOTALREV")] %>%
 group_by(VESSEL_ID) %>%
 summarize(AVERAGEREV = mean(TOTALREV[SURVEY_YEAR != SURVEY_YEAR]))

This code should create my ideal outcome:

VESSEL_ID <- c(1,2,3,1,2,3,1,2,3)
SURVEY_YEAR <- c(2009,2009,2009,2010,2010,2010,2011,2011,2011)
TOTALREV <- c(2718,9939,4014,7019,2016,2025,3218,7727,7252)
AVERAGEREV <- c(5118.5,4871.5,4638.5,2968,8833,5633,4868.5,5977.5,3019.5)
mydata <- data.frame(VESSEL_ID,SURVEY_YEAR,TOTALREV,AVERAGEREV)

r

share|improve this question

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge

CPTWG <- CEEDCdatEXP[,c("VESSEL_ID","SURVEY_YEAR","TOTALREV")] %>%
 group_by(VESSEL_ID) %>%
 summarize(AVERAGEREV = mean(TOTALREV[SURVEY_YEAR != SURVEY_YEAR]))

VESSEL_ID <- c(1,2,3,1,2,3,1,2,3)
SURVEY_YEAR <- c(2009,2009,2009,2010,2010,2010,2011,2011,2011)
TOTALREV <- c(2718,9939,4014,7019,2016,2025,3218,7727,7252)
AVERAGEREV <- c(5118.5,4871.5,4638.5,2968,8833,5633,4868.5,5977.5,3019.5)
mydata <- data.frame(VESSEL_ID,SURVEY_YEAR,TOTALREV,AVERAGEREV)

share|improve this question

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge

share|improve this question

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge

asked Mar 27 at 20:39

Gary EatonGary Eaton

1111 bronze badge