1) SQL
select ename, sal
from emp
where sal > ( select sal
from emp
where ename='JONES' );
2) python
jones_sal = emp['sal'][ emp.ename=='JONES'].values[0]
emp[['ename', 'sal']] [ emp.sal > jones_sal ]
1) SQL
select ename, sal
from emp
where sal = ( select max(sal)
from emp );
2) python
emp.loc[emp.sal == emp.sal.max(),['ename','sal']]
문제2. 아래의 SQL을 판다스로 구현하시오
1) SQL
select ename, hiredate
from emp
where hiredate = (select max(hiredate) from emp);
2) python
emp.loc[emp.hiredate == emp.hiredate.max(),['ename','hiredate']]
문제3. (multiple row subquery) 아래의 sql을 판다스로 구현하시오
1) SQL
select ename, sal
from emp
where deptno in (select deptno
from emp
where job ='SALESMAN');
2) python
sales_deptno= emp.loc[emp.job.str.lower() =='salesman', 'deptno'].values
emp.loc[emp.deptno.isin(sales_deptno),['ename','sal']]
-> emp DataFrame에서 deptno가 sales_deptno 리스트에 포함된 모든 직원의 이름과 급여를 조회하게 됩니다.
문제 4. 지하철에서 가장 많이 발생하는 범죄가 무엇인지 출력하시오
import pandas as pd
crime = pd.read_csv('c:\\data\\crime_loc.csv',encoding = 'cp949')
a = crime.loc[ crime.장소 =='지하철',:].sort_values(by='건수',ascending=False)
print(a.head(1).loc[:,'범죄'].values[0])
문제 5. (multiple column subquery) 아래의 sql 을 판다스로 구현하시오
1) SQL
select ename, sal, job
from emp
where deptno in (select deptno
from emp
where comm is not null)
and job in (select job
from emp
where comm is not null);
2) python
sub1 = emp.loc[~emp.comm.isnull(),'deptno'].values
sub2 = emp.loc[~emp.comm.isnull(),'job'].values
emp.loc[(emp.deptno.isin(sub1)) & (emp.job.isin(sub2))],['ename','sal','deptno']
★ 빅분기 실기 기출문제 2회_작업형1_문제2
Q. 주어진 데이터셋(users.csv) 의 앞에서부터 순서대로 75% 데이터만 활용해 'feature' 컬럼 결측치를 최빈값으로 채우기 전 후의 표준편차를 각각 구해서 두 표준편차의 차이를 계산하시오 !
#1. 데이터 불러오기
df = pd.read_csv("c:\\data\\users.csv")
df.isnull().sum() #결측값 확인
std = df['order_amount'].std() *3
mean = df['order_amount'].mean()
3) 이상치 구하기
out = df.loc[ ( df['order_amount'] > mean - std ) | ( df['order_amount'] < mean + std ), :]
out
4) 이상치들의 합 계산하기
result = out['order_amount'].sum()
5) 결과출력하기
print(result)
⭐빅분기 3회_작업형1_문제1 (simulated_data.csv)
주어진 데이터셋 (simulated_data.csv) 의 모든 결측값을 제거한 후, 데이터 처음부터 순서대로 80% 추출하여 average_income 변수의 3분위수를 출력하기
주의사항 : 문자형 컬럼에 빈칸 ‘’인 데이터가 존재할 수 있으므로, 이를 결측으로 처리하고 제거해야함
#문제를 5단계로 나누기
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\simulated_data.csv")
df
#12. 모든 결측값을 제거하기
#df.isnull().sum()
df_cleaned = df.dropna().copy()
df_cleaned.isnull().sum()
#주의사항 : 문자형 컬럼에 빈칸 ‘’인 데이터가 존재할 수 있으므로, 이를 결측으로 처리하고 제거해야함
#df_cleaned.info()
df_cleaned = df_cleaned.loc[ df_cleaned['region'] != '', : ]
df_cleaned
#3. 데이터를 처음부터 순서대로 80% 추출
#df_cleaned.iloc[ rows, columns ]
# 행번호 , 열번호
# 0:10 , 0:3
data_subset = df_cleaned.iloc[ 0: int(len(df_cleaned) * 0.8) , : ]
data_subset
#4. average_income 변수의 3분위수를 산출하기
result = data_subset['average_income'].quantile(0.75)
result
#5. print 로 결과를 출력하기
print(result)
#9회 예상문제
#1분위수(25%)
result = data_subset['average_income'].quantile(0.25)
#중앙값 또는 2분위수(50%)
result = data_subset['average_income'].quantile(0.50)
#3분위수( 75%)
result = data_subset['average_income'].quantile(0.75)
#9분위수(90%)
result = data_subset['average_income'].quantile(0.90)
예상 문제. 주어진 데이터셋 (simulated_data.csv) 의 모든 결측값을 제거한 후, 데이터를 처음부터 순서대로 90% 추출하여 total_population 변수의 중앙값(2분위수)을 산출하기
주의사항 : 문자형 컬럼에 빈칸 ‘’인 데이터가 존재할 수 있으므로, 이를 결측으로 처리하고 제거해야함
#문제를 5단계로 나누기
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\simulated_data.csv")
df
#12. 모든 결측값을 제거하기
#df.isnull().sum()
df_cleaned = df.dropna().copy()
df_cleaned.isnull().sum()
#주의사항 : 문자형 컬럼에 빈칸 ‘’인 데이터가 존재할 수 있으므로, 이를 결측으로 처리하고 제거해야함
#df_cleaned.info()
df_cleaned = df_cleaned.loc[ df_cleaned['region'] != '', : ]
df_cleaned
#3. 데이터를 처음부터 순서대로 90% 추출
#df_cleaned.iloc[ rows, columns ]
# 행번호 , 열번호
# 0:10 , 0:3
data_subset = df_cleaned.iloc[ 0: int(len(df_cleaned) * 0.9) , : ]
data_subset
#4. total_population 변수의 중앙값, 2분위수를 산출하기
result = data_subset['total_population'].quantile(0.50)
result
#5. print 로 결과를 출력하기
print(result) ###2594.0
⭐빅분기 5회_작업형1_문제1 (product_data.csv)
특정 회사의 제품 데이터에서 제품 종류가 ‘일반제품’ 이고 용도가 ‘음식물처리’인 제품 중 1L 가격의 평균을 구하기
단, 가격이 0인 제품은 제외하고 계산한 뒤 반올림하여 정수로 출력하기
데이터 설명 : product_data.csv 파일에는 여러 제품의 종류 ,용도, 그리고 다양한 용량의 따른 가격 정보가 포함되어 있음
한글이 포함된 csv 파일이라면 encoding = utf-8 이나 cp949 사용해야함
#문제를 5단계로 나눠서 풀이
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\product_data.csv" ,encoding="utf-8") #cp949 , utf-8
df
#2. 제품 데이터에서 제품 종류가 '일반제품'이고 용도기 '음식물처리'인 제품 중 1L 가격 제품 구하기 ( 가격이 0인 제품은 제외 )
df2 = df.loc[ (df['제품종류'] == '일반제품') & (df['용도']=='음식물처리') & (df['1L 가격'] > 0), : ]
df2
#3. 1L 가격의 평균 구하기
a = df2.loc[ :,'1L 가격'].mean()
a
#4. 반올림하여 정수로 출력
result = round(a)
result
#5. 결과를 print로 제풀
print(result) #9655
예상 문제. 특정 회사의 제품 데이터에서 제품 종류가 ‘할인제품’ 이고 용도가 ‘전자기기’인 제품 중 2L 가격의 최대값을 구하기
단, 가격이 0인 제품은 제외하고 계산한 뒤 반올림하여 정수로 출력하기
데이터 설명 : product_data.csv 파일에는 여러 제품의 종류 ,용도, 그리고 다양한 용량의 따른 가격 정보가 포함되어 있음
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\product_data.csv")
df
#2. 제품 종류가 '할인제품' 이고 용도가 '전자기기'인 제품 중 2L 가격을 뽑아내기 (가격이 0인 제품은 제외)
df2 = df.loc[ (df['제품종류'] == '할인제품') & (df['용도']=='전자기기') & (df['2L 가격'] > 0), : ]
df2
#3. 2L 가격의 최대 구하기
a = df2.loc[ :,'2L 가격'].max()
a
#4. 반올림하여 정수로 출력
result = round(a)
result
#5. 결과를 print로 제품
print(result) ###29148
⭐빅분기 5회_작업형1_문제2 (employee_vo2max_data.csv)
직원들의 체력 측정 데이터를 바탕으로 V02 Max 값을 계산하고, V02 Max 수치가 우수한 체력인 사람과 보통 체력인 사람의 수의 차이를 절대값으로 구하기 (결과는 정수로 제출할 것)
V02 Max 값 계산 공식
V02 Max = 15 * (HRmax / HRrest)
HRmax : 최대 심박수
HRrest : 안정시 심박수
V02 Max 기준:
매우낮음: V02 Max < 20 낮음 : 20 <= V02 Max < 30 보통체력 : 30 <= V02 Max < 40 우수한 체력 : V02 Max >= 40
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\employee_vo2max_data.csv")
df
#2. 직원들의 체력 측정 데이터를 바탕으로 V02 Max 값을 계산
df['V02Max'] = 15 * (df['HRmax'] / df['HRrest'] )
df
#3. V02 Max 수치가 우수한 체력인 사람과 보통 체력인 사람의 수를 각각 구하기
#보통체력 : 30 <= V02 Max < 40
#우수한 체력 : V02 Max >= 40
excellent_fitness = df.loc[ df['V02Max'] >= 40, : ]
a = len(excellent_fitness)
#a
average_fitness = df.loc[ ( df['V02Max'] >= 30 ) & ( df['V02Max'] < 40 ), : ]
b = len(average_fitness)
b
#4. 수의 차이를 절대값으로 구하기
result = abs( a - b)
result
#5. 결과 출력하기 (중요)
print(result) #395
예상문제. 직원들의 체력 측정 데이터를 바탕으로 V02 Max 값을 계산하고, V02 Max 수치가 우수한 체력인 사람과 매우낮음 체력인 사람의 수의 차이를 절대값으로 구하기 (결과는 정수로 제출할 것)
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\employee_vo2max_data.csv")
df
#2. 직원들의 체력 측정 데이터를 바탕으로 V02 Max 값을 계산
df['V02Max'] = 15 * (df['HRmax'] / df['HRrest'] )
df
#3. V02 Max 수치가 우수한 체력인 사람과 보통 체력인 사람의 수를 각각 구하기
#매우낮음: V02 Max < 20
#우수한 체력 : V02 Max >= 40
excellent_fitness = df.loc[ df['V02Max'] >= 40, : ]
a = len(excellent_fitness)
#a
verylow_fitness = df.loc[ df['V02Max'] < 20 , : ]
b = len(verylow_fitness)
b
#4. 수의 차이를 절대값으로 구하기
result = abs( a - b)
result
#5. 결과 출력하기 (중요)
print(result) #231
⭐빅분기 5회_작업형1_문제3 (car_produnction_data.csv)
자동차 제조 회사의 각 공장에서 생산된 차량수와 이송된 차량수를 바탕으로, 순수 생산량( 생산된 차량수 - 이송된 차량수) 이 가장 많은 공장의 생산된 차량수를 구하기
데이터 설명 : car_production_data.csv 는 각 공장의 생산된 차량수(produced), 이송된 차량수(transferred), 그리고 공장명(factory) 정보가 포함되어있음
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\car_production_data.csv")
df
#2. 순수 생산량(생산된 차량수 - 이송된 차량수)을 파생컬럼으로 생성해서 구하기
df['net_production'] = df['Produced'] - df['Transferred']
df
#3. 순수 생산량이 가장 많은 공장의 생산된 총 차량수 구하기
a = df.loc[ df['net_production'] == df['net_production'].max(), 'Produced']
a
#4. 결과 출력
result = a.values[0]
print(result) #1651
⭐빅분기 6회_작업형1_문제2번 (department_project_data.csv)
회사에서 직원 한명 당 담당하는 프로젝트 수가 가장 많은 부서를 찾고, 그 부서의 총 직원수를 구해서 제출하기 (정수 출력)
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\department_project_data.csv",encoding ="utf-8")
df
#2. 직원 한명 당 담당하는 프로젝트 수 구하기
df['p_cnt'] = (df['1프로젝트'] + df['1프로젝트'] + df['1프로젝트'] + df['1프로젝트'] + df['1프로젝트']) / df['직원수']
df
#3. 직원 한명당 담당하는 프로젝트가 가장 많은 부서의 총 직원수 구하기
a = df.loc[ df['p_cnt'] == df['p_cnt'].max(), '직원수']
a
#4. 결과 출력
result = a.values[0]
print(result) ### 10
⭐빅분기 6회_작업형1_문제3번 기출문제 (sales_data2.csv)
연도별로 각 제품의 월별 판매량(제품 유형의 총합)의 월 평균값을 구한후,
그 값이 가장 큰 연도를 찾아, 해당 연도의 총 판매량의 월평균값을 출력하세요. 결과를 제출할때는 반올림하여 정수로 출력하시오 !
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\sales_data2.csv")
#2. 연도 파생컬럼 생성
df['연도'] = df['날짜'].apply( lambda x : x[0:4])
df
#3. 월별로 판매한 제품들의 총 판매량 구합니다.
df['총판매량'] = df.iloc[ : , 1:5 ].sum(axis=1) # 가로의 합: axis=1, 세로의 합:axis=0
df
#4. 연도, 연도별 총 판매량의 평균값을 출력
result = df.groupby('연도')['총판매량'].mean().reset_index()
result
#5. 총 판매량의 평균값이 가장 큰 연도의 총 판매량 출력
a = result.sort_values(by='총판매량', ascending=False)
a
#6. 결과를 제출할때는 반올림하여 정수로 출력
b = a.loc[a.연도=='2021', '총판매량'].values[0]
print( round(b)) ###507
⭐빅분기 7회_작업형1_문제1번 (정규화와 표준화 문제)
정규화와 표준화를 해야하는 이유?
서로 다른 변수의 값 범위를 일정한 수준으로 맞추는 작업
아무리 좋은 데이터를 가지고 있어도 그 데이터로 기계학습을 시키거나
통계분석을 하려면 데이터 스케일링(scaling)을 해야함
예 ) 체중과 키의 경우는 서로 단위가 다름. 키 데이터가 체중보다 크다고 해서 기계가 체중은 중요하지 않고 키만 중요하다고 인식하면 안되므로 둘 다 학습 시키기 전에 똑같은 수준으로 맞추는 작업이 필요
시험에 자주 나오는 스케일링 방법 2가지
최소 최대 정규화 : 데이터를 0~1 사이의 값으로 변환하는것
예) from sklearn.preprocessing import MinMaxScaler
표준화 : 데이터를 0을 중심으로 양쪽으로 분포 시키는 것
예) from sklear.preprocessing import StandardScaler
시험장에 갈 때 반드시 외워가야하는 명령어 : help(”sklearn.preprocessing”)
가장 많이 응시한 시험 ID를 찾고, 해당 시험 성적 데이터를 표준화 하여 표준화된 점수 중에서 가장 낮은 점수값을 소수점 세번째 자리까지 구하여 출력하기
어떤 학교에서는 학생들의 성적 데이터를 바탕으로 특정 시험에서 가장 성적이 낮은 학생의 표준화된 점수를 구하고자 함
데이터에는 결측치가 포함되어 있어서 결측치행을 제거해야함
데이터 설명: id_student(학생id), id_assessment(시험id), score(점수)
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\student_scores.csv")
df
#2. 데이터에는 결측치가 포함되어 있어서 결측치행을 제거하기
df.isnull().sum()
df.dropna(inplace=True)
df.isnull().sum()
#3. 가장 많이 응시한 시험id를 찾기(최빈값)
mode_value = df['id_assessment'].mode().values[0]
mode_value
#4. 가장 많이 응시한 시험 id의 시험 성적을 검색하기
df2 = df.loc[ df['id_assessment']==mode_value, : ].copy()
#5. df2의 시험 점수를 표준화하기
df2['score'].describe()
from sklearn.preprocessing import StandardScaler # 표준화 함수
scaler = StandardScaler() #데이터 표준화 작업을 위한 scaler 객체를 생성
a = scaler.fit_transform(df2[['score']]) #데이터 프레임으로 제공해야함
df2['score'] = a
#df2['score'].describe()
#6. 표준화한 점수중에서 가장 작은 값 출력하기
result = df2['score'].min()
#7. 소수점 3번째 자리에서 반올림해서 결과 제출하기
print(round(result,3)) ### -1.695
예상 문제. 가장 많이 응시한 시험 ID를 찾고, 해당 시험 성적 데이터를 최대최소 정규화 하여 그 중에서 가장 낮은 점수값을 소수점 세번째 자리까지 구하여 출력하기
#1. 데이터 불러오기
import pandas as pd
df = pd.read_csv("c:\\data\\student_scores.csv")
df
#2. 데이터에는 결측치가 포함되어 있어서 결측치행을 제거하기
df.isnull().sum()
df.dropna(inplace=True)
df.isnull().sum()
#3. 가장 많이 응시한 시험id를 찾기(최빈값)
mode_value = df['id_assessment'].mode().values[0]
mode_value
#4. 가장 많이 응시한 시험 id의 시험 성적을 검색하기
df2 = df.loc[ df['id_assessment']==mode_value, : ].copy()
#5. df2의 시험 점수를 표준화하기
df2['score'].describe()
from sklearn.preprocessing import MinMaxScaler # 최대최소 정규화 함수
scaler = MinMaxScaler() #데이터 표준화 작업을 위한 scaler 객체를 생성
a = scaler.fit_transform(df2[['score']]) #데이터 프레임으로 제공해야함
df2['score'] = a
#df2['score'].describe()
#6. 표준화한 점수중에서 가장 작은 값 출력하기
result = df2['score'].min()
#7. 소수점 3번째 자리에서 반올림해서 결과 제출하기
print(round(result,3)) #0.0
