• Datacamp 내 'Intermediate SQL' 강의 내용을 정리한 포스트입니다.

기본 개념

• 테이블(table): 데이터가 담긴 표
• 필드(field): 열(세로)
• 레코드(record): 행(가로)

이때, 테이블과 필드 이름은 소문자만 가능하며, 공백이 없어야 한다. 또, 테이블명과 필드명을 통틀어 중복이 없어야 한다.

(+) 쿼리 작성 후 마지막에는 세미 콜론(;)을 찍어준다!

쿼리 기초 문법

아래와 같은 형태의 테이블이 있다고 가정하자.

📍 Selecting

• SELECT 필드명: 특정 필드를 골라 나타낸다.

  #1 그냥 고르기
  SELECT 제목
  FROM 영화;
  
  #2 고르고 이름 바꾸기
  SELECT 제목 as 영화제목
  FROM 영화;

• COUNT(필드명): 레코드 수를 센다.

  • COUNT(*): 전체 테이블을 기준으로 레코드 수를 센다.
  • COUNT(필드명): 특정 필드를 기준으로 레코드 수를 센다.
  • COUNT(DISTINCT 필드명): 특정 필드에서 중복값을 제거한 후 레코드 수를 센다.

     SELECT COUNT(DISTINCT 연도) AS 
      FROM 영화;

📍 Filtering

• WHERE 조건: 특정 조건을 만족하는 행을 나타낸다.

  • 숫자일 때는 그냥 (ex: 연도 = 2023 )
  • 문자일 때는 따옴표 붙여서 (ex: 국가 = '한국')

    SELECT 연도
    FROM 영화
    WHERE 연도=2023;

• LIMIT 레코드 수: 몇 개의 레코드만 나타낼지 정한다.

     SELECT 연도
     FROM 영화
     WHERE 연도=2023;

• AND / OR / BETWEEN : 여러 개의 조건 다룸

  • AND: 앞뒤 조건 동시 만족
  • OR: 앞뒤 조건 중 하나만 만족
  • BETWEEN: 범위 포함 (양끝 포함 X)

    #1
    SELECT 제목
    FROM 영화
    WHERE 연도 > 1994
     AND 연도 < 2000;
    

  #2 (#1과 같은 의미) SELECT 제목 FROM 영화 WHERE 연도 BETWEEN 1994 and 2000;

  #3 SELECT * FROM 영화 WHERE 연도 > 2000

  AND 연도 < 2010
  AND 국가 = '한국';

• LIKE / NOT LIKE / IN

  • LIKE "표현식": 표현식을 만족하는 문자

  • NOT LIKE "표현식": 표현식을 만족하지 않는 문자

  • IN (a,b,c): 괄호 안에 포함되는 문자 (숫자 등 다른 데이터 타입도 가능)

    %: 0개 이상의 문자
    _: 문자 1개
    
    ex) 이름의 세번째 문자가 t?
    WHERE 이름 LIKE '__t%'
    
    ex) 1920,1930,1940 중 하나?
    WHERE 연도 IN (1920,1930,1940)

• NULL: 누락되거나, 알 수 없는 값 (missing value)

  • COUNT(필드명): 해당 필드의 null값 제외하고 셈

    • COUNT(*): 전체 다 셈

      #1
      SELECT COUNT(예산) AS 예산_정제
      FROM 영화;
      

  #2 SELECT COUNT(예산) AS 예산_정제 FROM 영화 WHERE 예산 IS NOT NULL;

  => 위 두 코드를 통해 동일한 결과를 얻을 수 있음!
  
  
  

📍 Aggregating & Arithmetic

<필드 내 연산>

• AVG(필드명), SUM(필드명), MIN(필드명), MAX(필드명), COUNT(필드명)

  • MIN, MAX, COUNT는 해당 필드의 레코드가 숫자형이 아닐 때도 적용 가능 (알파벳 비교, 날짜 비교 등)

  Q. 예산이 1,000,000원 이상인 영화들의 예산 간 합을 구하라.
  
  SELECT SUM(예산)
  FROM 영화
  WHERE 예산>1000000;

<레코드 내 연산>

• +, -, *, /

  SELECT 이익 - 예산 as 순이익
  FROM 영화
  WHERE (이익-예산) > 0;

  💡실행 순서는 코드 적는 순서와 달리 FROM -> WHERE -> SELECT 이므로, WHERE에는 SELECT에서 바꾼 이름 사용 불가

📍 Sorting & Grouping

• ORDER BY: 특정 필드 순서로 정렬

  • ORDER BY 필드명 ASC : (기본) 오름차순 정렬
  • ORDER BY 필드명 DESC : 내림차순 정렬

• GROUP BY: 특정 필드로 그룹짓고 요약 통계 제공

  • 그룹 짓지 않을 필드를 SELECT하면 오류 발생

  • 그룹 지은 후 필터링하려면 HAVING 사용

    Q. 연도별 영화 이익 합을 구하라. 
    (단, 이익이 1,000,000 이상인 영화를 대상으로 합을 구하며, 합이 10,000,000 이상인 연도만을 나타내라. 합 기준 내림차순으로 정렬하며, 상위 5개 연도만 나타내라.)
    
    SELECT 연도, SUM(이익) AS 이익합
    FROM 영화
    WHERE 이익 > 1,000,000
    GROUP BY 연도
    HAVING SUM(이익) > 10,000,000
    ORDER BY 이익합
    LIMIT 5;

