R, Python 분석과 프로그래밍의 친구 (by R Friend)

[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜

Greenplum and PostgreSQL Database 2019. 3. 16. 22:30

이번 포스팅에서는 Greenplum DB, Postgresql 에서 테이블을 생성한 후에 SQL로 데이터 전처리하는 몇 가지 방법을 소개하겠습니다.

예제로 사용할 간단한 고객 정보 테이블을 생성하고, 행 번호, 고객번호, 이름, 나이, 성별, 지역, 등록일 값을 입력해보겠습니다.

DROP TABLE IF EXISTS public.cust;

CREATE TABLE public.cust (

seq_num integer

, cust_id text not null

, name text not null

, age integer

, gender text

, region text

, regist_date date

);

INSERT INTO public.cust VALUES

(1, 'A001', 'choi', 25, 'M', 'seoul', '2018-01-25'),

(2, 'A002', 'kang', 30, 'F', 'Busan', '2019-02-08'),

(3, 'A003', 'lee', 29, NULL, 'seoul', '2018-05-30'),

(4, 'B001', 'kim', 35, 'F', 'seoul', '2018-12-22'),

(5, 'B002', 'sung', 34, 'M', 'busan', '2019-02-19'),

(6, 'B003', 'park', NULL, NULL, 'SEOUL', '2019-03-15');

SELECT * FROM public.cust ORDER BY seq_num;

위의 테이블에서

(1) 고객ID(cust_id) 문자열의 첫 번째 문자열을 가져다가 group 칼럼 만들기
: SUBSTRING(cust_id, 1, 1) AS group

(2) group별로 seq_num 순서에 따라 행 번호 부여하기
: ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num

(3) 나이(age) 결측값을 전체 평균 값으로 채우기
: COALESCE(age, AVG(age) OVER())::INTEGER AS age

(4) 성별(gender) 결측값을 "Unknown" 값으로 채우기
: COALESCE(gender, 'Unknown') AS gender

(5) 지역(region) 대문자를 소문자로 바꾸기
: LOWER(region) AS region

(6) 이름(name)이 'choi', 'park', 'lee', 'kim'은 그대로 두고, 그 외는 'others'로 바꾸어서 name_2 칼럼 만들기
: CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name ELSE 'others' END AS name_2

-- substring of id's first character
-- insert row number
-- fill missing value of 'age' with average
-- fill missing value of 'gender' with 'Unknown'
-- convert upper letter into lower letter
-- if name IN ('choi', 'park', 'lee', 'kim') then name, else 'other'

DROP TABLE IF EXISTS public.cust_preprocessed CASCADE;

CREATE TABLE public.cust_preprocessed AS

(

SELECT

seq_num,

cust_id,

SUBSTRING(cust_id, 1, 1) AS group,

ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num,

COALESCE("age", AVG(age) OVER())::INTEGER AS "age",

COALESCE(gender, 'Unknown') AS gender,

LOWER(region) AS region,

name,

CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name

ELSE 'others' END AS name_2,

regist_date

FROM public.cust

ORDER BY cust_id

) DISTRIBUTED RANDOMLY;

SELECT * FROM public.cust_preprocessed ORDER BY seq_num;

다음으로 날짜 형식의 데이터에서 년(year), 월(month), 일(day), 현재 날짜(now), 입력 날짜로 부터 현재 날짜까지의 소요 일(day until now)을 계산해보겠습니다.

(7) 등록 날짜에서 년(year) 정보 추출
: EXTRACT (YEAR FROM regist_date)::int AS year

(8) 등록 날짜에서 월(month) 정보 추출
: EXTRACT (MONTH FROM regist_date)::int AS month

(9) 등록 날짜에서 일(day) 정보 추출
: EXTRACT (DAY FROM regist_date)::int AS day

(10) 현재 날짜 자동 입력
: now()::DATE

(11) 이전 등록 날짜에서 현재까지의 소요 일 계산
: AGE(regist_date) AS time_from_regist

-- extract year, month, day from regist_date

DROP TABLE IF EXISTS public.cust_date CASCADE;

CREATE TABLE public.cust_date AS

(

SELECT *,

EXTRACT (YEAR FROM regist_date)::int AS year,

EXTRACT (MONTH FROM regist_date)::int AS month,

EXTRACT (DAY FROM regist_date)::int AS day,

now()::DATE,

AGE(regist_date) AS time_from_regist

FROM public.cust_preprocessed

ORDER BY cust_id

) DISTRIBUTED RANDOMLY;

SELECT

seq_num, regist_date, year, month, day,

now, time_from_regist

FROM public.cust_date

ORDER BY seq_num;

많은 도움이 되었기를 바랍니다.

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] GPDB docker에 PostGIS 설치하기 (0)	2019.03.27
[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21
[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import (0)	2019.03.10
Greenplum DB, Postgresql DB에 사용할 수 있는 오픈소스 database tool DBeaver 설치 및 사용 방법 (0)	2019.03.04
[Docker] Error response from daemon: driver failed programming external connectivity on endpoint, port is already allocated 대처 방법 (port 강제 종료) (11)	2018.09.24

Posted by Rfriend

,

[SQL]웹으로 SQL 연습할 수 있는 웹사이트: w3schools.com/sql : 유통업체 ERD 및 Query 예시

Hive 2017. 6. 26. 23:13

지난번 포스팅에서는 웹으로 간단하게 SQL 을 연습할 수 있는 온라인 사이트로서

w3schools.com/sql 과 테이블을 쿼리해서 데이터 항목을 알아보았습니다.

주문, 주문상세 테이블과 고객, 상품, 상품카테고리, 상품제공업체, 배송업체, 종업원의 기준정보 테이블이 있는 것으로 봐서 유통업체의 데이터임을 알 수 있습니다. 이벤트나 프로모션 정보 테이블, 온라인이나 모바일 등의 채널 이용 정보 테이블, 고객등급/고객세분화 정보 테이블, 결제수단 정보 테이블 등... 뭐, 유통업체라면 더 많은 테이블이 있어야 겠지만서도, SQL 연습하라고 만든 가상의 약식 데이터 DB 테이블이므로 '이 정도도 어디야'하고 감사하면 사용하면 좋겠습니다.

ERD (Entity Relationship Diagram)이 없어서 테이블, 데이터 간의 관계를 한 눈에 파악하는 것이 어려웠는데요, 시간 좀 내서 아래처럼 ERD 그려보았습니다.

[ 유통업체 ERD (Entity Relationship Diagram) ]

* https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 에 있는 테이블별 칼럼을 보고 추측해서 ERD 그린 것임. SQL 연습하려는 분은 이 ERD 참고해서 테이블 간 join 해서 분석하면 됨.

테이블을 여러개 Join 해서 통계량 집계하고 정렬하는 예를 들어보겠습니다.

[SQL Query 문제]

"제품 카테고리 중 'Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments' 카테고리에 대해서 카테고리별로 판매가 일어난(주문이 발생한) 제품들의 가격의 합계, 총 주문 발생 회수, 제품들의 가격의 평균을 구하시오.

단, 카테고리별 제품 가격의 합계가 1,100 이상인 경우만 집계 결과를 제시하되,

가격의 합계를 기준으로 내림차순으로 정렬하여 제시하시오."

SELECT e.CategoryName AS CategoryName,

SUM(e.Price) AS Price_sum,

COUNT(*) AS Order_cnt,

AVG(e.Price) AS Price_avg

FROM

(SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName -- sub query 2

FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID -- sub query 1

FROM OrderDetails a

INNER JOIN Products b ON a.ProductID = b.ProductID) c

LEFT JOIN Categories d ON c.CategoryID = d.CategoryID) e

WHERE e.CategoryName IN ('Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments')

GROUP BY e.CategoryName

HAVING Price_sum > 1100

ORDER BY Price_sum DESC;

Number of Records: 3

====================================================

CategoryName Price_sum Order_cnt Price_avg

====================================================

Dairy Products 2863.2 100 28.63

Seafood 1345.17 67 20.07

Condiments 1121.5 49 22.88

위의 문제가 너무 복잡하고, SQL Query도 SUM(), COUNT(), AVG() 등의 aggregation 함수, FROM 절에 Sub Query 랑 INNER JOIN, LEFT JOIN 이 들어가 있고, WHERE 조건절, GROUP BY, HAVING, ORDER BY 등 어지간한 SQL 기능이 망라되어 있어서 복잡하긴 합니다.

Query가 잘 이해가 안되면 Sub Query를 하나씩 순차적으로 실행시켜보면서 결과를 확인해보면 한결 이해하기가 쉽습니다.

예를 들어보자면, 위의 Query를 가장 안에 위치한 Sub Query 부터 하나씩 아래에 풀어보겠습니다.

=========================================================================

[ sub query 1]

OrderDetails 테이블(a)과 Products 테이블(b)을 ProductID key를 기준으로 INNER JOIN으로 교집합을 구해서 Products 테이블에서 상품의 가격과 카테고리ID 데이터를 가져왔습니다. (테이블 구분하기 편하라고 a, b 라는 alias name 별명을 부여해서 변수 앞에 b.Price 처럼 붙여서 사용합니다) 상위 5개만 예시로 가져오겠습니다.

SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

FROM OrderDetails a

INNER JOIN Products b ON a.ProductID = b.ProductID

LIMIT 5;

OrderID ProductID Price CategoryID

10248 11 21 4

10248 42 14 5

10248 72 34.8 4

10249 14 23.25 7

10249 51 53 7

=========================================================================

[sub query 2]

위의 'sub query 1' 결과 테이블(c)에다가 CategoryID key를 기준으로 Categories 테이블(d)을 LEFT JOIN 하여 Categories 테이블에 있는 CategoryName 칼럼을 붙여서 가져왔습니다. CategoryName 을 붙여 와야지 원래의 SQL Query 문제에 나와있는 'CategoryName별 판매상품 가격의 합계, 판매(주문)회수, 평균판매가격'을 구할 수 있겠지요?

SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName

FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

FROM OrderDetails a

INNER JOIN Products b ON a.ProductID = b.ProductID) c

LEFT JOIN Categories d ON c.CategoryID = d.CategoryID

LIMIT 5;

c.OrderID c.ProductID c.Price c.CategoryID d.CategoryName

10248 11 21 4 Dairy Products

10248 42 14 5 Grains/Cereals

10248 72 34.8 4 Dairy Products

10249 14 23.25 7 Produce

10249 51 53 7 Produce

========================================================================

아래 Query 는 CASE WHEN ~ THEN ... ELSE ... END 문으로 연속형 변수(continuous variable)를 범주형 변수(categorical variable) 로 변환하는 예제 Query 입니다. 아래처럼 '가격대(Price_grp)' 변수를 만든 후에 위에 'SQL Query' 문제에서 사용했던 Query 를 사용해서 다른 응용을 할 수 있습니다.

SELECT a.OrderID, a.ProductID, b.Price,

CASE WHEN b.Price >= 40 THEN '1_over_40'

WHEN b.Price < 40 AND b.Price >= 20 THEN '2_20_40'

ELSE '3_under_20' END Price_grp

FROM OrderDetails a

INNER JOIN Products b ON a.ProductID = b.ProductID

LIMIT 10;

OrderID ProductID Price Price_grp

10248 11 21 2_20_40

10248 42 14 3_under_20

10248 72 34.8 2_20_40

