I have a huge dataset with two specific columns for Sales Person and Manager. I want to make a new column which assigns sales person name on different basis.
So lets say that Under Manager John, I have 4 executives - A, B, C, D
I want to replace the existing sales person under John with the executives A, B, C and D in a sequence.
Here is what I want to do -
Input-
ID | SalesPerson | Sales Manager |
---|---|---|
AM12 | Oliver | Bren |
AM21 | Athreyu | John |
AM31 | Margarita | Fer |
AM41 | Jenny | Fer |
AM66 | Omar | John |
AM81 | Michael | Nati |
AM77 | Orlan | John |
AM87 | Erika | Nateran |
AM27 | Jesus | John |
AM69 | Randy | John |
Output -
ID | SalesPerson | Sales Manager | SalesPerson_new |
---|---|---|---|
AM12 | Oliver | Bren | oliver |
AM21 | Athreyu | John | A |
AM31 | Margarita | Fer | Margarita |
AM41 | Jenny | Fer | Jenny |
AM66 | Omar | John | B |
AM81 | Michael | Nati | Michael |
AM77 | Orlan | John | C |
AM87 | Erika | Nateran | Nateran |
AM27 | Jesus | John | D |
AM69 | Randy | John | A |
We can do this with cumcount
and .map
first we need to build up a dictionary that repeats ABCD
in multiple of fours.
i.e {0 : 'A', 1 : 'B', 2 : 'C', 3 : 'D', 4 : 'A'}
we can do this with a helper function and some handy modules from the itertools library.
from itertools import cycle, zip_longest, islice
from string import ascii_uppercase
import pandas as pd
import numpy as np
def repeatlist(it, count):
return islice(cycle(it), count)
mapper = dict(zip_longest(range(50), repeatlist(ascii_uppercase[:4],50)))
df['SalesPersonNew'] = np.where(
df['Sales Manager'].eq('John'),
df.groupby('Sales Manager')['SalesPerson'].cumcount().map(mapper),
df['SalesPerson'])
print(df)
ID SalesPerson Sales Manager SalesPersonNew
0 AM12 Oliver Bren Oliver
1 AM21 Athreyu John A
2 AM31 Margarita Fer Margarita
3 AM41 Jenny Fer Jenny
4 AM66 Omar John B
5 AM81 Michael Nati Michael
6 AM77 Orlan John C
7 AM87 Erika Nateran Erika
8 AM27 Jesus John D
9 AM69 Randy John A
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments