CODEWITHSUNDEEP
pythonpandas
Quincy
Quincy

Reputation: 143

How to create a column which notifies which column a change across a row occurred using pandas?

I want to create a column which tells me where B becomes a missing value across each row in the dataframe below.

> data = [['Abbey','A','B','C','D','E'],['Brian','A','B',np.NAN,np.NAN,np.NAN],['Charles',np.NaN,'A','A','B',np.NaN],['Daniel','A','B','B',np.NaN,np.NaN]]

> df = pd.DataFrame(data, columns = ['Name',1,2,3,4,5])

And here is the expected outcome:

> df['B to Nan'] = ['N/A',3,5,4] 

> df

Upvotes: 1

Views: 35

Answers (1)

anky
anky

Reputation: 75080

You can do this by checking if df.eq('B') shifted by 1 place in axis=1 & df.isna() returns True, then use df.dot() with the columns to return the column wherever the logic returns True:

df=df.assign(B_to_Nan=(df.isna()&df.eq('B').shift(axis=1).
                        fillna(False)).dot(df.columns.astype(str)))

      Name    1  2    3    4    5 B_to_Nan
0    Abbey    A  B    C    D    E         
1    Brian    A  B  NaN  NaN  NaN        3
2  Charles  NaN  A    A    B  NaN        5
3   Daniel    A  B    B  NaN  NaN        4

Upvotes: 1

Related Questions