How to append columns of a pandas Dataframe based on none/null values?

Question

Need your help on one issue. I have a dataframe df which has 3 columns of 'object' data type- opt1,opt2,opt3.

Now I need to create a new column var which will have followings-

if opt2 and opt3 are None/Null/Empty and opt1 is not None/Null/Empty: then df['var'] = df['opt1']
elif opt1 and opt3 are None/Null/Empty and opt2 is None/Null/Empty: then df['var'] = df['opt1'] + '|' + df['opt3']
elif opt1 and opt2 are None/Null/Empty and opt3 is None/Null/Empty: then df['var'] = df['opt1'] + '|' + df['opt2']
else: df['var'] = df['opt1'] + '|' + df['opt2'] + '|' + df['opt3']

Please suggest me to put above conditions in Python 3.6 or share me any better approach.

Actually I am unable to design a correct if-elif-else condition for this scenario, this snippet is only for understanding my test cases. If possible please share the correct version of whole condition. — AnalyticsPy
– AnalyticsPy, Commented May 15, 2018 at 6:32

jezrael · Accepted Answer · 2018-05-15 06:43:41Z

1

I think need:

df = pd.DataFrame({'opt1':['',np.nan,'a','a','a',np.nan],
                       'opt2':[np.nan,'b',np.nan,'b','b',np.nan],
                        'opt3':['c','Null',np.nan,'c',np.nan,np.nan]})

print (df)
  opt1 opt2  opt3
0       NaN     c
1  NaN    b  Null
2    a  NaN   NaN
3    a    b     c
4    a    b   NaN
5  NaN  NaN   NaN

#replace strings Null and empty strins to NaN 
df1 = df.mask(df.isin(['Null','']))
#join values per rows with filter out NaNs
df['var'] = df1.apply(lambda x: '|'.join(x.dropna()), 1)
print (df)
  opt1 opt2  opt3    var
0       NaN     c      c
1  NaN    b  Null      b
2    a  NaN   NaN      a
3    a    b     c  a|b|c
4    a    b   NaN    a|b
5  NaN  NaN   NaN

edited May 15, 2018 at 6:43

answered May 15, 2018 at 6:32

jezrael

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

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

5 Comments

AnalyticsPy Over a year ago

df['var'] = df.apply(lambda x: '|'.join(x.dropna()), 1) it gives me error-KeyError: "['opt1' 'opt2' 'opt3'] not in index"

AnalyticsPy Over a year ago

currently in opt1 there are values but in other two columns values are as None. But I cannot remove these two columns because in future they may have data.

jezrael Over a year ago

It remove dynamically - only if None or NaN, else not.

AnalyticsPy Over a year ago

Instead of Nan or Null, opt2 and opt3 columns have None. Will this work for this also? Because I am getting KeyError: "['opt1' 'opt2' 'opt3'] not in index"

jezrael Over a year ago

Yes, dropna remove None and NaN if both missing values. If None is string, need df1 = df.mask(df.isin(['Null','', 'None']))

Collectives™ on Stack Overflow

How to append columns of a pandas Dataframe based on none/null values?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related