Data Analysis / pandas / DataFrame(2)

< Database로 부터 DataFrame 생성 >

Databse 안의 Table을 사용하는 방법은 크게 두 가지이다.

 1. Django에서 사용하는 ORM 방식 (Model을 이용해서 Tablel을 사용)

 => 장점 : SQL이라고 불리는 Database 언어를 몰라도 Table 사용이 가능하다.

 2. 일반적으로는 SQL 구문을 이용해서 Database 안의 Table을 사용

 => SQL 구문을 알아야 table에서 내가 원하는 데이터를 추출할 수 있다.

# 저번에 만들었던 Database의 table을 활용해보자
import pymysql.cursors
import pandas as pd

# 데이터베이스에 연결
conn = pymysql.connect(host='localhost', user='data', password='data',
                       db='library', charset='utf8')

# 데이터베이스에 접속되면 SQL 문을 실행시켜서 데이터를 가져올 수 있다.
# 가져온 데이터를 DataFrame으로 생성한다.
# book이라는 table로 부터 btitle, bauthor, bprice 를 가져온다.
sql = 'select btitle, bauthor, bprice from book'

# 조건을 주고 싶다면? => where 구문 사용
# book이라는 table로 부터 bpice > 30000인 항목들의
# btitle, bauthor, bprice 를 가져온다.
sql = 'select btitle, bauthor, bprice from book where bpice>30000'

# 책 제목에 특정 키워드가 들어있는 책들만 고르고 싶다면? => 패턴매칭
# like 키워드를 사용한다.
# % : 여러 글자를 의미
# book table로 부터 btitle에 "JAVA" 단어가 들어가는 항목들의
# btitle, bpric 를 가져온다.
sql = 'select btitle, bprice from book where btitle like "%JAVA%"

# 연결된 데이터베이스에서 sql 구문에 해당하는 데이터를 읽어온다.
df = pd.read_sql(sql, con=conn)
display(df)

< btitle에 JAVA가 들어가는 항목 추출 >

< DataFrame을 JSON 파일로 저장하기 >

이제 csv 파일이나 데이터베이스로부터 데이터를 읽어들여 DataFrame을 생성할 수 있게되었다.

이렇게 생성한 DataFrame을 JSON 파일로 저장해보자.

# Database 안의 book table에서 원하는 내용의 데이터를 가져온 후
# DataFrame으로 만들어서 이 DataFrame을 JSON 파일로 저장해보자
import pymysql.cursors
import pandas as pd

conn = pymysql.connect(host='localhost', user='data', password='data',
                       db='library' charset='utf8')
                       
# 책 제목에 "여행"이 들어가는 책들에 대한 query
sql = 'select btitle, bauthor, bprice from bobok where btitle like "%여행%"

df = pd.read_sql(sql, con=conn)

# DataFrame을 json 파일로 저장할 때 총 4가지의 서로다른 형식이 있다.
# unicode로 파일을 생성한 후, 데이터를 저장해야 한글이 정상 처리가 된다.
# 파일을 저장할 폴더는 미리 생성이 되어있어야 한다.

# 일반적으로 파일을 처리할 때 순서는 파일열기 -> 내용쓰기 -> 파일닫기
# with 구문을 이용하면 resource의 close(해제)처리가 자동으로 수행된다.
# open('파일경로', 열기모드, encoding='인코딩타입') : 파일을 여는 함수
# 열기모드 : 'r'(읽기모드), 'w'(쓰기모드)

# 한글 처리를 위해서는 force_ascii를 False로 설정해야 한다.
# df.to_json(파일객체, force_ascii=False, orient='...')
# orient는 columns(default) / records / index / values의 4가지가 있다.

# 1. columns : json의 key 값을 column으로 설정
with open('./data/json/books_columns.json', 'w', encoding='utf-8') as file:
    df.to_json(file, force_ascii=False, orient='columns')
    
# 2. records : record 단위로 생성
with open('./data/json/books_records.json', 'w', encoding='utf-8') as file:
    df.to_json(file, force_ascii=False, orient='records')    

# 3. index : 각 인덱스 단위로 생성
# records 에서 인덱스 넘버만 붙은 형태
with open('./data/json/books_index.json', 'w', encoding='utf-8') as file:
    df.to_json(file, force_ascii=False, orient='index')

# 4. values : 값만 생성
# records 에서 column 명이 없는 형태
with open('./data/json/books_values.json', 'w', encoding='utf-8') as file:
    df.to_json(file, force_ascii=False, orient='values')

 

아래는 각 경우에 따른 결과 이미지이다. 

1. columns

2. records

3. index

4. values

< JSON 파일로 부터 DataFrame 생성 >

# JSON 파일을 읽어서 DataFrame을 생성해보자

import numpy as np
import pandas as pd
import json

# json 파일을 open 해서 내용을 읽어들여야 한다.
with open('./data/json/books_columns.json', 'r', encoding='utf-8') as file:
    dict_books = json.load(file)  # json 데이터를 python의 dictionary로 저장
    
