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

# import libraries

 import numpy as np

 import pandas as pd

 import os

# set directory with yours

 base_dir = 'D:/admin/Documents'

 excel_file = 'sales_per_region.xlsx'

 excel_dir = os.path.join(base_dir, excel_file)

# read a excel file and make it as a DataFrame

 df_from_excel = pd.read_excel(excel_dir, # write your directory here

                              sheet_name = 'Sheet1', 

                              header = 2, 

                              #names = ['region', 'sales_representative', 'sales_amount'], 

                              dtype = {'region': str, 

                                         'sales_representative': np.int64, 

                                         'sales_amount': float}, # dictionary type

                              index_col = 'id', 

                              na_values = 'NaN', 

                              thousands = ',', 

                              nrows = 10, 

                              comment = '#')

# check index

df_from_excel.index

Int64Index([1, 2, 3, 4, 5, 6, 7], dtype='int64', name='id')

# check data type

df_from_excel.dtypes

region                   object
sales_representative      int64
sales_amount            float64
dtype: object

# check 'region' column

df_from_excel['region']

id
1      seoul
2     inchon
3      busan
4    guangju
5      ulsan
6     sejong
7     jeunju
Name: region, dtype: object

 (1) pd.read_csv() 에서 thousands = ',' 옵션 설정하여 
      text, csv file을 불러올 때 천 단위 자리수 구분 없애고 불러오기

In [1]: import pandas as pd

In [2]: import os

In [3]: base_dir = 'D:/admin/Documents/'

In [4]: file_nm = 'per_capita_GDP_for_countries.txt'

In [5]: data_dir = os.path.join(base_dir, file_nm)

In [6]: per_capita_1 = pd.read_csv(data_dir, delimiter = '\t')

In [7]: per_capita_1.head(10)

Out[7]:

   Rank Country/Territory      USD

0     1        Luxembourg  114,234

1     2       Switzerland   82,950

2     2             Macau   82,388

3     3            Norway   81,695

4     4           Ireland   76,099

5     5           Iceland   74,278

6     6             Qatar   70,780

7     7         Singapore   64,041

8     8     United States   62,606

9     9           Denmark   60,692

In [8]: per_capita_2 = pd.read_csv(data_dir, delimiter = '\t', thousands = ',')

In [9]: per_capita_2.head(10)

Out[9]:

   Rank Country/Territory     USD

0     1        Luxembourg  114234

1     2       Switzerland   82950

2     2             Macau   82388

3     3            Norway   81695

4     4           Ireland   76099

5     5           Iceland   74278

6     6             Qatar   70780

7     7         Singapore   64041

8     8     United States   62606

9     9           Denmark   60692

 (2) DataFrame의 문자열 df.column.str.replace(',', '').astype('int64') 메소드를
     이용하여 데이터 형태 및 유형 변환하기

In [10]: per_capita_1.dtypes

Out[10]:

Rank int64

Country/Territory object

USD object
dtype: object 

In [11]: per_capita_1['USD_2'] = per_capita_1.USD.str.replace(',', '').astype('int64')

In [12]: per_capita_1.dtypes

Out[12]:

Rank int64

Country/Territory object

USD object

USD_2 int64

dtype: object

In [13]: per_capita_1.head(10)

Out[13]:

   Rank Country/Territory      USD   USD_2

0     1        Luxembourg  114,234  114234

1     2       Switzerland   82,950   82950

2     2             Macau   82,388   82388

3     3            Norway   81,695   81695

4     4           Ireland   76,099   76099

5     5           Iceland   74,278   74278

6     6             Qatar   70,780   70780

7     7         Singapore   64,041   64041

8     8     United States   62,606   62606

9     9           Denmark   60,692   60692

  (3) PostgreSQL, Greenplum DB 에서 천 단위 구분 기호 콤마(',') 없애는 방법

 SELECT replace(column_name, ',', '')::numeric

 FROM table_name

# make a sample DataFrame

import pandas as pd

