Python - How to split column values using multiple separators

Question

I am reading a .csv file and creating a Panda Dataframe. From this Dataframe I am fetching a value which is supposed to be a "list" item with comma separated values in it. But it comes out as a "string" item and I have to use a separator to split the values in it.

For example : I have a string variable by name "column_names" with below values

column_names = "First_Name, Last_Name,Middle_Name"
column_names = column_name.split(',')

Please note the space before the second value. So when I print this variable, I would be getting a space before the second element which will further create trouble while extracting values from this variable.

print(column_names)

['First_Name', ' Last_Name', 'Middle_Name']

In order to overcome this, if I keep separator to have a space along with actual separator (here it will be ', ' ), then the values are not getting splitted properly as seen below

column_names = "First_Name, Last_Name,Middle_Name"
column_names = column_names.split(', ')
print(column_names)

['First_Name', 'Last_Name,Middle_Name']

Notice the space to the right of comma while splitting. Using this separator, I am able to get only two values instead of three values.

My problem is the variable may contain comma separated values along with a space to the left or right of the comma or there may be no space at all. I have to handle all the cases with a single command (if possible). Something like providing multiple separator values while splitting.

For example : column_names.split(','|', '|' ,').

Not sure whether there is any as such but any pointers to this will be helpful.

cs95 · Accepted Answer · 2017-12-12 11:55:57Z

4

This is a common issue with CSVs. Fortunately, you can nip this in the bud, simply by reading your CSV properly, so you don't have to do all this unnecessary post-processing later.

When reading your dataframe with read_csv, pass a regex to sep\ delimiter -

df = pd.read_csv(..., sep='\s*,\s*', engine='python')

Now, df.columns should be a list of strings.

edited Dec 12, 2017 at 11:55

answered Dec 12, 2017 at 10:14

cs95

406k106 gold badges744 silver badges797 bronze badges

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

4 Comments

JKC Over a year ago

Thanks @coldspeed . This helps . But just for knowledge. Lets leave pandas. Inorder to split a string variable with multiple separators can I use any of the answers mentioned here ?

cs95 Over a year ago

@JKC No. You will have to use regular expressions in that case.

JKC Over a year ago

@coldspeed I think your answer should contain pd.read_csv instead of pd.DataFrame

JKC Over a year ago

Thanks @coldspeed :-)

MaxU - stand with Ukraine · Accepted Answer · 2017-12-12 10:33:08Z

3

You can make use of skipinitialspace=True parameter:

df = pd.read_csv(filename, sep=',', skipinitialspace=True)

skipinitialspace : boolean, default False
Skip spaces after delimiter.

NOTE: this parameter takes care only of spaces after delimiter, so @cᴏʟᴅsᴘᴇᴇᴅ's answer is more generic.

edited Dec 12, 2017 at 10:33

answered Dec 12, 2017 at 10:20

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

6 Comments

cs95 Over a year ago

By the way, does this also work if the space exists before the delimiter?

MaxU - stand with Ukraine Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ, no, it'll take care only of spaces after delimiter

cs95 Over a year ago

Oh, ok. I think that should be fine, but might just want to make it clear that "First_Name , Last_Name ,Middle_Name" for example will retain trailing spaces.

MaxU - stand with Ukraine Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ, well Galen's answer would help OP to deal with the list of columns, so it does answer the original question. BUT OP would face a next problem (spaces in values) immediately after that... ;-)

JKC Over a year ago

Thanks @MaxU. Good one. But I have to go with Coldspeed's answer as it is more generic.

|

Masudul Hasan · Accepted Answer · 2017-12-12 10:32:59Z

0

import re

column_names = "First_Name , Last_Name,Middle_Name"
l = re.compile("\s*,\s*").split(column_names)
print(l)

answered Dec 12, 2017 at 10:32

Masudul Hasan

1473 silver badges20 bronze badges

2 Comments

Vivek Kumar Over a year ago

Please add some explanation as to how this solves the question.

JKC Over a year ago

@Masudul Hasan This is good to split string variables but please explain when you provide an answer so that others users may understand easily. Thanks

Collectives™ on Stack Overflow

Python - How to split column values using multiple separators

3 Answers 3

4 Comments

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related