Categorize ordering column in according two columns in a python dataframe

Question

I want to enumerate the Customer-Assessor group in ascending order. Restarting every Client. Could you help me how to do it? I've tried using the groupby function, however, I'm not getting it to be restarted for each client.

Here is an example of the dataframe:

Date	Customer	Advisor
01/01/2023	A	AA
02/01/2023	A	BB
03/01/2023	A	CC
04/01/2023	A	AA
01/01/2023	B	AA
02/01/2023	B	BB
03/01/2023	B	AA
04/01/2023	B	CC

Follow the expected result: The date is important, but the dataframe is already sorted by date.

Date	Customer	Advisor	Customer-Advisor
01/01/2023	A	AA	1
02/01/2023	A	BB	2
03/01/2023	A	CC	3
04/01/2023	A	AA	1
01/01/2023	B	AA	1
02/01/2023	B	BB	2
03/01/2023	B	AA	1
04/01/2023	B	CC	3

Thank you

score 1 · Answer 1 · answered Mar 21 '23 at 15:02

1

With pd.Series.factorize to encode Advisor values as categorical variables:

df['Customer-Advisor'] = np.ravel([g['Advisor'].factorize()[0] + 1 
                                   for _, g in df.groupby('Customer', sort=False)])

          Date Customer Advisor  Customer-Advisor
0  01/01/2023        A       AA                 1
1  02/01/2023        A       BB                 2
2  03/01/2023        A       CC                 3
3  04/01/2023        A       AA                 1
4  01/01/2023        B       AA                 1
5  02/01/2023        B       BB                 2
6  03/01/2023        B       AA                 1
7  04/01/2023        B       CC                 3

answered Mar 21 '23 at 15:02

RomanPerekhrest

88,541
4
65
105

You are right, it is correct. – jezrael Mar 21 '23 at 15:32
1

Or `df["Customer-Advisor"] = df.groupby(["Customer"])['Advisor'].transform(lambda g: g.factorize()[0]) + 1` – jezrael Mar 21 '23 at 15:33
@jezrael answer it's working, but RomanPerekhrest answer didn't works. How do I choose jezrael answer as a valid solution? Thank you guys! – haraujo Mar 21 '23 at 15:56
@RomanPerekhrest for your answer I got ValueError: Length of values does not match length of index – haraujo Mar 21 '23 at 15:58
@haraujo, It should work. Make sure you're not somehow changing the input df in the middle or mixing up different dataframes – RomanPerekhrest Mar 21 '23 at 16:22
@jezrael, I saw your alternative with `transform`, but it runs slower – RomanPerekhrest Mar 21 '23 at 16:49

score 0 · Answer 2 · edited Mar 21 '23 at 15:27

0

It looks like you want a unique ID for each Advisor.

df["group_id"] = df.groupby(["Advisor"]).ngroup()

This starts the IDs at 0, but you can just add one to the column if you really care about that.

If you wanted to generate a unique ID for multiple groups (e.g. Customer AND Advisor), simply add to the list in groupby.

df["group_id"] = df.groupby(["Customer", "Advisor"]).ngroup()

edited Mar 21 '23 at 15:27

jezrael

822,522
95
1,334
1,252

answered Mar 21 '23 at 14:47

stressed

328
2
7

Categorize ordering column in according two columns in a python dataframe

2 Answers2