How to remove subsequent values after first instance only when other values are absent

identify first occurrence excel
how to highlight duplicate values except first instance in excel?
excel count duplicate values only once
highlight unique values in excel
how to get the unique values from a column in excel vba
count unique values excel
index match countif duplicates
excel find first occurrence of value in column

I am trying to remove zeros after the first instance of a zero, when all future values are 0. Eventually I would love to do this group_by species but baby steps. Here's an example;

# Sample
library(tidyverse)
id<-c("a","b","c","d","e","f","g","h","i","j")
time<-c(1,2,3,4,5,6,7,8,9,10)
value<-c(90, 50, 40, 0, 30, 30, 0, 10, 0, 0)
df<-data.frame(id, time, value)
df

   id time value
1   a    1    90
2   b    2    50
3   c    3    40
4   d    4     0
5   e    5    30
6   f    6    30
7   g    7     0
8   h    8    10
9   i    9     0
10  j   10     0

I would like to see observation id "j" and only observation id "j" removed. I am not even sure where to start. Any suggestions are much appreciated!

here is a solution within the tidyverse which also works on a larger number of trailing zeros:

df <- tibble(id = letters[1:11], time = 1:11, 
             value = c(90,50,40,0,30,30,0,10,0,0,0))
df %>% 
  slice(n():1) %>% 
  slice(c(which(cumsum(value > 0) > 0)[1] - 1, which(cumsum(value > 0) > 0))) %>% 
  slice(n():1)

3 Ways to extract unique values from a range in Excel, Advanced Filter; Index- Match Array Formula; Excel Macro (VBA); Remove select a range in which final output to be put; Check Unique records only; Click Ok Suppose there are missing or blank values in your list from which you want to extract In other words, we can perform some calculation on more than one value  The only way I've found to delete duplicates is the standard filter that will just remove all duplicates. I can't do this since I have the other cols like B and C that have data. The reason I need this is due to the program I need to import and it will overwrite each line with the next if it has the same ID.

In base R only.It uses rle to get the number of trailing zeros, if any. Then subsets the dataframe with head.

r <- rle(df$value == 0)
if(r$values[length(r$values)]) head(df, -(r$lengths[length(r$values)] - 1))
#  id time value
#1  a    1    90
#2  b    2    50
#3  c    3    40
#4  d    4     0
#5  e    5    30
#6  f    6    30
#7  g    7     0
#8  h    8    10
#9  i    9     0

You can write a function with the code above, and maybe *apply it to groups.

trailingZeros <- function(DF, col = "value"){
    r <- rle(DF[[col]] == 0)
    if(r$values[length(r$values)] && r$lengths[length(r$values)] > 1)
        head(DF, -(r$lengths[length(r$values)] - 1))
    else
        DF
}

trailingZeros(df)

Note that this also works with a larger number of trailing zeros.

id2 <- c("a","b","c","d","e","f","g","h","i","j","k")
time2 <- c(1,2,3,4,5,6,7,8,9,10,11)
value2 <- c(90, 50, 40, 0, 30, 30, 0, 10, 0, 0, 0)    # One more zero at end
df2 <- data.frame(id = id2, time = time2, value = value2)

trailingZeros(df2)

Excel formula: Flag first duplicate in a list, To mark the first duplicate in a list, you can use a formula based on the COUNTIF function. Optionally, you can flag subsequent duplicates with a different marker. This formula will return 1 only when a value has been encountered twice – the first occurrence This is a great way to visually highlight missing items in a list. Remove duplicate values. When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Tidyverse solution that also works with groups

based on sample data (without grouping) code can be shortened, but this looks very readable ;-)

df %>% 
  #arrange by id
  arrange( id ) %>%
  #no grouping valiable in sample data.. so don't use group_by here
  #group_by( group) %>%
  #create dummy's: position in group, last value of group, position of last non-zero in group, previous value (within group)
  mutate( pos_in_group = 1:n() ) %>%
  mutate( last_value = last( value ) ) %>%
  mutate( pos_last_not_zero = max( which( value != 0) ) ) %>%
  mutate( prev_value = lag( value ) ) %>%
  #filter all rows where: 
  #   the last value of the group != 0 AND 
  #   the previous row (within the group) != 0 AND 
  #  the position of the row is 'below' the last non-zero measurement (in the group)
  filter( !(last_value == 0 & prev_value == 0 & pos_in_group >= pos_last_not_zero + 1 ) ) %>%
  #throw away the dummy's
  select( -c( pos_in_group, last_value, pos_last_not_zero, prev_value ) )

#   id time value
# 1  a    1    90
# 2  b    2    50
# 3  c    3    40
# 4  d    4     0
# 5  e    5    30
# 6  f    6    30
# 7  g    7     0
# 8  h    8    10
# 9  i    9     0

