[내일배움캠프] 22일 차 - SQL 코드카타, Python 알고리즘 코드카타, 데이터 전처리&시각화 1, 아티클 스터디

 

 

 SQL 코드카타

 

 

문제 90

Confirmation Rate

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

☑ link

 

 

최종 답안

SELECT user_id,
       ROUND(confirmed_user / total, 2) AS confirmation_rate
FROM (
    SELECT S.user_id,
           COUNT(*) AS total,
           COUNT(CASE WHEN C.action = 'confirmed' THEN 1 ELSE NULL END) AS confirmed_user
    FROM Signups S
    LEFT JOIN Confirmations C
    ON S.user_id = C.user_id
    GROUP BY S.user_id) a;

 

✔️ 틀리지는 않았으나, Runtime 1098ms, Beats 5.04%로 개선 여지가 있다고 판단됨!

 

 

개선점

 

SQL에서 비율 구할 때 기존에 쓰던 템플릿(서브쿼리에 COUNT(CASE WHEN)) 대신 다른 템플릿 사용

 

 

새롭게 알게 된 것

 

[ANSI SQL] 그룹별 비율 구하는 템플릿

 

1. 윈도우 함수

• GROUP BY를 사용하지 않아 성능 상 이점이 있다

SELECT column1, (또는 DISTINCT column1) -- JOIN 시 중복 row 발생 가능성 있음
       ROUND(
        COUNT(CASE WHEN 조건 THEN 1 ELSE NULL END) OVER (PARTITION BY column1) 
        -- 그룹별 분자
        * 100.0 / COUNT(*) OVER (PARTITION BY column1), 2) AS ratio 
        -- 그룹별 분모
FROM table
WHERE -- 선택 사항
-- GROUP BY 사용하지 않는 것이 핵심
ORDER BY column1; -- 선택 사항

• 이 문제를 윈도우 함수 템플릿으로 풀기
  ✔️ Runtime 642ms, Beats 77.80%

SELECT DISTINCT S.user_id, -- S.user_id 중복 row 발생해서 DISTINCT 사용
       ROUND(
       COUNT(CASE WHEN C.action = 'confirmed' THEN 1.0 ELSE NULL END) 
             -- 실수 1.0 곱해 정수 나눗셈 방지 
       OVER (PARTITION BY S.user_id) / 
       COUNT(*) OVER (PARTITION BY S.user_id), 2) AS confirmation_rate
FROM Signups S
LEFT JOIN Confirmations C
ON S.user_id = C.user_id;
-- GROUP BY 없음

 

2. AVG()

• 한 그룹의 모든 값이 1 아니면 0일 때,
  (그룹의 평균 * 100) =  전체에서 1이 차지하는 비율

SELECT column1,
       ROUND(AVG(CASE WHEN 조건 THEN 1.0 ELSE 0.0 END) * 100, 2 
                        -- 1.0, 0.0 실수 사용해 정수 나눗셈 방지
             ) AS ratio
FROM table
GROUP BY column1;

• 이 문제를 AVG 템플릿으로 풀기
  ✔️ Runtime 672ms, Beats 57.81%

SELECT S.user_id,
       ROUND(AVG(CASE WHEN C.action = 'confirmed' THEN 1.0 ELSE 0.0 END), 2) 
       AS confirmation_rate
FROM Signups S
LEFT JOIN Confirmations C
ON S.user_id = C.user_id
GROUP BY S.user_id;

 

3. COUNT(CASE WHEN)   ✔️ 이번 문제에서 사용한 방법

• SQL 개인과제 포스팅 참고

SELECT column1,
       ROUND(conditional_count * 100.0 / total_count, 2) -- 비율 구하는 식
FROM
    ( SELECT column1,
             COUNT(*) AS total_count, -- 분모
             COUNT(CASE WHEN 조건 THEN 1 ELSE NULL END) AS conditional_count -- 분자
      FROM table
      WHERE -- 선택 사항
      GROUP BY column1
    ) AS count_data
ORDER BY column1; -- 선택 사항

 

참고 자료

• Gemini 검색 후 정리

 

 

 

 Python 알고리즘 코드카타

 

 

