Removing nan from pandas dataframe and reshaping dataframe

Question

I have a pandas dataframe df which looks as following:

    0    1    2   3    4     5    6
0   3    74                 
1   4    2                  
2            -9             
3                 -1   2    -16   -21
4             1             
5             28

I want to remove all the nan from the above and realign the data in each row to get the following:

    0   1    2   3
0   3   74      
1   4   2       
2   -9          
3   -1  2   -16  -21
4   1           
5   28

Basically I am trying to left align all the data in each row after removing nan. I am not sure how to proceed with this.

score 2 · Accepted Answer · answered May 03 '20 at 05:30

First shift all non missing values by justify and then use DataFrame.dropna for remove only NaNs columns:

arr = justify(df.to_numpy(), invalid_val=np.nan)
df = pd.DataFrame(arr).dropna(axis=1, how='all')
print (df)
      0     1     2     3
0   3.0  74.0   NaN   NaN
1   4.0   2.0   NaN   NaN
2  -9.0   NaN   NaN   NaN
3  -1.0   2.0 -16.0 -21.0
4   1.0   NaN   NaN   NaN
5  28.0   NaN   NaN   NaN

#https://stackoverflow.com/a/44559180/2901002
def justify(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:
        mask = ~np.isnan(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)
    out = np.full(a.shape, invalid_val) 
    if axis==1:
        out[justified_mask] = a[mask]
    else:
        out.T[justified_mask.T] = a.T[mask.T]
    return out

sammywemmy · Answer 2 · 2020-05-03T07:00:23.307

This solution takes the data into numpy territory, run some computations with numpy isnan and numpy compress, create individual dataframes, and lump back into one dataframe with pandas concat :

data = """    0    1    2   3    4     5    6
              3    74   None  None  None    None   None   
              4    2    None  None  None    None   None   
              None   None  -9   None  None    None   None 
              None   None  None   -1   2    -16   -21
              None   None  1    None  None    None   None
              None   None  28   None  None    None   None         """

df = pd.read_csv(StringIO(data), sep='\s{2,}',engine='python', na_values=["None"])
df

      0 1        2  3      4     5        6
0   3.0 74.0    NaN NaN  NaN    NaN      NaN
1   4.0 2.0     NaN NaN  NaN    NaN      NaN
2   NaN NaN    -9.0 NaN  NaN    NaN      NaN
3   NaN NaN     NaN -1.0 2.0   -16.0    -21.0
4   NaN NaN     1.0 NaN NaN     NaN      NaN
5   NaN NaN     28.0NaN NaN     NaN      NaN

#convert to numpy array
M = df.to_numpy()

#get True or False depending on the null status of each entry
condition = ~np.isnan(M)

#for each array, get entries that are not null
step1 = [np.compress(ent,arr) for ent,arr in zip(condition,M)]
step1

#concatenate each dataframe 
step2 = pd.concat([pd.DataFrame(ent).T for ent in step1],ignore_index=True)
print(step2)

     0        1     2   3
0   3.0     74.0    NaN NaN
1   4.0     2.0     NaN NaN
2   -9.0    NaN     NaN NaN
3   -1.0    2.0   -16.0 -21.0
4   1.0     NaN    NaN  NaN
5   28.0    NaN    NaN  NaN

#alternatively, from step1 we could find the longest array and use that value to resize all the other arrays :
reshape = max(len(arr) for arr in step1)
#this happens in place
[arr.resize(reshape,refcheck=False) for arr in step1]
outcome = pd.DataFrame(step1).where(lambda x: x.ne(0),np.nan)

Removing nan from pandas dataframe and reshaping dataframe

2 Answers2

Linked