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

  (0) Python 패키지 사전 설치 및 PostgreSQL, Greenplum database 접속

-- (명령 프롬프트 창에서)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

%%sql
DROP TABLE IF EXISTS houses;
CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
            size INT, lot INT, region TEXT);
INSERT INTO houses VALUES
  (1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100, 'seoul'),
  (2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000, 'seoul'),
  (3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500, 'busan'),
  (4 ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500, 'seoul'),
  (5 , 1320 ,       3 ,    2 , 133000 , 1500 , 30000, 'bundang'),
  (6 , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700, 'bundang'),
  (7 , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000, 'busan'),
  (8 ,  680 ,       2 ,    1 , 142500 , 1170 , 22000, 'busan'),
  (9 , 1840 ,       3 ,    2 , 160000 , 1500 , 19000, 'inchon'),
 (10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000, 'seoul'),
 (11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500, 'inchon'),
 (12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000, 'busan'),
 (13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000, 'bundang'),
 (14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000, 'bundang'),
 (15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000, 'inchon');

  (1) ResultSet = %sql SELECT ... (single-line query)

# for 1 line SQL query
house_sub_1 = %sql SELECT * FROM houses WHERE price <= 80000;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

house_sub_1

[Out]

type(house_sub_1)

[Out] sql.run.ResultSet

# converting 'sql.run.ResultSet into pandas DataFrame

df_house_sub_1 = house_sub_1.DataFrame()
type(df_house_sub_1)

[Out] pandas.core.frame.DataFrame

# plot using pandas function

import matplotlib.pyplot as plt
df_house_sub_1['price'].plot(kind='bar')
plt.show()

  (2) %%sql ResultSet << SELECT ... (multi-line queries)

%%sql house_sub_2 << SELECT *
FROM houses
WHERE price <= 80000;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.
Returning data to local variable house_sub_2

house_sub_2

[Out]

type(house_sub_2)

[Out] sql.run.ResultSet

# Converting 'sql.run.ResultSet' into pandas DataFrame

df_house_sub_2 = house_sub_2.DataFrame()
type(df_house_sub_2)

[Out] pandas.core.frame.DataFrame

  (3) %%sql SELECT ... 를 먼저 실행한 후에,

    ResultSet = _  (select 결과를 사후에 할당)

%sql SELECT * FROM houses WHERE price <= 80000;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

# Capturing the quries results and assigning to ResultSet

house_sub_3 = _

house_sub_3

[Out]

type(house_sub_3)

[Out] sql.run.ResultSet

df_house_sub_3 = house_sub_3.DataFrame()
type(df_house_sub_3)

[Out] pandas.core.frame.DataFrame

  (0) 필요 Python 패키지 사전 설치

-- (명령 프롬프트 창에서 pip 로 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

%load_ext sql

%sql postgresql://gpadmin:changeme@localhost/demo

[Out] 'Connected: gpadmin@demo'

%sql select version();

 * postgresql://gpadmin:***@localhost/demo
1 rows affected.

%%sql
DROP TABLE IF EXISTS houses;
CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
            size INT, lot INT, region TEXT);
INSERT INTO houses VALUES
  (1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100, 'seoul'),
  (2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000, 'seoul'),
  (3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500, 'busan'),
  (4 ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500, 'seoul'),
  (5 , 1320 ,       3 ,    2 , 133000 , 1500 , 30000, 'bundang'),
  (6 , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700, 'bundang'),
  (7 , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000, 'busan'),
  (8 ,  680 ,       2 ,    1 , 142500 , 1170 , 22000, 'busan'),
  (9 , 1840 ,       3 ,    2 , 160000 , 1500 , 19000, 'inchon'),
 (10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000, 'seoul'),
 (11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500, 'inchon'),
 (12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000, 'busan'),
 (13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000, 'bundang'),
 (14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000, 'bundang'),
 (15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000, 'inchon');

  (방법 1) Variable Substitution:   %sql SELECT :variable_name

id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN :id_var;

[Out]

 * postgresql://gpadmin:***@localhost/demo
3 rows affected.

region_var = 'seoul'

%sql SELECT * FROM houses WHERE region = :region_var;

[Out]

 * postgresql://gpadmin:***@localhost/demo
