Reputation: 113
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
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