Get column dynamically in loc based on condition

Question

I have a dataframe :

df.head() :
col_a col_a Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
10      2
20      6
44      3
55      1 
86      4
67      5

What I want do : I want values of col_a to be assigned based on col_b in a specific month, for example, the first value of column_a ie 10 should be assigned to Month 2 based on 2 that is coming from col_b Similarly, for col_a=67 should be assigned to Month 5

output:

col_a    col_b Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
    10      2            10
    20      6                                           20
    44      3                    44
    55      1   55
    86      4                             86
    67      5                                     67

I can do this by iterating over each row and extracting the value from col_b and using regex to match the appropriate month, and then assigning the value. Since I have a large number of rows 3000+ this is going to take time. Can somebody help with a better approach?

PS:- The dtype is str not int.

Please provide an [mre], for pandas see here: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Andreas, Sep 21 '21 at 18:45
this is an unusual data manipulation - it seems like `col_b` makes for a more reasonable data format. Why do you want to do this? — anon01, Sep 21 '21 at 18:54

score 1 · Answer 1 · answered Sep 21 '21 at 18:46

Try with pivot then update

df.update(df[['col_a','col_b']].pivot(columns='col_b',values='col_a').add_prefix('Month '))

out

df
Out[234]: 
   col_a  col_b  Month 1  Month 2  Month 3  Month 4  Month 5  Month 6
0     10      2     NaN    10.0     NaN     NaN     NaN     NaN
1     20      6     NaN     NaN     NaN     NaN     NaN    20.0
2     44      3     NaN     NaN    44.0     NaN     NaN     NaN
3     55      1    55.0     NaN     NaN     NaN     NaN     NaN
4     86      4     NaN     NaN     NaN    86.0     NaN     NaN
5     67      5     NaN     NaN     NaN     NaN    67.0     NaN

Get column dynamically in loc based on condition

1 Answers1