문제 47

문자열 내 마음대로 정렬하기

문자열로 구성된 리스트 strings와, 정수 n이 주어졌을 때, 각 문자열의 인덱스 n번째 글자를 기준으로 오름차순 정렬하려 합니다. 예를 들어 strings가 ["sun", "bed", "car"]이고 n이 1이면 각 단어의 인덱스 1의 문자 "u", "e", "a"로 strings를 정렬합니다. 인덱스 1의 문자가 같은 문자열이 여럿일 경우, 사전순으로 앞선 문자열이 앞쪽에 위치합니다.

☑ link

 

 

최종 답안 (구글링해서 품)

def solution(strings, n):
    answer = sorted(strings, key=lambda x: (x[n], x))
    return answer

 

 

틀린 이유 

 

문제의 정렬 조건이 2개이다.
여러 개의 조건을 기준으로 정렬해야 할 때 어떻게 해야 할지 몰랐다.

 

 

새롭게 알게 된 것

 

여러 개의 조건을 기준으로 정렬할 때 템플릿

• sorted(iterable, key=function, reverse=False)

# 리스트
my_list = []
sorted_my_list = sorted(my_list, key=lambda x: (x[n], x))  
# x는 리스트의 원소

# 딕셔너리를 원소로 갖는 리스트
data = [{}, {}, {}]
sorted_data = sorted(data, key=lambda item: (item['key1'], item['key2'])) 
# item 딕셔너리의 원소

# x가 숫자일 때 내림차순 정렬: -
sorted_my_list = sorted(my_list, key=lambda x: -x)

• sorted() 함수에 key=tuple이 입력되면
  • 튜플의 첫번째 요소를 기준으로 비교
  • 첫 번째 요소가 같다면 두 번째 요소를 기준으로 비교

 

 

 

 데이터 전처리 & 시각화 Pandas - 1

 

 

 

데이터 전처리 & 시각화 강의 3주차 : Pandas 위주로 정리 - 1

 

1. Pandas 알아보기

 

1) Pandas 알아보기

• pandas 라이브러리 불러오기

import pandas as pd

# 에러날 경우 terminal에 pip install pandas
#                  또는 pip3 install pandas

•  오픈소스 데이터셋 불러오기

import seaborn as sns
# seaborn은 데이터를 시각화 하는 라이브러리

data = sns.load_dataset('tips')
# tips라는 이름의 데이터셋을 불러와서 data 변수에 저장

data
# 데이터 출력

 

2) 데이터 불러오기

• 엑셀/CSV 데이터 불러오기

# pd.read_excel('파일경로/파일명.확장자')

# 엑셀 불러오기
pd.read_excel('파일경로/파일명.xlsx')

# 현재 위치에 있는 파일 불러옴
pd.read_excel('customer_data.xlsx')
pd.read_excel('./customer_data.xlsx')

# Users/jameskim 위치에 있는 파일 불러옴
pd.read_excel('/Users/jameskim/customer_data.xlsx')

# csv 파일 불러오기
pd.read_csv('파일경로/파일명.xlsx')

• 파일 경로 찾는 법
• Window
  • 파일 탐색기(File Explorer) 사용
    파일 탐색기를 열고 원하는 파일 또는 폴더를 찾은 후, 주소 표시줄(Address Bar)에 파일 경로가 표시됩니다. 이 경로를 복사하여 사용하거나, 마우스 오른쪽 버튼을 클릭하고 "속성(Properties)"을 선택하면 파일 또는 폴더의 속성 창에서 경로를 확인할 수 있습니다.
  • 명령 프롬프트(Command Prompt) 사용
    명령 프롬프트(cmd)를 열고, cd 명령어를 사용하여 원하는 디렉토리로 이동합니다. 그리고 pwd 명령어를 입력하면 현재 디렉토리의 경로를 확인할 수 있습니다.
