How do I fill a string column using a set in Pandas dataframe?

rajnish chauhan Published at Dev

rajnish chauhan

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

Manakin

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.