[FIXED] How can I import this csv with some quoted lines into pandas?

Issue

I have a csv with a structure like this:

project, location, badness
foo, N/A, 0
bar, 'path/to/file:[7,23]', 120

I want to import this into a Pandas dataframe. When I use pd.read_csv(filename, quotechar="'", sep=".\s+") right now, I get columns like:

project   location           badness
foo       N/A                0
bar       'path/to/file:[7   23]'       120

with the final dangling column unnamed.

How can I import this in a way that respects the quotes? That is, how can I get the "location" column to have 'path/to/file:[7,23]' on the second line?

Solution

Try to change the separator to ",\s+":

from io import StringIO

import pandas as pd

text = """\
project, location, badness
foo, N/A, 0
bar, 'path/to/file:[7,23]', 120"""

df = pd.read_csv(StringIO(text), quotechar="'", sep=r",\s+", engine="python")
print(df)

Prints:

  project               location  badness
0     foo                    NaN        0
1     bar  'path/to/file:[7,23]'      120

Answered By - Andrej Kesely