💡실행 순서는 코드 적는 순서와 달리 **FROM -> WHERE -> GROUP BY -> HAVING - SELECT -> ORDER BY -> LIMIT** 이므로, HAVING에는 SELECT에서 바꾼 이름 사용 불가

노션 페이지에 차곡차곡 쌓아둘 예정-! (오역이 많을테지만 공부 목적이니까 ^_^...) 두 챕터씩 할 때마다 게시글 수정해서 업데이트해야지

1월 2주

Ch 2. Time series decomposition & Ch 3. Time series decomposition

Q. 고전적 분해법은 데이터를 과하게 평활도 시키는 동시에 이상치에도 민감한 것인가?

1월 3-4주

Ch 4. Time series features & Ch 5. The forecasters toolbox

(Ch 5.3) 예를 들어 평균 방식을 사용한다면, 적합값 y_t hat은 c hat(모든 가용 관측값의 평균)이다. 이때 가용 관측값에는 t 이후의 시간대도 포함된다. => Q. 미래의 걸 예측한다면 그 전까지의 값들을 다 평균내는 거 아니었나?

(Ch 5.8) 양수 오차보다 음수 오차에 더 큰 페널티를 부여한다는 것도 단점이다. 이를 보완하기 사용되는 것이 대칭적 MAPE, sMAPE이다. ⇒ Q. 양수 오차보다 음수 오차에 더 큰 페널티를 부여한다는 게 무슨 뜻?

2월 2주

Ch 7. Time series regression model

2월 3-4주

Ch 8. Exponential smoothing & Ch 9. ARIMA model

💡어떤 데이터?

https://news.sbs.co.kr/news/endPage.do?news_id=N1007399352

=> 요것도 한번 봐주시길 ㅎ

​ 이태원 참사 1주기 관련 보도에 들어가기 위한 분석이었고, 현재 위반건축물 상황이 어떤지를 보는 게 목적이었다. 특히, 할로윈을 앞두고 경찰이 설정한 고밀집 위험 골목에는 위반건축물이 얼마나 있는지 알아보면 좋겠다고 말씀해주셔서 분석을 시작했다. 말씀해주신 것 말고도 궁금한 거 이것저것 들여다보았다.

• 고밀집 위험 골목: 너비가 좁거나 경사가 가팔라, 인파가 몰릴 때 특별 관리가 필요한 마포, 강남, 용산 지역 16개 골목

​ 분석에 활용한 데이터는, 아래 '건물통합정보_마스터 - 오픈마켓'의 9월 데이터였다. 분석 시작 시점에 10월 데이터가 나와있지 않았어서, 우선 9월로 진행하고 추후 개별 건축물대장에서 여전히 위반 건축물인지 확인하는 작업이 이루어졌다. (그게 아쉬웠어서, 포스팅할 때는 11/6에 등록된 데이터로 해봤다. 현 시점 최신 데이터는 12월 13일 자 등록 데이터!) ​ http://data.nsdi.go.kr/dataset/12623 (대용량 건축물 대장)

💡어떤 데이터?

(1) 데이터를 어떻게 써야 하는가?

늘 익숙하게 쓰던 csv, xlsx 파일이 아니었기 때문에, 뭘 어떻게 봐야 하는지도 공부가 필요했다. 건물통합정보 마스터에서 데이터를 딱 다운받으면, 아래 사진과 같이 구성되어 있다.

shp & shx & dbf 는 꼭 같이 다녀야 하는 세트인데, 간단히 각 파일 형식에 대해 설명을 해보자면 아래와 같다.

• shp : 도형(geometry) 정보가 담긴 파일
• shx : 각 도형에 대한 인덱스가 담긴 파일
• dbf : 각 도형의 속성 정보가 담긴 파일

shp 파일은 지도 시각화 툴인 QGIS에 바로 올려서 (아래 그림처럼) 도형으로 나타나도록 하는 형태라면, dbf는 R에서 흔히 사용하는 데이터프레임의 형태라고 보면 된다! 우선 R로 전체 상황을 파악하려 하기 때문에, dbf를 불러올 것이다.

#install.packages('foreign')
library(foreign)

#공간정보
bldg_seoul_re <- read.dbf('이태원/F_FAC_BUILDING_서울_1106/F_FAC_BUILDING_11_202311.dbf', as.is = TRUE)

그냥 read_csv 나 read_xlsx로는 읽을 수 없어서! foreign 패키지를 깔고 read.dbf 함수로 읽어준다. 데이터는 준비 끝! ​

(2) 그래서 전체적으로 어떻게 변했나?

