R: Remove rows from data frame based on values in several columns

Question

I have the following dataframe (df) - there are more columns, but these are the relevant columns:

I would like to subset this dataframe such that if any of the costs for a particular ID = $0, then it should remove all those rows (i.e. all the rows for that particular ID.)

Therefore, in this example, ID 2 and 5 contain a $0, so all of ID 2 and ID 5 rows should be deleted.

Here is the resulting df I would like:

Could someone help with this? I tried some combinations of the subset function, but it didn't work.

** On a similar note: I have another dataframe with "NA"s - could you help me figure out the same problem, in case it were NAs, instead of 0's.

Thanks in advance!!

A data.table option is. library(data.table); setDT(df)[, if(!any(Cost=='$0')) .SD, ID] — akrun
– akrun, Commented Jun 9, 2015 at 17:09

Aaron Katch · Accepted Answer · 2015-06-09 16:58:55Z

4

try this:

subset(df,!df$ID %in% df$ID[is.na(df$Cost) | df$Cost == "$0"])

this gives you:

  ID Cost
1  1 $100
2  1 $200
6  3  $10
7  4 $100

answered Jun 9, 2015 at 16:58

Aaron Katch

4513 silver badges13 bronze badges

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

1 Comment

Pierre L Over a year ago

+1 nice job using subset. you may save keystrokes with with(df, subset(df,!ID %in% ID[is.na(Cost) | Cost == "$0"]))

C_Z_ · Accepted Answer · 2015-06-09 16:50:06Z

3

Try

df[!df$ID %in% df$ID[df$Cost=="$0"],]

answered Jun 9, 2015 at 16:50

C_Z_

7,8765 gold badges53 silver badges92 bronze badges

Comments

josliber · Accepted Answer · 2015-06-09 16:53:48Z

1

You can compute the IDs that you want to remove with something like tapply:

(has.zero <- tapply(df$Cost, df$ID, function(x) sum(x == 0) > 0))
#     1     2     3     4     5 
# FALSE  TRUE FALSE FALSE  TRUE

Then you can subset, limiting to IDs that you don't want to remove:

df[!df$ID %in% names(has.zero)[has.zero],]
#   ID Cost
# 1  1  100
# 2  1  200
# 6  3   10
# 7  4  100

This is pretty flexible, because it enables you to limit IDs based on more complicated criteria (e.g. "the average cost for the ID must be at least xyz").

answered Jun 9, 2015 at 16:53

josliber

44.4k12 gold badges103 silver badges136 bronze badges

2 Comments

user4918087 Over a year ago

thanks @josilber! What if I want to remove the rows based on NAs?

josliber Over a year ago

Then you would change sum(x == 0) > 0 to sum(is.na(x)) > 0.

Collectives™ on Stack Overflow

R: Remove rows from data frame based on values in several columns

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related