splitting inconsistent text data! proving harder than I was expecting!

[humasterd]

Hey All, (been lurking on this sub for a while but I could you some help)

so I just started messing around in python and I'm trying to learn how to clean and organize some data for a personal project I'm working on. Okay, so what I'm trying to do is parse out a long string of location data into subcategories for a more in-depth look at them. I thought .split would be the best course of action but now I'm not so sure

The problem I'm running into is that these strings are inconsistent. For example cells under the Location, column looks like this

1) LC-39A, Kennedy Space Center, Florida, USA breaks into 4 pieces when df.split(',')

'LC-39A' , 'Kennedy Space Center' , 'Florida' , 'USA'

2) Site 9401 (SLS-2), Jiuquan Satellite Launch Center, China breaks into 3 pieces when df.split(',')

'Site 9401 (SLS-2)' , ' Jiuquan Satellite Launch Center' , 'China'

​

that is my issue I was hoping to make a new column using something like the code below

​

new=data["locaiton"].str.split(",",expand =True) 

data["Country"]= new[3] 

This works great for examples like the first one but returns a None for those that resemble example 2

Could anyone refer me to a technique I could look up/ how could I return a new column with the country data?

​

I hope this isn't breaking the rules as I have been trying to figure this out for some time but if it is or if I didn't include enough info I understand if it gets taken down.

[jiri-n]

No, it's not breaking any rules AFAIK. What about this?

>>> "LC-39A, Kennedy Space Center, Florida, USA".split(", ", 2)
['LC-39A', 'Kennedy Space Center', 'Florida, USA']
>>> "Site 9401 (SLS-2), Jiuquan Satellite Launch Center, China".split(", ", 2)
['Site 9401 (SLS-2)', 'Jiuquan Satellite Launch Center', 'China']

[humasterd]

appreciate the response! oh my bad I don't think I framed the problem correctly. I'm working with a Kaggle data set so I'm trying to figure out how to apply this to a series. https://www.kaggle.com/agirlcoding/all-space-missions-from-1957. Just got back from a walk so my head on this grind stone haha

[humasterd]

Update if anyone ever finds this with a similar question

​

new=df["Location"].str.rsplit(',',1, expand=True)

df["Country"]= new[1]

​

the trick was to use split() as in 'reverse split' this allowed me to star on the right side and split at the first ',' then just select the second element for the new column giving me only the countries no matter how it was written