• Mac
  • Finder 사용
    Finder를 열고, 원하는 파일 또는 폴더를 찾은 후, 상단의 경로 표시줄(Address Bar)에 파일 경로가 표시됩니다. 이 경로를 복사하여 사용하거나, 파일 또는 폴더를 선택한 후 마우스 오른쪽 버튼을 클릭하여 "정보 보기(Get Info)"를 선택하면 파일 또는 폴더의 경로를 확인할 수 있습니다.
  • 터미널(Terminal) 사용
    터미널을 열고, pwd 명령어를 입력하면 현재 작업 디렉토리의 경로를 확인할 수 있습니다.

 

3) 데이터 저장하기

# 엑셀로 저장
pd.to_excel(’파일경로/파일명.확장자’, index = False)

# csv로 저장
pd.to_csv(’파일경로/파일명.확장자’, index = False)

 

4) 인덱스

 

(1) 개념

• 데이터프레임(DataFrame) 또는 시리즈(Series)의 각 행 또는 각 요소에 대한 식별자
• 숫자, 문자
• 파일 불러올때 , 인덱스를 지정하는 것 가능

# 불러올 때 column1을 인덱스로 지정
pd.read_csv('./data/file.csv', index_col = 'column1') 

# 불러올 때 파일의 첫 번째 열을 인덱스로 지정
pd.read_csv('./data/file.csv', index_col = 0)
# 가장 왼쪽 열 0, 그 다음이 1, ...

 

(2) 종류

• 기본 인덱스
  데이터프레임 생성 시 자동으로 0부터 시작하는 정수 인덱스 제공됨

import pandas as pd

# 기본 정수 인덱스를 가진 데이터프레임 생성
df = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'c']})

>>>
   A   B 
0  1   a
1  2   b
2  3   c

• 사용자 지정 인덱스

# 사용자가 직접 인덱스를 설정한 데이터프레임 생성
df = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'c']}, index=['level1', 'level2', 'level3'])

>>>
        A   B 
level1  1   a
level2  2   b
level3  3   c

 

(3) 인덱스 활용

• .loc['row']: 특정 인덱스의 행에 접근

df = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'c']}, index=['idx1', 'idx2', 'idx3'])

# 특정 인덱스의 행에 접근
row = df.loc['idx2']

• .sort_index(): 인덱스 기준으로 데이터프레임 정렬

df = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'c']}, index=['idx1', 'idx2', 'idx3'])

# 인덱스를 기준으로 데이터프레임 정렬
sorted_df = df.sort_index()

>>>
index  A   B
idx1   1   a
idx2   2   b
idx3   3   c

# 특정 컬럼 기준으로 내림차순 정렬
sorted_df = df.sort_values(by='A', ascending=False)

>>>
index  A   B
idx3   3   c
idx2   2   b
idx1   1   a

• .set_index('column'): 특정 컬럼을 인덱스로 지정하기

# df가 가지고 있는 특정 컬럼 기준으로 인덱스 설정
data = df.set_index('column1')
data.head()  # 기본값 상위 5개 행 보여줌
             # head(n): 상위 n개 행 보여줌

• df.index: 인덱스 확인, 인덱스 새로 지정

#인덱스 확인
data.index

#리스트 형태로 인덱스를 새로 입력
data.index = ['1번' , '2번' , '3번']

• .reset_index(): 현재 인덱스를 0부터 시작하는 정수로 변경

# 0부터 시작하는 새 인덱스 제공
# 현재 인덱스는 그대로 유지되어 컬럼으로 변경됨
data.reset_index()

# 0부터 시작하는 새 인덱스 제공
# 현재 인덱스는 사라짐
data.reset_index(drop=True)

 

 

2. 데이터 전처리 - 컬럼

 

1) 컬럼 (Column)

 

(1) 예시

import pandas as pd

# 데이터프레임 생성
data = {
    '이름': ['Gerri', 'Angela', 'Deric'],
    '나이': [35, 40, 29],
    '성별': ['여', '여', '남']
}

df = pd.DataFrame(data)

# 각 컬럼 출력
print(df['이름'])  # '이름' 컬럼 출력

>>>
0  Gerri
1  Angela
2  Deric

 

(2) 활용

• name = ['컬럼명1', '컬럼명2', ...]: 컬럼명 변경

pd.read_csv('./data/file.csv', names = [’컬럼명1’, ‘컬럼명2’, … ,‘컬럼명15’])

