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

# 신경망 모델 학습 진행 경과 확인 python code

print('Epoch:', '%04d' % (epoch + 1),

         'Avg. cost =', '{:.3f}'.format(total_cost / total_batch))

 (1) Format 을 갖춘 문자열을 만들어주는 연산자 (String Formatting Operator): %

>>> "I am %s and %d years old" %('Mr.Hong', 40)

'I am Mr.Hong and 40 years old'

%와 함께 사용하는 형식 기호

(Format Symbol with %) 

변환 (Conversion)

 %c

 문자 (character)

 %s

 str() 메소드를 사용해서 문자열로 변환한 후 formatting

%i 

 부호가 있는 십진법 정수 (signed decimal integer)

 %d

 부호가 있는 십진법 정수 (signed decimal integer)

 %u

 부호가 없는 십진법 정수 (unsigned decimal integer)

 %o

 8진법 정수 (octal integer)

 %x

 소문자의 16진법 정수 (hexadecimal integer, lowercase letters)

 %X

 대문자의 16진법 정수 (hexadecimal integer, UPPERcase letters)

 %e

 소문자 'e'를 사용한 지수 표기 (exponential notation with lowercase 'e')

 %E

 대문자 'E'를 사용한 지수 표기 (exponential notation with UPPERcase 'E')

 %f

 부동소수형 실수 (floating point real number)

 %g

 %f(부동소수형 실수)와 %e (소문자 'e' 지수 표기) 의 단축형 표기

 %G

 %f(부동소수형 실수)와 %E(대문자 'E' 지수 표기)의 단축형 표기

>>> "I am %s and %d years old" %('Mr.Hong', '40')  # '40' is not a number, but a string

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

TypeError: %d format: a number is required, not str

>>> "%04d" % (5)

'0005'

>>> "%04d" % (55)

'0055'

>>> "%04d" % (555)

'0555'

>>> "%04d" % (5555)

'5555'

>>> "%04d" % (55555)

'55555'

>>> "%.3f" % (5)

'5.000'

>>> "%.3f" % (0.5)

'0.500'

>>> "%.3f" % (0.55)

'0.550'

>>> "%.3f" % (0.555)

'0.555'

>>> "%.3f" % (0.5555)

'0.555'

>>> print('Epoch:', '%04d' % (100),

...       'Avg. cost =', '{:.3f}'.format(10 / 1000))

Epoch: 0100 Avg. cost = 0.010

In [1]: import pandas as pd

In [2]: df = pd.DataFrame({'id': ['a', 'b', 'c', 'd'],

   ...: 'val': [1.045, 5.200, 0.051, 8.912]})

In [3]: df

Out[3]:

  id    val

0  a  1.045

1  b  5.200

2  c  0.051

3  d  8.912

In [4]: formater = lambda x:" %.2f" %(x)

In [5]: df['val'] = df['val'].map(formater)

In [6]: df

Out[6]:

  id    val

0  a   1.04

1  b   5.20

2  c   0.05

3  d   8.91

 (2) Format 을 갖춘 문자열을 만들어주는 메소드 (String Formatting Method): format()

>>> "I am {name} and {age} years old".format(name='Mr.Hong', age=40)

'I am Mr.Hong and 40 years old'

>>> "I am {name} and {age} years old".format(age=40, name='Mr.Hong')

'I am Mr.Hong and 40 years old'

>>> "I am {0} and {1} years old".format('Mr.Hong', 40)

'I am Mr.Hong and 40 years old'

>>> "I am {0} and {1} years old".format(40, 'Mr.Hong') # sequence matters

'I am 40 and Mr.Hong years old'

[참고]  메소드 (Method)

내장 함수(Built-in Function)와는 달리 문자열 자료형과 같이 특정 자료형이 가지고 있는 함수를 메소드(Method) 라고 합니다. 메소드는 객체 지향 프로그래밍의 기능에 대응하는 파이썬 용어입니다. 함수와 거의 동일한 의미이지만 메소드는 클래스의 멤버라는 점이 다릅니다. 

