How Can I drop a column if the last row is nan

Question

I have found examples of how to remove a column based on all or a threshold but I have not been able to find a solution to my particular problem which is dropping the column if the last row is nan. The reason for this is im using time series data in which the collection of data doesnt all start at the same time which is fine but if I used one of the previous solutions it would remove 95% of the dataset. I do however not want data whose most recent column is nan as it means its defunct.

A B C
nan t x 
1 2 3
x y z
4 nan 6

Returns

A C
nan x
1 3
x z
4 6

Agaz Wani · Accepted Answer · 2020-10-17 20:44:02Z

5

You can also do something like this

df.loc[:, ~df.iloc[-1].isna()]

answered Oct 17, 2020 at 20:44

Agaz Wani

5,7829 gold badges45 silver badges62 bronze badges

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

Comments

BENY · Accepted Answer · 2020-10-17 20:43:37Z

4

Try with dropna

df = df.dropna(axis=1, subset=[df.index[-1]], how='any')
Out[8]: 
     A  C
0  NaN  x
1    1  3
2    x  z
3    4  6

answered Oct 17, 2020 at 20:43

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

rafaelc · Accepted Answer · 2020-10-17 20:51:15Z

3

You can use .iloc, .loc and .notna() to sort out your problem.

df = pd.DataFrame({"A":[np.nan, 1,"x",4],  
                   "B":["t",2,"y",np.nan],
                   "C":["x",3,"z",6]})
 
df = df.loc[:,df.iloc[-1,:].notna()]

edited Oct 17, 2020 at 20:51

rafaelc

59.5k15 gold badges64 silver badges88 bronze badges

answered Oct 17, 2020 at 20:41

Nuno B. Brandao

766 bronze badges

Comments

Michael Szczesny · Accepted Answer · 2020-10-17 20:32:39Z

2

You can use a boolean Series to select the column to drop

df.drop(df.loc[:,df.iloc[-1].isna()], axis=1)

Out:

answered Oct 17, 2020 at 20:32

Michael Szczesny

5,0465 gold badges20 silver badges36 bronze badges

Comments

Knl_Kolhe · Accepted Answer · 2020-10-18 08:22:41Z

for i in range(temp_df.shape[1]):
    if temp_df.iloc[-1,i] == 'nan':
        temp_df = temp_df.drop(i,1)

This will work for you. Basically what I'm doing here is looping over all columns and checking if last entry is 'nan', then dropping that column. temp_df.shape[1] this is the numbers of columns.

pandas.df.drop(i,1) i represents the column index and 1 represents that you want to drop the column.

EDIT: I read the other answers on this same post and it seems to me that notna would be best (I would use it), but the advantage of this method is that someone can compare anything they wish to. Another method I found is isnull() which is a function in the pandas library which will work like this:

for i in range(temp_df.shape[1]):
    if temp_df.iloc[-1,i].isnull():
        temp_df = temp_df.drop(i,1)

Collectives™ on Stack Overflow

How Can I drop a column if the last row is nan

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest