How to use csv reader to map columns from one file to another

Question

I am new to Python csv.reader and I am trying to populate the data in sheet2 with the headers in sheet1:

sheet1.csv contains the following headers

  1         2         3         4         5         6         7  

sheet2.csv contains the following data

  col_1     col_4     col_5     col_7
 yellow      Blue     Green       Red

The desired output is as follows:

      1         2         3         4         5         6         7  
 Yellow                          Blue     Green                 Red

Originally I have done this using pandas however I would like to do this using csv.reader in python.

This is my attempt so far:

import csv

with open('sheet1.csv', newline='') as myFile:
    reader = csv.reader(myFile)
    for row in reader:
        print(row)

Answer 1

I know I've answered this before but am too lazy to go look for the original post.

[mre] version:

from io import StringIO
import csv
import sys

# map column names to template
mapper = lambda fieldname: fieldname.split('_')[-1]

template = '''\
1,2,3,4,5,6,7
'''

source = '''\
col_1,col_4,col_5,col_7
Yellow,Blue,Green,Red
White,Black,Cyan,Magenta
'''

fieldnames = csv.DictReader(StringIO(template)).fieldnames

out = csv.DictWriter(sys.stdout, fieldnames)
out.writeheader()

reader = csv.DictReader(StringIO(source))
for row in reader:
    out.writerow({mapper(k): v for (k, v) in row.items()})

with actual files version:

import csv

# map column names to template
mapper = lambda fieldname: fieldname.split('_')[-1]

with open('template.csv', newline='') as template:
    fieldnames = csv.DictReader(template).fieldnames

with open('source.csv', newline='') as source, open('output.csv', 'w', newline='') as output:
    reader = csv.DictReader(source)

    out = csv.DictWriter(output, fieldnames)
    out.writeheader()

    for row in reader:
        out.writerow({mapper(k): v for (k, v) in row.items()})

output:

1,2,3,4,5,6,7
Yellow,,,Blue,Green,,Red
White,,,Black,Cyan,,Magenta