python / pandas(검색, 조인, 서브쿼리, 그룹함수)

064 Pandas 를 이용한 데이터 검색 

데이터 다루는 일을 할 때 쉽고 빠르게 일을 하려면 판다스를 잘 알아야함.

SQL과 Pandas는 데이터 분석을 위한 필수 기술.

ㅇ판다스를 이용해서 할 수 있는 것?

1. 데이터 검색

2. 데이터 시각화

3. 머신러닝 구현

# 문법
# 데이터프레임명[보고싶은 컬럼명][검색조건]

 

예제. dept3.csv 판다스의 데이터 프레임으로 생성하시오.(데이터프레임명 : dept)

import pandas as pd
dept = pd.read_csv("c:\\data\\dept3.csv")

dept

문제220. 부서위치가 DALLA의 부서명(dname)을 출력하시오.

dept[['dname']][dept['loc']=='DALLAS']

문제221. 확진자별.csv로 wuhan이라는 데이터 프레임을 만들고 아래의 SQL을 pandas로 구현하시오.

# 데이터 불러오기 및 데이터타입 확인
import pandas as pd
wuhan = pd.read_csv("c:\\data\\확진자별.csv",encoding = "euckr")

wuhan.dtypes
	#일자      object
	#국내발생    object    <= 데이터는 숫자로 담겨있는데 형식이 문자형임
	#해외유입     int64
	#사망       int64

코로나 백신접종 이전의 확진자수와 코로나 백신 접종 이후의 확진자수를 비교

- 백신접종 이전

-- SQL
select sum(국내발생)
 from wuhan
 where 일자 between to_date('2020/01/20','rrrr/mm/dd')
               and to_date('2021/02/25','rrrr/mm/dd');

# 판다스
wuhan['국내발생'][wuhan['일자'].between('2020-01-20','2021-02-25')].sum()

- 백신접종 이후

# 판다스
wuhan['국내발생'][wuhan['일자'].between('2021-02-26','2021-12-19')].sum()

- 백신접종 이전/이후를 union all로 묶은 SQL을 판다스로 구현하려면?

-- SQL
select sum(국내발생)
from wuhan_inf
where 일자 between to_date('2020/01/20', 'rrrr/mm/dd')
             and  to_date('2021/02/25', 'rrrr/mm/dd')
union all             
select sum(국내발생)
from wuhan_inf
where 일자 between to_date('2021/02/26', 'rrrr/mm/dd')
             and  to_date('2021/12/19', 'rrrr/mm/dd');

# 판다스
a=wuhan[['국내발생']][wuhan['일자'].between('2020-01-20','2021-02-25')].sum()
b=wuhan[['국내발생']][wuhan['일자'].between('2021-02-26','2021-12-19')].sum()

pd.concat([a,b])

위의 결과를 막대 그래프로 그리시오.

# 폰트 설정
from matplotlib import font_manager, rc
import matplotlib.pyplot as plt
font_name = font_manager.FontProperties(fname="c:/Windows/Fonts/malgun.ttf").get_name()
rc('font', family=font_name) 

# 데이터 추출
a=wuhan[['국내발생']][wuhan['일자'].between('2020-01-20','2021-02-25')].sum()
b=wuhan[['국내발생']][wuhan['일자'].between('2021-02-26','2021-12-19')].sum()

result = pd.concat([a,b])      # 데이터 타입이 series이기 때문에 데이터프레임 형태로 만들어줘야함
result2 = pd.DataFrame(result)    # 데이터프레임 형태로 변환
result2.columns = ['총발생수']    # 컬럼명 바꾸기
result2.index = ['접종전','접종후']    # 인덱스(행) 바꾸기
result2.plot.bar()   # 막대그래프 그리기

 

 

065 Pandas 를 이용한 조인 

# 조인 문법
# pd.merge(테이블1, 테이블2, on = 연결컬럼)

예제. 이름과 부서위치를 출력하세요.

-- SQL
select e.ename, d.loc
 from emp e, dept d
 where d.deptno - d.deptno;

# 판다스
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
dept = pd.read_csv("c:\\data\\dept3.csv")
result = pd.merge(emp, dept, on = 'deptno')
result[['ename','loc']]

문제222. DALLAS에서 근무하는 사원들의 이름과 부서위치를 출력하시오.

-- SQL
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno and d.loc = 'DALLAS';

# 판다스
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
dept = pd.read_csv("c:\\data\\dept3.csv")
result = pd.merge(emp, dept, on = 'deptno')

result[['ename','loc']][result['loc']=='DALLAS']

 

문제223. 아래의 SQL을 판다스로 구현하시오

-- SQL
select e.ename, d.loc, e.sal
 from emp e, dept d
 where e.deptno = d.deptno and e.sal >= 3000;

# 판다스
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
dept = pd.read_csv("c:\\data\\dept3.csv")
result = pd.merge(emp, dept, on = 'deptno')

result[['ename','loc','sal']][result['sal']>= 3000]

문제224. 아래의 SQL을 판다스로 구현하시오

-- SQL
select e.ename, e.sal, d.loc
 from emp e, dept d
 where e.deptno = d.deptno and e.deptno in (10,20) ;

