CSV nested list reading in with quotes

Question

I currently have;

files = ['a.txt', 'b.txt', 'c.txt']
ftypes = [['a', 'b'], ['c'], ['a', 'b', 'c']]
with open('files.csv', 'wb') as file:
    writer = csv.writer(file, quoting=csv.QUOTE_MINIMAL)
    for f, g in zip(files, ftypes):
        writer.writerow((f, g))

to read it in I've used;

with open('files.csv', 'r') as rfile:
    csvread = csv.reader(rfile)
    files = []
    ftypes = []
    for row in csvread:
        files.append(row[0])
        ftypes.append(row[1])

In the actual csv, the second column is populated correctly with the sub-lists filling the 2nd column, however upon reading in ftypes, it returns ["['a', 'b']", "['c']", "['a', 'b', 'c']"] which is incorrect. It treats it as a list of strings, so I can't loop through them like so;

for i in ftypes:
    for j in i:
       print(j)

How do I fix my writing/reading so that the nested list parses correctly?

Edit:

CSV file contents

a.txt,"['a', 'b']"
b.txt,"['c']"
c.txt,"['a', 'b', 'c']"

It appears as though Excel 2013 is parsing the double quotes and ignoring them, but python is parsing the actual csv contents correctly. What parameters do I need to pass into csv.writer to correct this?

Please paste a sample of your CSV file contents – Andrés Pérez-Albela H. Nov 25 '15 at 05:48 — Andrés Pérez-Albela H., Nov 25 '15 at 05:48
How do you want it to appear in Excel? – Martin Evans Nov 25 '15 at 07:25 — Martin Evans, Nov 25 '15 at 07:25

score 2 · Accepted Answer · edited May 23 '17 at 11:54

I think you should use Convert string representation of list to list in Python.

The things happened are pretty obvious - Python reads CSV row, and each element in the row is a string. It doesn't know what type it supposed to be.

So you will have something like

import ast
with open('files.csv', 'r') as rfile:
    csvread = csv.reader(rfile)
    files = []
    g_list = []
    for row in csvread:
        files.append(row[0])
        ftypes.append(ast.literal_eval(row[1]))

To avoid this hack and fix exactly writing you could do the following:

files = ['a.txt', 'b.txt', 'c.txt']
ftypes = [['a', 'b'], ['c'], ['a', 'b', 'c']]

with open('files.csv', 'wb') as file:
    writer = csv.writer(file, quoting=csv.QUOTE_MINIMAL)
    for f, g in zip(files, ftypes):
        g.insert(0, f)
        writer.writerow(g)


ftypes_from_file = []
files_from_file = []
with open('files.csv', 'r') as rfile:
    csvread = csv.reader(rfile)
    files = []
    g_list = []
    for row in csvread:
        files_from_file.append(row[0])
        ftype = []
        for item in row[1:]:
            ftype.append(item)
        ftypes_from_file.append(ftype)

print(ftypes_from_file)

The main trick it to deal with dynamic count of items in the row.

I hope the new format of the row in CSV will be OK to you.

`ftypes.append(ast.literal_eval(row[1]))` worked just fine! Thanks! This, however, does not answer the broader question of writing in the values correctly in the first place. — bmikolaj, Nov 25 '15 at 05:57

CSV nested list reading in with quotes

1 Answers1