Pandas Dataframe -update dynamic rows and columns without loops

Question

I have a source dataframe

source.head()
Out[41]: 
          A         B       C       compare
892  4.031250  6.218750  6.515625  4.246094
893  4.079531  6.222656  5.945312  4.038281
894  4.023438  6.226562  6.039062  4.250000
895  5.109531  4.238281  6.035156  4.038281
896  4.019531  6.242188  6.089844  4.242188

and a target dataframe with same index

target.tail()
Out[42]: 
          A    B    C
636893    0    0    0
636894    0    0    0
636895    0    0    0
636896    0    0    0
636897    0    0    0

I want to do this-

in source, select

 a. index where 'compare' is less than min of the rest. 
 b. the column of the min of the rest

in target, update that location (index and column) with source['compare']/source[min_col]

So with the above source values, the result of the target would be this

target.head()
Out[41]: 
           A                     B                     C
892        0                     0                     0
893        4.038281/4.079531     0                     0
894        0                     0                     0
895        0                     4.038281/4.238281     0
896        0                     0                     0

I do 1a. i.e. selecting index where compare less than min of the rest by:

      tenors = ['A', 'B', 'C']
      idx_select= source.index[(source['compare'] < source[tenors].min(1))]

I can do 1b. i.e. get the column where min occurs by:

      col_min = source.ix[idx_select, tenors].idxmin(1)

I am not sure how to the second step, i.e. update target values except doing a loop by rows.

Any suggestions to avoid loops?

Quang Hoang · Accepted Answer · 2020-07-24 16:45:18Z

1

You can try the following, hopefully it's self-explained:

cols = ['A','B','C']
target = df[cols].div(df.compare, axis='rows')
target = target.where(target.gt(1) & target.eq(target.min(1), axis='rows'), 0)

Output:

print(target)

            A         B    C
892  0.000000  0.000000  0.0
893  1.010215  0.000000  0.0
894  0.000000  0.000000  0.0
895  0.000000  1.049526  0.0
896  0.000000  0.000000  0.0

answered Jul 24, 2020 at 16:45

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas Dataframe -update dynamic rows and columns without loops

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related