Merge columns based on values in multiple columns pandas

Question

I have a DataFrame as follows:

   Name  Col2        Col3
0  A     16-1-2000   NaN
1  B     13-2-2001   NaN
2  C     NaN         NaN
3  D     NaN         23-4-2014
4  X     NaN         NaN
5  Q     NaN         4-5-2009

I want to make a combined column based on either data of Col2 & Col3, such it would give me following output.

   Name  Col2        Col3      Result
0  A     16-1-2000   NaN       16-1-2000
1  B     13-2-2001   NaN       13-2-2001
2  C     NaN         NaN       NaN
3  D     NaN         23-4-2014 23-4-2014
4  X     NaN         NaN       NaN
5  Q     NaN         4-5-2009  4-5-2009

I have tried following:

df['Result'] = np.where(df["Col2"].isnull() & df["Col3"].isnull(), np.nan, df["Col2"] if dfCrisiltemp["Col2"].notnull() else df["Col3"])

but no success.

jezrael · Accepted Answer · 2018-09-04 12:49:08Z

1

Use combine_first or fillna:

df['new'] = df["Col2"].combine_first(df["Col3"])
#alternative
#df['new'] = df["Col2"].fillna(df["Col3"])
print (df)
  Name       Col2       Col3        new
0    A  16-1-2000        NaN  16-1-2000
1    B  13-2-2001        NaN  13-2-2001
2    C        NaN        NaN        NaN
3    D        NaN  23-4-2014  23-4-2014
4    X        NaN        NaN        NaN
5    Q        NaN   4-5-2009   4-5-2009

Your solution should be changed to another np.where:

df['new'] = np.where(df["Col2"].notnull() & df["Col3"].isnull(), df["Col2"],
            np.where(df["Col2"].isnull() & df["Col3"].notnull(), df["Col3"], np.nan))

Or numpy.select:

m1 = df["Col2"].notnull() & df["Col3"].isnull()
m2 = df["Col2"].isnull() & df["Col3"].notnull()
df['new'] = np.select([m1, m2], [df["Col2"], df["Col3"]], np.nan)

For general solution filter all columns without first by iloc, forward fill NaNs and last select last column:

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

edited Sep 4, 2018 at 12:49

answered Sep 4, 2018 at 12:42

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abhi1610 Over a year ago

Awesomme...Thanks for the answer. I was thinking about numpy.select and there you have it in your answer.

Collectives™ on Stack Overflow

Merge columns based on values in multiple columns pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related