2

I want to build upon mutate_each / summarise_each in dplyr: how do I select certain columns and give new names to mutated columns? thread. It talks about applying mutate to multiple columns. However, I understand that we can use functions such as sum etc. but I am not sure how I can apply mathematical operations such as addition, multiplication, division and subtraction.

Here are my data:

dput(DF)
structure(list(FY = c(2015, 2016, 2017, 2030, 2015, 2016, 2017, 
2030, 2015, 2016, 2017, 2030, 2015, 2016, 2017, 2030, 2015, 2030
), Value = c(5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 
19, 20, NA, NA)), .Names = c("FY", "Value"), row.names = c(NA, 
18L), class = "data.frame")

Here's my working code to show you what I want:

DF<-DF %>% 
  dplyr::group_by(FY) %>%
  dplyr::summarise(Numbers = sum(Value,na.rm = TRUE)) %>%
  spread(FY,Numbers)

DF$`2016`<-DF$`2016` + DF$`2030`/3
DF$`2017`<-DF$`2017` + DF$`2030`/3
DF$`2015`<-DF$`2015` + DF$`2030`/3
DF$`2030`<-NULL

DF <- DF %>%
gather(FY,Values,`2015`:`2017`)

My objective is to use mutate_each() to automate the following lines of code and reduce repetition. I am unsure how I can use mutate to calculate 1/3rd of 2030 column and then add it back to 2016

   DF$`2016`<-DF$`2016` + DF$`2030`/3
    DF$`2017`<-DF$`2017` + DF$`2030`/3
    DF$`2015`<-DF$`2015` + DF$`2030`/3

What can I do to minimize the repetition?

Expected Output after applying above operation:

dput(DF)
structure(list(FY = c("2015", "2016", "2017"), Values = c(62.6666666666667, 
66.6666666666667, 70.6666666666667)), row.names = c(NA, -3L), .Names = c("FY", 
"Values"), class = c("tbl_df", "tbl", "data.frame"))
Community
  • 1
  • 1
watchtower
  • 4,140
  • 14
  • 50
  • 92
  • 1
    mutate_each creates column, but here your expected output is summarised – akrun Feb 03 '17 at 05:40
  • @akrun--Please correct me if I am wrong. Isn't `mutate_each` used for applying a function to each column in the dataset? Hence, assuming that there are three columns in my dataset (above) --`2015, 2016 and 2017`, can't we use `mutate_each` on each column to perform mathematical operation? I'm curious. Thanks in advance for all your help... – watchtower Feb 03 '17 at 06:12

2 Answers2

2

With dplyr we can group_by FY. Get the sum of each group and add 1/3 rd part of FY 2030 to all the sum.

library(dplyr)
DF %>%
   group_by(FY) %>%
   summarise(Sum = sum(Value, na.rm = TRUE)) %>%
   mutate(NewValue = Sum + Sum[FY == '2030']/3) %>%
   filter(FY != 2030)

#    FY   Sum  NewValue
#  <dbl> <dbl>    <dbl>
#1  2015    44 62.66667
#2  2016    48 66.66667
#3  2017    52 70.66667
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
1

We can use data.table

library(data.table)
setDT(DF)[FY %in% 2015:2017, .(NewValue = sum(Value, na.rm = TRUE) + 
                  sum(DF[FY==2030]$Value, na.rm=TRUE)/3), by = FY]
#     FY NewValue
#1: 2015 62.66667
#2: 2016 66.66667
#3: 2017 70.66667
akrun
  • 874,273
  • 37
  • 540
  • 662