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

user_13194245 0
Viewed 124

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!

pandas python
6 Answers

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]
user_25702610

Please try

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

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
user_42384080

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
user_98406370

Try something new

df = df.join(df.groupby(['D']*df.shape[1],axis=1).first())
df
Out[44]: 
     A    B    C    D
0  NaN  NaN  cat  cat
1  dog  NaN  NaN  dog
2  NaN  cat  NaN  cat
3  NaN  NaN  dog  dog
user_79645270

df.melt with dropna works too:

df['D'] = df.T.melt()['value'].dropna().to_numpy()
>>> 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
user_56045620
Related