Select multiple values from a column based on condition

Question

I have this data frame called df:

df <- structure(list(opty_id = c(123L, 147L, 165L, 198L, 203L, 209L, 219L, 271L, 284L), 
               amount_opty = c(855252.21, 590259.86, 742818.76, 742818.76, 211760, 149190.3, 118924.22, 414663.687, 403313.319 ), 
               percent = c(NA, 0.309841175, NA, 0, 0.714923732, 0.295474594, 0.202868953, NA, 0.027372467)), 
               .Names = c("opty_id", "amount_opty", "percent"), 
               class = "data.frame", row.names = c(NA, -9L))

The percent column is the percentage increase compared to the previous row.

I need to find out the pair of rows where percent is less than 10%.

The desired output would be: id 8 and id 9 (basically its resulted id and -1)

pogibas · Accepted Answer · 2018-02-06 12:28:59Z

2

You can do this.

Divide previous diff by current value and find fraction (percentage):

c(df$diff[-1], NA) / df$value
# [1] 0.3333333 0.0000000 0.0400000 0.1666667        NA

Check if fraction difference is less than 0.1 (10%)

c(df$diff[-1], NA) / df$value < 0.1
# [1] FALSE  TRUE  TRUE FALSE    NA

Find ID's in your data:

which(c(df$diff[-1], NA) / df$value < 0.1) + 1
# [1] 3 4

Data:

df <- structure(list(id = 1:5, value = c(60L, 40L, 50L, 48L, 40L), 
diff = c(0L, 20L, 0L, 2L, 8L)), .Names = c("id", "value", "diff"), row.names = c(NA, -5L), class = "data.frame")

answered Feb 6, 2018 at 12:28

pogibas

28.5k21 gold badges92 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

umakant Over a year ago

thanks for the response.But its not working.. no need to calculate the percentage since its still there. I need to use the "percentage" column for filter and select the two consecutive ids..

pogibas Over a year ago

What kind of error message does it give? Why it doesn't work?

KenHBS · Accepted Answer · 2018-02-06 13:31:10Z

0

You can use which() to find the elements in df$percent that are smaller than 0.1 and also larger than 0:

main_id <- which(df$percent < 0.1 & df$percent > 0.0)   # gives 9
target  <- c(main_id - 1, main_id)
target
# [1] 8 9

If you don't want the row numbers, but you want the opty_id instead, use:

df$opty_id[target]
# [1] 271 284

edited Feb 6, 2018 at 13:31

answered Feb 6, 2018 at 12:40

KenHBS

7,2546 gold badges41 silver badges55 bronze badges

Collectives™ on Stack Overflow

Select multiple values from a column based on condition

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related