python与SQL比较与转化实例操作

python与SQL比较与转化
由于许多潜在的pandas用户对SQL有一定的了解 ，因此本页面旨在提供一些如何使用pandas执行各种SQL操作的示例。

按照惯例，我们按如下方式导入pandas和NumPy：

import numpy as np
import pandas as pd
url = 'https://raw.github.com/pandas-dev/pandas/master/pandas/tests/data/tips.csv'
tips = pd.read_csv(url)
tips.head()

Out[11]:

sql(SELECT)和python进行转化：

 sql:

SELECT total_bill, tip, smoker, time
FROM tips
LIMIT 5;

python :

tips[['total_bill', 'tip', 'smoker', 'sex']].head(5)

输出结果： 

sql(WHERE)和python进行转化：

sql：

SELECT *
FROM tips
WHERE time = 'Dinner'
LIMIT 5;

python：

tips[tips['time']=='Dinner'].head(5)

 结果：

Out[71]:

另一种实现方法：

is_dinner = tips['time'] == 'Dinner'
is_dinner.value_counts()
tips[is_dinner].head(5)

 结果：

sql:

SELECT *
FROM tips
WHERE time = 'Dinner' AND tip > 5.00;

 python:

tips[(tips['time']=='Dinner')&(tips['tip']>5)]

数据源：

frame = pd.DataFrame({'col1': ['A', 'B', np.NaN, 'C', 'D'],
   ....:                       'col2': ['F', np.NaN, 'G', 'H', 'I']})

 sql：

SELECT *
FROM frame
WHERE col2 IS NULL;

python：

frame[frame['col2'].isna()]

 sql：

SELECT *
FROM frame
WHERE col1 IS NOT NULL;

python：

frame[frame['col1'].notna()]

sql(GROUP BY)和python的转化：

sql：

SELECT sex, count(*)
FROM tips
GROUP BY sex;

python：tips.groupby('sex').size()而不是count()

tips.groupby('sex').size()

Out[92]:

sex
Female     87
Male      157
dtype: int64

tips.groupby('sex').count()

count()也可以实现：

tips.groupby('sex')['total_bill'].count() 

Out[93]:

sex
Female     87
Male      157
dtype: int64

sql:

SELECT day, AVG(tip), COUNT(*)
FROM tips
GROUP BY day;
/*
Fri   2.734737   19
Sat   2.993103   87
Sun   3.255132   76
Thur  2.771452   62
*/

python:

tips.groupby('day').agg({'tip':np.mean,'day':np.size})

 sql：

SELECT smoker, day, COUNT(*), AVG(tip)
FROM tips
GROUP BY smoker, day;
/*
smoker day
No     Fri      4  2.812500
       Sat     45  3.102889
       Sun     57  3.167895
       Thur    45  2.673778
Yes    Fri     15  2.714000
       Sat     42  2.875476
       Sun     19  3.516842
       Thur    17  3.030000
*/

python： 

tips.groupby(['smoker', 'day']).agg({ 'day': np.size,'tip': np.mean})
tips.groupby(['smoker', 'day']).agg({'tip': [np.size, np.mean]})
#与上面等价
tips.groupby(['smoker', 'day'])['tip'].agg([('avg_balance','size'), ('stdev_balance','mean')])

JOIN 数据源

In [22]: df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'],
   ....:                     'value': np.random.randn(4)})
   ....: 

In [23]: df2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'],
   ....:                     'value': np.random.randn(4)})

sql： 

--INNER JOIN
SELECT *
FROM df1
INNER JOIN df2
  ON df1.key = df2.key;

 python：

pd.merge(df1,df2,on='key')

 第二种实现方式：

indexed_df2 = df2.set_index('key')
pd.merge(df1, indexed_df2, left_on='key', right_index=True)

Out[105]:

 sql：

--LEFT  JOIN
-- show all records from df1
SELECT *
FROM df1
LEFT  JOIN df2
  ON df1.key = df2.key;

pd.merge(df1,df2,on='key',how='left')

FULL JOIN
-- show all records from both tables
SELECT *
FROM df1
FULL OUTER JOIN df2
  ON df1.key = df2.key;

pd.merge(df1, df2, on='key', how='outer')

UNION
UNION ALL can be performed using concat().

In [30]: df1 = pd.DataFrame({'city': ['Chicago', 'San Francisco', 'New York City'],
   ....:                     'rank': range(1, 4)})
   ....: 

In [31]: df2 = pd.DataFrame({'city': ['Chicago', 'Boston', 'Los Angeles'],
   ....:                     'rank': [1, 4, 5]})
   ....: 

SELECT city, rank
FROM df1
UNION ALL
SELECT city, rank
FROM df2;
/*
         city  rank
      Chicago     1
San Francisco     2
New York City     3
      Chicago     1
       Boston     4
  Los Angeles     5
*/

pd.concat([df1,df2])

SELECT city, rank
FROM df1
UNION
SELECT city, rank
FROM df2;

pd.concat([df1,df2]).drop_duplicates()

SELECT * FROM tips
ORDER BY tip DESC
LIMIT 10 OFFSET 5;

tips.nlargest(10+5,columns='tip').tail(10)

-- Oracle's ROW_NUMBER() analytic function
SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn
  FROM tips t
)
WHERE rn < 3
ORDER BY day, rn;

 方法一

tips.assign(rn=tips.sort_values(['total_bill'],ascending=False).groupby(['day']).cumcount()+1).query('rn<3').sort_values(['day','rn'])

tips.assign(rnk=tips.groupby(['day'])['total_bill'].rank(method='first', ascending=False)).query('rnk < 3').sort_values(['day','rnk'])

-- Oracle's RANK() analytic function
SELECT * FROM (
  SELECT
    t.*,
    RANK() OVER(PARTITION BY sex ORDER BY tip) AS rnk
  FROM tips t
  WHERE tip < 2
)
WHERE rnk < 3
ORDER BY sex, rnk;

tips[tips['tip'] < 2].assign(rnk_min=tips.groupby(['sex'])['tip'].rank(method='min')).query('rnk_min < 3').sort_values(['sex','rnk_min'])

UPDATE
UPDATE tips
SET tip = tip*2
WHERE tip < 2;

tips.loc[tips['tip'] < 2, 'tip'] *= 2

--DELETE
DELETE FROM tips
WHERE tip > 9;

 tips = tips.loc[tips['tip'] <= 9]

开窗函数：

array =  [
    ['a', 2, 29],
    ['a', 1, 18],
    ['a', 0, 18],
    ['b', 2, 25],
    ['b', 4, 12],
    ['c', 6, 21],
]
data = pd.DataFrame(array, columns=["name", "class", "score"])

# 去重计数：nunique
data['nunique'] = data.groupby('name')['name'].transform('nunique')

# # 求和：sum

data['sum'] = data.groupby('name')['score'].transform('sum')
# # 均值：mean
data['mean'] = data.groupby('name')['score'].transform('mean')

# row_number
data['row_number'] = data.groupby('name')['class'].rank(ascending=True,method='first')  

结果：