Find the median from a CSV File using Python

问题:

I have a CSV file named 'salaries.csv' The content of the files is as follows:

City,Job,Salary
Delhi,Doctors,500
Delhi,Lawyers,400
Delhi,Plumbers,100
London,Doctors,800
London,Lawyers,700
London,Plumbers,300
Tokyo,Doctors,900
Tokyo,Lawyers,800
Tokyo,Plumbers,400
Lawyers,Doctors,300
Lawyers,Lawyers,400
Lawyers,Plumbers,500
Hong Kong,Doctors,1800
Hong Kong,Lawyers,1100
Hong Kong,Plumbers,1000
Moscow,Doctors,300
Moscow,Lawyers,200
Moscow,Plumbers,100
Berlin,Doctors,800
Berlin,Plumbers,900
Paris,Doctors,900
Paris,Lawyers,800
Paris,Plumbers,500
Paris,Dog catchers,400

I need to print the median salary of each profession. I tried a code, which shows some error.

My code is :

from StringIO import StringIO
import sqlite3
import csv
import operator #from operator import itemgetter, attrgetter

data = open('sal.csv', 'r').read()
string = ''.join(data)
f = StringIO(string)
reader = csv.reader(f)
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute('''create table data (City text, Job text, Salary real)''')
conn.commit()
count = 0

for e in reader:
    if count==0:
        print ""
    else:
        e[0]=str(e[0])
        e[1]=str(e[1])
        e[2] = float(e[2])
        c.execute("""insert into data values (?,?,?)""", e)
        count=count+1
        conn.commit()

labels = []
counts = []
count = 0
c.execute('''select count(Salary),Job from data group by Job''')

for row in c:
      for i in row:
            if count==0:
               counts.append(i)
               count=count+1
           else:
                count=0
      labels.append(i)

c.execute('''select Salary,Job from data order by Job''')

count = 1
count1 = 1
temp = 0
pri = 0
lis = []

for row in c:
      lis.append(row)
for cons in counts:
      if cons%2 == 0:
         pri = cons/2
     else:
         pri = (cons+1)/2
     if count1 == 1:
        for li in lis:
              if count == pri:
                  print "Median is ",li
        count = count + 1
        count = 0
        temp = pri+cons
     else:
        for li in lis:
              if count == temp:
                  print "Median is",li
              count = count+1
              count = 0
              temp = temp + pri
       count1 = count1 + 1

However, it is showing some error:

IndentationError('expected an indented block', ('', 28, 2, 'if count==0:\n'))

How do I fix the error?

回答1:

You can use defaultdict to put all the salaries for each profession then just get the median.

import csv
from collections import defaultdict

with open("C:/Users/jimenez/Desktop/a.csv","r") as f:
    d = defaultdict(list)
    reader = csv.reader(f)
    reader.next()
    for row in reader:
        d[row[1]].append(float(row[2]))   

for k,v in d.iteritems():
    print "{} median is {}".format(k,sorted(v)[len(v) // 2])
    print "{} average is {}".format(k,sum(v)/len(v))

Outputs

Plumbers median is 500.0
Plumbers average is 475.0
Lawyers median is 700.0
Lawyers average is 628.571428571
Dog catchers median is 400.0
Dog catchers average is 400.0
Doctors median is 800.0
Doctors average is 787.5

回答2:

It is easy if you use pandas (http://pandas.pydata.org):

import pandas as pd
df = pd.read_csv('test.csv', names=['City', 'Job', 'Salary'])
df.groupby('Job').median()

#               Salary
# Job                 
# Doctors          800
# Dog catchers     400
# Lawyers          700
# Plumbers         450

If you want the average instead of the median,

df.groupby('Job').mean()

#                   Salary
# Job                     
# Doctors       787.500000
# Dog catchers  400.000000
# Lawyers       628.571429
# Plumbers      475.000000

回答3:

If your problem is computing he median, and not inserting everything in a SQL databas and scrambling it about, it is a matter of just reading all lines, group all salaries in a list, and get the median from there - this reduces your hundred-line-magnitude script to:

import csv
professions = {}

with open("sal.csv") as data:
    for city, profession, salary in csv.reader(data):
        professions.setdefault(profession.strip(), []).append(int(salary.strip()))

for profession, salaries in sorted(professions.items()):
    print ("{}: {}".format(profession, sorted(salaries)[len(salaries//2)] ))

(give or take "1" to get the proper median from the sorted salaries)