리눅스 / 마리아디비 파이썬 연동해서 시각화하기1(코로나 데이터)
31 마리아 디비의 테이블을 파이썬과 연동해서 시각화 하기1(코로나 데이터)
질문 : 우리나라에서 코로나 확진자가 많은 도시는 어디인가? 순위는 어떻게 되는가?
1. 데이터는 무조건 데이터 베이스에 저장하고 관리할 수 있게 해줘야합니다.
(왜 데이터 베이스에 저장해야하는가?
예를 들어, 책(데이터)를 구입했는데 db에 저장안하면 방바닥에 두는 것이고 db에 저장하면 책장에 정리해둔 것)
2. 데이터 검색에 최적화된 데이터 베이스 소프트웨어의 인덱스라는 강력한 무기를 이용해서 데이터를 검색하여 판다스 데이터 프레임을 생성합니다.
3. 분석 결과를 시각화하여 더 이해되기 쉽게 결과 보고서를 만듭니다.
위의 작업을 하려면 테이블부터 설계(SQL기술) 해야하고 테이블에 데이터를 이행(SQL기술)을 해야합니다. 디비와 파이썬을 연동해서 시각화를 해야합니다.
+) ETL이란? 데이터 추출(Extract), 변환(Transform), 적재(Load)
#1. 코로나 확진자 데이터인 Case.csv를 리눅스 서버에 /home/oracle 밑에 올립니다.
모바텀 통해서 /home/oracle 밑에 올려둠
#2. mariaDB에 scott으로 접속해서 cov_case 테이블을 생성합니다.
[root@localhost ~]# mysql -h 192.168.122.1 -u scott -p
MariaDB [(none)]> use orcl;MariaDB [orcl]> create table cov_case
-> (case_id int(8),
-> province varchar(30),
-> city varchar(20),
-> group2 varchar(20),
-> infection_case varchar(50),
-> confirmed float,
-> latitude varchar(20),
-> longitude varchar(20) );
#3. Case.csv를 cov_case 테이블에 입력합니다.
문제187. Case.csv를 리눅스에서 열어 6번째 컬럼 데이터 ca를 0으로 변경하시오.
[root@localhost oracle]# awk -F ',' '$6 == "ca"' Case.csv | wc -l
[root@localhost oracle]# vi Case.csv또다른 데이터를 vi편집기를 이용해 지우세요.
MariaDB [orcl]> load data local infile '/home/oracle/Case.csv'
-> replace
-> into table orcl.cov_case
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
-> ignore 1 lines
-> (case_id,province,city,group2,infection_case,confirmed,latitude,longitude);
#4. 주피터 노트북에서 마리아 디비에 있는 cov_case 테이블을 데이터 프레임으로 만듭니다.
import mysql.connector
config = {
"user": "scott",
"password": "tiger",
"host": "192.168.122.1", #local
"database": "orcl", #Database name
"port": "3306" #port는 최초 설치 시 입력한 값(기본값은 3306)
}
conn = mysql.connector.connect(**config)
# db select, insert, update, delete 작업 객체
cursor = conn.cursor()
# 실행할 select 문 구성
sql = """
select *
from cov_case
"""
# cursor 객체를 이용해서 수행한다.
cursor.execute(sql)
# select 된 결과 셋 얻어오기
rows= cursor.fetchall() # tuple 이 들어있는 list
#print(rows)
####################################
import pandas as pd
colname_n = cursor.description
col_n = [i[0].lower() for i in colname_n]
cov_case = pd.DataFrame(rows, columns = col_n)
cov_case
문제188. 집단 감염자가 더 많은지 일반 감염자수가 더 많은지 확인하기 위해 group2를 출력하고 group2별 인원수를 출력하시오.
cov_case.groupby('group2')['case_id'].count().reset_index()
일반감염자보다 그룹 감염자가 더 많은 것을 확인할 수 있습니다.
#5. 데이터를 시각화합니다.
위의 결과를 원형 그래프로 시각화하세요.
a = cov_case.groupby('group2')['case_id'].count()
a.columns = ['group2','cnt']
a.index = ['group_no','group_yes']
a.plot(kind = 'pie')
문제189. 우리나라에서 코로나 확진자가 많은 도시를 출력하기 위해 도시명(province), 도시별 확진자수(confirmed)의 토탈값을 출력하시오.
MariaDB [orcl]> select province, sum(confirmed)
-> from cov_case
-> group by province;
+-------------------+----------------+
| province | sum(confirmed) |
+-------------------+----------------+
| Busan | 156 |
| Chungcheongbuk-do | 60 |
| Chungcheongnam-do | 158 |
| Daegu | 6680 |
| Daejeon | 131 |
| Gangwon-do | 62 |
| Gwangju | 43 |
| Gyeonggi-do | 1000 |
| Gyeongsangbuk-do | 1324 |
| Gyeongsangnam-do | 132 |
| Incheon | 202 |
| Jeju-do | 19 |
| Jeollabuk-do | 23 |
| Jeollanam-do | 25 |
| Sejong | 49 |
| Seoul | 1185 |
| Ulsan | 51 |
+-------------------+----------------+
17 rows in set (0.001 sec)
문제190. 위의 결과를 다시 출력하는데 도시명(province), 도시별 확진자수(confirmed)의 토탈값을 출력하고 그 옆에 순위를 출력하세요.
a = cov_case.groupby('province')['confirmed'].sum().reset_index()
a['confirmed_rank'] = a['confirmed'].rank(ascending = False).astype(int)
a.sort_values(by = 'confirmed_rank', ascending = True)
문제191. (오늘의 마지막 문제) 위의 결과를 막대 그래프로 시각화하시오.
a = cov_case.groupby('province')['confirmed'].sum().reset_index()
a['confirmed_rank'] = a['confirmed'].rank(ascending = False).astype(int)
rnk_cov = a.sort_values(by = 'confirmed_rank', ascending = True)
plt.figure(figsize = (20,10))
plt.bar(rnk_cov.province, rnk_cov.confirmed)
plt.xticks(rotation = 90,size = 15)
plt.yticks(size = 15)
plt.show()