CODEWITHSUNDEEP
pythonsqlpandassqldf
leo
leo

Reputation: 441

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

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!

Upvotes: 0

Views: 169

Answers (1)

Corralien
Corralien

Reputation: 120469

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

Upvotes: 1

Related Questions