10249 14 23.25 2_20_40

10249 51 53 1_over_40

10250 41 9.65 3_under_20

10250 51 53 1_over_40

10250 65 21.05 2_20_40

10251 22 21 2_20_40

10251 57 19.5 3_under_20

많은 도움이 되었기를 바랍니다.

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^

728x90

저작자표시 비영리 변경금지

'Hive' 카테고리의 다른 글

[R] R로 Oracle DB 접근 방법, R로 Presto 접근 방법 (R Oracle DB connect, R Presto DB connect) (2)	2017.08.29
[SQL] 웹으로 SQL 연습할 수 있는 online site : w3schools.com/sql (2)	2017.06.23
[Hive] R로 Hive에 접속해서 데이터 추출하기, R DB connect (R, Hive dbConnect) : rJava, RJDBC, dbConnect(), dbGetQuery(), R로 PostgreSQL 사용하는 방법, R로 MySQL 접속해서 사용하는 방법 (4)	2016.09.03
[Hive] 뷰(View), 갭슐화(encapsulation) (0)	2016.06.26
[Hive] 조인 문 : INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN, CARTESIAN PRODUCT JOIN, MAP-SIDE JOIN, UNION ALL (0)	2016.06.26

Posted by Rfriend

,

[SQL] 웹으로 SQL 연습할 수 있는 online site : w3schools.com/sql

