How to capture percent change

[ernijs12]

I really need your help, any support will be much appreciated.

This is the example data:

tibble::tribble(
    ~Item, ~Amount,        ~Date,
    "saw",     10L, "01/01/2020",
    "saw",     20L, "04/01/2020",
    "saw",      5L, "03/06/2020",
    "saw",     30L, "02/07/2020",
  "nails",    300L, "02/02/2020",
  "nails",    200L, "04/03/2020",
  "nails",    250L, "05/03/2020",
  "nails",    400L, "10/05/2020"
  )

I am trying to create a new column next to date that captures the percent change in amount for each item from one date to the next.

The result would look like:

tibble::tribble(
    ~Item, ~Amount,        ~Date, ~`%.change`,
    "saw",     10L, "01/01/2020",        "0%",
    "saw",     20L, "04/01/2020",      "100%",
    "saw",      5L, "03/06/2020",      "-75%",
    "saw",     30L, "02/07/2020",      "500%",
  "nails",    300L, "02/02/2020",        "0%",
  "nails",    200L, "04/03/2020",      "-33%",
  "nails",    250L, "05/03/2020",       "25%",
  "nails",    400L, "10/05/2020",       "60%"
  )

The formula for percent change: (new observation- original observation)/original observation * 100

And for the first date for each item, when there still hasn't been any change value of 0 or empty cell.

Again any help will be much appreciated, and huge thanks in advance!!

[arthur.t]

Try something like this.

library(tidyverse)
  
df <- tibble::tribble(
  ~Item, ~Amount,        ~Date,
  "saw",     10L, "01/01/2020",
  "saw",     20L, "04/01/2020",
  "saw",      5L, "03/06/2020",
  "saw",     30L, "02/07/2020",
  "nails",    300L, "02/02/2020",
  "nails",    200L, "04/03/2020",
  "nails",    250L, "05/03/2020",
  "nails",    400L, "10/05/2020"
)

df %>%
  relocate(Amount, .after = Date) %>%
  arrange(Item, Date) %>%
  group_by(Item) %>%
  mutate(
    difference = c(NA, diff(Amount)),
    percent_change = difference / lag(Amount, 1) * 100
  ) %>%
  ungroup()
#> # A tibble: 8 x 5
#>   Item  Date       Amount difference percent_change
#>   <chr> <chr>       <int>      <int>          <dbl>
#> 1 nails 02/02/2020    300         NA           NA  
#> 2 nails 04/03/2020    200       -100          -33.3
#> 3 nails 05/03/2020    250         50           25  
#> 4 nails 10/05/2020    400        150           60  
#> 5 saw   01/01/2020     10         NA           NA  
#> 6 saw   02/07/2020     30         20          200  
#> 7 saw   03/06/2020      5        -25          -83.3
#> 8 saw   04/01/2020     20         15          300

^{Created on 2022-05-24 by the reprex package (v2.0.1)}

[ernijs12]

Many thanks to you!!!