[ASAC 13일차] SQL EDA, Numpy, Pandas

 

4월 8일의 기록

---

또 한주가 시작되었다. 이번 주가 지나면 개인 프로젝트를 진행하게 될텐데, 아직 어떤 주제로 하는게 좋을지 결정하지 못했다.

우선 지금 당장에 개인적으로 분석을 도전해보고 싶은 도메인 쪽은 게임, 금융, 이커머스 인데, 정말 확 마음에 드는 분석 아이디어가 없어서 고민이 된다.. 쉬는 시간이랑 점심시간에도 계속 생각해보면서 빠르게 초안을 작성하겠다는 목표로 만들어봐야겠다..!

 

목차

---

1. SQL EDA
2. Numpy의 array
3. Pandas의 Series

1. SQL EDA

• 오전에는 지난주 마지막 시간에 이어서 EDA 하는 과정을 리뷰하면서 SQL에 대해서 최종 정리하였다.

-- 컬럼 설명 : ClothingID 상품 코드 번호( unique value :옷 종류에 대한 유니크)
-- Age     : 리뷰 작성자의 연령
-- Title   : 작성 리뷰 제목
-- Review Text : 실제 작성한 리뷰 내용....
-- Rating  : 구매 상품에 대한 평점....(리뷰 작성자가 생각하는 평점)
-- Recomened~~ : 리뷰어가 추천할 것인지에 대한 판단....
-- positive feed~~~ : 해당 리뷰가 다른 고객들이 도움이 되었다고 한 사람 수
-- Division Name  : 상품이 속한 Division     ( 대분류 ) 
-- Department Name : 상품이 속한 Department  ( 중분류 ) 
-- Class Name      : 상품의 타입정도...       ( 소분류 ) 

#1-- Division Name의 종류별로 평점의 평균!!!!!! + 평점 내림차순으로 정렬..
select divisionName, avg(rating) from dataset2

group by DivisionName
order by avg(rating) desc;

#2 Department Name의 종류별로 평점의 평균!!!! + 평점 내림차순으로 정렬
select departmentname, avg(rating) from dataset2
group by Departmentname
order by avg(rating) desc;

-- 중분류가 Trend인 것들이 좀 뭐가 문제가 있나???고민이 되는 것!!
-- 참고) 빈칸의 정체를 확인하려면?????
select * from dataset2 where `Department Name`is null;
select * from dataset2 where `Department Name` = "";
-- 중분류가 누락된 데이터가 몇 개?
select count(1) as `중분류 누락 수` 
		from dataset2 where `Department Name` = "";

#3 Department Name의 값이 Trend인 항목에 대해서 평점이 3점 이하인 데이터만 보자
select * from dataset2
where rating <= 3
and departmentname = "Trend";

# 4 3번의 데이터를 나이대별로 처리!
select
case
when age between 50 and 59 then '50대'
when age between 40 and 49 then '40대'
when age between 30 and 39 then '30대'
when age between 20 and 29 then '20대'
when age between 10 and 19 then '10대'
else '기타'
end as "AGEBAND",
count(1) as count

from dataset2
where rating <= 3
and departmentname = "Trend"
group by ageband
order by count desc;

# 4-1) 꼼수를 사용한 다른 방법
select floor(11/10)*10;
select floor(10/10)*10;

select floor(age/10) *10 as `AGE_Band` from dataset2
		where `Department Name`="Trend" 
						and Rating <= 3;
select floor(age/10) *10 as `AGE_Band`, count(1) as `CNT`
		from dataset2
		where `Department Name`="Trend" 
						and Rating <= 3
		group by `AGE_Band`
        order by `CNT` desc;

#5 Trend 항목에 대한 리뷰 평점에 대해서 나이대별로 몇 건인지 확인
# -- 이유 : 3점 이하에서 40~50대가 불만이 많았으므로
# 실제로 trend 상품을 40~50대가 많이 구매해서 이러한 결과가 나타난건지
# 확인해봐야 함
select 
case
when age between 50 and 59 then '50대'
when age between 40 and 49 then '40대'
when age between 30 and 39 then '30대'
when age between 20 and 29 then '20대'
when age between 10 and 19 then '10대'
else '기타'
end as "AGEBAND",
count(1) as count