Example with some grouping involved

# Sample
library(tidyverse)
id<-c("a","b","c","d","e","f","g","h","i","j","k")
group<-c(1,1,1,1,1,1,2,2,2,2,2)
time<-c(1,2,3,4,5,6,7,8,9,10,11)
value = c(90,0,0,40,0,0,30,30,0,0,0)
df<-data.frame(id, group, time, value)

df
#    id group time value
# 1   a     1    1    90
# 2   b     1    2     0
# 3   c     1    3     0
# 4   d     1    4    40
# 5   e     1    5     0
# 6   f     1    6     0
# 7   g     2    7    30
# 8   h     2    8    30
# 9   i     2    9     0
# 10  j     2   10     0
# 11  k     2   11     0

df %>% 
  #arrange by id
  arrange( id ) %>%
  #group
  group_by( group) %>%
  #create dummy's: position in group, last value of group, position of last non-zero in group, previous value (within group)
  mutate( pos_in_group = 1:n() ) %>%
  mutate( last_value = last( value ) ) %>%
  mutate( pos_last_not_zero = max( which( value != 0) ) ) %>%
  mutate( prev_value = lag( value ) ) %>%
  #filter all rows where: 
  #   the last value of the group != 0 AND 
  #   the previous row (within the group) != 0 AND 
  #  the position of the row is 'below' the last non-zero measurement (in the group)
  filter( !(last_value == 0 & prev_value == 0 & pos_in_group >= pos_last_not_zero + 1 ) ) %>%
  #throuw away the dummy's
  select( -c( pos_in_group, last_value, pos_last_not_zero, prev_value ) )

# # A tibble: 8 x 4
# # Groups:   group [2]
#   id    group  time value
#   <fct> <dbl> <dbl> <dbl>
# 1 a         1     1    90
# 2 b         1     2     0
# 3 c         1     3     0
# 4 d         1     4    40
# 5 e         1     5     0
# 6 g         2     7    30
# 7 h         2     8    30
# 8 i         2     9     0

Microsoft Project 2010: The Missing Manual, Tip: If you want to copy tasks without copying any field values other than task Click the first Task Name cell in the blank rows corresponding to the new tasks, and Tip: If you copy tasks after you've entered actual values, be sure to delete the In the “Replace with” box, type the new term for the copied tasks, for instance,  2. Then the duplicate values have been merged into one cell. And click Home > Merge & Center > Unmerge Cells to split them. See screenshot: Now the result has been shown as this: Method 2 Select Duplicate & Unique Cells (4 steps) 1. Select the list of data you want to remove duplicates from, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

Introduction to Law, In addition, other civil actions provide methods to regain wrongfully obtained In the first instance, a party may seize control of property without permission. is also permitted to bring a lawsuit based on conversion that requires only proof of if the property has been altered or disposed of, then its value may be recovered​. Change the format as desired for duplicate values, you can change the format for Numbers, Font, Borders, and Fill. Click ‘ OK ‘. Click ‘ OK ‘ again. Steps are shown below for your convenience. Figure 1: Steps to highlight the values after the second occurrence. You will see that duplicate values are highlighted after the second

Pandas: Drop consecutive duplicates, Note the difference in index values, thanks @BjarkeEbert! If only the values are needed, we could get major boost by simply indexing into the array This will remove the next duplicate from multiple columns but not drop all of the columns. When the dataframe is sorted it will keep the first row but drop the second row if the  To mark the first duplicate in a list, you can use a formula based on the COUNTIF function. Optionally, you can flag subsequent duplicates with a different marker. In the example shown the formula in cell C4 is: This formula has been copied down the column, from C4 to C11.

British Medical Journal, Expectant treatment should be employed in the first instance in cases of difluse provided that there were no other reasons requiring operative interference, and said he believed that after excision of the hip the subsequent malnutrition and What views do others hold as to the value of the lid sign? as a matter of fact, is it  Filters instances according to the value of an attribute. Valid options are: -C <num> Choose attribute to be used for selection. -S <num> Numeric value to be used for selection on numeric attribute. Instances with values smaller than given value will be selected. (default 0)

Comments
  • This seems to work consistently and works with grouping. Thank you Cett!!
  • this does not work if there are two consecutive zeroes which are not at the end: for example value = c(90,50,40,0,0,0,30,30,0,0)
  • @cett I updated my answer, now also compatible with grouping
  • I love how this is explained! I'm getting the following error; Error in select(., -c(pos_in_group, last_value, pos_last_not_zero, prev_value)) : unused argument (-c(pos_in_group, last_value, pos_last_not_zero, prev_value))
  • @Dustin; perhaps you are trying to deselect a field/column that you do not have in your data.frame?
  • It works!!! And with grouping! My earlier error had something to do with Sys.setenv in v.3.4.2. Thank you so much!!!