CODEWITHSUNDEEP
pythonpandas
designil
designil

Reputation: 763

How to split one column into multiple columns in Pandas using regular expression?

For example, if I have a home address like this:

71 Pilgrim Avenue, Chevy Chase, MD

in a column named 'address'. I would like to split it into columns 'street', 'city', 'state', respectively.

What is the best way to achieve this using Pandas ?

I have tried df[['street', 'city', 'state']] = df['address'].findall(r"myregex").

But the error I got is Must have equal len keys and value when setting with an iterable.

Thank you for your help :)

Upvotes: 9

Views: 18587

Answers (2)

jezrael
jezrael

Reputation: 862921

You can use split by regex ,\s+ (, and one or more whitespaces):

#borrowing sample from `Allen`
df[['street', 'city', 'state']] = df['address'].str.split(',\s+', expand=True)
print (df)
                              address id             street          city  \
0  71 Pilgrim Avenue, Chevy Chase, MD  a  71 Pilgrim Avenue   Chevy Chase   
1         72 Main St, Chevy Chase, MD  b         72 Main St   Chevy Chase   

  state  
0    MD  
1    MD

And if need remove column address add drop:

df[['street', 'city', 'state']] = df['address'].str.split(',\s+', expand=True)
df = df.drop('address', axis=1)
print (df)
  id             street         city state
0  a  71 Pilgrim Avenue  Chevy Chase    MD
1  b         72 Main St  Chevy Chase    MD

Upvotes: 21

Allen Qin
Allen Qin

Reputation: 19957

df = pd.DataFrame({'address': {0: '71 Pilgrim Avenue, Chevy Chase, MD',
      1: '72 Main St, Chevy Chase, MD'},
     'id': {0: 'a', 1: 'b'}})
#if your address format is consistent, you can simply use a split function.
df2 = df.join(pd.DataFrame(df.address.str.split(',').tolist(),columns=['street', 'city', 'state']))
df2 = df2.applymap(lambda x: x.strip())

Upvotes: 3

Related Questions