1
$\begingroup$

i'm wondering if anyone can provide some input on improving the speed and calculations of a pandas result.

What i am trying to obtain is a summation of IDs in one table (player table) based on each row of a second table (UUID). Functionally each row needs to sum the total of the players table rows that are contained in its Active row and assign the UUID as the index to that row.

My initial thought was to loop row by row and calculate out my results but that has produced quite a slow result, and i suspect is not the optimal way that this could be accomplished. In the version below my estimate total time for the full dataset would be around 66 minutes. Running on a subsample of 10,000 takes around 20 seconds.

Would anyone have a better solution to calculating these results?

Thanks in advance!


UUID Table

This is a subset of the whole table

shape = (2060590, 2)

UUID Table


Player ID Table

This is a subset of the whole table

shape = (39,8)

points table


Final Table

final table


Code

# executes in ~20 seconds
df = None
for ix, i in enumerate(uuid_df[["UUID", "Active"]].sample(10000).itertuples(index=False)):
    # Get UUID for row
    _uuid = i[0]
    # Get list of "Active items" (these are the ones that will be summed)
    _active = i[1]

    # Create new frame by selecting everything from points table where the ID is in the Active List.
    # Sum selected values, convert to a dataframe with UUID as index and tranpose
    _dff = points_table_df.loc[points_table_df.index.isin(_active)].sum().to_frame(_uuid).T

    # Check if first dataframe, if not concat to existing one
    if df is None:
        df = _dff
    else:
        df = pd.concat([df, _dff])
$\endgroup$
  • $\begingroup$ can you give how you calculate it for one example? $\endgroup$ – Yohanes Alfredo Nov 19 at 16:50
  • $\begingroup$ I have the code block at the bottom which is calculating each row, are you thinking something more specific then that? I've added some comments to the code. $\endgroup$ – Cody Nov 19 at 16:56
  • $\begingroup$ Correct me if I am wrong, say if the active for a row is [4967,4023] then the answer should be [2.0,2.0,4.0,2.0,4.0,0.0,0.0] (in order of your output columns)? $\endgroup$ – Yohanes Alfredo Nov 19 at 17:00
  • $\begingroup$ Sorry, i'll note that it is a subset of the whole table, thats why i noted the shape size. Will clarify. $\endgroup$ – Cody Nov 19 at 17:00
  • $\begingroup$ Please check my assumption above, I can give you a much faster solution if my assumption is correct. $\endgroup$ – Yohanes Alfredo Nov 19 at 17:03
0
$\begingroup$

If I understand your DataFrame schemas correctly, then this should do it. All these operations are vectorized, so they should be much faster than iterating over the dataframe.

# "explode" the values in the `Active` column to get a dataframe of (UUID, player_id) pairs
uuid_df = uuid_df.explode('Active').rename(columns={
    'Active': 'player_id'
})

# inner join with the player stats dataframe 
# (this assumes that `player_id` is the index of `points_table_df`)
joined_df = uuid_df.merge(points_table_df, left_on='player_id', right_index=True)

# group by the UUID and sum to get aggregate stats
stats_df = joined_df.groupby('UUID').sum()

# promote the UUID to the index:
stats_df = stats_df.set_index('UUID')
$\endgroup$
  • 1
    $\begingroup$ I will try this and see how it performs. Thanks. $\endgroup$ – Cody Nov 19 at 17:19
0
$\begingroup$

This could actually be done quickly and intuitively using linear algebra.

So consider your player as label binarized array (can be done with MultiLabelBinarizer) so you would expect an array of size (2060590, 39) containing 0 an 1, rearrange the columns similar as how you order the your player table (or the other-way around which ever is easier), basically such that first column of your new matrix correspond to the same player on the player table. Finally just apply matrix multiplication, and done.

This is an example using generated sample, but hopefully you get the idea of doing this.

import numpy as np
import pandas as pd
from sklearn.preprocessing import MultiLabelBinarizer

mlb = MultiLabelBinarizer()

sample_active = pd.Series([[100,50,150,200],
                           [100,50,150],
                           [100,50],
                           [100]])
sample_df = pd.DataFrame()

sample_df['id'] = ['fadfsadsa', 'dsafsadf', 'dfsafsda', 'dasfasdfsaf']
sample_df['active'] = sample_active
## sample_df should look close to your original df

classes = [50,100,150,200]

player_df = pd.DataFrame({cl : np.random.uniform(0,1,size=5) for cl in classes}).T
player_df.columns = ['A','B','C','D','E'] 

sample_transformed = mlb.fit_transform(sample_active.values) ##apply multilabel binarizer

output = sample_transformed.dot(player_df.loc[mlb.classes_]) ##matrix multiply and get your required answer, use loc so the order will be similar as your binarized matrix.


new_df = pd.concat([sample_df['id'], pd.DataFrame(output)], axis = 1)
new_df.columns = ['id'] + list(player_df.columns)

For your case I think this should work :

mlb = MultiLabelBinarizer()
active_transformed = mlb.fit_transform(uuid_df['Active'])
output = active_transformed.dot(points_table_df.loc[mlb.classes_])
df = pd.concat([uuid_df[['UUID']], output], axis = 1)
df.columns = ['UUID'] + list(points_table_df.columns)

Try it!

$\endgroup$
  • $\begingroup$ Would you have an example of how to implement this? $\endgroup$ – Cody Nov 19 at 17:19
  • $\begingroup$ I think this is just a fancy way to join the UUID table with the player stats table, no? If you treat the UUID table as a 2060590 x 39 binary matrix, and the player stats table as a 39 x 8, then they're product will be the 2060590 x 8 matrix you would get from a left join. $\endgroup$ – zachdj Nov 19 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.