CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Karthick Raju
Karthick Raju

Reputation: 797

Pandas - Find empty columns of the row and update in one column

I've below code to read excel values

import pandas as pd
import numpy as np
import os

df = pd.read_excel(os.path.join(os.getcwd(), 'TestData.xlsx'))
print(df)

Excel data is 

    Employee ID First Name Last Name    Contact Technology  Comment
0            1   KARTHICK      RAJU  9500012345       .NET      
1            2       TEST            9840112345       JAVA      
2            3                 TEST  9145612345        AWS      
3            4                       9123498765     Python      
4            5       TEST      TEST  9156478965

Below code returns True if any cell holds empty value

print(df.isna())

like below

   Employee ID  First Name  Last Name  Contact  Technology  Comment
0        False       False      False    False       False     True
1        False       False       True    False       False     True
2        False        True      False    False       False     True
3        False        True       True    False       False     True
4        False       False      False    False        True     True

I want to add the comment for each row like below

Comment

Last Name is empty
First Name is empty
First Name and Last Name are empty
Technology is empty

One way of doing is iterating over each row to find the empty index and based on the index, comments can be updated.

If the table has huge data, iteration may not be a good idea

Is there a way to achieve this in more pythonic way?

Upvotes: 0

Views: 1427

Answers (1)

jezrael
jezrael

Reputation: 863351

You can simplify solution and instaed is and are use -, use matrix multiplication DataFrame.dot with boolean mask and columns with new value, last remove separator by DataFrame.dot:

#if column exist
df = df.drop('Comment', axis=1)

df['Comment'] = df.isna().dot(df.columns + '-empty, ').str.rstrip(', ')
print (df)
   Employee ID First Name Last Name     Contact Technology  \
0            1   KARTHICK      RAJU  9500012345       .NET   
1            2       TEST       NaN  9840112345       JAVA   
2            3        NaN      TEST  9145612345        AWS   
3            4        NaN       NaN  9123498765     Python   
4            5       TEST      TEST  9156478965        NaN   

                             Comment  
0                                     
1                    Last Name-empty  
2                   First Name-empty  
3  First Name-empty, Last Name-empty  
4                   Technology-empty

Upvotes: 3

Related Questions