Read CSV using pandas with values enclosed with double quotes and values have comma in column

I need to read a file in python pandas of the following type

"column1","column2","column3","column4"

"value1","value,1","value2","value3"

"value5","value6","value7","value8"

"value32","value21","value,31","value,44"

I tried using

file1 = pd.read_csv('sample.txt',sep=',s+',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine=python)

it says something like ValueErro(Expected some lines got something else ) not exactly

I need to read a large CSV file of this type and load it to dataframe. what changes should i make to read it correctly.

edited Mar 3 '17 at 12:25

Alex

5,38111019

asked Mar 3 '17 at 10:20

Ajay K S

21112

I think you need to use sep=',s*' instead of sep=',s+'. As about comma inside quoted value (as it is a case for "value,31") it comply with rfc4180 and shouldn't be an issue

– Alex
Mar 3 '17 at 11:01

Earlier it was showingValueError('Expected 1 fields in line 328, saw4',) **and after changing it to * it shows **ValueError('Expected 1 fields in line 328, saw6',)

– Ajay K S
Mar 3 '17 at 12:00

It looks like the issue with source data. Check that line 328 in source data file

– Alex
Mar 3 '17 at 12:27

I am sorry i haven't mentioned about that, I have checked it and found that there is an extra comma inside double quotes. i removed it manually and the code works fine. But i cannot do this all time, how can I change code to handle the situation. There is another problem that inside the double quotes for one value there was another " " it also make the program to exit.

– Ajay K S
Mar 3 '17 at 12:43

comma inside double quotes is Ok. As about " " - you need to clean up source file before processing. If double quotes stay together as "" it shouldn't be an issue too because it comply with CSV standard, it calls escaped double quotes. If there is a space between double quotes then run sed -r 's/"s+"/""/g' src.csv >cleared.csv before you feeding CSV to pandas. It will remove space between quotes or run sed -r 's/"s+"//g' src.csv >cleared.csv to remove internal quotes completely

– Alex
Mar 3 '17 at 13:09

|
show 3 more comments

I need to read a file in python pandas of the following type

"column1","column2","column3","column4"

"value1","value,1","value2","value3"

"value5","value6","value7","value8"

"value32","value21","value,31","value,44"

I tried using

file1 = pd.read_csv('sample.txt',sep=',s+',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine=python)

it says something like ValueErro(Expected some lines got something else ) not exactly

I need to read a large CSV file of this type and load it to dataframe. what changes should i make to read it correctly.

edited Mar 3 '17 at 12:25

Alex

5,38111019

asked Mar 3 '17 at 10:20

Ajay K S

21112

I think you need to use sep=',s*' instead of sep=',s+'. As about comma inside quoted value (as it is a case for "value,31") it comply with rfc4180 and shouldn't be an issue

– Alex
Mar 3 '17 at 11:01

Earlier it was showingValueError('Expected 1 fields in line 328, saw4',) **and after changing it to * it shows **ValueError('Expected 1 fields in line 328, saw6',)

– Ajay K S
Mar 3 '17 at 12:00

It looks like the issue with source data. Check that line 328 in source data file

– Alex
Mar 3 '17 at 12:27

I am sorry i haven't mentioned about that, I have checked it and found that there is an extra comma inside double quotes. i removed it manually and the code works fine. But i cannot do this all time, how can I change code to handle the situation. There is another problem that inside the double quotes for one value there was another " " it also make the program to exit.

– Ajay K S
Mar 3 '17 at 12:43

comma inside double quotes is Ok. As about " " - you need to clean up source file before processing. If double quotes stay together as "" it shouldn't be an issue too because it comply with CSV standard, it calls escaped double quotes. If there is a space between double quotes then run sed -r 's/"s+"/""/g' src.csv >cleared.csv before you feeding CSV to pandas. It will remove space between quotes or run sed -r 's/"s+"//g' src.csv >cleared.csv to remove internal quotes completely

– Alex
Mar 3 '17 at 13:09

|
show 3 more comments

I need to read a file in python pandas of the following type

"column1","column2","column3","column4"

"value1","value,1","value2","value3"

"value5","value6","value7","value8"

"value32","value21","value,31","value,44"

I tried using

file1 = pd.read_csv('sample.txt',sep=',s+',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine=python)

