Compare missing values in python pandas from another column

Question

I have a pandas data frame that consists of two columns with value. Some of the values are missing and I would like to create a third column that marks if there are missing values in both columns or if one is filled. I am unsure on how to do this since I am new any help you can provided would be greatly appreciated

#input 
df = {'First': ['','','A','B','B','C'], 
  'Second': ['12', '', '10', '', '', '11']}
df = pd.DataFrame(data = d)

#Possible output of third column
df['Third'] = ['Secondfilled', 'missing', 'bothfilled', 'Firstfilled', 'Firstfilled', bothfilled']

Edo · Accepted Answer · 2021-04-09 16:04:11Z

3

One-line solution with no ifelse nor custom functions. Improved with @SeaBean 's suggestion!

d = {0: 'Missing', 1: 'FirstFilled', 2: 'SecondFilled', 3: 'BothFilled'}
df['Third'] = (df.ne('')*(1,2)).sum(1).map(d)

Output:

print(df)

  First Second         Third
0           12  SecondFilled
1                    Missing
2     A     10    BothFilled
3     B          FirstFilled
4     B          FirstFilled
5     C     11    BothFilled

edited Apr 9, 2021 at 16:04

answered Apr 9, 2021 at 15:42

Edo

7,8982 gold badges13 silver badges21 bronze badges

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

2 Comments

SeaBean Over a year ago

Upvoted. Can also do .sum(1)to replace .apply(sum, axis = 1) to further simplify it.

Edo Over a year ago

Great idea! Thanks

Ynjxsjmh · Accepted Answer · 2021-04-09 15:32:46Z

1

You can use apply() with a lookup dict.

lookup = {'10': 'Firstfilled', '01': 'Secondfilled', '11': 'bothfilled', '00': 'missing'}

def fill(row):
    key = '00'

    if row['First'] != '':
        key = '1' + key[1]

    if row['Second'] != '':
        key = key[0] + '1'

    return lookup[key]

df['Third'] = df.apply(fill, axis=1)

# print(df)

  First Second         Third
0           12  Secondfilled
1                    missing
2     A     10    bothfilled
3     B          Firstfilled
4     B          Firstfilled
5     C     11    bothfilled

answered Apr 9, 2021 at 15:32

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

Compare missing values in python pandas from another column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related