len() : 문자열 길이

# len() : Returns the length of the string

>>> a = 'I Love Python'

>>> len(a)

13

 min(), max() : 문자열 내 문자, 혹은 숫자의 최소값, 최대값 (알파벳 순서, 숫자 순서 기반)

# max(str), min(str) : Returns the max, min alphabetical character from the string str

>>> d = 'abc'

>>> f = '123'

>>> 

>>> min(d)

'a'

>>> max(d)

'c'

>>> 

>>> min(f)

'1'

>>> max(f)

'3'

 count() :  문자열 안에서 매개변수로 입력한 문자열이 몇 개 들어있는지 개수를 셈 

                (begin, end 위치 설정 가능)

# count() : Counts how many times str occurs in string

>>> a = 'I Love Python'

>>> a.count('o')

2

>>> 

>>> a = 'I Love Python'

>>> a.count('o', 7, len(a)) # count(string, begin, end)

1

>>> 

>>> a.count('k') # there is no 'k' character in 'a' string

0

  startswith() : 문자열이 매개변수로 입력한 문자열로 시작하면 True, 그렇지 않으면 False 반환

# startswith(): Determines if string or a substring of string

>>> a = 'I Love Python'

>>> a.startswith('I')

True

>>> a.startswith('I Lo')

True

>>> a.startswith('U')

False

  endswith() : 문자열이 매개변수로 입력한 문자열로 끝나면 True, 그렇지 않으면 False 반환

# endswith(): Determines if string or a substring of string

>>> a = 'I Love Python'

>>> a.endswith('Python')

True

>>> a.endswith('Pycham')

False

 find() : 문자열에 매개변수로 입력한 문자열이 있는지를 앞에서 부터 찾아 index 반환, 없으면 '-1' 반환

# find() : Search forwards, Determine if str occurs in string and return the index

>>> a = 'I Love Python'

>>> a.find('o')

3

>>> a.find('k') # if there is no string, then '-1'

-1

  rfind() : 문자열에 매개변수로 입력한 문자열이 있는지를 뒤에서 부터 찾아 index 반환, 없으면 '-1' 반환

# rfind() : Same as find(), but search backwards in string

>>> a = 'I Love Python'

>>> a.rfind('o')

11

 index() :  find()와 기능 동일하나, 매개변수로 입력한 문자열이 없으면 ValueError 발생

# index(): Same as find(), but raises an exception if str not found

>>> a = 'I Love Python'

>>> a.index('o')

3

>>> a.index('k') # ValueError: substring not found

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

ValueError: substring not found

 rindex() : index()와 기능 동일하나, 뒤에서 부터 매개변수의 문자열이 있는지를 찾음

# rindex(): Same as index(), but search backwards in string

>>> a = 'I Love Python'

>>> a.rindex('o')

11

>>> a.rindex('k') # ValueError: substring not found

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

ValueError: substring not found

  isalnum() : 문자열이 알파벳과 숫자로만 이루어졌으면 True, 그렇지 않으면 False

>>> a = 'I Love Python'

>>> d = 'abc'

>>> e = '123abc'

>>> f = '123'

>>> 

# isalnum() : Returns true if string has at least 1 character and 

#                  all characters are alphanumeric and false otherwise

>>> a.isalnum() # False

False

>>> d.isalnum() # True

True

>>> e.isalnum() # True

True

>>> f.isalnum() # True

True

  isalpha() : 문자열이 알파벳(영어, 한글 등)으로만 이루어졌으면 True, 그렇지 않으면 False

>>> a = 'I Love Python'

>>> d = 'abc'

>>> e = '123abc'

>>> f = '123'

>>> 

# isalpha() : Returns true if string has at least 1 character 

#                 and all characters are alphabetic and false otherwise

>>> a.isalpha() # False (there is space between characters)

False

>>> d.isalpha() # True

True

>>> e.isalpha() # False

False

>>> f.isalpha() # False

