How to convert SQL query to python when it has "CASE" and "%" sign-CodePudding

I'm trying to convert our old sql codes to python and I have pandas DataFrame with tons of items. I have the below SQL query and I want to convert this to python code. However, it does have "CASE" and "%" sign. Probably I can use the "if" statement when it has "CASE". but what should I want to use efficiently transfer when it has a "%" sign? Also is it possible to use lambda in here with if statement?

CASE when `item_name`like '%Tulip%' then CONCAT('Tulip Market',                                                          
((CASE      WHEN (SUBSTRING(`Item`, INSTR(`Item`, '_') 1))

CodePudding user response：

There are several ways to convert SQL codes to Pandas codes.

One of options is that you can use apply and lambda as you mentioned.

I made a simple example code to show you how to implement the SQL code using Pandas (In particular, SQL code with LIKE and %), so that you can customized this code for your own code.

import pandas as pd

df = pd.DataFrame({
    'item_name': ['aTulip1', 'Tulip2', 'rose']
})

print(df)
#  item_name
#0   aTulip1
#1    Tulip2
#2      rose

df['item_name'] = df['item_name'].apply(lambda string: string   ' Market' if 'Tulip' in string else string)

print(df)
#        item_name
#0  aTulip1 Market
#1   Tulip2 Market
#2            rose