너가 차라리 여자라면 매달리기라도 할텐데...

푸바오와의 피튀기는 혈투도 어느새 1주차에 접어들었다. 이 정도 끈기로 사랑을 했다면 지금쯤 애가 있을 수도 있을텐데...라는 마음으로 푸바오에 대해 알아가는 와중, 나 외에도 많은 사람이 푸바오로 인해 밤잠을 설치고 있음을 알게 되었고, 이에 오랜만에 온보딩 주차의 전설적인 팀, 십오야 맴버들을 모았다.

오늘 TIL은 서버(인지 내 컴퓨터인지 알 수 없는) 이슈로 작성하지 못한 어제 TIL까지 작성한 이유로, 조금 이야기가 길어질 예정이다. 하지만 어차피 한 것 자체는 적어서 또 많이 길지는 않으리라 믿는다.

코드카타, 널 싫어해. but 너가 보고싶어. 이런 내 맴은 뭘까...?

코드카타는 총 SQL 4문제를 풀었다. 알고리즘은 이제 그냥 놓아버렸다. 난 그렇게 밀당 없는 일방적인 짝사랑은 싫다. SQL은 나에게 여지를 준다는 점에서 내 맘을 미치게 한다.

58번 문제

SELECT ad.APNT_NO, p.PT_NAME, ad.PT_NO, ad.MCDP_CD, ad.DR_NAME, ad.APNT_YMD
FROM 
    (
    SELECT a.APNT_NO, a.PT_NO, a.MDDR_ID, d.DR_NAME, d.MCDP_CD, a.APNT_YMD
    FROM 
        (
            SELECT APNT_YMD, APNT_NO, PT_NO, MCDP_CD, MDDR_ID, APNT_CNCL_YN, APNT_CNCL_YMD
            FROM APPOINTMENT
            WHERE APNT_CNCL_YN = 'N' 
            and DATE_FORMAT(APNT_YMD,'%Y-%m-%d') = '2022-04-13'
        ) as a
        JOIN
        (
            SELECT DR_NAME, DR_ID, MCDP_CD
            FROM DOCTOR
            WHERE MCDP_CD = 'CS'
        ) as d
        on a.MDDR_ID = d.DR_ID
    ) as ad
JOIN
    (
        SELECT PT_NO as PT_N, PT_NAME
        FROM PATIENT
    ) as p
on ad.PT_NO = p.PT_N
ORDER BY ad.APNT_YMD ASC

58번 문제는 쿼리가 정말 더럽게 길이가 길다. 하지만 그만큼 푸는 보람이 있던 문제였다. 사실 쿼리가 긴 것은 내가 SQL 문제에 접근하는 방식에 있다. 나는 WITH는 사용하지 않는 근본 서브쿼리파이기 때문이다. JOIN을 이따구로 풀면 나중에 함께할 동료들에게 돌팔매질을 당하진 않을까 걱정된다.

그렇다. 이 문제는 3중 조인이라는 장벽만 어떻게 넘는다면 전혀 어려울 것이 없는 문제이다. 정말 그것만 하면 된다. 아 물론 JOIN 내부 쿼리에 WHERE 조건을 걸어주기만 하면 본 쿼리는 상당히 간단한 편이다.

59번 문제

SELECT CAR_ID,
       max(
           if('2022-10-16' between start_date AND end_date,'대여중','대여 가능')
          ) AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

사실 쿼리가 짧아서 그렇지, 이 문제가 진짜 하이라이트였다. MAX 함수의 진정한 힘을 볼 수 있었기 때문이다. 아, 사실 그 힘은 잘 모른다. 지금도 왜 MAX를 써야하는지 모르기 때문이다. 예전에 같은 팀 맴버인 혜진님과 같이 풀었던 문제이기 때문에, 답을 쓰면서도 갸우뚱했다. 이건 좀 질문을 해봐야겠다.

60번 문제

SELECT YEAR(b.SALES_DATE) YEAR, MONTH(b.SALES_DATE) MONTH, a.GENDER, COUNT(distinct a.USER_ID) as USERS
FROM
    (
        SELECT *
        FROM USER_INFO
    ) as a
JOIN
    (
        SELECT *
        FROM ONLINE_SALE
    ) as b
on a.USER_ID=b.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY 1,2,3
ORDER BY 1,2,3 ASC

