CODEWITHSUNDEEP
pythonpandas
Flora
Flora

Reputation: 285

Rearrange columns by name and number in Pandas dataframe

I have a dataframe with columns like this for example (as 1 being any values to simplify things here:

         ID    App R1    Pear R1    Oro R1   App R2   Pear R2    Oro R2    App R3   Pear R3   Oro R3
0         1         1         1         1         1         1         1         1         1        1
1         1         1         1         1         1         1         1         1         1        1

I would like to rearrange the columns like this:

         ID    App R1    App R2    App R3   Pear R1   Pear R2    Pear R3    Oro R1   Oro R2   Oro R3
0         1         1         1         1         1         1         1         1         1        1
1         1         1         1         1         1         1         1         1         1        1

I have tried:

df.columns = [['ID'] + ['App R{}'.format(r+1)] + ['Pear R{}'.format(r+1)] + ['Matched fragment ions R{}'.format(r+1)] + ['Oro R{}'.format(r+1)] for r in range(len(files))]

n.b: The number r of App, Pear and Oro will depend on the number r of csv I start with

However, no success so far.

Thank you if you can help me solve this problem.

Upvotes: 1

Views: 1946

Answers (2)

Zero
Zero

Reputation: 76937

Use custom sorted with lookup key

In [4291]: look = {'I':0, 'A':1, 'P':2, 'O':3}  # order for letters 

In [4292]: sorted(df.columns, key=lambda x: look.get(x[0], ''))  # first letter key
Out[4292]:
['ID',
 'App R1',
 'App R2',
 'App R3',
 'Pear R1',
 'Pear R2',
 'Pear R3',
 'Oro R1',
 'Oro R2',
 'Oro R3']

In [4293]: df[sorted(df.columns, key=lambda x: look.get(x[0], ''))]
Out[4293]:
   ID  App R1  App R2  App R3  Pear R1  Pear R2  Pear R3  Oro R1  Oro R2  \
0   1       1       1       1        1        1        1       1       1
1   1       1       1       1        1        1        1       1       1

   Oro R3
0       1
1       1

Upvotes: 4

jezrael
jezrael

Reputation: 863031

I think MultiIndex should help:

#set all not R columns to index
df = df.set_index(['ID'])
#create multiindex
df.columns = df.columns.str.split(expand=True)
#custom reordering
df = df.reindex(columns=['App','Pear','Oro'], level=0)
print (df)
   App       Pear       Oro      
    R1 R2 R3   R1 R2 R3  R1 R2 R3
ID                               
1    1  1  1    1  1  1   1  1  1
1    1  1  1    1  1  1   1  1  1

df.columns = df.columns.map(' '.join)
print (df)
    App R1  App R2  App R3  Pear R1  Pear R2  Pear R3  Oro R1  Oro R2  Oro R3
ID                                                                           
1        1       1       1        1        1        1       1       1       1
1        1       1       1        1        1        1       1       1       1

Upvotes: 1

Related Questions