with python write to csv file from csv dictionary

Question

I am trying to write a CSV file from a CSV dictionary. It's a bit more complicated than it seems. Please correct my code so that I can get the desired CSV file.

My csv file:

id;Remarks;x;y;z
a1;Mv_biw;10;12;3
b1;Ins_slt_po_zd;20;22;5
a2;Mv_biw;25;17;7
a1;Ins_slt_po_zd;35;13;3
a1;Ins_slt_dkz;15;19;9
b1;Mv_biw;65;11;2
b2;Ins_slt_dkz;50;23;1
b2;Mv_biw;75;17;7

My code so far:

import os
import csv
import collections
from csv import DictWriter    

with open(r'C:\incsv_new.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:\outcsv_new.csv','w', newline='') as wf:
    fieldnames = ['ID','x_Mv_biw', 'y_Mv_biw', 'z_Mv_biw', 'x_Ins_slt_po_zd', 'y_Ins_slt_po_zd', 'z_Ins_slt_po_zd', 'x_Slb_po_zd', 'y_Slb_po_zd', 'z_Slb_po_zd', 'x_Slb_dkz', 'y_Slb_dkz', 'z_Slb_dkz', 'x_Ins_slt_dkz', 'y_Ins_slt_dkz', 'z_Ins_slt_dkz'] 
    my_write = csv.DictWriter(wf, fieldnames = fieldnames, delimiter = ';')
    my_write.writeheader()

Desired output CSV file:

id;x_Mv_biw;y_Mv_biw;z_Mv_biw;x_Ins_slt_po_zd;y_Ins_slt_po_zd;z_Ins_slt_po_zd;x_Slb_po_zd;y_Slb_po_zd;z_Slb_po_zd;x_Slb_dkz;y_Slb_dkz;z_Slb_dkz;x_Ins_slt_dkz;y_Ins_slt_dkz;z_Ins_slt_dkz
a1;10;12;3;35;13;3;-1;-1;-1;-1;-1;-1;15;19;9
a2;25;17;7;-1;-1;-1;-1;-1;-1;-1;-1;-1;-1;-1;-1
b1;65;11;2;20;22;5;-1;-1;-1;-1;-1;-1;-1;-1;-1
b2;75;17;7;-1;-1;-1;-1;-1;-1;-1;-1;-1;50;23;1

Answer 1

You would need to loop over my_dict and the construct the dictionary for each row. Your dictionary keys also need to be constructed for each of the 3 elements, in this case zip() is used to merge each value with x y and z:

import os
import csv
import collections
from csv import DictWriter    

with open(r'incsv_new.csv', 'r', newline='') as csvfile:
    reader = csv.reader(csvfile, delimiter=';')
    next(reader)    # skip over the header
    my_dict = collections.defaultdict(dict)

    for row in reader:
        my_dict[row[0]][row[1]] = [row[2], row[3], row[4]]

with open(r'outcsv_new.csv', 'w', newline='') as wf:
    fieldnames = ['ID','x_Mv_biw', 'y_Mv_biw', 'z_Mv_biw', 'x_Ins_slt_po_zd', 'y_Ins_slt_po_zd', 'z_Ins_slt_po_zd', 'x_Slb_po_zd', 'y_Slb_po_zd', 'z_Slb_po_zd', 'x_Slb_dkz', 'y_Slb_dkz', 'z_Slb_dkz', 'x_Ins_slt_dkz', 'y_Ins_slt_dkz', 'z_Ins_slt_dkz'] 
    my_write = csv.DictWriter(wf, fieldnames=fieldnames, delimiter=';', restval=-1)
    my_write.writeheader()

    for id, values in my_dict.items():
        row = {'ID':id}

        for key, values_xyz in values.items():
            for xyz, value in zip("xyz", values_xyz):
                row[f'{xyz}_{key}'] = value

        my_write.writerow(row)

Giving you an output CSV file:

ID;x_Mv_biw;y_Mv_biw;z_Mv_biw;x_Ins_slt_po_zd;y_Ins_slt_po_zd;z_Ins_slt_po_zd;x_Slb_po_zd;y_Slb_po_zd;z_Slb_po_zd;x_Slb_dkz;y_Slb_dkz;z_Slb_dkz;x_Ins_slt_dkz;y_Ins_slt_dkz;z_Ins_slt_dkz
a1;10;12;3;35;13;3;-1;-1;-1;-1;-1;-1;15;19;9
b1;65;11;2;20;22;5;-1;-1;-1;-1;-1;-1;-1;-1;-1
a2;25;17;7;-1;-1;-1;-1;-1;-1;-1;-1;-1;-1;-1;-1
b2;75;17;7;-1;-1;-1;-1;-1;-1;-1;-1;-1;50;23;1

The header row should also be skipped whilst reading.