mardi 31 juillet 2018

V-lookup style referencing in R without merging

I have two tables. One with information on commissions payable for selling items on different websites. The other holding items which I would like to create a sale price for.

Please see both tables:

Channel <- c("DIRECT", "EBAY", "AMAZON")
Commission_percentage <- c(.1, .5, .4)
Min_commission <- c(0, 0, 2)
Processing <- c(0.05, 0.05, 0.03)

Commission_table <- as.data.frame(cbind(Channel, Commission_percentage, 
Min_commission, Processing))

The commissions table above shows commission percentages for each website, but shows that AMAZON will charge £2 minimum for each sale. It also shows another % charge for payment processing. The table below shows 5 items which are to be sold. It shows the website and also the amount paid for the item previously (cost).

Item <- c("A1", "A2", "A3", "A4", "A5")
Cost <- c(10, 14, 18.85, 20, 5)    
Channel <- c("DIRECT", "EBAY", "AMAZON", "AMAZON", "AMAZON")

Price_table <- as.data.frame(cbind(Item, Cost, Channel))

I need to create a column for the Price_table which shows the "break-even" price for each item. I need to find the applicable commission charges depending on the channel on which the item is being sold, and for the Amazon items, I need to make sure that the minimum commission of £2 is charged if the standard commission percentage is calculated to be less than this.

To clarify, the break-even price needs to be; Cost + max(commission_percentage * cost, min_commission) + (processing*(cost + max(commission_percentage * cost, min_commission)))

Ideally this needs to be memory efficient as the database is very, very large. Hence why I haven't just gone for a merge function and removed the extra columns once the price is calculated.

Many thanks, Dan

Aucun commentaire:

Enregistrer un commentaire