[본캠프 36일차] SQL 공부, 머신러닝 공부, 파이썬 공부

 

나는 통계를 이해하고 싶은데, 다가갈수록 통계가 내게서 계속 멀어지는 이 상황을 한 단어로 설명하는 독일어 단어 없나.

 

오늘 한 일은,

• SQL 공부
  • [코드카타] SQL 3문제 풀기 (119~121번)
• 머신러닝 공부
  • [실무에 쓰는 머신러닝 기초] 1-3 수강하기
  • [실무에 쓰는 머신러닝 기초] 1-2~1-3 속 데이터 전처리 하는 과정에서 코드들 정리하기
• 파이썬 공부
  • 수준별 학습반: [python standard] 5회차(이론), 6회차(실습) 수강하기
• 세 번째 아티클 스터디 진행하기 (누적은 23번째(11+4+5+3))

---

 

SQL 공부: [코드카타] SQL 문제 풀기 (119~121번)

119. (185) Department Top Three Salaries

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
Write a solution to find the employees who are high earners in each of the departments.
Return the result table in any order.

 

250408 난이도 hard... easy, medium도 어려웠는데 hard는 얼마나 더 어려울까 (끄앍)

-- 첫 번째 제출한 쿼리 (정답 처리됨)
SELECT s.Department,
       s.Employee,
       s.salary
FROM (
    SELECT e.name Employee,
           e.salary,
           d.id,
           d.name Department,
           DENSE_RANK() OVER(PARTITION BY e.departmentId ORDER BY e.salary DESC) rnk
    FROM Employee e LEFT JOIN Department d ON e.departmentId = d.id
) s
WHERE s.rnk <= 3

난이도에 쫄았던 것에 비해선 문제가 쉬웠다.

 

이번에 문제 풀면서 찾아본 RANK()와 DENSE_RANK()의 차이를 정리해본다. 

[참고] [MySQL] 행의 순위를 만드는 함수 RANK, DENSE_RANK, ROW_NUMBER(),

• RANK() OVER() : 공동 순위가 발생하는 만큼 건너뛰어서 다음 순위를 부여함
  (ex. 1등이 1명, 2등이 2명, 그 다음 순위는 4등)
• DENSE_RANK() OVER() : 공동 순위만큼 건너뛰지 않고 순위를 부여함 
  (ex. 1등이 1명, 2등이 2명이더라도, 그 다음 순위는 3등)
• ROW_NUMBER() OVER : 공동 순위가 발생해도 무시하고 순위를 부여함 (=공동 순위가 아예 없고 다 다른 숫자를 가짐)
  (ex. 1등 1명, 2등도 1명, 3등도 1명, 4등도 1명)

 

120. (1667) Fix Names in a Table

Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.
Return the result table ordered by user_id.

SELECT user_id,
       CONCAT(UPPER(SUBSTR(name, 1, 1)), LOWER(SUBSTR(name, 2))) name
FROM Users
ORDER BY user_id

어려울 것 없이 바로 SUBSTR()으로 잘라서 각 부분을 UPPER()와 LOWER()로 변환해주고 CONCAT()으로 합쳤다.

이때 SUBSTR()에서 3번째 파라미터를 생략하고 파라미터를 2개만 입력할 시에 두 번째로 입력한 숫자부터 끝까지 가져온다는 것만 기억하면 되겠다.

 

121. (1527) Patients With a Condition

Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.
Return the result table in any order.

-- 첫 번째 작성한 쿼리 (오답 처리됨, testcase 13에서)
SELECT *
FROM Patients
WHERE conditions LIKE '%DIAB1%'

문제에서 요구한 조건에 정확히 부합하진 않지만 혹시나 싶어서 제출했는데 역시나 걱정했던 부분이 그대로 testcase에 있어서 틀렸다.

 

-- 두 번째 작성한 쿼리(정답 처리됨)
SELECT *
FROM Patients
WHERE (conditions LIKE 'DIAB1%') OR
      (conditions LIKE '% DIAB1%')

WHERE절에 조건을 추가해줬더니 통과됐다.

 

---

 

머신러닝 공부: [실무에서 쓰는 머신러닝 기초] 1-3 수강하기

