Replace a loop over pandas DataFrame

Question

I am iterating through a pandas dataframe (df) and adding scores to a dictionary containing python lists (scores):

for index, row in df.iterrows():
    scores[row["key"]][row["pos"]] = scores[row["key"]][row["pos"]] + row["score"]

The scores dictionary initially is not empty. The dataframe is very large and this loop takes a long time. Is there a way to do this without a loop or speed it up in some other way?

Answer 1

A for loop seems somewhat inevitable, but we can speed things up with NumPy's fancy indexing and Pandas' groupby:

# group the scores over `key` and gather them in a list
grouped_scores = df.groupby("key").agg(list)

# for each key, value in the dictionary...
for key, val in scores.items():
    
    # first lookup the positions to update and the corresponding scores
    pos, score = grouped_scores.loc[key, ["pos", "score"]]

    # then fancy indexing with `pos`: reaching all positions at once
    scores[key][pos] += score