score = pd.DataFrame({

        'date': ['2019-07-28']*4, 

        'name': ['kim', 'lee', 'choi', 'park'], 

        'age': [19, 20, 19, 20], 

        'math_score': [91, 95, 92, 70], 

        'pass_yn': [True, True, True, False]}, 

         columns=['date', 'name', 'age', 'math_score', 'pass_yn'])

import sqlalchemy

from sqlalchemy import create_engine

# engine = sqlalchemy.create_engine("postgresql://user:password@host:port/database")

engine = create_engine("postgresql://postgres:postgres@localhost:5432/postgres") # set yours

engine.execute("DROP TABLE IF EXISTS public.score;") # drop table if exists

score.to_sql(name = 'score', 

             con = engine, 

             schema = 'public', 

             if_exists = 'fail', # {'fail', 'replace', 'append'), default 'fail'

             index = True, 

             index_label = 'id', 

             chunksize = 2, 

             dtype = {

                     'id': sqlalchemy.types.INTEGER(), 

                     'date': sqlalchemy.DateTime(), 

                     'name': sqlalchemy.types.VARCHAR(100), 

                     'age': sqlalchemy.types.INTEGER(), 

                     'math_score': sqlalchemy.types.Float(precision=3), 

                     'pass_yn': sqlalchemy.types.Boolean()

                     })

  (1) 다양한 동점 처리방법(tie-breaking methods)에 따른 순위(rank) 구하기 비교

In [1]: import numpy as np

In [2]: import pandas as pd

In [3]: df = pd.DataFrame({

   ...: 'name': ['kim', 'lee', 'park', 'choi', 'jung', 'gang', 'nam'],

   ...: 'score': [70, 95, 100, 95, 70, 90, 70]

   ...: }, columns=['name', 'score'])

In [4]: df

Out[4]:

name score

0 kim 70

1 lee 95

2 park 100

3 choi 95

4 jung 70

5 gang 90

6 nam 70

In [5]: df['rank_by_average'] = df['score'].rank(ascending=False) # rank default method='average

In [6]: df['rank_by_min'] = df['score'].rank(method='min', ascending=False)

In [7]: df['rank_by_max'] = df['score'].rank(method='max', ascending=False)

In [8]: df['rank_by_first'] = df['score'].rank(method='first', ascending=False)

In [9]: df['rank_by_dense'] = df['score'].rank(method='dense', ascending=False)

In [10]: df

Out[10]:

  name   score   rank_by_average    rank_by_min   rank_by_max    rank_by_first \

0 kim        70                   6.0              5.0              7.0              5.0

1 lee         95                   2.5              2.0              3.0              2.0

2 park      100                  1.0               1.0              1.0              1.0

3 choi        95                  2.5               2.0              3.0              3.0

4 jung        70                  6.0               5.0              7.0              6.0

5 gang       90                  4.0               4.0              4.0              4.0

6 nam        70                  6.0               5.0              7.0              7.0

rank_by_dense

0            4.0

1            2.0

2            1.0

3            2.0

4            4.0

5            3.0

6            4.0

  (2) 순위를 오름차순으로 구하기 (Rank in Ascending order)

In [11]: df_score = df[['name', 'score']].copy()

In [12]: df_score['rank_ascending'] = df_score['score'].rank(method='min', ascending=True)

In [13]: df_score

Out[13]:

name    score     rank_ascending

0 kim        70                  1.0

1 lee         95                    5.0

2 park      100                    7.0

3 choi        95                    5.0

4 jung        70                    1.0

5 gang       90                    4.0

6 nam        70                    1.0 

  (3) 그룹 별로 순위 구하기 (Rank by Groups): df.groupby().rank()

In [14]: from itertools import chain, repeat

    ...:

In [15]: df2 = pd.DataFrame({

    ...: 'name': ['kim', 'lee', 'park', 'choi']*3,

    ...: 'course': list(chain.from_iterable((repeat(course, 4)

    ...: for course in ['korean', 'english', 'math']))),

    ...: 'score': [70, 95, 100, 95, 65, 80, 95, 90, 100, 85, 90, 90]

    ...: }, columns=['name', 'course', 'score'])

In [16]: df2

Out[16]:

    name   course  score

0    kim   korean     70

1    lee   korean     95

2   park   korean    100

3   choi   korean     95

4    kim  english     65

5    lee  english     80

6   park  english     95

7   choi  english     90

8    kim     math    100

9    lee     math     85

10  park     math     90

11  choi     math     90

In [17]: df2['rank_by_min_per_course'] = df2.groupby('course')['score'].rank(method='min', ascending=False)

In [18]: df2

Out[18]:

    name   course  score  rank_by_min_per_course

0    kim   korean     70                     4.0

1    lee   korean      95                     2.0

2   park   korean    100                     1.0

3   choi   korean      95                     2.0

4    kim  english      65                     4.0

5    lee  english       80                     3.0

6   park  english      95                     1.0

7   choi  english      90                     2.0

8    kim     math    100                     1.0

9    lee     math      85                     4.0

10  park     math     90                     2.0

11  choi     math     90                     2.0

  (4) 칼럼을 기준으로 순위 구하기 (Rank over the columns): df.rank(axis=1)

In [19]: df3 = pd.DataFrame({

    ...: 'col_1': [1, 2, 3, 4],

    ...: 'col_2': [3, 5, 1, 2],

    ...: 'col_3': [3, 1, 2, 4]})

In [20]: df3

Out[20]:

 col_1 col_2 col_3

0   1      3      3

1   2      5      1

2   3      1      2

3   4      2      4

In [21]: df3.rank(method='min', ascending=False, axis=1)

Out[21]:

   col_1    col_2    col_3

0    3.0       1.0       1.0

1    2.0       1.0       3.0

2    1.0       3.0       2.0

3    1.0       3.0       1.0 

import numpy as np

import pandas as pd

# make a DataFrame

school = pd.DataFrame({'region': ['gangnam', 'secho', 'bundang', 'mokdong'], 

                       'student_cnt': [100, 120, 150, 90], 

                       'math_score': [91, 95, 92, 93]}, 

                        columns=['region', 'student_cnt', 'math_score'])

school

Out[02]: 

    region  student_cnt  math_score

0  gangnam          100          91

1    secho          120          95

2  bundang          150          92

3  mokdong           90          93

  (1) DataFrame을 CSV 파일로 내보내기 (export a DataFrame to CSV file)

school.to_csv('C:/Users/admin/Documents/data/school.csv', 

              sep=",", 

              na_rep="NaN", 

              index=False, 

              header=False)

  (2) Postgresql, Greenplum DB에 연결하고 Table 만들기

# Postgresql DB connect using psycopg2

from psycopg2 import connect

conn = connect(host='localhost',  # set yours

               port=5432, 

               database='postgres', 

               user='postgres', 

               password='postgres')

cur = conn.cursor()

# Create a table at Postgresql public schema with school name

cur.execute("""

    DROP TABLE IF EXISTS school;

    CREATE TABLE school (

        region varchar(100), 

        student_cnt numeric, 

        math_score numeric

    )

""")

conn.commit()

  (3) Postgresql, Greenplum DB의 Table에 CSV file을 Copy해서 집어넣기

query = """

    COPY school FROM STDIN DELIMITER ',' CSV;

"""

with open('C:/Users/admin/Documents/data/school.csv', 'r') as f:

    cur.copy_expert(query, f)

conn.commit()

# close connection

conn.close()

# check 

cur.execute("SELECT * FROM school;")

school_df = cur.fetchall()

school_df

Out[39]: 

[('gangnam', Decimal('100'), Decimal('91')),

 ('secho', Decimal('120'), Decimal('95')),

 ('bundang', Decimal('150'), Decimal('92')),

 ('mokdong', Decimal('90'), Decimal('93'))]