(출처: https://news.sbs.co.kr/news/endPage.do?news_id=N1007010590)

과거와의 비교가 필요하기 때문에, 선배가 작년 12월에 보도된 분석 결과를 보여주셨다. 2022년 12월 5일에 공개된 동일 데이터를 기준으로 했을 때, 위반 건축물의 수는 총 44,452건.

그럼 올해는 어떨까?

요 데이터프레임에는 건물의 층수, 건물 용도, 시군구 ID 등 여러 정보가 담겨 있는데, 그중 위반 건축물 여부를 나타내는 열은 VIOL_BD_YN이다. 이 값이 1이면 위반 건축물이라는 것이고, 0이면 아니라는 것이다.

그래서 서울시 전체 위반 건축물 수는 매우매우 간단하게 계산이 가능하다.

bldg_seoul_re %>% count(VIOL_BD_YN) %>% View()

VIOL_BD_YN == 1 인 건물은 총 44,760개. 작년 12월 데이터보다 308건 늘었다. 소폭 증가했지만, '증가했다'는 것만으로도 문제다. 그렇게 불법 건축물이 이슈가 돼도 줄긴 커녕 늘었다니.

​

(3) 구와 동별로 살펴보자면?

그럼 조금 더 자세하게, 구와 동 단위로 들어가보면 어떨까? 어느 구, 어느 동이 불법 건축물 비율이 가장 높을까?

건축물대장 데이터에 한글로 어느 구, 어느 동 쓰여있지 않고 코드로 쓰여 있기 때문에, 이를 해독할 수 있는 데이터를 다운받았다. 아래 링크 들어가면 검색과 데이터 다운 모두 가능하다!

https://www.code.go.kr/stdcode/regCodeL.do

건축물 대장 데이터에서 구에 대한 코드는** COL_ADM_SE** 열에 담겨 있었다. 11110 은 서울 종로구, 11560은 서울 영등포구, 요런 식이다.

#기본 패키지 불러오기
library(tidyverse)
library(stringr)
library(readxl)

#법정동 조회자료 불러오기
code <- read_xls('이태원/법정동코드 조회자료.xls')

#구별로 그룹을 지어 전체 건축물 수 / 위반 건축물 수 / 위반 건축물 비율 찾기
bldg_gu_ratio <- bldg_seoul_re %>% group_by(COL_ADM_SE) %>%
  summarize(total = n(),
            viol = length(which(VIOL_BD_YN=="1")),
            ratio = viol/total) %>%
  arrange(desc(ratio))

#법정동 코드 파일에서 다섯자리 구 코드 & 구 이름만 똑 떼어오기
gu <- code %>% filter((str_sub(법정동코드,6,10)=="00000") & (str_sub(법정동코드,3,5)!="000")) %>%
  mutate(COL_ADM_SE = str_sub(법정동코드,1,5)) %>%
  select(-법정동코드) 

#두 데이터 합치고, 비율 높은 순으로 정렬하기
bldg_gu_ratio_name <- merge(bldg_gu_ratio, gu, by='COL_ADM_SE')
bldg_gu_ratio_name %>% arrange(desc(ratio)) %>% View()

법정동 코드 조회 자료가 요렇게 생겼는데, 우선은 '구' 자료만 필요하기 때문에

(1) 35번째 자리가 000이 아닌 놈 & (2) 610번째 자리가 00000인 놈

으로 필터를 걸어서 구 데이터만 쏘옥 골라왔다. ​ 그렇게 정렬을 해보니, 광진구가 12.3%의 비율 (총 27,492개 건축물 중 3,380개의 위반 건축물) 로 서울시 전체 구 중 위반 건축물 비율 1등이었다. 이태원동이 있는 용산구는 6.3% (총 29,777개 건축물 중 1,877개의 위반 건축물) 로 9위. ㄴ.내가 사는 영등포구는 8.8% (총 27,627개 건축물 중 2,420개) 로 무려 4위! ​ 비율이 아닌 개수로 보면, 광진구 1위, 영등포구 6위, 용산구 9위였다. ​

구는 좀 크니까, 이제 동(법정동)으로 들어가보자. 건축물 대장 데이터에서 법정동 코드는, 동만 예쁘게 딱 나와 있지 않다. 이걸 찾느라 초반에 애를 좀 먹었는데.. 필지고유번호가 적혀 있는 PNU 열에 포함돼 있었다. ​ 필지 고유번호는 19자리로 구성되어 있는데, 구성은 아래 사진과 같다.

아까 얘를 다시 보면, 서울특별시 종로구 청운동의 경우 11(서울시)+110(종로구)+101(청운동) 이런 식인 것!

bldg_dong_ratio <- bldg_seoul_re %>% mutate(detail = str_sub(PNU, 1, 8)) %>%
  group_by(detail) %>%
  summarize(total = n(),
            viol = length(which(VIOL_BD_YN=="1")),
            ratio = viol/total)

dong <- code %>% filter((str_sub(법정동코드,6,10)!="00000")) %>%
  mutate(detail = str_sub(법정동코드,1,8)) %>%
  select(-법정동코드)

bldg_dong_ratio_name <- merge(bldg_dong_ratio, dong, by='detail')
write_csv(bldg_dong_ratio_name, 'bldg_dong_ratio_name.csv')

코드는 비슷하다. 다만 이번에는 PNU에서도 1~8번째 자리만 쏙 뽑고, 법정동 조회 파일에서도 610번째 자리가 00000이 아닌 놈을 골라서 18번째 자리만 쏙 뽑았다. ​ 동별로 따져보면, 중구 다동이 25.2%의 비율 (총 111개 건축물 중 28개의 위반 건축물) 로 서울시 전체 동 중 위반 건축물 비율 1등이었다. 줄세워놓고 보니 종로구와 중구가 상위권을 차지했다. 용산구 이태원동은 7.9% (총 35,47개 건축물 중 283개의 위반 건축물) 로 145위. 내가 사는 동네는 300등대였다.

​ 이태원 참사 이후 발의된 법안들을 살펴봤을 때, 본회의 통과된 건 아무것도 없었고, 특히 불법건축물 관련 법안은 마땅히 발의된 것조차 없었다. 이번 건축물 데이터를 살펴봤을 때 위반 건축물 수가 작년에 비해 늘어난 것을 보고, 씁쓸하면서도 '아 뉴스에 데이터 활용할 수 있겠다' 싶어 약간은 안도했던 내 자신이 좀 별로였다. ​ QGIS 활용한 건 2편에 -

That's me.

마지막으로 참여한 마부뉴스 정규레터 작업이 너무너무 재밌었어서! 꼭 남겨두고 싶었다.

QGIS 2탄은 잠시 미뤄두고... 우리 일러 웅니 얘기 쫌 할게요.

💡어떤 데이터?

다들 아마 아시겠지만 .... 테일러 스위프트 그녀는 타임지가 선정한(마부뉴스도 선정한 ㅎㅎ) 2023 올해의 인물!!!

올 3월부터 시작한 월드투어 The Eras Tour로 벌써 한화 1조가 넘는 수익을 벌어들였더랬죠.. (60회 공연으로 이미 1조 넘겼는데 앞으로 80회 넘게 남은.. (이미 앉아있지만 더더욱) 돈방석 위에 앉게 될 그녀)

​

도대체 그 투어가 뭐길래 이렇게 많은 이들이 찾았는지 알아보고자! Spotify에서 제공하는 각 음악의 특징을 활용해 투어의 구성을 알아보면 좋겠다고 선배가 제안해주셨다.

이번 레터하면서 처음 알았는데, 스포티파이는 API가 정말 잘 되어 있었다. Client ID & Secret Key 받는 방식도 아래 페이지에서 시키는 대로 했더니 정말 간단했다.

https://developer.spotify.com/documentation/web-api (API 활용법 참고 링크)

또, 해당 API에서 뽑아낼 수 있는 정보에 대한 설명도 정말 자세하고 친절했다. 설명은 아래 문서에서!

https://developer.spotify.com/documentation/web-api/reference/search (API 정보 참고 링크)

💡어떤 고민?

(1) 정보를 어떻게 빼올까?

다운로드 받을 수 있는 형태의 데이터 분석이나 크롤링은 많이 해봤지만 API 따오는 건 많이 안 해봤어서, 쿼리 날려서 정보 가져오는 걸 어떻게 해야할지 .. 고민하고 있었다.

바로 그 순간 등장한 나의 구세주, Spotipy 패키지

패키지 이름은 Spotify + python = Spotipy인 것 같은데, API 정보 뽑아내는 주요한 방법을 함수로 구현해둔 거였다... 짱편함 !!!!!!

https://spotipy.readthedocs.io/en/2.22.1/ (공식 다큐멘트)

공식 다큐멘트보다 아래 벨로그 글을 먼저 봤는데, audio feature 뽑는 등 분석 지향점이 비슷해서 API 파악에 도움이 많이 됐다 !!

https://velog.io/@mare-solis/%EC%8A%A4%ED%8F%AC%ED%8B%B0%ED%8C%8C%EC%9D%B4-API%EB%A1%9C-%EC%9D%8C%EC%95%85-%EB%B6%84%EC%84%9D%ED%95%98%EA%B8%B0

​

(2) 어떤 정보가 필요하고, 어떤 과정을 거칠 것인가?

선배께서, 우선 정규 앨범 전곡의 음악적 특징을 다 뽑아보고 필요에 따라 투어 곡들을 골라내는 게 좋을 것 같다고 의견을 주셔서 그렇게 해보기로 했다. 그렇다면 내가 필요한 것은?

** 1) 정규 앨범 목록과 고유 ID 값 2) 정규 앨범 내 트랙 목록과 고유 ID 값 3) 각 트랙의 음악적 특징 4) The Eras Tour 트랙 목록**

