how to read csv file with commas within double quote using pandas (python)

Question

I have a dataset looks like this on wordpad.

"state","industry","2000","2005"

"A","art,music",2934,2454

"B","farm",3949,2343

And I want to read this on python like this.

"state"	"industry"	"2000"	"2005"
"A"	"art,music"	2934	2454
"B"	"farm"	3949	2343

I tried the codes below.

df = pd.read_csv(os.path.join(path, filename), engine='python', sep=',' , quoting=3)

this casts an error "ParserError: Expected 6 fields in line 8, saw 8"

df = pd.read_csv(os.path.join(path, filename), engine='python', sep='",' , quoting=3)

this puts all the numbers in a same cell.

I read a lot of posts asking similar question, but mine is a bit different from then because 1) I have a data which contains commas within double quotes and 2) employment numbers are not quoted.

How can I handle it? Help appreciated!

quoting=3 tells pandas that nothing in the csv file is quoted, which isn't the case for this file. Use quoting=0 instead. See stackoverflow.com/a/63357614/494134 — John Gordon
– John Gordon, Commented Oct 7, 2021 at 22:10
I don't understand why you want those extra quotes in the strings. By default, read_csv would read strings which would would see as, for instance, state (5 characters). But you want quotes around them? The python literal would be '"state"' (7 characters). — tdelaney
– tdelaney, Commented Oct 7, 2021 at 22:13

tdelaney · Accepted Answer · 2021-10-07 22:19:29Z

1

The default parameters to read_csv should work

import pandas as pd
import io

# for test
csv = io.StringIO('''\
"state","industry","2000","2005"
"A","art,music",2934,2454
"B","farm",3949,2343''')

df = pd.read_csv(csv)
print(df)
print(df.dtypes)

output

  state   industry  2000  2005
0     A  art,music  2934  2454
1     B       farm  3949  2343
state       object
industry    object
2000         int64
2005         int64

answered Oct 7, 2021 at 22:19

tdelaney

77.9k6 gold badges91 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to read csv file with commas within double quote using pandas (python)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related