Python: import csv, do math with specific cells, output new columns to new (or same) CSV

Question

I've been working on this for days-

I have a CSV that looks like:

COL A || COL B|| COL C||

0.1 || 0.0 || 0.5 ||

0.4 || 60 || 0.6 ||

0.3 || -60 || 0.5 ||

...

0.2 || -60 || 0.4 ||

There are 25 rows of numbers- they all vary slightly. I want to import this CSV using python, do some slight math (ex. finding the avg between cell A1 and C1) then either print a new COLUMN to a whole new CSV file or add a new COLUMN to the beginning of my current or (even duplicated) file. I know the the actual math part is easy. It's the importing, manipulation, then exporting a new COLUMN that I just cannot get.

Here's what I've tried: 1) First I tried importing the csv, changing it to a list, reading the columns I need then exporting to a new csv. The issue I have is that when I export to the CSV it doesn't create columns. It just adds things to a single cell that look like (0.111, 1.002, ..).

import csv
ofile=open('duplicate.csv', "w")
writer=csv.writer(ofile, delimiter=',')

with open('/Users/myCSV.csv', 'rb') as f:
    mycsv = csv.reader(f)
    mycsv = list(mycsv)
    avg=[]
    high=[]

    #average number
    for i in range(1,25):
        x=float(mycsv[i][16])
        avg.append(x)
    #print avg
    average=zip(avg)    

    #highest number
    for i in range(1,25):
        x=float(mycsv[i][15])
        high.append(x)
    #print high
    highest=zip(high)    
    print highest

writer.writerow([average,highest])

ofile.close()

2)Then I tried just creating a new column to a duplicate file and adding information into that column. I got a similar version of this from another similar question. This just doesn't work- I get the error "TypeError: can only assign an iterable"

import csv
infilename = r'/Users/myCSV.csv'
outfilename = r'/Users/myCSV_duplicate.csv'

with open(infilename, 'rb') as fp_in, open(outfilename, 'wb') as fp_out:
    reader = csv.reader(fp_in, delimiter=",")
    writer = csv.writer(fp_out, delimiter=",")

    headers = next(reader)  # read title row
    headers[0:0] = ['avg']
    writer.writerow(headers)

    for row in reader:
        for i in range(1,25):
            mycsv=list(reader)
            row[0:0] = float(mycsv[i][15])
        writer.writerow(row)

I've been at this for DAYS can someone please help!?!?!?

I've written all of this in MATLAB but need to transfer it over to Python... MATLAB was easier to figure out.

In the second code snippet use row.insert(0, float(mycsv[i][15])) instead of row[0:0] = float(mycsv[i][15]). — Ella Sharakanski
And in the second code snippet, what output do you expect? You only write a single row. Please show output example — Ella Sharakanski
Hey Ella- ideally for the second code snippet I'd want to create a new column which will be filled in by the column from my original CSV file- so in row 0 start the column down... I see now what it looks like it's doing. Here's the original text where a column of zeros was added: headers = next(reader) # read title row headers[26:26] = ['New Label'] writer.writerow(headers) for row in reader: row[26:26] = [0] writer.writerow(row) — AMK1234321
Oh sorry I meant what does the first code supposed to do. And did what I offer for the second code fixed the exception? — Ella Sharakanski
Yeah I think I figured it out! avg=[] #average for i in range(1,25): x=float(mycsvv[i][16]) avg.append(x) #print avg[1] for i in range(0,24): writer.writerow([avg[i]]) That was able to print a new column in a new CSV (using the code from my first attempt! Thanks for the help! — AMK1234321

TheBlackCat TheBlackCat · Accepted Answer · 2015-06-24T11:48:10

Use pandas. This is what it is designed for.

import pandas as pd

df = pd.read_csv('test.csv', sep='\|\|', usecols=[0, 1, 2])
df['avg'] = df.loc[:, ('COL A', 'COL C')].mean(axis=1)

df.to_csv('test2.csv', index=False)
df.to_csv('tes3.csv', index=False, columns='avg')