Find a function to return value based on condition using R

Question

I have a table with values

KId sales_month quantity_sold
100        1    0
100        2    0
100        3    0
496        2    6
511        2    10
846        1    4
846        2    6
846        3    1
338        1    6
338        2    0    

now i require output as

KId sales_month quantity_sold result
100           1     0         1
100           2     0         1
100           3     0         1
496           2     6         1
511           2     10        1
846           1     4         1
846           2     6         1
846           3     1         0
338           1     6         1
338           2     0         1

Here, the calculation has to go as such if quantity sold for the month of march(3) is less than 60% of two months January(1) and February(2) quantity sold then the result should be 1 or else it should display 0. Require solution to perform this.

Thanks in advance.

Answer 1

If I understand well, your requirement is to compare sold quantity in month t with the sum of quantity sold in months t-1 and t-2. If so, I can suggest using dplyr package that offer the nice feature of grouping rows and mutating columns in your data frame.

resultData <- group_by(data, KId) %>% 
    arrange(sales_month) %>% 
    mutate(monthMinus1Qty = lag(quantity_sold,1), monthMinus2Qty = lag(quantity_sold, 2)) %>% 
    group_by(KId, sales_month) %>%
    mutate(previous2MonthsQty = sum(monthMinus1Qty, monthMinus2Qty, na.rm = TRUE)) %>%  
    mutate(result = ifelse(quantity_sold/previous2MonthsQty >= 0.6,0,1)) %>%
    select(KId,sales_month, quantity_sold, result)

The result is as below:

Adding

select(KId,sales_month, quantity_sold, result)

at the end let us display only columns we care about (and not all these intermediate steps).

I believe this should satisfy your requirement. NA is the result column are due to 0/0 division or no data at all for the previous months. Should you need to expand your calculation beyond one calendar year, you can add year column and adjust group_by() arguments appropriately. For more information on dplyr package, follow this link