Read a list from a csv row 'as a list' in python using DictReader

Question

My CSV file looks like this:

    id,name,list
    1,Beans,[1,2,3]
    2,Spam,[5,6,7]
    5,Spam,[7,8,9]

When I try to read the last column using the following code:

with open('some.csv', newline='') as csvfile:
     reader = csv.DictReader(csvfile)
     for row in reader:
         print(row["list"])

the output i get is:

[1
[5
[7

Apparently, it separates the list at the first ','. However i want it to read the whole list as one column. So my expected output is:

[1,2,3]
[5,6,7]
[7,8,9]

I plan to store each of these in variables so they can be used as I'd use a normal list to iterate over it or perform other tasks.

How do i achieve this?

Answer 1

• The issue is the CSV is not properly formatted with double quotes around the lists
  • Fix the CSV file, by wrapping the lists column in double quotes
    • list is a python data type, so it should never be used as a variable name.
• Use this solution for pandas
  • ast.literal_eval to evaluate the strings back into lists

import pandas as pd
from ast import literal_eval

# fix the csv file by wrapping the list with quotes
with open('test.csv', 'r+', newline='') as f:
    rows = [s.replace(',[', ',"[').replace(']', ']"').strip() for s in f.readlines()]
    f.seek(0)
    f.truncate()
    f.writelines(s + '\n' for s in rows)


# read the csv and evaluate the list column as lists
df = pd.read_csv('test.csv', converters={'lists': literal_eval})

# display(df)
   id   name      lists
0   1  Beans  [1, 2, 3]
1   2   Spam  [5, 6, 7]
2   5   Spam  [7, 8, 9]
3   6  Steak         []

print(type(df.loc[0, 'lists']))
[out]:
list

with open

# converts
id,name,lists
1,Beans,[1,2,3]
2,Spam,[5,6,7]
5,Spam,[7,8,9]
6,Steak,[]

# into
id,name,lists
1,Beans,"[1,2,3]"
2,Spam,"[5,6,7]"
5,Spam,"[7,8,9]"
6,Steak,"[]"