Best way to replicate SQL "update case when..." with Pandas?

user_5377679 0
Viewed 68

I have this sample data set

City
LAL
NYK
Dallas
Detroit
SF
Chicago
Denver
Phoenix
Toronto

And what I want to do is update certain values with specific values, and the rest of it I would leave as it is.

So, with SQL I would do something like this:

update table1
set city = case 
when city='LAL' then 'Los Angeles'
when city='NYK' then 'New York'
Else city
end

What would be the best way to do this in Pandas?

pandas str-replace dataframe python
3 Answers

You can directly replace the values like this:

replacement_dict = {"LAL": "Los Angeles", "NYK": "New York"}
for key, value in replacement_dict.items():
    df['City'][df['City'] == key] = value
user_123928030

You can replace it using replace(). One option ist to define a dict.

Example

df = pd.DataFrame({'City':["LAL","NYK","Dallas","Detroit","SF","Chicago","Denver","Phoenix","Toronto"]})
df.replace({"LAL": "Los Angeles", "NYK": "New York"})
user_140587260

Use replace on the City column:

df['City'] = df['City'].replace({"LAL": "Los Angeles", "NYK": "New York"})

output:

          City
0  Los Angeles
1     New York
2       Dallas
3      Detroit
4           SF
5      Chicago
6       Denver
7      Phoenix
8      Toronto
user_163434640
Related