- 시각화 + pandas조작 + Pandas DB 연동
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import MySQLdb
import pickle
config = {}
plt.rc('font', family='malgun gothic')
plt.rcParams['axes.unicode_minus'] = False
try:
with open('mydb.dat', 'rb') as f:
config = pickle.load(f)
except Exception as e:
print('err :', e)
try:
conn = MySQLdb.connect(**config)
cursor = conn.cursor()
sql = "SELECT jikwon_no, jikwon_name, buser_name, jikwon_pay, jikwon_jik, jikwon_gen FROM jikwon a INNER JOIN buser b ON a.buser_num=b.buser_no"
cursor.execute(sql)
df = pd.DataFrame(cursor.fetchall(), columns=['사번', '이름', '부서명', '연봉', '직급', '성별'])
print('부서별 연봉 합 :\n', df.groupby(['부서명'])['연봉'].sum(), end="\n\n")
print('부서별 최대 연봉 :\n', df.groupby(['부서명'])['연봉'].max(), end="\n\n")
print('부서별 최소 연봉 :\n', df.groupby(['부서명'])['연봉'].min(), end="\n\n")
freq_table = pd.crosstab(index=df['부서명'], columns=df['직급'], margins=True)
print(freq_table)
sql = "SELECT a.jikwon_no, jikwon_name, gogek_no, gogek_name, gogek_tel FROM jikwon a LEFT OUTER JOIN gogek b ON a.jikwon_no=b.gogek_damsano"
gogek_df = pd.read_sql(sql, conn)
gogek_df.columns = ['사번', '이름', '고객번호', '고객명', '전화번호']
print(gogek_df.isnull().sum())
gogek_df = gogek_df.fillna('담당 고객 X')
print(gogek_df)
print()
data = df.groupby(['부서명'])['연봉'].mean()
print(data)
plt.barh(y=data.index, width=data.values)
plt.title('부서명별 연봉 평균')
plt.show()
jik_df = pd.read_sql("SELECT * FROM jikwon", conn)
jik_df.columns = ['사번', '이름', '부서번호', '직급', '연봉', '입사일', '성별', '점수']
pv_table = pd.pivot_table(jik_df,
index='성별',
values='연봉',
aggfunc='mean')
print(pv_table)
y_value = pv_table.values.reshape(1,2)[0]
x_value = pv_table.index
sns.barplot(y=y_value, x=x_value)
plt.show()
print()
crs_table = pd.crosstab(index=df['부서명'],
columns=df['성별'])
print(crs_table)
except Exception as e:
print('err :', e)
finally:
cursor.close()
conn.close()