Reading from and Writing to CSV files

Question

I am struggling with Python 2.7.10. I’m trying to create a program that will eventually open a CSV file, read numbers from the file, perform calculations with the numbers and write back to the CSV file.

The code (i.e. the calculations) is not finished, I just wanted to try a few small bits so I could start to identify problems. The data in the CSV file looks like this:

['110000,75000\n', '115000,72500\n', '105000,85250\n', '100000,70000']

One thing that I am having issues with is properly converting the CSV strings to numbers and then telling Python what row, column I want to use in the calculation; something like Row(0), Column(0) – Row(1) Column(1).

I have tried a few different things but its seems to crash on the converting to numbers bit. Error message is TypeError int() argument must be a string or a number, not list OR IOError File not open for string – depending on what I have tried. Can someone point me in the right direction?

import csv

def main():
    my_file = open('InputData.csv','rU')
    #test = csv.writer(my_file, delimiter=',')

    file_contents = my_file.readlines()
    print file_contents

    for row in file_contents:
        print row

    #convert to numbers
    #val0 = int(file_contents.readlines(0))
    #val1 = int(file_contents.readlines(1))
    #val0 = int(my_file.readlines(0))
    #val1 = int(my_file.readlines(1))

    #perform calculation
    #valDiff = val1 - val0

    #append to third column, may need to be in write file mode, num to strings
    #file_contents.append
    my_file.close()

main()

Possible duplicate of [Reading and Writing data in CSV files in Python](http://stackoverflow.com/questions/34605150/reading-and-writing-data-in-csv-files-in-python) — gr1zzly be4r, May 10 '16 at 12:28

score 0 · Answer 1 · answered May 10 '16 at 12:41

0

The list file_contents now contains all of your excel data, so trying to use readlines probably won't work on the list type. I would try

row0 = file_contents[0].split(",")

Which should give you the first row in a list format. You should (and most likely will need to) put this in a loop to cover any size excel sheet you have. Then

val0 = int(row0[0])

should give you the value you want. But again I would make this iterative to save yourself some time and effort.

answered May 10 '16 at 12:41

E Bels

1

OK, I tried using your suggestion of splitting but I'm not getting what I thought I would. If Ispilt file_contents[0] I get 110000, which I expect but if I change this to file_contents[1] I get 72500 (which is row 1, col1) - I would have though that I would have gotten 115000. So what am I doing wrong to separate the rows and columns so I can do calculations on them ? – May 10 '16 at 13:28
The key here is that you should be creating another list with each split operation. And that list should contain each row. If this is not working well for you, try alec_djinn's answer. It operates under the same principle but may better serve your mindset. – E Bels May 10 '16 at 18:19

score 0 · Answer 2 · answered May 10 '16 at 14:20

Assuming that your file is in plain text format and that you do not want to use a third party library like pandas then this would be the basic way to do it:

data = []

with open('InputData.csv','r') as my_file:
    for row in my_file:
        columns = row.split(',') #clean and split
        data.append([int(value) for value in columns])

print(data[0][0]) #row=0 col=0
print(data[0][1]) #row=0 col=1

martineau · Answer 3 · 2016-05-10T14:41:49.057

I think this will do what you want:

import csv

def main(filename):
    # read entire csv file into memory
    with open(filename, 'rb') as my_file:
        reader = csv.reader(my_file, delimiter=',')
        file_contents = list(reader)

    # rewrite file adding a difference column
    with open(filename, 'wb') as my_file:
        writer = csv.writer(my_file, delimiter=',')
        for row in file_contents:
            val0, val1 = map(int, row)
            difference = val1 - val0
            #print(val0, val1, difference)
            writer.writerow([val0, val1, difference])

if __name__ == '__main__':
    main('InputData.csv')

Be careful when using this because it will rewrite the file. For testing and debugging, you might want to have it write the results to a second file with a different name.

Reading from and Writing to CSV files

3 Answers3