Common SQL queries in Pandas

1. Data extract

- SQL

SELECT *
FROM history_dt 
LIMIT 5;

- Pandas

tips.head()

 

2. Filter data

- SQL

SELECT t1.STK_CD, t1.DT, t1.O_PRC
FROM history_dt t1
WHERE STK_CD='000020'
LIMIT 5;

- Pandas

tips.query("day=='Thur'").head()

or

tips.loc[tips['day']=='Thur'].head()

or

tips[tips['day']=='Thur'].head()

 

3. Sort data

1)

- SQL

SELECT t1.STK_CD, t1.DT, t1.O_PRC
FROM history_dt t1
WHERE STK_CD='005930'
ORDER BY t1.O_PRC
;

- Pandas

tips.sort_values('tip')

or

tips.sort_values('tip', ascending=False)

or

tips.sort_values('tip', ascending=0)

2)

- SQL

SELECT t1.STK_CD, t1.DT, t1.O_PRC, t1.H_PRC
FROM history_dt t1
ORDER BY t1.O_PRC DESC, t1.H_PRC DESC
LIMIT 5
;

or

SELECT t1.STK_CD, t1.DT, t1.O_PRC, t1.H_PRC
FROM history_dt t1
ORDER BY t1.O_PRC, t1.H_PRC DESC
LIMIT 5
;

- Pandas

tips.sort_values(['size','tip'],ascending=False)

or

tips.sort_values(['size','tip'],ascending=[True,False])

or

tips.sort_values(['size','tip'],ascending=[1,0])

 

4. Aggregate data

1)

- SQL

SELECT AVG(t1.O_PRC) AS avg_o_prc, 
       AVG(t1.H_PRC) AS avg_h_prc, 
       AVG(t1.L_PRC) AS avg_l_prc
FROM history_dt t1
LIMIT 5
;

- Pandas

tips.mean()

2)

- SQL

SELECT DISTINCT t1.STK_CD
FROM history_dt t1
;

- Pandas

tips['day'].unique()

 

3)

- SQL

SELECT COUNT(DISTINCT t1.STK_CD)
FROM history_dt t1
;

- Pandas

tips['day'].nunique()

 

5. Aggregate data by group

- SQL

SELECT t1.STK_CD, AVG(t1.O_PRC) AS avg_o_prc
FROM history_dt t1
GROUP BY t1.STK_CD
LIMIT 5
;

- Pandas

tips.groupby('day')[['tip']].mean()

or

tips.groupby('day')[['tip']].agg('mean')

 

referencd : https://towardsdatascience.com/writing-5-common-sql-queries-in-pandas-90b52f17ad76