I have one column containing all the data which looks something like this (values that need to be separated have a mark like (c)):
UK (c)
London
Wales
Liverpool
US (c)
Chicago
New York
San Francisco
Seattle
Australia (c)
Sydney
Perth
And I want it split into two columns looking like this:
London UK
Wales UK
Liverpool UK
Chicago US
New York US
San Francisco US
Seattle US
Sydney Australia
Perth Australia
Question 2: What if the countries did not have a pattern like (c)?
Using apply() method If you need to apply a method over an existing column in order to compute some values that will eventually be added as a new column in the existing DataFrame, then pandas. DataFrame. apply() method should do the trick.
To create a new column, use the [] brackets with the new column name at the left side of the assignment.
You can create a conditional DataFrame column by checking multiple columns using numpy. select() function. The select() function is more capable than the previous methods. We can use it to give a set of conditions and a set of values.
Step by step with endswith
and ffill
+ str.strip
df['country']=df.loc[df.city.str.endswith('(c)'),'city']
df.country=df.country.ffill()
df=df[df.city.ne(df.country)]
df.country=df.country.str.strip('(c)')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With