Subtract columns when column name is a year

user_16913681 0
Viewed 61

I'm certain the answer to this is simple, but I can't figure it out.

I have a pivot table where the column headings are years.


# A tibble: 3 x 5

country  2012  2013  2014  2015 
<chr>    <dbl> <dbl> <dbl> <dbl>
USA      45    23    12    42
Canada   67    98    14    25
Mexico   89    104   78    3

I want to create a new column that calculate the difference between two other columns. Rather than recognize the year as a column heading, however, R takes the difference between the two years.

Below is a sample of my code. If I put " " around the years, I get an error: "x non-numeric argument to binary operator". Without " ", R creates a new column with the value -3, simply subtracting the years.


df %>%
  pivot_wider(names_from = year, values_from = value) %>%
  mutate(diff = 2012 - 2015)

How do I re-write this to get the following table:


# A tibble: 3 x 6

country  2012  2013  2014  2015  diff
<chr>    <dbl> <dbl> <dbl> <dbl> <dbl>
USA      45    23    12    47    -2
Canada   67    98    14    25    42
Mexico   89    104   78    3     86
dplyr tidyverse r
1 Answers

You may try

df  %>%
  pivot_wider(names_from = year, values_from = value) %>%
  mutate(diff = .$'2012' - .$'2015')

with your data,

df <- read.table(text = "country  2012  2013  2014  2015 

USA      45    23    12    42
Canada   67    98    14    25
Mexico   89    104   78    3  
", header = T)
names(df) <- c("country",  2012,  2013,  2014,  2015 )

df  %>%
  mutate(diff = .$'2012' - .$'2015')

  country 2012 2013 2014 2015 diff
1     USA   45   23   12   42    3
2  Canada   67   98   14   25   42
3  Mexico   89  104   78    3   86
user_167291750
Related