Sunday, 27 October 2019

Dynamic assignment of unique values - Python

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 


from Dynamic assignment of unique values - Python

No comments:

Post a Comment