How to combine three string columns to one which have Nan values in Pandas

Question

I have the following dataframe:

   A    B     C    
0 NaN   NaN   cat
1 dog   NaN   NaN   
2 NaN   cat   NaN   
3 NaN   NaN   dog

I would like to add a colunm with the value that doesnt have the NaN value. So that:

   A    B     C    D
0 NaN   NaN   cat  cat
1 dog   NaN   NaN  dog 
2 NaN   cat   NaN  cat 
3 NaN   NaN   dog  dog

would it be using an lambda function? or fillna? Any help would be appreciated! Thanks!

Answer 1

use combine_first chained

df['D'] = df.A.combine_first(df.B).combine_first(df.C)

alternatively, forward fill and pick the last column

df['D'] = df.ffill(axis=1).iloc[:,-1]

# specifying the columns explicitly:
df['D'] = df[['A', 'B', 'C']].ffill(1).iloc[:, -1]

Answer 2

Please try

df['D']=df.stack().reset_index(drop=True)

Answer 3

Let's try bfill:

df['D'] = df.bfill(1).iloc[:,0]

Output:

     A    B    C    D
0  NaN  NaN  cat  cat
1  dog  NaN  NaN  dog
2  NaN  cat  NaN  cat
3  NaN  NaN  dog  dog

Answer 4

I would go with bfill or ffill on axis=1 here as QuangHoang suggests, however if there is 1 column having values always , another alternative with df.isna , df.dot and df.lookup:

df['D'] = df.lookup(df.index,df.notna().dot(df.columns))
print(df)

---

     A    B    C    D
0  NaN  NaN  cat  cat
1  dog  NaN  NaN  dog
2  NaN  cat  NaN  cat
3  NaN  NaN  dog  dog