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

  (1) iris dataset 의 species 를 분류할 수 있는 Random Forest 분류 모델 훈련

#%% import libraries

import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

import seaborn as sns

#%% iris dataset

iris = sns.load_dataset('iris')

iris.info()

<class 'pandas.core.frame.DataFrame'>

RangeIndex: 150 entries, 0 to 149

Data columns (total 5 columns):

 #   Column        Non-Null Count  Dtype  

---  ------        --------------  -----  

 0   sepal_length  150 non-null    float64

 1   sepal_width   150 non-null    float64

 2   petal_length  150 non-null    float64

 3   petal_width   150 non-null    float64

 4   species       150 non-null    object 

dtypes: float64(4), object(1)

memory usage: 6.0+ KB

iris.groupby('species').size()

Out[1]: 

species

setosa        50

versicolor    50

virginica     50

dtype: int64

#%% scatter plot by 'species'

sns.scatterplot(x='petal_length', 

                y='petal_width', 

                hue='species', 

                style='species', 

                s=100, 

                data=iris)

plt.show()

#%% Classification using Random Forest

from sklearn.ensemble import RandomForestClassifier

X = np.array(iris[['petal_length', 'petal_width']])

y = iris['species']

y = np.where(y=='setosa', 0, np.where(y=='versicolor', 1, 2))

rfc = RandomForestClassifier(max_depth=2, n_estimators=200, random_state=1004)

rfc.fit(X, y)

  (2) numpy의 meshgrid() 로 설명변수 공간의 좌표들을 생성하여 

      Random Forest 분류 모델로 예측

#%% predict

pred = rfc.predict(X)

print(pred == y)

[ True  True  True  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True  True  True  True  True False  True

  True  True  True  True  True False  True  True  True  True  True False

  True  True  True  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True  True  True  True  True False  True

  True  True  True  True  True  True  True  True  True  True  True False

  True  True  True  True  True  True  True  True  True  True  True  True

  True False False  True  True  True  True  True  True  True  True  True

  True  True  True  True  True  True]

# create coordinate matrices from x_min~x_max, y_min~y_max coordinates

h = 0.01

x_min, x_max = X[:, 0].min() - .1, X[:, 0].max() + .1

y_min, y_max = X[:, 1].min() - .1, X[:, 1].max() + .1

xx, yy = np.meshgrid(np.arange(x_min, x_max, h), np.arange(y_min, y_max, h))

XY = np.c_[xx.ravel(), yy.ravel()]

XY.shape

Out[4]: (158600, 2)

# predict

pred_cls = rfc.predict(XY)

# align the shape of Z with xx

Z = pred_cls.reshape(xx.shape)

  (3) matplotlib.contourf() 로 Random Forest Classifier 예측 결과 시각화

      -->  (4) iris dataset 의 species 의 산점도를 겹쳐서 그리기

# Random Forest Classifier: Decision Boundary

plt.contourf(xx, yy, Z)

plt.axis('off')

plt.contourf(xx, yy, Z)

plt.axis('off')

sns.scatterplot(x='petal_length', 

                y='petal_width', 

                hue='species', 

                style='species', 

                s=100, 

                data=iris)

plt.title('Decision Boundary by Random Forest', fontsize=14)

plt.show()

plt.contourf(xx, yy, Z)

plt.axis('off')

# or plot data points using matplotlib and for loop

N = 50

CLS_NUM = 3

markers = ['o', 'x', 's']

for i in range(CLS_NUM):

    plt.scatter(X[i*N: (i+1)*N, 0], X[i*N:(i+1)*N, 1], s=40, marker=markers[i])

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

$ pip install nltk

-- NLTK 모듈 확인

$ pip show nltk

Name: nltk

Version: 3.5

Summary: Natural Language Toolkit

Home-page: http://nltk.org/

Author: Steven Bird

Author-email: stevenbird1@gmail.com

License: Apache License, Version 2.0

Location: /Users/ihongdon/anaconda3/envs/py3.8_tf2.3/lib/python3.8/site-packages

Requires: regex, click, tqdm, joblib

Required-by: 

 (1) NLTK로 Word Tokenization 하기

#%% word tokenization

import nltk

# Please use the NLTK Downloader to obtain the resource:

nltk.download('punkt')

sentense = "NLTK is a leading platform for building Python programs to work with human language data."

tokens = nltk.word_tokenize(sentense)

