Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Drop pandas dataframe row based on max value of a column

I have a Dataframe like so:

      p_rel      y_BET  sq_resid
1  0.069370  41.184996  0.292942
2  0.116405  43.101090  0.010953
3  0.173409  44.727748  0.036832
4  0.225629  46.681293  0.540616
5  0.250682  46.980616  0.128191
6  0.294650  47.446113  0.132367
7  0.322530  48.078038  0.235047

How do I get rid of the fourth row because it has the max value of sq_resid? note: the max will change from dataset to dataset so just removing the 4th row isn't enough.

I have tried several things such as I can remove the max value which leaves the dataframe like below but haven't been able to remove the whole row.

  p_rel      y_BET  sq_resid
1  0.069370  41.184996  0.292942
2  0.116405  43.101090  0.010953
3  0.173409  44.727748  0.036832
4  0.225629  46.681293  Nan
5  0.250682  46.980616  0.128191
6  0.294650  47.446113  0.132367
7  0.322530  48.078038  0.235047
like image 818
Fungie Avatar asked Jan 29 '16 15:01

Fungie


People also ask

How do you drop pandas rows based on condition?

Use pandas. DataFrame. drop() method to delete/remove rows with condition(s).

How do I drop a specific row in a DataFrame?

To drop a specific row from the data frame – specify its index value to the Pandas drop function. It can be useful for selection and aggregation to have a more meaningful index. For our sample data, the “name” column would make a good index also, and make it easier to select country rows for deletion from the data.


1 Answers

You could just filter the df like so:

In [255]:
df.loc[df['sq_resid']!=df['sq_resid'].max()]

Out[255]:
      p_rel      y_BET  sq_resid
1  0.069370  41.184996  0.292942
2  0.116405  43.101090  0.010953
3  0.173409  44.727748  0.036832
5  0.250682  46.980616  0.128191
6  0.294650  47.446113  0.132367

or drop using idxmax which will return the label row of the max value:

In [257]:
df.drop(df['sq_resid'].idxmax())

Out[257]:
      p_rel      y_BET  sq_resid
1  0.069370  41.184996  0.292942
2  0.116405  43.101090  0.010953
3  0.173409  44.727748  0.036832
5  0.250682  46.980616  0.128191
6  0.294650  47.446113  0.132367
7  0.322530  48.078038  0.235047
like image 121
EdChum Avatar answered Oct 30 '22 21:10

EdChum