from dataset2
where rating <= 3
group by ageband
order by count desc ;

#6 Trend 항목에 대한 리뷰 중에서 50대들의 3점 이하의 리뷰들을 출력( 10개만)
# 실제 리뷰를 읽어보면서 문제가 있는지 찾아보는 것
# 
select * from dataset2
where age between 50 and 59
and rating <=3
limit 10;

#7 (Deparment and ClothID)의 항목을 기준으로 평점을 계산
select departmentname as dep_name,clothingid, avg(rating)as avg_rate from dataset2

group by departmentname, clothingid;

#8 랭킹을 하기는 하는데, Department별로 랭킹을 독립적으로 부여
select *, row_number() over(Partition by dep_name order by avg_rate) as RNK
from ( select Departmentname as dep_name, clothingid as clothing_id, avg(rating) as avg_rate from dataset2 group by departmentname, clothingid) A;

# size가 언급되었던 리뷰 수
select 
count(1) as n_size, 

case
when reviewtext like "%size%" then 1 else 0
end size_yn

from dataset2
group by size_yn;

select count(1) as n_size, 
sum(case
when reviewtext like "%size%" then 1 else 0 end) N_total 
from dataset2;

# Large, Loose, small, tight 별로 리뷰 텍스트가 어느정도로 분포되어 있는지 확인

select 
departmentName,
size/total, 
large/total, 
small/total, 
tight/total, 
loose/total

from
(select departmentName,
	sum(1) as total,
	sum(case when reviewtext like "%size%" then 1 else 0 end) size,
	sum(case when reviewtext like "%large%" then 1 else 0 end) large,
	sum(case when reviewtext like "%small%" then 1 else 0 end) small,
	sum(case when reviewtext like "%tight%" then 1 else 0 end) tight,
	sum(case when reviewtext like "%Loose%" then 1 else 0 end) loose

from dataset2 group by departmentName) as total_review

order by size/total;

select n_size
(
select 
count(ReviewText) as n_size, 
case
when reviewtext like "%size%" then 1 else 0
end size_yn

from dataset2
group by size_yn
having size_yn = 1
) as size;

#SQL EDA_2 Instacart E-commerce Data

select eval_set, count(1) from orders

group by eval_set;

select * from order_products__prior;

#1. 전체 주문 건수 확인하기

select count(1) from orders; # 전체 총 주문 수

#2. 한 사람이 2번이상 구매할 수 있으니 유니크한 구매자 수 확인
select count(distinct user_id) from orders; # 유니크한 구매자 수

#3. 상품이름별로 몇 건의 주문이 있었는지 체크
select o.order_id,count(opp.product_id) as `주문별 상품` from orders o
inner join order_products__prior opp on opp.order_id = o.order_id
group by o.order_id;

select product_name, count(1) as count, opp.order_id from order_products__prior opp
inner join products p on p.product_id = opp.product_id
group by product_name, opp.order_id
order by count(1) desc;

#4. 카트에 제일 먼저 1번으로 담는 상품 10개 대해
#출력 : 상품 번호, 상품 이름, 몇번 1번
# 몇 번째 담기는 정보 + 카운팅 + top 10

select product_id, add_to_cart_order from order_products__prior;

-- 필터링  : where / case when ~~~

select product_id, sum(case when add_to_cart_order =1 then 1 else 0 end) as cart 
from order_products__prior
group by product_id;

# 계산이 된 값들을 중심으로 정렬 & limit ==> top 10

select product_id, sum(case when add_to_cart_order =1 then 1 else 0 end) as cart 
from order_products__prior
group by product_id
order by cart desc limit 10; #10개만 짜르기;

#5. 시간대 별로 주문 건수 테이블 작성하기