Hive 2017. 6. 23. 23:59

집에서 개인 컴퓨터로 SQL 연습을 하고 싶은데

- 상용 DBMS 평가판 혹은 오픈 소스 DBMS 설치하자니 힘들고

- DB, Table 생성하고, 데이터 파일을 구해서 import 하거나

혹은 건건이 insert 하기에 힘들고

할 때 아주 쉽고 빠르게, 간편하게 웹 상에서 SQL 연습할 수 있는 사이트가 있어서 소개합니다.

w3schools.com 이라는 곳에서 다양한 언어의 튜토리얼을 제공하는데요, 그 중에서 SQL도 튜토리얼과 함께 연습할 수 있는 웹 환경도 제공하고 있습니다. MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres 등의 다양한 DB에 대한 SQL 튜토리얼을 제공하니 이곳만 잘 이용해도 특정 DB를 염두에 두고 쓰여진 SQL 책보다 더 유용할 수도 있겠습니다. Data 도 준비가 다 되어있어서 그냥 웹에 접속해서 연습하면 됩니다.

단, Chrome, Safari, FireFox 브라우저만 지원하고, Internet Explorer 는 지원하지 않습니다.

접속할 주소는요,

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

이며, 아래와 같은 화면이 나타납니다.

[ w3schools.com 의 SQL 연습할 수 있는 초기 화면 ]

순서대로 살펴보면

(1) 왼쪽 상단에 SQL 을 입력할 수 있는 'SQL Statement: ' 창이 있습니다.

(2) 왼쭉 중간에 'Run SQL >' 이라는 네모 단추가 있는데요, 이를 커서로 클릭하면 SQL이 실행됩니다.

(3) 왼쪽 하단에 'Reslut: ' 란에 SQL 실행 결과가 나타납니다.

(4) 우측 상단에 보면 Database에 들어있는 Table 이름과 Record 수가 나옵니다.

아래의 이름으로 총 8개의 Table에 있는 데이터를 SQL 연습하는데 사용할 수 있습니다!

No.	Tablename	Records
1	Customers	91
2	Categories	8
3	Employees	10
4	OrderDetails	518
5	Orders	196
6	Products	77
7	Shippers	3
8	Suppliers	29

ERD (Entity Relationship Diagram) 이 있으면 좋을 텐데요, 그게 없는지... 못 찾겠네요.

각 Table 별로 상위 5개씩 Select 해서 조회를 해보면 아래와 같습니다.

각 Table 이름을 봐도 그렇고, 상위 5개 records 조회를 해서 봐도 그렇고, 유통업체에서 사용하는 DB table 들을 예로 간단한 예제 DB를 제공한다고 보면 되겠습니다.

SELECT *

FROM Customers

LIMIT 5;

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

SELECT *

FROM Categories

LIMIT 5;

CategoryID CategoryName Description

1 Beverage Soft drinks, coffees, teas, beers, and ales

2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings

3 Confections Desserts, candies, and sweet breads

4 Dairy Products Cheeses

5 Grains/Cereals Breads, crackers, pasta, and cereal

SELECT *

FROM Employees

LIMIT 1;

EmployeeID LastName FirstName BirthDate Photo Notes

1 Davolio Nancy 1968-12-08 EmpID1.pic Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'.

SELECT *

FROM OrderDetails

LIMIT 5;

OrderDetailID OrderID ProductID Quantity

1 10248 11 12

2 10248 42 10

3 10248 72 5

4 10249 14 9

5 10249 51 40

SELECT *

FROM Orders

LIMIT 5;

OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90 5 1996-07-04 3