오케이 순서대로 가보잣. ​ 먼저, 패키지를 설치 및 장착하고 스포티파이에서 준 내 client ID 값과 Secret 값을 넣어준다.

pip install spotipy

import spotipy
from spotipy.oauth2 import SpotifyClientCredentials
client_credentials_manager = SpotifyClientCredentials(client_id='내 아이디', client_secret='내 비번')
sp = spotipy.Spotify(client_credentials_manager=client_credentials_manager)

​ *1) 정규 앨범 목록과 고유 ID 값 *

특정 아티스트의 앨범에 대한 정보를 얻기 위한 함수는 artist_albums. 그 아티스트의 고유 ID와, 얻고 싶은 앨범의 타입, 검색할 앨범 개수 등의 매개변수가 필요했다.

• album = 정규 / single = 싱글 / appears_on = 피처링 등 / compilation = 여러 가지 섞인 (소위 짜깁기) 음반

아티스트 고유번호는, 간단하게 search 함수를 이용해서 'Taylor Swift' 이름을 검색해 알아냈다.

track_results = sp.search(q='Taylor Swift', type='artist', limit=10, offset=0)

​ 06HL4z0CvFAxyc27GXpf02 요것이 스포티파이 속 테일러 스위프트의 고유 ID. ​ artist_albums 함수의 결과가 어떻게 생겼는지 아래 예시를 통해 살짝 뜯어보자면, 앨범 이름이나 고유 ID 값은 모두 'items' 라는 key 값에 대응하는 value 속에 들어있었다.

