how to write a matrix to a csv file in python with adding static headers in first row and first column?

Question

I have a matrix which is generated after running a correlation - mat = Statistics.corr(result, method="pearson"). now I want to write this matrix to a csv file but I want to add headers to the first row and first column of the file so that the output looks like this:

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

I have a list which contains the columns names - colmn = ['col1','col2','col3','col4','col5','col6']. The index in the above format is a static string to indicate the index names. i wrote this code but it only add the header in first row but i am unable to get the header in the first column as well:

with open("file1", "wb") as f:
        writer = csv.writer(f,delimiter=",")
        writer.writerow(['col1','col2','col3','col4','col5','col6'])
        writer.writerows(mat)

How can I write the matrix to a csv file with heading static headers to the first row and 1st column?

Possible duplicate of Adding row/column headers to Numpy Matrices — Ulf Aslak
– Ulf Aslak, Commented Apr 2, 2016 at 19:52

Ilja Everilä · Accepted Answer · 2016-04-02 19:57:45Z

2

You could use pandas. DataFrame.to_csv() defaults to writing both the column headers and the index.

import pandas as pd
headers = ['col1','col2','col3','col4','col5','col6']
df = pd.DataFrame(mat, columns=headers, index=headers)
df.to_csv('file1')

If on the other hand this is not an option, you can add your index with a little help from enumerate:

with open("file1", "wb") as f:
    writer = csv.writer(f,delimiter=",")
    headers = ['col1','col2','col3','col4','col5','col6']
    writer.writerow(['index'] + headers)
    # If your mat is already a python list of lists, you can skip wrapping
    # the rows with list()
    writer.writerows(headers[i:i+1] + list(row) for i, row in enumerate(mat))

edited Apr 2, 2016 at 19:57

answered Apr 2, 2016 at 19:52

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

2 Comments

user2966197 Over a year ago

When I use this I see few values as empty. Actually some of values in my matrix are nan and I think thats why when writing they are getting empty. Is it possible I can write 0 in place of keeping them empty while writing?

Ilja Everilä Over a year ago

Pass na_rep='0' to to_csv.

user6072577 · Accepted Answer · 2016-04-02 20:34:20Z

0

You can use a first variable to indicate the first line, and then add each row name to the row as it is written:

cols = ["col2", "col2", "col3", "col4", "col5"]

with open("file1", "wb") as f:
    writer = csv.writer(f)
    first = True
    for i, line in enumerate(mat):
        if first:
            writer.writerow(["Index"] + cols)
            first = False
        else:
            writer.writerow(["Row"+str(i)] + line)

answered Apr 2, 2016 at 20:34

user6072577

3495 silver badges12 bronze badges

Collectives™ on Stack Overflow

how to write a matrix to a csv file in python with adding static headers in first row and first column?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related