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

✋🏾 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;

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

✋🏾 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
;

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

✋🏾 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문은 많이 사용되니 잘 알아두도록 하자.

AB Test란 무엇일까?

이 포스팅은 3개의 목차로 구성되어 있습니다.

1. AB Test의 정의
2. AB Test의 대상이 되는 요소
3. AB Test 프로세스

1. AB Test의 정의

AB Test란 온라인으로 접속한 사용자를 임의로 여러개의 그룹으로 분리하고
각 사용자 그룹에게 서로 다른 UI나 알고리즘을 노출시키고 비교함으로써
여러 안 중 가장 효과가 좋은 안을 찾아내는 것을 말한다.

쉽게말하자면, A안과 B안을 만들어 비교하고 더 나은 것을 찾는 방법이라 할 수 있다.

구글, 페이스북, 디스커버리와 같은 많은 유명기업들은
AB Test를 사용하여 페이지를 개선하고 목표를 달성한다고 한다.

2. AB Test의 대상이 되는 요소

AB Test의 대상이 되는 요소는 대표적으로 Headline Text와 CTA Text가 있다.

• Headline Text
  • 페이지 방문자가 처음으로 읽게 될 문구
• CTA Text ( Call-to-action )
  • 페이지 방문자에게 목표 행동을 유도할 문구
  • ex) 목표행동 : 회원가입, 구매, 사이트 방문

3. AB Test 프로세스

AB Test는 크게 6가지 프로세스를 통해 수행된다.

1. 데이터 수집
   • 전환율이 낮은 트래픽 구역 찾고 , 데이터 수집
2. 목표 설정
   • ex) 버튼 클릭, 구매링크 클릭, 회원 가입
3. 가설 수립
   • 현재(기존) 버전보다 나을 것 같은 아이디어를 기준으로 가설 수립
4. 비교 버전 만들기
   • AB Test 소프트웨어 이용하여 비교 버전 만들기
   • ex) CTA 버튼 색깔 변경, 문구 변경
5. 실험 수행
   • 페이지 방문자들에게 랜덤으로 페이지 배정 (A or B) 하여 테스트
6. 결과 분석
   • 두 버전의 퍼포먼스 비교
   • 이 분석 결과가 통계적으로 유의미한지
   • 일반적으로 P-value, AA Test를 통해 통계적 유의미성 확인함