230307_DB복습

안녕하세요. 보랏입니다. 

 

오늘은 어제에 이어서 시각화, 통계를 배웠으며, 

오늘은 AWS, 스타벅스, 2022년 KBO 선수를 대상으로 하여 분석을 진행하였습니다. 

 

바로 복습 시작하겠습니다. 

 

1. AWS

• 학원에서 AWS에 대한 비용을 어느정도 지원을 해준다고 하여 오늘부터 AWS를 통해 RDS를 구축하였고, 오늘부터 데이터를 저장 및 불러오는 작업을 하였습니다. 
• RDS(Relational Database Service) : DB인스턴스 생성하여 연결
  • MariaDB, MySQL, Microsoft SQL Server, Oracle,PostgreSQL을 사용하여 DB인스턴스 생성 가능
  • DB인스턴스에 대한 주소값과 각 DB엔진별 포트를 사용하여 각 컴퓨터에서 SQL을 통해 RDS에 연결하였습니다.

cur, con = dbconnect.connection(host="encore.cluster-cd9ecw5piy57.ap-northeast-2.rds.amazonaws.com",
                     user='?????', password='?????', db='??????')

 

2. 스타벅스 데이터 분석

• 오늘은 예전에 진행하였던 스타벅스 데이터를 활용하여 오픈일자를 중심으로 데이터 시각화를 진행하였습니다. 
• 우선 RDS에 올려놓은 스타벅스 데이터를 파이썬으로 불러온 다음에 open_dt라는 데이터를 추가해주었습니다.

import matplotlib.pyplot as plt
import pandas as pd
import numpy as np

star = pd.read_sql_query("select * from starbucks", con) # RDS 스타벅스 쿼리 읽기

star_date = pd.read_sql_query("select open_dt from starbucks2", con) # 스타벅스 오픈 일정 컬럼 읽기
star_date['year'] = star_date['open_dt'].apply(lambda x : x.year) # 연도만 뽑아 새로운 year컬럼 추가
graph = star_date.value_counts('year').sort_index() # 연도별 개수 확인 후 인덱스로 정렬

import matplotlib.pyplot as plt
x_value_01 = graph.index #인덱스 값
y_value_01 = graph.values #스타벅스 매장 오픈 개수

# 마커를 포함한 빨간선 대쉬 선 그래프를 그리고, label은 'red line'으로 설정  
plt.plot(x_value_01, y_value_01, color='red', marker='o', linestyle='dashed', label='red line')
# X값에 따른 Y값을 나타내는 초록색 막대 그래프를 그리고 label은 'bar plot'으로 설정
plt.bar(x_value_01, y_value_01, color='green', label='bar plot')
plt.xlabel('x axis')
plt.ylabel('y axis')
# 두개의 그래프에서 label설정된 문자열 값을 범례로 표시
plt.legend()

plt.title('Hello plot')

plt.show()

 

3. 환율 분석

• 환율변화를 일정별로 분석하기 위해 크롤링을 하여 날짜, 화폐코드, type별 환율을 구하는 함수를 만들었습니다. 

import requests 
url = "https://www.kebhana.com/cms/rate/wpfxd651_01i_01.do"

payload = {"ajax" : "true",
            "curCd" : "",
            "pbldDvCd" : "0",
            "pbldSqn" : "",
            "hid_key_data" : "",
            "inqStrDt" : "20230307",
            "inqKindCd" : "1",
            "hid_enc_data" : "",
            "requestTarget" : "searchContentDiv",}

def get_exchange(date_, currency, type_=1):
    """
    date_ : 날짜, 날짜는 yyyy-mm-dd 형식으로 입력 
    currency: 화폐 코드 
    type_ : 0 사실때 , 1 파실때 
    """
    global hana
    payload['inqStrDt'] = date_.replace("-","")
    r  = requests.post(url, data=payload)
    hana = pd.read_html(r.text)[0]
    hana.columns = ["_".join(sorted(list(set(x)))) for x in hana.columns]
    if type_ == 0:
        return hana.loc[hana.통화.str.find(f"{currency}".upper()) > -1,  "사실 때_현찰_환율"].values[0]
    elif type_ == 1:
        return hana.loc[hana.통화.str.find(f"{currency}".upper()) > -1,  "파실 때_현찰_환율"].values[0]
    else:
        return -1
        
{x.strftime("%Y-%m-%d"):get_exchange(x.strftime("%Y-%m-%d"), 'usd') for x in pd.date_range("2023-03-01", '2023-03-05')}

결과값

