Nodal/Label assignment according to the load at a terminal in pandas data frame python

Question

Input data frame with requirement for boarding in a transport allocation setting.

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)

output

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

second data frame with a route plan/to load according to the Terminal/Nodal load.

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)

output of the second data frame.

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

required Dataframe format..

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)

required output...

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.

Is there a question here? Please clarify your question to indicate what you would like to get, what you are getting as well as the least amount of code to illustrate your issue. — itprorh66, Jun 02 '22 at 23:39
The required Dataframe is the combination of both the first to dataframe, I need to assign the PCode based on the requirement Terminal Load. — vinsent paramanantham, Jun 03 '22 at 06:03
@mozway, Hi Mozway i have added another question please have a look at, https://stackoverflow.com/questions/72481631/nodal-label-assignment-according-to-the-load-at-a-terminal-in-pandas-data-frame — vinsent paramanantham, Jun 03 '22 at 06:29
@vinsentparamanantham - tested my solution and working well. Do you test it with this data? — jezrael, Jun 03 '22 at 08:02
`s = df1.groupby(['N_Id','N_Name'])['pcode'].agg(list).rename('Pcode') df = df2.join(s, on=['N_Id','N_Name']) df['Pcode'] = [','.join(x[:int(y)]) if int(y) != 0 else None for x, y in zip(df['Pcode'], df['Terminal_Load'])]` — jezrael, Jun 03 '22 at 08:02
One idea, is possible use `s = df1.drop_duplicates(['N_Id','N_Name','pcode']).groupby(['N_Id','N_Name'])['pcode'].agg(list).rename('Pcode')` instead `s = df1.groupby(['N_Id','N_Name'])['pcode'].agg(list).rename('Pcode')` ? — jezrael, Jun 03 '22 at 08:04
@jezrael, kindly look into the following question, https://stackoverflow.com/questions/72545167/ — vinsent paramanantham, Jun 08 '22 at 13:33

jezrael · Accepted Answer · 2022-06-07T05:44:51.427

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

I am suppose to get the following in the 3rd row.. 3 V4 8 2 2 Pstation L1234,K0324, why I am getting L123,L1234 twice in the second answer — vinsent paramanantham, Jun 03 '22 at 09:51
@vinsentparamanantham - now understand, more complicated like seems. — jezrael, Jun 03 '22 at 11:58
@vinsentparamanantham - Sure, I can explain. I try add some description, if something not clear (or too small decription) let me know. — jezrael, Jun 07 '22 at 05:47