tokens

Out[1]: 

['NLTK',

 'is',

 'a',

 'leading',

 'platform',

 'for',

 'building',

 'Python',

 'programs',

 'to',

 'work',

 'with',

 'human',

 'language',

 'data',

 '.']

  (2) NLTK로 품사 태깅하기 (Tagging)

#%% word tagging

# Please use the NLTK Downloader to obtain the resource:

nltk.download('averaged_perceptron_tagger')

tagged = nltk.pos_tag(tokens)

tagged

POS tag list:

CC coordinating conjunction

CD cardinal digit

DT determiner

EX existential there (like: "there is" ... think of it like "there exists")

FW foreign word

IN preposition/subordinating conjunction

JJ adjective 'big'

JJR adjective, comparative 'bigger'

JJS adjective, superlative 'biggest'

LS list marker 1)

MD modal could, will

NN noun, singular 'desk'

NNS noun plural 'desks'

NNP proper noun, singular 'Harrison'

NNPS proper noun, plural 'Americans'

PDT predeterminer 'all the kids'

POS possessive ending parent\'s

PRP personal pronoun I, he, she

PRP$ possessive pronoun my, his, hers

RB adverb very, silently,

RBR adverb, comparative better

RBS adverb, superlative best

RP particle give up

TO to go 'to' the store.

UH interjection errrrrrrrm

VB verb, base form take

VBD verb, past tense took

VBG verb, gerund/present participle taking

VBN verb, past participle taken

VBP verb, sing. present, non-3d take

VBZ verb, 3rd person sing. present takes

WDT wh-determiner which

WP wh-pronoun who, what

WP$ possessive wh-pronoun whose

WRB wh-abverb where, when

* source: https://pythonprogramming.net/part-of-speech-tagging-nltk-tutorial/ 

  (3) WordNet에서 동의어 (Synonyms) 찾기

#%% Senses and Synonyms from Wordnet

# Please use the NLTK Downloader to obtain the resource:

nltk.download('wordnet')

from nltk.corpus import wordnet as wn

wn.synsets('car')

Out[3]: 

[Synset('car.n.01'),

 Synset('car.n.02'),

 Synset('car.n.03'),

 Synset('car.n.04'),

 Synset('cable_car.n.01')]

car = wn.synset('car.n.01')

car.definition()

Out[4]: 'a motor vehicle with four wheels; usually propelled by an internal combustion engine'

car.lemma_names()

for synset in wn.synsets('car'):

    print(synset.lemma_names())

['car', 'auto', 'automobile', 'machine', 'motorcar']

['car', 'railcar', 'railway_car', 'railroad_car']

['car', 'gondola']

['car', 'elevator_car']

['cable_car', 'car']

  (4) WordNet에서 반대말 (Antonyms) 찾기

#%% antonym

wn.lemma('supply.n.02.supply').antonyms()

Out[7]: [Lemma('demand.n.02.demand')]

wn.lemma('king.n.01.king').antonyms()

  (5) WordNet에서 단어 위계구조 (Hierarchy) 찾기

#%% The Wordnet Hierarchy

# 상위어 (hypernym)

from nltk.corpus import wordnet as wn

car = wn.synset('car.n.01')

car.hypernym_paths()

Out[9]: 

[[Synset('entity.n.01'),

  Synset('physical_entity.n.01'),

  Synset('object.n.01'),

  Synset('whole.n.02'),

  Synset('artifact.n.01'),

  Synset('instrumentality.n.03'),

  Synset('container.n.01'),

  Synset('wheeled_vehicle.n.01'),

  Synset('self-propelled_vehicle.n.01'),

  Synset('motor_vehicle.n.01'),

  Synset('car.n.01')],

 [Synset('entity.n.01'),

  Synset('physical_entity.n.01'),

  Synset('object.n.01'),

  Synset('whole.n.02'),

  Synset('artifact.n.01'),

  Synset('instrumentality.n.03'),

  Synset('conveyance.n.03'),

  Synset('vehicle.n.01'),

  Synset('wheeled_vehicle.n.01'),

  Synset('self-propelled_vehicle.n.01'),

  Synset('motor_vehicle.n.01'),

  Synset('car.n.01')]]

# indexing

car.hypernym_paths()[0]

Out[10]: 

