1

I have a pandas dataframe as below. I want to rearrange columns in my dataframe based on the sequence seperately for XX_ and YY_ columns.

import numpy as np
import pandas as pd
import math
import sys
import re
data=[[np.nan,2, 5,np.nan,np.nan,1],
      [np.nan,np.nan,2,np.nan,np.nan,np.nan],
      [np.nan,3,np.nan,np.nan,np.nan,np.nan],
      [1,np.nan,np.nan,np.nan,np.nan,1],
      [np.nan,2,np.nan,np.nan,2,np.nan],
      [np.nan,np.nan,np.nan,2,np.nan,5]]
df = pd.DataFrame(data,columns=['XX_4','XX_2','XX_3','YY_4','YY_2','YY_3'])
df

My output dataframe should look like:

   XX_2  XX_3  XX_4  YY_2  YY_3  YY_4
0   2.0   5.0   NaN   NaN   1.0   NaN
1   NaN   2.0   NaN   NaN   NaN   NaN
2   3.0   NaN   NaN   NaN   NaN   NaN
3   NaN   NaN   1.0   NaN   1.0   NaN
4   2.0   NaN   NaN   2.0   NaN   NaN
5   NaN   NaN   2.0   NaN   5.0   2.0

Since this is a small dataframe, I can manually rearrange the columns. Is there any way of doing it based on _2, _3 suffix?

JohanC
  • 71,591
  • 8
  • 33
  • 66
Shanoo
  • 1,185
  • 1
  • 11
  • 38

1 Answers1

0

IIUC we can use a function based off Jeff Attwood's article on sorting alphanumeric columns written by Mark Byers :

https://stackoverflow.com/a/2669120/9375102

import re 
def sorted_nicely( l ): 
    """ Sort the given iterable in the way that humans expect.""" 
    convert = lambda text: int(text) if text.isdigit() else text 
    alphanum_key = lambda key: [ convert(c) for c in re.split('([0-9]+)', key) ] 
    return sorted(l, key = alphanum_key)

df = pd.DataFrame(data,columns=['XX_9','XX_10','XX_3','YY_9','YY_10','YY_3'])    
data = df.colums.tolist()
print(df[sorted_nicely(data)])
 XX_3  XX_9  XX_10  YY_3  YY_9  YY_10
0   5.0   NaN    2.0   1.0   NaN    NaN
1   2.0   NaN    NaN   NaN   NaN    NaN
2   NaN   NaN    3.0   NaN   NaN    NaN
3   NaN   1.0    NaN   1.0   NaN    NaN
4   NaN   NaN    2.0   NaN   NaN    2.0
5   NaN   NaN    NaN   5.0   2.0    NaN
Umar.H
  • 22,559
  • 7
  • 39
  • 74