# 판다스
result[['ename','loc','sal']][result['deptno'].isin([10,20])]

문제225. 아래의 SQL을 판다스로 구현하시오

-- SQL
select e.ename, e.sal, d.loc 
 from emp e, dept d
 where e.deptno = d.deptno and e.sal between 1000 and 3000;

# 판다스
result[['ename','sal','loc']][result['sal'].between(1000,3000)]

 

ㅇ오라클 조인 종류 4가지

1. equi join

2. non equi join

3. outer join

4. self join

 

방금 전까지 진행한 게 equi join 

이제 outer join을 알아보겠음.

 

-- SQL
select e.ename, d.loc
 from emp e, dept d
 where e.deptno(+) = d.deptno;

문제226. 아래의 SQL을 판다스로 구현하시오

# 판다스
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
dept = pd.read_csv("c:\\data\\dept3.csv")
result = pd.merge(emp, dept, on = 'deptno', how= 'right')
result[['ename','loc']]

ㅇ how에 사용할 수 있는 옵션 3가지

1. right

2. left

3. outer

 

문제227. 다음의 SQL을 판다스로 구현하시오( full outer join  구현)

-- SQL
select e.ename, d.loc
 from emp e full outer join dept d
 on (e.deptno = d.deptno);

# 판다스
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
dept = pd.read_csv("c:\\data\\dept3.csv")
result = pd.merge(emp, dept, on = 'deptno', how= 'outer')
result[['ename','loc']]

문제228. 부서위치가 CHICAGO이고 월급이 1000 이상인 사원들의 이름과 월급과 부서위치를 출력하시오.

import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
dept = pd.read_csv("c:\\data\\dept3.csv")
result = pd.merge(emp, dept, on = 'deptno')
result[['ename','sal','loc']][(result['loc']=='CHICAGO') & (result['sal']>=1000)]


'''
and --> &
or --> | (수직바)
[(검색조건1)&(검색조건2)]
'''

 

 

ㅇ판다스에서 순위를 출력하는 방법

-- SQL
select ename, sal, rank() over (order by sal desc) as 순위
 from emp;

# 판다스
emp['순위'] = emp['sal'].rank(ascending = False).astype('int')
emp[['ename','sal','순위']].sort_values(by=['순위'])
	# sort_values(by=['순위']) : 순위로 정렬


# 순위를 오라클의 dense_rank()처럼 구현할 때 
emp['순위'] = emp['sal'].rank(method='dense',ascending = False).astype('int')
emp[['ename','sal','순위']].sort_values(by=['순위'])
	# rank() 괄호 안에 method='dense' 추가하면 됨

문제229. 아래의 SQL을 판다스로 구현하시오

-- SQL
select 일자, 국내발생, dense_rank() over (order by(국내발생) desc) as 순위 
    from wuhan_inf
    where 일자 between to_date('2021/11/01', 'rrrr/mm/dd')
             and  to_date('2021/12/01', 'rrrr/mm/dd');

# 판다스
import pandas as pd
wuhan = pd.read_csv("c:\\data\\확진자별.csv",encoding = "euckr")

wuhan['순위'] = wuhan['국내발생'][wuhan['일자'].between('2020-10-01','2021-11-01')].rank(method='dense',ascending=False).astype('int')
wuhan[['일자','국내발생','순위']][wuhan['일자'].between('2020-10-01','2021-11-01')].sort_values(by=['순위'])
# 결과는 똑같이 출력되지만, astype('int')가 안먹힘

  #선생님이 하신 방법
  import pandas as pd
  wuhan = pd.read_csv("c:\\data\\확진자별.csv",encoding = "euckr")

  w2=wuhan[['일자','국내발생']][wuhan['일자'].between('2021-10-01','2021-11-01')]
  w2['순위'] = w2['국내발생'].rank(method='dense', ascending=False).astype('int')
  w2[['일자','국내발생','순위']].sort_values(by = ['순위'])

 

 

066 Pandas 를 이용한 서브쿼리

-- SQL
select ename, sal
 from emp
 where sal > (select sal
 		from emp
                where ename='Jones');

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

jones_sal = emp['sal'][emp['ename']=='JONES'].values[0]
emp[['ename','sal']][emp['sal']>jones_sal]

문제230. 아래의 SQL을 판다스로 구현하시오

-- SQL
select ename, sal
 from emp
 where sal = (select max(sal)
 				from emp);

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

max_sal = emp['sal'].max()
emp[['ename','sal']][emp['sal'] == max_sal]

문제231. 아래의 SQL을 판다스로 구현하시오

-- SQL
select ename, sal, job
 from emp
 where sal in (select sal
 				from emp
                where job = 'SALESMAN');

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

sales_sal = emp['sal'][emp['job']=='SALESMAN']
emp[['ename','sal','job']][emp['sal'].isin(sales_sal)]

 

ㅁ values가 무엇인가?

[1000,2000,3000]  = 리스트

리스트 --> numpy array --> Pandas Series --> Pandas DataFrame

                                         (컬럼)                   (테이블)

import numpy as np
import pandas as pd

