Selection o the value that corresponds to the intersection between columns and rows

user_19267644 0
Viewed 32
PRODUCT VALUE         NUMBER OF PAYMENTS
MIN VALUE MAX VALUE 1 TO 6 7 TO 11
500 1000 2% 4%
1001 ∞ 3% 5%

This is a small sample of the table of % interest to pay (the actual table is 5x5), I need to select the interest between a value and a number of payment installments, eventually I did with conditional---- were 81 lines of code, well for me it was ok, but I kept thinking what would be the "professional solution" using tables, pandas or numpy.

For example if my product cost 1500 USD, With 2 payments my interest rate would be 3%

select python
1 Answers

Using pandas the first part is easy. Replace MAX VALUE (where it is ∞) with a large integer (e.g. 1000000) and then you can select the appropriate row using:

product_value = 1500
df[(df['MIN VALUE'] <= product_value) & (df['MAX VALUE'] >= product_value)]
#    MIN VALUE  MAX VALUE 1 TO 6 7 TO 11
# 1       1001    1000000    3%       5%

The hard part is selecting the appropriate column from that row. One way to do that would be to create ranges from the column names:

ranges = { k : range(int(v[0]),int(v[1])+1) for k, v in [ (c, c.split(' TO ')) for c in df.columns ] if len(v) == 2}
print(ranges)
# {'1 TO 6': range(1, 7), '7 TO 11': range(7, 12)}

Then you could select the column from that dictionary:

no_payments = 2
col = [k for k, v in ranges.items() if no_payments in v][0]
# '1 TO 6'

Finally you can find the value you want using the row and column indexes:

df[(df['MIN VALUE'] <= product_value) & (df['MAX VALUE'] >= product_value)][col].values[0]

Output:

3%

Note: given the small size of your table, you could make your life much easier by having a separate column for each number of payments value. Then you could simply do:

df[(df['MIN VALUE'] <= product_value) & (df['MAX VALUE'] >= product_value)][no_payments].values[0]
user_94737640
Related