Python + CSV: Need to retrieve index for each blank cell in a row, even if they repeat

Question

I'm attempting to process some CSV data using Python 3 and the csv library, and some rows have blank cells:

ID	attribute	price	color
1	spicy	cheap	red
2			blue
3	fuzzy	expensive	green

I need to reformat the data to hand off to someone else who will load it. It needs to look like the example below. (Why, you ask? See "hand off to someone else who will load.")

ID	VALUE	HEADER
1	spicy	attribute
1	cheap	price
1	red	color
...	...	...

I've put the headers in a dict (keys are integers corresponding to column position). I'm attempting to use the index of each cell in a row to retrieve the corresponding header.

Problem is, when two or more blank cells exist together in a row, the cell index *does not iterate. A snippet showing just this problem is just below.

with open('testdata/csv_test_in_003.csv', encoding = 'utf-8-sig', errors = 'ignore', newline = '') as file_in:
        reader = csv.reader(file_in)
        for row in reader:
            row_is_dict = {}
            for cell in row:
                content = {row.index(cell): cell}
                row_is_dict.update(content)
            print(row_is_dict)

*I suppose rather than saying that the cell index value "does not iterate," I should instead simply say that the second blank cell simply isn't processed.

From output:

{0: '2', 1: '', 3: 'blue'}`

As a result, when I'm writing to an output file (excerpt below)...

import csv
[...]
for cell in row:
   newrow = []
   [...] # skip first cell, only process following cells
      newrow.append(row[0]) # get ID, which must be in leftmost column
      newrow.append(cell) # get value
      newrow.append(headers[cellindex]) # fail :( to get the correct header
      writer.writerow(newrow)

...and I encounter a row with two or more blanks in a row, I get the same header repeated for each of the blanks, something like this:

ID	VALUE	HEADER
2		attribute
2		attribute*
2	blue	color

*This should be 'price'

I suspect many of you with much more Python experience than myself might suggest using a completely different approach for processing the data, perhaps using pandas or another toolset. If possible, please give me answers which not require knowledge of pandas or another such framework, but would simply allow me to tweak my approach.

user_10035985 · Answer

You can use csv.DictWriter for the task. I assume the empty cells are strings '':

import csv

with open("data.csv", "r") as f_in:
    reader = csv.DictReader(f_in)

    out = []
    for row in reader:
        for key in row.keys() - ["ID"]:
            out.append({"ID": row["ID"], "VALUE": row[key], "HEADER": key})

with open("out.csv", "w") as f_out:
    writer = csv.DictWriter(f_out, fieldnames=["ID", "VALUE", "HEADER"])
    writer.writeheader()
    writer.writerows(out)

Creates out.csv (screenshot from LibreOffice):

Python + CSV: Need to retrieve index for each blank cell in a row, even if they repeat

1 Answers