False

  isdigit() : 문자열이 숫자만 포함하고 있으면 True, 그렇지 않으면 False, isnumeric()과 동일

>>> a = 'I Love Python'

>>> d = 'abc'

>>> e = '123abc'

>>> f = '123'

>>> 

# isdigit() : Returns true if string contains only digits and false otherwise

>>> a.isdigit() # False 

False

>>> d.isdigit() # False

False

>>> e.isdigit() # False

False

>>> f.isdigit() # True

True

  isnumeric() : 문자열이 숫자로만 이루어져 있으면 True, 그렇지 않으면 False, isdigit()과 동일

>>> a = 'I Love Python'

>>> d = 'abc'

>>> e = '123abc'

>>> f = '123'

>>> 

# isnumeric(): Returns true if a unicode string contains only numeric characters

>>> a.isnumeric() # False

False

>>> d.isnumeric() # False

False

>>> e.isnumeric() # False

False

>>> f.isnumeric() # True

True

  isdecimal() : 문자열이 10진수 문자이면 True, 그렇지 않으면 False

>> a = 'I Love Python'

>>> d = 'abc'

>>> e = '123abc'

>>> f = '123'

>>> 

# isdecimal(): Returns true if a unicode string contains only decimal characters

>>> a.isdecimal() # False

False

>>> d.isdecimal() # False

False

>>> e.isdecimal() # False

False

>>> f.isdecimal() # True 

True

  islower() : 문자열이 모두 소문자로만 되어있으면 True, 그렇지 않으면 False

>>> a = 'I Love Python'

>>> g = 'i love python'

>>> h = 'I LOVE PYTHON'

>>> 

# islower(): Returns true if string has at least 1 cased character 

#            and all cased characters are in lowercase and false otherwise

>>> a.islower() # False

False

>>> g.islower() # True

True

>>> h.islower() # False

False

  isupper() : 문자열이 모두 대문자로만 되어있으면 True, 그렇지 않으면 False

>>> a = 'I Love Python'

>>> g = 'i love python'

>>> h = 'I LOVE PYTHON'

>>> 

# isupper(): Returns true if string has at least one cased character 

#           and all cased characters are in uppercase and false otherwise

>>> a.isupper() # False

False

>>> g.isupper() # False

False

>>> h.isupper() # True

True

  lower() : 문자열 내 모든 대문자를 모두 소문자(a lowercase letter)로 변환

>>> a = 'I Love Python'

>>> 

# lower(): Converts all uppercase letters in string to lowercase

>>> a.lower()

'i love python'

 upper() :  문자열 내 모든 소문자를 모두 대문자(a uppercase letter)로 변환

>>> a = 'I Love Python'

>>> 

# upper(): Converts lowercase letters in string to uppercase

>>> a.upper()

'I LOVE PYTHON'

 swapcase() : 문자열 내 소문자는 대문자로 변환, 대문자는 소문자로 변환

# swapcase(): Inverts case for all letters in string

>>> a = 'I Love Python'

>>> a.swapcase() # 'i lOVE pYTHON'

'i lOVE pYTHON'

>>> 

>>> g = 'i love python'

>>> g.swapcase() # 'I LOVE PYTHON' (same as upper())

'I LOVE PYTHON'

>>> 

>>> h = 'I LOVE PYTHON'

>>> h.swapcase() # 'i love python' (same as lower())

'i love python'

  istitle() : 문자열이 제목 형식에 맞게 대문자로 시작하고 이후는 소문자이면 True, 그렇지 않으면 False

>>> a = 'I Love Python'

>>> g = 'i love python'

>>> h = 'I LOVE PYTHON'

>>> 

# istitle(): Returns true if string is properly "titlecased" and false otherwise

>>> a.istitle() # True

True

>>> g.istitle() # False

False

>>> h.istitle() # False

False

 title() : 문자열을 제목 형식(titlecased)에 맞게 시작은 대문자로, 나머지는 소문자로 변환 

>>> g = 'i love python'

>>> h = 'I LOVE PYTHON'

