create a column as a function of columns with number as names dplyr

Question

I would like to create a column in a data frame based on two other columns. The column names are years and I wanted to provide it as a variables.

For example, if I have a data frame data1 containing several years of data with each column name being the year (I know this might not be a proper name), then:

year = 2022

  data1 %>%
  select(sprintf("%d", seq(
  from = (year - 4),
  to = year,
  by = 1
))) %>%
  mutate(percent = year/(year-1) - 1)

where the (year/(year-1) - 1 would be the percentual change from 2021 to 2022, so the columns 2021 and 2022 would be used. Obviously this code does not work.

Thank you very much for your help.

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. — MrFlick, Jul 26 '22 at 15:29

score 0 · Answer 1 · answered Jul 26 '22 at 15:23

The column names are strings, thus we may need to convert to string and use .data with [[ for extracting the column

 data1 %>%
  select(sprintf("%d", seq(
  from = (year - 4),
  to = year,
  by = 1
))) %>%
    mutate(percent = .data[[as.character(year)]]/.data[[as.character(year -1)]] - 1)

create a column as a function of columns with number as names dplyr

1 Answers1