Pandas removing whitespace from columns to create on column

Question

I have some data which looks like

pd.DataFrame([
    {'col1': 'x', 'col2': 'name_x', 'col3': '', 'col4': '', 'col5': '', 'col6': ''},
    {'col1':'', 'col2':'', 'col3': 'y', 'col4':'name_y', 'col5': '', 'col6':''},
    {'col1':'', 'col2':'', 'col3': 'yy', 'col4':'name_yy', 'col5': '', 'col6':''},
    {'col1':'', 'col2':'', 'col3': '', 'col4':'', 'col5': 'z', 'col6':'name_z'},
    {'col1':'xx', 'col2':'name_xx', 'col3': '', 'col4':'', 'col5': '', 'col6':''},
    {'col1':'', 'col2':'', 'col3': 'yyy', 'col4':'name_yyy', 'col5': '', 'col6':''}
])

   col1  col2   col3    col4    col5    col6
0   x   name_x              
1                y     name_y       
2                yy    name_yy      
3                                z  name_z
4   xx  name_xx             
5                yyy   name_yyy         

I need to push all the data to the left most columns ie. col1 and col2

Final data should look like this:

    col1    col2
0   x   name_x
1   y   name_y
2   yy  name_yy
3   z   name_z
4   xx  name_xx
5   yyy name_yyy

Answer 1

df = df.transform(lambda x: sorted(x, key=lambda k: k == ""), axis=1)
print(df)

Prints:

  col1      col2 col3 col4 col5 col6
0    x    name_x                    
1    y    name_y                    
2   yy   name_yy                    
3    z    name_z                    
4   xx   name_xx                    
5  yyy  name_yyy                    

If you want the two columns, then you can do print(df[["col1", "col2"]]) afterwards.

Answer 2

out = (df.agg(" ".join, axis=1)
         .str.split(expand=True)
         .rename(columns={0: "col1", 1: "col2"}))

aggregrate the rows with joining them with whitespace and then split over whitespace, lastly rename columns for the output.

to get

  col1      col2
0    x    name_x
1    y    name_y
2   yy   name_yy
3    z    name_z
4   xx   name_xx
5  yyy  name_yyy