import pandas as pd
data1 = {
'N_Id': [0,1,2,2,2,2,4,5,5,5,10,10,11,11,13,13,13,13],
'N_Name': ['Destiny','N_Area','Pstation','Pstation','Pstation','Pstation','A_Area','T_depot','T_depot','T_depot','B_colony','B_colony','c_colony','c_colony','Z_colony','Z_colony','Z_colony','Z_colony'],
'Geocode': ['9.994798,0.206728','2.994798,0.206728','2.989385,0.201941','2.989385,0.201941','2.989385,0.201941','2.989385,0.201941','3.006515,0.247101','3.000568,0.256468','3.000568,0.256468','3.000568,0.256468','3.049031,0.167278','3.049031,0.167278','4.049031,0.167278','4.049031,0.167278','5.2,0.7','5.2,0.7','5.2,0.7','5.2,0.7'],
'pcode': ['None','M023','L123','M0232','L1234','K0324','M0137','M01368','M01369','K07249','M01375','F04509','F04609','F04610','F1','F2','F3','F4']
}
df1 = pd.DataFrame.from_dict(data1)
df1
Out[45]:
N_Id N_Name Geocode pcode
0 0 Destiny 9.994798,0.206728 None
1 1 N_Area 2.994798,0.206728 M023
2 2 Pstation 2.989385,0.201941 L123
3 2 Pstation 2.989385,0.201941 M0232
4 2 Pstation 2.989385,0.201941 L1234
5 2 Pstation 2.989385,0.201941 K0324
6 4 A_Area 3.006515,0.247101 M0137
7 5 T_depot 3.000568,0.256468 M01368
8 5 T_depot 3.000568,0.256468 M01369
9 5 T_depot 3.000568,0.256468 K07249
10 10 B_colony 3.049031,0.167278 M01375
11 10 B_colony 3.049031,0.167278 F04509
12 11 c_colony 4.049031,0.167278 F04609
13 11 c_colony 4.049031,0.167278 F04610
14 13 Z_colony 5.2,0.7 F1
15 13 Z_colony 5.2,0.7 F2
16 13 Z_colony 5.2,0.7 F3
17 13 Z_colony 5.2,0.7 F4
data2= {
'BusID': ['V1','V1','V1','V4','V4','V4','V5','V5','V5','V0','v100'],
'Tcap': [4,4,4,8,8,8,12,12,12,12,8],
'Terminal_Load':[1,2,1,2,1,1,2,1,1,0,4],
'N_Id': [1,2,5,2,5,10,11,4,10,0,13],
'N_Name': ['N_Area','Pstation','T_depot','Pstation','T_depot','B_colony','c_colony','A_Area','B_colony','Destiny','Z_colony'],
}
df2 = pd.DataFrame.from_dict(data2)
df2
Out[46]:
BusID Tcap Terminal_Load N_Id N_Name
0 V1 4 1 1 N_Area
1 V1 4 2 2 Pstation
2 V1 4 1 5 T_depot
3 V4 8 2 2 Pstation
4 V4 8 1 5 T_depot
5 V4 8 1 10 B_colony
6 V5 12 2 11 c_colony
7 V5 12 1 4 A_Area
8 V5 12 1 10 B_colony
9 V0 12 0 0 Destiny
10 v100 8 4 13 Z_colony
data3 = {
'BusID': ['V1','V1','V1','V4','V4','V4','V5','V5','V5','V0','v100'],
'Tcap': [4,4,4,8,8,8,12,12,12,12,8],
'Terminal_Load':[1,2,1,2,1,1,2,1,1,0,4],
'N_Id': [1,2,5,2,5,10,11,4,10,0,13],
'N_Name': ['N_Area','Pstation','T_depot','Pstation','T_depot','B_colony','c_colony','A_Area','B_colony','Destiny','Z_colony'],
'Pcode': ['M023','L123,M0232','M01368','L1234,K0324','M01369','M01375','F04609,F04610','M0137','F04509','','F1,F2,F3,F4'],
}
df3 = pd.DataFrame.from_dict(data3)
df3
Out[47]:
BusID Tcap Terminal_Load N_Id N_Name Pcode
0 V1 4 1 1 N_Area M023
1 V1 4 2 2 Pstation L123,M0232
2 V1 4 1 5 T_depot M01368
3 V4 8 2 2 Pstation L1234,K0324
4 V4 8 1 5 T_depot M01369
5 V4 8 1 10 B_colony M01375
6 V5 12 2 11 c_colony F04609,F04610
7 V5 12 1 4 A_Area M0137
8 V5 12 1 10 B_colony F04509
9 V0 12 0 0 Destiny
10 v100 8 4 13 Z_colony F1,F2,F3,F4
The required Dataframe is the combination of both the first to dataframe, I need to assign the PCode based on the requirement Terminal Load. The load at a terminal is on the Terminal_Load value, for example at nodal ID 1 the load is 1 so 1 person is listed in the pcode, 2nd row terminal load is 2 and the nodal ID is 2 hence two person are loaded at Pcode. Terminal load 0 at the 0 node. requesting a pandas dataframe solution or any Non Pandas implementation just guessing any labelled pop up methods..or any algorithm to capacity and allocate.. and after allocating remove the label from the queue.
First aggregate list like previous solution and add new column to df2 with aggregate lists:
s = (df1.groupby(['N_Id','N_Name'])['pcode']
.agg(list)
.rename('Pcode'))
df = df2.join(s, on=['N_Id','N_Name'])
Then need split lists per groups (same per groups, so selected first list by iat[0]) by Terminal_Load values like linked solution - only is necessary cumulative sum and starting by 0:
def f(x):
indices = [0] + x['Terminal_Load'].cumsum().tolist()
s = x['Pcode'].iat[0]
#https://stackoverflow.com/a/10851479/2901002
x['Pcode'] = [','.join(s[indices[i]:indices[i+1]]) for i in range(len(indices)-1)]
return x
df = df.groupby(['N_Id','N_Name']).apply(f)
print (df)
BusID Tcap Terminal_Load N_Id N_Name Pcode
0 V1 4 1 1 N_Area M023
1 V1 4 2 2 Pstation L123,M0232
2 V1 4 1 5 T_depot M01368
3 V4 8 2 2 Pstation L1234,K0324
4 V4 8 1 5 T_depot M01369
5 V4 8 1 10 B_colony M01375
6 V5 12 2 11 c_colony F04609,F04610
7 V5 12 1 4 A_Area M0137
8 V5 12 1 10 B_colony F04509
9 V0 12 0 0 Destiny
10 v100 8 4 13 Z_colony F1,F2,F3,F4