Read CSV using pandas with values enclosed with double quotes and values have comma in column
4
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 SAjay K S
21112
|
show 3 more comments
4
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 SAjay K S
21112
I think you need to usesep=',s*'instead ofsep=',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 runsed -r 's/"s+"/""/g' src.csv >cleared.csvbefore you feeding CSV to pandas. It will remove space between quotes or runsed -r 's/"s+"//g' src.csv >cleared.csvto remove internal quotes completely
– Alex
Mar 3 '17 at 13:09
|
show 3 more comments
4
4
4
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 SAjay 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
python csv
edited Mar 3 '17 at 12:25
Alex
5,38111019
asked Mar 3 '17 at 10:20
Ajay K SAjay K S
21112
edited Mar 3 '17 at 12:25
Alex
5,38111019
asked Mar 3 '17 at 10:20
Ajay K SAjay 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
5,38111019
asked Mar 3 '17 at 10:20
Ajay K SAjay K S
21112
asked Mar 3 '17 at 10:20
Ajay K SAjay K S
21112
asked Mar 3 '17 at 10:20
Ajay K SAjay K S
21112
21112
I think you need to usesep=',s*'instead ofsep=',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 runsed -r 's/"s+"/""/g' src.csv >cleared.csvbefore you feeding CSV to pandas. It will remove space between quotes or runsed -r 's/"s+"//g' src.csv >cleared.csvto remove internal quotes completely
– Alex
Mar 3 '17 at 13:09
|
show 3 more comments
I think you need to usesep=',s*'instead ofsep=',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 runsed -r 's/"s+"/""/g' src.csv >cleared.csvbefore you feeding CSV to pandas. It will remove space between quotes or runsed -r 's/"s+"//g' src.csv >cleared.csvto 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
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
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
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
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
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
2
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
DeepakDeepak
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 addingskipinitials = Trueto yourpd.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 isdataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)
– yoonghm
Sep 18 '18 at 15:28
add a comment |
2
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.
answered Mar 3 '17 at 14:39
AlexAlex
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 missingpd.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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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
DeepakDeepak
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 addingskipinitials = Trueto yourpd.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 isdataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)
– yoonghm
Sep 18 '18 at 15:28
add a comment |
2
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
DeepakDeepak
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 addingskipinitials = Trueto yourpd.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 isdataset = pd.read_csv('sample.csv', sep=',', skipinitialspace=True)
– yoonghm
Sep 18 '18 at 15:28
add a comment |
2
2
2
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
DeepakDeepak
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
DeepakDeepak
1213
answered Oct 10 '17 at 9:35
DeepakDeepak
1213
answered Oct 10 '17 at 9:35
DeepakDeepak
1213
answered Oct 10 '17 at 9:35
DeepakDeepak
1213
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 addingskipinitials = Trueto yourpd.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 isdataset = 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 addingskipinitials = Trueto yourpd.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 isdataset = 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
That didn't work for me, I'm still seeing single quotes in my dataframe.
– DataGirl
Apr 20 '18 at 20:32
1
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
@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
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 |
2
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.
answered Mar 3 '17 at 14:39
AlexAlex
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 missingpd.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 |
2
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.
answered Mar 3 '17 at 14:39
AlexAlex
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 missingpd.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 |
2
2
2
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.
answered Mar 3 '17 at 14:39
AlexAlex
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')
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.
answered Mar 3 '17 at 14:39
AlexAlex
5,38111019
edited Dec 31 '18 at 11:25
answered Mar 3 '17 at 14:39
AlexAlex
5,38111019
answered Mar 3 '17 at 14:39
AlexAlex
5,38111019
answered Mar 3 '17 at 14:39
AlexAlex
5,38111019
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 missingpd.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 missingpd.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
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
@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
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
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
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I think you need to use
sep=',s*'instead ofsep=',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 runsed -r 's/"s+"/""/g' src.csv >cleared.csvbefore you feeding CSV to pandas. It will remove space between quotes or runsed -r 's/"s+"//g' src.csv >cleared.csvto remove internal quotes completely– Alex
Mar 3 '17 at 13:09