-1

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)
martineau
  • 119,623
  • 25
  • 170
  • 301
Girl007
  • 165
  • 1
  • 13

1 Answers1

0

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
  • Replace `sys.stdout` in the call to `out = csv.DictWriter(sys.stdout, fieldnames)` with your file, of course. –  May 20 '21 at 15:58
  • I have got 1,2,3,4,5,6,7 this as my output.csv @Justin Ezequiel – Girl007 May 20 '21 at 16:19
  • Do `print(fieldnames)` to see what was read from 'template.csv'. It looks like your file is missing the '1'. –  May 20 '21 at 16:26
  • Most of these you can debug yourself by adding calls to print. I suggest you go back to reading up on the basics. –  May 20 '21 at 16:27
  • Watch out for a [Byte order mark (BOM)](https://en.wikipedia.org/wiki/Byte_order_mark) at the very start of your input CSV files. –  May 20 '21 at 16:34