빅분기 모의고사 3회차

작업 1유형¶

In [3]:

import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p1_1_.csv')
df.head(5)

Out[3]:

	시도명	시군구명	종량제봉투종류	종량제봉투처리방식	종량제봉투용도	종량제봉투사용대상	1L가격	1.5L가격	2L가격	2.5L가격	...	60L가격	75L가격	100L가격	120L가격	125L가격	관리부서명	관리부서전화번호	데이터기준일자	제공기관코드	제공기관명
0	경기도	안양시	규격봉투	매립용	생활쓰레기	사업장용	0	0	0	0	...	0	2100	0	0	0	자원순환과	031-8045-5448	2022-08-12	3830000	경기도 안양시
1	경기도	안양시	규격봉투	매립용	생활쓰레기	기타	0	0	0	0	...	0	2100	0	0	0	자원순환과	031-8045-5448	2022-08-12	3830000	경기도 안양시
2	경기도	안양시	규격봉투	소각용	음식물쓰레기	가정용	0	50	0	80	...	0	0	0	0	0	자원순환과	031-8045-5448	2022-08-12	3830000	경기도 안양시
3	경기도	안양시	규격봉투	소각용	음식물쓰레기	사업장용	0	50	0	80	...	0	0	0	0	0	자원순환과	031-8045-5448	2022-08-12	3830000	경기도 안양시
4	경기도	안양시	규격봉투	소각용	음식물쓰레기	기타	0	50	0	80	...	0	0	0	0	0	자원순환과	031-8045-5448	2022-08-12	3830000	경기도 안양시

5 rows × 26 columns

1. 20L가격과 5L가격이 모두 0원이 아닌 데이터만 필터를 한 후, 각 row별로 20L가격과 5L가격의 차이를 ‘차이가격’ 이라 부른다고 하자. 시도명 별 차이가격의 평균가격을 비교할때 그 값이 가장 큰 금액을 반올림하여 소숫점 이하 1자리까지 구하여라¶

In [15]:

target = df.loc[(df['20L가격']!=0) & (df['5L가격']!=0)]
target['차이가격'] = target['20L가격'] - target['5L가격']
target = target.groupby('시도명')['차이가격'].mean().sort_values(ascending=False)
print(round(target.values[0],1))

619.0

C:\Users\Public\Documents\ESTsoft\CreatorTemp/ipykernel_33544/1825671075.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  target['차이가격'] = target['20L가격'] - target['5L가격']

In [16]:

import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p1_2_.csv')
df.head(5)

Out[16]:

	ID	height(cm)	weight(kg)
0	user_1	173.0	67.5
1	user_2	149.5	82.5
2	user_3	143.8	64.6
3	user_4	163.1	87.2
4	user_5	159.6	77.1

2. BMI는 몸무게(kg) / (키(M) * 키(M)) 로 정의 된다. 초고도 비만은 BMI 25이상 , 고도 비반은 BMI 25미만 - 23이상 , 정상은 23미만 - 18.5이상 저체중은 18.5미만으로 정의 된다. 주어진 데이터에서 초고도비만 인원 + 저체중 인원 의 숫자는?¶

In [23]:

df['BMI'] = df['weight(kg)'] / ((df['height(cm)']*(1/100))**2)
def solution(x):
    if x>=25:
        return '초고도비만'
    elif 23<=x<25:
        return '고도비만'
    elif 18.5<=x<23:
        return '정상'
    elif x<18.5:
        return '저체중'
    
df['bmi_range'] = df['BMI'].apply(solution)
target = df.loc[(df['bmi_range']=='초고도비만') | (df['bmi_range']=='저체중')]
print(len(target))

8998

In [24]:

import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p1_3.csv')
df.head(5)

Out[24]:

	지역	초등학교_전출_도내	초등학교_전출_도외	초등학교_전입_도내	초등학교_전입_도외	중학교_전출_도내	중학교_전출_도외	중학교_전입_도내	중학교_전입_도외	고등학교_전출_도내	고등학교_전출_도외	고등학교_전입_도내	고등학교_전입_도외	년도
0	종로구	221	132	255	126	63	71	104	73	203	72	166	75	2012
1	중구	295	139	203	100	66	43	58	44	156	37	104	39	2012
2	용산구	380	269	302	174	134	78	91	106	102	66	113	48	2012
3	성동구	643	344	376	221	154	103	98	82	179	37	153	41	2012
4	광진구	560	382	595	312	165	158	158	118	115	70	154	66	2012

