How to handle multiple delimiters in the pd.read_csv function in python?

Question

I have a CSV file with delimiter as ';'.

The below code only handles the CSV with ';' as the delimiter.

dataset = pd.read_csv('/Data/formatted1.csv',header=0, delimiter = ';', encoding = 'unicode_escape')

But, if the CSV has the delimiter as ',' or 'tab' or '\n' then it should handle it dynamically. Is there a way to handle all of them dynamically?

I have tried the below code but it doesn't work.

df = pd.read_csv('file.csv', delim_whitespace=True)

E. Zeytinci · Accepted Answer · 2020-01-30 13:35:18Z

1

IMHO, you can use regex,

import pandas as pd

df = pd.read_csv(PATH, sep=r'[,|;\t"]+(?=\S)', engine='python')

Here are some examples,

from io import StringIO
import pandas as pd

s1 = """a|b|c
1|2|3
"""

s2 = """a;b;c
1;2;3
"""

print(pd.read_csv(StringIO(s1), sep=r'[,|;\t"]+(?=\S)', engine='python'))
print()
print(pd.read_csv(StringIO(s2), sep=r'[,|;\t"]+(?=\S)', engine='python'))

   a  b  c
0  1  2  3

   a  b  c
0  1  2  3

edited Jan 30, 2020 at 13:35

answered Jan 30, 2020 at 10:09

E. Zeytinci

2,6332 gold badges23 silver badges40 bronze badges

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

5 Comments

P H Over a year ago

Thank you so much for your answer. I tried your code. But, I am getting the below error "Expected 17 fields in line 58, saw 27. Error could possibly be due to quotes being ignored when a multi-char delimiter is used."

chimbu Over a year ago

Refer this stackoverflow.com/questions/15242746/… it resolves your error.

P H Over a year ago

@E.Zeytinci Awesome. The new answer works perfectly. Thank you so much!

E. Zeytinci Over a year ago

Hi @PH if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.

P H Over a year ago

@E.Zeytinci I have done it :) I didn't know about this option.

chimbu · Accepted Answer · 2020-01-30 12:58:05Z

0

Hope this helps. You can use either Delimiter or Sep. Refer Python Documentation.

\s - Matches any whitespace character; this is equivalent to the set [ \t\n\r\f\v].

import pandas as pd
my_cols = [str(i) for i in range(45)] # create some row names
dataset = pd.read_csv('/Data/formatted1.csv', sep="\s+|;|,", names=my_cols, header=None, engine="python")

Thanks to @Tai

edited Jan 30, 2020 at 12:58

answered Jan 30, 2020 at 12:52

chimbu

1141 silver badge11 bronze badges

1 Comment

P H Over a year ago

Thank you @chimbu. The below answer from E. Zeytinci worked for me.

Collectives™ on Stack Overflow

How to handle multiple delimiters in the pd.read_csv function in python?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related