[내일배움캠프] 9일차

1. 오늘 학습 키워드

• 기초 데이터 분석 팀과제

 

---

 

2. 오늘 학습 한 내용을 나만의 언어로 정리하기

EDA(Exploratory Data Analysis)

1. 아마존 책 리뷰 데이터를 파악

Objective: 아마존 책 정보와 리뷰 데이터를 파악하고 다양한 분석을 해보고 이상치, 결측치 찾아서 해결하기

원본 데이터가 양이 크기 때문에 다음과 같이 샘플을 추출하여 데이터 분석

• sampled_books: 책 4.2만개 정보(3.6MB)
• sampled_ratings: 60만 개의 리뷰(50MB)

sampled_books: 책에 대한 정보가 있는 테이블

변수명	설명
Title	책 제목
description	책 줄거리
authors	저자명
image	책 표지 링크
previewLink	google Books에서 책의 링크
publisher	출판사명
publishedDate	출판일
infoLink	google Books에서 책의 상세페이지 링크
categories	책 장르
ratingsCount	책 평균평점

 

사실상 링크변수는 분석 시 사용하지 않으므로 분석 시 image, previewLink, infoLink 컬럼은 사용하지 않을 것이다.

 

sampled_ratings: 리뷰에 대한 정보가 있는 테이블

변수명	설명
id	책 아이디
Title	책 제목
Price	책 가격
User_id	리뷰를 작성한 사용자 아이디
profileName	리뷰를 작성한 사용자명
review/helpfulness	책의 유용함 리뷰(비율로서 평가, 0부터 1 사이의 값)
review/score	리뷰 점수(0부터 5 사이의 값)
review/time	리뷰 작성 시간
review/summary	리뷰 요약
review/text	리뷰 원본

 

profileName, review/time도 사용하지 않을 것이다.

 

limit 이용하여 테이블 컬럼들 확인하기

처음 10개의 row 만 보여줌

 

books

select *
from sampled_books
limit 10

 

 

ratings

select *
from sampled_ratings 
limit 10

 

 

ratings 테이블의 title컬럼과 books 테이블의 title컬럼을 join

select *
from sampled_books b inner join sampled_ratings r on b.title=r.title

 

두 테이블의 Title이 같은 변수이므로 inner join을 사용하여 합친다.

 

두 테이블을 병합하여 하나의 테이블로 merge하려 했으나 데이터 반정규화를 우려하여 병합하지 않을 것이다.

데이터 파악에 image, previewLink, infoLink, profileName, review/time 컬럼은 유용하지 않으므로 나머지 컬럼들만 이용할 것이다.

 

 

books테이블에 ratingsCount컬럼은 ratings테이블에 review/score컬럼의 평균일까? 아니면 ratings테이블에 review의 개수일까?

 

1. ratingsCount컬럼과 ratings테이블에 review/score컬럼의 평균 비교

select r.title, b.ratingsCount, round(avg(r.`review/score`), 1) avg_score
from sampled_ratings r inner join sampled_books b on r.title=b.title
group by 1, 2
limit 100

 

review/score컬럼의 평균과 일치하지 않는 값이 많다.

따라서 books테이블에 ratingsCount컬럼은 ratings테이블에 review/score컬럼의 평균이라고 볼 수 없다.

 

2. ratingsCount컬럼과 ratings테이블에 review의 개수 비교

select b.ratingsCount, count(r.title) 'cnt_review'
from sampled_ratings r inner join sampled_books b on r.title=b.title
group by 1
limit 100

 

review의 개수와 일치하지 않는 값이 많다.

따라서 books테이블에 ratingsCount컬럼은 ratings테이블에 review의 개수라고 볼 수 없다.

 

3. ratingsCount의 Null값

(books테이블에 있는 row는 대략 4만9천개)

select ratingsCount, count(*)
from sampled_books
where ratingsCount is null
group by 1

(ratingsCount null값은 대략 3만2천개)

ratingsCount의 null 값이 너무 많다.

 

ratingsCount변수를 파악해봤을 때 명확하지 않다. 또한 Null값이 너무 많다. →ratingsCount컬럼을 분석 시 고려하지 않을 것이다.

 

2. 생각해볼 수 있는 분석 방향

• 인기가 많은 작가를 찾아서 상위 10위 작가를 도출하고 작가별로 다른 컬럼에서 특징이 있는지
• 인기가 많은 출판사를 찾아서 상위 10위 출판사를 도출하고 출판사별로 다른 컬럼에서 특징이 있는지
• 인기가 많은 책 장르를 찾아서 상위 5위 장르를 도출하고 장르별로 다른 컬럼에서 특징이 있는지

특히 작가별로 리뷰를 분류하고 순위를 매기는 것은 작가를 따라 책을 소비하는 최근 트렌드와 맞는지 확인할 수 있다. 따라서 작가별 분석은 더 깊게 ‘fan’등의 단어가 review/summary나 review/text에 포함되어 있는지 확인할 수 있다.

 

3. 인기가(리뷰수가) 많은 작가를 찾아서 상위 10위 작가를 도출하고 작가별로 다른 컬럼에서 특징이 있는지

평점 vs. 리뷰수

모든 세부 분석 전에 두 테이블에서 판매량 변수가 없기 때문에 우리는 책의 인기를 평점으로 판단할 것인지, 리뷰수로 판단할 것인지 결정해야 한다.

 

group by, limit 이용하여 평점이 높은 상위 10위 작가 도출하기(평점 평균이 높은 책 상위 10위을 가져오며, 동점일 경우 리뷰수가 많은 책을 우선 출력)

