CODEWITHSUNDEEP
pythonpandasdataframenumpysorting
d_frEak
d_frEak

Reputation: 470

Sorting one group of columns according to another group of columns

This question is similar to this, the last time I thought it will be that simple but it seems not (thank you @anon01 and @Ch3steR who answered my previous question there)

so here is my new dataframe

matrix = [(1, 3, 2, "1a", "3a", "2a"),
          (6, 5, 4, "6a", "5a", "4a"),
          (8, 7, 9, "8a", "7a", "9a"),
          ]
df = pd.DataFrame(matrix, index=list('abc'), columns=["price1","price2","price3","product1","product2","product3"])

    price1  price2  price3  product1    product2    product3
a   1         3       2       1a           3a         2a
b   6         5       4       6a           5a         4a
c   8         7       9       8a           7a         9a

I need to sort by price within each row but price and product is a pairs so if the price move to price1 then the product also need to move to product1 because they are pairs Here is the output will be

        price1  price2  price3  product1    product2    product3
a         1       2        3       1a         2a          3a
b         4       5        6       4a         5a          6a
c         7       8        9       7a         8a          9a

from the last question, I tried the suggested solution using np.sort it can work to sort the price but if I have another column it is not working. I tried to rematching the product with the price but I think it will cost more so I still using my previous brute force solution as using swapping from this link

df.loc[df['price1']>df['price2'],['price1','price2','product1','product2']] = df.loc[df['price1']>df['price2'],['price2','price1','product2','product1']].values
df.loc[df['price1']>df['price3'],['price1','price3','product1','product3']] = df.loc[df['price1']>df['price3'],['price3','price1','product3','product1']].values
df.loc[df['price2']>df['price3'],['price2','price3','product2','product3']] = df.loc[df['price2']>df['price3'],['price3','price2','product3','product2']].values

The problem is I have more pairs than 3 if someone has an idea for this matter, it will be very helpful, thank you

Upvotes: 1

Views: 75

Answers (1)

cs95
cs95

Reputation: 402483

We can make use of numpy.sort for "price", and numpy.argsort for product. This is all vectorized by numpy.

# Gets all "price" columns
price = df.filter(like='price')
# Gets all "product" columns
product = df.filter(like='product')

# Sorts "price" columns row-wise and assigns an array back
df[price.columns] = np.sort(price.to_numpy(), axis=1)

# Builds indices for re-organizing "product" based on sorted "price" 
ix = np.arange(product.shape[1])[:,None]
iy = np.argsort(price.to_numpy(), axis=1)
# Re-arranges "product" array and assigns it back  
df[product.columns] = product.to_numpy()[ix, iy]

df
   price1  price2  price3 product1 product2 product3
a       1       2       3       1a       2a       3a
b       4       5       6       4a       5a       6a
c       7       8       9       7a       8a       9a

Upvotes: 4

Related Questions