Read csv in pandas with different separator (commas)

Question

I want to read a CSV file and save it as data frame in pandas.

But I have a problem because I have rows like this:

BG,6141.6,6141.6,,3.0,,,ic

As you see there are three separators: ',,,' , ',,' and ,

How can I load it correctly into pandas?

You should take a close look at data, most likely the multiple separators would mean that your data has 8 columns but columns 4,6 and 7 in this particular row are blank — Vaishali
– Vaishali, Commented Jun 22, 2017 at 16:16
You should indicate what you already tried, and why you consider it did not work. — bli
– bli, Commented Jun 22, 2017 at 16:19
@jezrael yes, there are. The problem is, that instead of 1 million rows pandas reads only 45000. I didn't use some special options, only provided the path. The structure of data I gave in the post. What the solution could be? — yef_dan_92
– yef_dan_92, Commented Jun 22, 2017 at 16:27

jezrael · Accepted Answer · 2017-06-22 16:14:46Z

2

Use regex separator [,]+ - one or more ,:

import pandas as pd
from pandas.compat import StringIO

temp=u"""iBG,6141.6,6141.6,,3.0,,,ic"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
df = pd.read_csv(StringIO(temp), sep="[,]+", header=None, engine='python')
print (df)
     0       1       2    3   4
0  iBG  6141.6  6141.6  3.0  ic

answered Jun 22, 2017 at 16:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read csv in pandas with different separator (commas)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related