10249 81 6 1996-07-05 1

10250 34 4 1996-07-08 2

10251 84 3 1996-07-08 1

10252 76 4 1996-07-09 2

SELECT *

FROM Products

LIMIT 5;

ProductID ProductName SupplierID CategoryID Unit Price

1 Chais 1 1 10 boxes x 20 bags 18

2 Chang 1 1 24 - 12 oz bottles 19

3 Aniseed Syrup 1 2 12 - 550 ml bottles 10

4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22

5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35

SELECT *

FROM Shippers;

ShipperID ShipperName Phone

1 Speedy Express (503) 555-9831

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

SELECT *

FROM Suppliers

LIMIT 2;

SupplierID SupplierName ContactName Address City PostalCode Country Phone

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222

2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822

다음번 포스팅에서는 ERD 한번 그려서 올려보겠습니다. 그리고 table 간 Join 도 해보고, aggregation 함수도 몇 개 예를 들어서 한번 더 포스팅해보겠습니다.

SQL 집에서 간단하게 공부하시려는 분들에게 도움이 되었기를 바랍니다.

이번 포스팅이 도움이 되셨다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^

728x90

저작자표시 비영리 변경금지

'Hive' 카테고리의 다른 글

[R] R로 Oracle DB 접근 방법, R로 Presto 접근 방법 (R Oracle DB connect, R Presto DB connect) (2)	2017.08.29
[SQL]웹으로 SQL 연습할 수 있는 웹사이트: w3schools.com/sql : 유통업체 ERD 및 Query 예시 (0)	2017.06.26
[Hive] R로 Hive에 접속해서 데이터 추출하기, R DB connect (R, Hive dbConnect) : rJava, RJDBC, dbConnect(), dbGetQuery(), R로 PostgreSQL 사용하는 방법, R로 MySQL 접속해서 사용하는 방법 (4)	2016.09.03
[Hive] 뷰(View), 갭슐화(encapsulation) (0)	2016.06.26
[Hive] 조인 문 : INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN, CARTESIAN PRODUCT JOIN, MAP-SIDE JOIN, UNION ALL (0)	2016.06.26

Posted by Rfriend

,

R 에서 SQL 사용 sqldf 패키지, 집계 aggregate() 함수

R 분석과 프로그래밍/R 데이터 전처리 2015. 8. 28. 23:56

거래 원 데이터 (transaction raw data)를 받으면 분석 용도에 맞게 데이터 전처리를 할 때 보통 하는 일이 특정 기준 (가령, 고객 ID, 상품 ID, 채널 ID 등)에 대해 데이터를 집계(합계, 평균, 분산 등의 함수를 적용)하는 작업을 하게 됩니다.

R에는 aggregate() 라는 함수가 있습니다만, 기존에 SQL에 익숙한 분석가라면 R 에서 SQL 문을 사용할 수 있게 해주는 sqldf package를 사용하면 쉽고 빠르게 집계를 할 수 있겠습니다.

(단, sqldf 가 performance 이슈가 있으니 데이터 사이즈가 크다면, 그리고 데이터 처리 속도가 중요한 경우라면 sqldf 는 부적할 수도 있다는 점은 고려하셔야 겠습니다.)

R sqldf package 소개자료에 보면

- Perform SQL Selects on R Data Frames
- Manipulate R data frames using SQL

이라고 되어 있습니다.

그럼, sqldf package의 여러 기능, 함수 중에서 데이터 집계 관련한 함수만 몇 가지 선별하여서 소개하도록 하겠습니다.

실습에 사용할 데이터는 MASS 패키지에 내장된 Cars93 데이터 프레임의 자동차 유형(Type), 도시 연비(MPG.city), 고속도로 연비(MPG.highway) 를 사용하겠습니다.