it says something like ValueErro(Expected some lines got something else ) not exactly

I need to read a large CSV file of this type and load it to dataframe. what changes should i make to read it correctly.

edited Mar 3 '17 at 12:25

Alex

5,38111019

asked Mar 3 '17 at 10:20

Ajay K S

21112

I need to read a file in python pandas of the following type

"column1","column2","column3","column4"

"value1","value,1","value2","value3"

"value5","value6","value7","value8"

"value32","value21","value,31","value,44"

I tried using

file1 = pd.read_csv('sample.txt',sep=',s+',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine=python)

it says something like ValueErro(Expected some lines got something else ) not exactly

I need to read a large CSV file of this type and load it to dataframe. what changes should i make to read it correctly.

python csv

edited Mar 3 '17 at 12:25

Alex

5,38111019

asked Mar 3 '17 at 10:20

Ajay K S

21112

edited Mar 3 '17 at 12:25

Alex

5,38111019

asked Mar 3 '17 at 10:20

Ajay K S

21112

edited Mar 3 '17 at 12:25

Alex

5,38111019

edited Mar 3 '17 at 12:25

Alex

5,38111019

edited Mar 3 '17 at 12:25

Alex

5,38111019

asked Mar 3 '17 at 10:20

Ajay K S

21112

asked Mar 3 '17 at 10:20

Ajay K S

21112

asked Mar 3 '17 at 10:20

Ajay K S

21112

I think you need to use sep=',s*' instead of sep=',s+'. As about comma inside quoted value (as it is a case for "value,31") it comply with rfc4180 and shouldn't be an issue

– Alex
Mar 3 '17 at 11:01

Earlier it was showingValueError('Expected 1 fields in line 328, saw4',) **and after changing it to * it shows **ValueError('Expected 1 fields in line 328, saw6',)

– Ajay K S
Mar 3 '17 at 12:00

It looks like the issue with source data. Check that line 328 in source data file

– Alex
Mar 3 '17 at 12:27

I am sorry i haven't mentioned about that, I have checked it and found that there is an extra comma inside double quotes. i removed it manually and the code works fine. But i cannot do this all time, how can I change code to handle the situation. There is another problem that inside the double quotes for one value there was another " " it also make the program to exit.

– Ajay K S
Mar 3 '17 at 12:43

comma inside double quotes is Ok. As about " " - you need to clean up source file before processing. If double quotes stay together as "" it shouldn't be an issue too because it comply with CSV standard, it calls escaped double quotes. If there is a space between double quotes then run sed -r 's/"s+"/""/g' src.csv >cleared.csv before you feeding CSV to pandas. It will remove space between quotes or run sed -r 's/"s+"//g' src.csv >cleared.csv to remove internal quotes completely

– Alex
Mar 3 '17 at 13:09

|
show 3 more comments

I think you need to use sep=',s*' instead of sep=',s+'. As about comma inside quoted value (as it is a case for "value,31") it comply with rfc4180 and shouldn't be an issue

– Alex
Mar 3 '17 at 11:01

Earlier it was showingValueError('Expected 1 fields in line 328, saw4',) **and after changing it to * it shows **ValueError('Expected 1 fields in line 328, saw6',)

– Ajay K S
Mar 3 '17 at 12:00

It looks like the issue with source data. Check that line 328 in source data file

– Alex
Mar 3 '17 at 12:27

I am sorry i haven't mentioned about that, I have checked it and found that there is an extra comma inside double quotes. i removed it manually and the code works fine. But i cannot do this all time, how can I change code to handle the situation. There is another problem that inside the double quotes for one value there was another " " it also make the program to exit.

– Ajay K S
Mar 3 '17 at 12:43

comma inside double quotes is Ok. As about " " - you need to clean up source file before processing. If double quotes stay together as "" it shouldn't be an issue too because it comply with CSV standard, it calls escaped double quotes. If there is a space between double quotes then run sed -r 's/"s+"/""/g' src.csv >cleared.csv before you feeding CSV to pandas. It will remove space between quotes or run sed -r 's/"s+"//g' src.csv >cleared.csv to remove internal quotes completely

– Alex
Mar 3 '17 at 13:09

I think you need to use sep=',s*' instead of sep=',s+'. As about comma inside quoted value (as it is a case for "value,31") it comply with rfc4180 and shouldn't be an issue

