Separate csv data from single column to multiple columns

Question

I am pretty new to python and tried searching many forums but didn't get the answer. I have a table created with tabulate and written in csv file. But I am getting data in single column, which I would like to get in multiple columns. [Sample data can be replaced] (screenshot attached)

If I try 'text to column function' in excel it does not separate data properly in column as expected.

data = [['Zone1', z1_results[0], z1_results[1], z1_results[2], z1_results[3],
     z1_results[4], z1_results[5]],
    ['Zone2', z2_results[0], z2_results[1], z2_results[2], z2_results[3],
     z2_results[4], z2_results[5]],
    ['Zone3', z3_results[0], z3_results[1], z3_results[2], z3_results[3],
     z3_results[4], z3_results[5]],
    ['Zone4', z4_results[0], z4_results[1], z4_results[2], z4_results[3],
     z4_results[4], z4_results[5]],
    ['Zone5', z5_results[0], z5_results[1], z5_results[2], z5_results[3],
     z5_results[4], z5_results[5]]]

head = ['Zone', 'M1Min (kN.m)', 'M1Max (kN.m)', 'M2Min (kN.m)', 'M2Max (kN.m)', 'M3Min (kN.m)', 'M3Max (kN.m)']

Table = tabulate(data, headers=head, tablefmt="plain")
print(tabulate(data, headers=head, tablefmt="fancy_grid"))

text_file = open("Table_summary.csv", "w", encoding='utf-8')
text_file.write(Table)
text_file.close()

screenshot

Patrick · Accepted Answer · 2022-07-09T18:15:39.890

The object returned from a tabulate is not in CSV format because it is missing the comma's "," between each value.

CSV's us a comma to determine columns and a new line character "\n" to determine rows (which is why the data would be inside one column and have the correct rows). The best way I see is:

Join the header with a comma.
Write the header to the file.
Loop through each list of datasets in the data object.
Add a new line (since it's a new row).
Join the dataset with a comma and write to the file.

As for the code, it could look like this:

from tabulate import tabulate

data = [['Zone1', z1_results[0], z1_results[1], z1_results[2], z1_results[3], z1_results[4], z1_results[5]],
    ['Zone2', z2_results[0], z2_results[1], z2_results[2], z2_results[3], z2_results[4], z2_results[5]],
    ['Zone3', z3_results[0], z3_results[1], z3_results[2], z3_results[3], z3_results[4], z3_results[5]],
    ['Zone4', z4_results[0], z4_results[1], z4_results[2], z4_results[3], z4_results[4], z4_results[5]],
    ['Zone5', z5_results[0], z5_results[1], z5_results[2], z5_results[3], z5_results[4], z5_results[5]]]

head = ['Zone', 'M1Min (kN.m)', 'M1Max (kN.m)', 'M2Min (kN.m)', 'M2Max (kN.m)', 'M3Min (kN.m)', 'M3Max (kN.m)']

Table = tabulate(data, headers=head, tablefmt="plain")
print(tabulate(data, headers=head, tablefmt="fancy_grid"))

text_file = open("Table_summary.csv", "w", encoding='utf-8')

# Write header to file
headerCSV = ','.join(head)
text_file.write(headerCSV)

# Loop through each row in the data object
for row in data:

    # Add a new line
    text_file.write("\n")

    # Write row to file
    rowCSV = ','.join(map(str(row))
    text_file.write(rowCSV)   

text_file.close()

For testing I replaced the variables inside the data object with dummy data:

data = [['Zone1', "11", "12", "13", "14","15","16"],
    ['Zone2', "21", "22", "23", "24","25","26"],
    ['Zone3', "31", "32", "33", "34","35","36"],
    ['Zone4', "41", "42", "43", "44","45","46"]]

This was also ran in Python3.

Thanks for a quick reply. My results are float typeand I am getting the error: "TypeError: sequence item 1: expected str instance, numpy.float64 found". How can I get over it? — Omkar S, Jul 09 '22 at 17:55
Easy, just need to conver the float64 to a string using the map() function. Will update the answer above. — Patrick, Jul 09 '22 at 18:02
thanks, understood. By the way what is the purpose of fileContent in this code? — Omkar S, Jul 09 '22 at 18:11
Glad I could help. That varibale serves no purpose. I accedently left that those 3 lines in there as I was testing out some alternate solutions which didn't work. — Patrick, Jul 09 '22 at 18:14

Separate csv data from single column to multiple columns

1 Answers1