python SQL like operator needs to return match from list of SQLs-CodePudding

I have a dataframe similar to.

Animal
------
Cat
Horse
Mouse

My list of SQL's are.

["SELECT * FROM df WHERE Animal LIKE '%og'", "SELECT * FROM df WHERE Animal LIKE '%at'", "SELECT * FROM df WHERE Animal LIKE '%ous'"]

My desired result needs to be.

Animal  sql
-----------
Cat     %at
Mouse   %ous

I have tried the below and tried to add a JOIN/INSERT with limited success.

from pandasql import sqldf
import pandas as pd

df  = {'Animal': ['Cat', 'Horse', 'Mouse']}
df = pd.DataFrame(df)
searchStrings = ['%og', '%ouse', '%at']

sqls = []
for i in searchStrings:
    j = "SELECT * from df where Animal LIKE '%s'" %(i)
    sqls.append(j)

dfNew = [sqldf(k).drop_duplicates() for k in sqls]
dfNew = pd.concat(dfNew)

thx!

CodePudding user response：

Use a dict instead of a list:

sqls = {i: f"SELECT * FROM df WHERE Animal LIKE '{i}'" for i in searchStrings}

out = pd.concat({key: sqldf(qs) for key, qs in sqls.items()}, names=['sql', None]) \
        .droplevel(1).reset_index().drop_duplicates()

Output:

>>> out
     sql Animal
0  %ouse  Mouse
1    %at    Cat