>>> 

# title(): Returns "titlecased" version of string, that is, 

#          all words begin with uppercase and the rest are lowercase

>>> g.title() # 'I Love Python'

'I Love Python'

>>> h.title() # 'I Love Python'

'I Love Python'

  capitalize)=() : 문자열 내 첫번째 문자를 대문자로 변환하고, 나머지는 모두 소문자로 변환

>>> a = 'I Love Python'

>>> g = 'i love python'

>>> h = 'I LOVE PYTHON'

>>> 

# capitalize(): Capitalizes first letter of string

>>> a.capitalize() # 'I love python'

'I love python'

>>> g.capitalize() # 'I love python'

'I love python'

>>> h.capitalize() # 'I love python'

'I love python'

 lstrip() : 문자열의 왼쪽에 있는 공백을 제거

# lstrip() : Removes all leading whitespace in string

>>> b = '      I Love Python'

>>> b.lstrip()

'I Love Python'

  rstrip() : 문자열의 오른쪽에 있는 공백을 제거

# rstrip() : Removes all trailing whitespace of string

>>> c = 'I Love Python      '

>>> c.rstrip()

'I Love Python'

  strip() : 문자열의 양쪽에 있는 공백을 제거

# strip() : Performs both lstrip() and rstrip() on string

>>> '     I Love Python     '.strip()

'I Love Python'

  isspace() : 문자열이 단지 공백(whitespace)으로만 되어있을 경우 True, 그렇지 않으면 False

# isspace(): Returns true if string contains only whitespace characters and false otherwise

>>> i = '     '

>>> j = '      I Love Python'

>>> 

>>> i.isspace() # True

True

>>> j.isspace() # False

False

  center(width) : 총 길이가 매개변수로 받는 문자열폭(width)만큼 되도록 공백을 추가하여 중앙 정렬

# center(): Returns a space-padded string with the original string 

#                centered to a total of width columns

>>> a = 'I Love Python'

>>> a.center(21)

'    I Love Python    '

  split() : 문자열을 구분자(delimiter, separator) 기준에 따라 나누기

# split(): Splits string according to delimiter str (space if not provided) 

#    and returns list of substrings; split into at most num substrings if given

>>> x = 'haha, hoho, hihi'

>>> x.split(sep=',') # as a list ['haha', ' hoho', ' hihi']

['haha', ' hoho', ' hihi']

>>> ha, ho, hi = x.split(sep=',')

>>> ha

'haha'

>>> ho

' hoho'

>>> hi

' hihi'

>>> a = 'I Love Python'

>>> a.split(' ') # without arg 'sep='

['I', 'Love', 'Python']

>>> a.split() # default delimiter is space if not provided

['I', 'Love', 'Python'] 

  splitlines() : 여러개의 줄로 이루어진 문자열을 줄 별로 구분하여 리스트 생성

# splitlines(): returns a list with all the lines in string, 

#     optionally including the line breaks (if num is supplied and is true)

>>> y = 'haha, \nhoho, \nhihi'

>>> y

'haha, \nhoho, \nhihi'

>>> y.splitlines() # ['haha, ', 'hoho, ', 'hihi']

['haha, ', 'hoho, ', 'hihi']

 replace(old, new, max) : old 문자열을 new 문자열로 교체.  단, max 매개변수 있으면, max 개수 만큼만 교체하고 이후는 무시

# replace(old, new): Replaces all occurrences of old in string with new 

#        or at most max occurrences if max given

>>> a = 'I Love Python'

>>> a.replace('Python', 'R')

'I Love R'

>>> 

>>> 

>>> a_2 = 'I Love Python, Python, Python, Python, Python~!!!'

>>> a_2.replace('Python', 'R', 3) # str.replace(old, new, max)

'I Love R, R, R, Python, Python~!!!'

 join() :  여러개의 문자열을 구분자(separator) 문자열을 사이에 추가하여 붙이기

# join(): Merges (concatenates) the string representations of elements 

#         in sequence seq into a string, with separator string

>>> mylist = ['I', 'Love', 'Python']

>>> print(mylist)

['I', 'Love', 'Python']

>>> 

>>> mystring = '_'.join(mylist)

>>> print(mystring) # 'I_Love_Python'

I_Love_Python

# To concatenate item in list to strings with join() method

>>> mylist_num = [1, 2, 3, 4, 5]

>>> print(mylist_num) # [1, 2, 3, 4, 5]

[1, 2, 3, 4, 5]

>>> 

>>> mylist_str = ''.join(map(str, mylist_num))

>>> print(mylist_str) # 12345

12345

>>> 

>>> '_'.join(map(str, mylist_num)) # '1_2_3_4_5'

'1_2_3_4_5'

 zfill(width) : 문자열을 매개변수 width만큼 길이로 만들되, 추가로 필요한 자리수만큼 '0'을 채움

# zfill(width): Returns original string leftpadded with zeros to a total of width characters; 

#      intended for numbers, zfill() retains any sign given (less one zero)

>>> f = '123'

>>> f.zfill(10)

'0000000123' 

 ljust(width[, fillchar]) : 문자열을 매개변수 width만큼 길이로 만들되, 왼쪽은 원본 문자열로 채우고, 

오른쪽에 추가로 필요한 자리수만큼 매개변수 fillchar 문자열로 채움

# ljust(): Returns a space-padded string with the original string left-justified 

#          to a total of width columns

# str.ljust(width[, fillchar])

>>> a = 'I Love Python'

>>> a.ljust(20, 'R')

'I Love PythonRRRRRRR'

 rjust(width[, fillchar]) : 문자열을 매개변수 width만큼 길이로 만들되, 오른쪽은 원본 문자열로 채우고, 

 왼쪽에 추가로 필요한 자리수만큼 매개변수 fillchar 문자열로 채움

# rjust(): Returns a space-padded string with the original string right-justified 

#          to a total of width columns

>>> a.rjust(20, 'R')

'RRRRRRRI Love Python'

>>> a.rjust(20, ' ')

'       I Love Python'

 구분

버전/내용

[참고] terminal shell script 

 OS

 mac OS X 10.12.5

 Python

 3.6.1

# python 버전 확인

$ python3 --version

Python 3.6.1

 tensorflow

 1.2.1

# tensorflow 버전 확인

$ python3 -c 'import tensorflow as tf; print(tf.__version__)'

1.2.1

 CPU or GPU

 CPU

 설치/사용 환경

virtual env.

(가상환경)

# virtualenv (가상환경) 활성화 : activate

$ source /Users/Desktop/tensorflow/bin/activate

# 가상환경(virtual env.)에서 Jupyter Notebook 열기

$ jupyter notebook

# importing tensorflow

import tensorflow as tf

# mnist dataset loading

from tensorflow.examples.tutorials.mnist import input_data

mnist = input_data.read_data_sets("./mnist/data/", one_hot=True)

# solution for 'SSL: CERTIFICATE_VERIFY_FAILED' error (=> type in below bash script at terminal)

$ /Applications/Python\ 3.6/Install\ Certificates.command 

-- pip install --upgrade certifi

Requirement already up-to-date: certifi in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages

 -- removing any existing file or link

 -- creating symlink to certifi certificate bundle

 -- setting permissions

 -- update complete

# virtualenv (가상환경) 활성화 : activate

$ source /Users/Desktop/tensorflow/bin/activate

# 가상환경(virtual env.)에서 Jupyter Notebook 열기

$ jupyter notebook 

# importing tensorflow

import tensorflow as tf

# mnist dataset loading

from tensorflow.examples.tutorials.mnist import input_data

mnist = input_data.read_data_sets("./mnist/data/", one_hot=True) 

Extracting ./mnist/data/train-images-idx3-ubyte.gz

Extracting ./mnist/data/train-labels-idx1-ubyte.gz
Extracting ./mnist/data/t10k-images-idx3-ubyte.gz
Extracting ./mnist/data/t10k-labels-idx1-ubyte.gz

