Python - Create a new column that takes the first column from the right that is not NaN in Pandas

user_8993860 0
Viewed 115

I have a DataFrame with col names 'a', 'b', 'c', 'd', 'e'

#Input
import pandas as pd
import numpy as np

list_of_dicts = [
  {'a' : np.nan, 'b' : 4, 'c' : np.nan, 'd' : 5, 'e' : 2},
  {'a' : 1, 'b' : np.nan, 'c' : np.nan, 'd' : np.nan, 'e' : np.nan},
  {'a' : 7, 'b' : 5, 'c' : 0, 'd' : 5, 'e' : 3  },
  {'a' : np.nan, 'b' : 3, 'c' : np.nan, 'd' : 5, 'e' : np.nan  },
  {'a' : np.nan, 'b' : np.nan, 'c' : np.nan, 'd' : np.nan, 'e' : np.nan  }
]

df = pd.DataFrame(list_of_dicts)

#Input DataFrame
-----|-------|-------|-------|------|------|
     |  a    |  b    |  c    |  d   |  e   |
-----|-------|-------|-------|------|------|
 0   |  NaN  |  4    |  NaN  |  5   |  2   |
 1   |  1    |  NaN  |  NaN  |  NaN |  NaN |
 2   |  7    |  5    |  0    |  5   |  3   |
 3   |  NaN  |  3    |  NaN  |  5   |  NaN |
 4   |  NaN  |  NaN  |  NaN  |  NaN |  NaN |

I want to create a new column Op that takes the first column from the right that is not NaN and populates the value. If all observations are NaN, then Op = NaN.

#Output
-----|-------|-------|-------|------|------|------|
     |  a    |  b    |  c    |  d   |  e   |  Op  |
-----|-------|-------|-------|------|------|------|
 0   |  NaN  |  4    |  NaN  |  5   |  2   |  2   |
 1   |  1    |  NaN  |  NaN  |  NaN |  NaN |  1   |
 2   |  7    |  5    |  0    |  5   |  3   |  3   |
 3   |  NaN  |  3    |  NaN  |  5   |  NaN |  5   |
 4   |  NaN  |  NaN  |  NaN  |  NaN |  NaN |  NaN |

Explanation:

  1. Row 0 - Since 'e' is the first column from the Right that is not NaN, Op = 2
  2. Row 1 - Since 'a' is the first column from the Right that is not NaN, Op = 1
  3. Row 2 - Since 'e' is the first column from the Right that is not NaN, Op = 3
  4. Row 3 - Since 'd' is the first column from the Right that is not NaN, Op = 5
  5. Row 4 - Since all rows are NaN, Op = NaN

Looking for a Solution using Python, Pandas and numpy.

pandas dataframe numpy python
2 Answers

Try with ffill

df['new'] = df.ffill(1).iloc[:,-1]
df
Out[59]: 
     a    b    c    d    e  new
0  NaN  4.0  NaN  5.0  2.0  2.0
1  1.0  NaN  NaN  NaN  NaN  1.0
2  7.0  5.0  0.0  5.0  3.0  3.0
3  NaN  3.0  NaN  5.0  NaN  5.0
4  NaN  NaN  NaN  NaN  NaN  NaN
user_79645270

You can use series.last_valid_index() and return the value or np.nan if no value is found.

def r(row):
    last_index = row.last_valid_index()
    return row[last_index] if last_index else np.nan

df['op'] = df.apply(r, axis=1)

Outputs

     a    b    c    d    e   op
0  NaN  4.0  NaN  5.0  2.0  2.0
1  1.0  NaN  NaN  NaN  NaN  1.0
2  7.0  5.0  0.0  5.0  3.0  3.0
3  NaN  3.0  NaN  5.0  NaN  5.0
4  NaN  NaN  NaN  NaN  NaN  NaN
user_79428560
Related