How to transpose rows of data separated by commas in certain cells to single column using data from a CSV file?

Question

I have a CSV file with text data separated by commas in some columns, but not in others, e.g.:

https://i.imgur.com/X6bq09I.png

I want to export each row of my CSV file to a new CSV file. An example desired output for the first row of my original file would look like this:

https://i.imgur.com/QB9sLeL.png

I have tried the code offered in the first answer of this post: Open CSV file and writing each row to new, dynamically named CSV file.

This is the code I used:

import csv

counter = 1

with open('mock_data.csv', 'rU') as csvfile:
reader = csv.reader(csvfile)
for row in reader:
    if row:
        filename = "trial%s" % str(counter)
        with open(filename, 'w') as csvfile_out:
            writer = csv.writer(csvfile_out)
            writer.writerow(row)
            counter = counter + 1

This code does produce a new .csv file for each row. However...

EDIT: I have three remaining issues, for which I have not found the right code:

1. I want each word to have its own cell in each row; I don't know how to do this when certain cells contain a multiple words separated by commas, while other cells contain only a single word;
2. Once each word has its own cell, I want to transpose each row into a single column in the new .csv file;
3. I want to remove duplicate values from the column.

Answer 1

If you actually want a file extension, then use filename = "trial%s.csv" % str(counter)

But CSV files don't care about file extensions. Any file reader or code should be able to read the file.

TextEdit is just the Mac default for that.

I need a single column with one word in each cell, in each new output file

When you do writer.writerow(row), then make sure if len(row) == 1 rather than if row