그리고 그 value 값이 리스트로 묶여 있는 것으로 보아, 여러 앨범이면 리스트 요소가 여러 개일 것. 개별 앨범의 속성에 접근하려면 리스트 각 요소에서 다시 찾고 싶은 key 값에 대응하는 value를 찾아야 했다.

artist_name =[]
album_name = []
album_id =[]
album_type =[]

albums = sp.artist_albums(artist_id='06HL4z0CvFAxyc27GXpf02', album_type='album', country=None, limit=20, offset=0)

#(1) 'items'에 대응하는 value 값: albums['items']
#(2) value에 해당하는 리스트의 각 요소 돌기
for each in albums['items']:
    artist_name.append(each['artists'][0]['name'])
    album_name.append(each['name'])
    album_id.append(each['id'])
    album_type.append(each['album_type'])

album_df = pd.DataFrame({'artist_name' : artist_name, 'album_name' : album_name, 'album_id' : album_id, 'album_type' : album_type})

​ 정규 앨범이 총 20개 이하니까 굳이 반복문 안 쓰고 limit=20, offset=0으로 걸어줬다. 찾은 값은 리스트에 넣어주고, 데이터프레임으로 만들기! 지금 우리가 얻은 정보는 *각 앨범명과 앨범 개별 ID​! *(앨범 타입은 이미 설정해뒀지만 확인용으로 추출 ㅎㅎ)

​ *2) 정규 앨범 내 트랙 목록과 고유 ID 값 *

특정 앨범 내 트랙 정보를 얻기 위한 함수는 album_tracks. 아까 얻은 개별 앨범 아이디 이제 쓰면 된다.

t_artist_name =[]
t_album_name = []
t_album_id =[]
t_track_name = []
t_track_id = []
t_track_duration = []


for id_index in range(len(album_id)):
    for i in range(0,200,20):
        tracks = sp.album_tracks(album_id=album_id[id_index],limit=20, offset=i)
        for each in tracks['items']:
            t_artist_name.append(each['artists'][0]['name'])
            t_album_name.append(album_name[id_index])
            t_album_id.append(album_id[id_index])
            t_track_name.append(each['name'])
            t_track_id.append(each['id'])
            t_track_duration.append(each['duration_ms'])

tracks_df = pd.DataFrame({'artist_name' : t_artist_name,
                          'album_name' : t_album_name,
                          'album_id' : t_album_id, 
                          'track_name' : t_track_name,
                          'track_id' : t_track_id ,
                          'track_duration_ms' : t_track_duration})

​ 얘 역시 Sample Response 보면서 구조 파악하고, 찾고 싶은 값을 찾아줬다. 아까 만든 album_id 리스트 살려서 야무지게 써먹기 ㅎㅎ

총 트랙 수는 limit으로 건 20개보다 분명 많을테니, 페이지 넘기듯이 '한번에 20개씩 찾되 총 200개 찾아줘' 라고 for 반복문을 걸었다. 이제 개별 트랙 이름과 ID, 곡 길이까지 땄다!

​ *3) 각 트랙의 음악적 특징 *

특정 앨범 내 트랙 정보를 얻기 위한 함수는 audio_features. 스포티파이에서 임의로 정한 값이긴 하지만, 그래도 음악을 정량적으로 표현하고자 했다는 점에서 신기했다. 분석 때 뭘 쓸지 모르니, 우선 가져올 만한 건 다 가져왔다.

어쿠스틱한 정도, 얼마나 댄서블한지, 얼마나 에너제틱한지, 악기 소리 비중은 어느 정도인지 등등 흥미로운 정보가 많았다. 아까 가져왔던 519개의 트랙 각각의 음악적 특징을 뽑아야 하기 때문에, 역시나 반복문을 활용해 필요한 정보를 리스트에 쭉 담은 다음, 앞서 추출한 트랙 정보와 합쳤다.

f_track_name = []
acousticness = []
danceability = []
f_duration_ms = []
energy = []
f_id = []
instrumentalness = []
key = []
liveness = []
loudness = []
mode = []
speechiness = []
tempo = []
time_signature = []
track_href = []
type = []
valence = []