## --> 출력 시간 순서대로 나열 하면서 그 시간대 주문이 몇건인지 출력
# 내가 3시때만 보겠다 하면 case when으로 하고 전체 시간대로 다 본다면 count하면 된다

select order_hour_of_day, count(1) from orders
group by order_hour_of_day
order by order_hour_of_day;

#6. 첫 구매 후 다음 구매까지 걸린 평균 일수 구하기
select avg(days_since_prior_order) from orders o
inner join order_products__prior opp on opp.order_id = o.order_id

where reordered = 0
;

select avg(days_since_prior_order) from orders o
#inner join order_products__prior opp on opp.order_id = o.order_id
where order_number = 2;

#첫 구매 후 다음번째 구매의 의미는?

#7. 주문 건당 구매 상품 수
select order_id, count(product_id) from order_products__prior
group by order_id;

select count(product_id)/ count(distinct order_id) as 주문 건당 상품 구매 수 from order_products__prior;

#8. 인당 평균 주문 건수
select count(order_id) / count(distinct user_id) as 인당 평균 주문 수 from orders;

#select avg(count(product_id)) from order_products__prior;

 
 
 #9. 재구매율이 높은 상품 10개
 select product_id,sum(case when reordered = 1 then 1 else 0 end) as `재구매 상품`
 from order_products__prior
 
 group by product_id
 order by 2 desc;
 
 
 #10. 상품별 재구매율
 select product_id,
 sum(case when reordered = 1 then 1 else 0 end) 
 /count(order_id) as `reorder_rate`
 
 from order_products__prior
 
 group by product_id;
 
 
# 재구매율이 높은 순서대로 순위 부여
select * , 
row_number() over(order by `reorder_rate` desc) `Ranking`

from (
select product_id, sum(case when reordered = 1 then 1 else 0 end) / count(product_id) as `reorder_rate`
 from order_products__prior
 
 group by product_id) A ;
 
 # 재구매율이 높은 상품의 id, 이름, 재구매율, 주문건수를 나타내보자.
 select P.product_name,  b.reorder_rate, count(p.product_id) from 
 
(select * , 
row_number() over(order by `reorder_rate` desc) `Ranking`

from (
		select product_id, sum(case when reordered = 1 then 1 else 0 end) / count(product_id) as `reorder_rate`
 		from order_products__prior
		group by product_id
 	) A 
 ) B 
 
 left join products p on B.product_id = p.product_id
 GROUP BY P.product_name, B.reorder_rate;
 

2. Numpy

numpy와 pandas는 왜 배워야 할까?

→ 데이터 핸들링을 위해

1. 내가 원하는 데이터를 수집/가져오고
2. 데이터에 대한 전처리를 한다.
3. 원하는 결과에 표/시각화/그래프를 그린다.
4. 즉, 주어진 데이터들에 대한 핸들링을 통해 데이터를 잘 분석하기 위함이다.

전제 조건

• 데이터 핸들링을 하기 위해선 패키지가 필요하다.
  예시 (numpy, pandas, scikit-learn, tf/pytorch)

기본 import 방식

거의 대부분 np, pd의 약어를 쓰니 알아두자

 

Numpy

• 데이터를 처리하는 가장 기본이 되는 파이썬 패키지
• Numerical Python : 수치 연산

numpy를 만들게 된 이유

→ 수치 연산을 파이썬에서 하기 위해서

→ 기존의 파이썬의 자료형(리스트, 튜플, dict etc)에서는 불편하다.

• 실제 데이터는 그때 그때 다르지만..
• 하려는 연산, 모델의 방법은 고정
• 수식으로 간단히 표현!!! (선형대수)
• 논문의 수식을 그대로 코드화

앞으로의 모델링 쪽은 다 벡터 연산을 기반으로 하는 선형대수학!!

기본 파이썬에는 이러한 목적으로 운영되는 자료형이 없다. 모든 값에 일괄 적용시키는 것이 불가능하다. 따라서 numpy를 활용하여 이를 가능하게끔 하고자 사용하게 되었다.

 

 

