Python csv rearrange columns and calling from a dictionary (no pandas)

user_18421201 0
Viewed 50

I need the code to rearrange the columns while changing the header names without using Pandas. I also need to omit some of the columns in the new csv file.

Sample Input csv file:

    Alfa,Beta,Charlie,Delta,Echo,Foxtrot,Golf,Hotel,India,Juliett,Kilo
    A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1
    A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2
    A3,B3,C3,D3,E3,F3,G3,H3,I3,J3,K3
    A4,B4,C4,D4,E4,F4,G4,H4,I4,J4,K4
    A5,B5,C5,D5,E5,F5,G5,H5,I5,J5,K5
    A6,B6,C6,D6,E6,F6,G6,H6,I6,J6,K6
    A7,B7,C7,D7,E7,F7,G7,H7,I7,J7,K7
    A8,B8,C8,D8,E8,F8,G8,H8,I8,J8,K8
    A9,B9,C9,D9,E9,F9,G9,H9,I9,J9,K9

What I have so far:

import csv

#Opens and readers csv file
open_cd_csv = open("book1.csv", "r", encoding="utf-8", errors='ignore')
reader_cd_csv = csv.DictReader(open_cd_csv, delimiter=',', quotechar='"')
header_csv = next(reader_cd_csv)

#dictionary sample
dictionary_sample = {
    "Beta_New" : reader_cd_csv["Beta"],
    "Echo_New" : reader_cd_csv["Echo"],
    "Foxtrot_New_All" : reader_cd_csv["Foxtrot"],
    "Hotel_New" : reader_cd_csv["Hotel"],
    "India_New" : reader_cd_csv["India"],
    "Charlie_New" : reader_cd_csv["Charlie"],
    }
    
#Opens and writes csv file
output_test_csv_file = "xtest_file.csv"
open_output_test_csv = open(output_test_csv_file, "w", encoding="utf-8", errors='ignore')
writer_output_test_csv = csv.writer(open_output_test_csv, delimiter=',', quotechar='"')


sample_list = []
for row in reader_cd_csv:
    sample_list.append(dictionary_sample(row))
    writer_output_test_csv.writerow(sample_list)

What the Output csv should look like:

Beta_New,Echo_New,Foxtrot_New_ALL,Hotel_New,India_New,Charlie_New
B1,E1,F1,H1,I1,C1
B2,E2,F2,H2,I2,C2
B3,E3,F3,H3,I3,C3
B4,E4,F4,H4,I4,C4
B5,E5,F5,H5,I5,C5
B6,E6,F6,H6,I6,C6
B7,E7,F7,H7,I7,C7
B8,E8,F8,H8,I8,C8
B9,E9,F9,H9,I9,C9
csv python
1 Answers

You want something like this:

import csv

fieldnames_dict = {
    'Beta': 'Beta_New',
    'Echo': 'Echo_New',
    'Foxtrot': 'Foxtrot_New_ALL',
    'Hotel': 'Hotel_New',
    'India': 'India_New',
    'Charlie': 'Charlie_New'
}

with open("book1.csv", "r", encoding="utf-8", errors='ignore') as csv_in:
    with open("xtest_file.csv", "w", encoding="utf-8", errors='ignore') as csv_out:
        reader = csv.DictReader(csv_in, delimiter=',', quotechar='"')
        writer = csv.DictWriter(csv_out, delimiter=',', quotechar='"',
                                fieldnames=list(fieldnames_dict.values()))
        writer.writeheader()
        for row_in in reader:
            row_out = {new: row_in[old] for old, new in fieldnames_dict.items()}
            writer.writerow(row_out)

Please note that your line header_csv = next(reader_cd_csv) should not be there, because it skips the first data line, not the header line, that is read automatically to learn the column keys.

Also note that you use dictionary_sample like a function, but it's a dict instead – a dict that doesn't work, because reader_cd_csv is a DictReader, and cannot be indexed with square brackets.

It's good practice to open files in a with statement, which provides a context that causes the file to be closed when the context is exited. In this case the contexts for reading and writing are nested, since I chose reading and writing to be done "at the same time".

I used a DictWriter for the output, which is the counterpart of the DictReader. Please refer to the documentation for how to use both. All that is needed in addition to that is to transform the input dictionary of each row into its corresponding output dictionary, which I did with a dict comprehension.

user_10460070
Related