formatting python output file in such a way that columns can be accessed later

Question

In the program I am working on I am reading in data from a .xls filethat looks like this

2012-SEP-27     04:35:00        0.035173        0.019666        0.001566        -0.002356       0.028054  0.009819        -9999.000000    0.036353        0.021943        0.003582        -0.0006520.025411 0.009548        -9999.000000    0.035703        0.019268        0.003582        -0.0005340.040426 0.010700        -9999.000000    56.904729       95.722267       7.827207

braking it into columns doing some screening of the numbers (this includes grouping certain columns together into 'filters') and then trying to write it back to another .xls file. I can write it to a file fine and the output file looks the same as the input file, but when I try to access separate columns I either can't (it sees the whole thing as a column) or I can only access it character by character.

I've tried formatting the output a number of ways including:

AA = ('{0}     {1}     {2}     {3}     {4}     {5}     {6}     {7}     {8}     {9}     {10}    {11}    {12}    {13}    {14}    {15}    {16}    {17}    {18}    {19}    {20}    {21}    {22}    {23}    {24}    {25}'.format(date, time, filter1[0], filter1[1], filter1[2], filter2[0], filter2[1], filter2[2], filter3[0], filter3[1], filter3[2], filter4[0], filter4[1], filter4[2], filter5[0], filter5[1], filter5[2], filter6[0], filter6[1], filter6[2], filter7[0], filter7[1], filter7[2], LZA, SZA, LAM) + '\n')

AA = [date, time, float(filter1[0]), float(filter1[1]), float(filter1[2]), filter2[0], filter2[1], filter2[2], filter3[0], filter3[1], filter3[2], filter4[0], filter4[1], filter4[2], filter5[0], filter5[1], filter5[2], filter6[0], filter6[1],filter6[2], filter7[0], filter7[1],filter7[2],LZA, SZA, LAM]


AA = [(date, time, filter1[0], filter1[1], filter1[2], filter2[0], filter2[1], filter2[2], filter3[0], filter3[1], filter3[2], filter4[0], filter4[1], filter4[2], filter5[0], filter5[1], filter5[2], filter6[0], filter6[1], filter6[2], filter7[0], filter7[1], filter7[2], LZA, SZA, LAM)]

I'm looking for some general help on writing data into a file in such a way that it is accessible by columns, the code I'm working on is hard to explain concisely so general examples are fine. I am new to python and appreciate your help and patience

edit

                writer = csv.writer(outputfile, delimiter = '\t')
                row = [date, time]
                row.extend(map(float, filter1))
                for filter in (filter2, filter3, filter4, filter5, filter6, filter7):
                        row.extend(filter)
                row.extend([LZA, SZA, LAM])
                writer.writerow(row)

gives me an output that looks like

2012-SEP-27     04:35:00        -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -.0      56.904729       95.722267       "7.827207
"
2012-SEP-27     04:39:00        -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 -9999.0 0.0      -9999.0 56.007862       96.708467       "1.784808
"
2012-SEP-27     04:43:00        0.036885        0.038065        0.047741        0.021099        0.0      0.035384        0.034978        0.045806        0.019952        0.013064        0.021955
"
2012-SEP-27     04:47:00        0.038996        0.039075        0.04026 0.020357        0.012688 .036156 0.033027        0.011406        0.011693        0.011979        -9999.0 -9999.0 -9999.0 5
"
2012-SEP-27     04:51:00        -9999.0 -9999.0 -9999.0 0.019505        0.016133        0.018253 146     0.043547        0.012732        0.014486        0.015731        -9999.0 -9999.0 -9999.0 5
"

I am trying to access each column using

from __future__ import division
import csv
v = open("Pt_2_Test_Data.csv", 'wb') 

with open("outputfile.csv", 'rb') as w:
        reader = csv.reader(w, delimiter = '\t', quotechar = '|')

        for row in w:
                columns = row.split(',')
                date = columns[0]
                time = columns[1]

print date will give me the dates, however anything other then columns[0] gives this error

    time = columns[1]
IndexError: list index out of range

Have you considered installing Pandas? I use it very frequently for tabular data, in conjunction with the iPython HTML notebook, and it has proven useful for slicing and dicing data. It also provides a few simple functions (e.g. .to_csv('filename.csv')) for easy writing of data to CSV format. Buzz me if you'd like help getting started with it. — ericmjl
– ericmjl, Commented Jul 10, 2013 at 15:03

Martijn Pieters · Accepted Answer · 2013-07-10 14:52:35Z

1

Use the csv module and write tab delimited values:

import csv

with open('outputfile.csv', 'wb') as outputfile:
    writer = csv.writer(outputfile, delimiter='\t')
    row = [date, time]
    row.extend(map(float, filter2))
    for filter in (filter2, filter3, filter4, filter5, filter6, filter7):
        row.extend(filter)
    row.extend([LZA, SZA, LAM])
    writer.writerow(row)

Your input file most likely used tab-delimited columns as well, not multiple spaces.

answered Jul 10, 2013 at 14:52

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

KJo Over a year ago

how would I get it to iterate for each row? I tried adding "for row in w:" (where w = the data file) before the last line but it printed everything out character by character

Martijn Pieters Over a year ago

Everything beyond the writer = line should really be done per row of course; your old code presumably handled things per line of data from the w file? It all comes down to you creating a list of columns to pass to writer.writerow() for each line in the output file.

KJo Over a year ago

got it, I had stupidly placed the "with open(.....)" line inside the loop so it was getting written over everytime. thanks again

KJo Over a year ago

so now if I want to access a column of data in another body of code (so from outputfile.csv) how would I do that?

KJo Over a year ago

again, when I look at the file it looks like it's formatted correctly, but when I try to access it by columns in another code using line or row.split("\t") I can't.

|

Collectives™ on Stack Overflow

formatting python output file in such a way that columns can be accessed later

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related