#총 519개의 트랙
for track_idx in range(len(t_track_id)):
    features = sp.audio_features(t_track_id[track_idx])[0]
    f_track_name.append(t_track_name[track_idx])
    acousticness.append(features['acousticness'])
    danceability.append(features['danceability'])
    f_duration_ms.append(features['duration_ms'])
    energy.append(features['energy'])
    f_id.append(features['id'])
    instrumentalness.append(features['instrumentalness'])
    key.append(features['key'])
    liveness.append(features['liveness'])
    loudness.append(features['loudness'])
    mode.append(features['mode'])
    speechiness.append(features['speechiness'])
    tempo.append(features['tempo'])
    time_signature.append(features['time_signature'])
    track_href.append(features['track_href'])
    type.append(features['type'])
    valence.append(features['valence'])

features_df = pd.DataFrame({'track_name' : f_track_name,
                            'acousticness' : acousticness,
                            'danceability ' : danceability,
                            'duration_ms' : f_duration_ms,
                            'energy' : energy,
                            'track_id' : f_id,
                            'instrumentalness' : instrumentalness,
                            'key' : key,
                            'liveness' : liveness,
                            'loudness' : loudness,
                            'mode' : mode,
                            'speechiness' : speechiness,
                            'tempo' : tempo,
                            'time_signature' : time_signature,
                            'track_href' : track_href,
                            'type' : type,
                            'valence' : valence})

#track df랑 합치기!

track_features_df = pd.concat([tracks_df, features_df], axis=1)

​ 그럼 이제 앨범, 트랙, 트랙별 음악적 특징 모두 가져왔으니, 보여주기만 하면 되겠다!

​

(3) 무엇을 보여줄 것인가?

하단의 USA TODAY 기사에서 투어의 세트리스트를 얻었다. 얘는 어느 콘서트 기준인지 모르겠지만, 45곡 중 37번과 38번 곡은 매 공연 지역마다 달라지는 스페셜 트랙이라고 한다!

https://www.usatoday.com/story/entertainment/music/2023/03/18/taylor-swift-set-list-eras-tour/11499422002/

우선 어떻게 나오나 싶어서 위 45곡의 모든 음악적 특징을 순서대로 다 표현해봤다.

1) Energy와 Danceability의 흐름이 대체로 비슷했고, Acousticness의 흐름은 눈에 잘 보여서 재밌었다.

2) 혼자서 Duration_ms (곡 길이) 가장 높은 저 녀석이 10분 길이의 All Too Well이었는데, 저렇게 중반에 호흡 길게 가져가면 집중 팍 됐겠다! 싶었다.

3) Tempo도 오르락내리락 하는 걸 보니, 확실히 밀당 잘하는 재밌는 공연이었을 거라는 생각이 들었다.

4) Valence라는 지표도 꽤 흥미로운데, '노래의 긍정적인 정도'를 판단하는 지표였다. (1에 가까울수록 긍정적인 것) 장조 단조 여부로 판단하는 거려나? 기준은 잘 모르겠지만, 긍정 수치가 치솟은 34번 곡이 짱 신나는 Shake it off 였어서 왠지 믿음이 갔다.

​ 요렇게 그래프를 찬찬히 보면서, 콘서트를 설명하는 데에 도움이 될 만한 것들을 추려봤다. 1차로 고른 지표들은 Acousticness & Duration_ms & Energy & Valence 이렇게 4개! 스페셜 트랙 부분은 비워두고, 그래프에서 특징적인 부분을 콘서트 세트리스트를 이용해 간단히 설명하고자 했다.

• 이번 테일러 콘서트는 각 정규앨범 별로 섹션(era)이 나뉘어 구성되어 있었다. 예컨대 7집 Lover의 수록곡들을 부를 때는 'Lover Era'인 것이고, 그 다음 2집 Fearless의 수록곡들을 부를 때는 'Fearless Era'인 것이다. 그래서 콘서트 명도 The Eras! ​

(1) 7집, 2집에 해당하는 Era 이후에는 얼터너티브 록, 포크 풍의 9집 Evermore Era가 시작된다. 콘서트 직캠을 보니 숲처럼 꾸며둔 무대 위에서 피아노를 연주하며 노래했던데, 내가 팬이었음 울었을듯 ㅇㅇ ㅜ

(2) 요렇게 서정적인 Evermore Era가 끝나면.. 테일러의 흑화 시기..라고 불리는 6집 Reputation 수록곡들이 등장한다. 에너지 수치가 확 높아지는 걸 알 수 있다. 특히 '...Ready for it?' 을 부를 때 표범을 연상케 하는 옷을 입고 분위기를 뒤집어 놓으시더라.... (일러 선배님 짱)

(3) 앞서 언급했듯 테일러는 공연 중반부에, 기타 메고 홀로 10분 길이의 'All Too Well' 무대를 꽉 채웠다. 아무리 생각해도 무대 장악력이 어마어마한 가수다.

(4) 또 잔잔하다가 Energy와 Valence가 확 튀는 구간이! (내가 젤 사랑하는 앨범) 5집 1989 Era다. 'Shake it Off'도 포함된 ㅎㅎ

​ 하지만! 하나의 시각화로 너무 많은 정보를 전하기는 어렵고 그리 효과적이지도 않기 때문에, '콘서트의 분위기'를 보여주겠다 하는 목적에 부합한 딱 2개의 지표만 골랐다. 서로 교차되는 게 분명히 보이는 Acousticness와 Energy가 최종 선정 지표 되시겠다.

