How to utilize numpy vectorization to map values of a column to anaother column based on a value in an intermediate column

user_16028265 0
Viewed 41

I have 3 columns: [whiteResult, blackResult, poiColor]. The nature of my data is chess game results

poi in my case is person_of_interest.

poiColor takes 2 values [white, black] (categorical column).

whiteResult and blackResult are categorical columns with 7/8 different categories [win, resigned, checkmated, abandoned,.....]

I want to create a new column poiResult: which copies the result of whiteResult if poiColor is white and vice-versa.

using df.loc, itertuples is very slow. Is there any suggestion on how to use numpy vectorizations, df.apply,... (any faster alternative) to achieve this?

Sample data:


blackResult whiteResult poiColor
0   resigned    win white
1   resigned    win white
2   win resigned    black
3   win checkmated  black
4   checkmated  win white
5   resigned    win white
6   resigned    win white
7   checkmated  win black
8   timeout win black
9   win resigned    black

MY CURRENT SOLUTION (VERY SLOW)

df.loc[df['poiColor']=='white', 'poiResult'] = df['whiteResult']
df.loc[df['poiColor']=='black', 'poiResult'] = df['blackResult']

I would like some optimization that enhances the performance of this code.

I have 10 million rows and it is a pain to use this solution. Even with Dask

array-broadcasting pandas python-3.x numpy python
1 Answers

Use np.where

import numpy as np

df['poiResult'] = np.where(df['poiColor'] == "white", 
                           df['whiteResult'], df['blackResult'])

>>> df
  blackResult whiteResult poiColor   poiResult
0    resigned         win    white         win
1    resigned         win    white         win
2         win    resigned    black         win
3         win  checkmated    black         win
4  checkmated         win    white         win
5    resigned         win    white         win
6    resigned         win    white         win
7  checkmated         win    black  checkmated
8     timeout         win    black     timeout
9         win    resigned    black         win

Performance: for 10,000,000 rows

cats = ["win", "resigned", "checkmated", "abandoned"]
df1 = pd.DataFrame({"blackResult": np.random.choice(cats, 10000000),
                    "whiteResult": np.random.choice(cats, 10000000),
                    "poiColor": np.random.choice(["white", "black"], 10000000)})

%timeit np.where(df1['poiColor'] == "white", df1['whiteResult'], df1['blackResult'])

1.21 s ± 59.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
user_152399510
Related