Pandas Dataframe from csv not displaying correctly

Question

I'm trying to import a csv file (25MB - 80000 rows) into pandas dataframe but it's not displaying correctly. The columns are seperated with a ';' when calling df.columns.

  Originated GeoZone;Booking ...                                                                                                                                                                                                                                                                                                                                                          
1  PARIS;PARIS;;MARKer;EQDff;...                                                                                                                                                                                                                                                                                                                                                                                              
2  PARIS;PARIS;;MARKer;EQDff;...                                                                                                                                                                                                                                                                                                                                                                                              
3  PARIS;PARIS;;MARKer;EQDff;...                                                                                                                                                                                                                                                                                                                                                                                             
4  PARIS;PARIS;;MARKer;EQDff;...

The csv file is perfectly clear on Excel. Why is it not well interpreted by pandas.

CSV literally means "comma separated values". How various programs from python to excel parse non-comma separators tends to vary. General solution is to specify the separator explicitly, as @Junkrat does above — JohnE
– JohnE, Commented May 7, 2019 at 16:25

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

You should use delimiter or sep attribute in read_csv:

sep : str, default ‘,’

Delimiter to use. If sep is None, the C engine cannot automatically detect the separator, but the Python parsing engine can, meaning the latter will be used and automatically detect the separator by Python’s builtin sniffer tool, csv.Sniffer. In addition, separators longer than 1 character and different from '\s+' will be interpreted as regular expressions and will also force the use of the Python parsing engine. Note that regex delimiters are prone to ignoring quoted data. Regex example: '\r\t'.

delimiter : str, default None

Alias for sep.

df = pd.read_csv('waka.csv', sep=';')

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 7, 2019 at 16:27

vurmux

10k3 gold badges30 silver badges49 bronze badges

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

Comments

Lukas Tomek · Accepted Answer · 2019-05-07 16:26:37Z

3

This problem might be caused by different separator in Excel, it depends usually on country you are from.

You can try this:

pd.read_csv("your file", sep = ";")

This should works.

Theory: this is caused by different separators for csv files. Python use comma by default, but some files might use something other the comma, for example: ";" or "|".

answered May 7, 2019 at 16:26

Lukas Tomek

10612 bronze badges

Comments

some_programmer · Accepted Answer · 2019-05-07 16:27:14Z

2

Solution:

pd.read_csv('your file', sep = ';')

As @RafaelC pointed out, your default delimiter is ;, so you have to specify it separately

answered May 7, 2019 at 16:27

some_programmer

3,6408 gold badges33 silver badges74 bronze badges

Collectives™ on Stack Overflow

Pandas Dataframe from csv not displaying correctly

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related