I am fairly new to programming. I am working on my portfolio, and am looking at a dataset regarding the price of food from distribution centers to a grocery store. What I am looking at is a set of data with the price, item, and date of transaction. What I am looking for is to find the rate of change from the distribution center to the store, and when it happened.

Note: the price of the item changes from the distribution center.

Here is an example of what I am looking at:

As you can see, the price for the same quantity for the same product changes randomly over time. What I would like to analyse is:

1. The rate of change per item
2. When the change occured

This is the ideal outcome:

#Continuing the table below. These columns would go to the right of the columns above. #Unfortunetly, StackOverflow was not able to create a table with everything together. #total_change is for the entire period

As you can see, some items can have more price changes per month than others depending on the item. Some items have drastic changes, some have no changes at all.

Presuming there are over 14,000 unique items what would you recommend to gather the data an place them in a table as seen in the "Ideal outcome" section?

I am still new to programming, please don't be too harsh!

Thanks!

CodePudding user response：

Something like this?

library(tidyverse)

df %>%
  mutate(Date = as.Date(Date, format = "%d.%m.%Y"),
         Price = parse_number(Price)) %>%
  group_by(Item) %>%
  arrange(Date) %>%
  mutate(appearance = row_number(),
         change = Price - lag(Price)) %>%
  ungroup() %>%
  pivot_wider(names_from = appearance, 
              values_from = c(Date, Price, change),
              names_vary = "slowest")

Result

# A tibble: 4 × 11
  Item     Kg Date_1     Price_1 change_1 Date_2     Price_2 change_2 Date_3     Price_3 change_3
  <chr> <int> <date>       <dbl>    <dbl> <date>       <dbl>    <dbl> <date>       <dbl>    <dbl>
1 Apple     1 2022-02-01       1       NA 2022-02-16       2        1 2022-02-25     0.5     -1.5
2 Meat      1 2022-02-02       4       NA 2022-02-15       5        1 2022-02-15     3       -2  
3 Fish      1 2022-02-03       3       NA 2022-02-20       3        0 NA            NA        0  
4 Bread     1 2022-02-03       1       NA NA              NA        0 NA            NA        0  

Source data

df <- data.frame(
  stringsAsFactors = FALSE,
              Date = c("01.02.2022","02.02.2022",
                       "03.02.2022","03.02.2022","15.02.2022","15.02.2022",
                       "16.02.2022","20.02.2022","25.02.2022"),
              Item = c("Apple","Meat","Fish",
                       "Bread","Meat","Meat","Apple","Fish","Apple"),
             Price = c("$1.00","$4.00","$3.00",
                       "$1.00","$5.00","$3.00","$2.00","$3.00","$0.50"),
                Kg = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L)
)