3. 순유입인원은 초중고 도내,도외 전입인원에서 초중고 도내, 도외 전출인원을 뺀값이다. 각년도별로 가장 큰 순유입인원을 가진 지역구의 순유입인원을 구하고 전체 기간의 해당 순유입인원들의 합을 구하여라¶

In [30]:

df['순유입인원'] = (df['초등학교_전입_도내']+df['초등학교_전입_도외']+df['중학교_전입_도내']+
              df['중학교_전입_도외']+df['고등학교_전입_도내']+df['고등학교_전입_도외']) - (df['초등학교_전출_도내']+df['초등학교_전출_도외']+
                                                                   df['중학교_전출_도내']+df['중학교_전출_도외']+df['고등학교_전출_도내']+df['고등학교_전출_도외'])
target = df.sort_values(by=['년도','순유입인원'], ascending = False).groupby('년도').head(1)
print(target['순유입인원'].sum())

13853

작업 2유형¶

벤츠 차량 가격 예측 : https://www.kaggle.com/datasets/mysarahmadbhat/mercedes-used-car-listing¶

train = https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p2_train_.csv¶

test = https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p2_test_.csv¶

예측 변수 price, test.csv에 대해 ID별로 price 값을 예측하여 제출, 제출 데이터 컬럼은 ID와 price 두개만 존재해야함. 평가지표는 rmse

In [71]:

#데이터 불러오기
import pandas as pd
x_train = pd.read_csv('https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p2_train_.csv')
x_test = pd.read_csv('https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p2_test_.csv')

y_train = x_train[['ID','price']]
X_train = x_train.drop(columns = ['ID','price'])
X_test = x_test.drop(columns = 'ID')
display(X_train)
display(y_train.head())

y_train = y_train['price']

#print(X_train.info())
#print(X_train.nunique())
#print(X_train.isnull().sum()) # 개꿀 널 값 없음
#print(X_test.isnull().sum()) # 개꿀 널 값 없음

#이상치 평균을 대체해주자
def solution(data,col):
    q3 = data[col].quantile(0.75)
    q1 = data[col].quantile(0.25)
    iqr = q3-q1
    
    upper = q3 + (1.5*iqr)
    lower = q1 - (1.5*iqr)
    data[col] = data[col].map(lambda x : data[col].mean() if (x>upper)or(x<lower) else x)
    return data

for i in X_train.select_dtypes(exclude='object').columns:
    solution(X_train, i)
    solution(X_test, i)
    

#데이터를 분할하자
from sklearn.model_selection import train_test_split
X_train, X_validation, Y_train, Y_validation = train_test_split(X_train, y_train, test_size=0.33, random_state=43)
#print(X_train.shape, X_validation.shape, Y_train.shape, Y_validation.shape)
X_train.reset_index(drop=True, inplace=True)
X_validation.reset_index(drop=True, inplace=True)


#로그변환 해주자
import numpy as np
for i in X_train.select_dtypes(exclude='object').columns:
    if X_train[i].min() < 0:
        X_train[i] = X_train[i].map(lambda x : x+abs(X_train[i].min()))
    X_train[i] = np.log1p(X_train[i])
    
    if X_validation[i].min() < 0:
        X_validation[i] = X_validation[i].map(lambda x : x+abs(X_validation[i].min()))
    X_validation[i] = np.log1p(X_validation[i])
    
    if X_test[i].min() < 0:
        X_test[i] = X_test[i].map(lambda x : x+abs(X_test[i].min()))
    X_test[i] = np.log1p(X_test[i])

    
#StandardScaler 스케일링 해주자
obj_col = X_train.select_dtypes(include='object').columns
from sklearn.preprocessing import StandardScaler
sds = StandardScaler()
sds.fit(X_train.drop(columns = obj_col))

X_train_sc = sds.transform(X_train.drop(columns = obj_col))
X_train_sc = pd.DataFrame(X_train_sc, columns = X_train.drop(columns = obj_col).columns)

X_validation_sc = sds.transform(X_validation.drop(columns = obj_col))
X_validation_sc = pd.DataFrame(X_validation_sc, columns = X_validation.drop(columns = obj_col).columns)

X_test_sc = sds.transform(X_test.drop(columns = obj_col))
X_test_sc = pd.DataFrame(X_test_sc, columns = X_test.drop(columns = obj_col).columns)


