How to write a matrix (read from one file) to another csv file in python with a specific output format

Question

I have a csv file which has data in matrix format a sample of which is shown below:

index,col1,col2,col3,col4,col5,col6
col1_1,1,0.005744233,0.013118052,-0.003772589,0.004284689
col2_1,0.005744233,1,-0.013269414,-0.007132092,0.013950261
col3_1,0.013118052,-0.013269414,1,-0.014029249,-0.00199437
col4_1,-0.003772589,-0.007132092,-0.014029249,1,0.022569309
col5_1,0.004284689,0.013950261,-0.00199437,0.022569309,1

No I want to read the data in this file and write it to another csv file but the format I need is this:

col1_1,value,col1
col1_1,value,col2
col1_1,value,col3
.
.
.
col2_1,value,col1
col2_1,value,col2
.
.
.

So basically 1st element will be the column names in 1st column followed by value for that column and element in 1st row.

I wrote this code but it just writes in the wrong format:

reader = csv.reader(open(IN_FILE, "r"), delimiter=',')
writer = csv.writer(open(OUT_FILE, "w"), delimiter=',')

with open(IN_FILE) as infile:
    with open(OUT_FILE, "w") as outfile:
        reader = csv.reader(infile, delimiter=",")
        writer = csv.writer(outfile, delimiter=",")
        writer.writerow(next(reader))
        for line in reader:
            writer.writerow([line[0],line[1]])

How can I do this in python?

@TomRees Yes memory is not a problem. The file is small enough to be read into memory in one go — user2966197
– user2966197, Commented Apr 1, 2016 at 15:46

Tom Rees · Accepted Answer · 2016-04-01 15:56:45Z

1

Try this:

reader = csv.reader(open(IN_FILE, "r"), delimiter=',')
writer = csv.writer(open(OUT_FILE, "w"), delimiter=',')

with open(IN_FILE) as infile:
    with open(OUT_FILE, "w") as outfile:
        reader = csv.reader(infile, delimiter=",")
        writer = csv.writer(outfile, delimiter=",")

        first_row = None
        for line in reader:
            if first_row is None:
                first_row = line
            else:
                for index, col in enumerate(first_row[1:]):
                    writer.writerow([line[0],line[index + 1],col])

This seems to work. Although your test data looked to be missing a 'col6'.

The problem with your initial code was that it wasn't looping through each column the rows.

edited Apr 1, 2016 at 15:56

answered Apr 1, 2016 at 15:48

Tom Rees

4552 silver badges10 bronze badges

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

Comments

Python Cheese · Accepted Answer · 2016-04-01 16:01:34Z

0

If your file includes the column and row indices like I assume, this should do it.

old_data = reader
new_data = []

for row in xrange(0,len(old_data)):
    for col in xrange(0,len(row)):
        if (not row == 0 and not col == 0):
            new_data.append([old_data[row][0],old_data[row][col],old_data[0][col]])

writer.writerows(new_data)
csv_file.close()

answered Apr 1, 2016 at 16:01

Python Cheese

1572 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to write a matrix (read from one file) to another csv file in python with a specific output format

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related