switch value between column based pandas-CodePudding

I have the following data frame:

     Name  Age      City Gender Country
0     Jane   23     NaN      F    London
1  Melissa   45     Nan      F    France
2     John   35     Nan      M   Toronto

I want to switch value between column based on condition:

if Country equal to Toronto and London

I would like to have this output:

   Name  Age      City  Gender Country
0     Jane   23     London   F     NaN   
1  Melissa   45     NaN      F    France
2     John   35     Toronto  M     NaN

How can I do this?

CodePudding user response：

I would use .loc to check the rows where Country contains London or Toronto, then set the City column to those values and use another loc statement to replace London and Toronto with Nan in the country column

df.loc[df['Country'].isin(['London', 'Toronto']), 'City'] = df['Country']
df.loc[df['Country'].isin(['London', 'Toronto']), 'Country'] = np.nan

output:

      Name  Age     City Gender Country
0     Jane   23   London      F     NaN
1  Melissa   45      NaN      F  France
2     John   35  Toronto      M     NaN

CodePudding user response：

You could use np.where:

cities = ['London', 'Toronto']
df['City'] = np.where(
    df['Country'].isin(cities),
    df['Country'],
    df['City']
)
df['Country'] = np.where(
    df['Country'].isin(cities),
    np.nan,
    df['Country']
)

Results:

      Name  Age     City Gender Country
0     Jane   23   London      F     NaN
1  Melissa   45      NaN      F  France
2     John   35  Toronto      M     NaN

CodePudding user response：

cond = df['Country'].isin(['London', 'Toronto'])
df['City'].mask(cond, df['Country'], inplace = True)
df['Country'].mask(cond, np.nan, inplace = True)

      Name  Age     City Gender Country
0     Jane   23   London      F     NaN
1  Melissa   45      NaN      F  France
2     John   35  Toronto      M     NaN