=========  Python 3 설치하기  ==========

혹시 컴퓨터에 Python 3 설치 안하신 분은 아래 참고해서 설치하시기 바랍니다. 

(Python 설치되어 있는 분이나 혹은 Python 2.7 사용하는 분은 pass)

1. Python 공식 사이트에 접속해서 Python 3.5 설치 파일 다운로드 하기

   https://www.python.org/downloads/

2. 다운로드한 Python 3.5 클릭해서 디폴트 세팅으로 설치하기 

(1) CPU 지원 환경에서 텐서플로우 (tensorflow) 설치 

 (2) 텐서플로우 가상환경 설치 완료 후 텐서플로우 사용 하기

$ source /Users/Desktop/tensorflow/bin/activate

===== 터미널에서 Jupyter Notebook 설치하기 =====  (이미 설치하신 분은 pass)

$ pip3 install jupyter

===== 터미널에서 Jupyter Notebook 실행하기 =====

$ jupyter notebook

(tensorflow) ~:$ deactivate

 (1) 수 (Numbers)

: 정수(integer), 실수(real number), 복소수 (complex number)

#%% (1) Numbers # (1-1) int : signed integers

In [1]: num_int = 100

In [2]: type(num_int)

Out[2]: int 

연산자 (operator)

 설명

예 

 +

 더하기 (addition)

 5 + 2 = 7

 -

 빼기 (subtraction)

5 - 2 = 3 

* 

 곱하기 (multiplication)

5 * 2 = 10

 /

나누기 (division) 

 5 / 2 = 2.5

 //

나눗셈의 몫 (floor division) 

 5 // 2 = 2

 %

나눗셈의 나머지 (modulus) 

5 % 2 = 1 

 **

지수 (exponent) 

5 ** 2 = 25

진법별로 변환해주는 함수 (function)

 접두사 (prefix)

예 

2진수(Binary number)로 변환: bin()

 0b

In [26]: bin(10)

Out[26]: '0b1010'

8진수(Octal number)로 변환: oct()

 0o

 In [27]: oct(10)

Out[27]: '0o12'

 16진수(Hexadecimal number)로 변환: hex()

 0x

 In [28]: hex(10)

Out[28]: '0xa'

# (1-2) float : floating point arithmetic

In [3]: num_float = 12.345

In [4]: type(num_float)

Out[4]: float 

In [5]: import math

In [6]: math.pi

Out[6]: 3.141592653589793

In [7]: math.e

Out[7]: 2.718281828459045 

# (1-4) complex : complex numbers

In [8]: num_complex = 3 + 0.45j

In [9]: type(num_complex)

Out[9]: complex

In [10]: num_complex.real

Out[10]: 3.0

In [11]: num_complex.imag

Out[11]: 0.45

In [12]: num_complex_2 = num_complex + (1 + 2j)

In [13]: num_complex_2

Out[13]: (4+2.45j)

# delete number objects

In [14]: del num_int, num_float, num_complex, num_complex_2

 (2) 문자열 (String) 

In [14]: str_1 = 'Hello World'

In [15]: str_1

Out[24]: 'Hello World'

In [16]: type(str_1)

Out[16]: str 

In [17]: mysql_Query = """SELECT var1, count(*) as cnt

    ...: FROM mytable

    ...: WHERE var1 = 'aaa'

    ...: GROUP BY var1

    ...: ORDER BY var1"""

In [18]: mysql_Query

Out[18]: "SELECT var1, count(*) as cnt\n FROM mytable\n WHERE var1 = 'aaa'\n GROUP BY var1\n ORDER BY var1"

Python 

R 

In [19]: a = 'Hello World'

In [20]: print(a)

Hello World

> a <- c('Hello World')

> a

[1] "Hello World"

# [] and [:] : slice operator with indexes starting at 0

# in the beginning of the string

In [21]: a[1]

Out[21]: 'e'

> substr(a, 2, 2)