select b.authors, r.title, b.categories, count(1) cnt_review, round(avg(r.`review/score`), 1) avg_score 
from sampled_ratings r inner join sampled_books b on r.title=b.title
group by 1, 2, 3
order by 5 desc, 4 desc
limit 10

작가별 평점순은 비지니스나 논픽션, 종교 등 비소설 분야의 작가가 강세이고 리뷰 수가 비교적 적다.

 

group by, limit 이용하여 리뷰수가 높은 상위 10위 작가 도출하기(리뷰수가 많은 책 상위 10위을 가져오며, 동점일 경우 평점이 높은 책을 우선 출력)

select b.authors, r.title, b.categories, count(1) cnt_review, round(avg(r.`review/score`), 1) avg_score 
from sampled_ratings r inner join sampled_books b on r.title=b.title
group by 1, 2, 3
order by 4 desc, 5 desc
limit 10

작가별 리뷰수순은 소설 분야의 작가(톨킨 만만세)가 강세이고 리뷰수가 압도적으로 많아서 분석하기 좋다.

리뷰수는 얼마나 많은 사람이 읽고 리뷰를 남겼는가, 평점은 독자들이 얼마나 만족했는가를 보여주는 지표이기에 두 개가 모두 높을 수록 인기가 높은 책이다.

평점은 바로 보이는 만족도를 보여주고, 리뷰수는 사회적으로 검증된 효과를 보여준다. 그러나 리뷰수가 적으면 신뢰도가 낮아지며 평점은 양은 많아도 그안에 든 질이 낮을 수 있다.

또한 실제 사용자의 데이터이므로 평점에서의 결측치도 무시할 수 없다.

따라서 인기는 리뷰수로 우선 정렬한 후, 평점으로도 정렬한 순위로 판단한다.

인기가 많은 작가를 찾아서 상위 10위 작가를 도출할 때 작가의 팬이 많을 수록 책을 많이 읽고 리뷰를 많이 남기는지가 궁금하다. 따라서 review/summary나 review/text에서 ‘fan’, ‘into’, ‘interest’, ‘stan’ 등의 표현있는지 찾아본다.

 

in, like 이용하여 review/summary나 review/text에서 ‘fan’, ‘into’, ‘interest’, ‘stan’ 등의 표현있는지 찾아보기

 

우선 작가별 리뷰수 순위를 알아보자. (리뷰수 우선 정렬 후, 평균 평점 정렬)

select b.authors, count(1) cnt_review, round(avg(r.`review/score`), 1) avg_score 
from sampled_ratings r inner join sampled_books b on r.title=b.title
group by 1
order by 2 desc, 3 desc
limit 20

리뷰수순으로 작가를 정렬했을 때의 결과값이다. 리뷰수: cnt_review

 

 

다음은 작가별 ‘fan’, ‘into’, ‘interest’, ‘stan’ 등의 표현을 사용한 리뷰의 개수 순위이다.

select b.authors,
       COUNT(*) cnt
from sampled_ratings r INNER JOIN sampled_books b ON TRIM(LOWER(r.title)) = TRIM(LOWER(b.Title))
where `review/text` like "% fan %"
or `review/text` like "% interest %"
or `review/text` like "% into %"
or `review/text` like "% stan %"
or `review/summary` like "% fan %"
or `review/summary` like "% interest %"
or `review/summary` like "% into %"
or `review/summary` like "% stan %"
group by 1
order by 2 desc

작가별 ‘fan’, ‘into’, ‘interest’, ‘stan’ 등의 표현을 사용한 리뷰의 개수순으로 작가를 정렬했을 때의 결과값이다. 리뷰수: cnt

 

여기서 두 결과값을 join하고 cnt를 cnt_review로 나누면 각 작가별 전체 리뷰수 중 팬과 관련된 용어를 사용한 리뷰수의 비율을 구할 수 있다. 비율이 큰 순서대로 정렬한다.

select ca.authors, cb.cnt/ca.cnt_review
from 
(select b.authors, count(1) cnt_review, round(avg(r.`review/score`), 1) avg_score 
from sampled_ratings r inner join sampled_books b on r.title=b.title
group by 1
order by 2 desc, 3 desc
limit 20) ca
inner join
(select b.authors,
       COUNT(*) cnt
from sampled_ratings r INNER JOIN sampled_books b ON TRIM(LOWER(r.title)) = TRIM(LOWER(b.Title))
where `review/text` like "% fan %"
or `review/text` like "% interest %"
or `review/text` like "% into %"
or `review/text` like "% stan %"
or `review/summary` like "% fan %"
or `review/summary` like "% interest %"
or `review/summary` like "% into %"
or `review/summary` like "% stan %"
group by 1
order by 2 desc) cb
on ca.authors=cb.authors
group by 1
order by 2 desc

상위 10위의 작가들은 팬과 관련된 용어를 사용한 리뷰수가 그 작가의 전체 리뷰수의 20%~35%가 되는 것을 확인할 수 있다.

 

따라서 사람들은 어떤 작가의 팬일수록 리뷰를 많이 한다는 것을 알 수 있다. 그만큼 판매량이 많은지는 판매량 지표가 없기 때문에 다른 문제이다.

 

 

 

 

3. 학습하며 겪었던 문제점 & 에러 

 

-

 

4. 내일 학습 할 것은 무엇인지 

 

SQL 코드카타 ,기초 데이터 분석 팀과제