Conditional sorting, using groupby on columns in pandas

Viewed 37

Consider I have following df.

first_primary='N8910-011B0BB' 

dct = {'Store':('19387','19387','19387','19387','19387'),
       'SELF-NBR':('INC1539641','INC1514967','INC1518545','INC1518666','INC1514983'),
       'PARTS_NEW':('N8910-011B0BB','N8910-011B0BB','Q24-FR128043','Q24-FR128043','Q24-FR128042'),
       'date_diff':(1,7,1,1,0),
       'PLACEDAY' :('2020-05-11 18:58:35','2020-05-18 05:25:39','2020-05-12 04:42:29','2020-05-12 02:22:22','2020-05-11 15:20:32')
    }

df = pd.DataFrame(dct)
df['PLACEDAY']= pd.to_datetime(df['PLACEDAY'])

print(df)

    Store   SELF-NBR    PARTS_NEW        date_diff  PLACEDAY
0   19387   INC1539641  N8910-011B0BB       1      2020-05-11 18:58:35
1   19387   INC1514967  N8910-011B0BB       7      2020-05-18 05:25:39
2   19387   INC1518545  Q24-FR128043        1      2020-05-12 04:42:29
3   19387   INC1518666  Q24-FR128043        1      2020-05-12 02:22:22
4   19387   INC1514983  Q24-FR128042        0      2020-05-11 15:20:32

Above dataframe df is achived from for loop and varies for each unique store_id.
Here data is for store = 19387, first_primary also varies for each store but will be idenitified by first_primary as decribed in the code

I want to conditional sort df only if first_primary is repeated multiple times, sort (Descending) the dataframe by PLACEDAY, ensuring first_primary always stays on the top.

Expected Dataframe

    Store   SELF-NBR    PARTS_NEW        date_diff  PLACEDAY
0   19387   INC1514967  N8910-011B0BB       7      2020-05-18 05:25:39
1   19387   INC1539641  N8910-011B0BB       1      2020-05-11 18:58:35
2   19387   INC1518545  Q24-FR128043        1      2020-05-12 04:42:29
3   19387   INC1518666  Q24-FR128043        1      2020-05-12 02:22:22
4   19387   INC1514983  Q24-FR128042        0      2020-05-11 15:20:32

Summary:

  1. Incase of repeatative first_primary in PARTS_NEW, first_primary with latest time-stamp should be on the top with respective cols('SELF-NBR', 'PARTS_NEW', etc..)

  2. If first_primary in PARTS_NEW, is counted only one time, ensure first_primary is on the top if the df

Let me know if any more query is required.

1 Answers

Try sort_values + argsort with isin

df = df.sort_values('PLACEDAY',ascending=False)
first_primary='N8910-011B0BB' 
df = df.iloc[(~df.PARTS_NEW.isin([first_primary])).argsort()]
df
   Store    SELF-NBR      PARTS_NEW  date_diff            PLACEDAY
1  19387  INC1514967  N8910-011B0BB          7 2020-05-18 05:25:39
0  19387  INC1539641  N8910-011B0BB          1 2020-05-11 18:58:35
2  19387  INC1518545   Q24-FR128043          1 2020-05-12 04:42:29
3  19387  INC1518666   Q24-FR128043          1 2020-05-12 02:22:22
4  19387  INC1514983   Q24-FR128042          0 2020-05-11 15:20:32
Related