오늘은 어제 수강한 1-2의 데이터 전처리 과정을 실습하는 1-3을 수강했다. 그래서 복습할겸 각 코드들을 꼼꼼하게 정리해보려 한다.

---

어제 공부한 이론 파트는 여기에 정리했다! :

[본캠프 35일차] SQL 공부, 머신러닝 공부

---

 

데이터 전처리 :①결측치 처리 (제거 / 대체)

# ----------------------------------------------------------
# 1) 결측치 처리
#    (1-1) 일부 행 제거
#    (1-2) 평균·중앙값 등으로 대체
# ----------------------------------------------------------

# (1-1) 일부 행 제거 (간단하지만 데이터가 줄어든다는 단점)
df_dropna = df.dropna()  # 모든 컬럼 중 결측값이 있으면 제거

# (1-2) 평균·중앙값 등으로 대체 (데이터가 줄진 않지만 잘못된 값으로 대체할 시 데이터를 왜곡한다는 단점)
df_fillna = df.copy() # 결측치를 대표값으로 대체할 데이터프레임 사본 생성

# 수치형은 열별 평균으로 대체 (mean)
df_fillna['num1'] = df_fillna['num1'].fillna(df_fillna['num1'].mean())
df_fillna['num2'] = df_fillna['num2'].fillna(df_fillna['num2'].mean())

# 범주형은 최빈값으로 대체 (mode)
# 최빈값이 여럿일 수도 있으니 .mode()까지만 코드를 입력해 최빈값들을 확인해보고서 .iloc[n]의 n에 들어갈 값을 선택할 수도 있음
most_freq_cat = df_fillna['cat_col'].mode().iloc[0]
df_fillna['cat_col'] = df_fillna['cat_col'].fillna(most_freq_cat)
# 날짜열은 제거하지 않고 그대로 둠(또는 임의 날짜로 대체 가능)
# -> 시연을 위해 NaT(결측)도 남겨둠

print("=== [결측치 제거 후 shape] ===")
print(df_dropna.shape)
print("=== [결측치 대체 후 shape] ===")
print(df_fillna.shape)
print()

• df.dropna() : 결측치가 있다면 해당 행을 삭제함
  • df.dropna(subset=['컬럼명']) : 입력한 컬럼에 결측치가 있을 경우에 해당 행을 삭제함
    입력하지 않은 컬럼에 결측치가 있다해도 입력한 컬럼은 잘 채워져 있다면 그 행은 삭제되지 않음
  • df.dropna(axis=1) : 결측치가 있다면 해당 컬럼을 삭제함
  • df.dropna(how= ) : 매개변수 how에는 전달인자로 'any'/'all'을 입력 가능
    • 'any' : 행에 결측치가 하나라도 있다면 제거함 [기본값]
    • 'all' : 행에 모든 값이 결측치라면 제거함
• df['컬럼명'].fillna(value) : 입력한 컬럼에 결측치가 있을 경우 괄호 안에 입력한 value로 대체
  • 수치형 데이터 → 주로 평균, 중앙값으로 대체 이상치의 영향을 덜 받아야겠다면 중앙값을 활용
    범주형 데이터 → 주로 최빈값으로 대체
  • .fillna(df['컬럼명'].mean()) : 입력한 컬럼의 평균으로 결측치를 대체
  • .fillna(df['컬럼명'].median()) : 입력한 컬럼의 중앙값으로 결측치를 대체
  • .fillna(df['컬럼명'].mode().iloc[0]) : 입력한 컬럼의 최빈값 중 첫 번째 값으로 결측치를 대체
    최빈값의 경우 값이 여럿일 수 있기에 .mode()를 통해 최빈값이 무엇인지 확인하고서 여럿이라면 어떤 값으로 채워넣을지 iloc[n]으로 선택할 수 있음

 

데이터 전처리 : ② 이상치 탐지 및 처리

※ 이상치를 처리하기란 결측치보다 더 까다롭다. 이상치로 판단하는 과정은 분석가의 합리적인 주관과 근거를 요구하기 때문인데, '무엇을 이상치로 간주할 것인지', '이상치를 어떻게 처리할지'는 도메인 지식을 활용해 판단해야 한다.

# ----------------------------------------------------------
# 2) 이상치 제거
#    - (2-1) 표준편차 기준 (mu ± 3*std)
#    - (2-2) IQR 기준
# ----------------------------------------------------------

