Reading and formatting CSV file (New Line Insertion)

Question

I have a CSV file that has numerous data points included in each row, despite belonging to the same column. Something similar to this:

A, B, C, X, Y, Z

Now, what I would like to do is to reformat the file such that the resulting CSV is:

A, B, C
X, Y, Z

I'm not too sure how to go about this / express it in a pythonic way. One idea (which I wasn't sure how to code) was to create a for loop similar to

For elements in file:
   new_csv = []
   counter = 0
   max_counter = 3

   ##I'm using english because I dont know how to express it in python syntax
   take first element in CSV and add 1 to counter
   append first element to new_csv
   is counter < max_counter?
       if yes: go to next element
       if no: append /n to new csv then go to next element

This probably is a terrible way to do it, but I'm struggling to find another way. How would I express this in the python syntax? Any ideas?

do you mean you want to split the six elements into two three element lines? — Nick Beeuwsaert, Feb 14 '14 at 16:01
OK then, can you format your CSV lines as code? right now the two lines are on the same line — Nick Beeuwsaert, Feb 14 '14 at 16:04
Have a look at this post on stackoverflow [here][1] [1]: http://stackoverflow.com/questions/9777409/adding-a-line-to-a-csv-file-without-changing-its-format-python — Zuko, Feb 14 '14 at 16:12

score 3 · Answer 1 · edited May 23 '17 at 11:57

3

Just write two rows to an output csv.writer() object:

with open(inputfilename, 'rb') as infh, open(outputfilename, 'wb') as outfh:
    reader = csv.reader(infh)
    writer = csv.writer(outfh)

    for row in reader:
        writer.writerows([row[:3], row[3:]])

Each row the csv.reader() object produces is a list object, so all we have to do is using the Python slice notation to slice of a new list object of the first 3 elements, and another list object for the remaining 3 elements.

edited May 23 '17 at 11:57

Community

1
1

answered Feb 14 '14 at 16:10

Martijn Pieters

1,048,767
296
4,058
3,343

I am really unsure about the `"wb"` for writing csv. The documentation doesn't use this in the examples and I think you loose the encoding facility in this case. This supposes you manipulate bytes all the way (and only works because you read also with `rb`, or you get `TypeError: 'str' does not support the buffer interface`) – Cilyan Feb 14 '14 at 16:35
@Cilyan: Python 2 recommends you use `rb` so that the CSV reader and writer objects fully control line endings. Python 3 uses `newline=''` instead, to the same purpose. So the recommendation differs between the Python versions here. – Martijn Pieters Feb 14 '14 at 16:39
@Cilyan: Since Python 2 is still used by a majority of people, I stick to that; the `csv.writer` documentation link goes to the Python 2 documentation, you may note. – Martijn Pieters Feb 14 '14 at 16:40

score 1 · Answer 2 · answered Feb 14 '14 at 16:11

1

You could probably use python's CSV module

Example:

#!/usr/bin/env python
import csv

with open("input.csv", "r") as input_file, open("output.csv", "w+"):
    input_csv, output_csv = csv.reader(input_file), csv.writer(output_file);
    for row in input_csv:
        output_csv.writerows([row[:len(row)/2],row[len(row)/2]]);

Which given the input

a,b,c,d,e,f
A,B,C,D,E,F,G
1,2,3,4,5,6

will output

a,b,c
d,e,f
A,B,C
D,E,F,G
1,2,3
4,5,6

answered Feb 14 '14 at 16:11

Nick Beeuwsaert

1,598
1
11
18

I think there are several typos here (missing target for the second open, missing colon.) – DSM Feb 14 '14 at 16:49

score 0 · Answer 3 · edited May 23 '17 at 10:25

This solution is for Python 3. For Python 2, see Martijn Pieters's answer (differs in the way files are opened).

Python has a module to read and write csv files. There are some nice examples in the official documentation on how to use them. (Note the newline="" for output file, this lets the csv module take care of line return, which according to the specification should be \r\n, \n being used for line feed in a cell.)

While reading, you then split the rows with slicing and feed the parts in the writer.

import csv

# with ensures files are closed when leaving block
with open("in.csv", "r") as fin, open("out.csv", "w", newline="") as fout:
    reader = csv.reader(fin, delimiter=",")
    writer =csv.writer(fout, delimiter=",", quotechar='"', quoting=csv.QUOTE_MINIMAL)
    # Rows are each line of the csv returned as a list
    for row in reader:
        # Separate the row:
        # from beginning to 3rd element, and from 3rd element to end
        writer.writerows((row[:3], row[3:])

Reading and formatting CSV file (New Line Insertion)

3 Answers3