Numpy 값을 접근하는 방식

기본 파이썬과 numpy의 차이점 

 

간단 실습

# 정리!!!!)
# numpy패키지의 array의 자료형!!!
# 1) 모든 원소에 일괄 적용이 되는 벡터연산을 기본으로 함!!!
# 2) --> 모든 코드 작업이 기능 중심으로 작성을 함!!!
#        ( 수식을 그대로 코드화 작업에 용이함!!)
# 3) 나중에는 내가 상요하는 데이터 : ndim, shape, dtype
#     -> DL에 꼭 필수!!!!차원/모양중심으로 코드를 작성!!!!!
# 4) 기본은 여러개의 값을 다루는 자료형 중 하나!

a_arr[ [0,1,3]] # 인덱스 자리에 내가 보고자하는 것들을 덩어리로 던질 수 있다.

# 참고) 강제로 값의 타입 변경!!!!
b = [ 1,2,3]
b_arr = np.array( b)
b_arr.dtype

# dtype에는 numpy가 정한 메뉴얼상 정한 값의 타입을 지정할 수 있음!!
# --> 훨씬 많은 타입이 지정이 되어 있음..
# 참고 : <https://numpy.org/doc/stable/reference/arrays.dtypes.html>
b_arr = np.array( b, dtype = np.string_)
b_arr.dtype
# --> 처음에 데이터를 불러올 때 원하는 형식을 지정하는 방식..

# 데이터를 불러놓고, 원하는 형이 아니여서 변경할 때...
# --> 강제적으로 내부 데이터 타입 변경!!!
b_arr.astype(np.float64)

b_arr # --> 값을 변경하고 싶다면 astype는 재할당을 해야 한다.

# numpy의 인덱싱 연습!
# 주어진 2차원 array에 대해서 생성할 때
# m1) 그냥 직접 값들을 다 작성하는 방법
# m2) 벡터연산을 활요하는 방법 : 규칙!!!!
m = [
     [ 0,1,2,3,4,5,],
     [10,11,12,13,14,15],
     [20,21,22,23,24,25],
     [30,31,32,33,34,35],
     [40,41,42,43,44,45],
     [50,51,52,53,54,55]
]
m

list(range(0,6,1)) # 쌩파이썬 [0,1,2,3,4,5]

# 앞에 시작하는 컬럼벡터 0, 10,20,... : 2D
np.arange(0,51,10)[ :, np.newaxis]

a = np.arange(0,51,10)[ :, np.newaxis] + np.arange(0,6,1)
a
# 벡터 연산을 통해서 규칙있는 2D를 만들 수 있구나....

print(a.dtype)
print(a.ndim)
print(a.shape)

# 원하는 값에 대한 2차원에 대한 접근 연습
# 예) 12값에 대한 접근을 하기 위해서는.
# 쌩파이썬... 순차적으로 접근을 해야 함!!
a[1][2]

# numpy는 바로 접근이 가능함!!
# ---> [ 내가 원하는 위치를 차원에 맞춰서 던질 수 있다.
a[1,2]

# 정리) numpy에서 2D 차원 데이터의 값의 접근 방식!!!
# 1) 쌩 파이썬 리스트 처럼 : 가로 접근 후에 --> 새로 접근 하는
#     a[가로위치][세로위치]
# 2) + 원하는 포지션을 직접 지정이 가능함!!!
#    ==> 가로 어디, 세로 어디!!!!!
#    a[가로어디, 세로 어디]

# Q) 중간에 값들에대한 퐁당 퐁당
a[ 2::2, ::2 ]
# ---> 주어진 2차원 데이터를 바라보는 관점이 가로, 세로!!!!!!

# Q) 밑에 오른쪽 하단 덩어리...
a[4:, 4:]

 

 

불리언 인덱싱 실습

#### 중요!!!!!!
# 불리언 인덱싱!!!!!!
# 인덱스 : 정수, 슬라이싱, 리스트(주의해서...) : 가로,세로 독립적
#          ++ 조건식이 들어감!!!!

