Order the rows of one dataframe (column with duplicates) based on a column of another dataframe in Python

user_14353779 0
Viewed 132

I have two dataframes df1 and df2. I want to order df1 based on a column SET (which has duplicates for SET column but not other columns) in the order of column SETf column dataframe df2 .

df1 :-

SET    Date      cust_ID  TYPE  amt     total flag LEVEL
A   6/10/2019   113252981   R   1317    16237   Y    3
C   6/18/2019   112010871   R   4582    12455   Y    2
B   6/22/2019   204671333   S   2364    24311   Y    1
B   6/22/2019   202770598   S   4721    10582   Y    1
B   6/22/2019   202706466   S   1904    25343   N    2
B   6/22/2019   202669668   S   3713    25166   N    1
B   6/22/2019   202754932   T   4792    16888   Y    2
D   6/7/2019    120304631   P   4968    25297   Y    2
D   6/7/2019    112353651   P   1622    14384   Y    3
D   6/7/2019    112349221   P   4721    15878   Y    3
D   6/8/2019    111197161   P   4490    25489   N    2
E   6/8/2019    137049981   Q   4409    10842   Y    2
A   6/8/2019    137281821   Q   1060    24085   Y    2
C   6/8/2019    136390501   Q   1649    13626   N    2
C   6/9/2019    136326431   Q   3822    13599   N    2

df2 :-

  s_no  SETf
    1   B
    2   D
    3   C
    4   A
    5   E

I want to sort rows of df1 based on the same order of SETf of df2.

What I tried :-

df1 =df1.set_index('SET')

df1= df1.reindex(df2.index['SETf'])

df1= df1.reset_index()

It does not work as I have duplicates in SET in df1.In addition to doing that I want to order the rows based on LEVEL ascending within each SET and flag

pandas dataframe python-3.x
1 Answers

In your second dataframe create if your s_no column is unique and ascending [1,2,3,4,etc.], then merge the two dataframes and sort by the s_no column you merged in and then drop it:

df1 = pd.merge(df1, df2[['SETf', 's_no']].rename({'SETf':'SET'}, axis=1), how='left',on='SET')
df1 = df1.sort_values(['s_no', 'flag', 'LEVEL']).drop('s_no', axis=1)
df1
Out[490]: 
   SET       Date    cust_ID TYPE   amt  total flag  LEVEL
5    B  6/22/2019  202669668    S  3713  25166    N      1
4    B  6/22/2019  202706466    S  1904  25343    N      2
2    B  6/22/2019  204671333    S  2364  24311    Y      1
3    B  6/22/2019  202770598    S  4721  10582    Y      1
6    B  6/22/2019  202754932    T  4792  16888    Y      2
10   D   6/8/2019  111197161    P  4490  25489    N      2
7    D   6/7/2019  120304631    P  4968  25297    Y      2
8    D   6/7/2019  112353651    P  1622  14384    Y      3
9    D   6/7/2019  112349221    P  4721  15878    Y      3
13   C   6/8/2019  136390501    Q  1649  13626    N      2
14   C   6/9/2019  136326431    Q  3822  13599    N      2
1    C  6/18/2019  112010871    R  4582  12455    Y      2
12   A   6/8/2019  137281821    Q  1060  24085    Y      2
0    A  6/10/2019  113252981    R  1317  16237    Y      3
11   E   6/8/2019  137049981    Q  4409  10842    Y      2
user_63667700
Related