I'm trying to automate a bunch of annual tables in my workplace that require the last column to be "Change" and shows the previous year taken away from this year.
I'd like to choose the last column and penultimate column dynamically so that I can build it into a function and use it everywhere reliably.
The closest I've come to it is using last_col():
library(tidyverse)
lakers |>
mutate(year = ymd(date) |> year()) |>
count(year,type) |>
pivot_wider(names_from = year, values_from = n) |>
mutate(change = pick(last_col()) - pick(last_col(offset= 1)))
what I get:
# A tibble: 74 × 4
#type `2008` `2009` change$`2009`
#<chr> <int> <int> <int>
# 1 "" 3852 6530 2678
#2 "3pt" 1114 1940 826
#3 "alley oop dunk" 46 51 5
#4 "alley oop layup" 20 40 20
#5 "away from play" 1 2 1
#6 "def" 1734 3034 1300
#7 "defense 3 second" 12 26 14
#8 "defensive goaltending" 13 28 15
#9 "delay of game" 7 13 6
#10 "double personal" 1 1 0
But that still leaves the name as change$[last_col_name] rather than just change. The column needs to be named col for some later styling I am doing in the flextable package.
What I want is:
#type `2008` `2009` Change
#<chr> <int> <int>
# 1 "" 3852 6530 2678
#2 "3pt" 1114 1940 826
#3 "alley oop dunk" 46 51 5
#4 "alley oop layup" 20 40 20
#5 "away from play" 1 2 2
#6 "def" 1734 3034 1300
#7 "defense 3 second" 12 26 14
#8 "defensive goaltending" 13 28 15
#9 "delay of game" 7 13 6
#10 "double personal" 1 1 0
I understand this might be possible with multiple assignments in magrittr but I would like to keep this to the native pipe rather than magrittr because I don't want to add the added complexity.
Use any of these as the last line:
# 1 - unlist
mutate(Change = unlist(pick(last_col()) - pick(last_col(offset= 1))))
# 2 - pull
mutate(Change = pull(pick(last_col()) - pick(last_col(offset= 1)), 1))
# 3 - pull alternative
mutate(Change = pull(pick(everything()), -1) - pull(pick(everything()), -2))
# 4 - across
mutate(across(last_col(), .names = "Change") - pick(last_col(offset = 1)))