CODEWITHSUNDEEP
rdataframe
user8974334
user8974334

Reputation: 23

Extract Index of repeat value

how do I extract specific row of data when the column has repetitive value? my data looks like this: I want to extract the row of the end of each repeat of x (A 3 10, A 2 3 etc) or the index of the last value

Name    X   M
 A      1   1
 A      2   9
 A      3   10
 A      1   1
 A      2   3
 A      1   5
 A      2   6
 A      3   4
 A      4   5
 A      5   3
 B      1   1
 B      2   9
 B      3   10
 B      1   1
 B      2   3

Expected output

 Index  Name X   M
  3      A   3   10
  5      A   2   3
  10     A   5   3
  13     B   3   10
  15     B   2   3

Upvotes: 0

Views: 84

Answers (2)

thelatemail
thelatemail

Reputation: 93813

Using base R duplicated and cumsum:

dups <- !duplicated(cumsum(dat$X == 1), fromLast=TRUE)
cbind(dat[dups,], Index=which(dups))

#   Name X  M Index
#3     A 3 10     3
#5     A 2  3     5
#10    A 5  3    10
#13    B 3 10    13
#15    B 2  3    15

Upvotes: 1

www
www

Reputation: 39154

A solution using dplyr.

library(dplyr)

df2 <- df %>%
  mutate(Flag = ifelse(lead(X) < X, 1, 0)) %>%
  mutate(Index = 1:n()) %>%
  filter(Flag == 1 | is.na(Flag)) %>%
  select(Index, X, M)
df2
#   Index X  M
# 1     3 3 10
# 2     5 2  3
# 3    10 5  3
# 4    13 3 10
# 5    15 2  3

Flag is a column showing if the next number in A is smaller than the previous number. If TRUE, Flag is 1, otherwise is 0. We can then filter for Flag == 1 or where Flag is NA, which is the last row. df2 is the final filtered data frame.

DATA

df <- read.table(text = "Name    X   M
 A      1   1
 A      2   9
 A      3   10
 A      1   1
 A      2   3
 A      1   5
 A      2   6
 A      3   4
 A      4   5
 A      5   3
 B      1   1
 B      2   9
 B      3   10
 B      1   1
 B      2   3",
                 header = TRUE, stringsAsFactors = FALSE)

Upvotes: 0

Related Questions