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

user_14089634 0
Viewed 358

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?

csv python-3.x python
4 Answers
  • 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 
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,"[]"
user_77588040

If you are unable to change the input file you can still work with it using the following code to handle the list column -

with open('test.csv', newline='') as csvfile:
    reader = csv.DictReader(csvfile)
    stringVal = ","
    for row in reader:
        newList = row['list'] + "," + stringVal.join(row[None])
        print(newList)
user_140898530

The problem has to do with your csv file. If that is what the actual file looks like, then it will do as you say it does. Wrap the list in double quotes like this:

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

You can quote your csv file like this:

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

Then you can read it using csv module with an optional argument quoting=csv.QUOTE_ALL. After this you can convert these "string lists" to regular lists. e.x. if these "string lists" are numeric only then you can filter the results and then cast it to ints. Here is code:

import csv

with open('test.csv', 'r') as fh:
    data = list(csv.reader(fh, quoting=csv.QUOTE_ALL))
    for i in range(1, 4):
      data[i][2] = list(filter(lambda c: str.isnumeric(c), data[i][2]))
      data[i][2] = list(map(lambda c: int(c), data[i][2]))
    print(*data, sep='\n')

And the results:

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