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

Andrej Kesely · Accepted Answer · 2021-05-06 11:16:55Z

6

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.

answered May 6, 2021 at 11:16

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mustafa Aydın · Accepted Answer · 2021-05-06 11:26:43Z

1

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

answered May 6, 2021 at 11:26

Mustafa Aydın

18.4k4 gold badges21 silver badges43 bronze badges

Collectives™ on Stack Overflow

Pandas removing whitespace from columns to create on column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related