Python VS SQL

많은 pandas 패키지를 이용하는 개발자들은 SQL에 어느 정도 익숙하므로 이번 포스팅에서는 pandas를 사용하여 다양한 SQL 작업을 수행하는 방법에 대한 몇 가지 예를 비교해서 설명하겠습니다. 본 포스팅은 pandas 홈페이지를 기반으로 하여 작성하였습니다.

 

 

관례적으로 다음과 같이 pandas와 NumPy를 가져옵니다.

import numpy as np
import pandas as pd

 

대부분의 예제는 pandas 테스트에서 찾은 tips 데이터 세트를 활용합니다. tip이라는 DataFrame으로 데이터를 읽고 동일한 이름과 구조의 데이터베이스 테이블이 있다고 가정합니다. 아래 데이터를 다운로드하세요.

 

tips.csv

0.01MB

 

 

 

tips = pd.read_csv("D:/python_exer/tips.csv")
tips 

 

 

SELECT

 

-- sql
SELECT total_bill, tip, smoker, time
FROM tips;

SELECT *, tip/total_bill as tip_rate
FROM tips;

# pandas
tips[["total_bill", "tip", "smoker", "time"]]
tips.assign(tip_rate=tips["tip"] / tips["total_bill"])

 

WHERE

 

-- sql
SELECT *
FROM tips
WHERE time = 'Dinner'

# pandas
tips[tips["time"] == "Dinner"]

--------------

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

# pandas
tips[(tips["time"] == "Dinner") & (tips["tip"] > 5.00)]

--------------

-- sql
SELECT *
FROM tips
WHERE size >= 5 OR total_bill > 45;

#pandas
tips[(tips["size"] >= 5) | (tips["total_bill"] > 45)]

 

NULL chek

 

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

# 위의 DataFrame과 동일한 구조의 테이블이 있다고 가정

-- sql
SELECT *
FROM frame
WHERE col2 IS NULL;

#pandas
frame[frame["col2"].isna()]

--------------------------

# not null 확인
-- sql
SELECT *
FROM frame
WHERE col1 IS NOT NULL;

# pandas
frame[frame["col1"].notna()]

 

 

 

 

 

GROUP BY

 

pandas에서 SQL의 GROUP BY 작업은 비슷한 이름의 groupby() 메서드를 사용합니다. groupby()는 일반적으로 데이터 세트를 그룹으로 분할하고 일부 기능(일반적으로 집계)을 적용한 다음 그룹을 함께 결합하는 프로세스입니다. 

 

일반적인 SQL 작업은 데이터 세트 전체에서 각 그룹의 레코드 수를 가져오는 것입니다. 예를 들어 성별이 남긴 팁 수를 가져오는 쿼리는 다음과 같습니다.

 

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

#pandas
tips.groupby("sex").size()

 

pandas 코드에서는 count()가 아닌 size()를 사용했습니다. count()가 각 열에 함수를 적용하여 각 열에 NOT NULL 레코드 수를 반환하기 때문입니다.

 

한 번에 여러 기능을 적용할 수도 있습니다. 예를 들어 팁 금액이 요일별로 어떻게 다른지 확인하고 싶다고 가정해 보겠습니다. agg()를 사용하면 그룹화된 DataFrame에 딕셔너리를 전달하여 특정 열에 적용할 함수를 표시할 수 있습니다.

 

-- sql
SELECT day, AVG(tip), COUNT(*)
FROM tips
GROUP BY day;

#pandas
tips.groupby("day").agg({"tip": np.mean, "day": np.size})

 

둘 이상의 열을 기준으로 그룹화하려면 열 목록을 groupby() 메서드에 전달합니다.

 

-- sql
SELECT smoker, day, COUNT(*), AVG(tip)
FROM tips
GROUP BY smoker, day;

# pandas
tips.groupby(["smoker", "day"]).agg({"tip": [np.size, np.mean]})

 

 

 

 

 

JOIN

 

JOIN은 join() 또는 merge()로 수행할 수 있습니다. 기본적으로 join()은 인덱스에서 DataFrames를 조인합니다. 각 메서드에는 수행할 조인 유형(LEFT, RIGHT, INNER, FULL) 또는 조인할 열(열 이름 또는 인덱스)을 지정할 수 있는 매개변수가 있습니다. 

 

# 간단한 임시 DataFrames 생성
df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})

 

DataFrames와 이름과 구조가 같은 두 개의 데이터베이스 테이블이 있다고 가정합니다. 이제 다양한 유형의 JOIN에 대해 살펴보겠습니다.

 

INNER JOIN

 

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

# pandas
pd.merge(df1, df2, on="key")

 

merge()는 또한 한 DataFrame의 열을 다른 DataFrame의 인덱스와 결합하려는 경우에 대한 매개변수를 제공합니다.

 

# pandas
indexed_df2 = df2.set_index("key")
pd.merge(df1, indexed_df2, left_on="key", right_index=True)

 

LEFT OUTER JOIN

 

-- sql
SELECT *
FROM df1 LEFT OUTER JOIN df2 ON df1.key = df2.key;

#pandas
pd.merge(df1, df2, on="key", how="left")

 

RIGHT JOIN

 

-- sql
SELECT *
FROM df1 RIGHT OUTER JOIN df2 ON df1.key = df2.key;

# pandas
pd.merge(df1, df2, on="key", how="right")

 

FULL JOIN

 

pandas는 조인된 열이 일치하는 항목을 찾았는지 여부에 관계없이 데이터 세트의 양쪽을 표시하는 FULL JOIN도 허용합니다.

 

-- sql
SELECT *
FROM df1 FULL OUTER JOIN df2 ON df1.key = df2.key;

# pandas
pd.merge(df1, df2, on="key", how="outer")

 

 

 

 

 

UNION

 

UNION ALL은 concat()을 사용하여 수행할 수 있습니다.

 

# 임시 DataFrames 생성
df1 = pd.DataFrame({"city": ["Chicago", "San Francisco", "New York City"], "rank": range(1, 4)}) 
df2 = pd.DataFrame({"city": ["Chicago", "Boston", "Los Angeles"], "rank": [1, 4, 5]})

-- sql
SELECT city, rank
FROM df1
UNION ALL
SELECT city, rank FROM df2;

#pandas
pd.concat([df1, df2])

 

SQL의 UNION은 UNION ALL과 유사하지만 UNION은 중복 행을 제거합니다.

 

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

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

 

LIMIT

 

-- sql
SELECT *
FROM tips
LIMIT 10;

# pandas
tips.head(10)

 

일부 SQL 분석 및 집계 함수에 해당하는 pandas

 

그룹당 상위 n개 행

 

-- sql
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;

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

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

-- sql : Oracle's RANK() analytic function
SELECT * F
ROM
__(
____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 < 2)에 대한 성별 그룹당(rank < 3)의 팁을 구해보겠습니다. rank(method='min') 함수를 사용할 때 rnk_min은 동일한 팁에 대해 동일하게 유지됩니다(오라클의 RANK() 함수와 동일).

 

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

 

UPDATE

 

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

# pandas
tips.loc[tips["tip"] < 2, "tip"] *= 2

 

DELETE

 

--sql
DELETE
FROM tips
WHERE tip > 9;

# pandas
tips = tips.loc[tips["tip"] <= 9]