split column regex dataframe python

user_12448331 0
Viewed 59

I have a column in a dataframe where in some rows I have the state, and sometimes just the city. For example in some rows I just have: 'Los Angeles', but in other rows I may have 'CA Los Angeles'.

I want to split that column into two new ones: states and cities, and if the state is not specified, then it can be blank. Something like this:

COLUMN STATE CITY
FL Miami FL Miami
Houston null Houston

I was thinking by maybe splitting using regex like '[A-Z][A-Z]\s' or something like that but I cannot make it work. Any ideas?

dataframe css python split regex
1 Answers

You can use

^(?:([A-Z]{2})\s+)?(.*)

See the regex demo. Details:

  • ^ - start of string
  • (?:([A-Z]{2})\s+)? - an optional occurrence of
    • ([A-Z]{2}) - Group 1: two uppercase ASCII letters
    • \s+ - one or more whitespaces
  • (.*) - Group 2: any zero or more chars other than line break chars as many as possible.

If you are using Pandas use

df[['STATE','CITY']] = df['COLUMN'].str.extract(r'^(?:([A-Z]{2})\s+)?(.*)', expand=False)
user_38329700
Related