그래프를 겹쳐서 한 눈에 볼 수 있게 그리고, 그래프 아래에는 콘서트 구간 맞춰서 앨범 고유 색상으로 컬러바도 넣어줬다. 시각화 피드백 과정에서 이거 너무 10덕스럽다는 의견까지 나온,

결과가 어땠는지, 그리고 테일러 스위프트에 대해 무슨 말을 하고 싶었는지 더 궁금하다면 아래 기사 참조 - ㅎ.ㅎ

https://premium.sbs.co.kr/article/24fgp-tZEDM

​ 사실 지향점을 선배께서 먼저 제안해주셨기 때문에 분석과 시각화에 대한 아주 깊은 고민이 들어가진 않았지만, 데이터로 요런 것까지 할 수 있구나, 하는 생각이 들어 개인적으로 기억에 남는 경험이었다. (연말결산 제외) 마지막 정규 레터에서까지 소중한 경험 얻어가서 행복했다 -

💡어떤 데이터?

https://github.com/blushedmw/MabuChallenge/tree/53d372362a81744a6c9dd7f1db03fd8744211595/10%EC%9B%94_3%EC%A3%BC ㄴ 위 링크의 "dsat_dist_2020_10.xlsx" 파일을 활용했다.

시카고 대학교에서 만든 The Database on Suicide Attacks (DSAT)의 데이터로, 1982년부터 2019년까지의 자살테러 사건 관련 정보가 들어있다. sheet 2에는 공격 전반에 대한 정보가, 3에는 테러 주장에 대한 정보, 4에는 공격자에 관한 정보가 담겨있다. 그 중 내가 사용한 건 sheet 2!

💡어떤 고민?

(1) 아이디어를 섞고 싶다는 생각

여러 지역의 테러 정보가 있지만, 이-팔 지역에 초점을 두고 싶었고, 얼마나 큰 피해가 있었는지를 나타내고 싶었다. 그리고 데이터를 들여다보니 부상자, 사망자 수 뿐 아니라 민간인 사망자 수도 집계되어 있어 이것 또한 담아보고자 했다. ​ 그럼 이제 어떻게 보여줘야 할까.

이전에 US Mass Shooting data로 시각화했을 때, 선배께서 사망자와 부상자를 핏자국처럼 보이게 시각화하신 적이 있었는데, 그게 너무 신기했어서 나도 아이디어를 섞어보고 싶었다.

그 핏자국 시각화는 아래와 같은 느낌이다. (이번 데이터로 코드 카피해봤다) 색이 진한 원이 사망자 수, 회색 원은 사망자 + 부상자 수이다. (즉 전체 피해자 수!)

library(tidyverse)
library(lubridate)
library(readxl)

dsat <- read_xlsx('dsat_dist_2020_10.xlsx',sheet=2)

#핏자국 코드카피
dsat %>% filter((admin0_txt %in% c("Israel","Palestine")) & date_year>=1990) %>%
  mutate(date_x = as_date(paste('2020-',as.character(date_month),'-',as.character(date_day))),
         damaged = wounded_high+killed_high) %>%
  ggplot(aes(x=date_x, y=date_year)) +
  geom_point(aes(size=damaged), color='grey80', alpha=0.5)+
  geom_point(aes(size=killed_high), color='#C70050', alpha=0.5)+
  theme_minimal() +
  scale_x_date(date_breaks = "1 month", date_labels = "%b") +
  scale_size(range=c(0.1,18))+
  scale_y_reverse()+
  theme(legend.position = "none")

아!! ggblur 패키지 쓰면 저 점이 번져서 더더욱 의도한 느낌을 낼 수 있었는데,

install.packages('devtools')
devtools::install_github("coolbutuseless/ggblur")
install.packages('ggblur')
library(ggblur)

그걸 위해 필요한 저 위의 과정을 실행해보려다가

"네임스페이스 ‘pkgload’ 1.2.4는 로드되었으나 >= 1.3.0가 필요합니다" ㄴ 이딴 에러가 나를 열받게 하여서, 에러 고치면 다시 업뎃해보도록 하겠습니다, ​ (2) 레퍼런스 찾기

저렇게 원의 크기로 피해를 나타내는 것 말고 다른 게 뭐가 있을까 하다가, 세상엔 똑똑한 사람들이 참 많으니 전쟁 피해 시각화 관련 레퍼런스를 찾아보기로! 했다. War damage visualization .. Death data visualization ... 이런 키워드로 검색을 막 하다가 발견한, 심플하면서도 처절한 시각화. ​ South China Morning post에서 제작한, 미 연합군의 이라크 침공으로 인한 사망자 시각화이다. 쨍한 빨간색은 시민 사망자를 나타낸 것이고, 더 짙은 빨간색은 연합군 사망자이다.

https://www.scmp.com/infographics/article/1284683/iraqs-bloody-toll

검색하다가 함께 찾은 아래 사진을 보고, 역시 시각화는 보여주고자 하는 방향에 따라 천차만별이 될 수 있음을 다시금 깨달았다. 오른쪽 그림처럼 뒤집어 보면, 사망자가 감소하고 있었다 ^^ 라고 말할 수 있었을 테니 말이다. 전쟁 피해의 무게를 강조하기 위해 피가 흘러내리는 듯한 모양으로 시각화를 한 센스에 감탄했고, 이걸 따라해보기로 했다!