역시 어렵지 않다. 이런 문제는 그냥 시키는대로만 하면 답이 나오기 때문에, 쿼리의 길이가 얼마나 길든, 초장에 접근만 제대로 하면 된다. 이제 60번대 문제까지 오면서 느낀 점이지만, 문제를 잘 이해하고, 그 문제에 어떻게 접근할 것인지 전략만 잘 짠다면 어떻게든 문제를 풀어내는 편이다. 물론 앞으로도 그럴지는 불분명하다.

61번 문제

SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, ROUND(AVG(b.REVIEW_SCORE),2) SCORE
FROM
    (
        SELECT * 
        FROM REST_INFO
    ) as a
JOIN
    (
        SELECT * 
        FROM REST_REVIEW
    ) as b
on a.REST_ID=b.REST_ID
WHERE a.ADDRESS like '서울%'
GROUP BY 1
ORDER BY 6 DESC, 4 DESC

억까가 심각했던 문제였다. 분명 쿼리를 맞게 작성했는데 정답이 나오지 않아, 결국 자존심이 무너져 분노한 마음으로 SQL_raw에 가서 다른 사람의 답안지를 확인했다. 그리고 난 억장이 무너져버렸다.

서울이 주소인 곳을 찾기 위해 '%서울%'을 사용했는데, 그게 아니라 '서울%'을 사용해야했기 때문이다. 주소 맨 앞에 '서울시' 혹은 '서울특별시'라고 써있기 때문으로 추측되는데, 난 지금도 내가 왜 틀렸는지 모르겠다. 다른 지역에 서울이라는 구가 있는 걸까? 하, 너란 SQL 정말...

오 푸바오 풀긴 했어?

푸바오 개인 과제가 나왔고, 나는 처음에 정말 오랜 시간이 걸렸다. 1,2번 문제를 푸는데 1시간 정도가 걸린 것 같고, 3,4번은 각각에 그만큼씩 더 걸렸다. 하지만 난 과거 SQL에서도 그랬기 때문에, 괜찮다. 개인적으론 어떻게든 뭔가를 해보려는 내 모습이 불피우려고 애를 쓰는 오스트랄로 피테쿠스 같았다.

