1. 문제 발생

유저 id , 마켓 , 국가 , 고객 유형, 매출이 각각 필드로 저장되어 있는 데이터가 있다.

유저들의 매출 총액을 마켓 , 국가 , 고객 타입별로 구하려고 하며 소계와 총계도 같이 보려고 한다.

즉 , 각 필드를 기준으로 CUBE 계산을 하려고 한다.
( CUBE 계산 : 결합 가능한 모든 값에 대하여 다차원 집계를 생성하는 것을 말함)

결과적으로 ,아래 이미지와 같이 집계 결과를 출력하면 된다.
( (ALL)로 적혀있는 부분이 소계라고 보면 된다.)

ROLLUP 구문을 이용하고 싶었으나, ROLLUP은 인수 순서에 따라
소계 집계가 계층별로 이루어져 모든 조합(필드)에 대한 소계치를 볼 수 없었다.
( [null]로 적혀있는 부분이 소계라고 보면 된다.)

아래 이미지가 rollup 구문으로 출력한 결과다.

-- rollup 구문 
SELECT
 market
 , country
 , customer_type
 , SUM(won) AS krw
FROM sample_table
WHERE 1=1
GROUP BY ROLLUP (market, country , customer_type)

즉, rollup 구문으로는 아래 이미지처럼 노란 형광펜으로 칠해진 부분을 구할수 없다.

어떻게 쿼리 구문을 짜야 모든 필드를 기준으로 소계를 구할수 있을까?

2. 문제 해결

UNNEST를 이용하여 배열을 만들고 ,그 배열을 소계를 구하고자 하는 필드에 덮어씌워
GROUP BY 해줌으로써 이를 구현할 수 있다.

말이 복잡해보일 수 있는데 , 정리하자면 아래 구문을 이용한다고 보면 된다.

-- UNNEST([필드1,'전체']) AS 칼럼 1 
	
SELECT
  필드1
  , SUM(won) AS krw
FROM sample_table
, UNNEST([필드1,'전체']) AS 필드1
WHERE 1=1
GROUP BY 필드1

소계 계산할 칼럼을 UNNEST로 묶어준다.
‘전체’를 값으로 넣은 배열을 만들어주고 UNNEST 해줘서 풀어버린후 집계해준다.
( 전체 대신 “ALL”로 전체치를 표시하고 싶으면 ‘전체’ 대신 “ALL” 을 넣으면 된다)

문제 상황에 적용해보면 아래 구문이 될것이다.

-- unnest로 cube하기 
(Google BIGQUERY)

SELECT
 market
 , country
 , customer_type
 , SUM(won) AS user_cnt
FROM  blog.post1_unnest_cube 
,UNNEST([market,'(ALL)']) AS market
,UNNEST([country,'(ALL)']) AS country
,UNNEST([customer_type,'(ALL)']) AS customer_type 
GROUP BY market,country,customer_type
;

-- unnest로 cube하기 
(PostgreSQL)

SELECT
 market2 AS market
 , country2 AS country
 , customer_type2 AS customer_type
 , SUM(won) AS krw
FROM blog.post1_unnest_cube 
, UNNEST(ARRAY[market,'(ALL)'] ) AS market2
, UNNEST(ARRAY[country,'(ALL)']) AS country2
, UNNEST(ARRAY[customer_type,'(ALL)']) AS customer_type2
GROUP BY market2,country2,customer_type2
;

집계 결과가 원하는대로 나온 것을 알 수 있다.

✋🏾 Google BigQuery을 이용하여 쓰인 포스팅입니다.

1. null과 관련된 조건식 _ coalesce, nullif, ifnull 란?

BigQuery 조건식 중에 null을 이용하는 조건식은 대표적으로 3가지가 있다.