– Alex
Mar 3 '17 at 11:01

Earlier it was showingValueError('Expected 1 fields in line 328, saw4',) **and after changing it to * it shows **ValueError('Expected 1 fields in line 328, saw6',)

– Ajay K S
Mar 3 '17 at 12:00

It looks like the issue with source data. Check that line 328 in source data file

– Alex
Mar 3 '17 at 12:27

I am sorry i haven't mentioned about that, I have checked it and found that there is an extra comma inside double quotes. i removed it manually and the code works fine. But i cannot do this all time, how can I change code to handle the situation. There is another problem that inside the double quotes for one value there was another " " it also make the program to exit.

– Ajay K S
Mar 3 '17 at 12:43

comma inside double quotes is Ok. As about " " - you need to clean up source file before processing. If double quotes stay together as "" it shouldn't be an issue too because it comply with CSV standard, it calls escaped double quotes. If there is a space between double quotes then run sed -r 's/"s+"/""/g' src.csv >cleared.csv before you feeding CSV to pandas. It will remove space between quotes or run sed -r 's/"s+"//g' src.csv >cleared.csv to remove internal quotes completely

– Alex
Mar 3 '17 at 13:09

|
show 3 more comments

2 Answers
2

active

oldest

votes

No need to preprocess csv file, just use engine type python :

dataset = pd.read_csv('sample.csv', sep=',', engine='python')

answered Oct 10 '17 at 9:35

Deepak

1213

That didn't work for me, I'm still seeing single quotes in my dataframe.

– DataGirl
Apr 20 '18 at 20:32

1

@DataGirl try adding skipinitials = True to your pd.read_csv(...). As you can see here: stackoverflow.com/a/37076344/3286178

– rwenz3l
Jun 15 '18 at 9:16

As pointed out by @rwenz3l, but the syntax is dataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)

– yoonghm
Sep 18 '18 at 15:28

add a comment |

Use in python pandas sep=',s*' instead of sep=',s+', it will make space(s) optional after each comma:

file1 = pd.read_csv('sample.txt',sep=',s*',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine='python')

Comma inside double quotes is Ok, it's allowed by rfc4180 standard.

As about " " inside of data values (such as "value" "13") - you will need to clean up source file before processing. If double quotes stay together as "" it shouldn't be an issue because it comply with CSV standard, it calls escaped double quotes, but if there is a space between double quotes then you need to clean it up

Use:

sed -r 's/"s+"/""/g' src.csv >cleared.csv

before you feeding CSV to pandas. It will remove space between quotes or run

sed -r 's/"s+"//g' src.csv >cleared.csv

to remove internal quotes completely.

edited Dec 31 '18 at 11:25

answered Mar 3 '17 at 14:39

Alex

5,38111019

Alex, where do you run sed -r 's/"s+"//g' src.csv >cleared.csv? Is this a command line piece of code?

– DataGirl
Apr 20 '18 at 20:29

@DataGirl Yes it should be run from command line, in terminal on Unix base system or if you on Windows then you need to install cygwin to use "sed" program

– Alex
Jul 18 '18 at 14:46

sep=',s*' seems to break using quotechar='"', quoting=csv.QUOTE_ALL

– Frobbit
Sep 24 '18 at 21:30

single quote is missing pd.read_csv( ...... engine='python')

– Dipankar Nalui
Nov 23 '18 at 9:59

Fixed. Tnx Dipankar

– Alex
Dec 31 '18 at 11:27

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1184776%2fread-csv-using-pandas-with-values-enclosed-with-double-quotes-and-values-have-co%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

No need to preprocess csv file, just use engine type python :

dataset = pd.read_csv('sample.csv', sep=',', engine='python')

answered Oct 10 '17 at 9:35

Deepak

1213

That didn't work for me, I'm still seeing single quotes in my dataframe.

– DataGirl
Apr 20 '18 at 20:32

1

@DataGirl try adding skipinitials = True to your pd.read_csv(...). As you can see here: stackoverflow.com/a/37076344/3286178

– rwenz3l
Jun 15 '18 at 9:16

As pointed out by @rwenz3l, but the syntax is dataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)

– yoonghm
Sep 18 '18 at 15:28