print(dict_books)
# {'btitle': {'0': 'IT CookBook, ...
# 'bprice': {'0': 25000, '1': 15000, '2': 15800, '3': 15000}}

# dictionary를 그대로 전달해서 DataFrame 생성 가능
df = pd.DataFrame(dict_books)
display(df)

< json 파일로 DataFrame 생성 >

< Open API를 이용해서 DataFrame 생성 >

일일박스오피스 순위를 알려주는 영화진흥위원회 open api를 이용해서 데이터를 json으로 받아온 후, 이 json을 DataFrame으로 만들어주면 된다.

network 연결을 통해 open api를 호출해보자.

< 영화진흥위원회 api : http://www.kobis.or.kr/kobisopenapi/homepg/main/main.do >

import numpy as np
import pandas as pd
import json
import urllib

# 영화진흥위원회 open api에서 open api url에 대한 내용을 참고해서 작성
key = ''  # 영화진흥위원회 회원가입 후에 얻을 수 있는 open api key
targetDt = '20200901'  # 내가 찾고 싶은 날짜
openapi_url = 'http://www.kobis.or.kr/kobisopenapi/webservice/rest/boxoffice/searchDailyBoxOfficeList.json?key={}&targetDt={}''
               .format(key,targetDt)

# urllib.request.urlopen : request를 해당 url로 보내는 함수
# request를 보내면 response가 오는데 이 객체를 load_page에 저장
load_page = urllib.request.urlopen(openapi_url)

# response 객체를 read() 함수를 이용하면 json 객체로 변환 가능
# json.loads() 함수로 json을 dictionary 형태로 추출
json_page = json.loads(load_page.read())  # dictionary

# 얻어온 정보 중 순위, 영화제목, 총매출액만 DataFrame으로 만들어보자
# DataFrame의 values에는 영화제목과 총매출액을 저장
# DataFrame의 index에는 순위값을 지정
# 각각에 대해 빈 dict와 list를 생성
movie_data = dict()
rank_list = list()
title_list = list()
sales_list = list()

# indexing은 코드 아래쪽의 데이터 구조 사진 참고
for tmp in json_page['boxOfficeResult']['dailyBoxOfficeList']:
    rank_list.append(tmp['rank'])
    title_list.append(tmp['movieNm'])
    sales_list.append(tmp['salesAcc'])

# dictionary에 해당 key로 value를 전달하여 구성
movie_data['영화제목'] = title_list
movie_data['총매출액'] = sales_list

df = pd.DataFrame(movie_data)
df.index = rank_list
display(df)

< response json의 구조 >

< Question : 없는 column을 전달해서 DataFrame을 만들면? >

import numpy as np
import pandas as pd

# dictionary를 하나 만들어요
data = {'이름':['이순신', '홍길동', '강감찬', '김유신', '장보고'],
       '학과':['컴퓨터', '기계', '철학', '컴퓨터', '국어국문'],
       '학년':[1,2,2,4,3],
        '학점':[1.5, 2.0, 3.1, 1.1, 2.7]}

# 만약 잘못된 column값을 전달해서 매칭되는 값이 없다면?
# DataFrame에 들어가긴 하지만, 값이 모두 NaN으로 나타난다.
# 없는 column을 만드는데 활용할 수도 있지만, 값이 NaN으로 설정됨에 유의
df = pd.DataFrame(data,
                 columns=['학과','이름','학점','학년'],
                 index=['one','two','three','four','five'])
                 
display(df)

< 없는 column으로 DataFrame 생성 >

< DataFrame indexing >

1. column indexing

# DataFrame에서 특정 column 추출 가능
# 만약 하나의 column을 DataFrame에서 추출하면 결과는 Series

data = {'이름':['이순신', '홍길동', '강감찬', '김유신', '장보고'],
       '학과':['컴퓨터', '기계', '철학', '컴퓨터', '국어국문'],
       '학년':[1,2,2,4,3],
        '학점':[1.5, 2.0, 3.1, 1.1, 2.7]}

df = pd.DataFrame(data,
                 columns=['학과','이름','학점','학년','등급'],
                 index=['one','two','three','four','five'])

# indexing
print(df['이름'])  # 이름 부분만 가져와서 Series로 생성
# one      이순신
# two      홍길동
# three    강감찬
# four     김유신
# five     장보고
# Name: 이름, dtype: object

# 속성으로 접근할 수도 있다.
# 문자열인데 따옴표를 사용하지 않고 전달가능
print(df.이름)  # 같은 결과 생성

# 하지만, 이 방법은 프로그래밍에 적합한 형태가 아니므로 잘 쓰지 않음

year = df['학년']  # Series : view로 생성됨
year['one'] = 100  # Series의 내용을 변경
# view를 수정하면 원본도 변경되기 때문에 사용자에게 경고문이 출력됨
display(df)  # 확인해보면 값이 바뀌어 있음

