Calculate Sales and Stock in R

Question

Im working with a Sales/Stocks database. Original tablet had a problem, only show the last STOCK status by Date, SKY, COMPANY and STORE, like this example.

Date	SKU	COMPANY	STORE	SALES	STOCK
2022-01-01	ABC	WALMART	DOWNTOWN	1	0
2022-01-02	ABC	WALMART	DOWNTOWN	1	0
2022-01-03	ABC	WALMART	DOWNTOWN	1	0
2022-01-04	ABC	WALMART	DOWNTOWN	1	0
2022-01-05	ABC	WALMART	DOWNTOWN	1	0
2022-01-06	ABC	WALMART	DOWNTOWN	0	5

I need create Stock's history in the original table, starting from the last date to the first existing date, like this table:

Date	SKU	COMPANY	STORE	SALES	STOCK
2022-01-01	ABC	WALMART	DOWNTOWN	1	10
2022-01-02	ABC	WALMART	DOWNTOWN	1	9
2022-01-03	ABC	WALMART	DOWNTOWN	1	8
2022-01-04	ABC	WALMART	DOWNTOWN	1	7
2022-01-05	ABC	WALMART	DOWNTOWN	1	6
2022-01-06	ABC	WALMART	DOWNTOWN	0	5

Basically I must add to the stocks of the previous day, the amount of sales of the day and I dont know how to do this with dplyr::group_by().

Thanks in advance.

Example file here

Answer 1

There are probably more elegant ways to do this, but to do this with the original dataset you provided (have), you can get the desired dataset you provided (want) using:

have <- read.table(text = "Date SKU COMPANY STORE   SALES   STOCK
                 2022-01-01 ABC WALMART DOWNTOWN    1   0
                 2022-01-02 ABC WALMART DOWNTOWN    1   0
                 2022-01-03 ABC WALMART DOWNTOWN    1   0
                 2022-01-04 ABC WALMART DOWNTOWN    1   0
                 2022-01-05 ABC WALMART DOWNTOWN    1   0
                 2022-01-06 ABC WALMART DOWNTOWN    0   5",
                 header = TRUE)

want <- have %>% mutate(new_value = rev(max(STOCK)+cumsum(rev(SALES))))

Output:

#   Date       SKU COMPANY    STORE SALES STOCK new_value
# 1 2022-01-01 ABC WALMART DOWNTOWN     1     0        10
# 2 2022-01-02 ABC WALMART DOWNTOWN     1     0         9
# 3 2022-01-03 ABC WALMART DOWNTOWN     1     0         8
# 4 2022-01-04 ABC WALMART DOWNTOWN     1     0         7
# 5 2022-01-05 ABC WALMART DOWNTOWN     1     0         6
# 6 2022-01-06 ABC WALMART DOWNTOWN     0     5         5

Note I didnt rewrite over the original column, but instead created a new one just for purposes of this example

You can then use group_by() to do this in datasets among groups.

Please note that many users don't click on external links or download external datasets (including me); if this doesn't solve your issue please update the data provided in your question to be more representative of the real data and I'll be happy to edit my answer!