python pandas separator within quote leading to error tokenizing

user_10436172 0
Viewed 109

I have a .csv containing data like the following:

....
"4", "mercedes", "BLT254", "Arkis-UDV GmbH, Berlin, Oberweg", "2007"
"5", "bmw", "SUV873", "Meier Auto", "2013"
....

I tried reading it by means of read_csv:

data = pd.read_csv("Auszug_2020.csv", sep = ",", encoding = "ISO-8859-1", quotechar = '"')

Every data piece is wrapped inside an " ". Within the quotes, sometimes the separator "," occurs. That's a problem! I thought I could fix this by using quoechar = '"', unfortunately it is still not working.

ParserError: Error tokenizing data. C error: Expected 5 fields in line 4, saw 7

What am I doing wrong?

EDIT: My bad! The encoding is "utf-16", I just realized. Now everything is working. Please forgive me python pandas, I'll never say anything bad about you again.

pandas separator python
2 Answers

Going by the sample data that you have shared, can you try reading it like this:

df = pd.read_csv("sample.csv", header=None, sep='", "')
df.iloc[:, 0] = df.iloc[:, 0].str.replace('"', '')
df.iloc[:,-1] = df.iloc[:,-1].str.replace('"', '')

I tested it as follows:

Created a sample csv file with 4 records:

"4", "mercedes", "BLT254", "Arkis-UDV GmbH, Berlin, Oberweg", "2007"
"5", "bmw", "SUV873", "Meier Auto", "2013"
"4", "mercedes", "BLT254", "Arkis-UDV GmbH, Berlin, Oberweg", "2007"
"5", "bmw", "SUV873", "Meier Auto", "2013"

Code to test:

import pandas as pd

df = pd.read_csv("sample.csv", header=None, sep='", "')
df.iloc[:, 0] = df.iloc[:, 0].str.replace('"', '')
df.iloc[:,-1] = df.iloc[:,-1].str.replace('"', '')

print(df)

Output:

   0         1       2                                3     4
0  4  mercedes  BLT254  Arkis-UDV GmbH, Berlin, Oberweg  2007
1  5       bmw  SUV873                       Meier Auto  2013
2  4  mercedes  BLT254  Arkis-UDV GmbH, Berlin, Oberweg  2007
3  5       bmw  SUV873                       Meier Auto  2013
user_126961630

Use, the optional parameter skipinitialspace=True in the pd.read_csv method to skip spaces after seperator , which will produce the desired result:

data = pd.read_csv(
    "Auszug_2020.csv", sep=",", encoding="ISO-8859-1",
    quotechar='"', skipinitialspace=True)
user_128331660
Related