0

I have a dataframe df

import pandas as pd
df = pd.DataFrame({"Cust": ['cst1', 'cst1', 'cst1', 'cst2', 'cst2', 'cst2', 'cst3', 'cst3', 'cst3', 'cst4', 'cst4', 'cst4'],
                   "act": ['ac1', 'ac2', 'ac3','ac1', 'ac2', 'ac3','ac1', 'ac2', 'ac3','ac1', 'ac2', 'ac3' ],
                   "rating": ['a', 'b', 'c', 'b', 'b', 'c', 'h', 'i', 'i', 'c', 'c', 'a']})

df_priority = pd.DataFrame({"rating":['a','b', 'c', 'd', 'e', 'f','g','h','i','j','k','l','m','n','o','p','q','r','s'],
                            "priority":[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]})

and another dataframe with priority of rating

my df table looks like:

    Cust  act rating
0   cst1  ac1      a
1   cst1  ac2      b
2   cst1  ac3      c
3   cst2  ac1      b
4   cst2  ac2      b
5   cst2  ac3      c
6   cst3  ac1      h
7   cst3  ac2      i
8   cst3  ac3      i
9   cst4  ac1      c
10  cst4  ac2      c
11  cst4  ac3      a

and my df_priority table looks like:

   rating  priority
0       a         1
1       b         2
2       c         3
3       d         4
4       e         5
5       f         6
6       g         7
7       h         8
8       i         9
9       j        10
10      k        11
11      l        12
12      m        13
13      n        14
14      o        15
15      p        16
16      q        17
17      r        18
18      s        19

I need to check and replace the rating value in the df table for each cust with the maximum priority rating for that cust.

For example, for cust = cst1, I should have rating as a for all the three records as priority of a is more than b and c. Similarly it should go for each cust and then should check into the pririty table and update accordingly.

My expected output is:

    Cust  act rating
0   cst1  ac1      a
1   cst1  ac2      a
2   cst1  ac3      a
3   cst2  ac1      b
4   cst2  ac2      b
5   cst2  ac3      b
6   cst3  ac1      h
7   cst3  ac2      h
8   cst3  ac3      h
9   cst4  ac1      a
10  cst4  ac2      a
11  cst4  ac3      a

How can I do this in Pandas ?

Archit
  • 542
  • 1
  • 4
  • 15

2 Answers2

1

We can do map with transform idxmim then assign it back with reindex

df['new']=df.rating.map(dict(zip(df_priority.rating,df_priority.priority)))
df.groupby('Cust').new.transform('idxmin')
0      0
1      0
2      0
3      3
4      3
5      3
6      6
7      6
8      6
9     11
10    11
11    11
Name: new, dtype: int64

df['newcol'] = df.rating.reindex(df.groupby('Cust').new.transform('idxmin')).tolist()
df
    Cust  act rating  new newcol
0   cst1  ac1      a    1      a
1   cst1  ac2      b    2      a
2   cst1  ac3      c    3      a
3   cst2  ac1      b    2      b
4   cst2  ac2      b    2      b
5   cst2  ac3      c    3      b
6   cst3  ac1      h    8      h
7   cst3  ac2      i    9      h
8   cst3  ac3      i    9      h
9   cst4  ac1      c    3      a
10  cst4  ac2      c    3      a
11  cst4  ac3      a    1      a
BENY
  • 317,841
  • 20
  • 164
  • 234
1

Let's try mapping the rating to its priority, then groupby with idxmin to find the top priority ones, and finally assign back:

idx=(df['rating'].map(df_priority.set_index('rating')['priority'])
   .groupby(df['Cust']).transform('idxmin')
)
df['rating'] = df.loc[idx,'rating'].values

Output:

    Cust  act rating
0   cst1  ac1      a
1   cst1  ac2      a
2   cst1  ac3      a
3   cst2  ac1      b
4   cst2  ac2      b
5   cst2  ac3      b
6   cst3  ac1      h
7   cst3  ac2      h
8   cst3  ac3      h
9   cst4  ac1      a
10  cst4  ac2      a
11  cst4  ac3      a
Quang Hoang
  • 146,074
  • 10
  • 56
  • 74