Performing calculations in python using excel values

Question

I'm trying to do some calculations in python using an excel with several rows and using 3 columns. I'm using the columns 'Band' and 'Antenna_Heigh' and I would like to save these values in the column 'Colors'. My code looks like this but I have an error saying 'list assignment index out of range'.

madrid = pd.read_excel('Madrid.xlsx')

ch = []

d=[]
  
L = 8.4

for i in range(0, len(madrid)):

    ch[i] = 0.8 + (1.1*math.log10(madrid['Band'][i]) - 0.7)*madrid['Antenna_Height'][i] - 1.56*math.log10(madrid['Band'][i])

    d[i] = ((L - 69.55 - 26.16*math.log10(madrid['Band'][i]) + 13.82*math.log10(madrid['Antenna_Height'][i]) + ch[i])/(44.9 - 6.55*math.log10(madrid['Antenna_Height'][i])))**10

    madrid['Colors'][i] = d[i]

Use for i in range(0, len(madrid)):. Remember that Python's indexes start in 0, not in 1. — Ignatius Reilly
– Ignatius Reilly, Commented Jul 26, 2022 at 16:04
Yes, I started using 0 but i was trying with 1. But I have the same error even if I use 0 — João Morais
– João Morais, Commented Jul 26, 2022 at 16:05
Ok, but if I do d = []; d[1] = 'foo', I get the same error. So, at least, keep the range starting in 0 for assigning values to the lists. — Ignatius Reilly
– Ignatius Reilly, Commented Jul 26, 2022 at 16:09
Do you need to keep the values of ch and d after generating the 'Colors' column? — Ignatius Reilly
– Ignatius Reilly, Commented Jul 26, 2022 at 16:10

blackraven · Accepted Answer · 2022-07-26 16:21:18Z

1

The list ch is empty, so calling any element index like ch[0] or ch[1] will result in error "list index out of range"

I see you're trying to append to the various lists. You can try (but I'm not sure about the formula, please check them)

import math

madrid = pd.read_excel('Madrid.xlsx')
ch = []
d=[]
L = 8.4

for i in range(0, len(madrid)):
    ch_temp = 0.8 + (1.1*math.log10(madrid['Band'][i]) - 0.7)*madrid['Antenna_Height'][i] - 1.56*math.log10(madrid['Band'][i])
    ch.append(ch_temp)
    d_temp = ((L - 69.55 - 26.16*math.log10(madrid['Band'][i]) + 13.82*math.log10(madrid['Antenna_Height'][i]) + ch[i])/(44.9 - 6.55*math.log10(madrid['Antenna_Height'][i])))**10
    d.append(d_temp)
    madrid['Colors'][i] = d[i]

print(madrid)

Output:

   Band  Antenna_Height      Colors
0     1               3   18.262619
1     2               3   62.881852
2     3               3  121.442224
3     4               3  188.948366
4     5               3  262.794745
5     6               3  341.404788

edited Jul 26, 2022 at 16:21

answered Jul 26, 2022 at 16:11

blackraven

5,6797 gold badges27 silver badges51 bronze badges

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

7 Comments

João Morais Over a year ago

I'm getting a 'math domain error' with this code

blackraven Over a year ago

yes that could be due to the formula, i'm not sure if the input values like ch[i] etc are correct. My simple table seems to work

João Morais Over a year ago

The input values are just the 'Band' and the 'Antenna_Height'

Ignatius Reilly Over a year ago

Is it possible you have a value of 0 or NaN in 'Antenna_Height'?

Ignatius Reilly Over a year ago

math.log10(0) produces ValueError: math domain error

|

Ignatius Reilly · Accepted Answer · 2022-07-26 16:35:15Z

1

The pandas' way of applying a function to many rows is with pd.apply. I'm using index=1 to get the values horizontally through both columns 'Antenna_Height' and 'Band':

def get_color(row):
    L = 8.4
    ch = 0.8 + (1.1*math.log10(row['Band']) - 0.7)*row['Antenna_Height'] - 1.56*math.log10(row['Band'])
    d = ((L - 69.55 - 26.16*math.log10(row['Band']) + 13.82*math.log10(row['Antenna_Height']) + ch)/(44.9 - 6.55*math.log10(row['Antenna_Height'])))**10
    return d

madrid['Color'] = madrid.apply(get_color, axis = 1)

This is not necessarily faster, but in general makes the intention of the code more obvious.

edited Jul 26, 2022 at 16:35

answered Jul 26, 2022 at 16:22

Ignatius Reilly

1,7524 gold badges9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Performing calculations in python using excel values

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related