[Synset('entity.n.01'),

 Synset('physical_entity.n.01'),

 Synset('object.n.01'),

 Synset('whole.n.02'),

 Synset('artifact.n.01'),

 Synset('instrumentality.n.03'),

 Synset('container.n.01'),

 Synset('wheeled_vehicle.n.01'),

 Synset('self-propelled_vehicle.n.01'),

 Synset('motor_vehicle.n.01'),

 Synset('car.n.01')]

  (6) WordNet을 이용한 단어 간 의미 유사도 (Semantic Similarity) 측정

#%% Semantic Similarity

# (1) Path Distance Similarity

right_whale = wn.synset('right_whale.n.01')

orca = wn.synset('orca.n.01')

right_whale.path_similarity(orca)

Out[11]: 0.16666666666666666

minke = wn.synset('minke_whale.n.01')

right_whale.path_similarity(minke)

Out[12]: 0.25

tortoise = wn.synset('tortoise.n.01')

right_whale.path_similarity(tortoise)

Out[13]: 0.07692307692307693

novel = wn.synset('novel.n.01')

right_whale.path_similarity(novel)

Out[14]: 0.043478260869565216

# (3) Leacock Chordorow (LCH) Similarity

right_whale.lch_similarity(orca)

Out[15]: 1.845826690498331

right_whale.lch_similarity(minke)

Out[16]: 2.2512917986064953

right_whale.lch_similarity(tortoise)

Out[17]: 1.072636802264849

right_whale.lch_similarity(novel)

Out[18]: 0.5020919437972361

# (2) Wu-Palmer Similarity

right_whale.wup_similarity(orca)

Out[19]: 0.8484848484848485

right_whale.wup_similarity(minke)

Out[20]: 0.9090909090909091

right_whale.wup_similarity(tortoise)

Out[21]: 0.6

right_whale.wup_similarity(novel)

Out[22]: 0.08333333333333333

  (방법 1) for loop 으로 A배열의 idx 위치에 B배열의 원소 순서대로 더하기

import numpy as np

A = np.arange(24).reshape(8, 3)

print(A)

[[ 0  1  2]
 [ 3  4  5]
 [ 6  7  8]
 [ 9 10 11]
 [12 13 14]
 [15 16 17]
 [18 19 20]
 [21 22 23]]

B = np.arange(12).reshape(4, 3)

print(B)

[[ 0  1  2]
 [ 3  4  5]
 [ 6  7  8]
 [ 9 10 11]]

idx = np.array([0, 0, 3, 5])

for i, id in enumerate(idx):

    A[id] += B[i]

print(A)

[[ 3  6  9]
 [ 3  4  5]
 [ 6  7  8]
 [15 17 19]
 [12 13 14]
 [24 26 28]
 [18 19 20]
 [21 22 23]]

  (방법 2) np.add.at(A, idx, B) 함수로 A배열의 idx 위치에 B배열의 원소 순서대로 더하기

import numpy as np

A = np.arange(24).reshape(8, 3)

B = np.arange(12).reshape(4, 3)

idx = np.array([0, 0, 3, 5])

np.add.at(A, idx, B)

print(A)

[[ 3  6  9]
 [ 3  4  5]
 [ 6  7  8]
 [15 17 19]
 [12 13 14]
 [24 26 28]
 [18 19 20]
 [21 22 23]]

  (1) SQL LIKE, ~~ 연산자 ('LIKE', or '~~' Operator)

 pattern matching

 returns

  SELECT 'abc' LIKE 'abc';

 true

  SELECT 'abc' LIKE 'a%';

 true 

  SELECT 'abc' LIKE '%b%';

 true 

  SELECT 'abc' LIKE '_b_';

 true 

  SELECT 'abc' LIKE '_c';

 false 

  SELECT 'abc' LIKE '__c';

 true 

 SELECT 'abc' ~~ 'a%';  --> returns true

 SELECT 'abc' LIKE 'a%';  --> returns true

 SELECT 'abc' LIKE 'A%';  --> returns false

 SELECT 'abc' LIKE 'a%';         --> returns true

 SELECT 'abc' NOT LIKE 'a%';  --> retruns false

 SELECT 'abc' ~~ 'a%';          --> returns true

 SELECT 'abc' !~~ 'a%';         --> returns false

  (2) SIMILAR TO 연산자 (the SIMILAR TO Operator)

 SELECT 'abc' SIMILAR TO 'abc'; -- true

 SELECT 'abc' SIMILAR TO 'a';    -- false

 SELECT 'abc' SIMILAR TO 'a__'; -- true

 SELECT 'abc' SIMILAR TO '_b_'; -- true

 SELECT 'abc' SIMILAR TO '_bc'; -- true

 SELECT 'abc' SIMILAR TO 'a%';   -- true

 SELECT 'abc' SIMILAR TO '%b';   -- false

 SELECT 'abc' SIMILAR TO '%b%'; -- true