a = [1000,2000,3000]
a_n = np.array(a)    #리스트 a를 numpy array로 변환하는 코드

a_p = pd.Series(a_n)   #a_n numpy array를 판다스의 Series로 변환하는 코드

a_d = pd.DataFrame(a_p)  #a_p Series를 판다스 DataFrame으로 변환하는 코드
a_d  #데이터 프레임 짠!


# 그렇다면 이때, values는 무엇이냐?
a_p.values    #판다스 Series에 .values를 붙이면 numpy array가 출력됨
a_p.values[0]   #numpy array의 첫번째 값

 

 

067 Pandas 와 오라클 그룹함수의 비교 

-- SQL
select max(sal)
 from emp;

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

emp['sal'].max()

emp['sal'].max()   # 최대값

emp['sal'].min()   # 최소값

emp['sal'].sum()   # 토탈값

emp['sal'].mean()  # 평균값

emp['sal'].count()   # 건수

emp['sal'].var()   # 분산값 = 데이터의 퍼짐 정도를 수치로 나타낸 것

emp['sal'].std()   # 표준편차(분산값에 루트를 씌운 값)

 

문제232. 아래의 SQL을 판다스로 구현하시오

-- SQL
select max(sal)
 from emp
 where deptno = 20 ;

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

emp['sal'][emp['deptno']==20].max()

문제233. 아래의 SQL을 판다스로 구현하시오.

-- SQL
select deptno, sum(sal)
 from emp
 group by deptno;

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

emp.groupby('deptno')['sal'].sum().reset_index()

.reset_index() 를 사용안하면 결과가 판다스의 Series로 출력되고 .reset_index()를 사용하면 결과가 판다스의 DataFrame으로 출력됨.

 

문제234. 아래의 SQL을 판다스로 구현하시오

-- SQL
select job, sum(sal)
 from emp
 group by job
 having sum(sal) > 5000;

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

result = emp.groupby('job')['sal'].sum().reset_index()
result[result['sal']>5000]

 

문제235. 아래의 SQL을 판다스로 구현하시오

-- SQL
select job, sum(sal)
 from emp
 group by job
 order by 2 desc;

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

result = emp.groupby('job')['sal'].sum().reset_index()
result.sort_values(by=['sal'], ascending = False)

 

문제236. 코로나 환자 데이터인 PatientInfo.csv로 판다스 데이터 프레임을 생성하시오.

import pandas as pd
cov =pd.read_csv("c:\\data\\PatientInfo.csv")
cov

문제237. 코로나에 감염된 나잇대, 나잇대별 건수를 출력하는데 나잇대별 건수가 높은것부터 출력하시오

result = cov.groupby('age')['patient_id'].count().reset_index()
result.sort_values(by =['patient_id'], ascending=False)

문제238. 환자번호, 확진받은 날짜부터 완치한 날짜까지의 일수를 출력하시오.

days = cov['released_date']-cov['confirmed_date']
#TypeError: unsupported operand type(s) for -:'str' and 'str'

문자에서 문자를 뺄 수 없음 = 연산 불가

날짜에서 날짜를 빼줘야 함 <= 날짜형으로 데이터 타입 변경

판다스의 to_datetime을 이용해서 날짜형으로 변경한 뒤 데이터 연산

cov['c_days'] = pd.to_datetime(cov['released_date'])-pd.to_datetime(cov['confirmed_date'])
cov[['patient_id','c_days']]

 

문제240. 코로나 확진 이후 회복까지의 평균기간이 얼마인지 출력하시오.

cov['c_days'].mean()    #24일

 

문제241. 나잇대, 나잇대별 코로나 확진이후 회복까지의 평균기간을 출력하시오

result = cov.groupby('age')['c_days'].mean().reset_index()
result.sort_values(by=['c_days'], ascending=False)

문제242. 위의 데이터에서 c_day를 출력할 때 앞에 2자리만 출력되게 하세요.

import datetime

cov['c_day3'] = cov['c_day'].dt.days
cov['c_day3']

결측치가 있어서 float를 int로 변경하지 못하므로 결측치를 제거하는 작업을 먼저 해줘야 함.

 

문제243. (오늘의 마지막 문제) 아래의 SQL을 판다스로 구현하시오.

-- SQL
select job, sum(sal)
 from emp
 where job != 'SALESMENA'
 group by job
 having sum(sal) >= 3000
 order by sum(sal) desc;

# 판다스
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")

job_sal = emp.groupby('job')['sal'].sum().reset_index()
result = job_sal[(job_sal['job'] != 'SALESMAN')&(job_sal['sal']>=3000)]
result.sort_values(by = ['sal'],ascending = False)

 

 

 

+) 코로나 회복 평균기간 막대그래프

import  pandas  as   pd
cov=pd.read_csv("c:\\data\\PatientInfo.csv")
cov['c_day']=pd.to_datetime( cov['released_date'] ) - pd.to_datetime(cov['confirmed_date'] )

import datetime

cov['c_day3'] = cov['c_day'].dt.days
result =cov[['age','c_day3']].groupby('age')['c_day3'].count().reset_index()
result.plot.bar(x='age')