In python 2.7 - How to read data from csv, reformat data, and write to new csv

Question

I'm a programing noob, and I'm stuck... My goal is to open a csv file (an export from program A), reformat the data, and write it to a new csv file (for program B to import). I know my code isn't pretty, though it works up until it writes to the new csv file. It only writes the last row of data from the old csv.

import csv

print 'Enter file location or press enter for default location.'
aPath = raw_input('> ')  # user path to file
if aPath == '':
    aPath = '/default/path/to/file'  # default path
aFile = raw_input('Enter file name: ')  # user file name
if aFile == '':
    aFile = 'orginal.csv'  # default file name
myCSV = csv.DictReader(open(aPath + aFile, 'r'), delimiter=',')
print myCSV.fieldnames # verify fieldnames

for p in myCSV:
    w = csv.writer(open("output.csv", "w"))
    try:
        p = dict((k, v) for k, v in p.iteritems()
            if v.lower() != 'null')
    except AttributeError, e:
        print e
        print p
        raise Exception()
# reformats the columns of data for the output.csv
    pID = p.get('Last Name')[-4:]
    pName = p.get('Last Name')[:-4].strip() + ', ' + p.get('First Name')
    pDate = p.get('Start Time')[:-5]
    pBlank = p.get('')
    pCourse = p.get('Assigned Through')
    pScore = p.get('Score')[:-1]

# verifies the new columns
    print pID, pName, pDate, pBlank, pCourse, pBlank, pBlank, pScore

# NOT working...Only writes the last row of the orginal.csv to output.csv
    w.writerow([pID, pName, pDate, pBlank, pCourse, pBlank, pBlank, pScore])

Check your `w = csv.writer...` line. Are you sure it should be *inside* the `for` loop? This means the file is opened again and again and the row is overwritten again and again. — WGS, Apr 28 '14 at 21:36
Thank you @Nanashi, that fix it; however I'm getting a blank row between each row with data. — cstafford, Apr 28 '14 at 22:04

score 3 · Answer 1 · answered Apr 28 '14 at 21:35

3

You are re-opening the file and overwriting it on each pass through the for p in myCSV loop. You need to create w once before entering the for loop.

answered Apr 28 '14 at 21:35

Travis D.

342
1
9

score 1 · Accepted Answer · edited May 23 '17 at 10:26

The following modified code should work:

import csv

print 'Enter file location or press enter for default location.'
aPath = raw_input('> ')  # user path to file
if aPath == '':
    aPath = '/default/path/to/file'  # default path
aFile = raw_input('Enter file name: ')  # user file name
if aFile == '':
    aFile = 'orginal.csv'  # default file name
myCSV = csv.DictReader(open(aPath + aFile, 'r'), delimiter=',')
print myCSV.fieldnames # verify fieldnames

with open("output.csv", "wb") as ofile:
    w = csv.writer(ofile)
    for p in myCSV:
        try:
            p = dict((k, v) for k, v in p.iteritems()
                if v.lower() != 'null')
        except AttributeError, e:
            print e
            print p
            raise Exception()
    # reformats the columns of data for the output.csv
        pID = p.get('Last Name')[-4:]
        pName = p.get('Last Name')[:-4].strip() + ', ' + p.get('First Name')
        pDate = p.get('Start Time')[:-5]
        pBlank = p.get('')
        pCourse = p.get('Assigned Through')
        pScore = p.get('Score')[:-1]

    # verifies the new columns
        print pID, pName, pDate, pBlank, pCourse, pBlank, pBlank, pScore

    # NOT working...Only writes the last row of the orginal.csv to output.csv
        w.writerow([pID, pName, pDate, pBlank, pCourse, pBlank, pBlank, pScore])

Things we changed:

We used the with operator. When working with opening and closing files, using with is a generally accepted protocol to follow unless otherwise inapplicable since it handles the closing of files properly, among other things.
Use wb instead of just w as your second argument to open. This will allow you to open the file in write-binary mode. See this for reference.

Hope this helps.

Works perfectly! Thank you again @Nanashi. This community is the greatest! — cstafford, Apr 28 '14 at 22:21
You're welcome and good luck. As a final aside, check out the Pandas library when you have time. You might find it extremely useful for CSV manipulations like this. :) — WGS, Apr 28 '14 at 22:25

score 0 · Answer 3 · answered Apr 28 '14 at 21:44

I'm afraid you're re-writing your output file at each iteration ... You should simply take the line

w = csv.writer(open("output.csv", "w"))

out of the loop as :

w = csv.writer(open("output.csv", "w"))
try:
    for p in myCSV:
        try:
            ...
            w.writerow([pID, pName, pDate, pBlank, pCourse, pBlank, pBlank, pScore])
finally:
    w.close()

You could also have a look to "with" constructs that automatically cope with potential io errors and close the file at end of block with no need for the additional try.

In python 2.7 - How to read data from csv, reformat data, and write to new csv

3 Answers3

Linked