Make calculation on one column in CSV file and add it into the file using python

Question

I'm looking for a way in which I could make calculation of one column and add it into the column below.

Example: I have this file as input:

Name;Size
Paul;175
Simon;178
Jhon;120

Result expected in the other csv file:

  Total size = 473
  Average = 157
  Number of player called paul =1

This is what I have:

import operator

data = csv.reader(open('player.csv'),delimiter=';')
total=0 
for row in data:
    total += int(row[2])

with open("result.csv", "wb") as f:
    writer = csv.writer(f)
    writer.writerows(total)

But it seems that total cannot be written into the other csv file. The thing is I don't know how to print all the calculation into the csv.

Any help would be great, BR

There are two problems: first, you need to skip the first input line (the header). Second, the expression row[2] means the third column. The first column is size[0] and the second column is row[1]. — Hai Vu
– Hai Vu, Commented Sep 11, 2015 at 13:24

Hai Vu · Accepted Answer · 2015-09-11 14:18:29Z

2

Besides the two problems I mentioned in the comment, the output file is not really a CSV. It is just a plain text file, so you don't need to use csv for that.

import csv
import collections

data = csv.reader(open('player.csv', 'rb'), delimiter=';')
next(data)  # Skip the header

sizes = []
players_count = collections.Counter()
for name, size in data:
    players_count.update([name])
    sizes.append(float(size))

total = sum(sizes)
average = total / len(sizes)

with open('result.txt', 'wb') as f:
    f.write('Total size = {}\n'.format(total))
    f.write('Average = {}\n'.format(average))

    # Output count of players, sorted by count in decending order
    f.write('\nCount Player\n')
    for player, count in players_count.most_common():
        f.write('{:5} {}\n'.format(count, player))

edited Sep 11, 2015 at 14:18

answered Sep 11, 2015 at 13:26

Hai Vu

41.4k16 gold badges75 silver badges106 bronze badges

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

1 Comment

Hai Vu Over a year ago

I updated the code to use the collections.Counter to do the counting of players' names.

m00am · Accepted Answer · 2015-09-11 14:53:03Z

Another solution would be to use the zip* function to separate the name and the size column into independent iterables. The central computation is of course similar to the solution presented by @Hai Vu.

#!/usr/bin/env python3
import csv
from collections import Counter

header, *data = csv.reader(open('player.csv', 'r'), delimiter=';')

# separate names and sizes into individual iterables
names, sizes = zip(*data)
sizes = list(map(int, sizes))

total_size = sum(sizes)
mean_size = total_size // len(sizes)

# pass all names to the counter at once
name_counts = Counter(names)

with open("result.txt", 'w') as outfile:
    print("Total size = {}".format(total_size), file=outfile)
    print("Average size = {}".format(mean_size), file=outfile)

    name_occurences = ["Number of players called {} = {}".format(name, count) for name, count in name_counts.most_common()]
    print("\n".join(name_occurences), file=outfile)

I used integer division here to make sure the fraction is dropped as in your expected output. If you would like floats just remove one of the slashes and change the average size output to

    print("Average size = {:2f}".format(mean_size), file=outfile)

to round the average size to two decimal positions.

Collectives™ on Stack Overflow

Make calculation on one column in CSV file and add it into the file using python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related