# display(df['학과','학점'])  # error (문법자체가 틀림)
# slicing
# display(df['학과' : '학점'])  # error : column에 대한 slicing을 지원하지 않음
# Fancy indexing
display(df[['학과', '학점']])  # OK

# 특정 column 값의 수정
# 단일값을 사용하면 broadcasting 되고 수행
df['등급'] = 'A'
# list로 전달 가능
df['등급'] = ['A','B','A','D','F']
display(df)
# ndarray를 사용해서 수정도 가능
df['등급'] = np.array(['A','B','A','D','F'])

# 두 개 이상의 특정 column 값 수정
# 단일값
# df[['학과','등급']] = 'A'  # broadcasting
# 2차원 list
df[['학과','등급']] =  [['영어영문','A'], ['철학', 'C'], ['국어국문','B'],
                   ['화학','F'], ['물리','C']]
# 데이터 수가 맞지 않으면 error 발생!!
display(df)

# 새로운 column 추가
# scalar(단일값), ndarray, list, Series 사용 가능

df['나이'] = [20,21,22,23,24]  # 없는 column을 전달하면 생성
# df['나이'] = [20,21,22]  # error : 개수가 맞지 않음

df['나이'] = pd.Series([20,21,22,23,24])  # 값이 추가가 되지 않음
# Series로 column을 추가할 때는 값의 대입기준이 index이므로
# index를 매칭시켜 주어야 정상적으로 값이 들어간다.
df['나이'] = pd.Series([20,21,22,23,24],
                    index=['one','two','three','four','five'])

# list에 비해서 많이 불편한 것 같은데...
# 장점 : 일부 index에만 값을 설정할 수 있다!!
df['나이'] = pd.Series([20,21,22],
                    index=['one','three','five'])
                    
# column 연산을 통해 새로운 column 추가
# 학점이 3.0 이상인 학생은 True, 아닌 학생은 False 값을 가지는
# '장학생여부'라는 column을 하나 추가해보자
df['장학생여부'] = df['학점'] > 3.0  # Boolean mask

# column 삭제
# 열(column)을 삭제하는 경우, 행(row)을 삭제하는 경우 둘 다 drop 함수 사용
# axis=0 : 행 / axis=1 : 열
# inplace = True : 원본을 삭제 (return 없음)
# inplace = False : 원본은 보존하고 삭제된 결과 df를 return
# 보통 원본은 수정하려고 하지 않기 때문에 default=False
# Fancy indexing을 이용해보자
new_df = df.drop(['학점','등급'], axis=1, inplace=False)
# 열에서 '학점', '등급'을 찾아서 지움

 

2. row indexing

# row indexing
import numpy as np
import pandas as pd

data = {'이름':['이순신', '홍길동', '강감찬', '김유신', '장보고'],
       '학과':['컴퓨터', '기계', '철학', '컴퓨터', '국어국문'],
       '학년':[1,2,2,4,3],
        '학점':[1.5, 2.0, 3.1, 1.1, 2.7]}

df = pd.DataFrame(data,
                 columns=['학과','이름','학점','학년','등급'],
                 index=['one','two','three','four','five'])
                 
# 1. 단순 indexing 사용
# print(df['one'])  # error : 단순히 접근하면 column을 찾는 것이므로
# 숫자 index
# print(df[0])   # error : row에 대해서 숫자 index로 단일 indexing 불가

# slicing
# display(df[1:3])  # OK : start 포함, end 미포함

# 숫자 index를 이용한 Fancy indexing
# display(df[[0,2]])  #  error : row에 대해서 숫자 index를 이용한 Fancy indexing 불가

# 2. 행에 대한 별도의 index(사용자 지정 index) 이용
# 'one','two',...,'five' 로 지정했었다.

# slicing
display(df['one':'three'])  # OK : start 포함, end 포함
# display(df['three':-1])  # error : 숫자 index와 혼용해서 slicing 불가

# Fancy indexing
# display(df[['two', 'four']])  # error : Fancy indexing 사용 불가

# 3. loc[]을 이용한 row indexing => 많이 쓰이는 방법
# loc을 이용할 때는 숫자 index가 아닌 부여한 index(사용자지정 index) 사용
print(df.loc['one'])  # OK : loc와 index를 이용하면 단일 row 추출 가능
# Series 형태로 return

# slicing
display(df.loc['one':'three'])  # OK
# display(df.loc['one':-1])  # error : 숫자 index와 혼용 불가

# Fancy indexing
display[df.loc[['one','three']]  # OK

# 4. iloc[]를 이용한 row indexing => 많이 쓰임
# iloc[]는 무조건 숫자 index만 사용 가능
# 사용자 지정 index를 사용하면 error
# 단일 indexing
display(df.iloc[0])  # OK

# slicing
display(df.iloc[0:2])  # OK : end 미포함

# Fancy indexing
display(df.iloc[[0,2]])  # OK