[Pandas/MySQL] Rank

[Pandas/MySQL] Rank

 

---

데이터 생성하기

from sqlalchemy import create_engine
import numpy as np
import pandas as pd
engine = create_engine(MYSQL_CONNECTION_STRING)

%load_ext sql
%sql {MYSQL_CONNECTION_STRING}

n = 300
data = {
    '국어': np.random.randint(50, 100, size=n),
    '영어': np.random.randint(50, 100, size=n),
    '수학': np.random.randint(50, 100, size=n),
    '성별': np.random.choice(['남', '여'], size=n),
    '반': np.random.choice([f'{i}반' for i in range(1,10)], size=n)
}

df = pd.DataFrame(data)
df.to_sql(name='temp', con=engine, if_exists='replace', index=False)

• 이런 데이터가 있다고 할 때, 평균 성적이라든지 과목별 성적에 대해서 순위를 매겨야 하는 경우가 있다.
• 국어 점수에 대해서 순위를 매기는 경우를 생각해보자.

---

Pandas 쿼리

methods = ['average', 'min', 'max', 'dense', 'first']

for method in methods:
    df[f'rank_{method}'] = df['국어'].rank(method=method, ascending=False).astype(int)

result = df[['국어'] + [f'rank_{method}' for method in methods]].sort_values('국어', ascending=False).head(20)
result

• 같은 국어 점수라도, index에 따라서  rank first가 부여된 것을 확인할 수 있다.
• rank_average의 경우, 1~9까지 합인 45를 10으로 나눠서 4.5에 반올림 시킨 값인 5이다.

---

MySQL 쿼리

query = """
SELECT 
    국어,
    RANK() OVER (ORDER BY 국어 DESC) as `rank_min`,
    DENSE_RANK() OVER (ORDER BY 국어 DESC) as `rank_dense`,
    ROW_NUMBER() OVER (ORDER BY 국어 DESC) as `rank_first`
FROM temp
ORDER BY 국어 DESC
LIMIT 20;
"""

temp = %sql {query}
df1 = pd.DataFrame(temp)
df1

• GROUP BY와 사용할 때, PARTITION BY 기준컬럼을 추가적으로 적어주면 된다.

---

정리

average (평균)

• 동일한 값이 있을 경우, 해당 그룹에 동일한 순위를 매기고, 순위의 평균 값을 할당.
• 예를 들어, 세 학생이 국어 점수에서 공동 2등(90점)을 차지했다면, 이들 모두에게 (2 + 3 + 4) / 3 = 3 순위가 부여.

 

min (최소) / RANK()

• 동일한 값이 있을 경우, 해당 그룹에 동일한 순위를 매기고, 그룹에서 가능한 가장 낮은 순위를 할당.
• 예를 들어, 90점을 받은 세 학생은 모두 2등으로 표시됩니다.

 

 

max (최대)

• 동일한 값이 있을 경우, 해당 그룹에 동일한 순위를 매기고, 그룹에서 가능한 가장 높은 순위를 할당.
• 90점을 받은 세 학생은 모두 4등으로 표시됩니다.

 

 

dense (밀도 순위) / DENSE_RANK()

• 동일한 값을 가진 그룹에 동일한 순위를 매기고, 다음 순위가 자동적으로 증가하지 않고 연속적으로 붙음.
• 즉, 공동 순위가 있어도 다음 순위는 +1만 증가합니다.

 

 

first (출현 순서) / ROW_NUMBER()

• 동일한 값을 가진 경우, 데이터 순서에 따라 순위를 매깁니다. (위 예시는 index 번호로 매겨짐)
• 즉, 데이터 프레임에서 값이 나타난 순서대로 순위가 결정.