CODEWITHSUNDEEP
pythonparsingpython-2.7csvdictionary
vivs
vivs

Reputation: 113

Sum of a particular column in a csv file

There is a csv file, say A.csv, having content:

Place,Hotel,Food,Fare

Norway,Regal,NonVeg,5000    
Poland,Jenny,Italiano,6000    
Norway,Suzane,Vegeterian,4000   
Norway,Regal,NonVeg,5000

I have to parse this csv and obtain an output by passing arguments in command prompt.

Example 1: 

mycode.py Place

Desired output is: 

Place,Fare    
Norway,14000  
Poland,6000

Example 2: 

mycode.py Place Hotel

Desired output is:

Place,Hotel,Fare
Norway,Regal,10000  
Poland,Jenny,6000  
Norway,Suzane,4000

So it is clear from the above example that no matter what you pass as argument it gives you the sum of the Fare header for the common ones.

Below is my code and I am able to pass arguments and get an output, but I am stuck in sum of Fare. Can any one help me with this.

import sys
import csv
import collections

d = collections.defaultdict(list)

Data = []
Result = []
Final = []
Argvs = []
argv_len = len(sys.argv)
index = 0
input = ''

file = open('A.csv', 'rb')
try:
    reader = csv.reader(file)
    for row in reader:
        Data.append(row)

    for x in range(1, argv_len):
        Argvs.append(sys.argv[x])
    Argvs.append('Fare')

    for input in Argvs:
        for y in range(0, len(Data[0])):
            if(input == Data[0][y]):
                for z in range(1, len(Data)):
                    Result.append(Data[z][y])                   
                break       
        Final.append(Result)
        Result = []

    New = []
    NewFinal = []
    for x in range(0, len(Final[0])):
        for y in range(0, len(Final)):
            New.append(Final[y][x])
        NewFinal.append(New)
        New = []
    out = {}
    for a in NewFinal:
        out.setdefault(a[0],[]).append(int(a[-1]))
    with open("output.csv", "wb") as csv_file:
        writer = csv.writer(csv_file,  dialect='excel', delimiter=',')
        writer.writerow(Argvs)
        for k,v in out.iteritems():
            writer.writerow((k,sum(v)))
except Exception,e:
    print str(e)
finally:
    file.close()

I edit the code and tried to group it. Now I am able to get the aggregate of the Fare but not the desired output.

So when I am passing:

mycode.py Place Hotel

Instead of: 

Place,Hotel,Fare  
Norway,Regal,10000  
Poland,Jenny,6000  
Norway,Suzane,4000

I am getting:

Place,Hotel,Fare  
Norway,14000  
Poland,6000

Upvotes: 0

Views: 775

Answers (1)

vivs
vivs

Reputation: 113

Finally i managed to get my desired output.
Below i am sharing the final code. \

import sys
import csv

Data = []
Result = []
Final = []
Argvs = []
argv_len = len(sys.argv)
index = 0
input = ''

file = open('A.csv', 'rb')
try:
    reader = csv.reader(file)
    for row in reader:
        Data.append(row)

    for x in range(1, argv_len):
        Argvs.append(sys.argv[x])
    Argvs.append('Fare')

    for input in Argvs:
        for y in range(0, len(Data[0])):
            if(input == Data[0][y]):
                for z in range(1, len(Data)):
                    Result.append(Data[z][y])                   
                break       
        Final.append(Result)
        Result = []

    New = []
    NewFinal = []
    for x in range(0, len(Final[0])):
        for y in range(0, len(Final)):
            New.append(Final[y][x])
        NewFinal.append(New)
        New = []
    out = {}
    for a in NewFinal:
        count_val = a[-1]
        del a[-1]
        key_val = ','.join(a)
        out.setdefault(key_val.strip('"'),[]).append(int(count_val))
    with open("output.csv", "wb") as csv_file:
        writer = csv.writer(csv_file, delimiter=',',quotechar=' ')
        writer.writerow(Argvs)
        for k,v in out.iteritems():
            writer.writerow((k,sum(v)))
except Exception,e:
    print str(e)
finally:
    file.close()

Upvotes: 1

Related Questions