4 rows affected.

  (방법 2) Variable Substitution:  %sql SELECT {variable_name}

id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN {id_var};

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

region_var = 'seoul'

%sql SELECT * FROM houses WHERE region = '{region_var}';

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
4 rows affected.

  (방법 3) Variable Substitution:  %sql SELECT $variable_name

id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN $id_var;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

region_var = "'seoul'"

%sql SELECT * FROM houses WHERE region = $region_var;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

(0) Python 패키지 사전 설치

-- (명령 프롬프트 창에서 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 (1) %sql postgresql://Username:Password@Host:Port/Database

%load_ext sql

# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@demo'

 (2) %sql $connection_string

%load_ext sql

# DB credentials

username = "gpadmin"
password = "changeme"
host = "localhost"
port = "5432"
database = "demo"

# connection strings using Python string format
connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

# dynamic access dredentials
%sql $connection_string

 (3) config.py 별도 파일 & %sql $connection_string

%load_ext sql

# put a folder and DB credential files at HOME directory

import os
homedir = os.getenv('HOME')
cred_path = os.path.join(homedir, 'db_cred')

# add a 'cred_path' for interpreter to search
import sys
sys.path.append(cred_path)

# import DB credentials from 'gpdb_credentials.py' dictionary file.

from gpdb_credentials import demo_db

# parsing DB credentials and connect to Greenplum using %sql $connection_string

username = demo_db['Username']
password = demo_db['Password']
host = demo_db['Host']
port = demo_db['Port']
database = demo_db['Database']

connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

%sql $connection_string

(4) db_credentials 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path

%load_ext sql

import os
homedir = os.getenv('HOME')

# parse and configure gpdb credentials and access to GPDB
%config SqlMagic.dsn_filename = "$homedir/.odbc.ini"

# put [alias_name] after %sql in the first line
%sql [demo_db] SELECT version();

[Out] * postgresql://gpadmin:***@localhost:5432/demo

1 rows affected.

 (1) melt() 함수로 data.table 자료의 칼럼 이름에서 패턴을 찾아 녹여서(melt)

     넓은 자료를 길게 (wide to long) 재구조화 하기

library(data.table)

## -- melting and casting multiple columns
s2 <- "family_id age_mother dob_child1 dob_child2 dob_child3 gender_child1 gender_child2 gender_child3
1         30 1998-11-26 2000-01-29         NA             1             2            NA
2         27 1996-06-22         NA         NA             2            NA            NA
3         26 2002-07-11 2004-04-05 2007-09-02             2             2             1
4         32 2004-10-10 2009-08-27 2012-07-21             1             1             1
5         29 2000-12-05 2005-02-28         NA             2             1            NA"

DT <- fread(s2)
DT
# family_id age_mother dob_child1 dob_child2 dob_child3 gender_child1 gender_child2 gender_child3
# 1:         1         30 1998-11-26 2000-01-29       <NA>             1             2            NA
# 2:         2         27 1996-06-22       <NA>       <NA>             2            NA            NA
# 3:         3         26 2002-07-11 2004-04-05 2007-09-02             2             2             1
# 4:         4         32 2004-10-10 2009-08-27 2012-07-21             1             1             1
# 5:         5         29 2000-12-05 2005-02-28       <NA>             2             1            NA

str(DT)
# Classes 'data.table' and 'data.frame':    5 obs. of  8 variables:
#   $ family_id    : int  1 2 3 4 5
# $ age_mother   : int  30 27 26 32 29
# $ dob_child1   : IDate, format: "1998-11-26" "1996-06-22" "2002-07-11" "2004-10-10" ...
# $ dob_child2   : IDate, format: "2000-01-29" NA "2004-04-05" "2009-08-27" ...
# $ dob_child3   : IDate, format: NA NA "2007-09-02" "2012-07-21" ...
# $ gender_child1: int  1 2 2 1 2
# $ gender_child2: int  2 NA 2 1 1
# $ gender_child3: int  NA NA 1 1 NA
# - attr(*, ".internal.selfref")=<externalptr>

## a) Enhanced melt to multiple columns simultaneously.
## The functionality is implemented entirely in C,
## and is therefore both fast and memory efficient in addition to being straightforward.
colA <- paste("dob_child", 1:3, sep="")
colB <- paste("gender_child", 1:3, sep="")

colA
# [1] "dob_child1" "dob_child2" "dob_child3"

colB
# [1] "gender_child1" "gender_child2" "gender_child3"

## We pass a list of columns to 'measure.vars', where each element
## of the list contains the columns that should be combined together.
DT.m2 <- melt(DT,
              measure = list(colA, colB),
              value.name = c("dob", "gender"))
DT.m2
# family_id age_mother variable        dob gender
# 1:         1         30        1 1998-11-26      1
# 2:         2         27        1 1996-06-22      2
# 3:         3         26        1 2002-07-11      2
# 4:         4         32        1 2004-10-10      1
# 5:         5         29        1 2000-12-05      2
# 6:         1         30        2 2000-01-29      2
# 7:         2         27        2       <NA>     NA
# 8:         3         26        2 2004-04-05      2
# 9:         4         32        2 2009-08-27      1
# 10:         5         29        2 2005-02-28      1
# 11:         1         30        3       <NA>     NA
# 12:         2         27        3       <NA>     NA
# 13:         3         26        3 2007-09-02      1
# 14:         4         32        3 2012-07-21      1
# 15:         5         29        3       <NA>     NA

str(DT.m2)
# Classes 'data.table' and 'data.frame':    15 obs. of  5 variables:
#   $ family_id : int  1 2 3 4 5 1 2 3 4 5 ...
# $ age_mother: int  30 27 26 32 29 30 27 26 32 29 ...
# $ variable  : Factor w/ 3 levels "1","2","3": 1 1 1 1 1 2 2 2 2 2 ...
# $ dob       : IDate, format: "1998-11-26" "1996-06-22" "2002-07-11" "2004-10-10" ...
# $ gender    : int  1 2 2 1 2 2 NA 2 1 1 ...
# - attr(*, ".internal.selfref")=<externalptr>

## Using patterns()
DT.m2 <- melt(DT,
              # using regular expression
              measure = patterns("^dob", "^gender"),
              value.name = c("dob", "gender"))

DT.m2

# family_id age_mother variable        dob gender
# 1:         1         30        1 1998-11-26      1
# 2:         2         27        1 1996-06-22      2
# 3:         3         26        1 2002-07-11      2
# 4:         4         32        1 2004-10-10      1
# 5:         5         29        1 2000-12-05      2
# 6:         1         30        2 2000-01-29      2
# 7:         2         27        2       <NA>     NA
# 8:         3         26        2 2004-04-05      2
# 9:         4         32        2 2009-08-27      1
# 10:         5         29        2 2005-02-28      1
# 11:         1         30        3       <NA>     NA
# 12:         2         27        3       <NA>     NA
# 13:         3         26        3 2007-09-02      1
# 14:         4         32        3 2012-07-21      1
# 15:         5         29        3       <NA>     NA

(2) dcast() 함수로 data.table 자료의 여러개의 칼럼 이름을 주조(cast)하여

     긴 자료를 넓게 (long to wide) 재구조화 하기

## b) Enhanced dcast : casting multiple value.vars simultaneously
## we can provide multiple 'value.var' columns to dcast for data.tables directly
## Everything is taken care of internally, and efficiently. fast and memory efficient.
DT.c2 <- dcast(DT.m2,
               family_id + age_mother ~ variable,
               value.var = c("dob", "gender"))

DT.c2

# family_id age_mother      dob_1      dob_2      dob_3 gender_1 gender_2 gender_3
# 1:         1         30 1998-11-26 2000-01-29       <NA>        1        2       NA
# 2:         2         27 1996-06-22       <NA>       <NA>        2       NA       NA
# 3:         3         26 2002-07-11 2004-04-05 2007-09-02        2        2        1
# 4:         4         32 2004-10-10 2009-08-27 2012-07-21        1        1        1
# 5:         5         29 2000-12-05 2005-02-28       <NA>        2        1       NA

  (1) melt() : data.table 을 녹여서 넓은 자료구조를 길게 (wide to long) 재구조화

library(data.table)

library(MASS)

DT <- data.table(Cars93[1:5, c("Model", "Type", "DriveTrain", "Length", "Width", "Weight")])

print(DT)

# Model    Type DriveTrain Length Width Weight

# 1: Integra   Small      Front    177    68   2705

# 2:  Legend Midsize      Front    195    71   3560

# 3:      90 Compact      Front    180    67   3375

# 4:     100 Midsize      Front    193    70   3405

# 5:    535i Midsize       Rear    186    69   3640

## -- 1. Melting data.tables (wide to long)

DT_melt_1 <- melt(DT, 

                  id.vars = c("Model", "Type", "DriveTrain"), 

                  measure.vars = c("Length", "Width", "Weight"))

## By default, the molten columns are automatically named 'variable' and 'value'.

print(DT_melt_1)

# Model    Type DriveTrain variable value

# 1: Integra   Small      Front   Length   177

# 2:  Legend Midsize      Front   Length   195

# 3:      90 Compact      Front   Length   180

# 4:     100 Midsize      Front   Length   193

# 5:    535i Midsize       Rear   Length   186

# 6: Integra   Small      Front    Width    68

# 7:  Legend Midsize      Front    Width    71

# 8:      90 Compact      Front    Width    67

# 9:     100 Midsize      Front    Width    70

# 10:    535i Midsize       Rear    Width    69

# 11: Integra   Small      Front   Weight  2705

# 12:  Legend Midsize      Front   Weight  3560

# 13:      90 Compact      Front   Weight  3375

# 14:     100 Midsize      Front   Weight  3405

# 15:    535i Midsize       Rear   Weight  3640

str(DT_melt_1)

# Classes 'data.table' and 'data.frame': 15 obs. of  5 variables:

#   $ Model     : Factor w/ 93 levels "100","190E","240",..: 49 56 9 1 6 49 56 9 1 6 ...

# $ Type      : Factor w/ 6 levels "Compact","Large",..: 4 3 1 3 3 4 3 1 3 3 ...

# $ DriveTrain: Factor w/ 3 levels "4WD","Front",..: 2 2 2 2 3 2 2 2 2 3 ...

# $ variable  : Factor w/ 3 levels "Length","Width",..: 1 1 1 1 1 2 2 2 2 2 ...

# $ value     : int  177 195 180 193 186 68 71 67 70 69 ...

# - attr(*, ".internal.selfref")=<externalptr>

## By default, 'variable' column is of type factor. 

## Set variable.factor argument to FALSE if you like to return a character vector. 

DT_melt_2 <- melt(DT, 

                  id.vars = c("Model", "Type", "DriveTrain"), 

                  measure.vars = c("Length", "Width", "Weight"), 

                  variable.factor = FALSE)

str(DT_melt_2)

# Classes 'data.table' and 'data.frame': 15 obs. of  5 variables:

#   $ Model     : Factor w/ 93 levels "100","190E","240",..: 49 56 9 1 6 49 56 9 1 6 ...

# $ Type      : Factor w/ 6 levels "Compact","Large",..: 4 3 1 3 3 4 3 1 3 3 ...

# $ DriveTrain: Factor w/ 3 levels "4WD","Front",..: 2 2 2 2 3 2 2 2 2 3 ...

# $ variable  : chr  "Length" "Length" "Length" "Length" ...  # <--- charactr vector

# $ value     : int  177 195 180 193 186 68 71 67 70 69 ...

# - attr(*, ".internal.selfref")=<externalptr>

## Name the 'variable' and 'value' columns to 'measure' and 'val' respectively.

DT_melt_3 <- melt(DT, 

                  id.vars = c("Model", "Type", "DriveTrain"), 

                  measures.vars = c("Length", "Width", "Weight"), 

                  variable.name = "measure", 

                  value.name = "val")

head(DT_melt_3)

# Model    Type DriveTrain measure val

# 1: Integra   Small      Front  Length 177

# 2:  Legend Midsize      Front  Length 195

# 3:      90 Compact      Front  Length 180

# 4:     100 Midsize      Front  Length 193

# 5:    535i Midsize       Rear  Length 186

# 6: Integra   Small      Front   Width  68

  (2) dcast() : data.table 을 주조하여 긴 자료구조를 넓게 (long to wide) 재구조화

## -- 2. dcasting data.tables (long to wide)

## reverse operation of melting

## dcast uses formula interface.

dcast(DT_melt_1, Model + Type + DriveTrain ~ variable)

# Model    Type DriveTrain Length Width Weight

# 1:     100 Midsize      Front    193    70   3405

# 2:    535i Midsize       Rear    186    69   3640

# 3:      90 Compact      Front    180    67   3375

# 4: Integra   Small      Front    177    68   2705

# 5:  Legend Midsize      Front    195    71   3560

## 'value.var' denotes the column to be filled in with while casting to wide format. 

dcast(DT_melt_3, Model + Type + DriveTrain ~ measure, 

      value.var = "val")

# Model    Type DriveTrain Length Width Weight

# 1:     100 Midsize      Front    193    70   3405

# 2:    535i Midsize       Rear    186    69   3640

# 3:      90 Compact      Front    180    67   3375

# 4: Integra   Small      Front    177    68   2705

# 5:  Legend Midsize      Front    195    71   3560

## You can pass a function to aggregate by in dcast with the argument 'fun.aggregate. 

dcast(DT_melt_1, Type ~ variable, fun.aggregate = mean)

# Type   Length Width Weight

# 1: Compact 180.0000    67   3375

# 2: Midsize 191.3333    70   3535

# 3:   Small 177.0000    68   2705

## 'fun.agg' is the same with 'fun.aggregate'

## subset: Specified if casting should be done on a subset of the data.

dcast(DT_melt_1, Type ~ variable, 

      fun.agg = function(x) sum(!is.na(x)), 

      subset = .(variable != "Length"))

# Type Width Weight

# 1: Compact     1      1

# 2: Midsize     3      3

# 3:   Small     1      1

  (1) := 연산자를 사용해 data.table을 참조하여 얕은 복사한 객체를 수정했을 때 부작용

library(data.table)
library(MASS)

DT <- data.table(Cars93[, c("Model", "Type", "Price", "Length", "Width")])
head(DT)
# Model    Type Price Length Width
# 1: Integra   Small  15.9    177    68
# 2:  Legend Midsize  33.9    195    71
# 3:      90 Compact  29.1    180    67
# 4:     100 Midsize  37.7    193    70
# 5:    535i Midsize  30.0    186    69
# 6: Century Midsize  15.7    189    69

## (1) Shallow copy by reference
shallow_copy_func <- function(data_tbl){
  data_tbl[, area := Length * Width]
  data_tbl[, .(max_area = max(area)), by = Type]
}

DT_2 <- shallow_copy_func(DT)

  (2) copy() 함수를 사용해 깊은 복사하기

## Using copy() function did not update DT data.table by reference.
## It doesn't contain the column 'Area'.
head(DT)
# Model    Type Price Length Width
# 1: Integra   Small  15.9    177    68
# 2:  Legend Midsize  33.9    195    71
# 3:      90 Compact  29.1    180    67
# 4:     100 Midsize  37.7    193    70
# 5:    535i Midsize  30.0    186    69
# 6: Century Midsize  15.7    189    69


## DT_3 contains the maximum Price for each Type.
head(DT_3)
# Type max_area
# 1:   Small    12036
# 2: Midsize    15096
# 3: Compact    12730
# 4:   Large    16863
# 5:  Sporty    14700
# 6:     Van    15132

## When we store the column names on to a variable, e.g. DT_names = names(DT),
## and then add/ update/ delete columns(s) by referene,
## it would also modify DT_names, unless we do copy(names(DT)).
DT = data.table(x = 1L, y = 2L)
DT_names = names(DT)
print(DT_names)
# [1] "x" "y"

## add a new column by reference.
DT[, z := 3L]

## DT_names also gets updated.
print(DT_names)
# [1] "x" "y" "z"

## use 'copy()' function, deep copy.
DT_names = copy(names(DT))
DT[, w := 4L]

## DT_names doesn't get updated. No "w" column in DT_names.
print(DT_names)
# [1] "x" "y" "z"

(0) 사전 설치가 필요한 Python 패키지 리스트

-- 명령 프롬프트 창에서 pip 로 python 패키지 설치

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 (1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

%load_ext sql

# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@testdb'

 (2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

%sql SELECT version();

[Out]

%%sql
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
    schemaname != 'information_schema'
LIMIT 1;

 (3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands (\l, \dn, \dt)

%sql \l

[Out]

  (1) Key 값이 매칭되는 모든 행 가져오기: mult = "all"

library(data.table)

library(MASS)

DT <- data.table(Cars93[, c("Model", "Type", "Price", "DriveTrain")])

> str(DT)

Classes 'data.table' and 'data.frame': 93 obs. of  4 variables:

 $ Model     : Factor w/ 93 levels "100","190E","240",..: 55 9 22 30 52 83 90 11 7 14 ...

 $ Type      : Factor w/ 6 levels "Compact","Large",..: 1 1 1 1 1 1 1 1 1 1 ...

 $ Price     : num  19.5 29.1 13.4 11.4 15.8 13.3 11.3 17.5 16.5 15.7 ...

 $ DriveTrain: Factor w/ 3 levels "4WD","Front",..: 1 2 2 2 2 2 2 2 2 2 ...

 - attr(*, ".internal.selfref")=<externalptr> 

 - attr(*, "sorted")= chr [1:2] "Type" "DriveTrain"

> # Set the key with Type and DriveTrain columns

> setkey(DT, Type, DriveTrain)

> key(DT)

[1] "Type"       "DriveTrain"

# Subset 'all' rows where  

# where Type matches ("Compact", "Sporty") and DriveTrain matches "Rear".  

DT[.(c("Compact", "Van", "Sporty"), "Rear")] # default mult = "all"

# or equivalently

> DT[.(c("Compact", "Van", "Sporty"), "Rear"), mult = "all"] # default

      Model    Type Price DriveTrain

1:     190E Compact  31.9       Rear      #<--- first of "Compact" Type & "Rear" DriveTrain

2:      240 Compact  22.7       Rear      #<--- last of "Compact" Type & "Rear" DriveTrain

3:     <NA>     Van    NA       Rear    #<--- first of "Van" Type & "Rear" DriveTrain

4:     <NA>     Van    NA       Rear    #<--- last of "Van" Type & "Rear" DriveTrain

5:   Camaro  Sporty  15.1       Rear      #<--- first of "Sporty" Type & "Rear" DriveTrain

6: Corvette  Sporty  38.0       Rear

7:  Mustang  Sporty  15.9       Rear

8:     RX-7  Sporty  32.5       Rear

9: Firebird  Sporty  17.7       Rear         #<--- last of "Sporty" Type & "Rear" DriveTrain

  (2) Key 값이 매칭되는 첫번째 행 가져오기: mult = "first"

# Subset only the 'first' matching row from all rows 

# where Type matches ("Compact", "Van", "Sporty") and DriveTrain matches "Rear".  

> DT[.(c("Compact", "Van", "Sporty"), "Rear"), mult = "first"]

    Model    Type Price DriveTrain

1:   190E Compact  31.9       Rear

2:   <NA>     Van    NA       Rear

3: Camaro  Sporty  15.1       Rear

  (3) Key 값이 매칭되는 마지막 행 가져오기: mult = "last"

# Subset only the 'last' matching row from all rows

#  where Type matches ("Compact", "Van", "Sporty") and DriveTrain matches "Rear".  

> DT[.(c("Compact", "Van", "Sporty"), "Rear"), mult = "last"]

      Model    Type Price DriveTrain

1:      240 Compact  22.7       Rear

2:     <NA>     Van    NA       Rear

3: Firebird  Sporty  17.7       Rear

  (4) Key 값이 매칭되는 마지막 행의 값이 존재하는 행만 가져오기

      : mult = "last", nomatch = NULL

# We can choose if queries that do not match should return NA 

# or be skipped altogether using the nomatch argument.(nomatch = NULL)

> DT[.(c("Compact", "Van", "Sporty"), "Rear"), 

+    mult = "last", 

+    nomatch = NULL]

      Model    Type Price DriveTrain

1:      240 Compact  22.7       Rear

2: Firebird  Sporty  17.7       Rear

  1. 키 설정 후, 특정 키(Key) 값에 행을 한정해 칼럼 j 가져오기 (select in j)

library(data.table)
library(MASS)
DT <- data.table(Cars93[, c("Model", "Type", "Price", "MPG.highway", "DriveTrain")])

# Combining keys with j and by
# Set keys with Type and DriveTrain
setkey(DT, Type, DriveTrain)

> key(DT)
[1] "Type"       "DriveTrain"

>

> # reordered by Keys

> head(DT, 10)
       Model    Type Price MPG.highway DriveTrain
 1:   Legacy Compact  19.5          30        4WD
 2:       90 Compact  29.1          26      Front
 3: Cavalier Compact  13.4          36      Front
 4:  Corsica Compact  11.4          34      Front
 5:  LeBaron Compact  15.8          28      Front
 6:   Spirit Compact  13.3          27      Front
 7:    Tempo Compact  11.3          27      Front
 8:   Accord Compact  17.5          31      Front
 9:      626 Compact  16.5          34      Front
10:   Altima Compact  15.7          30      Front

> # Select in j
> DT[.("Compact", "Front"), .(Model, MPG.highway)]

       Model MPG.highway
 1:       90          26
 2: Cavalier          36
 3:  Corsica          34
 4:  LeBaron          28
 5:   Spirit          27
 6:    Tempo          27
 7:   Accord          31
 8:      626          34
 9:   Altima          30
10:  Achieva          31
11:  Sunbird          31
12:      900          26
13:   Passat          30

  2.  체인연산으로 결과 정렬하기 (chaining)

> # Chaining
> # - use chaining to order the MPG.highway column.
> # in ascending order
> DT[.("Compact", "Front"), .(Model, MPG.highway)][order(MPG.highway)]
       Model MPG.highway
 1:       90          26
 2:      900          26
 3:   Spirit          27
 4:    Tempo          27
 5:  LeBaron          28
 6:   Altima          30
 7:   Passat          30
 8:   Accord          31
 9:  Achieva          31
10:  Sunbird          31
11:  Corsica          34
12:      626          34
13: Cavalier          36
>

> # in descending order
> DT[.("Compact", "Front"), .(Model, MPG.highway)][order(-MPG.highway)]
       Model MPG.highway
 1: Cavalier          36
 2:  Corsica          34
 3:      626          34
 4:   Accord          31
 5:  Achieva          31
 6:  Sunbird          31
 7:   Altima          30
 8:   Passat          30
 9:  LeBaron          28
10:   Spirit          27
11:    Tempo          27
12:       90          26
13:      900          26

  3. 특정 키(Key) 값에 행을 한정해 칼럼 j 에 연산 수행하기 (compute or do in j)

> # Compute or do in j
> # - Find the maximum, minimum MPG.highway corresponding to Type == "Compact" and DriveTrain == "Front".
> DT[.("Compact", "Front"), max(MPG.highway)] # max
[1] 36
>
> DT[.("Compact", "Front"), mean(MPG.highway)] # mean
[1] 30.07692
>
> DT[.("Compact", "Front"), median(MPG.highway)] # median
[1] 30
>
> DT[.("Compact", "Front"), min(MPG.highway)] # min
[1] 26
>
> DT[.("Compact", "Front"), quantile(MPG.highway, c(0.25, 0.75))] # Q1, Q3
25% 75%
 27  31

  4. 칼럼 j에 := 를 사용하여 키의 부분집합에 할당하기(sub-assign by reference)

> # sub-assign by reference using := in j
> DT <- data.table(Cars93[, c("Model", "Type", "Price", "MPG.highway", "DriveTrain")])
> setkey(DT, Type)
> key(DT)
[1] "Type"
>
> DT[, .N, by=Type]
      Type  N
1: Compact 16
2:   Large 11
3: Midsize 22
4:   Small 21
5:  Sporty 14
6:     Van  9

>

> DT[.("Large"), Type := "Big"]

>

> DT[, .N, by=Type]
      Type  N
1: Compact 16
2:     Big 11
3: Midsize 22
4:   Small 21
5:  Sporty 14
6:     Van  9
>

> key(DT) # the key is removed by setting to NULL
NULL

  5. by 를 사용하여 그룹별 집계하기 (aggregation using by)

> # Aggregation using by
> setkey(DT, Type)
> key(DT) # key is Type.
[1] "Type"
>
> agg <- DT["Compact",
+           .(mpg_max = max(MPG.highway)),
+           keyby = DriveTrain] # keyby sets DriveTrain as the key and order it.
>
> head(agg)
   DriveTrain mpg_max
1:        4WD      30
2:      Front      36
3:       Rear      29