-- | denotes alternation (either of two alternatives).

-- Parentheses () can be used to group items into a single logical item.

 SELECT 'abc' SIMILAR TO '%(b|d)%'; -- true

 SELECT 'abc' SIMILAR TO '%(d|e)%'; -- false

-- {m} denotes repetition of the previous item exactly m times.

 SELECT 'abbbc' SIMILAR TO '%b{3}%'; -- true

 SELECT 'abbbc' SIMILAR TO '%b{4}%'; -- false

-- {m,} denotes repetition of the previous item m or more times.

 SELECT 'abbbc' SIMILAR TO '%b{2,}%'; -- true

 SELECT 'abbbc' SIMILAR TO '%b{4,}%'; -- false

-- {m,n} denotes repetition of the previous item at least m and not more than n times.

 SELECT 'abbbc' SIMILAR TO '%b{2,3}%'; -- true

 SELECT substring('foobar' from '%#"o_b#"%' for '#'); -- oob

--  It returns null if there is no match.

 SELECT substring('foobar' from '#"o_b#"%' for '#');  -- NULL

 SELECT substring('foobar' from 'o...r'); -- oobar 

 SELECT substring('foobar' from 'o(...)r'); -- oba

  (3) 정규 표현식 (POSIX-Style Regular Expressions)

-- Regular Expressions Matching Operators: ~, ~*, !~, !~*

-- ~ operator: Matches regular expression, case sensitive

 SELECT 'thomas' ~ '.*thomas.*'; -- true

 SELECT 'thomas' ~ '.*Thomas.*'; -- False

-- ~* operator: Matches regular expression, case insensitive

 SELECT 'thomas' ~* '.*Thomas.*'; -- true

-- !~ operator: Does not match regular expression, case sensitive

 SELECT 'thomas' !~ '.*thomas.*'; -- False

 SELECT 'thomas' !~ '.*Thomas.*'; -- True

-- !~* operator: Does not match regular expression, case insensitive

 SELECT 'thomas' !~* '.*Thomas.*'; -- false

 SELECT 'thomas' !~ '.*Thomas.*'; -- true

 SELECT 'thomas' !~* '.*steve.*'; -- true

  (1) PostgreSQL 문자열형 포함한 테이블 생성 및 문자열 입력

-- Create a table for an example

DROP TABLE IF EXISTS char_type_test;

CREATE TABLE char_type_test (

id SERIAL PRIMARY KEY 

, col_1 CHAR(2)

, col_2 VARCHAR(10)

, col_3 TEXT

);

-- PostgreSQL issued an error

-- SQL Error [22001]: ERROR: value too long for type character(2)

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hollo'

, 'PostgreSQL WORLD'

, 'I love PostgreSQL all the time'

);

-- PostgreSQL issued an error

-- SQL Error [22001]: ERROR: value too long for type character varying(10)

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hi'

, 'PostgreSQL WORLD'

, 'I love PostgreSQL all the time'

);

-- No error

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hi'

, 'PostgreSQL'

, 'I love PostgreSQL all the time')

, (

'Hi'

, 'Greenplum'

, 'I love Greenplum too'

);

SELECT * FROM char_type_test ORDER BY id;

  (2) PostgreSQL 문자열 연산자 (Operator)와 함수 (Functions)

SELECT

*

, col_1 || ', ' || col_2 AS db_name

, id || ' : ' || col_2 AS db_id

FROM char_type_test;

SELECT 

*

, CONCAT(col_1, ', ', col_2) AS db_name_2

, CONCAT(id, ' : ', col_2) AS db_id_2

FROM char_type_test;

-- Concatenate using || with a NULL value returns NULL

SELECT col_1 || NULL AS result_string FROM char_type_test;

-- Concat function ignores NULL arguments.

SELECT CONCAT(col_1, NULL) AS result_string_2 FROM char_type_test;

-- (3) CONCAT_WS : CONCAT With Separator

