I have a dataframe that looks like this:
Year <- c("2004", "2004", "2004", "2005", "2005", "2005", "2006", "2006", "2006")
Province <- c("0101", "0102", "0103", "0101", "0102", "0103", "0101", "0102", "0103")
Price <- c("1,02", "1,50", "1,70", "1,25", "1,56", "1,78", "1,32", "1,65", "1,88")
Data <- data.frame(Year, Province, Price)
Year Province Price
2004 0101 1,02
2004 0102 1,50
2004 0103 1,70
2005 0101 1,25
2005 0102 1,56
2005 0103 1,78
2006 0101 1,32
2006 0102 1,65
2006 0103 1,88
I am now trying to create a variable that depicts the differences in prices to the previous period for each province. The prefered outcome would be:
Year Province Price Diff_Price
2004 0101 1,02 NA
2004 0102 1,50 NA
2004 0103 1,70 NA
2005 0101 1,25 0,23
2005 0102 1,56 0,06
2005 0103 1,78 0,08
2006 0101 1,32 0,07
2006 0102 1,65 0,09
2006 0103 1,88 0,1
I found a simular post froma a while ago, but this was done for only one "province" and maybe by now there are also easier solutions for this problem: Creating a delta column to plot time series differences in R
Another option would be to use dplyr::lag (and readr::parse_number for the conversion to a numeric).
library(dplyr, warn=FALSE)
library(readr)
Data |>
# Convert to proper numeric
mutate(
Price = readr::parse_number(Price, locale = readr::locale(decimal_mark = ","))
) |>
group_by(Province) |>
mutate(Diff_Price = Price - lag(Price, order_by = Year)) |>
ungroup()
#> # A tibble: 9 × 4
#> Year Province Price Diff_Price
#> <chr> <chr> <dbl> <dbl>
#> 1 2004 0101 1.02 NA
#> 2 2004 0102 1.5 NA
#> 3 2004 0103 1.7 NA
#> 4 2005 0101 1.25 0.23
#> 5 2005 0102 1.56 0.0600
#> 6 2005 0103 1.78 0.0800
#> 7 2006 0101 1.32 0.0700
#> 8 2006 0102 1.65 0.0900
#> 9 2006 0103 1.88 0.100