Converting vertical headers to horizontal in csv

Question

I am new to Python. I have a csv file which will generate the file in below format:

Timestamp for usage of CPU
1466707823  1466707828  1466707833

Percent use for CPU# 0
0.590551162 0.588235305 0.59055119

Percent use for CPU# 1
7.874015497 7.843137402 7.67716547

But I need to generate csv file in this format:

Timestamp for usage of CPU    Percent use for CPU# 0    Percent use for CPU# 1

1466707823                    0.590551162               7.874015497

1466707823                    0.588235305               7.843137402

1466707828                    0.59055119                7.67717547

I am not getting any idea how to proceed further. Could any one please help me out with this?

Possible duplicate of [Matrix Transpose in Python](http://stackoverflow.com/questions/4937491/matrix-transpose-in-python) — Peter Wood, Jun 27 '16 at 12:30
hello peter,no the data which i am getting is in a csv file.So when i open the file the headers are in vertical format and data is in horizontal format...but for me i want to convert headers to horizontal and data to vertical...in python i am not getting it — keerthi priya, Jun 27 '16 at 12:47

martineau · Accepted Answer · 2016-06-28T15:25:51.853

It seems like the simplest way to do it would be to first read and convert the data in the input file into a list of lists with each sublist corresponding to a column of data in the output csv file. The sublists will start off with the column's header and then be followed by the values associated with it from the next line.

Once that is done, the built-in zip() function can be used to transpose the data matrix created. This operation effectively turns the columns of data it contains into the rows of data needed for writing out to the csv file:

import csv

def is_numeric_string(s):
    """ Determine if argument is a string representing a numeric value. """
    for kind in (int, float, complex):
        try:
            kind(s)
        except (TypeError, ValueError):
            pass
        else:
            return True
    else:
        return False

columns = []
with open('not_a_csv.txt') as f:
    for line in (line.strip() for line in f):
        fields = line.split()
        if fields:  # non-blank line?
            if is_numeric_string(fields[0]):
                columns[-1] += fields  # add fields to the new column
            else:  # start a new column with this line as its header
                columns.append([line])

rows = zip(*columns)  # transpose
with open('formatted.csv', 'w') as f:
    csv.writer(f, delimiter='\t').writerows(rows)

Thanks alott Martineau.But when i have tried your code it has generated as expected but between three coloumns there is no space all values are combined — keerthi priya, Jun 28 '16 at 06:16
You're welcome. There should be a `\t` (tab) character between each column in the output file. For that reason, the columns may not appear to line up nicely depending on what you are using to view its contents. You can insert a `print(columns)` after they have been read and make sure each list created has the correct data in it (a header plus 3 _separate_ numeric values). — martineau, Jun 28 '16 at 12:21

Converting vertical headers to horizontal in csv

1 Answers1