I have the below 2 dataframes
df_A
salesperson | Limit |
---|---|
Aa | 22 |
Bb | 13 |
Cc | 12 |
Dd | 30 |
Ee | 24 |
Ff | 14 |
df_B (Pls Assume this dataframe has 100 rows of play store shops)
play stores | Assigned |
---|---|
1 | |
2 | |
......... | |
100 |
I need to assign the sales persons to the play stores in a round robin method (ABCDE , ABCDE ...so on)
Conditions
To the extend possible, it should be equally distributed, i.e in the above example, first Dd should be assigned 6 stores, when Dd limit reaches 24, Dd and Ee should be assigned 2 in round robin manner until their limit reaches 22, then Dd, Ee and Aa should be assigned in round robin manner until their limit reaches 14, then Ff will be included....so on
Of course, the assignment needs to stop once the corresponding limit (in column 2 of dataframe_1) for the salesguy is reached.
(Really sorry about the complications)
I tried the below ...
df_B['Assigned'] = (df_A['salesperson'].repeat(df1['Limit'])
.sort_values(key=lambda s: s.groupby(s).cumcount(),
kind='stable', ignore_index=True)
)
But in this I am unable to add a way to check the equal distribution bit.
please help with a straight forward way to do the following :- How do I first determine the salesperson with the largest limit, then assign him to x number of stores to reach the limit of the second guy, then assign them and y number of stores to reach the third salesperson ....until either I run out of stores or I run out of limit for the salespersons.
CodePudding user response:
Step 0: Creating the dataframes that you have:
df_A = pd.DataFrame(data={'salesperson':['Aa','Bb', 'Cc','Dd','Ee', 'Ff'], 'Limit':[22,13,12,30,24,14]})
df_B = pd.DataFrame(data={'play stores':list(range(1,100))})
Step 1: Get all the assignments by keeping an equal distribution. Find salesperson with largest limit and assign him as many times as the second largest limit, and then update his new limit. Re-do the procedure as far as we have available_assignments (which also get updated at each iteration)
all_assignments = []
available_assignments = df_A['Limit'].sum()
while available_assignments > 0:
largest_limit_salesperson = df_A.iloc[df_A['Limit'].idxmax()]['salesperson']
largest_limit = df_A['Limit'].nlargest(2).iloc[0]
second_largest_limit = df_A['Limit'].nlargest(2).iloc[1]
allowed_assignments = largest_limit-second_largest_limit
if allowed_assignments == 0:
allowed_assignments = 1
new_limit = largest_limit - allowed_assignments
df_A.loc[df_A['Limit'].idxmax(), 'Limit'] = new_limit
all_assignments.extend([largest_limit_salesperson for i in range(allowed_assignments)])
available_assignments = available_assignments - allowed_assignments
Step 2: pick as many assignments as the available stores
df_B['Assigned'] = all_assignments[:df_B.shape[0]]