for i in obj_col:
    X_train_sc[i] = X_train[i]
    X_validation_sc[i] = X_validation[i]
    X_test_sc[i] = X_test[i]
    
#원핫 인코딩 해주자
#display(X_test_sc)
X_full = pd.concat([X_train_sc, X_validation_sc, X_test_sc])
X_full = pd.get_dummies(X_full)
X_train_sc = X_full[:len(X_train_sc)]
X_validation_sc = X_full[len(X_train_sc):len(X_train_sc)+len(X_validation_sc)]
X_test_sc = X_full[len(X_train_sc)+len(X_validation_sc):]
#display(X_test_sc)


#모델링 선정(랜포, xgboost)

#랜포
from sklearn.ensemble import RandomForestRegressor
RFR = RandomForestRegressor(random_state=10)
RFR.fit(X_train_sc, Y_train)

pred_train = RFR.predict(X_train_sc)
pred_validation = RFR.predict(X_validation_sc)


#성능평가
from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score

mse_train = mean_squared_error(Y_train,pred_train)
mse_validation = mean_squared_error(Y_validation,pred_validation)

mae_train = mean_absolute_error(Y_train,pred_train)
mae_validation = mean_absolute_error(Y_validation,pred_validation)

rmse_train = np.sqrt(mse_train)
rmse_validation = np.sqrt(mse_validation)

r2_train = r2_score(Y_train,pred_train)
r2_validation = r2_score(Y_validation,pred_validation)

print('mse_train',mse_train)
print('mae_train',mae_train)
print('rmse_train',rmse_train)
print('r2_train',r2_train)
print('\n')
print('mse_validation',mse_validation)
print('mae_validation',mae_validation)
print('rmse_validation',rmse_validation)
print('r2_validation',r2_validation)

#xgboost
import xgboost as xgb
xgb = xgb.XGBRegressor(random_state=11)
xgb.fit(X_train_sc, Y_train)

pred_train = xgb.predict(X_train_sc)
pred_validation = xgb.predict(X_validation_sc)

from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score

mse_train = mean_squared_error(Y_train,pred_train)
mse_validation = mean_squared_error(Y_validation,pred_validation)

mae_train = mean_absolute_error(Y_train,pred_train)
mae_validation = mean_absolute_error(Y_validation,pred_validation)

rmse_train = np.sqrt(mse_train)
rmse_validation = np.sqrt(mse_validation)

r2_train = r2_score(Y_train,pred_train)
r2_validation = r2_score(Y_validation,pred_validation)

print('\n')
print('mse_train',mse_train)
print('mae_train',mae_train)
print('rmse_train',rmse_train)
print('r2_train',r2_train)
print('\n')
print('mse_validation',mse_validation)
print('mae_validation',mae_validation)
print('rmse_validation',rmse_validation)
print('r2_validation',r2_validation)

	model	year	transmission	mileage	fuelType	tax	mpg	engineSize
0	A Class	2012	Automatic	46000	Diesel	30	64.2	2.1
1	CL Class	2020	Semi-Auto	19	Petrol	150	36.7	2.0
2	A Class	2013	Manual	69031	Petrol	125	51.4	1.6
3	B Class	2015	Automatic	34739	Diesel	125	56.5	2.1
4	E Class	2017	Semi-Auto	21352	Diesel	20	72.4	2.0
...	...	...	...	...	...	...	...	...
9818	S Class	2016	Semi-Auto	25317	Petrol	570	24.1	6.0
9819	C Class	2016	Manual	34903	Diesel	20	68.9	2.1
9820	A Class	2015	Automatic	86890	Diesel	20	64.2	2.1
9821	A Class	2016	Manual	27958	Diesel	0	80.7	1.5
9822	C Class	2015	Semi-Auto	31878	Diesel	20	65.7	2.1

9823 rows × 8 columns

	ID	price
0	10170	11940
1	9551	38624
2	1790	8500
3	10018	13998
4	142	20998

mse_train 1268566.469164871
mae_train 651.4452318567434
rmse_train 1126.3065609170849
r2_train 0.9913435742033165


mse_validation 10448432.172870923
mae_validation 1717.169984305152
rmse_validation 3232.403466906773
r2_validation 0.9182965974531838


mse_train 2198616.742398563
mae_train 1063.8323742562634
rmse_train 1482.773328057449
r2_train 0.9849971104009643