• COALESCE(expr1,expr2,[expr3…….)
  
  • 인수로 들어간 표현식(expr) 중에 null이 아닌 첫번째 표현식 값을 반환한다.
    
  • 값이 반환되면 나머지 표현식은 평가되지 않는다.
    
  • 입력 표현식에는 모든 데이터 타입이 입력 가능하다.
    
    
• NULLIF(expr1, expr2)
  
  • expr1과 expr2가 값이 같으면, null을 반환하고 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2는 같은 상위 데이터 타입에 속해야하며, 비교 가능해야 한다.
    
    
• IFNULL(expr1, expr2)
  
  • expr1이 null이면 expr2를 반환한다. 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2에는 모든 데이터 타입이 입력될 수 있다.
    
  • COALESCE(expr1,expr2)와 같은 표현이다.
    
    

2. 예시

예시 데이터셋은 아래와 같다.

-- 예시 데이터 셋 제작 코드 
WITH ex_table AS ( 
    SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3 )
SELECT *
FROM ex_table;

위 예시 데이터에 null과 관련된 조건식( coalesce,nullif,ifnull)을 넣고 결과를 비교해보자
출력 결과는 테이블 맨 우측에 위치한 result 칼럼에서 확인할 수 있다.

• COALESCE 구문 예시
  
  

/*
coalesce 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3 )
SELECT
  *,
  coalesce(col1,col2,col3) as result_coalesce
FROM ex_table;

• NULLIF 구문 예시
  
  

/*
nullif 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 2 AS col2 , null AS col3 )
SELECT
  col1,
  col2,
 nullif(col1,col2) as result_nullif -- nullif는 expr 총 2개만 비교 가능
FROM ex_table

• IFNULL 구문 예시
  
  

/*
ifnull 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3 )
SELECT
  col1,
  col2,
 ifnull(col1,col2) as result_ifnull
FROM ex_table

(tableau) Spotify Music Characteristic Dashboard
태블로 퍼블릭 링크

1. 대시보드 소개

이 대시보드는 2010년부터 2019년까지 스포티파이의
인기곡 정보를 모아놓은 데이터를 태블로로 제작한 것이다.
(캐글의 Top Spotify songs from 2010-2019-By Year 데이터 활용)

“인기곡의 특성을 한눈에 볼 수 없을까?”라는 주제를 잡고 대시보드를 제작했다.

• Duration : 음악의 길이
  
• Tempo (bpm) : 음악의 빠르기
  
• Energy : 음악의 에너지
  
• Liveness : 음악의 생동감
  
• Valence: 음악이 주는 긍정적인 느낌
  
  

이 5가지 특성을 보았으며 Duration과 Tempo는 음악의 형식과 관련된 특성으로
Energy, Liveness, Valence는 음악이 주는 느낌과 관련된 특성으로 분류하였다.

이 분류에 따라 특성의 표현 색상을 구분했다. Duration과 Tempo는 회색으로
Energy,Liveness,Valence는 주황색으로 표현했다.

또한 각 특성값은 0~1의 값으로 정규화해준후 100을 곱해 100점 척도로 바꾸었다.
(MinMax Scaler 활용)

특성값을 100점 척도로 바꾼 이유는 2가지이다.

• 깂의 범위가 특성에 따라 차이가 커, 한 차트에 나타내기 쉽지 않기 때문이다.
  
• 100점 척도는 보는사람이 인식하기 쉽다.
  
  

위와 같은 작업을 거쳐 대시보드를 완성했다.

대시보드의 세부내용은 아래에서 살펴보자

2. 대시보드 세부 설명

각 인기곡의 5가지 특성 값을 보드를 이용해 나타냈다.

아래에서 언급할 DJ차트는 각 특성의 레이블이 차트에 표현되지 않는다.
따라서 이 보드를 통해 정확한 특성 값을 확인할 수 있도록 했다.

5가지 특성의 연도별 평균값을 이은 라인차트이다.
이 차트를 통해 각 특성의 평균값 추이를 볼 수 있다.

또한 DJ 차트를 통해 파악한 곡의 특성을 이 차트와 비교해
그 해의 유행과 유사한지를 알 수 있다.

전체 년도의 특성값을 보는 차트이기 때문에
이 대시보드에서 사용되는 필터(년도, 타이틀 기준)은 작동되지 않게 해놓았다.
동작무시를 사용하면 이 차트는 필터가 적용되지 않는다.

예를 들어 2019년의 Ed sheeran의 ‘I Don’t Care’는
2019년도의 유행하는 템포보다는 느리고 곡의 길이는 긴 것을 알 수 있다.

또한 이 곡이 주는 생동감과 에너지는 2019년의 유행보다는 적지만
긍정적인 느낌을 더 준다는 것을 파악할 수 있다.

년도를 선택하는 옵션칸이다.

이 옵션칸에서 선택한 년도 기준으로 곡 리스트 테이블이 세팅된다.
(필터로 작용)

이 대시보드의 핵심인 DJ차트이다.
DJ들이 사용하는 이퀄라이저의 형상을 본따 만든 차트이다.

곡 리스트 테이블에서 특정곡을 선택하면 그 곡의 특성값이 이 차트에 나타난다.

곡 리스트 테이블이다.
타이틀 - 아티스트 - 장르 - 인기도 순으로 구성되어있다.
인기도 값을 기준으로 내림차순 정렬해놨다.

위에서부터 아래로 인기있는 곡이라고 보면 될 것같다.

3. 피드백 및 느낀점

여태까지 작업했던 태블로 대시보드 중 가장 만족스럽게 나온 대시보드이다.
디자인과 구성이 깔끔하게 나와 뿌듯한 작업물이다.

이 대시보드의 포맷은 다른 데이터에도 충분히 활용될 수 있을 것 같다.
추후 다른 데이터에도 이 디자인과 구성을 활용해 대시보드를 제작해봐야겠다.

대시보드 링크 :https://public.tableau.com/app/profile/.31863300/viz/SpotifyMusicCharacteristicDashboard/1

1. 대시보드 소개

이 대시보드는 뉴욕의 2019년 AirBnB 데이터를 활용해 제작한 것이다.
“내가 뉴욕에 간다면 어떤 숙소를 선택하는 것이 좋을까?”라는 주제를 잡고 대시보드를 제작했다.

내가 여행을 할 때, 숙소를 정하는 기준은 크게 2가지이다.

• 가격 ( 숙소에 너무 큰 돈을 쓰고싶지는 않다 )
  
• 리뷰 수 ( 많은 리뷰 수와 좋은 평가는 인증된 숙소라는 느낌을 준다 )
  

이 대시보드는 위 두가지 기준 중 가격을 중점삼았고 태블로(Tableau)를 이용해 작업하였다.

2. 대시보드 세부 설명

숙소당 평균 숙박비용을 지역별로 비교한 차트이다.
많은 이들이 숙소를 선택할 때 숙박비용을 중요하게 생각한다.

이 차트는 평균 숙박비용을 지역별로 나누어 비교하였고,
이를 통해 숙소를 선택할 때 지역을 참고할 수 있도록 대시보드에 넣었다.

나의 경우, 맨해튼의 숙소들의 평균 숙박비용이 타 지역에 비해
약 2배이상 높기 때문에, 맨해튼의 숙소는 일단은 제외하는 방향으로 갈 것이다.

이 차트는 앞서 살펴본 숙소의 지역별 평균 숙박비용 차트를 보완하기 위해 넣어둔 차트이다.
지역별로 숙박비용의 분포를 살펴볼 수 있다.

이 차트를 보니, 맨해튼에도 충분히 낮은 가격대의 숙소가 많다는 점을 알 수 있었으며,
오히려 적당한 가격대(2k~4k)의 숙소가 타 지역보다 많다는 것을 알 수 있다.

따라서 나는 앞서 정한 방향과 달리 최종적으로 맨해튼 지역을 숙소를 찾을 지역으로 선택할 것이다.

종합적으로 앞서 차트에서 살펴본 가격대와 함께 다른 옵션(방 유형, 최소 숙박일수, 리뷰 수)도 선택할 수 있게 해 , 해당되는 숙소를 맵에 표시되게 하였다.

이를 통해, 내가 방문하고자 하는 숙소를 고를 수 있다.

(python) 파이썬으로 엑셀 파일 쪼개기

1. 문제 발생

각 사원들의 실적이 기록되어 있는 엑셀 파일이 있다.

실적 입력과 검수를 다 완료한 후 , 각 사원에게 본인의 실적이
기록된 엑셀 파일을 메일로 개별 전송하려고 한다.

엑셀을 일일이 열어서 파일을 개인별로 별도로
저장하는 것은 시간과 정확도에서 모두 비효율적이다.

이런 상황에서는 어떻게 해야할까?

2. 문제 해결 코드

파이썬을 이용해 엑셀 파일을 분할해 저장하는 작업을 할 것이다.
사원명을 기준으로 파일을 분할해보자

이 작업을 위해 판다스의 loc 구문과 to_excel,ExcelWriter구문을 활용한다.

예시코드를 아래에서 소개하겠다.

# 모듈 임포트 (판다스, 넘파이) 
import pandas as pd
from pandas import ExcelWriter
import numpy as np   

우선 판다스와 넘파이 모듈을 임포트해준다.

참고로 판다스와 넘파이 모듈은 데이터 작업을 할 때
자주 사용되니 기본적으로 임포트해주는게 편하다.

# 엑셀 파일 읽기 
sheet_1 = pd.read_excel("경로",header= , sheet_name= "")
sheet_2 = pd.read_excel("경로",header= , sheet_name= "")

엑셀을 시트별로 불러온다.

pd.read_excel 구문을 이용하여 엑셀파일을 불러온다.
pd.read_excel의 파라미터에 따라 , 원하는 옵션대로 불러올 수 있다.

pd.read_excel의 파라미터는 다음과 같다.

pd.read_excel ("경로", sheet_name="", header= , names= "", nrows= "" ) 

• 경로 : 불러오기 할 파일 경로
  
• sheet_name : 엑셀 파일에서 불러올 시트명 지정 ( 디폴트 값은 0이며, 별도로 지정하지 않으면 첫번째 시트가 설정된다.)
  
• header : 열 이름으로 사용할 행을 지정 ( 디폴트 값은 0이며, 별도로 지정하지 않으면 첫 행을 열의 이름으로 자동 지정된다.)
  
• names : 열의 이름을 리스트 형태로 지정한다. ( 디폴트 값은 None이다.)
  
• index_col : 인덱스로 사용할 열의 이름 또는 열의 번호를 지정한다. (생략하면 원본 데이터에 없는 0 부터 시작하는 행 번호가 첫번째 열에 추가된다.)
  
• usecols : 파일에서 읽어올 데이터의 열을 선택 ( 디폴트 값은 None이며, 별도로 지정하지 않으면 전체 열이 선택된다. 데이터로 가져올 열을 지정하는 방법은 콤마 또는 콜론으로 지정할 수 있다.
  
• nrows : 불러올 row 수
  
  

# 이름이 저장된 리스트 만들기 ( 인덱스 만들기 ) 
staff=np.unique(sheet_1["이름"].values).tolist() # 사원명 리스트 출력

이름이 저장된 리스트를 별도로 만들어
이름 칼럼을 기준으로 for문을 돌릴 것이다.
그러면 각각의 이름별로 파일이 분할된다.

파일을 분할하기 위해서
다음 코드를 응용할 것이다.

for s in staff:
	#1 파일명 생성
	file_name='.\_{}.xlsx'.format(s) # 파일이름 설정 (경로 포함)
	#2 저장할 데이터프레임 생성
	staff_save_df=df.loc[['staff_col']==s]
	#3 스태프 데이터프레임 저장되어있는
	staff_save_df.to_excel(file_name)

한 시트만 저장한다면 위 코드로 충분하지만 한 파일에 여러 시트를
저장하려면 판다스의 ExcelWriter 구문을 활용해야한다.

# 파일명 생성
# 칼럼명 선택
for s in staff_name:
    file_name=".\파일이름_{}.xlsx".format(s) # 파일 이름(경로 포함) 설정
    writer=pd.ExcelWriter(file_name,engine="xlsxwriter") # 시트 합치기 위해, xlsxwriter 사용
	# 시트별로 데이터 프레임 만들기
    staff_save_df_1=sheet_1.loc[sheet_1["칼럼명"]== "조건"]  
    staff_save_df_2=sheet_2[sheet_2["칼럼명"]== "조건"]  
    staff_save_df_3=sheet_2.loc[sheet_2["칼럼명"]== "조건" ]
    # 엑셀 파일로 변환
    staff_save_df_1.to_excel(writer,sheet_name="설정하고자 하는 시트이름") # staff_save_df_1 : 분할 파일 1번 시트에 들어감
    staff_save_df_2.to_excel(writer,sheet_name="설정하고자 하는 시트이름") # staff_save_df_2 : 분할 파일 2번 시트에 들어감 
    staff_save_df_3.to_excel(writer,sheet_name="설정하고자 하는 시트이름") # staff_save_df_3 : 분할 파일 3번 시트에 들어감
    writer.save()

이렇게 하면 엑셀파일을 이름 기준으로 각각의 파일로 분할할 수 있다.

처음 소개한 예시상황에 대입하면 코드는 다음과 같다.

import pandas as pd
from pandas import ExcelWriter
import numpy as np   

sheet_1 = pd.read_excel("./excel_split/2021년 12월 실적현황.xlsx" ,header=0 , sheet_name= "MAIN")
sheet_2 = pd.read_excel("./excel_split/2021년 12월 실적현황.xlsx" ,header=0 , sheet_name= "RAW")

staff=np.unique(sheet_1["이름"].values).tolist()

for s in staff:
    file_name="./excel_split/2021년 12월 실적현황_{}.xlsx".format(s) # 파일 이름(경로 포함) 설정
    writer=pd.ExcelWriter(file_name,engine="xlsxwriter") # 시트 합치기 위해, xlsxwriter 사용
	# 시트별로 데이터 프레임 만들기
    staff_save_df_1=sheet_1.loc[sheet_1["이름"]==s]  
    staff_save_df_2=sheet_2[sheet_2["이름"]== "s"] # 시트 2도 이름별로 분할
    staff_save_df_2=staff_save_df_2[staff_save_df_2["인센티브"]=="대상"]] # 추가 조건있다면 설정 

    # 엑셀 파일로 변환
    staff_save_df_1.to_excel(writer,sheet_name="MAIN") # staff_save_df_1 : 분할 파일 1번 시트에 들어감
    staff_save_df_2.to_excel(writer,sheet_name="MAIN_INCEN") # staff_save_df_2 : 분할 파일 2번 시트에 들어감 
    writer.save()