Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I create a new column in a dataframe from an existing column using conditions?

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)?

like image 769
Tsatsa Avatar asked Jun 27 '19 13:06

Tsatsa


People also ask

How do you add a new column to a DataFrame based on another column?

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.

How do you create a new column derived from existing columns?

To create a new column, use the [] brackets with the new column name at the left side of the assignment.

How do I add a conditional column to a data frame?

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.


1 Answers

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)')
like image 128
BENY Avatar answered Oct 21 '22 08:10

BENY