# 불리언 인덱싱 : numpy의 array의 인덱스 자리에 조건식이 들어간다!!
# ==> 필터링!!!!( 조건 검색 !!!) : sql : where/having~~~~~
#     pandas에서 데이터에 필터링을 할 때
#     필터링한 조건을 인덱스 자리에 작성을 함!!!!!
# + 벡터연산을 이해를 해야 함!!!!

test_arr = np.arange(10)
test_arr

# 예) 5보다 작은 값들만 추리고 싶다!!!!! --> 필터링, where
test_arr < 5

test_arr[ test_arr < 5 ]
# --> 인덱스 자리에 조건식이 들어가서
#     모든 원소에 해당하는 조건에 맞는 여부를 물어보고(벡터연산)
# --> 인덱스에 T/F가 T에 해당하는 값만 출력하는데,,: 필터링!

# 참고) 위의 기능을 쌩 파이썬으로 하면,,,
test_list = list( test_arr)
[i for i in test_list if i < 5]

# 참고) numpy가 수식 연산을 위한 패키지!!!
# --> 수식 함수들이 파이썬 기본보다 많이 존재를 함!!!!!
# 자세한 것은 메뉴얼 : 검색

sum(test_list)

test_arr.sum()  # --> 이미 패키지가 만든 기능이 있다면,
#                     그 기능을 쓰는 것을 추천을 함!!!!!

sum(test_arr)

불리언 인덱싱

 

3. Pandas

Pandas의 개발 동기

# 개발 동기 : 금융 데이터 처리를 파이썬으로 해볼까?
# ---> 삼성전자 주가 데이터에 처리!!!
#      종가 1개만 생각을 하고,,,,
#      83000, 84000, 84500, ....
# --> 1차원으로 가격들을 모아둘 수 있음(리스트, array)
# 오늘 가격은 얼마?
# 지난 금요일 가격은 얼마?
# --> 주말에 장 안열리고, 공휴일 안 열리고,,,
# ---> 몇 번째 데이터냐?? 접근을 하면 상당히 접근이 불편하다!!!
# ++ 정수인덱스 말고, 내가 값에 대한 인덱스를 만들어서 접근하자!!

pandas의 생성 방식

# 여러개의 값을 다루는 자료형에 대해서...
# 쌩 파이썬의 대표 : 리스트
# --> upgrade : numpy의 array ( 벡터연산이 되도록!!!)
# --> upgrade : pandas

# pandas를 개발한 사람들은 "차원을"중심으로 여러 자료형!!!
# 1차원 : 1차원 벡터 --> Series Type
# 2차원 : 2차원 행렬 --> DataFrame Type ** 주로 다룰 자료형!! **
# 3차원 : 3차원      --> Pannel Type
# 그 이상은 안 함!!!! --> Tensor ==> TF/ PyTorch!!!!!! DL

# + 특징 : 인덱스를 내가 원하는대로 만들고,
#          원하는 정보로 접근도 가능하고
#          ==> 내가 원하는대로 인덱스를 컨트롤 할 수 있다!!
# 물론 단점도 존재 : 속도가 느림
#              적당한 사이즈의 데이터 핸들링에는 아주 편함!
#          + sql+gpu etc

생성 방법 1 : 파이썬 리스트

# 생성1) 쌩 파이썬의 리스트
stock_price_list = [10000, 10300, 9900, 10500, 11000]

생성 방법 2 : numpy array

# 생성2) numpy->array
stock_price_arr = np.array(stock_price_list)

생성 방법 3: pandas의 Series

# 생성3) pandas의 1차원 Series 형으로 생성... S 대문자!!
stock_price_Series = pd.Series( stock_price_list )

 

 

기본 슬라이싱 방식과 iloc의 사용 방식, 데이터 많을 땐 속도 차이가 매우 크게 난다.

 

 

참고) 수치 연산의 기능은 Pandas에도 존재 한다.

 

 

파이썬의 함수로도 사용이 가능하다.