How To Move Nan Values To End In All Columns

I have a df like this, A B C a NaN NaN b NaN NaN c NaN NaN NaN a NaN NaN b NaN NaN c NaN NaN NaN a NaN NaN b NaN NaN c desi

Solution 1:

You can use a bit changed justify function:

defjustify(a, invalid_val=0, axis=1, side='left'):    
    """
    Justifies a 2D array

    Parameters
    ----------
    A : ndarray
        Input array to be justified
    axis : int
        Axis along which justification is to be made
    side : str
        Direction of justification. It could be 'left', 'right', 'up', 'down'
        It should be 'left' or 'right' for axis=1 and 'up' or 'down' for axis=0.

    """if invalid_val is np.nan:
        #change to notnull
        mask = pd.notnull(a)
    else:
        mask = a!=invalid_val
    justified_mask = np.sort(mask,axis=axis)
    if (side=='up') | (side=='left'):
        justified_mask = np.flip(justified_mask,axis=axis)
    #change dtype to object
    out = np.full(a.shape, invalid_val, dtype=object)  
    if axis==1:
        out[justified_mask] = a[mask]
    else:
        out.T[justified_mask.T] = a.T[mask.T]
    return out

---

df = pd.DataFrame(justify(df.values, invalid_val=np.nan, side='up', axis=0), 
                  columns=df.columns)
print (df)
     A    B    C
0    a    a    a
1    b    b    b
2ccc3NaNNaNNaN4NaNNaNNaN5NaNNaNNaN6NaNNaNNaN7NaNNaNNaN8NaNNaNNaN

Solution 2:

You can create a boolean mask, then sort the boolean mask using argsort, and index your final array:

A = df.isnull().values

out = df.values[np.argsort(A, axis=0, kind='mergesort'), np.arange(A.shape[1])]

array([['a', 'a', 'a'],
       ['b', 'b', 'b'],
       ['c', 'c', 'c'],
       [nan, nan, nan],
       [nan, nan, nan],
       [nan, nan, nan],
       [nan, nan, nan],
       [nan, nan, nan],
       [nan, nan, nan]], dtype=object)

And to recreate the DataFrame:

pd.DataFrame(out, columns=df.columns)

     A    B    C
0    a    a    a
1    b    b    b
2ccc3NaNNaNNaN4NaNNaNNaN5NaNNaNNaN6NaNNaNNaN7NaNNaNNaN8NaNNaNNaN