I have a df that contains numerous Places at recurring time periods. These Places are beginning and finishing in random fashion. For each time period, I want to assign each unique place to a Group. The central rules in doing this are:
1) Each Group can hold no more than 3 unique Places at any one time
2) Unique Places should be evenly distributed across each Group
I've taken a very small subsection of the df. There are 7 unique values (but no more than 5 occuring at any one time) and 2 Groups to choose from. But in practice, the df could contain up to 50 unique values in total that finish and end and varying time periods that will be distributed across a maximum of 6 Groups.
To understand how many Places are currently occuring I've included a Total, which is based on if the Place appears again.
The df contains all available Groups for each unique Place at each Period. Places Golf and Club will finish but we assume all other places are continued as they appear later in the df.
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'],
})
The main issue that's causing me trouble is Places appear/exist dynamically. In that, they finish and new ones begin in a random fashion. So assigning and distributing the current unique Places needs to account for this concept
Attempt:
def AssignPlace(df):
uniquePlaces = df['Place'].unique()
G3 = dict(zip(uniquePlaces, np.arange(len(uniquePlaces)) // 3 + 1))
df['Assigned Group'] = df['Place'].map(G3)
return df
df = df.groupby('Available Group', sort=False).apply(AssignPlace)
df = df.drop_duplicates(subset = ['Period','Place'])
Out:
Period Place Total Available Group Assigned Group
0 1 CLUB 1 1 1
1 2 CLUB 1 2 1
3 2 HOME 2 2 1
5 2 AWAY 3 2 1
7 3 WORK 4 2 2
9 3 AWAY 4 1 1
11 3 GOLF 5 1 2 #GOLF FINISHES SO 4 OCCURING FROM NEXT ROW
13 4 CLUB 4 2 1 #CLUB FINISHES BUT POOL STARTS SO STILL 4 OCCURING FROM NEXT ROW
15 4 POOL 4 2 2
17 4 HOME 4 2 1
19 5 WORK 4 2 2
21 5 AWAY 4 1 1
23 5 POOL 4 1 2
25 6 TENNIS 5 2 3 #Signifies issue
The last row display the issue. The function works fine in that it assigns the 7th unique value but it doesn't account for current unique values. As Club and Golf finish, they are only 5 current unqiue values and 2 available groups. But it's returning Group 3
Intended Output, TENNIS Assigned Group is now 1, instead of 3:
Period Place Total Available Group Assigned Group
0 1 CLUB 1 1 1
1 2 CLUB 1 2 1
3 2 HOME 2 2 1
5 2 AWAY 3 2 1
7 3 WORK 4 2 2
9 3 AWAY 4 1 1
11 3 GOLF 5 1 2
13 4 CLUB 4 2 1
15 4 POOL 4 2 2
17 4 HOME 4 2 1
19 5 WORK 4 2 2
21 5 AWAY 4 1 1
23 5 POOL 4 1 2
25 6 TENNIS 5 2 1
from Allocate values from different options - pandas
No comments:
Post a Comment