• df.columns: 컬럼 확인, 컬럼명 변경

# 컬럼 확인하기
data.columns

# 리스트 형태로 컬럼명 새롭게 입력
data.columns = ['Korean_food', 'Mexican_food', 'Moroccan_food']
data

• df.rename(columns={'기존컬럼명': '새컬럼명'}): 특정 컬럼명만 변경

# 특정 컬럼명만 변경
# '기존컬럼명'을 '새컬럼명'으로 바꿉니다.
data = data.rename(columns={'기존컬럼명1': '새컬럼명1', '기존컬럼명2': '새컬럼명2'})

 

(3) 파일이 깨질 때

• 파일 인코딩 방식 정확하게 지정해야 올바르게 데이터 읽어올 수 있다

import pandas as pd

# UTF-8 인코딩으로 파일 불러오기
data = pd.read_csv('file.csv', encoding='utf-8')

# ASCII 인코딩으로 파일 불러오기
data = pd.read_csv('file.csv', encoding='ascii')

 

 

3. 데이터 전처리 - 데이터 확인

 

1) 데이터 확인 함수

• .head()
  데이터를 n개 행까지 보여준다

data.head()  # head(): 5개 행 데이터 보여줌
data.head(3)  # ()안의 숫자만큼 데이터 보여줌

• .Info()
  데이터의 정보를 파악합니다.
  인덱스, 컬럼명, 컬럼의 데이터 개수, 데이터 타입

data.info() 
# null 값을 확인할때도 활용

• .describe()
  데이터의 기초통계량 확인
  개수, 평균, 표준편차, 사분위, 중앙값

data.describe()
# 숫자값에 대해서만 기초통계량 확인 가능

 

2) 데이터 불러온 다음 확인할 사항

 

• 데이터 결측치(null)가 있는지?

# 결측치 확인: isnull()
df.isnull()
df.isnull().sum()  # 결측치 몇 개 있는지 세어줌

# 결측치 제거: dropna()
df.dropna()

• 중복 데이터는 없는지?

# 중복 데이터 확인
df.duplicated(subset=['컬럼1', '컬럼2', '컬럼3'])

# 중복 데이터 제거
df.drop_duplicates(subset=['컬럼1', '컬럼2', '컬럼3'])

• 데이터 이상치는 없는지?
  IQR (Interquartile Range) 방법 찾아보기
  참고

# IQR 계산
Q1 = df['컬럼1'].quantile(0.25)
Q3 = df['컬럼1'].quantile(0.75)
IQR = Q3 - Q1

# 이상치 기준 설정
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# 이상치 제거
df[(df['컬럼1'] >= lower_bound) & (df['컬럼1'] <= upper_bound)]

• 데이터 타입은 알맞게 들어가 있는지?

# 데이터 타입 변경
df['column_name'].astype(int)
df['column_name'].astype(float)
df['column_name'].astype(str)
df['column_name'].astype(bool)
df['column_name'].astype('category')
df['column_name'].astype('datetime64[ns]')
df['column_name'].astype(complex)
df['column_name'].astype(object)

 

3) 데이터 타입 변경

int64, int32	정수형 데이터 (64비트 또는 32비트)	1, 42, -10, 1000
float64, float32	부동 소수점 수 (64비트 또는 32비트)	3.14, -0.001, 2.718
object	문자열 데이터 (일반적으로 문자열)	'Hello', 'Data Science'
bool	불리언(참/거짓) 데이터	True, False
datetime64	날짜와 시간 데이터	'2023-12-31 08:00:00'
timedelta64	시간 간격(두 날짜 또는 시간 사이의 차이) 데이터	'3 days', '2 hours'
category	카테고리형 데이터 (제한된 고유 값으로 구성)	'Red', 'Blue', 'Green'

• astype()
  • pandas 데이터프레임의 열의 데이터 타입 변경
  • DataFrame['column_name']
    데이터타입 변경하고자 하는 열 지정
  • new_dtype
    변경하고자 하는 새로운 데이터 타입 명시

import pandas as pd

# 예시 데이터프레임 생성
data = {'integer_column': [1, 2, 3, 4, 5]}
df = pd.DataFrame(data)

