Find final price in a dataframe of transactions with reference to a monthly refunds dataframe

user_13510834 0
Viewed 33

I have a dataframe of transactions which contains the product purchased, the date, and the gross price.

     Product       Date Price
1        Tea 2020-12-17   1.0
2  Ice-cream 2020-12-17   2.0
3        Tea 2021-01-19   1.0
4      Bread 2021-02-02   0.5
5      Bread 2021-02-02   0.5
6        Tea 2021-03-03   1.0
7        Tea 2021-03-05   1.0
8  Ice-cream 2021-03-05   2.0
9  Ice-cream 2021-03-09   2.0
10     Bread 2021-03-10   0.5

transactions <- data.frame(
    Product = c("Tea", "Ice-cream", "Tea", "Bread", "Bread", "Tea", "Tea", "Ice-cream", "Ice-cream", "Bread"), 
    Date=as.Date(c("17-12-2020", "17-12-2020", "19-01-2021", "02-02-2021", "02-02-2021", "03-03-2021", "05-03-2021", "05-03-2021", "09-03-2021", "10-03-2021"), format = "%d-%m-%Y"), 
    Price = c(1.00, 2.00, 1.00, 0.50, 0.50, 1.00, 1.00, 2.00, 2.00, 0.50)
)

However, under the arrangement some the cost of some products is refunded on a monthly basis, thus there is another dataframe called "refunds"

        Month Product Amount
1 December 20     Tea   0.50
2  January 21     Tea   0.50
3 February 21   Bread   0.50
4    March 21     Tea   1.00
5    March 21   Bread   0.25

refund <- data.frame(
  Month = c("December 20", "January 21", "February 21", "March 21", "March 21"),
  Product = c("Tea", "Tea", "Bread", "Tea", "Bread"),
  Amount = c(0.50, 0.50, 0.50, 1.00, 0.25)
)

I need to create an additional column in my "transactions" dataframe which reflects the net price, i.e. the price minus (the total refund for that month / number of that product purchased that month).

For simplicity I assumed that the refund arrangment is half the price of bread and tea, with no refund for ice-cream, so the answer should look like this.

     Product       Date Price Final_Price
1        Tea 2020-12-17   1.0        0.50
2  Ice-cream 2020-12-17   2.0        2.00
3        Tea 2021-01-19   1.0        0.50
4      Bread 2021-02-02   0.5        0.25
5      Bread 2021-02-02   0.5        0.25
6        Tea 2021-03-03   1.0        0.50
7        Tea 2021-03-05   1.0        0.50
8  Ice-cream 2021-03-05   2.0        2.00
9  Ice-cream 2021-03-09   2.0        2.00
10     Bread 2021-03-10   0.5        0.25

netprice_df <- data.frame(
  Product = c("Tea", "Ice-cream", "Tea", "Bread", "Bread", "Tea", "Tea", "Ice-cream", "Ice-cream", "Bread"), 
  Date=as.Date(c("17-12-2020", "17-12-2020", "19-01-2021", "02-02-2021", "02-02-2021", "03-03-2021", "05-03-2021", "05-03-2021", "09-03-2021", "10-03-2021"), format = "%d-%m-%Y"), 
  Price = c(1.00, 2.00, 1.00, 0.50, 0.50, 1.00, 1.00, 2.00, 2.00, 0.50)
  Final_Price = c(0.50, 2.00, 0.50, 0.25, 0.25, 0.50, 0.50, 2.00, 2.00, 0.25)
)

The real data is more complex than this.

Managing the date element is where I am having trouble...any help of tip would be much appreciated.

Note* I don't have easy access to the CRAN library, however I have Tidyverse downloaded.

dplyr tidyverse dataframe r
1 Answers

We could convert the 'Date' column to the format in 'refund' data, do a left_join and get the 'Final_price'

library(dplyr)
library(lubridate)
transactions%>%
    mutate(Month = format(ymd(Date), '%B %y')) %>%
    left_join(refund) %>%        
    group_by(Product, Month) %>%,
    mutate(Amount = -Amount/n()) %>%
    ungroup %>%
    mutate(Final_price = rowSums(select(., Price, Amount), na.rm = TRUE)) %>%
    select(names(transactions), Final_price)

-output

# A tibble: 10 x 4
#   Product   Date       Price Final_price
#   <chr>     <date>     <dbl>       <dbl>
# 1 Tea       2020-12-17   1          0.5 
# 2 Ice-cream 2020-12-17   2          2   
# 3 Tea       2021-01-19   1          0.5 
# 4 Bread     2021-02-02   0.5        0.25
# 5 Bread     2021-02-02   0.5        0.25
# 6 Tea       2021-03-03   1          0.5 
# 7 Tea       2021-03-05   1          0.5 
# 8 Ice-cream 2021-03-05   2          2   
# 9 Ice-cream 2021-03-09   2          2   
#10 Bread     2021-03-10   0.5        0.25
user_37322710
Related