vendredi 1 mai 2020

Pairing Rows together to create new category and if else conditional statement

I have a data set which is similar like this data set, here I have created an new column total sales based on sum of day sales, also I have sorted the df basis descending order of total sales value

library(dplyr)

empid <- c(10,11,12,13,14,15)  # Employee id
city <- c("Goa","Goa","Goa","Goa","Goa","Goa")  # City
Day1 <- c(5,15,5,9,2,9)  # Sales made on Day 1 and so on...
Day2 <- c(5,3,8,5,10,7)
Day3 <- c(3,9,6,4,8,10)
Day4 <- c(7,6,8,8,2,8)

salesdata <- data.frame(empid,city,Day1,Day2,Day3,Day4)
#str(salesdata)


salesdata<- salesdata %>% mutate(Total_Sales = rowSums(.[3:6])) ## New Column creation Total sales

salesdata <- salesdata[order(-salesdata$Total_Sales),] ## Sorting df - salesdata, basis total sales value in descending order

View(salesdata)

empid city  Day1 Day2 Day3  Day4 Total_Sales                  
    15   Goa    9   7    10   8     34
    11   Goa    15  3    9    6     33
    12   Goa    5   8    6    8     27
    13   Goa    9   5    4    8     26
    14   Goa    2   10   8    2     22
    10   Goa    5   5    3    7     20

Question#1 I need to club emp id into pair wise (3 pairs in total) basis first to last approach(basis Total Sales value, Highest to lowest at first then so on) and it should look something similar to below chunk so that "New set of operation(Ques- 2) can be performed by using group_by(Pair_number) on each pair

   empid city  Day1 Day2 Day3  Day4 Total_Sales Pair_number                 
    15   Goa    9   7    10    8        34           P1
    10   Goa    5   5    3     7        20           P1
    11   Goa    15  3    9     6        33           P2
    14   Goa    2   10   8     2        22           P2
    12   Goa    5   8    6     8        27           P3
    13   Goa    9   5    4     8        26           P3

Question#2. After it is done, I need to calculate sales incentive "Day wise"(4 new columns to be created for each pair) on each pair(p1,p2,p3) which will be like, Incentive-Day1(new column) for "P1" - if sales value of of both emp id - 15 & 10 is greater than 5(individually) then I need to multiply the extra sales value by 50. So min criteria is 5+5= 10, any value greater than 10 is to be * by 50. For P1 on Day1 it is 9+5 = 14, extra sales value is 4, so incentive value is 200 combined for Day1/Pair1, Else the pair gets disqualified from incentive for that day.

Expected Output

empid city  Day1 Day2 Day3  Day4 Total_Sales Pair_number  Incent-Day1  Incent-Day2   Incent-Day3       
    15   Goa    9   7    10    8        34           P1        200        100         Disqualified
    10   Goa    5   5    3     7        20           P1
    11   Goa    15  3    9     6        33           P2    Disqualified Disqualified     350
    14   Goa    2   10   8     2        22           P2
    12   Goa    5   8    6     8        27           P3       200          150         Disqualified      
    13   Goa    9   5    4     8        26           P3      

Aucun commentaire:

Enregistrer un commentaire