> library(MASS)
> str(Cars93)
'data.frame':	93 obs. of  27 variables:
 $ Manufacturer      : Factor w/ 32 levels "Acura","Audi",..: 1 1 2 2 3 4 4 4 4 5 ...
 $ Model             : Factor w/ 93 levels "100","190E","240",..: 49 56 9 1 6 24 54 74 73 35 ...
 $ Type              : Factor w/ 6 levels "Compact","Large",..: 4 3 1 3 3 3 2 2 3 2 ...
 $ Min.Price         : num  12.9 29.2 25.9 30.8 23.7 14.2 19.9 22.6 26.3 33 ...
 $ Price             : num  15.9 33.9 29.1 37.7 30 15.7 20.8 23.7 26.3 34.7 ...
 $ Max.Price         : num  18.8 38.7 32.3 44.6 36.2 17.3 21.7 24.9 26.3 36.3 ...
 $ MPG.city          : int  25 18 20 19 22 22 19 16 19 16 ...
 $ MPG.highway       : int  31 25 26 26 30 31 28 25 27 25 ...
 $ AirBags           : Factor w/ 3 levels "Driver & Passenger",..: 3 1 2 1 2 2 2 2 2 2 ...
 $ DriveTrain        : Factor w/ 3 levels "4WD","Front",..: 2 2 2 2 3 2 2 3 2 2 ...
 $ Cylinders         : Factor w/ 6 levels "3","4","5","6",..: 2 4 4 4 2 2 4 4 4 5 ...
 $ EngineSize        : num  1.8 3.2 2.8 2.8 3.5 2.2 3.8 5.7 3.8 4.9 ...
 $ Horsepower        : int  140 200 172 172 208 110 170 180 170 200 ...
 $ RPM               : int  6300 5500 5500 5500 5700 5200 4800 4000 4800 4100 ...
 $ Rev.per.mile      : int  2890 2335 2280 2535 2545 2565 1570 1320 1690 1510 ...
 $ Man.trans.avail   : Factor w/ 2 levels "No","Yes": 2 2 2 2 2 1 1 1 1 1 ...
 $ Fuel.tank.capacity: num  13.2 18 16.9 21.1 21.1 16.4 18 23 18.8 18 ...
 $ Passengers        : int  5 5 5 6 4 6 6 6 5 6 ...
 $ Length            : int  177 195 180 193 186 189 200 216 198 206 ...
 $ Wheelbase         : int  102 115 102 106 109 105 111 116 108 114 ...
 $ Width             : int  68 71 67 70 69 69 74 78 73 73 ...
 $ Turn.circle       : int  37 38 37 37 39 41 42 45 41 43 ...
 $ Rear.seat.room    : num  26.5 30 28 31 27 28 30.5 30.5 26.5 35 ...
 $ Luggage.room      : int  11 15 14 17 13 16 17 21 14 18 ...
 $ Weight            : int  2705 3560 3375 3405 3640 2880 3470 4105 3495 3620 ...
 $ Origin            : Factor w/ 2 levels "USA","non-USA": 2 2 2 2 2 1 1 1 1 1 ...
 $ Make              : Factor w/ 93 levels "Acura Integra",..: 1 2 4 3 5 6 7 9 8 10 ...

R의 aggregate() 함수로 차종(Type)별 도시 연비(MPG.city)와 고속도로 연비(MPG.highway)의 평균을 구해보겠습니다.

> # aggregate 
> R_aggregate_mean <- aggregate(Cars93[,c(7,8)], 
+                               by = list(Car_Type = Cars93$Type), # list
+                               FUN = mean, # function
+                               na.rm = TRUE)
> 
> R_aggregate_mean
  Car_Type MPG.city MPG.highway
1  Compact 22.68750    29.87500
2    Large 18.36364    26.72727
3  Midsize 19.54545    26.72727
4    Small 29.85714    35.47619
5   Sporty 21.78571    28.78571
6      Van 17.00000    21.88889

이번에는 install.packages()함수와 library()함수를 사용하여 sqldf Package 를 설치하고 호출한 후에, sqldf 패키지를 사용하여 위와 같이 차종(Type)별 도시 연비(MPG.city)와 고속도로 연비(MPG.highway)의 평균을 구해보겠습니다.

> install.packages("sqldf")
Installing package into ‘C:/Users/user/Documents/R/win-library/3.2’
(as ‘lib’ is unspecified)
trying URL 'http://cran.rstudio.com/bin/windows/contrib/3.2/sqldf_0.4-10.zip'
Content type 'application/zip' length 71825 bytes (70 KB)
downloaded 70 KB