# int → float
df['integer_column'] = df['integer_column'].astype(float) 
print(df.dtypes)  # 데이터프레임의 열 타입 확인

  

 

자료 출처

스파르타 코딩클럽 데이터 전처리 & 시각화 강의

 

 

 

 

 아티클 스터디

 

 

[아티클]

파이썬 초보자가 저지르는 10가지 실수

☑ link

 

---

 

[요약]

 

1. import*을 사용함

• 문제점
  • 비효율적
  • 변수명 충돌 위험성
• 해결법
  • 기존 from math import *
  • 개선 import math from math import pi
    
    

2. 예외 처리: ‘except’ 절에 예외를 지정하지 않음

• 해결법 try / except를 사용할 때 except 절에 예외 꼭 지정하기
  
  

3. 수학 계산에 Numpy 사용하지 않음

• 문제점 for loop보다 Numpy가 빠르다
• 해결법
  • 기존 for loop 사용 avg = sum() / len()
  • 개선 import numpy as np avg = .mean() 메서드 사용
    
    

4. 이전에 열었던 파일을 닫지 않음

• 해결법 with 구문에서 open, write/read, close 사용
  
  

5. PEP8의 가이드라인을 벗어남

• 해결법 PEP8을 읽고 가이드라인에 따른다
  
  

6. 딕셔너리를 사용할 때 .keys와 .values를 적절하게 사용하지 않음

• .keys 딕셔너리의 키만 보여주는 메서드
• .values 딕셔너리의 값만 보여주는 메서드
• 해결법
  • key 얻기 .keys 메서드 대신 for loop로 for key in dictionary: print(key)
  • value 얻기 for loop와 .items 메서드 조합 for key, value in dictionary.items(): print(value)
    
    

7. 컴프리헨션(comprehension)을 사용하지 않음 (혹은 언제나 사용)

• 해결법 for loop 대신 컴프리헨션 사용
  
  

8. range(len()) 사용

• 문제점 리스트를 반복할 때 range(len()) 사용
• 해결법 두 가지 리스트를 동시에 반복할 때 enumerate나 zip 사용
  
  

9. + 연산자를 사용한 문자열 연결

• 문제점 비효율적, 가독성 나쁨
• 해결법 f-strings 사용
  
  

10. Mutable value를 디폴트 매개변수로 사용할 때

• 문제점 이전 호출의 값을 계속 저장함
• 해결법 Mutable value(리스트 등)의 디폴트 값을 None으로 설정 if 구문 추가 if my_list is None: my_list = []

 

 

[핵심 개념 및 용어 정리]

 

Mutable

수정 가능한 것

• Immutable 객체: int, float, str, tuple
• Mutable 객체: list, dict, ndarray(Numpy의 배열)

 

참고 자료

☑ link

☑ link

 

 

디폴트 매개변수

def 함수이름(매개변수=디폴트값):
    실행문

함수이름()  # 값이 입력되지 않으면 디폴트값 자동 입력
함수이름(값)

 

참고 자료

☑ link

 

 

[인사이트]

 

• 4월부터 파이썬을 배우기 시작했다. 파이썬 초보자의 10가지 실수 중 일부는 공감되기도, 일부는 생소하기도 했다.
• PEP8 가이드라인이 있다는 것을 처음 알았다. 코드 샘플과 가이드라인이 제공된다고 하니 읽어봐야겠다. (5번)
• 파이썬 과제를 하면서 딕셔너리에 대한 이해도가 낮다는 것을 체감했다. .keys, .values, .items 메서드와 for loop를 이용해서 어떤 작업이 가능한지 새롭게 알 수 있었다. 코테를 풀 때 활용할 수 있을 것 같다. (6번)
• for loop 대신 comprehension 사용, range(len())대신 enumerate, zip 사용하는 것. 내 코드에서 개선할 점을 Gemini와 gpt에게 질문할 때마다 자주 나왔던 답이다. 컴프리헨션은 조금씩 써보는 중이고, 나머지는 아직 생소해서 시도하지 못했다. 이번 아티클을 계기로 enumerate, zip을 사용해 코테를 풀어야겠다. (7, 8번)