1

I need to loop through years in a df and replace values in another column (percentpp) only if the next year's percentpp is larger. Once a cell has reached a high value, it cannot go back down - only up, like a ratchet.

Eg., what I have: 

  cell_id   year    percentpp
1   40      2011    3
2   41      2011    1
3   42      2011    0
4   43      2011    0
5   40      2012    1
6   41      2012    5
7   42      2012    1   
8   43      2012    5
9   40      2013    2
10  41      2013    2
11  42      2013    2
12  43      2013    0
13  40      2014    2   
14  41      2014    3   
15  42      2014    3   
16  43      2014    3

and what I'd like it to become: 

  cell_id   year    percentpp
1   40      2011    3
2   41      2011    1
3   42      2011    0
4   43      2011    0
5   40      2012    3
6   41      2012    5
7   42      2012    1   
8   43      2012    5
9   40      2013    3
10  41      2013    5
11  42      2013    2   
12  43      2013    5    
13  40      2014    3   
14  41      2014    5   
15  42      2014    3   
16  43      2014    5

I'm imagining a function with this pseudo-R/SQL kind of logic that is then looped through using lapply:

function(x) {
  if df$percentpp[year+1,] is greater than df$percentpp[year,]
  when cell_id$year = cell_id$year+1
  then df$percentpp[year,] <- df$percentpp[year+1,]
}

But I'm not sure how to do this properly.

spops
  • 572
  • 1
  • 7
  • 25

1 Answers1

2

You can use cummax.

With base R:

df <- df[order(df$year), ]
df$percentpp <- ave(df$percentpp, df$cell_id, FUN = cummax)

With dplyr:

library(dplyr)

df <- df %>%
  group_by(cell_id) %>%
  arrange(year) %>%
  mutate(percentpp = cummax(percentpp)) %>%
  ungroup

Data:

df <- read.table(text = "
  cell_id   year    percentpp
1   40      2011    3
2   41      2011    1
3   42      2011    0
4   43      2011    0
5   40      2012    1
6   41      2012    5
7   42      2012    1   
8   43      2012    5
9   40      2013    2
10  41      2013    2
11  42      2013    2
12  43      2013    0
13  40      2014    2   
14  41      2014    3   
15  42      2014    3   
16  43      2014    3 
")
Scarabee
  • 5,437
  • 5
  • 29
  • 55