Filtering values on specific criteria-CodePudding

Below you can see my data.

df<-data.frame(  
         Description=c("15","11","12","NA","Total","NA","9","18","NA","Total","9","18","NA","11"),
         Value=c(158,196,NA,156,140,693,854,NA,904,925,156,140,693,123))

df

I want to filter this data. The filter needs to take into account only values until the last categorical value in column Description with value Total. The output that I looking for you can see in the next pic below.

I tried this command but is not worked.

df %>% filter(Description=='Total')

So can anybody help me how to solve this?

CodePudding user response：

Base R solution:

df[1:max(which(df$Description=="Total")),]

   Description Value
1           15   158
2           11   196
3           12    NA
4           NA   156
5        Total   140
6           NA   693
7            9   854
8           18    NA
9           NA   904
10       Total   925

CodePudding user response：

You could use a column with the row_number to filter all rows before the index of the row with the latest "Total" value with which and length like this:

library(dplyr)
df %>%
  mutate(number = row_number()) %>%
  filter(number <= which(Store == "Total")[length(which(Store == "Total"))]) %>%
  select(-number)
#>    Store Value
#> 1     15   158
#> 2     11   196
#> 3     12    NA
#> 4     NA   156
#> 5  Total   140
#> 6     NA   693
#> 7      9   854
#> 8     18    NA
#> 9     NA   904
#> 10 Total   925

^{Created on 2023-01-22 with reprex v2.0.2}

CodePudding user response：

alternatively, please check

data.frame(  
  Description=c("15","11","12","NA","Total","NA","9","18","NA","Total","9","18","NA","11"),
  Value=c(158,196,NA,156,140,693,854,NA,904,925,156,140,693,123)) %>% 
  mutate(tot=ifelse(str_detect(Description,'Total'), row_number(), NA)) %>%
  fill(tot, .direction = 'up') %>% filter(!is.na(tot)) %>% select(-tot)

^{Created on 2023-01-22 with reprex v2.0.2}

   Description Value
1           15   158
2           11   196
3           12    NA
4           NA   156
5        Total   140
6           NA   693
7            9   854
8           18    NA
9           NA   904
10       Total   925