(Tableau) NewYork AirBnB Dashboard

05 Feb 2022 in 분석 프로젝트 on 대시보드

내가 선택할 에어비앤비 숙소를 골라보자

1. 대시보드 소개

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

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

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

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

2. 대시보드 세부 설명

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

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

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

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

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

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

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

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

(Python) 엑셀 파일 쪼개기 _이름을 기준으로

12 Jan 2022 in 가이던스 on Python

파이썬을 이용해 엑셀 파일을 분할하자

(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()

(SQL) 이동평균 구하기

09 Dec 2021 in 가이던스 on SQL

(SQL) 이동평균 구하기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

1.이동평균이란?

이동평균이란 추세의 변동을 알 수 있도록 구간을 옮겨 가면서 구하는 평균을 말한다.

예를 들어 7일간 이동평균은 1~7일 평균, 2~8일 평균, 3~9일 평균 … 처럼
가장 오래된 데이터 대신에 최신의 데이터를 넣어 평균을 구하는 것을 말한다.

즉, 이동평균은 정해진 기간에서의 데이터의 평균이라고 할 수 있다.

2. 이동평균 구하는 구문

이동평균을 구하기 위해서, 윈도 함수인 OVER( ORDER BY ~) 구문을 활용해주어야 한다.
윈도함수를 통해 7일씩 데이터를 쪼개서 계산하는것이 원리이다.

이동평균을 구하는 구문은 다음과 같다.

SELECT 
    <날짜 칼럼>,
    AVG(SUM(구하고자 하는 칼럼)) OVER (ORDER BY <날짜 칼럼> ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7,
    -- 최근 최대 7일동안의 이동평균
    CASE
        WHEN count(*) OVER(ORDER BY <날짜 칼럼> ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) =7 THEN AVG(SUM(구하고자 하는 칼럼)) 
             OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    END AS  avg_7_strict-- 최근 7일 동안의 평균을 확실하게 계산하기
FROM <테이블명>
GROUP BY <날짜 칼럼>
;

위의 구문을 보면 이동평균 구하는 것을 2가지 방법으로 나누어 놓은 것을 볼 수 있다.

최근 최대 7일 동안의 이동평균
무조건 7일 동안의 평균

첫번째 방법은 7일의 기간이 충족되지 않는 1~6일도 있는 기간까지의 평균이 구해지는 방법이다.

반면 두번째 방법인 CASE 구문을 이용한 이동평균 구하기 방법은
무조건 기간이 7일이 되었을때 평균을 구하는 것이다.

3. 예제

다음 매출 데이터를 보자.

이 매출 데이터를 위에서 소개한 쿼리를 이용해 날짜별 매출과 7일 이동평균을 집계하는 결과물을 출력해보자

SELECT
    dt,
    SUM(purchase_amount) AS total_amount,
    AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS seven_day_avg, -- 최근 최대 7일 동안의 평균 계산하기.
    CASE
        WHEN count(*) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) =7 THEN AVG(SUM(purchase_amount))
              OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    END AS seven_day_avg_strict -- 최근 7일 동안의 평균을 확실하게 계산하기
FROM purchase_log
GROUP BY dt
ORDER BY dt;

아래와 같이 출력 됨을 알 수 있다.

(SQL) 윈도 함수를 이용해 데이터 가공하기

29 Nov 2021 in 가이던스 on SQL

(SQL) 윈도 함수를 이용해 데이터 가공하기

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

1. 윈도함수란?

윈도함수란 테이블 내부에 ‘윈도’라는 범위 를 정의하고 ,
해당 범위 내부에 포함된 값을 자유롭게 사용하려고 도입한 것이다.
즉, 자체적으로 범위를 정해 그 안에서 작업하기 위한 함수이다.

윈도함수를 통해 데이터를 집약하거나 그룹 내부의 순서를 배열할 수 있다.

윈도 함수를 사용하기 위해서는 윈도 함수 내부에
참조하고자하는 값의 위치를 명확하게 지정해야 한다.

OVER 구문 내부에 ORDER BY 구문을 사용하여 윈도 내부에 있는 데이터의 순서를 정의한다.

일반적인 윈도 함수 , 특히 OVER 구문은 다음과 같다.

SELECT <칼럼명> 
       , <함수> () OVER(PARTITION BY <칼럼명> ORDER BY <칼럼명> DESC ROWS BETWEEN "start" AND "end" )
FROM <테이블명>
    

2. 윈도함수 상세 설명

윈도우 함수 사용시 주목할 부분이 네 군데가 있다.

SELECT <칼럼명> 
       , <함수> () OVER(PARTITION BY <칼럼명> ORDER BY <칼럼명> DESC ROWS BETWEEN "start" AND "end" )
FROM <테이블명>
    

1) <함수>