add a comment |

No need to preprocess csv file, just use engine type python :

dataset = pd.read_csv('sample.csv', sep=',', engine='python')

answered Oct 10 '17 at 9:35

Deepak

1213

That didn't work for me, I'm still seeing single quotes in my dataframe.

– DataGirl
Apr 20 '18 at 20:32

1

@DataGirl try adding skipinitials = True to your pd.read_csv(...). As you can see here: stackoverflow.com/a/37076344/3286178

– rwenz3l
Jun 15 '18 at 9:16

As pointed out by @rwenz3l, but the syntax is dataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)

– yoonghm
Sep 18 '18 at 15:28

add a comment |

No need to preprocess csv file, just use engine type python :

dataset = pd.read_csv('sample.csv', sep=',', engine='python')

answered Oct 10 '17 at 9:35

Deepak

1213

No need to preprocess csv file, just use engine type python :

dataset = pd.read_csv('sample.csv', sep=',', engine='python')

answered Oct 10 '17 at 9:35

Deepak

1213

answered Oct 10 '17 at 9:35

Deepak

1213

answered Oct 10 '17 at 9:35

Deepak

1213

answered Oct 10 '17 at 9:35

Deepak

1213

That didn't work for me, I'm still seeing single quotes in my dataframe.

– DataGirl
Apr 20 '18 at 20:32

1

@DataGirl try adding skipinitials = True to your pd.read_csv(...). As you can see here: stackoverflow.com/a/37076344/3286178

– rwenz3l
Jun 15 '18 at 9:16

As pointed out by @rwenz3l, but the syntax is dataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)

– yoonghm
Sep 18 '18 at 15:28

add a comment |

That didn't work for me, I'm still seeing single quotes in my dataframe.

– DataGirl
Apr 20 '18 at 20:32

1

@DataGirl try adding skipinitials = True to your pd.read_csv(...). As you can see here: stackoverflow.com/a/37076344/3286178

– rwenz3l
Jun 15 '18 at 9:16

As pointed out by @rwenz3l, but the syntax is dataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)

– yoonghm
Sep 18 '18 at 15:28

That didn't work for me, I'm still seeing single quotes in my dataframe.

– DataGirl
Apr 20 '18 at 20:32

@DataGirl try adding skipinitials = True to your pd.read_csv(...). As you can see here: stackoverflow.com/a/37076344/3286178

– rwenz3l
Jun 15 '18 at 9:16

As pointed out by @rwenz3l, but the syntax is dataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)

– yoonghm
Sep 18 '18 at 15:28

add a comment |

Use in python pandas sep=',s*' instead of sep=',s+', it will make space(s) optional after each comma:

file1 = pd.read_csv('sample.txt',sep=',s*',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine='python')

Use:

sed -r 's/"s+"/""/g' src.csv >cleared.csv

before you feeding CSV to pandas. It will remove space between quotes or run

sed -r 's/"s+"//g' src.csv >cleared.csv

to remove internal quotes completely.

edited Dec 31 '18 at 11:25

answered Mar 3 '17 at 14:39

Alex

5,38111019

Alex, where do you run sed -r 's/"s+"//g' src.csv >cleared.csv? Is this a command line piece of code?

– DataGirl
Apr 20 '18 at 20:29

@DataGirl Yes it should be run from command line, in terminal on Unix base system or if you on Windows then you need to install cygwin to use "sed" program

– Alex
Jul 18 '18 at 14:46

sep=',s*' seems to break using quotechar='"', quoting=csv.QUOTE_ALL

– Frobbit
Sep 24 '18 at 21:30

single quote is missing pd.read_csv( ...... engine='python')

– Dipankar Nalui
Nov 23 '18 at 9:59

Fixed. Tnx Dipankar

– Alex
Dec 31 '18 at 11:27

add a comment |

Use in python pandas sep=',s*' instead of sep=',s+', it will make space(s) optional after each comma:

file1 = pd.read_csv('sample.txt',sep=',s*',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine='python')

Use:

sed -r 's/"s+"/""/g' src.csv >cleared.csv

before you feeding CSV to pandas. It will remove space between quotes or run

sed -r 's/"s+"//g' src.csv >cleared.csv

to remove internal quotes completely.

edited Dec 31 '18 at 11:25

