Sam
Sam

Reputation: 113

Applying values to column based on different columns

I'm trying to do a little data manipulation that has some odd requirements:

 Index   ht1   w1   ht2   w2   ht3   w3   top width  
 ------- ----- ---- ----- ---- ----- ---- ----------- 
      0     9    5                                 5  
      1     7    2     3                              
      2     3    4                                 4  
      3     4    3     5    6                      6  
      4     6    8     2    1     3    5           5  

I have data in the format of paired columns ie. height and width. There are three pairs of height and width columns. The last column picks up the last width value in the row ONLY IF there is a value in the height column. Additionally, if there is a value in any of the height columns but the corresponding width is empty, the top width will read empty e.g. for line index 1: the corresponding height to the width is blank, so the top width column will be blank.

In the data I have, the last column (top width) is empty and needs to be filled to look like the table above with the last width value.

For the code, I have tried using map and apply. In particular, I tried to use a condition with the apply method but it didn't work. Is there anyone who can help fill out the top width column based on the conditions set out above?

Thanks

Upvotes: 1

Views: 43

Answers (1)

jezrael
jezrael

Reputation: 863291

I think need:

#filter DataFrames by columns
a = df.filter(like='ht')
b = df.filter(like='w')

#boolean mask with XOR
m = (a.notnull().values ^ b.notnull().values).any(axis=1)

#get last values with replace to NaN by mask
df['new'] = b.ffill(axis=1).iloc[:, -1].mask(m)
print (df)
       ht1  w1  ht2   w2  ht3   w3  new
Index                                  
0        9   5  NaN  NaN  NaN  NaN  5.0
1        7   2  3.0  NaN  NaN  NaN  NaN
2        3   4  NaN  NaN  NaN  NaN  4.0
3        4   3  5.0  6.0  NaN  NaN  6.0
4        6   8  2.0  1.0  3.0  5.0  5.0

Upvotes: 1

Related Questions