I have a dataset like this:
ISIN,"MIC","Datum","Open","Hoog","Laag","Close","Number of Shares","Number of Trades","Turnover","Valuta"
NL0011821202,"Euronext Amsterdam Brussels","04/09/2017","14.82","14.95","14.785","14.855","7482805","6970","111345512.83","EUR"
NL0011821202,"Euronext Amsterdam Brussels","05/09/2017","14.91","14.92","14.585","14.655","15240971","12549","224265257.14","EUR"
NL0011821202,"Euronext Amsterdam Brussels","07/09/2017","14.69","14.74","14.535","14.595","15544695","15817","227478163.74","EUR"
However I can't read the file in correctly with pd.read_csv('filename.csv') I've tried all kinds of combinations like:
sep='"',
delimiter=","
But with no luck at all! I want the first line to be the columns and the quote characters and comma to be removed.
How do I go about this efficiently?
Problem is there is sometimes double ", solution is change separator for match zero or more " before and after ,:
df = pd.read_csv('ING_DAILY - ING_DAILY.csv', sep='["]*,["]*', engine='python')
Then is necessary remove " from columns names and from first and last columns:
df.columns = df.columns.str.strip('"')
df.iloc[:, [0,-1]] = df.iloc[:, [0,-1]].apply(lambda x: x.str.strip('"'))
print (df.head(3))
ISIN MIC Datum Open Hoog \
0 NL0011821202 Euronext Amsterdam Brussels 04/09/2017 14.82 14.950
1 NL0011821202 Euronext Amsterdam Brussels 05/09/2017 14.91 14.920
2 NL0011821202 Euronext Amsterdam Brussels 06/09/2017 14.69 14.725
Laag Close Number of Shares Number of Trades Turnover Valuta
0 14.785 14.855 7482805 6970 1.113455e+08 EUR
1 14.585 14.655 15240971 12549 2.242653e+08 EUR
2 14.570 14.615 14851426 15303 2.175316e+08 EUR