SELECT 다음에 <함수> 부분을 살펴보자.
이때 <함수>에는 다양한 함수가 사용가능하다.

집계할때 많이 사용하는 SUM( )이나 COUNT( ),MAX( ),MIN( ) 함수 모두 가능하다.
특히 순서를 배열할때는 row_number( ) 함수나 rank() 함수, dense_rank( ) 함수가 주로 쓰인다.

ROW_NUMBER(): 점수 순서로 유일한 순위를 붙임
RANK() : 같은 순위를 허용해서 순위를 붙임
DENSE_RANK() : 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 띄고 순위를 붙임

2) PARTITION BY <칼럼명>

두번째는 OVER( ) 다음에 바로 나오는 PARTITION BY <칼럼명> 이다.
PARTITION BY <칼럼명> 은 윈도함수 내부에서 데이터를 집약할 때 사용한다.

윈도함수 내부에서 GROUP BY를 해준다고 보면 되며
결과적으로 지정해준 <칼럼명> 필드 안에서 윈도우를 나눠서 집계한다고 생각하면 된다.

윈도 함수에서 특정 칼럼으로 집약하지 않고 순서 배열만 한다면 생략해도 무방하다.

3) ORDER BY <칼럼명>

세번째는 ORDER BY <칼럼명>이다. 윈도우를 세팅하는 과정이다.
윈도우를 어떤 필드를 기준으로 정렬(내림차순 or 오름차순)할지 정하는 것이다.
윈도우 내부를 어떤 순서로 구성할지 이해하면 편하다.

4) ROWS BETWEEN “start” AND “end”

마지막으로, OVER( )구문 안에 ROWS BETWEEN “start” AND “end”를 보자.
이것을 프레임 지정 구문이라고 한다.

프레임 지정이란 현재 레코드 위치를 기반으로 상대적인 윈도를 정의하는 구문이다.
“start”와 “end”에는 다음 키워드가 들어갈 수 있다.

CURRENT ROW : 현재의 행
n PRECEDING : n행 앞
n FOLLOWING : n행 뒤
UNBOUNDED PRECEDING : 이전 행 전부
UNBOUNDED FOLLOWING : 이후 행 전부

3. 윈도함수 예시

두가지 예시를 통해 윈도함수 대해 더 알아보자.

다음은 인기 있는 상품이 기록된 popular_products 테이블이다.

나는 윈도함수를 사용해 카테고리들의 순위를 계산하고 싶다.
다음 네가지를 한 테이블에 추출하고싶다.

1) 카테고리별로 점수 순서로 정렬하고 유일한 순위를 붙임
2) 카테고리별로 같은 순위를 허가하고 순위를 붙임
3) 카테고리별로 같은 순위가 있을 때 같은 순위
4) 다음에 있는 순위를 건너 띄고 순위를 붙임

이럴 경우, 윈도함수를 사용하면 추출할 수 있다.

SELECT 
 category
 , product_id
 , score
 , ROW_NUMBER() over (PARTITION BY category ORDER BY score DESC) AS row
 , RANK() OVER(PARTITION BY category ORDER BY score DESC) AS rank
 , DENSE_RANK() OVER(PARTITION BY category ORDER BY score DESC) AS dense_rank
