How to work around quotes when reading csv file on pandas

try below, it meets your requirement...

Sample file:

$ cat test.csv a,b,\\"c\\,d",e,f,g,h i,j,k,l,m,n,o p,q,r,s,t,u,v 

Solution based on the latest change on the Post:

Pandas is a tool to process tabular data. It means that each row should contain the same number of fields/rows. So, fields in each row should be in the same order.

But your input file actually fails to meet the criteria which pandas needs to read the CSV.

In your case, It's expecting 7 fields in line 36190, however it saw 8 which pandas don't really like hence you need to do cleaning of your data before processing some or other way around.

What you can do, read the data First into a single column while reading csv and do some cleaning, I've explained those below.

Hope this will give you an idea how to proceed, please keep in mind that you have to clean your data before you read them into pandas.

# Read your input file, using read_csv, but as a single column (sep set to a non-used char). df = pd.read_csv('test.csv', sep='|', names=['col1']) # Apply to replace with regex and remove backslash chars df['col1'] = df['col1'].replace(r'(\\|\\)', '', regex=True) df['col1']= df['col1'].replace(r'("c,d")', 'cd', regex=True) # Now save these into a new CSV file df.to_csv("new.csv") # Read new csv file again df2 = pd.read_csv("new.csv") # drop the `Unnamed: 0'` column as this is not required df2 = df2.drop('Unnamed: 0', 1) # Replace the unwanted chars in order to get the rows with same length df2['col1'] = df2['col1'].replace(r'(,d"|")', '', regex=True) df2 = df2['col1'].replace(r'(,d"|")', '', regex=True).str.split(",", expand=True).rename(columns={0:'AA',1:'BB',2:'CC',3:'DD',4:'EE',5:'FF',6:'GG'}) 

Result:

print(df2) AA BB CC DD EE FF GG 0 a b cd e f g h 1 i j k l m n o 2 p q r s t u v 

I think you can try the escapechar parameter of the pd.read_csv function. I've never had to use this before and I am not 100% sure I understand your question. Are you trying to combine columns C & D for all rows or only when these special characters/patterns are present in the data? Here is a link to the doc, there may be some more helpful parameters to address this issue: Pandas documentation

If you are trying to do this for all of the rows you may want to make a string processing helper function that combines these data and removes the backslashes, double quotes and comma.

Force the number of columns:

df = pd.read_csv('in.csv', header=None, names=list('12345678')) 

Outputs:

 1 2 3 4 5 6 7 8 0 a b c d e f g h 1 a b \\"c\\ d" e f g h 

Then clean etc. from there.

You can use

pd.read_csv('text.csv', sep=',') 

First of wall, the quotes are wrongly formatted, to consider a cell a quoted, it needs to start and end with quotation marks (or other chosen QUOTE symbol). In your examle it starts with a \\ so it is not considered as quoted, and the comma in between is not ignored. I don't know how this file was generated, but if it is a single such case, try fixing it manually. If it occurrs in multiple location, it needs to be regenerated properly, or if not possible it needs a custom preprocessing to fix such cases. A possible preprocessing might be replacing all \, with another unique symbol, and then replacing it back after reading.