Replace values by NaN's if other matrix values equals a certain value in pandas

user_13854431 0
Viewed 91

I have two multiIndex dataframes; 1 indicating which player is on server, the other keeps track of the points. Hereby the serving player rotates every game.

col0 = ['Game 1','Game 1','Game 2','Game 2','Game 3','Game 3','Game 4','Game 4','Game 5','Game 5']
col1 = ['P1','P2','P1','P2','P1','P2','P1','P2','P1','P2']
a = pd.DataFrame(data = np.random.rand(3,10))
a.columns = [col0,col1]

     Game 1              Game 2  ...    Game 4    Game 5          
         P1        P2        P1  ...        P2        P1        P2
0  0.375562  0.408865  0.107393  ...  0.552553  0.986619  0.635726
1  0.101053  0.949870  0.804260  ...  0.895951  0.384401  0.368055
2  0.879938  0.740631  0.369314  ...  0.624967  0.061308  0.625157

and dataframe 'b' indicating which player is on serve.

col0 = ['Game 1','Game 2','Game 3','Game 4','Game 5']
col1 = ['Server','Server','Server','Server','Server']
b = pd.DataFrame([[1,2,1,2,1],
                  [2,1,2,1,2], 
                  [1,2,1,2,1]])
b.columns = [col0, col1] 

  Game 1 Game 2 Game 3 Game 4 Game 5
  Server Server Server Server Server
0      1      2      1      2      1
1      2      1      2      1      2
2      1      2      1      2      1

Now I want to create dataframe c, that looks like:

     Game 1              Game 2  ...    Game 4    Game 5          
         P1        P2        P1  ...        P2        P1        P2
0  0.375562  0.408865  np.nan    ...  np.nan    0.986619  0.635726
1  np.nan    np.nan    0.804260  ...  0.895951  np.nan    np.nan
2  0.879938  0.740631  np.nan    ...  np.nan    0.061308  0.625157

I want the values of dataframe 'a' to be replaced by NaN's whenever player 2 is on serve. In the first row for example of dataframe 'c' only the points in game 1, game 3 and game 5 are shown, since player 1 is on serve in those games.

Anything would help!

missing-data pandas nan python replace
1 Answers

You can try with reindex, replace and where:

OPTION 1

temp=b.reindex(columns=map(lambda x:(x[0],'Server') ,a.columns)).replace({1:True,2:False})
a.where(temp.values)

Same as this with np.where:

OPTION 2

import numpy as np
temp=b.reindex(columns=map(lambda x:(x[0],'Server') ,a.columns))
pd.DataFrame(np.where(temp.eq(1), a, np.nan),columns=a.columns)

Same as modifying the original b, and applying the mask with where:

OPTION 3

msk=[x.repeat(2)==1 for x in b.values]
a.where(msk)

Details of OPTION 1:

First you map the columns of a like this:

list(map(lambda x:(x[0],'Server') ,a.columns))
[('Game 1', 'Server'), ('Game 1', 'Server'), ('Game 2', 'Server'), ('Game 2', 'Server'), ('Game 3', 'Server'), ('Game 3', 'Server'), ('Game 4', 'Server'), ('Game 4', 'Server'), ('Game 5', 'Server'), ('Game 5', 'Server')]

Then you use reindex with that mapped list:

b.reindex(columns=map(lambda x:(x[0],'Server') ,a.columns))
  Game 1        Game 2        Game 3        Game 4        Game 5       
  Server Server Server Server Server Server Server Server Server Server
0      1      1      2      2      1      1      2      2      1      1
1      2      2      1      1      2      2      1      1      2      2
2      1      1      2      2      1      1      2      2      1      1

After that, you use replace to get change values of temp:

b.reindex(columns=map(lambda x:(x[0],'Server') ,a.columns)).replace({1:True,2:False})
  Game 1        Game 2        Game 3        Game 4        Game 5       
  Server Server Server Server Server Server Server Server Server Server
0   True   True  False  False   True   True  False  False   True   True
1  False  False   True   True  False  False   True   True  False  False
2   True   True  False  False   True   True  False  False   True   True

And finally you map using where with this mask(temp) the values of a:

a.where(temp.values)
     Game 1             Game 2              Game 3              Game 4  \
         P1       P2        P1        P2        P1        P2        P1   
0  0.973453  0.02111       NaN       NaN  0.435252  0.335656       NaN   
1       NaN      NaN  0.195463  0.960642       NaN       NaN  0.527152   
2  0.280339  0.97697       NaN       NaN  0.833331  0.476428       NaN   

               Game 5            
         P2        P1        P2  
0       NaN  0.676733  0.600626  
1  0.924126       NaN       NaN  
2       NaN  0.675638  0.319161
user_136762020
Related