Adding All Values in Specific Column in Python

Question

I am using a Raspberry Pi to collect weather data and it does that fine. All the collected data to saved to a comma separated txt file.

Now I wish to get the average temp from this collected data, so therefore I need to add all the entries of a certain column together.

Here is a section of the collected data, the second column or index[1] is the captured temp data:

00:00,1.5,1025,100,1.5,Yes
01:00,2.0,1025,100,2.0,Yes
02:00,2.5,1025,100,2.5,Yes
03:00,2.7,1025,100,2.7,Yes
04:00,2.8,1025,100,2.8,Yes
05:00,3.0,1025,100,3.0,Yes
06:00,3.7,1025,100,3.7,Yes
07:00,4.1,1024,100,4.1,Yes
08:00,4.2,1025,100,4.2,Yes
09:00,4.6,1026,100,4.6,Yes
10:00,5.9,1026,100,5.9,Yes
11:00,6.3,1026,100,6.3,Yes
12:00,6.8,1026,100,6.8,Yes
13:00,7.9,1026,100,7.9,Yes
14:00,8.1,1026,100,8.1,Yes
15:00,7.8,1026,100,7.8,Yes
16:00,6.8,1026,100,6.8,Yes
17:00,5.8,1027,100,5.8,Yes
18:00,5.0,1027,100,5.0,Yes
19:00,4.7,1028,100,4.7,Yes
20:00,4.0,1028,100,4.0,Yes
21:00,3.7,1028,100,3.7,Yes
22:00,3.5,1028,100,3.5,Yes
23:00,4.0,1029,100,4.0,Yes
23:59,2.8,1029,100,2.8,Yes

Here is the sample code that I have been attempting to use to get the average temp but it keeps throwing an error:

#Adding all values in a columns
AvTemp = 0
with open(YdPath + yFileDate + '.txt', 'r') as AvgTemp:
    for line in AvgTemp:
        line = line.strip().split(',')
        AvTemp += sum(int(line[1]))
        print AvTemp

Here's the error:

AvTemp += sum(int(line[1])/25)
ValueError: invalid literal for int() with base 10: '1.5'

so I can see that the entry being a decimal causes the error because it's not an integer but I still get another error even if I remove the int part.

Any help would be appreciated

Thanks

UPDATE AFTER TRYING VARIOUS TIPS

After trying the various tips and answers added I am still not getting the required outcome. Basically I only need the actual final result to be printed i.e. the total of index[1] equates to 114.2 then this is divided by the number len of rows, in this case 25 to give a result of 4.568.

Using these various help tips, this is currently what is printed to the screen:

You can see that it keeps printing out each row with the addition of the last before coming to the final total of 114.2. (I've left out the division part here). I really don't want all the other lines printed. I assume it's a break needed somewhere perhaps?

Possible duplicate of [Add all values in a CSV column in Python](http://stackoverflow.com/questions/10657965/add-all-values-in-a-csv-column-in-python) — juanpa.arrivillaga, Jan 23 '17 at 11:12

Sudhir Bastakoti · Answer 1 · 2017-01-23T11:53:29.580

1

cast it to float, like:

for line in AvgTemp:
    line = line.strip().split(',')
    AvTemp += sum(float(line[1]))
print AvTemp

you can append values to a list and sum later like:

tot = []
for line in AvgTemp:
    line = line.strip().split(',')
    value = float(line[1])
    tot.append(value)
#sum values
total = sum(tot)

edited Jan 23 '17 at 11:53

answered Jan 23 '17 at 11:11

Sudhir Bastakoti

99,167
15
158
162

1

This code gives an error : AvTemp += sum(float(line[1])) TypeError: 'float' object is not iterable – Harshdeep Sokhey Jan 23 '17 at 11:23
Thanks but this still prints out each value cumulatively i.e. each entry printed is the addition of the current line added to all the previous lines in the column and the output displayed using the data I posted is: `1.5 3.5 6.0 8.7 11.5 14.5 18.2 22.3 26.5 31.1 37.0 43.3 50.1 58.0 66.1 73.9 80.7 86.5 91.5 96.2 100.2 103.9 107.4 111.4 114.2` – 1cm69 Jan 23 '17 at 12:10
1

@1cm69 you are print'ing inside for loop, hence such output, print the total out of for loop – Sudhir Bastakoti Jan 23 '17 at 15:24
At last, thank you. Something so simple as having my print statement incorrectly tabbed inside the loop - I just could see it – 1cm69 Jan 23 '17 at 15:31

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

The line[1] is a single value, the sum(int(line[1])) makes no sense. Replace

AvTemp += sum(int(line[1]))

with

AvTemp += float(line[1])

This should give you the total sum and then you can average it.

The complete code is :

with open(YdPath + yFileDate + '.txt', 'r') as AvgTemp:
    for line in AvgTemp:
        line = line.strip().split(',')
        AvTemp += float(line[1])
print AvTemp

You can also try looking at this question: Read specific columns from a csv file with csv module?

edited May 23 '17 at 12:24

Community

1
1

answered Jan 23 '17 at 11:21

Harshdeep Sokhey

324
5
15

This is getting closer although it does eventually display the column total it first prints all the cumulative entries one after another. I am trying to only print the final result i.e. 114.2 (the sum of all entries in index[1]) – 1cm69 Jan 23 '17 at 11:38

Adding All Values in Specific Column in Python

2 Answers2