package ‘sqldf’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
	C:\Users\user\AppData\Local\Temp\Rtmp4i7Dhq\downloaded_packages
> library(sqldf)
필요한 패키지를 로딩중입니다: gsubfn
필요한 패키지를 로딩중입니다: proto
필요한 패키지를 로딩중입니다: RSQLite
필요한 패키지를 로딩중입니다: DBI
Warning messages:
1: 패키지 ‘sqldf’는 R 버전 3.2.2에서 작성되었습니다 
2: 패키지 ‘gsubfn’는 R 버전 3.2.2에서 작성되었습니다 
3: 패키지 ‘RSQLite’는 R 버전 3.2.2에서 작성되었습니다 
4: 패키지 ‘DBI’는 R 버전 3.2.2에서 작성되었습니다

> R_sqldf_1 <- sqldf('
+                  select "Type" as "Car_Type", 
+                  avg("MPG.city") as "mean_MPG.city", 
+                  avg("MPG.highway") as "mean_MPG.highway"  
+                  from Cars93 
+                  group by Type
+                  order by Type
+                  ')
> R_sqldf_1
  Car_Type mean_MPG.city mean_MPG.highway
1  Compact      22.68750         29.87500
2    Large      18.36364         26.72727
3  Midsize      19.54545         26.72727
4    Small      29.85714         35.47619
5   Sporty      21.78571         28.78571
6      Van      17.00000         21.88889

R의 aggregate()함수로 만든 평균과 sqldf로 만든 평균 데이터 셋을 차종(Type) 을 key로 항 merge 한 후에 두 값들이 서로 같은지 한번 점검해보겠습니다.

> # 두개 데이터 셋 Merge, 동일 여부 check
> Type_mean <- merge(R_aggregate_mean, R_sqldf_1, by = 'Car_Type')
> Type_mean <- transform(Type_mean, 
+                        gap_MPG.city = MPG.city - mean_MPG.city, 
+                        gap_MPG.highway = MPG.highway - mean_MPG.highway)
>

> Type_mean
  Car_Type MPG.city MPG.highway mean_MPG.city mean_MPG.highway gap_MPG.city gap_MPG.highway
1  Compact 22.68750    29.87500      22.68750         29.87500            0               0
2    Large 18.36364    26.72727      18.36364         26.72727            0               0
3  Midsize 19.54545    26.72727      19.54545         26.72727            0               0
4    Small 29.85714    35.47619      29.85714         35.47619            0               0
5   Sporty 21.78571    28.78571      21.78571         28.78571            0               0
6      Van 17.00000    21.88889      17.00000         21.88889            0               0

얼핏 보면 R의 aggregate() 함수와 sqldf 가 서로 큰 차이가 없거나 혹은 aggregate()함수가 더 편하다고 느낄 수도 있겠습니다. 그런데, 아래의 경우처럼 다수의 함수들(count, sum, avg, variance, stdev, min, max 등)을 그룹 변수에 대해서 구분해서 집계를 할 경우에는, 그리고 SQL에 익숙한 사용자라면 sqldf 패키지를 사용하는게 편할 수 있을 것입니다

> # SQL의 aggregation 함수 사용하기
> R_sqldf_2 <- sqldf('
+                    select "Type" as "Car_Type", 
+                    count("MPG.city") as "count_MPG.city", 
+                    sum("MPG.city") as "sum_MPG.city", 
+                    
+                    avg("MPG.city") as "mean_MPG.city", 
+                    variance("MPG.city") as "variance_MPG.city", 
+                    stdev("MPG.city") as "stdev_MPG.city", 
+                    
+                    min("MPG.city") as "min_MPG.city", 
+ 
+                    max("MPG.city") as "max_MPG.city"
+                    
+                    from Cars93 
+                    group by Type
+                    order by Type desc
+                    ')
> 
> # count :  행의 개수
> # sum : 합계
> # avg : 평균
> # var : 분산
> # stddev : 표준편차
> # min : 최소값
> # max : 최대값
> # order by xx desc : 내림차순 정렬

> 
> R_sqldf_2
  Car_Type count_MPG.city sum_MPG.city mean_MPG.city variance_MPG.city stdev_MPG.city min_MPG.city max_MPG.city
1      Van              9          153      17.00000          1.500000       1.224745           15           18
2   Sporty             14          305      21.78571         15.258242       3.906180           17           30
3    Small             21          627      29.85714         37.328571       6.109711           22           46
4  Midsize             22          430      19.54545          3.593074       1.895540           16           23
5    Large             11          202      18.36364          2.254545       1.501514           16           20
6  Compact             16          363      22.68750          3.695833       1.922455           20           26

변수명을 SQL 문 내에서 바로 부여하는 것도 편리합니다. 그리고 SQL에 능숙한 분석가라면 subquery를 사용해서 한방에 query를 다 돌려서 원하는 데이터셋을 만들어낼 수도 있겠습니다. (단, sqldf는 속도는 희생될 수 있음)

그렇다고 sqldf가 데이터 집계를 하는데 있어 모든 통계량을 다 한번에 할 수 있는것은 아닙니다. R에서는 아래 처럼 median, quantile 을 1줄만에 처리할 수 있는 반면에, 이것과 동일한 결과를 얻으려면 SQL로는 참 어렵습니다.

> # R로 median, quantile 지정해서 구하기
> R_aggregate_median <- aggregate(Cars93[,c(7,8)], by = list(Car_Type = Cars93$Type), FUN = median)
> R_aggregate_median
  Car_Type MPG.city MPG.highway
1  Compact     23.0        30.0
2    Large     19.0        26.0
3  Midsize     19.0        26.5
4    Small     29.0        33.0
5   Sporty     22.5        28.5
6      Van     17.0        22.0
> 
> quantile_MPG.city <- quantile(Cars93[,c("MPG.city")], c(0, .01, .05, .1, .25, .5, .75, .9, .95, .99, 1))
> quantile_MPG.city
   0%    1%    5%   10%   25%   50%   75%   90%   95%   99%  100% 
15.00 15.00 16.60 17.00 18.00 21.00 25.00 29.00 31.40 42.32 46.00

sqldf 가 편하다고 했다가, 그냥 R 함수가 편하다가 했다가 오락가락 하는 것처럼 보일 수도 있겠는데요, 위의 예제를 보시고 데이터 전처리, 분석의 목적, 상황에 맞게 sqldf와 aggregate() 함수, R 함수를 선별해서 사용하시면 되겠습니다.

{dplyr} package의 summarise(n = n()), tally(), count() 함수를 사용한 집계 방법은 http://rfriend.tistory.com/240 포스팅을 참고하세요.

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡' 단추를 꾸욱 눌러주세요.^^

728x90

저작자표시 비영리 변경금지

'R 분석과 프로그래밍 > R 데이터 전처리' 카테고리의 다른 글

R clearing of console, datasets, plots in RStudio (0)	2015.09.17
R 데이터 재구조화 reshape 패키지 melt(), cast() 함수, reshape2의 acast(), dcast() 함수, tidyverse의 spread() 함수 (19)	2015.08.29
R 데이터 변환 : (6) 시그널 데이터 변환 - FFT (Fast Fourier Transform) (9)	2015.08.16
R 데이터변환 : (5) 차원 축소 - (5-2) 요인분석(factor analysis) (8)	2015.08.08
R 데이터 변환 : (5) 차원 축소 - (5-1) 주성분분석 (PCA) (59)	2015.08.08

Posted by Rfriend

,

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'SQL'에 해당되는 글 4건

[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[SQL]웹으로 SQL 연습할 수 있는 웹사이트: w3schools.com/sql : 유통업체 ERD 및 Query 예시

'Hive' 카테고리의 다른 글

[SQL] 웹으로 SQL 연습할 수 있는 online site : w3schools.com/sql

'Hive' 카테고리의 다른 글

R 에서 SQL 사용 sqldf 패키지, 집계 aggregate() 함수

'R 분석과 프로그래밍 > R 데이터 전처리' 카테고리의 다른 글

카테고리

태그목록

티스토리툴바