Elias Cort Aguelo Elias Cort Aguelo -4 years ago 90
PHP Question

Split one column csv data into multiple columns by number of rows

I'm new on this site, hope I'm doing my question properly and on the right side, if not, please any tip will be appreciated.

Just need some guidance with a problem.

I've a csv file like this:

| Column1|
----------
abc
def
ghi
12,34
32,67
jkl
mno
pqr
28,34
98,67


(really bad file)

And I want to transform into this kind of csv:

Something1 | Something2 | Something3 | Something4 | Something5
---------------------------------------------------------------
abc | def | ghi | 12,34 | 32,67
jkl | mno | pqr | 28,34 | 98,67


There're a total of 15 types of data on 15 consecutive rows on variable number of repeat times and I need to split'em into 15 columns in a new csv file.

My first tought was making a bash script with a function to count lines and split the data by the number of line into a new csv file but I realized the fact maybe it's better to do it using another way or use some pythonic way (with pandas and numpy ) or a PhP webservice (
fopen
and exploding data or something like that) because this is not going to be the last time I get that kind of trash csv file...

But I need some guidance to start with.

Some help will be kindly appreciated.

Answer Source

This should work: haven't tested so there may be some minor bug

from csv import writer

COLUMNS = 15

with open("input_file.csv", "r") as input:
    with open("output_file.csv", "w") as f:
        output = writer(f, delimiter=";")
        output.writerow(["Col {}".format(i+1) for i in xrange(COLUMNS)])
        buffer = []
        for row in input:
            buffer.append(row)
            if len(buffer) == COLUMNS:
                output.writerow(buffer)
                del buffer[:]
        // You may want to check if there is something inside buffer at the end, for example if it has 23 rows buffer here will contain 8 elements and you may want to append them with: output.writerow(buffer)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download