# (2-1) 표준편차 기준 (mu ± 3*std)
df_outlier_std = df_dropna.copy()
# 각 컬럼의 평균과 표준편차를 구함
mean_num1, std_num1 = df_outlier_std['num1'].mean(), df_outlier_std['num1'].std()
mean_num2, std_num2 = df_outlier_std['num2'].mean(), df_outlier_std['num2'].std()

# mu ± 3*std : 평균에서 3σ를 뺀 값을 하한, 평균에서 3σ를 더한 값을 상한으로 구함 (σ:표준편차)
lower_num1, upper_num1 = mean_num1 - 3*std_num1, mean_num1 + 3*std_num1
lower_num2, upper_num2 = mean_num2 - 3*std_num2, mean_num2 + 3*std_num2

# 3σ의 범위를 벗어나는 값들을 필터링으로 제거함
df_outlier_std = df_outlier_std[
    (df_outlier_std['num1'] >= lower_num1) & (df_outlier_std['num1'] <= upper_num1) &
    (df_outlier_std['num2'] >= lower_num2) & (df_outlier_std['num2'] <= upper_num2)
]

# (2-2) IQR 기반
# IQR 방식으로 이상치를 탐지할 시 더 엄격하게 보기 때문에 해당 방식을 적용해 이상치를 제거할 시 더 많이 제거될 수 있음
df_outlier_iqr = df_dropna.copy()
# 1분위수, 3분위수 구하기
Q1_num1 = df_outlier_iqr['num1'].quantile(0.25)
Q3_num1 = df_outlier_iqr['num1'].quantile(0.75)
# IQR(사분범위) 구하기
IQR_num1 = Q3_num1 - Q1_num1

Q1_num2 = df_outlier_iqr['num2'].quantile(0.25)
Q3_num2 = df_outlier_iqr['num2'].quantile(0.75)
IQR_num2 = Q3_num2 - Q1_num2

# IQR 기준으로 1분위수에서 IQR에 1.5배한 수를 뺀 것을 하한, 3분위수에서 IQR에 1.5배한 수를 더한 것을 상한으로 
low_num1 = Q1_num1 - 1.5*IQR_num1
up_num1  = Q3_num1 + 1.5*IQR_num1
low_num2 = Q1_num2 - 1.5*IQR_num2
up_num2  = Q3_num2 + 1.5*IQR_num2

# 위에서 구한 범위를 벗어나는 값들을 필터링으로 제거
df_outlier_iqr = df_outlier_iqr[
    (df_outlier_iqr['num1'] >= low_num1) & (df_outlier_iqr['num1'] <= up_num1) &
    (df_outlier_iqr['num2'] >= low_num2) & (df_outlier_iqr['num2'] <= up_num2)
]

print(f"=== [이상치 제거 전 shape] : {df_dropna.shape}")
print(f"=== [표준편차 기준 제거 후 shape] : {df_outlier_std.shape}")
print(f"=== [IQR 기준 제거 후 shape] : {df_outlier_iqr.shape}")
print()

• 3시그마 규칙을 사용할 시 → 특별히 사용할 함수는 없음 그냥 .mean() .std()로 뚝딱뚝딱 구하면 됨
• IQR 방식을 사용할 시 → df['컬럼명'].quantile(n) : 수치형 데이터를 크기 순으로 정렬했을 때 n에다 0~100%(0~1) 위치를 입력하면 그에 해당하는 숫자를 반환함

 

데이터 전처리 : ③ 스케일링

※ 표준화(모든 피처들을 평균이 0, 분산이 1인 정규분포를 따르도록 함) 데이터 내 이상치가 있을 경우 적절한 스케일링 방식은 아님(∵이상치가 있다면 평균과 분산에 영향을 크게 주기 때문)

※ 정규화(모든 피처들을 0과 1 사이의 데이터 값을 갖도록 함) 데이터 내 이상치가 있을 경우 적절한 스케일링 방식은 아님(∵이상치가 있다면 이상치가 극값이 되어 데이터들이 좁은 범위에 분포할 것이기에)

# ----------------------------------------------------------
# 3) 스케일링: 표준화(StandardScaler), 정규화(MinMaxScaler)
#    - 예시로 df_outlier_iqr 를 사용
# ----------------------------------------------------------

