Python DataFrame: rearrange the objects and empty values [duplicate]

Question

I have a Python DataFrame with 20000+ values as below. And I want to efficiently rearrange df with NaN goes after string of values.

    IT1     IT2     IT3     IT4     IT5     IT6
0   qwe     NaN     NaN     rew     NaN     NaN
1   NaN     NaN     sdc     NaN     NaN     wer
2   NaN     NaN     NaN     NaN     NaN     NaN
3   asd     fsc     ws      zd      ews     df 
.....

to

    IT1     IT2     IT3     IT4     IT5     IT6
0   qwe     rew     NaN     NaN     NaN     NaN
1   sdc     wer     NaN     NaN     NaN     NaN     
2   NaN     NaN     NaN     NaN     NaN     NaN
3   asd     fsc     ws      zd      ews     df 
.....

So each row can have no values like index = 2, or all values like index = 3. Is there a way to efficiently rearrange my dataframe df? Thanks in advance

Scott Boston · Accepted Answer · 2017-12-20 03:51:19Z

1

One way, albeit slowly, apply, dropna, and tolist:

 df.apply(lambda x: pd.Series(x.dropna().tolist()),1)\
   .set_axis(df.columns, axis=1, inplace=False)

Output:

   IT1  IT2  IT3  IT4  IT5  IT6
0  qwe  rew  NaN  NaN  NaN  NaN
1  sdc  wer  NaN  NaN  NaN  NaN
2  NaN  NaN  NaN  NaN  NaN  NaN
3  asd  fsc   ws   zd  ews   df

answered Dec 20, 2017 at 3:51

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

2 Comments

EJ Kang Over a year ago

set_axis() got multiple values for argument 'axis' <<<< this error occurs. Any ideas?

Scott Boston Over a year ago

@Kang Yes, you must have Pandas 0.21.0+ Upgrade pandas.

James · Accepted Answer · 2017-12-20 04:00:07Z

1

You can write a custom function that sorts a rows, then replaces the index (the columns) with the columns in the original order. Simply apply it to the dataframe row-wise

def row_sort(s):
    s2 = s.sort_values()
    s2.index = s.index
    return s2

df.apply(row_sort, axis=1)
# returns:
   IT1  IT2  IT3  IT4  IT5  IT6
0  qwe  rew  NaN  NaN  NaN  NaN
1  sdc  wer  NaN  NaN  NaN  NaN
2  NaN  NaN  NaN  NaN  NaN  NaN
3  asd   df  ews  fsc   ws   zd

answered Dec 20, 2017 at 4:00

James

37k4 gold badges54 silver badges79 bronze badges

1 Comment

EJ Kang Over a year ago

It works perfect, but only one problem. I want to keep the order, but drop nan value, but your code does not keep the original order. For example, in index 3, it should be in asd fsc ws zd ews df order. Thanks!

Collectives™ on Stack Overflow

Python DataFrame: rearrange the objects and empty values [duplicate]

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related