CODEWITHSUNDEEP
rdplyrtidyverse
Shree
Shree

Reputation: 11150

Find min value among rows including and below given row by group

I have a dataframe where I need to group by Mat and find the minimum value at each row from among rows including and below given row. Code shows test data and my code for doing this. Field min_value is the desired output. Wondering if there is a tidyverse way to do this -

df <- structure(list(Mat = c("A", "A", "A", "A", "A", "A", "B", "B", 
"B", "B", "B", "B"), value = c(11L, 22L, 0L, 22L, 33L, 43L, 108L, 
152L, 0L, 486L, 706L, 830L)), .Names = c("Mat", "value"), class = "data.frame", row.names = c(NA, 
-12L))

group_by(df, Mat) %>%
  mutate(
    min_value = sapply(row_number(), function(a) min(value[a:length(value)]))
  ) %>%
  ungroup()

# A tibble: 12 x 3
   Mat   value min_value
   <chr> <int>     <int>
 1 A        11         0
 2 A        22         0
 3 A         0         0
 4 A        22        22
 5 A        33        33
 6 A        43        43
 7 B       108         0
 8 B       152         0
 9 B         0         0
10 B       486       486
11 B       706       706
12 B       830       830

Upvotes: 0

Views: 57

Answers (1)

Frank
Frank

Reputation: 66819

You can do...

df %>% group_by(Mat) %>% mutate(v = rev(cummin(rev(value))))

# or maybe more 'verse-like
df %>% group_by(Mat) %>% mutate(v = value %>% rev %>% cummin %>% rev)

# or ...
below_min = . %>% rev %>% cummin %>% rev
df %>% group_by(Mat) %>% mutate(v = below_min(value))

Or with data.table (which modifies df itself)...

library(data.table)
setDT(df)

df[order(.N:1), v := cummin(value), by=Mat]

There's a similar Q&A over here, if anyone is interested:

Upvotes: 2

Related Questions