In [22]: a[1:4]

Out[22]: 'ell'

[1] "ell"

In [23]: a[1:]

Out[23]: 'ello World'

[1] "ello World"

In [24]: a[10]

Out[24]: 'd'

# final character of a string : string[-1]

In [25]: a[-1]

Out[25]: 'd'

> substr(a, 11, 11)

[1] "d"

[1] "d"

# plus (+) sign: the string concatenation operator

In [26]: a + ' I Love You'

Out[26]: 'Hello World I Love You'

# asterisk(*) : the repetition operator

In [27]: a*2

Out[27]: 'Hello WorldHello World'

 
[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

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

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

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

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 

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

> ##======================================================

> ## adding multiple vertical lines at time-series plot using R ggplot2

> ##======================================================

> 

> # making time series data

> dt <- c("20170609100000", "20170609100100", "20170609100200", 

+         "20170609100300", "20170609100400", "20170609100500")

> val <- c(5.2, 3.4, 3.9, 6.3, 4.7, 5.6)

> 

> dt_val <- data.frame(dt, val)

> dt_val <- transform(dt_val, 

+                     dt = as.POSIXct(dt, 

+                                     format = '%Y%m%d%H%M%S', 

+                                     origin = "1970-01-01", 

+                                     tz = "UTC"))

> 

> dt_val

                   dt val

1 2017-06-09 10:00:00 5.2

2 2017-06-09 10:01:00 3.4

3 2017-06-09 10:02:00 3.9

4 2017-06-09 10:03:00 6.3

5 2017-06-09 10:04:00 4.7

6 2017-06-09 10:05:00 5.6

> # making time series plot

> library(ggplot2)

> 

> ggplot(dt_val, aes(x = dt, y = val)) +

+   geom_line(size=1, color = "blue") + 

+   ggtitle("Time-series plot")

> # To add vertical line at time series plot

> # Error in Ops.POSIXt((x - from[1]), diff(from)) : '/' not defined for "POSIXt" objects

> ggplot(dt_val, aes(x = dt, y = val)) +

+   geom_line(size = 1, color = "blue") +

+   geom_vline(xintercept = dt_val$dt[3], color =  "red", linetype = 2) +

+   ggtitle("Adding vertical line at time-series plot using geom_vline()")

Error in Ops.POSIXt((x - from[1]), diff(from)) : 

  '/' not defined for "POSIXt" objects

> # Use as.numeric() function at xintercept

> ggplot(dt_val, aes(x = dt, y = val)) +

+   geom_line(size = 1, color = "blue") +

+   geom_vline(xintercept = as.numeric(dt_val$dt[3]), color = "red", linetype = 2) + # as.numeric() transformation

+   ggtitle("Adding vertical line at time-series plot using geom_vline() and as.numeric() transformation")

> # adding "Multiple" vertical lines

> ggplot(dt_val, aes(x = dt, y = val)) +

+   geom_line(size = 1, color = "blue") +

+   geom_vline(xintercept = as.numeric(dt_val$dt[c(3,5)]), color = "red", linetype = 2) +

+   ggtitle("Adding multiple vertical lines at time-series plot using geom_vline()")

HOW_BACKTRACE environmental variable.

> # adding multiple vertical lines with another data frame

> dt_2 <- c("20170609100150", "20170609100430")

> val_2 <- c("yes", "yes")

> dt_val_2 <- data.frame(dt_2, val_2)

> dt_val_2 <- transform(dt_val_2, 

+                       dt_2 = as.POSIXct(dt_2, 

+                                         format = '%Y%m%d%H%M%S', 

+                                         origin = "1970-01-01", 

+                                         tz = "UTC"))

> dt_val_2

                 dt_2 val_2

1 2017-06-09 10:01:50   yes

2 2017-06-09 10:04:30   yes

> # time series plot with multiple vertical lines from another data frame

> ggplot(dt_val, aes(x = dt, y = val)) +

+   geom_line(size = 1, color = "blue") +

+   geom_vline(xintercept = as.numeric(dt_val_2$dt_2), color = "red", linetype = 2) +

+   ggtitle("Time-seires plot with multiple vertical line from another dataframe")

HOW_BACKTRACE environmental variable.

> # time series plot with multiple vertical lines from another data frame(2)

> ggplot(dt_val, aes(x = dt, y = val)) +

+   geom_line(size = 1, color = "blue") +

+   geom_vline(data = dt_val_2, xintercept = as.numeric(dt_val_2$dt_2), color = "red", linetype = 2) +

+   ggtitle("Time-series plot with multiple vertical lines from another dataframe 2")

# installing and loading of stringdist package

install.packages("stringdist")

library(stringdist)

> # to compute string edit distances

> # default method is 'osa', which is Optimal string alignment, (restricted Damerau-Levenshtein distance)

> stringdist(c("shawn henry"), c("shan hennyy", "show hurry"))

[1] 3 4

> # to compute a dist object of class dist

> # => can be used by clustering algorithms such as stats::hclust

> stringdistmatrix(c("foo","bar","boo","baz"))

  1 2 3

2 3    

3 1 2  

4 3 1 2

> 

> str_dist_mat <- as.matrix(stringdistmatrix(c("foo","bar","boo","baz")))

> str_dist_mat

  1 2 3 4

1 0 3 1 3

2 3 0 2 1

3 1 2 0 2

4 3 1 2 0

> # Approximate string matching

> # amatch returns the position of the closest match of x in table

> # by default, the OSA algorithm is used 

> # : Optimal string aligment (restricted Damerau-Levenshtein distance)

> amatch(c("hello"),c("hillu","hala","hallo", "hi"),maxDist=2)

[1] 3

                           Corpus 

 Text

Life

Love 

Learn 

 Document 1

 1

0

5 

 Document 2

 4

7

3 

 Document 3

 40

70 

30

## installing and loading proxy package

install.packages("proxy")

library(proxy)

> # making Term Document Matrix

> Doc_1 <- c(1, 0, 5)

> Doc_2 <- c(4, 7, 3)

> Doc_3 <- c(40, 70, 30)

> 

> Doc_corpus <- rbind(Doc_1, Doc_2, Doc_3) # matrix

> colnames(Doc_corpus) <- c("Life", "Love", "Learn")

> Doc_corpus

      Life Love Learn

Doc_1    1    0     5

Doc_2    4    7     3

Doc_3   40   70    30

> # calculating cosine distance between documents using proxy package

> cosine_dist_Doc_mat <- as.matrix(dist(Doc_corpus, method = "cosine"))

> cosine_dist_Doc_mat

          Doc_1     Doc_2     Doc_3

Doc_1 0.0000000 0.5668373 0.5668373

Doc_2 0.5668373 0.0000000 0.0000000

Doc_3 0.5668373 0.0000000 0.0000000

n <- ncol(cosine_dist_Doc_mat) # number of columns
col_nm <- colnames(cosine_dist_Doc_mat) # column names
r <- 1 # row position number
cosine_dist_long <- data.frame() # blank data.frame to store the results

for (i in 1:(n-1)) {
  for (j in (i+1):n){
    cosine_dist_long[r, 1] <- col_nm[i]
    cosine_dist_long[r, 2] <- col_nm[j]
    cosine_dist_long[r, 3] <- cosine_dist_Doc_mat[i, j]
    r <- r+1
  }
}

cosine_dist_long
# V1    V2        V3
# 1 Doc_1 Doc_2 0.5668373
# 2 Doc_1 Doc_3 0.5668373
# 3 Doc_2 Doc_3 0.0000000

> # cosine distance function

> cosine_Dist <- function(x){

+   as.dist(1 - x%*%t(x)/(sqrt(rowSums(x^2) %*% t(rowSums(x^2))))) 

+ }

> 

> cosine_Dist(Doc_corpus)

          Doc_1     Doc_2

Doc_2 0.5668373          

Doc_3 0.5668373 0.0000000