Pandas: Formula for “If Value in Column Then”

You can use the following syntax in pandas to assign values to one column based on the values in another column:

df['new'] = df['col'].map(lambda x: 'new1' if 'A' in x else 'new2' if 'B' in x else '')

This particular syntax will create a new column called “new” that takes on the following values:

• new1 if the value in col is equal to A.
• new2 if the value in col is equal to B.
• An empty string if the value in col is equal to any other value.

The following example shows how to use this syntax in practice.

Example: Using a Formula for “If Value in Column Then” in Pandas

Suppose we have the following pandas DataFrame that contains information about various basketball players:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'C', 'C'],
                   'points': [14, 22, 25, 34, 30, 12, 10, 18]})

#view DataFrame
print(df)

  team  points
0    A      14
1    A      22
2    A      25
3    A      34
4    B      30
5    B      12
6    C      10
7    C      18

Now suppose that we would like to create a new column called city whose values depend on the corresponding value in the team column.

We can use the following syntax to do so:

#create new column called city whose values depend on values in team column
df['city'] = df['team'].map(lambda x: 'Atlanta' if 'A' in x else 'Boston' if 'B' in x else '')

#view updated DataFrame                            
print(df)

  team  points     city
0    A      14  Atlanta
1    A      22  Atlanta
2    A      25  Atlanta
3    A      34  Atlanta
4    B      30   Boston
5    B      12   Boston
6    C      10         
7    C      18       

This particular syntax created a new column called city that takes on the following values:

• Atlanta if the value in team is equal to A.
• Boston if the value in team is equal to B.
• An empty string if the value in team is equal to any other value.

Note that in this example we used an empty string after the last else statement to simply leave values blank that didn’t meet any condition.

Additional Resources

The following tutorials explain how to perform other common operations in pandas:

Pandas: Get Index of Rows Whose Column Matches Value
Pandas: How to Select Columns Containing a Specific String
Pandas: How to Check if Column Contains String