• 해당 함수는 hana라는 글로벌 변수를 설정하고 payload에 'inqStrDt'의 value값이 날짜라는 입력값을 통해 변하게 하기 위해서 변수를 생성하였습니다. 
• 그리고 type = 0 이면 살 때 현활 환율, 1이면 팔 때 현찰 환율로 설정하여, currency를 f스트링으로 변수값을 설정하면 해당 환율의 값이 나오게 한 함수입니다. 
• 그리고 2023-03-01 ~ 2023-03-07로 범위를 지정하여 get_exchange함수에 x에 맞는 함수값이 들어가도록 하여 날짜(key값)와 환율(value값) 이 나오는 딕셔너리 구조로 출력하였습니다. 
  • hana.통화.str.find(f'{currency}".upper() > -1 : hana의 '통화' 컬럼에 문자열 찾기 {화폐코드 입력}
  • > -1 은 0보다 값이 1개라도 있으면 출력

df = pd.read_sql_query("""SELECT * FROM exchange
WHERE ex_date > '2021-01-01' 
AND currency = 'USD'
AND sanda_panda = 1""", con)

df.ex_date #인덱스
df.ex_rate #환율 값

# 분기별 환율변화 추이 시각화 
import matplotlib.pyplot as plt
x_value_01 = df.ex_date
y_value_01 = df.ex_rate

# 마커를 포함한 빨간선 대쉬 선 그래프를 그리고, label은 'red line'으로 설정  
plt.plot(x_value_01, y_value_01, color='red', marker='o', linestyle='dashed', label='미화 환율금액')
# X값에 따른 Y값을 나타내는 초록색 막대 그래프를 그리고 label은 'bar plot'으로 설정
plt.xlabel('연도')
plt.ylabel('환율')
# 두개의 그래프에서 label설정된 문자열 값을 범례로 표시
plt.legend()

plt.title('환율추이')

plt.show()

• 현재 연결되어 있는 환율 DB에서 쿼리문으로 화폐코드가 USD, sanda_panda가 1, ex_date가 2021-01-01이후인 환율코드를 불러와 이에 대한 인덱스 값, 환율 값을 활용하여 시각화를 하였습니다. 
• matplotlib.pyplot 사용법
  • x , y 값 변수선언 (대부분 x 는 인덱스 값, y는 변화값으로 설정)
  • plt.plot(x값, y값)
  • color = 그래프 색깔 
  • marker = 그래프 선의 표시 방법 
  • linestyle  = 선 스타일 (solid : 직선 / dashed : --)
  • label : 범례
  • xlabel, ylabel : x행 이름, y열 이름

 

4. KBO 데이터 분석 

• 2022년 KBO데이터를 활용하여 분석을 진행하였고, 연봉에 따라 데이터를 정렬하였습니다. 연봉 컬럼은 뒤에 숫자뒤에 '만원', '달러'로 표기된 문자열 형식의 데이터로 우선 '연봉(원화)'라는 컬럼을 추가하여 숫자형 데이터 컬럼을 만들었습니다. 

import pickle
import pandas as pd
import numpy as np

with open("./kbo_player.pkl", "rb") as f:
    data = pickle.load(f)
    
kbo = pd.DataFrame(data)
kbo2 = kbo.loc[kbo.연봉.notnull()].copy() #연봉 중 null값이 있는 컬럼 제외 테이블 생성

kbo2.isnull().sum().sum() # 전체 결측치 개수 확인 
# 연봉(원화)컬럼 생성 해당 값은 null값이 없고 연봉컬럼에서 '달러', '만원'문자열 삭제하고 정수형으로 변경
kbo2.loc[kbo2['연봉(원화)'].isnull(), '연봉(원화)'] = kbo2.loc[kbo2['연봉(원화)'].isnull()]['연봉'].apply(lambda x : int(x[:-2]))

kbo2.groupby(['Team'])['연봉(원화)'].mean() # Team 값으로 묶은 후 '연봉(원화)'의 평균 값 출력

# Team별 평균값 구한 후 '연봉(원화)'로 정렬 
kbo2.groupby(['Team'])[['연봉(원화)']].mean().sort_values('연봉(원화)', ascending=False)

### agg집계함수 사용 컬럼 계산
kbo2.groupby(['Team'])[['연봉(원화)']].agg(['mean', 'median'])

• groupby함수를 사용하면 집계함수 agg를 사용하여 데이터 값 생성 가능
  • groupby([기준컬럼])[[변수 사용 컬럼]].agg(['평균', '중앙값']
• 판다스 컬럼명 기준 가장 큰 값 도출 
  • kbo2.sort_values('연봉(원화)', ascending = True).groupby(['Team']).first()

 

5. 환율과 코스피 지수 상관관계 분석

• 앞서 환율 관련 분석하였던 내용을 바탕으로 코스피 지수를 불러와 둘 간의 상관관계 분석을 진행하였습니다. 

naver_url = "https://finance.naver.com/sise/sise_index_day.naver?code=KOSPI&page={}"
head = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/110.0.0.0 Safari/537.36'} #head 값 설정
kospi = pd.concat([pd.read_html(requests.get(naver_url.format(x), headers=head).text)[0].dropna() for x in range(1, 101)]) #1~100페이지, null값 제거 후 concat으로 내용 합치기
kospi = kospi.iloc[::-1] #kospi는 시간이 빠른것부터 정렬되어 있어 역순으로 조정
kospi.날짜 = pd.to_datetime(kospi, 날짜) #판다스의 to_datetime 함수를 사용하여 날짜형태로 변환

kospi2 = kospi.query("날짜 >= '2021-01-01'").copy() # 2021-01-01 이후에 날짜를 검색

import matplotlib.pyplot as plt
plt.figure(figsize=(15,10))
plt.style.use("ggplot")
x_value_01 = df.ex_date
y_value_01 = df.ex_rate

# 마커를 포함한 빨간선 대쉬 선 그래프를 그리고, label은 'red line'으로 설정  
plt.plot(x_value_01, y_value_01, color='red', marker='o', linestyle='dashed', label='미화 환율금액')
# X값에 따른 Y값을 나타내는 초록색 막대 그래프를 그리고 label은 'bar plot'으로 설정
plt.plot(kospi2.날짜, kospi2.체결가, color='blue', marker='o', linestyle='dashed', label='코스피')
plt.xlabel('년도')
plt.ylabel('환율')
# 두개의 그래프에서 label설정된 문자열 값을 범례로 표시
#plt.xticks(ticks=x_value_01, rotation=45)
plt.legend()

plt.title('환율 추이')

plt.show()

• pandas의 query함수 기능
  • 비교 연산자 
  • in 연산자 
  • 논리 연산자
  • 외부 변수 참조 연산
  • 인덱스 검색
  • 문자열 부분검색

 

이렇게 오늘 복습도 마치겠습니다.

 

감사합니다.