-- : CONCAT_WS(separator, str_1, str_2, ...);

SELECT 

*

, CONCAT_WS(', ', col_1, col_2) AS db_name_3

, CONCAT_WS(' : ', id, col_2) AS db_id_3

FROM char_type_test;

-- Useful when there are lots of values to concatenate

SELECT CONCAT('a', ': ', 'b', ': ', 'c', ': ', 'd', ': ', 'e', ': ', 'f') AS concat_result_1;

SELECT CONCAT_WS(': ', 'a', 'b', 'c', 'd', 'e', 'f') AS concat_ws_result_2;

SELECT

col_3

, BIT_LENGTH(col_3) AS bit_len_col_3

, OCTET_LENGTH(col_3) AS cotet_len_col_3

, LENGTH(col_3) AS char_len_col_3

FROM char_type_test;

 SELECT OCTET_LENGTH('abc123');

 ---------

 result

 6

 SELECT LENGTH('abc123');

 ----------

 result 

 6

 SELECT OCTET_LENGTH('안녕하세요');

 ----------

 result

 15

 SELECT LENGTH('안녕하세요');

 ----------

 result 

 5

-- lower(string): convert a string to lower case

-- upper(string): convert a string to upper case

-- initcap(string): convert words in a string to title case

SELECT 

col_2

, LOWER(col_2) AS lower_col_2 

, UPPER(col_2) AS upper_col_2 

, INITCAP(col_2) AS initcap_col_2

FROM char_type_test;

-- overlay(string placing string from int [for int])

-- replace(string, from, to)

SELECT 

col_3

, OVERLAY(col_3 PLACING 'Opensource DB ' FROM 8 FOR 10) AS overlayed_col_3

, REPLACE(col_3, 'I', 'You') AS replaced_col_3

FROM char_type_test;

-- reverse(string)

-- repeat(string, int)

SELECT 

col_2

, REVERSE(col_2) AS reversed_col_2

, REPEAT(col_2, 3) AS repeated_col_2

FROM char_type_test;

----------------------------------

-- Location of specified substring

----------------------------------

SELECT 

col_3

, POSITION('g' in col_3) AS g_position

, POSITION('G' in col_3) AS "G_position"

, POSITION('love' in col_3) AS love_position

FROM char_type_test;

-- substring(string [from int] [for int])

SELECT 

*

, SUBSTR(col_2, 1, 3) AS substr_col_2_3

, SUBSTR(col_2, 1, 5) AS substr_col_2_5

FROM char_type_test;

--------------

-- Split a string on a specified delimeter

--------------

SELECT 

'2020-07-04' AS yyyymmdd

, SPLIT_PART('2020-07-04', '-', 1) AS year

, SPLIT_PART('2020-07-04', '-', 2) AS month

, SPLIT_PART('2020-07-04', '-', 3) AS day;

--  trim([leading | trailing | both] [character] from string)

SELECT 

'xTomxx' AS original_str

, TRIM(LEADING 'x' FROM 'xTomxx') AS trim_leading

, TRIM(TRAILING 'x' FROM 'xTomxx') AS trim_trailing

, TRIM(BOTH 'x' FROM 'xTomxx') AS trim_both

, LTRIM('xTomxx', 'x') AS l_trim

, RTRIM('xTomxx', 'x') AS r_trim;

--------------------------------

-- First n character in a string

--------------------------------

SELECT 

col_2

, LEFT(col_2, 3) AS first_n_from_left

, RIGHT(col_2, 3) AS first_n_from_right

FROM char_type_test;

-----------------------------------

-- PAD on the left or rght a string 

-----------------------------------

SELECT 

id

, LPAD(id::char, 5, '0') AS lpad_id

, RPAD(id::char, 5, '0') AS rpad_id

FROM char_type_test;

import numpy as np

import pandas as pd

# Make a DataFrame with missing values

df = pd.DataFrame({'a': [1, 2, 3, np.nan, 5], 

                   'b': [30, 20, np.nan, 35, 32], 

                   'c': [0.2, np.nan, 0.5, 0.3, 0.4], 

                   'd': ['c1', np.nan, 'c3', 'c4', 'c5'], 

                   'e': [10, 11, np.nan, 13, 15]})

df

missing_fill_val = {'a': 0, 

                    'b': df.b.mean(), 

                    'c': df.c.median(), 

                    'd': 'Unknown', 

                    'e': df.e.interpolate()}

print(missing_fill_val)

{'a': 0, 'c': 0.35, 'b': 29.25, 'e': 0    10.0
1    11.0
2    12.0
3    13.0
4    15.0
Name: e, dtype: float64, 'd': 'Unknown'}

df2 = df.fillna(missing_fill_val)

df2

df.fillna(missing_fill_val, inplace=True)

  (1) 산술 연산자 (Arithmetic Operators)

DROP TABLE IF EXISTS test;

CREATE TABLE test (

a int 

, b int

);

INSERT INTO test VALUES (2, 4), (3, 5), (4, 7);

SELECT * FROM test;

-------------

a      b

-------------

2 4

3 5

4 7

-- 나눗셈 결과의 소수점 자리 무시

SELECT b / a AS div FROM test;

----------

div

----------

2

1

1

-- 분자를 Numeric으로 형 변환하면 나눗셈 결과 소수점 자리 나옴

SELECT b::numeric / a AS div_1 FROM test;

----------

div_1

----------

2.0000000000000000

1.6666666666666667

1.7500000000000000

-- 분모를 Numeric으로 형 변환하면 나눗셈 결과 소수점 자리 나옴

SELECT b / a::numeric AS div_2 FROM test;

----------

div_2

----------

2.0000000000000000

1.6666666666666667

1.7500000000000000

  (2) 비교 연산자 (Comparison Operators)

-----------------------

-- Comparison Operators

-----------------------

DROP TABLE IF EXISTS comparison;

CREATE TABLE comparison (

a int 

, b int

);

INSERT INTO comparison VALUES (1, 1), (1, 2), (2, 1), (2, 2);

SELECT * FROM comparison;

----------

a       b

----------

1 1

1 2

2 1

2 2

-- equal to

SELECT * FROM comparison WHERE a = b;

----------

a       b

----------

1 1

2 2

-- not equal

SELECT * FROM comparison WHERE a != b;

SELECT * FROM comparison WHERE a <> b;

----------

a       b

----------

1 2

2 1

-- greater than

SELECT * FROM comparison WHERE a > b;

----------

a       b

----------

2 1

-- less than

SELECT * FROM comparison WHERE a < b;

----------

a       b

----------

1 2

-- greater than or equal to

SELECT * FROM comparison WHERE a >= b;

----------

a       b

----------

1 1

2 1

2 2

-- less than or equal to

SELECT * FROM comparison WHERE a <= b;

----------

a       b

----------

1 1

1 2

2 2

-- SQL Error [42883]: ERROR: operator does not exist: integer =! integer

SELECT * FROM comparison WHERE a =! b;

-- SQL Error [42883]: ERROR: operator does not exist: integer >< integer

SELECT * FROM comparison WHERE a >< b;

-- SQL Error [42601]: ERROR: syntax error at or near "=>"

SELECT * FROM comparison WHERE a => b;

-- SQL Error [42883]: ERROR: operator does not exist: integer =< integer

SELECT * FROM comparison WHERE a =< b;

  (3) 논리 연산자 (Logical Operators)

---------------------

-- Logical Operators

---------------------

INSERT INTO comparison VALUES (NULL, 5), (NULL, NULL);

SELECT * FROM comparison;

----------

a      b

----------

1 1

1 2

2 1

2 2

[NULL] 5

[NULL] [NULL]

-- AND

SELECT * FROM comparison WHERE a = 1 AND b = 2;

----------

a      b

----------

1 2

-- OR

SELECT * FROM comparison WHERE a = 1 OR b = 5;

----------

a      b

----------

1 1

1 2

[NULL] 5

-- NOT

SELECT * FROM comparison WHERE NOT (a = 1);

2 1

2 2

-- NOT IN

SELECT * FROM comparison WHERE a NOT IN (1);

----------

a      b

----------

2 1

2 2

-- IS NOT NULL

SELECT * FROM comparison WHERE b IS NOT NULL;

----------

a      b

----------

1 1

1 2

2 1

2 2

[NULL] 5

-- IS NOT NULL AND IS NOT NULL

SELECT * FROM comparison WHERE a IS NOT NULL AND b IS NOT NULL;

----------

a      b

----------

1 1

1 2

2 1

2 2

-- NOT BETWEEN

SELECT * FROM comparison WHERE b BETWEEN 1 AND 2;

----------

a      b

----------

1 1

1 2

2 1

2 2

SELECT * FROM comparison WHERE b NOT BETWEEN 1 AND 2;

----------

a      b

----------

[NULL] 5

  (1) YAML 파일을 파싱해서 Python 객체로 읽어오기: yaml.load()

import yaml

with open('vegetables.yml') as f:

    vegetables = yaml.load(f, Loader=yaml.FullLoader)

    print(vegetables)

{'Vegetables': ['Pepper', 'Tomato', 'Garlic']}

import yaml

with open('deployment-definition.yml') as f:

    deployment_def = yaml.load(f, Loader=yaml.FullLoader)

deployment_def

{'apiVersion': 'apps/v1',
 'kind': 'Deployment',
 'metadata': {'name': 'frontend',
  'labels': {'app': 'mywebsite', 'tier': 'frontend'}},
 'spec': {'replicas': 3,
  'template': {'metadata': {'name': 'myapp-pod', 'labels': {'app': 'myapp'}},
   'spec': {'containers': [{'name': 'nginx', 'image': 'nginx'}]}},
  'selector': {'matchLabels': {'app': 'myapp'}}}}

  (2) 여러개의 YAML 문서들을 파싱하여 읽어오기 : yaml.load_all()

import yaml

with open('fruit-vegetable.yml') as f:

    fruits_vegetables = yaml.load_all(f, Loader=yaml.FullLoader)

    for fruit_vegetable in fruits_vegetables:

        print(fruit_vegetable)

{'Fruits': ['Blueberry', 'Apple', 'Orange']}
{'Vegetables': ['Pepper', 'Tomato', 'Garlic']}

  (3) 읽어온 YAML 파일을 정렬하기

import yaml

with open('cars.yml') as f:

    cars_original = yaml.load(f, Loader=yaml.FullLoader)

    print(cars_original)

    print("---------------------")

    # sorting by Key

    cars_sorted = yaml.dump(cars_original, sort_keys=True)

    print(cars_sorted)

{'hyundai': 45000, 'tesla': 65000, 'chevrolet': 42000, 'audi': 51000, 'mercedesbenz': 80000}
---------------------
audi: 51000
chevrolet: 42000
hyundai: 45000
mercedesbenz: 80000
tesla: 65000

import yaml

with open('cars.yml') as f:

    cars_original = yaml.load(f, Loader=yaml.FullLoader)

    print(cars_original)

    print("---------------------")

    # sort by key in ascending order

    for key, value in sorted(cars_original.items()):

        print(key, ':', value

{'hyundai': 45000, 'tesla': 65000, 'chevrolet': 42000, 'audi': 51000, 'mercedesbenz': 80000}
---------------------
audi : 51000
chevrolet : 42000
hyundai : 45000
mercedesbenz : 80000
tesla : 65000

import yaml

with open('cars.yml') as f:

    cars_original = yaml.load(f, Loader=yaml.FullLoader)

    print(cars_original)

    print("---------------------")

    # sorting by key in reverse order

    for key, value in sorted(cars_original.items(), reverse=True):

        print(key, ':', value)

tesla : 65000
mercedesbenz : 80000
hyundai : 45000
chevrolet : 42000
audi : 51000

import yaml

with open('cars.yml') as f:

    cars_original = yaml.load(f, Loader=yaml.FullLoader)

    print(cars_original)

    print("---------------------")

    # sorting by value in ascending order

    for key, value in sorted(cars_original.items(), key = lambda item: item[1]):

        print(key, ':', value)

{'hyundai': 45000, 'tesla': 65000, 'chevrolet': 42000, 'audi': 51000, 'mercedesbenz': 80000}
---------------------
chevrolet : 42000
hyundai : 45000
audi : 51000
tesla : 65000
mercedesbenz : 80000

  (4) Python 객체를 YAML stream으로 직렬화 하기: yaml.dump()

import yaml

fruits = {'fruits': ['blueberry', 'apple', 'orange']}

# serialize a Python object into YAML stream

fruits_serialized_yaml = yaml.dump(fruits)

print(fruits_serialized_yaml)

fruits:
- blueberry
- apple
- orange

  (5) Python 객체를 YAML 파일로 쓰기: with open('w') as f: yaml.dump()

import yaml

fruits = {'fruits': ['blueberry', 'apple', 'orange']}

with open('fruits.yaml', 'w') as f:

    yaml.dump(fruits, f)

import pandas as pd