CODEWITHSUNDEEP
pythonpandasdataframesubset
hernanavella
hernanavella

Reputation: 5552

How can I remove columns of pandas dataframe conditional on last row values?

Given a data-frame like:

             A  B  C
2019-11-02  120 25 11
2019-11-03  119 28 15
2019-11-04  115 23 18
2019-11-05  119 30 20
2019-11-06  121 32 25
2019-11-07  117 24 30

I would like to remove the columns in which the value of the last row is less than (<) a constant X, say X = 25. In this example It would remove the column B only and the output would be:

             A  C
2019-11-02  120 11
2019-11-03  119 15
2019-11-04  115 18
2019-11-05  119 20
2019-11-06  121 25
2019-11-07  117 30

Thanks

Upvotes: 2

Views: 418

Answers (1)

Erfan
Erfan

Reputation: 42916

Method 1:

Use iloc, lt, and drop:

We select the last row with iloc[-1], then check which column is less than (lt) 25 and pass that column to DataFrame.drop

df = df.drop(columns = df.columns[df.iloc[-1].lt(25)])

Method 2:

Using tail, iloc, gt and all:

df = df.loc[:, df.tail(1).gt(25).all()]

              A   C
2019-11-02  120  11
2019-11-03  119  15
2019-11-04  115  18
2019-11-05  119  20
2019-11-06  121  25
2019-11-07  117  30

Step by step method 1:

# select last row

df.iloc[-1]

A    117
B     24
C     30
Name: 2019-11-07, dtype: int64

# check which columns have value < 25:

df.iloc[-1].lt(25)

A    False
B     True
C    False
Name: 2019-11-07, dtype: bool

# select those column(s) with boolean indexing:

df.columns[df.iloc[-1].lt(25)]

Index(['B'], dtype='object')

# finally pass it DataFrame.drop

df.drop(columns = df.columns[df.iloc[-1].lt(25)])

              A   C
2019-11-02  120  11
2019-11-03  119  15
2019-11-04  115  18
2019-11-05  119  20
2019-11-06  121  25
2019-11-07  117  30

Upvotes: 1

Related Questions