https://gravyanecdote.com/uncategorized/should-you-trust-a-data-visualisation/ (아래 사진 출처)

(3) 따라해보기

이제 시각화 진화 과정을 적어보겠다.

1) geom_col 버전

문제 1. 딱 방향만 뒤집어서 막대그래프(geom_col)로 나타낸 버전이다. (2000~2005년 사이 피해가 집중적으로 발생해서 이 구간만 시각화해보았다.) 막대가 네모져서 그런지, 뭘 나타내려 하는지 잘 모르겠다. 모양을 바꿔야 한다!

문제 2. 그리고 부상자, 사망자 수에 단순히 -1을 곱해 아래로 내리니, y축 라벨 값에 -200, -400 이렇게 적혀있다. 표현 방식을 바꿔주어야 한다!

문제 3. 또, (선배께서 짚어주신 부분인데) 지금 그래프에서는 빨간색(red)이 부상자, 가장 짙은 (red4) 빨간색이 사망자, 중간 짙은 빨간색 (red3)이 시민 사망자를 나타낸다. 그런데 부상자 수와 사망자 수는 별개이기 때문에, 이런 식으로 막대 위에 막대를 깔게 되면 부상자 수 안에 사망자 수가 포함되도록 잘못 해석할 수 있는 여지가 생긴다. 그러니, 가장 처음 두는 막대 레이어에 아예 부상자수 + 사망자수를 나타내는 게 좋겠다!

dsat %>% filter(admin0_txt %in% c("Israel","Palestine")) %>%
  mutate(attack_date_month = as_date(paste(as.character(date_year),'-',as.character(date_month),'-01')),
         wounded_high_rev = (-1)*wounded_high,
         killed_high_rev = (-1)*killed_high,
         killed_high_civilian_rev = (-1)*killed_high_civilian) %>%
  group_by(attack_date_month) %>% summarize(wdd_s = sum(wounded_high_rev),
                                            kill_s = sum(killed_high_rev),
                                            kill_c_s = sum(killed_high_civilian_rev)) %>%
  filter((attack_date_month >= as_date('2000-01-01')) & (attack_date_month <= as_date('2006-01-01'))) %>%
  ggplot(aes(x=attack_date_month)) +
  geom_col(aes(y=wdd_s), fill='red') +
  geom_col(aes(y=kill_s), fill='red4') +
  geom_col(aes(y=kill_c_s), fill='red3') +
  theme_minimal() +
  labs(title = 'Israel-Palestine Bloody toll',
       subtitle = '2000~2005') +
  theme(plot.title = element_text(hjust=0.5),
        plot.subtitle = element_text(hjust=0.5))

3) 이것저것 수정해본 버전

해결 1. geom_chicklet으로 둥글게 하고, 아까 전 사진에서 0 부근의 삐죽삐죽한 것들을 다듬어줬다. radius 값을 조정해준 건데, 좀 덜 둥글고 0 부근엔 아직도 둥근 값들이 조금씩 남아있지만 그래도 아까보단 낫다 ㅎ

해결 2. scale_y_reverse 를 써서 굳이 마이너스 안 붙이고도 아래로 내렸다!

해결 3. 부상자 수 + 사망자 수 더해서 피해자수 (damaged)로 제일 밑 막대 깔았다!

(+) 보기 좋게 하기 위하여... x축 y축 이름 바꾸고 연도 표시를 위로 올렸다. 그리고 (사랑해요 미리캔버스) 따로 범례 추가 ㅎㅎ

dsat %>% filter(admin0_txt %in% c("Israel","Palestine")) %>%
  mutate(attack_date_month = as_date(paste(as.character(date_year),'-',as.character(date_month),'-01')),
         damaged = wounded_high+killed_high) %>%
  group_by(attack_date_month) %>% summarize(dmg_s = sum(damaged),
                                            kill_s = sum(killed_high),
                                            kill_c_s = sum(killed_high_civilian)) %>%
  filter((attack_date_month >= as_date('2000-01-01')) & (attack_date_month < as_date('2006-01-01'))) %>%
  ggplot(aes(x=attack_date_month)) +
  geom_chicklet(aes(y=dmg_s), fill='red', color='red', radius=unit(0.3,'mm'), width=30) +
  geom_chicklet(aes(y=kill_s), fill='red4', color='red4', radius=unit(0.3,'mm'), width=30) +
  geom_chicklet(aes(y=kill_c_s), fill='red3', color='red3', radius=unit(0.3,'mm'), width=30) +
  theme_minimal() +
  scale_x_date(position = "top", breaks = "1 year", date_labels = "%Y") +
  scale_y_reverse()+
  labs(title = 'Israel-Palestine Bloody toll',
       subtitle = '2000~2005',
       x= 'Year', y= 'How many') +
  theme(plot.title = element_text(hjust=0.5),
        plot.subtitle = element_text(hjust=0.5))

세상에서 제일 주절대는 시각화일지 작성 완료..

최뱁새 열심히 해보겠음. 아자자 ^_^