Add row to specific columns with calculations

Question

Code for df:

df <- data.table(" " = c("Actual"), 
                 "2011" = c(3),
                 "2012" = c(4),
                 "2013" = c(2),
                 "2014" = c(5),
                 "2015" = c(10),
                 "2016" = c(3),
                 "2017" = c(12)
                  )

which looks like this:

             2011    2012    2013    2014    2015    2016    2017
 1  Actual     3       4        2      5       10       3      12

I'd like to create a new row (row 2) under columns 2015, 2016 & 2017 by taking the average of 2011 - 2014 so it looks like this

             2011    2012    2013    2014     2015             2016               2017
 1  Actual     3       4        2      5       10                3                 12
 2  Average    0       0        0      0     10 - (3+4+2+5/5)  3 - (3+4+2+5/5)    12 - (3+4+2+5/5)

so after calculations, and taking the sums of row 1 and 2 it will look like this

             2011    2012    2013    2014     2015      2016     2017
 1  Actual     3       4        2      5       10        3         12
 2  Average    0       0        0      0       7.2      0.2        9.2
 3   Sum       3       4        2      5       17.2      3.2       21.2

To start, I attempted to take the first average of 2011 - 2014

rowMeans(subset(df, select = c("2011", "2012", "2013", "2014")), na.rm = TRUE)

this works, but then I attempted to bind it for the specific rows as:

    df <- bind_rows(df[, 5:7], 
    rowMeans(subset(df, select = c("2011", "2012", "2013", "2014")), na.rm = TRUE)))

However this did not work. Once this code would have worked I planned to subtract the actual number from this average and then take the sum of rows 1 and 2.

Any help would be much appreciated! Thank you so much in advance.

Answer 1

I had some trouble understanding, but I think this might be what you want. If not, I still recommend using this style of data analysis. This uses a really popular library called tidyverse. It has a lot of really nice functions in it. It also has %>% which is an operator that takes whatever comes before it and inputs that value as the first argument to the following function.

# a really nice library for writing cleaner data analysis in R
library(tidyverse)

# Make the data into a long form data frame, so it's easier to work with
df = df[,2:ncol(df)] %>%
  gather("Year", "Actual") %>%
  as.data.frame()

#   Year Actual
# 1 2011      3
# 2 2012      4
# 3 2013      2
# 4 2014      5
# 5 2015     10
# 6 2016      3
# 7 2017     12

df %>%
  # make the average column
  mutate(AverageUpTo2014 = mean(Actual[Year <= 2014])) %>%
  # make the difference column
  mutate(DifferenceFromAverage = Actual - AverageUpTo2014) %>%
  # and you could change the earlier values to zeros if you want
  mutate(DifferenceFromAverage = ifelse(Year <= 2014, 0,
                                        DifferenceFromAverage))

#   Year Actual AverageUpTo2014 DifferenceFromAverage
# 1 2011      3             3.5                     0
# 2 2012      4             3.5                     0
# 3 2013      2             3.5                     0
# 4 2014      5             3.5                     0
# 5 2015     10             3.5                   6.5
# 6 2016      3             3.5                  -0.5
# 7 2017     12             3.5                   8.5