answered Mar 3 '17 at 14:39

Alex

5,38111019

Alex, where do you run sed -r 's/"s+"//g' src.csv >cleared.csv? Is this a command line piece of code?

– DataGirl
Apr 20 '18 at 20:29

@DataGirl Yes it should be run from command line, in terminal on Unix base system or if you on Windows then you need to install cygwin to use "sed" program

– Alex
Jul 18 '18 at 14:46

sep=',s*' seems to break using quotechar='"', quoting=csv.QUOTE_ALL

– Frobbit
Sep 24 '18 at 21:30

single quote is missing pd.read_csv( ...... engine='python')

– Dipankar Nalui
Nov 23 '18 at 9:59

Fixed. Tnx Dipankar

– Alex
Dec 31 '18 at 11:27

add a comment |

Use in python pandas sep=',s*' instead of sep=',s+', it will make space(s) optional after each comma:

file1 = pd.read_csv('sample.txt',sep=',s*',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine='python')

Use:

sed -r 's/"s+"/""/g' src.csv >cleared.csv

before you feeding CSV to pandas. It will remove space between quotes or run

sed -r 's/"s+"//g' src.csv >cleared.csv

to remove internal quotes completely.

edited Dec 31 '18 at 11:25

answered Mar 3 '17 at 14:39

Alex

5,38111019

Use in python pandas sep=',s*' instead of sep=',s+', it will make space(s) optional after each comma:

file1 = pd.read_csv('sample.txt',sep=',s*',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine='python')

Use:

sed -r 's/"s+"/""/g' src.csv >cleared.csv

before you feeding CSV to pandas. It will remove space between quotes or run

sed -r 's/"s+"//g' src.csv >cleared.csv

to remove internal quotes completely.

edited Dec 31 '18 at 11:25

answered Mar 3 '17 at 14:39

Alex

5,38111019

edited Dec 31 '18 at 11:25

answered Mar 3 '17 at 14:39

Alex

5,38111019

answered Mar 3 '17 at 14:39

Alex

5,38111019

answered Mar 3 '17 at 14:39

Alex

5,38111019

Alex, where do you run sed -r 's/"s+"//g' src.csv >cleared.csv? Is this a command line piece of code?

– DataGirl
Apr 20 '18 at 20:29

@DataGirl Yes it should be run from command line, in terminal on Unix base system or if you on Windows then you need to install cygwin to use "sed" program

– Alex
Jul 18 '18 at 14:46

sep=',s*' seems to break using quotechar='"', quoting=csv.QUOTE_ALL

– Frobbit
Sep 24 '18 at 21:30

single quote is missing pd.read_csv( ...... engine='python')

– Dipankar Nalui
Nov 23 '18 at 9:59

Fixed. Tnx Dipankar

– Alex
Dec 31 '18 at 11:27

add a comment |

Alex, where do you run sed -r 's/"s+"//g' src.csv >cleared.csv? Is this a command line piece of code?

– DataGirl
Apr 20 '18 at 20:29

@DataGirl Yes it should be run from command line, in terminal on Unix base system or if you on Windows then you need to install cygwin to use "sed" program

– Alex
Jul 18 '18 at 14:46

sep=',s*' seems to break using quotechar='"', quoting=csv.QUOTE_ALL

– Frobbit
Sep 24 '18 at 21:30

single quote is missing pd.read_csv( ...... engine='python')

– Dipankar Nalui
Nov 23 '18 at 9:59

Fixed. Tnx Dipankar

– Alex
Dec 31 '18 at 11:27

Alex, where do you run sed -r 's/"s+"//g' src.csv >cleared.csv? Is this a command line piece of code?

– DataGirl
Apr 20 '18 at 20:29

@DataGirl Yes it should be run from command line, in terminal on Unix base system or if you on Windows then you need to install cygwin to use "sed" program

– Alex
Jul 18 '18 at 14:46

sep=',s*' seems to break using quotechar='"', quoting=csv.QUOTE_ALL

– Frobbit
Sep 24 '18 at 21:30

single quote is missing pd.read_csv( ...... engine='python')

– Dipankar Nalui
Nov 23 '18 at 9:59

Fixed. Tnx Dipankar

– Alex
Dec 31 '18 at 11:27

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Jtdylktuy