Using simple code to get the average (in Python) of an entire column in a csv file

Question

I've seen similar questions, but never one that gives a simple straightforward pythonic answer.

I'm simply trying to get the average for the "high" column in a csv file.

import csv
import numpy as np    


with open('2010-Jan-June.csv', 'r', encoding='utf8', newline='') as f:
    highs = []
    for row in csv.DictReader(f, delimiter=','):
        high = int(row['high'])
print(sum(highs)/len(highs))

My csv looks like this:

date,high,low,precip
1-Jan,43,41,0
2-Jan,50,25,0
3-Jan,51,25,0
4-Jan,44,25,0
5-Jan,36,21,0
6-Jan,39,20,0
7-Jan,47,21,0.04
8-Jan,30,14,0
9-Jan,30,12,0

Change high = int(row['high']) to highs.append(int(row['high'])) — Stephen Rauch
– Stephen Rauch ♦, Commented Mar 24, 2018 at 23:44
@PaulPanzer it was actually in there. I meant to bring the code over out of my function box. — ArchivistG
– ArchivistG, Commented Mar 24, 2018 at 23:51

MaxU - stand with Ukraine · Accepted Answer · 2018-03-24 23:46:04Z

2

Using Pandas:

import pandas as pd

avg = pd.read_csv(r'/path/to/2010-Jan-June.csv', usecols=['high'], squeeze=True).mean()

edited Mar 24, 2018 at 23:46

answered Mar 24, 2018 at 23:43

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Comments

juanpa.arrivillaga · Accepted Answer · 2018-03-25 00:04:38Z

2

Note, this is totally possible using plain Python:

import csv
import statistics as stats

with open('2010-Jan-June.csv') as f:
    avg = stats.mean(row['high'] for row in csv.DictReader(f, delimiter=','))

print(avg)

answered Mar 25, 2018 at 0:04

juanpa.arrivillaga

97.6k14 gold badges141 silver badges190 bronze badges

Comments

hpaulj · Accepted Answer · 2018-03-25 00:06:24Z

Since you imported numpy you can use that - almost as easily as pandas:

Reading from a paste copy of your sample:

In [36]: txt="""date,high,low,precip
    ...: 1-Jan,43,41,0
    ...: 2-Jan,50,25,0
    ...: 3-Jan,51,25,0
    ...: 4-Jan,44,25,0
    ...: 5-Jan,36,21,0
    ...: 6-Jan,39,20,0
    ...: 7-Jan,47,21,0.04
    ...: 8-Jan,30,14,0
    ...: 9-Jan,30,12,0"""

Python3 with numpy 1.14 likes to have the encoding parameter:

In [38]: data = np.genfromtxt(txt.splitlines(),delimiter=',',dtype=None,names=True,
    ...: encoding=None)
In [39]: data
Out[39]: 
array([('1-Jan', 43, 41, 0.  ), ('2-Jan', 50, 25, 0.  ),
       ('3-Jan', 51, 25, 0.  ), ('4-Jan', 44, 25, 0.  ),
       ('5-Jan', 36, 21, 0.  ), ('6-Jan', 39, 20, 0.  ),
       ('7-Jan', 47, 21, 0.04), ('8-Jan', 30, 14, 0.  ),
       ('9-Jan', 30, 12, 0.  )],
      dtype=[('date', '<U5'), ('high', '<i8'), ('low', '<i8'), ('precip', '<f8')])

The result is a structured array, from which it is easy to pick the high field:

In [40]: data['high']
Out[40]: array([43, 50, 51, 44, 36, 39, 47, 30, 30])
In [41]: data['high'].mean()
Out[41]: 41.111111111111114

Or in one line, loading just one column:

In [44]: np.genfromtxt(txt.splitlines(),delimiter=',',skip_header=1,usecols=[1]).mean()
Out[44]: 41.111111111111114

Michael Back · Accepted Answer · 2018-03-25 00:18:46Z

0

Here is my attempt at a pythonic answer using just csv library...

import csv
with open ('names.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    print sum(float(d['high']) for d in reader) / (reader.line_num - 1)

Will have a divide by 0 if there are no lines in the file.

answered Mar 25, 2018 at 0:18

Michael Back

1,8712 gold badges16 silver badges17 bronze badges

Collectives™ on Stack Overflow

Using simple code to get the average (in Python) of an entire column in a csv file

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related