re-ordering a pandas dataframe to match column elements in another dataframe

Question

I'd like to reorder one data frame to match the order of two columns in another data frame.

This approach works -- but surely someone can suggest a more efficient approach?

import numpy as np
import pandas as pd
df = pd.DataFrame({'X_intent' : [1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8],
                   'Y_intent' :np.ravel([np.repeat(1,8),np.repeat(2,8), np.repeat(3,8)]),
                   'Values' : [1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8]})

p = pd.DataFrame( {'OrderX': [8,7,6,5,4,3,2,1,8,7,6,5,4,3,2,1,8,7,6,5,4,3,2,1], 'OrderY': np.ravel([np.repeat(1,8),np.repeat(2,8), np.repeat(3,8)])})

p['indx'] =  p.OrderX.astype(str)+' '+p.OrderY.astype(str)
df['indx'] =  df.X_intent.astype(str)+' '+df.Y_intent.astype(str)

res = list()
for i in p["indx"]:
    res.append(np.where(df['indx'] == i))

res = [np.asscalar(item[0]) for sublist in l for item in res]

df.iloc[res]

Thanks for your help.

jezrael · Accepted Answer · 2020-04-16 05:58:58Z

I think here is possible use DataFrame.merge, if all values in both DataFrame is possible use default inner join, rename is used for avoid duplication columns with same values:

c = {'OrderX':'X_intent','OrderY':'Y_intent'}
df = p.rename(columns=c).merge(df, on=['X_intent','Y_intent'])
print (df)

    X_intent  Y_intent  Values
0          8         1       8
1          7         1       7
2          6         1       6
3          5         1       5
4          4         1       4
5          3         1       3
6          2         1       2
7          1         1       1
8          8         2       8
9          7         2       7
10         6         2       6
11         5         2       5
12         4         2       4
13         3         2       3
14         2         2       2
15         1         2       1
16         8         3       8
17         7         3       7
18         6         3       6
19         5         3       5
20         4         3       4
21         3         3       3
22         2         3       2
23         1         3       1

df = p.merge(df, left_on=['OrderX','OrderY'], right_on=['X_intent','Y_intent'])
print (df)
    OrderX  OrderY  X_intent  Y_intent  Values
0        8       1         8         1       8
1        7       1         7         1       7
2        6       1         6         1       6
3        5       1         5         1       5
4        4       1         4         1       4
5        3       1         3         1       3
6        2       1         2         1       2
7        1       1         1         1       1
8        8       2         8         2       8
9        7       2         7         2       7
10       6       2         6         2       6
11       5       2         5         2       5
12       4       2         4         2       4
13       3       2         3         2       3
14       2       2         2         2       2
15       1       2         1         2       1
16       8       3         8         3       8
17       7       3         7         3       7
18       6       3         6         3       6
19       5       3         5         3       5
20       4       3         4         3       4
21       3       3         3         3       3
22       2       3         2         3       2
23       1       3         1         3       1

Collectives™ on Stack Overflow

re-ordering a pandas dataframe to match column elements in another dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related