CODEWITHSUNDEEP
pythoncsvpython-2.7append
SMNALLY
SMNALLY

Reputation: 1391

How to perform a simple calculation in a CSV and append the results to the file

I have a csv which contains 38 colums of data, all I want to find our how to do is, divide column 11 by column by column 38 and append this data tot he end of each row. Missing out the title row of the csv (row 1.)

If I am able to get a snippet of code that can do this, I will be able to manipulate the same code to perform lots of similar functions.

My attempt involved editing some code that was designed for something else. See below:

from collections import defaultdict

class_col = 11
data_col = 38

# Read in the data
with open('test.csv', 'r') as f:
    # if you have a header on the file
    # header = f.readline().strip().split(',')
    data = [line.strip().split(',') for line in f]

# Append the relevant sum to the end of each row
for row in xrange(len(data)):
    data[row].append(int(class_col)/int(data_col))

# Write the results to a new csv file
with open('testMODIFIED2.csv', 'w') as nf:
    nf.write('\n'.join(','.join(row) for row in data))

Any help will be greatly appreciated. Thanks SMNALLY

Upvotes: 0

Views: 8423

Answers (2)

dlm
dlm

Reputation: 4254

Use pandas:

import pandas
df = pandas.read_csv('test.csv') #assumes header row exists
df['FRACTION'] = 1.0*df['CLASS']/df['DATA'] #by default new columns are appended to the end
df.to_csv('out.csv')

Upvotes: 0

Curt
Curt

Reputation: 1414

import csv

with open('test.csv', 'rb') as old_csv:
    csv_reader = csv.reader(old_csv)
    with open('testMODIFIED2.csv', 'wb') as new_csv:
        csv_writer = csv.writer(new_csv)
        for i, row in enumerate(csv_reader):
            if i != 0:
                row.append(float(row[10]) / float(row[37]))
                csv_writer.writerow(row)

Upvotes: 4

Related Questions