Column Pair-wise reorganization in Pandas

user_8352482 0
Viewed 56

I have my data like this 

df = pd.DataFrame([[2,1,3,3],[2,3,2,4],[4,1,3,2]],columns=['A1','A2','B1','B2'])

df
   A1   A2   B1   B2
0  A:2  A:1  B:3  B:3
1  A:2  A:3  B:2  B:4
2  A:4  A:1  B:3  B:2

the value in A1, A2 is one pair, same with B1 and B2.

Now I want to reorganize each pair so they are in alphabet order:

df
   A1   A2   B1   B2
0  A:1  A:2  B:3  B:3
1  A:2  A:3  B:2  B:4
2  A:1  A:4  B:2  B:3

I can do this with a for loop for each pair, sort, and then reparse it into the pandas frame:

for index, row_ in df.iterrows():
    for pair_ in range(int(len(row_)/2)):
        print(index, pair_)
        pair = row_[pair_*2:(pair_*2+2)]
        df.iloc[index, pair_*2:(pair_*2+2)] = pair.sort_values()

but this seems to be very inefficient.

Please suggest me better approach on this, thank you

pandas python
3 Answers

I would use np.sort:

# replace with your number
num_col_in_group = 2
pd.DataFrame(np.sort(df.values.reshape(len(df), -1, num_col_in_group), 
                     axis=-1).reshape(len(df),-1), 
             columns=df.columns)

Output:

    A1   A2   B1   B2
0  A:1  A:2  B:3  B:3
1  A:2  A:3  B:2  B:4
2  A:1  A:4  B:2  B:3
user_42384080

You can simply take the min and max of 2 columns and re-assign them with that:

pairs = (('A1', 'A2'), ('B1', 'B2'))  # or any other way to define the pairs

for c1, c2 in pairs:
    df[c1], df[c2] = np.minimum(df[c1], df[c2]), np.maximum(df[c1], df[c2])
user_35452730

Quang's solution is pretty neat. Here is a bit of experiment with wide_to_long:

pair_names = ['A','B']
m = pd.wide_to_long(df.reset_index(),pair_names,'index','v').sort_values(by=pair_names)
idx = m.groupby(m.index.get_level_values('index')).cumcount().add(1)

out = m.droplevel('v').set_index(idx,append=True).sort_index('index').unstack()
out.columns = out.columns.map('{0[0]}{0[1]}'.format)

print(out)

       A1  A2  B1  B2
index                
0       1   2   5   3
1       2   3   2   4
2       1   4   2   3
user_98406370
Related