I have a file that looks like:
'colA'|'colB'
'word"A'|'A'
'word'B'|'B'
I want to use pd.read_csv('input.csv',sep='|', quotechar="'"
) but I get the following output:
colA colB
word"A A
wordB' B
The last row is not correct, it should be word'B B
. How do I get around this? I have tried various iterations but none of them word that reads both rows correctly. I need some csv reading expertise!
I think you need str.strip
with apply
:
import pandas as pd
import io
temp=u"""'colA'|'colB'
'word"A'|'A'
'word'B'|'B'"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), sep='|')
df = df.apply(lambda x: x.str.strip("'"))
df.columns = df.columns.str.strip("'")
print (df)
colA colB
0 word"A A
1 word'B B