Accessing one level previous records in R

Question

I've a dataset which looks like this

Dataset

I need to calculate Aspiration as follows:

If the FY=2009 (base year), then Asp = Performance else, Asp = pi - Performance of the previous FY for the company.

I wrote the following code in R:

mydata$Aspiration <- ifelse(mydata$FY == 2009, mydata$Performance, *something*)

Please help.

Your data looks like an image. Can you use [dput](https://stackoverflow.com/questions/49994249/example-of-using-dput) to show your data? — January, Jul 23 '19 at 05:21

score 0 · Answer 1 · answered Jul 23 '19 at 05:58

Not quite sure what's changing between different products and "Comp", but a quick solution is to save the different Comp's 2009 performances and put it in a vector for lookup. Here I am assuming that the Comp's performance is unique across all products.

library(dplyr)
comp <- mydata %>% distinct(Comp, Performance) %>% structure(.$Performance, .Names=$.Comp)
# comp is now a named numeric vector similar to
# comp <- c(A=1.1, B=0.5)

# calculate aspiration:
mydata %>% mutate(Aspiration=ifelse(FY == 2009, Performance, comp[Comp] - Performance))
# or with your code
mydata$Aspiration <- ifelse(mydata$FY == 2009, mydata$Performance, comp[mydata$Comp] - mydata$Performance)

Please consider upvoting the answers you found useful and/or accepting one of them. — MrGumble, Jul 24 '19 at 06:13

score 0 · Answer 2 · answered Jul 23 '19 at 06:07

We can recreate your data using:

df <- tibble::tibble(
  ID = c(rep(1,7),rep(2,8)),
  Comp = c(rep("A",7),rep("B",8)),
  Prod = c(
    "X","Y","Y","Z",
    "X","Y","Z",
    "E","F","G","E",
    "F","G","F","G"
  ),
  FY = c(
    2009,2009,2010,2010,
    2011,2011,2011,
    2009,2009,2009,2010,
    2010,2010,2011,2011
  ),
  `Performance (P)` = c(
    1.1,1.1,1.2,1.2,
    1.3,1.3,1.3,
    0.5,0.5,0.5,0.7,
    0.7,0.7,0.8,0.8
  )
)

We can subset ONLY the information we need using dplyr. We will call this subset dictionary.

dictionary <- dplyr::select(
  df,
  Comp, FY, `Performance (P)`
)
dictionary <- unique(dictionary)

Then we can create a function that looks up this information for each row in df.

fxn <- function(company, fy, performance, dictionary){
  new_values <- sapply(
    seq_along(company),
    function(i){
      # Attempt to extract the previous year's value.
      prev_performance <- dplyr::filter(
        dictionary,
        Comp == company[i],
        FY == fy[i] - 1
      )[["Performance (P)"]]

      # Return the new value or the current year's value.
      if(length(prev_performance) == 0){
        return(performance[i])
      }else{
        return(pi - prev_performance)
      }
    }
  )

  new_values
}

Finally, we can use the function mutate to apply this function to each row of df.

dplyr::mutate(
  df,
  `Aspiration (Asp)` = fxn(Comp, FY, `Performance (P)`, dictionary)
)

I hope this helps!

Accessing one level previous records in R

2 Answers2