Pandas optimize look up of values

Question

I have two dataframes simresult and skipper.

Simresult is in the format:

     player_id      0      1      2      3  ...      5      6      7      8      9
0        41843  16.38  10.97   0.37  15.38  ...  23.78  38.08  47.21  26.14  11.94
1        17648  30.14  41.08   1.92   0.18  ...   0.00  39.01  41.08   8.54  22.95
2        16500  19.72  14.38  12.99   3.74  ...  17.72  32.70   3.34   0.50  28.66
3        36727   8.96  30.07  28.45   0.00  ...   0.00  20.48   0.00  27.64   2.15
4        10512  46.59  16.94   1.54   0.00  ...   5.89   0.00  18.33  11.24   0.71
..         ...    ...    ...    ...    ...  ...    ...    ...    ...    ...    ...

skipper is:

          0      1        2        3  ...        6       7        8        9
0    449732  15862  1200230    10261  ...   814904   14622    16129  2500366
1     14759  10325    17769    16163  ...    10934   15862    17851    21540
2     14542  10934  2038972  2038976  ...  2039175   15181    35356    14622
3     11446  35356    10261    17851  ...   538921   16282    14542    34672
4     15806  21540   894408    37481  ...    15862  480013    15860   814065
..      ...    ...      ...      ...  ...      ...     ...      ...      ...

where each of those cells is a player_id.

I have some code that takes the player_ids from skipper and looks up the value, by column, from simresult then sums the row and gives me a list of lists.

i.e. there are 10 ids per row in skipper, the final output gives me the sum of those 10 ids as looked up in the first column, then the second column, etc.

This code works exactly as intended.

size = skipper.shape[0]
runs = simresult.shape[1]-1

def getScores(r):

    slist = []
    
    for s in range(0,size): #size
        
        z = 0
        
        for l in range(0,10):

            y = skipper.iloc[s,l]
            x = simresult.loc[simresult['player_id'] == y, r].iloc[0]

            z += x

        z = round(z,2)
        
        slist.append(z)
    
    return(slist)


scores = np.empty((runs,0)).tolist()
    
for r in range(0,runs):
    
    scores[r] = getScores(r)

Output after I take the list of lists and put it into another df:

          0       1       2       3  ...       6       7       8       9
0     73.90  128.52  106.79  151.37  ...  126.81  115.88  139.87  115.43
1     93.51  135.78  130.62  130.72  ...  136.51  133.90  179.96  207.32
2    112.65  174.84  131.14  103.40  ...  159.58   85.81  116.59  158.88
3     83.37  129.34  117.12   64.50  ...  175.28   69.81  142.74  110.61
4     80.90  142.68   90.23  128.25  ...   51.04  141.63  134.30   95.76
..      ...     ...     ...     ...  ...     ...     ...     ...     ...

My question: Is there a more efficient way to code this lookup nested loop?

Simplifed simresult:

simplified skipper:

          0      1        2        3  ...        6       7        8        9
0    449732  15862  1200230    10261  ...   814904   14622    16129  2500366

simplified output:

          0       1       2       3  ...       6       7       8       9
0     73.90  128.52  106.79  151.37  ...  126.81  115.88  139.87  115.43

user_7964527 · Answer

Use the magic of replace

rep_df = df.set_index('player_id')
out = skip.replace(rep_df)

user_16425566 · Answer

def getScores(c):
    
    rep_df = simresult.set_index('player_id')
    out = round(skipper.replace(rep_df[c]).sum(axis=1),2).to_list()

    
    return(out)

Thanks to @BENY for the suggestion. Doing the replace/lookup on one column at a time from the simresult df and then summing that in place gets me exactly what I want -- and at a significantly faster processing time.

Pandas optimize look up of values

2 Answers