1번 문제

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from google.colab import drive
drive.mount('/content/drive')
df=pd.read_csv("/content/drive/MyDrive/Colab_Notebooks/flight_data_homework.csv”)
df.shape
df.iloc[[0,1,2,3,4],:]
or
df.head()

첫 문제는 flight_data라는 데이터프레임의 정보를 출력하고, 다섯 번째 행까지를 출력하는 문제였다. 막상 문제를 풀 때 head()를 사용하면 정확히 다섯 번째 행까지 출력한다는 것을 기억하지 못해 iloc 함수를 사용해 정확히 다섯 번째 행까지를 출력해달라는 코드를 작성해서 문제를 풀었다. 정말 무식하기 그지없는 방법이었지만, 난 이 방법을 후회하진 않는다.

2번 문제

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from google.colab import drive
drive.mount('/content/drive')
df.isnull().sum()
df=df.drop(['Route'], axis=1)
df=df.drop(['Total_Stops'], axis=1)

데이터프레임의 NULL, 즉 결측치의 개수를 확인하고, 결측치가 포함된 행을 제거하는 문제였다. 아쉬운 점은 Route와 Total_Stop 두 행을 한 번에 제거할 수 있게 코드를 짤 수 있을 것이라는 믿음을 버릴 수가 없다는 것이다. 저렇게 두 줄로 따로 작성한 것이 얼마나 하찮고 귀여워보이는지 참을 수가 없다. 난 분명 언젠가는 한 번에 쓸 수 있을 것이다.

3번 문제

df.groupby('Destination')['Price'].agg(['mean','median']).round(1)
df2=pd.DataFrame(df, columns=['Airline','Total_Stops','Route'])
df2.drop_duplicates(['Route'],keep='first',ignore_index=True)

첫번째는 destination을 기준으로 price의 평균, 중간값을 소수점 첫 번째 자리까지 표현하는 것이었다. groupby를 사용할 줄 알아야 했으며, round()도 사용할 줄 알아야 한다.

두 번째는 Airline과 Total_stops를 기준으로 Route 컬럼을 중복값 없이 추출하고, 인덱스를 재정렬하며, 이것을 df2라는 데이터프레임으로 받는 것이 문제였다. pd.DateFrame으로 데이터프레임을 형성하고, 컬럼값을 설정하여 Route컬럼에 drop_duplicates 펑션을 걸어주면 답을 구할 수 있다.

4번 문제

pd.pivot_table(df, index=['Source','Destination'], values='Airline', aggfunc='nunique').sort_values(by='Airline', ascending=False)

4번 문제는 도착지와 출발지를 기준으로 항공사의 수를 카운트하는 피벗테이블을 만드는 문제였다. 이게 1번이고, 2번도 있는데, 개인적으로는 1번이 핵심이라고 생각해서, 2번은 내일 제출 전에 5,6번과 풀어볼 생각이다.

이 문제에서 핵심은, 피벗테이블을 만들 때 반드시 모든 요소를 작성해야 한다는 고정관념을 깨야 한다는 것이다. 만약 이 문제에서

오 이거 airline 컬럼으로 걸어주면 되는 거 아님? 완전 개꿀!

라고 생각한다면 나처럼 2시간을 해매면서, 십오야 팀원들과 논쟁을 벌이게 된다. airline은 항공사가 적혀있는 문자열 함수이다. 즉, 구성 데이터의 타입이 str이기 때문에, 항공사의 수를 카운트해야 하는 컬럼으로는 죽었다 깨어나도 설정이 안된다. 직관적으로 생각하는 것과 실제 판다스 라이브러리 함수의 작용 사이에 간극이 발생하면서 발생한 참극인 셈이다.

또한 colimns 값에 airline을 넣고, values에 Route를 넣으면 데이터프레임이 만들어지긴 한다. 하지만 이 경우, 같은 항공사에서 출발지와 목적지가 같더라도, 직항과 경유를 개별로 취급하게 되는 문제가 발생한다. 애당초 문제는 '출발지와 도착지를 기준으로 항로를 운영하는 항공사의 수'를 카운트하는 것이었으니 이건 잘못된 접근이다.

1시간 동안 십오야 팀원들과 문제를 분석한 결과(심지어 두 분 다 아직 이 문제를 풀지 않으셨음에도 나를 위해 함께 고민해주셨다. 엑셀로 피벗테이블을 만들어서 확인까지 해주시면서. 이런 무능한 저라도 함께 해주셔서 고맙읍니다...), '아니 그럼 columns만 없으면 되는 거 아니냐고!'라는 홧김의 생각이 가장 답에 근접해보이는 데이트프레임을 뱉어내게 되었다.

다만 여전히 이 데이터프레임은 정답이라고 빋기가 어려웠다. 우선은 왜 한 공항에서 갈 수 있는 공항이 1개 뿐이냐는 것이며, 12개의 항공사가 있는데 가장 항공사가 많은 항로의 항공사가 10개라는 건, 2개 항공사는 간판만 있고 비행기가 안 뜬다는 것인가? 라는 생각이 머리를 떠나지 않았기 때문이다.

이것이 정답이 아니어도 크게 상관은 없다. 집단 지성으로 답을 도출해냈고, 그것이 제법 마음에 들기 때문이다. 이게 뭔 헛소리냐고 할 수도 있겠지만 말이다. 뒤늦게 스터디룸에 합류한 명서님이 우릴 보면서 무슨 생각을 하셨을지는 굳이 글로 남기고 싶지 않다. 보기엔 답답해보였겠지만, 그것은 집단지성이 만들어낸 숭고한 과정이었다고 믿는다.

죽어라 간다 푸바오, 각오해라

난 최근 판다스 라이브 세션을 복습하는데 모든 것을 집중하고 있다. 적어도 뭐가 있는지는 알아야 나중에 프로젝트에서 버벅거리진 않을테니까. 이번주까지 노트필기를 마치고, 메서드와 함수 용어 리스트를 만들어 언제든 확인할 수 있게할 예정이다.

현재 병합 부분 필기까지 마쳤고, 빠르게 이상치와 결측치 처리 이론과 실습을 진행해야 곧 있을 시각화도 따라갈 수 있을 것 같다.

누군가를 가르쳐주기 위해 복습한다

오늘 십오야의 팀원이셨던 연서님이 판다스로 고생하는 우리를 위해 복습 퀴즈를 만들어주셨으며, 피벗 테이블에 들어갈 연산함수를 외울 수 있는 비결도 알려주셨다. 연서님 덕분에 우리를 포함한 스터디룸의 모두가 많이 웃었다. 연서님은 우리에게 퀴즈를 내주시기 위해 오히려 복습을 하게 되었다고 하셨다.

누군가를 가르쳐주기 위해 문제를 만드는 과정은 아주 좋은 복습 방법이다. 내가 전공 공부를 할 때 이 방법을 많이 썼기 때문에 아주 잘 안다. 나도 이 방법을 사용해서 복습을 해봐야겠다.