Change Column Headers using DictWriter

Question

I am working on some code to produce two new CSV files from a input .txt file. The .txt file has 40 columns of data and I am writing two new csv files that only take in 2 columns from the .txt file each.

My code is working great and producing almost exactly what i need. The last step is renaming the original headers in the two new csv outputs.

Here is my code:

import csv

QATargetHeaders = ['FNAME','LNAME']
FinalTargetHeaders = ['PROJECT_CODE','PHONE_NUMBER']

with open('TEST.txt','r') as csv_input, open('Test_QA2.csv','w') as csv_output:
    reader = csv.DictReader(csv_input, delimiter='^')
    writer = csv.DictWriter(csv_output, fieldnames=QATargetHeaders, extrasaction='ignore')
    writer.writeheader()
    for line in reader:
        writer.writerow(line)
with open('TEST.txt','r') as csv_input, open('Test_FINAL.csv','w') as csv_output:
    reader = csv.DictReader(csv_input, delimiter='^')
    writer = csv.DictWriter(csv_output, fieldnames=FinalTargetHeaders, extrasaction='ignore')
    writer.writeheader()
    for line in reader:
        writer.writerow(line)

For TEST_QA2.csv, i need to change the headers:

FNAME = First Name
LNAME = Last Name

For TEST_FINAL.csv, i need to change the headers:

PROJECT_CODE = PROJECT ID
PHONE_NUMBER = DEVICE1

Can someone help me figure out how to rewrite the headers here? The TargetHeaders must be defined as the exact column names from the .txt file so my writer knows what to copy in. Thank you in advance

When you define the writer you add the header as fieldnames already. The header is stored in QATargetHeaders and FinalTargetHeaders in your code. Adapt these variables to your desired ones. — nauer, Dec 11 '19 at 19:03
Sorry, I'm not sure i'm following your suggestion. Can you explain a bit more as to what I should do here? — eklone 1, Dec 11 '19 at 19:21

Stuart · Answer 1 · 2019-12-11T21:26:34.543

Instead of writeheader, use writerow with a dictionary mapping the old headers on to the new ones. You can then write the other rows as before.

QATargetHeaders = {'FNAME': 'First Name','LNAME': 'Last Name'}
FinalTargetHeaders = {'PROJECT_CODE': 'PROJECT ID','PHONE_NUMBER': 'DEVICE1'}
...
    writer = csv.DictWriter(csv_output, fieldnames=QATargetHeaders, extrasaction='ignore')
    writer.writerow(QATargetHeaders)  # instead of writeheader
    for line in reader:
        writer.writerow(line)
...

(As of Python 3.7 dictionaries preserve order so the columns will appear in the right order. If using earlier versions of Python you will need to either create a separate list of the field names, or use an OrderedDict, to ensure that the columns appear in the CSV file in the right order.)

Thank you! This worked! I like this approach! Did not know that dictionaries preserve order! — eklone 1, Dec 11 '19 at 22:12

nauer · Answer 2 · 2019-12-11T21:00:52.107

line is a dict. The dictionary keys are used as header. Therefore, you have to rename the keys before you write the new csv. You can do this by a dict comprehension.

import csv

QATargetHeaders = ['First Name','Last Name']
FinalTargetHeaders = ['PROJECT ID','DEVICE1']

map_QATargetHeaders = (('FNAME', 'First Name'), ('LNAME', 'Last Name'))
map_FinalTargetHeaders = (('PROJECT_CODE', 'PROJECT ID'), ('PHONE_NUMBER', 'DEVICE1LNAME'))

with open('TEST.txt','r') as csv_input, open('Test_QA2.csv','w') as csv_output:
    reader = csv.DictReader(csv_input, delimiter='^')

    writer = csv.DictWriter(csv_output, fieldnames=QATargetHeaders, extrasaction='ignore')
    writer.writeheader()
    for line in reader:
        line = {map[1]:line[map[0]] for map in map_QATargetHeaders}
        writer.writerow(line)

with open('TEST.txt','r') as csv_input, open('Test_FINAL.csv','w') as csv_output:
    reader = csv.DictReader(csv_input, delimiter='^')

    writer = csv.DictWriter(csv_output, fieldnames=FinalTargetHeaders, extrasaction='ignore')
    writer.writeheader()
    for line in reader:
        line = {map[1]:line[map[0]] for map in map_FinalTargetHeaders}
        writer.writerow(line)

This will not work. I need to define the TargetHeaders as they are in the text file so the writer knows which columns of data to copy. Is there any way to tell the program once the columns have been copied that FNAME = First Name and so on? — eklone 1, Dec 11 '19 at 19:45
@eklone1 Sorry, I did not read properly. I have updated my answer. — nauer, Dec 11 '19 at 21:02

Change Column Headers using DictWriter

2 Answers2