Missing Value detection fails for completely empty cells python pandas

user_14801858 0
Viewed 331

I have created a function for which the input is a pandas dataframe. It should return the row-indices of the rows with a missing value. It works for all the defined Missingness values except when the cell is entirely empty - even though I tried to specify this in the missing_values List as [...,""] .

What could be the issue here? Or is there even a more intuitive way to solve this in general?

def missing_values(x):
    df=x

    missing_values = ["NaN","NAN","NA","Na","n/a", "na", "--","-"," ","","None","0","-inf"] #common ways to indicate missingness 
    observations = df.shape[0]  # Gives number of observations (rows)
    variables = df.shape[1] # Gives number of variables (columns)

    row_index_list = []

    #this goes through each observation in the first row 
    for n in range(0,variables): #this iterates over all variables
        column_list = [] #creates a list for each value per variable
    
        for i in range(0,observations): #now this iterates over every observation per variable
            column_list.append(df.iloc[i,n]) #and adds the values to the list

        for i in range(0,len(column_list)): #now for every value
            if column_list[i] in missing_values: #it is checked, whether the value is a Missing one 
                            row_index_list.append(column_list.index(column_list[i])) #and if yes, the row index is appended

    finished = list(set(row_index_list)) #set is used to make sure the index only appears once if there are multiple occurences in one row and then it is listed

    return finished
missing-data pandas python
2 Answers

There might be spurious whitespace, so try adding strip() on this line:

if column_list[i].strip() in missing_values: #it is checked, whether the value is a Missing one

Also a simpler way to get the indexes of rows containing missing_values is with isin() and any(axis=1):

x = x.replace('\s+', '', regex=True)
row_index_list = x[x.isin(missing_values).any(axis=1)].index
user_131383640

When you import a file to Pandas using for example read_csv or read_excel, the missing variable (literally missing) is then can only be specify using np.nan or other type of null value with the numpy library.

(Sorry my bad right here, I was really silly when doing np.nan == np.nan)

You can replace the np.nan value first with:

df = df.replace(np.nan, 'NaN')

then your function can catch it.

Another way is to use isna() in pandas,

df.isna()

This will return the same DataFrame but with cell contains boolean value, True for each cell that is np.nan

If you do df.isna().any(),

This will return a Series with True value for any columns that contains null value.

If you want to retrieved the ID, simply adding the parameter axis = 1 to any():

df.isna().any(axis = 1)

This will return a Series show all the rows with np.nan value.

Now you have the boolean values that indicate which row contains null values. If you add these boolean value to a list and apply that on the DF.index this will took out the index value of the rows containing null.

booleanlist = df.isna().any(axis =1).tolist()  
null_row_id = df.index[booleanlist]
user_131847910
Related