Fill columns values at the last of dataframe and remove Nones from dataframe

Question

I have Dataframe df as shown below: For every row columns with value None should be replaced with columns 2 , 3, 4 ,5 values from dataframe.

  ID     1           2      3      4      5     col0      col1      col2      col3     col4     col5 col6
0 A1    ABC         RED1   RED2    RED3   RED4   10        20       None     None     None      None None
1 B1    ABC         OR1    OR2     OR3    OR4    40        None     None     None     None      None None
2 C1    ABC         WHITE1 WHITE2  WHITE3 WHITE4 50        34        35       57       78         98 None
3 D1    ABC         BLUE1  BLUE2   BLUE3  BLUE4  20        None     None     None      None     None None

I Want output:

  ID     1           col0      col1      col2      col3     col4     col5 col6 NEW1  NEW2  NEW3  NEW4
0 A1    ABC           10        20       RED1       RED2    RED3     RED4  
1 B1    ABC           40        OR1       OR2       OR3     OR4
2 C1    ABC           50        34        35       57       78         98  99 WHITE1 WHITE2 WHITE3 WHITE4
3 D1    ABC           20        BLUE1     BLUE2    BLUE3    BLUE4

provide the sample code to create that dataframe – Rahul Vishwakarma Jun 29 '20 at 11:08 — Rahul Vishwakarma, Jun 29 '20 at 11:08

score 0 · Answer 1 · answered Jun 29 '20 at 11:09

Idea is change order of columns - add 1-5 columns to last columns first:

m = df.columns.isin([2,3,4,5])
c = df.columns[~m].tolist() +  df.columns[m].tolist()
df = df.reindex(c, axis=1)
print (df)

   ID    1  col0 col1 col2 col3 col4 col5 col6       2       3       4       5
0  A1  ABC    10   20  NaN  NaN  NaN  NaN  NaN    RED1    RED2    RED3    RED4
1  B1  ABC    40  NaN  NaN  NaN  NaN  NaN  NaN     OR1     OR2     OR3     OR4
2  C1  ABC    50   34   35   57   78   98  NaN  WHITE1  WHITE2  WHITE3  WHITE4
3  D1  ABC    20  NaN  NaN  NaN  NaN  NaN  NaN   BLUE1   BLUE2   BLUE3   BLUE4

And then justify columns to left:

#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 = pd.notna(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, 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.fillna('').to_numpy(), invalid_val=''), 
                  columns=df.columns, 
                  index=df.index)
print (df)
   ID    1 col0   col1   col2   col3   col4  col5    col6       2       3  \
0  A1  ABC   10     20   RED1   RED2   RED3  RED4                           
1  B1  ABC   40    OR1    OR2    OR3    OR4                                 
2  C1  ABC   50     34     35     57     78    98  WHITE1  WHITE2  WHITE3   
3  D1  ABC   20  BLUE1  BLUE2  BLUE3  BLUE4                                 

        4 5  
0            
1            
2  WHITE4    
3

Fill columns values at the last of dataframe and remove Nones from dataframe

1 Answers1