# Pandas練習
# 吳智鴻

#Exercise#1

datas = [[70,90,85],[85,72,53],[90,76,77]]
indexs = ["吳智鴻","王小花","李大呆"]
columns = ["電腦","美術","體育"]
df2 = pd.DataFrame(datas, columns=columns, index = indexs)

print("學生成績 \n")
print(df2)

# ===========================================
# Exercise2 取得成績

# 取得我的所有成績
print(df2.values[0])

# 取得第二位同學的體育成績
print(df2.values[1][2])


# numpy計算平均
import numpy

data = [1,2,3,4,5,6]
mean = numpy.mean(data)
print(mean)

# ============================================
# Exercise3 平均
import numpy

myscore=df2.loc["吳智鴻",:]
print(myscore)
print('我的平均 : ', numpy.mean(myscore))
print('\n')

computer_score = df2.loc[:,"電腦"]
print(computer_score ,'\n')
print('電腦科平均 : ', numpy.mean(computer_score),'\n')

art_score = df2.loc[:,"美術"]
print(art_score ,'\n')
print('美術平均 : ', numpy.mean(art_score))


# ======================================
# Exercise 4 列印線形圖

# Colab 進行matplotlib繪圖時顯示繁體中文
# 下載台北思源黑體並命名taipei_sans_tc_beta.ttf，移至指定路徑
!wget -O TaipeiSansTCBeta-Regular.ttf https://drive.google.com/uc?id=1eGAsTN1HBpJAkeVM57_C7ccp7hbgSz3_&export=download

import matplotlib as mpl
import matplotlib.pyplot as plt 
from matplotlib.font_manager import fontManager

# 改style要在改font之前
# plt.style.use('seaborn')  

fontManager.addfont('TaipeiSansTCBeta-Regular.ttf')
mpl.rc('font', family='Taipei Sans TC Beta')

df2.plot()

# =================================================
# Exercise 5 讀取EXCEL檔
import pandas as pd
import numpy

data = pd.read_excel("學生成績.xlsx")

print(data)

print('\n')


print(data.iloc[1])

# ===========================================
# Exercise 6 計算平均
import pandas as pd
import numpy

data = pd.read_excel("學生成績.xlsx")
print(data)
print('\n')

# 
#print('axis = 1 橫向')
#print(data.mean(axis=1))
#print('\n')

#
#print('axis = 0 縱向')
#print(data.mean(axis=0))
#print('\n')


# 個人平均 axis=1 橫向
print('[ 個人平均 ] axis = 1 橫向')
student_avg = data.mean(axis=1)
print(student_avg)
print('\n')

# 科目平均 axis=0 縱向
print('[ 科目平均 ] axis = 1 縱向')
course_avg = data.mean(axis=0)
print(course_avg)
print('\n')

# =======================================================
# Exercise 7 寫入EXCEL
import pandas as pd
import numpy

data = pd.read_excel("學生成績.xlsx")
print(data)
print('\n')

# 個人平均 axis=1 橫向
print('[ 個人平均 ] axis = 1 橫向')
student_avg = data.mean(axis=1)
print(student_avg)
print('\n')

# 科目平均 axis=0 縱向
print('[ 科目平均 ] axis = 1 縱向')
course_avg = data.mean(axis=0)
print(course_avg)
print('\n')



#重新命名列名
data.columns = ['Name','電腦','美術','體育']

#取得資料共有幾列
end_rows = len(data)
print('資料共有幾列 ', end_rows, '\n')

# 寫入個人平均
data.insert(4, column="個人平均", value=student_avg)

# 寫入各科平均
data.at[end_rows, "Name"] = '各科平均'
data.at[end_rows, "電腦"] = course_avg.loc["電腦"]
data.at[end_rows, "美術"] = course_avg.loc["美術"]
data.at[end_rows, "體育"] = course_avg.loc["體育"]


print(data)

data.to_excel('output.xlsx')