3

I am trying to use Python to write to an existing .csv file but instead of appending the data to the bottom of the existing file, I would like to append the new information to a new column of the .csv file. The reason for this is that I am wanting to "infinitely" loop through a read data section of code and then add the data read during each loop iteration to a new column, instead of row. In essence, I would like something as follows:

Row1Iteration1, Row1Iteration2, Row1Iteration3,..., Row1IterationX
Row2Iteration1, Row2Iteration2, Row2Iteration3,..., Row2IterationX
Row3Iteration1, Row3Iteration2, Row3Iteration3,..., Row3IterationX
Row4Iteration1, Row4Iteration2, Row4Iteration3,..., Row4IterationX
Row5Iteration1, Row5Iteration2, Row5Iteration3,..., Row5IterationX

Instead of:

Row1Iteration1
Row2Iteration1
Row3Iteration1
Row4Iteration1
Row5Iteration1
Row1Iteration2
Row2Iteration2
Row3Iteration2
etc...

Does anyone know of a way to do this or is this an impossible constraint of .csv files? Is there another way, besides .csv, that I may be able to do this and still manipulate with Excel?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
H0ckeyfr33k99
  • 145
  • 2
  • 4
  • 13
  • 2
    It is important to notice that, in almost EVERY case, in any language, when people say "writing to the same file", what happens indeed is that the file is read and loaded to memory, its content is manipulated (for example, by adding stuff at the end), and this content is written again to disk, OVERWRITING the old file. What you most probably should do is to create another file to store your disired modified content. – heltonbiker Nov 12 '12 at 18:44
  • @heltonbiker: it is true (if you're not appending) but some API can hide it from its users e.g., [`fileinput` module](http://ideone.com/nZfY2F) – jfs Nov 12 '12 at 20:04

3 Answers3

2

This is simply not possible on any system or file type that I know of. You'll have to read the data in, manipulate it and save/overwrite the original file. This is just how file I/O works on pretty much any operating system. You can read files, (over)write files and append to the end of files, that's all.

If your file is very big, you could read it in piece by piece, make your manipulations and write those to a temporary file. Once you're done, the original file can be replaced with the new temporary file.

Chinmay Kanchi
  • 62,729
  • 22
  • 87
  • 114
  • It interesting to mention that python has a `'r+'` mode for reading and writing. That would theoretically allow to open a file, seek to a position and replace content by overwriting bytes. Of course, in almost all the cases this is not useful, unless you know the specific positions inside the file. – heltonbiker Nov 12 '12 at 18:47
  • True, but it still only allows you to overwrite, not append. So the number of bytes in the file remains the same (unless you write past the end of the file). – Chinmay Kanchi Nov 12 '12 at 18:49
  • In the case of appending I think the `'a'` mode is best, specially because then it's not possible to accidentaly damage the previous content of the file. – heltonbiker Nov 12 '12 at 18:57
  • Ok so following this same train of thought, is it theoretically possible to "overwrite" the first loop iteration's data with the first loop iteration's data + second loop iteration's data and continue that trend for x iterations? – H0ckeyfr33k99 Nov 12 '12 at 18:58
  • @heltonbiker: I am using 'a' mode currently, but it unfortunately does exactly what I have stated above (appending to new rows in the .csv instead of columns). I have ~30 rows of data streaming in so manually moving each iteration's data to a new column would be tedious for 100+ iterations. Just looking for an easier solution. – H0ckeyfr33k99 Nov 12 '12 at 19:00
  • If it's just ~30-ish rows for a 100 iterations, you should be able to store everything in RAM as a `list`, then re-arrange your rows however you want before writing to a file. – Chinmay Kanchi Nov 12 '12 at 19:24
  • PS: Looks like you need to refactor your loop. – Chinmay Kanchi Nov 12 '12 at 19:24
  • @H0ckeyfr33k99 you cannot modify the columns in append mode, since the file is saved in row order. Use what people are advising: load file content to memory, manipulate it and then save it to (another) file. – heltonbiker Nov 12 '12 at 19:26
2

I hope this example helps you:

# of course you should read the file someway
s = """
Row1Iteration1, Row1Iteration2, Row1Iteration3
Row2Iteration1, Row2Iteration2, Row2Iteration3
Row3Iteration1, Row3Iteration2, Row3Iteration3
Row4Iteration1, Row4Iteration2, Row4Iteration3
Row5Iteration1, Row5Iteration2, Row5Iteration3
"""
ncols = 3

iterations = []
for line in s.strip().split('\n'):
    iterations.append([l.strip() for l in line.split(',')])

with open('output.csv', 'w') as out:
    for col in xrange(ncols):
        for it in iterations:
            print it[col]
            out.write(col)
heltonbiker
  • 26,657
  • 28
  • 137
  • 252
  • I intentionally didn't use the `csv` module, so as to be more pedagogic, I think. If you don't understand some of the idioms or syntax, please ask! – heltonbiker Nov 12 '12 at 19:40
  • Thank you for your help heltonbiker. I am going to adapt the answers given into something that I can use. – H0ckeyfr33k99 Nov 12 '12 at 20:58
0

As Chinmay said your best bet might be to read in the csv file, massage it around, then output it back to csv. Take a look at this stackoverflow discussion that seems to address something similar to what you are asking about.

Community
  • 1
  • 1
JonathanV
  • 2,484
  • 1
  • 13
  • 9