pandas - Write CSV file using Python with the help of a csv dictionary / nested csv dictionary

Question

I am having a csv file and i want to write it to another csv file. It's a bit complicated than it seems. Hoping someone to correct my code and rewrite it, so that i can get the desired csvfile. I am using both versions python 2 and 3.

mycsvfile:

id,field1,point_x,point_y,point_z
a1,x1,10,12,3
b1,x2,20,22,5
a2,x1,25,17,7
a1,x2,35,13,3
a1,x5,15,19,9
b1,x1,65,11,2
b2,x5,50,23,1
b2,x1,75,17,7
c1,x2,70,87,2
c2,x1,80,67,4
c3,x2,85,51,6

Figure: mycsvfile

Mycode:

import os
import csv
import collections
from csv import DictWriter    

with open(r'C:UsersDesktopkar_csv_testworkfilesincsv.csv', 'r') as csvfile:
    reader = csv.reader(csvfile, delimiter=',')
    my_dict = collections.defaultdict(dict)
    for row in reader:
        my_dict[row[0]][row[1]] = [row[2],row[3],row[4]]

print (my_dict)


with open(r'C:UsersDesktopkar_csv_testworkfilesoutcsv.csv','w', newline='') as wf:
    fieldnames = ['id', 'x1(point_x)', 'x1(point_y)', 'x1(point_z)', 'x2(point_x)', 'x2(point_y)', 'x2(point_z)'] # >>>>>>etc, till x20(point_x), x20(point_y), x20(point_z)
    my_write = csv.DictWriter(wf, fieldnames = fieldnames, delimiter = ',')
    my_write.writeheader()






Desired output as csv file:

id,x1(point_x),x1(point_y),x1(point_z),x2(point_x),x2(point_y),x2(point_z)       
a1,10,12,3,35,13,3,
a2,25,17,7,,,,
b1,65,11,2,20,22,5,
b2,75,17,7,,,,
c1,,,,70,87,2,
c2,80,67,4,,,,
c3,,,,85,51,6,

Figure: Desiredcsvfile

Answer 1

This answer is for Python3 only. The csv module has a very different interface between Python2 and Python3 and writing compatible code is beyond what I am ready to do here.

Here, I would compute the fieldnames list, and compute each row on the same pattern:

...
with open(r'C:UsersDesktopkar_csv_testworkfilesoutcsv.csv','w', newline='') as wf:
    fieldnames = ['id'] + ['x{}(point_{})'.format(i, j)
                           for i in range(1, 6) for j in "xyz"] # only up to x5 here
    my_write = csv.DictWriter(wf, fieldnames = fieldnames, delimiter = ',')
    my_write.writeheader()
    for k, v in my_dict.items():
        row = {'x{}(point_{})'.format(i, k):
               v.get('x{}'.format(i), ('','',''))[j]   # get allows to get a blank triple is absent
               for i in range(1,6) for j,k in enumerate("xyz")}
        row['id'] = k                                  # do not forget the id...
        my_write.writerow(row)

With your example data, it gives:

id,x1(point_x),x1(point_y),x1(point_z),x2(point_x),x2(point_y),x2(point_z),x3(point_x),x3(point_y),x3(point_z),x4(point_x),x4(point_y),x4(point_z),x5(point_x),x5(point_y),x5(point_z)
a1,10,12,3,35,13,3,,,,,,,15,19,9
b1,65,11,2,20,22,5,,,,,,,,,
a2,25,17,7,,,,,,,,,,,,
b2,75,17,7,,,,,,,,,,50,23,1
c1,,,,70,87,2,,,,,,,,,
c2,80,67,4,,,,,,,,,,,,
c3,,,,85,51,6,,,,,,,,,