0

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

https://i.stack.imgur.com/fc6hs.png enter image description here

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.stack.imgur.com/XhI86.png enter image description here

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.
xaxa90
  • 23
  • 5
  • TextEdit isn't a format... – OneCricketeer Oct 05 '18 at 14:56
  • Thanks for pointing that out. That's not the biggest problem--I should've mentioned that I think it's strange when the function is "csvfile_out". I'm totally new to Python but it just doesn't seem intuitive to have that function produce a TextEdit document. – xaxa90 Oct 05 '18 at 15:01
  • TextEdit opens CSV or any plain text... I don't really understand that problem. It would help if you edit your question to include the code – OneCricketeer Oct 05 '18 at 17:34
  • I've included the code in my post now. The main issue is the format of the output file. I need a single column with one word in each cell, in each new output file. – xaxa90 Oct 05 '18 at 17:55
  • Possible duplicate of [How to transpose a dataset in a csv file?](https://stackoverflow.com/questions/4869189/how-to-transpose-a-dataset-in-a-csv-file) – tripleee Oct 09 '18 at 05:30

1 Answers1

0

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

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
  • Thanks for the suggestion. I've replaced `if row` with your suggested line, but now there's no output at all. Any idea what might be causing that? – xaxa90 Oct 05 '18 at 20:06
  • `if row` only tests if the list is non-empty, not that you have exactly one word in the row. That is the only reason why I suggested it. Considering I cannot see the content of your `mock_data.csv`, I would guess you get nothing now is because that file has more than one column in it. – OneCricketeer Oct 05 '18 at 23:28
  • I see. Yes, it is the case that my file has many many columns... My example was: row 1 column A contains "professional", row 1 column B might contain the words "good for hair, juicy". So, different columns may contain more than one word. But I want only one word in each row of my output file. Any advice? There's a lot going on. – xaxa90 Oct 07 '18 at 00:07
  • Just write out `row[0]` to get first column – OneCricketeer Oct 07 '18 at 04:07