CODEWITHSUNDEEP
pythonpandas
SOK
SOK

Reputation: 1792

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

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!

Upvotes: 8

Views: 412

Answers (6)

Grayrigel
Grayrigel

Reputation: 3594

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

Upvotes: 1

Haleemur Ali
Haleemur Ali

Reputation: 28233

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]

Upvotes: 6

BENY
BENY

Reputation: 323226

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

Upvotes: 4

anky
anky

Reputation: 75080

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

Upvotes: 4

Quang Hoang
Quang Hoang

Reputation: 150735

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

Upvotes: 4

wwnde
wwnde

Reputation: 26676

Please try

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

Upvotes: 4

Related Questions