CODEWITHSUNDEEP
pythonsqlpandaswhere-clause
tintin
tintin

Reputation: 91

Similar pandas statement of SQL where clause

Two tables: Price list table PRICE_LIST:

ITEM   PRICE
MANGO    5
BANANA   2
APPLE    2.5
ORANGE   1.5

Records of sale REC_SALE (list of transactions)

ITEM   SELLLING_PRICE
MANGO    4
MANGO    3
BANANA   2
BANANA   1
ORANGE   0.5
ORANGE   4

Selecting records from REC_SALE where Items were sold less than the PRICE listed in the PRICE_LIST table 

SELECT A.*
FROM
(
    select RS.ITEM,RS.SELLING_PRICE, PL.PRICE AS ACTUAL_PRICE
    from REC_SALE RS,
         PRICE_LIST PL
    where RS.ITEM = PL.ITEM
) A
WHERE A.SELLING_PRICE < A.ACTUAL_PRICE ;

Result:

ITEM  SELLING_PRICE  PRICE
MANGO    4             5
MANGO    3             5
BANANA   1             2
ORANGE   0.5          1.5

I have these same two tables as dataframe in jupyter notebook what would be a equivalent python statement of the SQL statement above using pandas?

Upvotes: 1

Views: 661

Answers (2)

jezrael
jezrael

Reputation: 862581

Use merge with query:

df = pd.merge(df1, df2, on='ITEM').query('PRICE >SELLLING_PRICE')
print (df)
     ITEM  PRICE  SELLLING_PRICE
0   MANGO    5.0             4.0
1   MANGO    5.0             3.0
3  BANANA    2.0             1.0
4  ORANGE    1.5             0.5

Upvotes: 0

BENY
BENY

Reputation: 323226

merge with .loc

df1.merge(df2).loc[lambda x : x.PRICE>x.SELLLING_PRICE]
Out[198]: 
     ITEM  PRICE  SELLLING_PRICE
0   MANGO    5.0             4.0
1   MANGO    5.0             3.0
3  BANANA    2.0             1.0
4  ORANGE    1.5             0.5

Upvotes: 1

Related Questions