I am trying to assign unique values to a specific allocation or group. What makes it tricky is these unique values are dynamically starting and finishing. So the groups will be holding previously seen values and taking new unique values at various time periods. In regards to the df, unique values are located in Place and the groups to pick from are found in Available Group for each period of time Period.
The broad guidelines I'm trying to adhere to is:
1) Each Group can hold no more than 3 unique Places at any one time
2) Current unique Places should be evenly distributed across each Group
3) Once Places get assigned to Group, keep until Group finishes. Unless Group becomes NA or uneven allocation of meetings
To understand how many Places are currently occuring I've included Total, which is based on if the Place value appears again. I'm meeting my first two guidelines and partially the third. When a Place is assigned to a Group it is held at the same Group until the Place is finished (does not appear again).
However, I'm not referencing Available Group to understand if that Group is available. When the Group becomes unavailable I'd like to rearrange those Places across the other Available Group. Using the df below, the assigning of places works well the unique places increases. But as soon as they start to finish and Group 2 becomes unavailable those places arent re-assigned to group 1. At this point there are only 3 places occurring.
df = pd.DataFrame({
'Period' : [1,2,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,4,5,5,5,5,5,5,6,6],
'Place' : ['CLUB','CLUB','CLUB','HOME','HOME','AWAY','AWAY','WORK','WORK','AWAY','AWAY','GOLF','GOLF','CLUB','CLUB','POOL','POOL','HOME','HOME','WORK','WORK','AWAY','AWAY','POOL','POOL','TENNIS','TENNIS'],
'Total' : [1,1,1,2,2,3,3,4,4,4,4,5,5,4,4,4,4,4,4,4,4,4,4,4,4,5,5],
'Available Group' : ['1','2','1','2','1','2','1','2','1','1','2','1','2','2','1','2','1','2','1','2','1','1','2','1','2','2','1'],
})
Attempt:
# df to store all unique places
uniquePlaces = pd.DataFrame(df["Place"].unique(), columns=["Place"])
# Start stores index of df where the place appears 1st
uniquePlaces["Start"] = -1
# End stores index of df where the place appears last
uniquePlaces["End"] = -1
def assign_place_label(group):
''' Create a label column that calculates the amount of unique meetings
throughout the racing schedule '''
label = uniquePlaces[uniquePlaces["Place"] == group.name].index[0]
group["Place Label"] = label
uniquePlaces.loc[label, "Start"] = group.index.min()
uniquePlaces.loc[label, "End"] = group.index.max()
return group
# Based on Start and End of each place, assign index to each place.
# when 'freed' the index is reused to new place appearing after that
def Assign_Meetings_group(up):
up["Index"] = 0
up["Freed"] = False
max_ind=0
free_indx = []
for i in range(len(up)):
ind_freed = up.index[(up["End"]<up.iloc[i]["Start"]) & (~up["Freed"])]
free = list(up.loc[ind_freed, "Index"])
free_indx += free
up.loc[ind_freed, "Freed"] = True
if len(free_indx)>0:
m = min(free_indx)
up.loc[i, "Index"] = m
free_indx.remove(m)
else:
up.loc[i, "Index"] = max_ind
max_ind+=1
up["Group"] = up["Index"]//3+1
return up
df2 = df.groupby("Place").apply(assign_place_label)
uniquePlaces = Assign_Meetings_group(uniquePlaces)
df3 = df2[df2['Period']!=0].drop_duplicates(subset = ['Period','Place'])
result = df3.merge(uniquePlaces[["Group"]], how="left", left_on="Place Label", right_index=True, sort=False)
Out:
Period Place Total Available Group Place Label Group
0 1 CLUB 1 1 0 1
1 2 CLUB 1 1 0 1
3 2 HOME 2 1 1 1
5 2 AWAY 3 1 2 1
7 3 WORK 4 1 3 2
9 3 AWAY 4 1 2 1
11 3 GOLF 5 1 4 2
13 3 HOME 5 1 1 1
15 4 CLUB 4 1 0 1
17 4 AWAY 3 1 2 1
19 4 POOL 3 1 5 1
21 5 WORK 3 1 3 2
23 5 POOL 2 1 5 1
25 6 GOLF 1 1 4 2
Intended Output:
Period Place Total Available Group Place Label Group
0 1 CLUB 1 1 0 1
1 2 CLUB 1 1 0 1
3 2 HOME 2 1 1 1
5 2 AWAY 3 1 2 1
7 3 WORK 4 1 3 2
9 3 AWAY 4 1 2 1
11 3 GOLF 5 1 4 2
13 3 HOME 5 1 1 1
15 4 CLUB 4 1 0 1
17 4 AWAY 3 1 2 1
19 4 POOL 3 1 5 1
21 5 WORK 3 1 3 1
23 5 POOL 2 1 5 1
25 6 GOLF 1 1 4 1