FROM popular_products
ORDER BY category,row;

결과가 다음과 같이 나온다.

두번째 예시이다.
윈도 함수를 이용해 카테고리별 주요 수치를 추출하고 싶다.

1) 점수 순서로 유일한 순위
2) 순위 상위부터의 누계 점수 계산
3) 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
4) 순위가 높은 상품 ID 추출하기

 SELECT
    product_id
    ,score
    , row_number() over (ORDER BY score DESC) AS row -- 점수 순서로 유일한 순위를 붙임
    ,SUM(score) OVER (ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cum_score -- 순위 상위부터 누계 점수 계산하기
    ,AVG(score) OVER (ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS local_avg -- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
    ,FIRST_VALUE(product_id) OVER(ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value -- 순위가 높은 상품 ID 추출하기
    ,LAST_VALUE(product_id) OVER(ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value -- 순위가 낮은 상품 ID 추출하기
FROM popular_products
;

결과가 다음과 같이 나온다.

(SQL) CASE 문을 이용하여 데이터 전처리를 하자

25 Aug 2021 in 가이던스 on SQL

(SQL) CASE 문을 이용하여 데이터 전처리 하기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

1. CASE문이란?

CASE문은 데이터 전처리를 할때 많이 사용된다.
CASE문을 이용하면 데이터를 조건에 따라 원하는 값으로 바꿀 수 있다.

기본 구문은 다음과 같다.

CASE  
    WHEN <조건식> THEN <조건을 만족할 때의 값>
    END

WHEN 뒤에 조건식을 만족하면 데이터를 THEN 다음에 나오는 값으로 바꿔준다.

2. CASE문의 실제 사용 예시

예시를 통해 CASE문에 대해 더 알아보자.

다음은 Vendors테이블이다.

나는 vend_country에 기록된 국가명을 각 국가가 속한 대륙명으로 바꾸고 싶다.
(USA –> America , England –> Europe, France –> Europe)

이럴 경우, CASE문을 사용하면 간단히 데이터를 원하는대로 바꿀 수 있다.

  SELECT vend_id,
       CASE
        WHEN vend_country= 'USA' THEN 'America'
        WHEN vend_country='England' Then 'Europe'
        WHEN vend_country='France' Then 'Europe'
        END AS vend_continent -- 칼럼명을 vend_continent로 변경
FROM tysql.vendors;

결과가 다음과 같이 나온다.

SQL내에서 데이터 전처리를 할 때
CASE문은 많이 사용되니 잘 알아두도록 하자.

(Tableau) NewYork AirBnB Dashboard

1. 대시보드 소개

2. 대시보드 세부 설명

(Python) 엑셀 파일 쪼개기 _이름을 기준으로

1. 문제 발생

2. 문제 해결 코드

(SQL) 이동평균 구하기

1.이동평균이란?

2. 이동평균 구하는 구문

3. 예제

(SQL) 윈도 함수를 이용해 데이터 가공하기

1. 윈도함수란?

2. 윈도함수 상세 설명

1) <함수>

2) PARTITION BY <칼럼명>

3) ORDER BY <칼럼명>

4) ROWS BETWEEN “start” AND “end”

3. 윈도함수 예시

(SQL) CASE 문을 이용하여 데이터 전처리를 하자

1. CASE문이란?

2. CASE문의 실제 사용 예시

Datata29

Error

1. 대시보드 소개

2. 대시보드 세부 설명

1. 문제 발생

2. 문제 해결 코드

1.이동평균이란?

2. 이동평균 구하는 구문

3. 예제

1. 윈도함수란?

2. 윈도함수 상세 설명

1) <함수>

2) PARTITION BY <칼럼명>

3) ORDER BY <칼럼명>

4) ROWS BETWEEN “start” AND “end”

3. 윈도함수 예시

1. CASE문이란?

2. CASE문의 실제 사용 예시

Pagination

Templates (for web app):

Error