2

Original data set is similar to dummy 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)

Question#1 I need to club emp id into pair wise (3 pairs in total) basis first to last approach(on 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.

Expecting this in output

   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. Then 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 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
rajeswa
  • 47
  • 9

1 Answers1

1

For your first problem

library(tidyverse)
salesdata <- tibble(empid,city,Day1,Day2,Day3,Day4) %>% 
  mutate(Total_Sales = rowSums(.[3:6])) %>% 
  arrange(desc(Total_Sales)) %>% 
  rowid_to_column("Pair_number") %>% 
  mutate(Pair_number = paste0("P", floor((Pair_number + 1) / 2 ))) %>% 
  select(empid, city,  Day1, Day2, Day3,  Day4, Total_Sales, Pair_number )

For your second problem - the condition is just too complicated for me to understand. But some suggestions:

  • Write your incentive logic as a function which you can test with discrete inputs.
  • Don't mix the 'Disqualified' character value in a column with numerical values. Use NA instead.
David T
  • 1,993
  • 10
  • 18
  • 1
    A minor change to your code will pair the IDs by highest and least total sales: salesdata <- tibble(empid,city,Day1,Day2,Day3,Day4) %>% mutate(Total_Sales = rowSums(.[3:6])) %>% arrange(desc(Total_Sales)) %>% rowid_to_column("Pair_number") %>% mutate(Pair_number = paste0("P", c(1:(nrow(salesdata)/2), (nrow(salesdata)/2):1))) %>% select(empid, city, Day1, Day2, Day3, Day4, Total_Sales, Pair_number ) %>% arrange(Pair_number) – Mohanasundaram May 02 '20 at 14:16
  • @David T, Thanks for Answering the Question , Apologies for replying late, this if else condition will really kill me in my original dataset. Will post it separately. – rajeswa May 04 '20 at 12:11