mse_validation 9295079.143196948
mae_validation 1728.6542670163456
rmse_validation 3048.783223385511
r2_validation 0.9273154497846098

xgboost가 더 잘나오므로 모델선정¶

In [83]:

# 최적의 파라미터 찾기
import xgboost as xgb
from sklearn.model_selection import GridSearchCV
model = xgb.XGBRegressor(random_state=11)
parameters = {
    'n_estimators' : [300,400,500],
    'max_depth' : [2,4,6]
}

gridsearch = GridSearchCV(model, parameters, cv = 3)
gridsearch.fit(X_train_sc, Y_train)
print('최적의 파라미터', gridsearch.best_params_)

최적의 파라미터 {'max_depth': 4, 'n_estimators': 400}

In [84]:

#xgboost
import xgboost as xgb
xgb = xgb.XGBRegressor(random_state=11, max_depth = 4, n_estimators = 400)
xgb.fit(X_train_sc, Y_train)

pred_train = xgb.predict(X_train_sc)
pred_validation = xgb.predict(X_validation_sc)

from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score

mse_train = mean_squared_error(Y_train,pred_train)
mse_validation = mean_squared_error(Y_validation,pred_validation)

mae_train = mean_absolute_error(Y_train,pred_train)
mae_validation = mean_absolute_error(Y_validation,pred_validation)

rmse_train = np.sqrt(mse_train)
rmse_validation = np.sqrt(mse_validation)

r2_train = r2_score(Y_train,pred_train)
r2_validation = r2_score(Y_validation,pred_validation)

print('\n')
print('mse_train',mse_train)
print('mae_train',mae_train)
print('rmse_train',rmse_train)
print('r2_train',r2_train)
print('\n')
print('mse_validation',mse_validation)
print('mae_validation',mae_validation)
print('rmse_validation',rmse_validation)
print('r2_validation',r2_validation)

mse_train 1860854.9834610405
mae_train 998.3757146891263
rmse_train 1364.131585830722
r2_train 0.9873019242788881


mse_validation 8573914.802647399
mae_validation 1727.061503396808
rmse_validation 2928.124792874682
r2_validation 0.9329547246005307

최종적으로 test데이터 예측후 제출 평가지표는 rmse¶

In [89]:

print('rmse_validation',rmse_validation)
pred_test = xgb.predict(X_test_sc)
pd.DataFrame({'ID':x_test['ID'], 'price':pred_test})

rmse_validation 2928.124792874682

Out[89]:

	ID	price
0	0	5675.770020
1	2	57309.285156
2	14	16614.427734
3	15	14263.237305
4	18	15874.054688
...	...	...
3291	13096	27131.759766
3292	13098	26317.640625
3293	13101	25688.949219
3294	13109	17712.232422
3295	13112	29389.109375

3296 rows × 2 columns

3유형¶

어느 학교에서 추출한 55명 학생들의 키 정보이다.¶

DataUrl = https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p3_1.csv¶

이 학생들의 키의 95% 신뢰구간을 구하고자 한다.¶

In [91]:

import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/Datamanim/datarepo/main/krdatacertificate/e5_p3_1.csv')
df.head()

Out[91]:

	ID	height
0	user_1	164.57
1	user_2	174.99
2	user_3	171.41
3	user_4	162.47
4	user_5	167.11

55명의 학생들의 키에 대한 표본 평균을 구하여라( 반올림해 소숫점 3쨰자리 까지 )¶

In [93]:

print(round(df['height'].mean(),3))

169.937

t분포 양쪽 꼬리에서의 t 값을 구하여라 (반올림하여 소수4째자리까지)¶

In [104]:

import numpy as np
from scipy.stats import t

std = np.std(df.height,ddof=1) #표준편차 자유도1
n = len(df.height) # 총인원수

# 신뢰수준, 자유도
confidence_level = 0.95
ddof = n-1

# t분포의 양쪽 꼬리에서의 t값
t_value = round(t.ppf((1+confidence_level) / 2, ddof),4)
print(t_value)

2.0049

In [110]:

lower = round(df['height'].mean() - (t_value*std/np.sqrt(n)),3)
upper = round(df['height'].mean() + (t_value*std/np.sqrt(n)),3)
print(lower, upper)

168.321 171.553

In [ ]:

In [ ]:

In [ ]: