#-----------------------------------------------------
#範例13-10：計算男女的數學平均
#-------------------------------------------------

#（1）步驟1：建議：mySQL範例要在anaconda裡面測試（不要再cola測試）
#要先安裝：python 3.x版本的安裝mysql的指令：pip install pymysql

#（2）步驟2：安裝mySQL
#安裝： wamp或xamp
#帳號為root，密碼不設定

#（3）步驟3：建立mydb資料庫，匯入scorechi資料表

#（4）步驟4：連線mydb資料庫指令
#import pymysql
#conn = pymysql.connect(host="localhost",user="root", password="", db="mydb")

#（5）步驟5：查詢scorechi資料表，轉成df指令
#import pandas as pd
#df = pd.read_sql_query("select * from scorechi", conn)

#（6）分群的方法,有4種、
#第1種： df['gender'].value_counts() ：相同數值的數目（Series）
#第2種： df['gender'].unique()    ：相同的幾類資料（Series）
#第3種： df['gender'].nunique()    ：m數值，種類數目（number）
#第4種： df.groupby('gender')

#------------------------------
#練習1：讀取mySQL資料庫
#------------------------------
#（1）下載檔案：https://acupun.site/lecture/pandas/example/chp10/pratice/scoreChi.xlsx
#（2）將excel檔案（scorechi.xlsx）轉成csv
#（3）轉成編碼 = utf8檔案
#（4）建立mydb資料庫
#（5）匯入，scorechi.csv

#連線mydb資料庫指令
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', db='mydb')

#查詢scorechi資料表，轉成df指令
import pandas as pd
df = pd.read_sql_query("select * from scorechi", conn)

print()
print("1-1.顯示全部資料=\n", df)

#------------------------------
#練習2：計算男女的數學平均

#SQL：計算男女的數學平均 ==> df2 = pd.read_sql_query("select 性別, avg(數學) from scorechi group by 性別", conn)
#Pandas：計算男女的數學平均， 方法1 ==> df.groupby('性別')['數學'].mean()
#Pandas：計算男女的數學平均， 方法2 ==>   df.groupby('性別')['數學'].agg('mean')
#       import numpy as np
#Pandas：計算男女的數學平均， 方法3 ==>   df.groupby('性別')['數學'].agg(np.mean)
#Pandas：計算男女的數學平均， 方法4 ==>   df.groupby('性別').agg({"數學": np.mean})
#------------------------------

print()
df2 = pd.read_sql_query("select 性別, avg(數學) from scorechi group by 性別", conn)
print("2-1.SQL：查詢男女的數學平均 =\n", df2)

print()
print("2-2.Pandas：查詢男女的數學平均，方法1 =\n", df.groupby('性別')['數學'].mean())

print()
print("2-3.Pandas：查詢男女的數學平均，方法2 =\n", df.groupby('性別')['數學'].agg('mean'))


print()
import numpy as np
print("2-4.Pandas：查詢查詢同類的數量，方法3 =\n", df.groupby('性別')['數學'].agg(np.mean))

print()
print("2-5.Pandas：列出同類種類，方法4 =\n", df.groupby('性別').agg({"數學": np.mean}))

#------------------------------
#練習3：查詢男女數學的『最高，最低，平均，標準差』

#SQL：計算男女的『中文平均，英文平均，數學平均，數量 』==> df2 = pd.read_sql_query("select 性別, max(數學), min(數學), avg(數學), stddev_samp(數學) from scorechi group by 性別", conn)
#Pandas：計算男女的『中文平均，英文平均，數學平均，數量 』， 方法1 ==> df.groupby('性別')['數學'].agg(['max', 'min', 'mean','std'])
#              import numpy as np
#Pandas：查詢男女數學的『最高，最低，平均，標準差』， 方法2 ==>   df.groupby('性別')['數學'].agg([np.max, np.min, np.mean, np.std])
#Pandas：查詢男女數學的『最高，最低，平均，標準差』， 方法3 ==>   df.groupby('性別').agg({"數學" : [np.max, np.min, np.mean, np.std]})
#------------------------------

print()
# mySQL的標準差指令：stddev_samp
# 參考：https://www.sjkjc.com/mysql-ref/stddev_samp/
df2 = pd.read_sql_query("select 性別, max(數學), min(數學), avg(數學), stddev_samp(數學) from scorechi group by 性別", conn)
print("3-1.SQL：查詢男女的中文/英文/數學平均/數量 =\n", df2)

print()
print("3-2.Pandas：查詢男女數學的『最高，最低，平均，標準差』，方法1 =\n", df.groupby('性別')['數學'].agg(['max', 'min', 'mean','std']))

print()
import numpy as np
print("3-3.Pandas：查詢男女數學的『最高，最低，平均，標準差』，方法2 =\n", df.groupby('性別')['數學'].agg([np.max, np.min, np.mean, np.std]))

print()
print("3-4.Pandas：查詢男女數學的『最高，最低，平均，標準差』，方法3 =\n", df.groupby('性別').agg({"數學" : [np.max, np.min, np.mean, np.std]}))


#------------------------------
#練習4：計算男女的『中文平均，英文平均，數學平均，數量 』

#SQL：計算男女的『中文平均，英文平均，數學平均，數量 』==> df2 = pd.read_sql_query("select 性別, avg(中文), avg(英文), avg(數學), count(*) from scorechi group by 性別", conn)
#Pandas：計算男女的『中文平均，英文平均，數學平均，數量 』， 方法1 ==>  df.groupby('性別').agg({"中文": np.mean, "英文": np.mean, "數學": np.mean})
#Pandas：算男女的『中文平均，英文平均，數學平均，數量 』， 方法2 ==>   df.groupby('性別').agg({"中文": 'mean', "英文": 'mean', "數學":'mean'})
#                       df['數量'] = 1
#Pandas：算男女的『中文平均，英文平均，數學平均，數量 』， 方法3 ==>   df.groupby('性別').agg({"中文": np.mean, "英文": np.mean, "數學": np.mean, "數量": np.size})
#------------------------------

print()
df2 = pd.read_sql_query("select 性別, avg(中文), avg(英文), avg(數學), count(*) from scorechi group by 性別", conn)
print("4-1.SQL：查詢男女的中文/英文/數學平均/數量 =\n", df2)

print()
print("4-2.Pandas：查詢男女的中文/英文/數學平均， 方法1 =\n", df.groupby('性別').agg({"中文": np.mean, "英文": np.mean, "數學": np.mean}))

print()
print("4-3.Pandas：查詢男女的中文/英文/數學平均， 方法2  =\n", df.groupby('性別').agg({"中文": 'mean', "英文": 'mean', "數學":'mean'}))

print()
df['數量'] = 1
print("4-4.Pandas：查詢男女的中文/英文/數學平均/數量， 方法4 =\n", df.groupby('性別').agg({"中文": np.mean, "英文": np.mean, "數學": np.mean, "數量": np.size}))


#關閉連線
conn.close()

#--------------------------------------------------------------------