df_scaled = df_outlier_iqr.copy()

scaler_std = StandardScaler()
scaler_minmax = MinMaxScaler()

df_scaled['num1_std'] = scaler_std.fit_transform(df_scaled[['num1']])
df_scaled['num2_std'] = scaler_std.fit_transform(df_scaled[['num2']])
df_scaled['num1_minmax'] = scaler_minmax.fit_transform(df_scaled[['num1']])
df_scaled['num2_minmax'] = scaler_minmax.fit_transform(df_scaled[['num2']])

print("=== [스케일링 결과 컬럼 확인] ===")
print(df_scaled[['num1','num1_std','num1_minmax','num2','num2_std','num2_minmax']].head())
print()

• 사용할 스케일링 방식을 변수에 할당해서 사용
  • 표준화 → StandardScaler() 
  • 정규화 → MinMaxScaler()
• 그리고 그에 .fit_transform() 메소드를 함께 사용
  • 스케일러.fit_transform(df['컬럼명']) : 해당 스케일러로 df의 해당 컬럼을 변환
  • [참고] [ML] fit_transform()과 transform() 근데 모르겠어... transform 개념은 알겠는데 fit이 뭔 소리... 모델을 학습??

 

데이터 전처리 : ④ 범주형 데이터 변환

※ 라벨 인코딩 : 순서가 있는 범주형 데이터 변환에 적합

※ 원-핫 인코딩 : 순서가 없는 범주형 데이터 변환에 적합

# ----------------------------------------------------------
# 4) 범주형 데이터 변환 (원-핫, 라벨 인코딩)
#    - 라벨 인코딩: cat_col
#    - 원-핫 인코딩: cat_col (또는 라벨 인코딩 후 다른 DF에 적용 가능)
# ----------------------------------------------------------

df_cat = df_scaled.copy()

# (4-1) 라벨 인코딩
label_encoder = LabelEncoder()
df_cat['cat_label'] = label_encoder.fit_transform(df_cat['cat_col'])

# (4-2) 원-핫 인코딩
df_cat = pd.get_dummies(df_cat, columns=['cat_col'])

print("=== [라벨 인코딩 + 원핫 인코딩 결과 컬럼] ===")
print(df_cat.head())
print()

• 라벨 인코딩 : LabelEncoder()를 변수에 할당 → 변수.fit_transform(df['컬럼명'])
• 원-핫 인코딩 : pd.get_dummies(df, columns= )로 컬럼 생성
  • df : 인코딩할 컬럼이 있는 데이터프레임 이름
  • columns : 인코딩 하고자 하는 범주형 데이터가 들어있는 컬럼명

 

데이터 전처리 : ⑤ 파생 변수 생성

# ----------------------------------------------------------
# 5) 파생변수 생성
#    - (5-1) 날짜 파생: 연, 월, 요일, 주말여부 등
#    - (5-2) 수치형 변수 조합
#
# ----------------------------------------------------------

df_feat = df_cat.copy()

# (5-1) 날짜 파생
df_feat['year'] = df_feat['date_col'].dt.year
df_feat['month'] = df_feat['date_col'].dt.month
df_feat['dayofweek'] = df_feat['date_col'].dt.dayofweek  # 월=0, 화=1, ...
df_feat['is_weekend'] = df_feat['dayofweek'].apply(lambda x: 1 if x>=5 else 0)

# (5-2) 수치형 변수 조합 예시: num1 + num2
df_feat['num_sum'] = df_feat['num1_std'] + df_feat['num2_std']


print("=== [파생 변수 생성 결과] ===")
print(df_feat[['date_col','year','month','dayofweek','is_weekend','num1','num2','num_sum']].head())
print()

