DAY42. Python Pandas (2)DFmerge, csvExcel, DummyEncoding

DF_merge

1. DF병합(merge) = join
DF1(id) + DF2(id) = DF3

2. DF결합(concat) = cbind, rbind
DF1 + DF2 = DF3

import pandas as pd
import os

os.chdir(r'C:\ITWILL\4_Python-2\data')
wdbc = pd.read_csv('wdbc_data.csv')

wdbc.shape #(569, 32)
cols = list(wdbc.columns)
print(cols)

1. DF병합(merge) = join

DF1 = wdbc[cols[:16]] #1~15번째 칼럼
DF1.shape #(569, 16)

DF2 = wdbc[cols[16:]]
DF2.shape #(569, 16)

칼럼 추가

DF2['id'] = wdbc.id
DF2.shape #(569, 17)

how="inner" -> 내부 조인(inner join)

DF3 = pd.merge(left=DF1, right=DF2, how="inner", on="id")
DF3.shape #(569, 32)

how = "inner" : 양쪽 DF에 존재하는 id기준 병합
how = "outer" : 한쪽 DF에 존재하는 id기준 병합



2. DF결합(concat)

DF1 = wdbc[cols[:16]] #1~16
DF2 = wdbc[cols[:16]] #16~32

DF4 = pd.concat(objs = [DF1, DF2], axis = 1) #axis (축의 방향) 1은 열 =cbind
DF4.shape #(569, 32)

 

 

 

 

 

csvExcelIO

1. csv file read
2. data 처리 
3. csv file write
4. excel file read/write

 

import pandas as pd #csv/excel file read/write
import os # file path 

os.chdir(r'c:\ITWILL\4_Python-2\data')

1. csv file read
1) 칼럼명이 없는 경우 

st = pd.read_csv('student.csv', header=None)
st #      0     1    2   3 -> 기본 칼럼명

칼럼명 수정 

col_names = ['sno','name','height','weight']

st.columns =  col_names
print(st)

2) 칼럼명이 특수문자(.) or 공백 -> '_'

iris = pd.read_csv('iris.csv')
iris.info()

iris.columns = iris.columns.str.replace('.','_')
iris.info()

iris.Sepal_Length

3) 특수구분자, 천단위 콤마 
pd.read_csv('file', delimiter='\t', thousands=',')



2. data 처리 : 파생변수 

print(st)

   sno  name  height  weight
0  101  hong     175      65
1  201   lee     185      85
2  301   kim     173      60
3  401  park     180      70

비만도지수(BMI)
BMI = 몸무게 / (키**2)
몸무게 단위 : kg
키 단위 : cm -> m

175 * 0.01 #1.75

bmi = st['weight'] / (st['height']*0.01)**2
bmi

파생변수1 추가 

st['bmi'] = bmi
print(st)

label = 정상 : 18~23, 23초과 : '비만', 18미만 : '저체중'

label = [] 

for bmi in st.bmi :
    if bmi >= 18 and bmi <= 23 :
        label.append('정상')
    elif bmi > 23 :
        label.append('비만')
    else :
        label.append('저체중')

print(label)

파생변수2 추가 

st['label'] = label

print(st)

 

3. csv file write

type(st) # pandas.core.frame.DataFrame

index=None : 행 이름 제외 

st.to_csv('st_info.csv', index=None, encoding='utf-8') 

new_st = pd.read_csv('st_info.csv', encoding='utf-8')
print(new_st)

 

4. excel file read/write

ex = pd.ExcelFile('sam_kospi.xlsx') #class() -> object
kospi = ex.parse('sam_kospi') #시트명 

print(kospi)
kospi.info() #<class 'pandas.core.frame.DataFrame'>

excel file write 

kospi.to_excel('kospi.xlsx', index = None)

 

 

 

 

 

dummy_encoding

1. one hot encoding : 2진수
범주 개수 k개 -> k개 가변수(dummy) 생성
분류분석에서 종속변수(y변수) 대상

2. 가변수(dummy)
범주 개수 k개 -> k-1개 가변수 생성
회귀분석에서 독립변수(x변수) 대상 
ex. 성별(남/여) -> 1개, 혈액형(A/B/O/AB) -> 3개

3. 레이블 인코딩(label encoding)
문자형 -> 10진수 변환
ex.단어 -> 고유 숫자 변환

import pandas as pd
import os

os.chdir(r'C:\ITWILL\4_Python-2\data')
iris = pd.read_csv('iris.csv')
iris.info()

꽃의 종 : 빈도 수

iris.Species.value_counts()

versicolor    50
setosa        50
virginica     50


1. one hot encoding : 2진수

iris_one_hot = pd.get_dummies(data=iris, columns = ['Species'])

iris_one_hot.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 150 entries, 0 to 149
Data columns (total 7 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_setosa      150 non-null    uint8  
 5   Species_versicolor  150 non-null    uint8  
 6   Species_virginica   150 non-null    uint8  

iris_one_hot.iloc[[0,50,100],4:]

     Species_setosa  Species_versicolor  Species_virginica
0                 1                   0                  0
50                0                   1                  0
100               0                   0                  1
분류분석 : 독립변수(x) 4개 -> 종속변수(y) 3개

2. 가변수(dummy) : k-1개 가변수 생성

iris_dummy = pd.get_dummies(data=iris, columns = ['Species'], drop_first=True)
iris_dummy.info()

 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_versicolor  150 non-null    uint8  -> 더미변수 : 칼럼명_값
 5   Species_virginica   150 non-null    uint8 
setosa : 기준(base)를 제외한 나머지 2개 변수 생성



* 회귀분석 : 독립변수(x) 5개(3개 연속형/2개 더미), 종속변수(y) 1개 
3. 범수 기준 변경 'versicolor(base)' -> 'virginica' -> 'setosa'
1) object를 순서 변경 가능한 category형으로 변경

iris['Species2'] = iris['Species'].astype('category') #object -> category
iris.info()

2) 순서 변경

iris['Species2'] = iris['Species2'].cat.set_categories(['versicolor', 'virginica', 'setosa'])

['versicolor', 'virginica', 'setosa']

 

iris['Species2'].value_counts()

versicolor    50
virginica     50
setosa        50

3)dummy 변수

iris_dummy2 = pd.get_dummies(data=iris, columns = ['Species2'],
                             drop_first=True)
iris_dummy2.info()

 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 
 5   Species2_virginica  150 non-null    uint8  
 6   Species2_setosa     150 non-null    uint8  

 
 
 4. 레이블 인코딩(label encoding) : 문자열 -> 10진수 변환

iris.info()

iris.Species.value_counts()

versicolor    50
setosa        50
virginica     50

from sklearn.preprocessing import LabelEncoder
import matplotlib.pyplot as plt #시각화 도구

생성자 -> object

encoder = LabelEncoder()

object.member()

encoder.fit(iris.Species)

data변환 : label -> 10진수

labels = encoder.transform(iris.Species)
print(labels) #0~2

versicolor    0
setosa        1
virginica     2

plt.scatter(x=iris['Sepal.Length'],
            y=iris['Petal.Length'], c=labels)