• 날짜형 데이터로 파생 변수 생성할 때,
  •  df['컬럼명'].dt.year : 연(4자리 숫자)
  •  df['컬럼명'].dt.month : 월(숫자)
  • df['컬럼명'].dt.day : 일(숫자)
  • df['컬럼명'].dt.hour : 시(숫자)
  • df['컬럼명'].dt.quarter : 분기(숫자)
  • df['컬럼명'].dt.weekday_name : 요일(문자)
  • df['컬럼명'].dt.weekday : 요일(숫자) 0=월, 1=화, 2=수 …
    • df['컬럼명'].apply(lambda x: 1 if x >= 5 else 0): 요일(숫자)가 들어있는 컬럼을 사용해서 주말인지 평일인지 구분하기
  • df['컬럼명'].dt.days_in_month : 해당 월이 총 며칠인지
  • df['컬럼명'].dt.dayofyear : 이 날이 그 해의 몇 번째 날인지
• 수치형 변수를 이용해 파생변수는 필요에 따라 생성함

 

데이터 전처리 : ⑥ 다중공선성 점검

# ----------------------------------------------------------
# 6) 다중공선성 확인 (상관관계, VIF)
#    - 예시로 수치형 변수(num1, num2, num_sum, num1_log 등)만 확인
# ----------------------------------------------------------

# 상관계수로 점검하기
# 상관계수는 독립 변수들끼리 봄
df_corr = df_feat[['target',	'num1_std',	'num2_std', 'cat_label', 'year',	'month',	'dayofweek',	'is_weekend', 'num_sum']].dropna()
print("=== [상관계수] ===")
print(df_corr.corr())

# VIF 계산하기
# 10을 넘어가면 문제가 있는 것. 그런 경우 그런 변수들 중에 한 개만 살려놓는 식으로 처리함
def calc_vif(df_input):
    vif_data = []
    for i in range(df_input.shape[1]):
        vif = variance_inflation_factor(df_input.values, i)
        vif_data.append((df_input.columns[i], vif))
    return pd.DataFrame(vif_data, columns=['feature','VIF'])

vif_df = calc_vif(df_corr)
print("\n=== [VIF 결과] ===")
print(vif_df)
print()

• df.corr() : 두 컬럼 간 상관관계를 나타내는 상관계수를 보여줌
  • 상관계수는 수치형 데이터로만 계산할 수 있기 때문에,
    1. df를 애초에 범주형 데이터가 들어간 컬럼을 제거하거나
    2. 괄호 안에 매개변수 numeric_only=True로 입력한다
• calc_vif 함수가 데이터프레임 내 두 컬럼(=feature) 간 VIF를 구해줌 

 

데이터 전처리 : ➆ 불균형 데이터 처리

# ----------------------------------------------------------
# 7) 불균형 데이터 처리: SMOTE
#    - 타깃이 [0,1]로 되어 있고, 1이 매우 적은 상태
#    - SMOTE 적용 위해선 '피처'와 '타깃' 분리 필요
# ----------------------------------------------------------

df_smote = df_feat.copy().dropna(subset=['target'])  # 일단 이상치,결측 제거된 DF 사용
X = df_smote[['num_sum', 'cat_label',	'year', 'month',	'dayofweek',	'is_weekend']]  # 간단히 수치형 2개만 피처로
y = df_smote['target']

print("=== [SMOTE 전 레이블 분포] ===")
print(y.value_counts())

sm = SMOTE(random_state=42)
X_res, y_res = sm.fit_resample(X, y)

print("=== [SMOTE 후 레이블 분포] ===")
print(pd.Series(y_res).value_counts())
print()

• SMOTE(random_state=42)를 변수(sm)에 할당
  → X_res, y_res = sm.fit_resample(X, y)

---

 

파이썬 공부: [python standard] 5ᐧ6회차 수강하기

데이터 정규화와 변형

표준화

• 평균이 0이고 분산이 1인 정규 분포를 가진 값으로 변환
  • 데이터가 가진 분포의 모양을 바꾸지 않음. 그대로 유지됨. 
  • 원래 데이터가 정규 분포를 따르지 않았다면 표준화 결과 역시 비정규 분포임
• StandardScaler() 사용
• 대부분의 머신러닝 기법은 표준화를 요구함 (∵이상치에 크게 휘둘리지 않기에)

정규화

• 0~1 범위로 피처(값, 컬럼)을 변환
  • 값들의 차이는 그대로 유지한 채로 최솟값을 0, 최댓값을 1로 맞추는 것 비율을 유지한 채로 압축
  • 그래서 이상치에 민감한 방식이기에 반드시 이상치를 처리한 후에 정규화를 진행해야 함
• MinMaxScaler() 사용