# To add a new cell, type '# %%'
# To add a new markdown cell, type '# %% [markdown]'
# %%
import os
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

print('import finished.')

file_path = 'd:\\stock.xlsx'

#读取CSV
#file_path = pd.read_csv(fifa_path, index_col='Date', parse_dates=True)

#读取EXCEL
datafr = pd.read_excel(file_path)

datafr


# %%
#行索引
datafr.index


# %%
#列索引
datafr.columns


# %%
#空值列
datafr.info()


# %%
#显示空值
datafr.isnull()


# %%
#空值填充
datafr.fillna(0)
datafr.fillna({'PARTN':0})


# %%
#表中数值分布
datafr.describe()


# %%
datafr['PRICE'].dtype


# %%
#数据指定列去重，并保留first/last行
datafr.drop_duplicates(subset=['PROD'], keep='last').head()


# %%
#索引列转为数据列
datafr.reset_index()


# %%
#指定列作为索引
datafr.reset_index().set_index('DATE')


# %%
#删除原有索引，并生成新索引
datafr.reset_index(drop=True)


# %%
#装入指定列
datafr[['PROD','QTY']]


# %%
#查找行列索引对应的数据
datafr.iloc[[0,3,5],[0,2,3,4]]


# %%
#查找第一行第一列的数据
datafr.iloc[1,1]


# %%
#行索引查找数据
datafr.loc[12]


# %%
#统计出现次数并降序显示
datafr['PROD'].value_counts(normalize=True,sort=True)


# %%
#取唯一值
datafr['PROD'].unique()


# %%
datafr['PROD'].isin([10,22])


# %%
#树型显示
datafr.stack()


# %%
#宽表转长表
datafr.set_index(['STOCK','PROD']).stack().reset_index()


# %%
#求方差
datafr.var(axis=1)


# %%
#所有数据执行函数
datafr[['QTY','TOTAL']].apply(lambda x:x*100)


# %%
#分组统计/求和,并重置索引
datafr.groupby('PROD').aggregate(['count','sum']).reset_index().head()


# %%
#数据透视表
pd.pivot_table(data=datafr,values=['QTY','TOTAL'],index='PROD',columns='PARTN',aggfunc={'QTY':'sum','TOTAL':'sum'},fill_value=0,margins=True,margins_name='SUM').to_excel(excel_writer='d:\\stock_pivot.xlsx')


# %%
plt.rcParams['font.family'] = ['simhei']
plt.rcParams['figure.autolayout'] = True

fig, axes = plt.subplots(1, 2)
ax1 = axes[0]
ax2 = axes[1]

pt = pd.pivot_table(data=datafr,values=['QTY','TOTAL'],index=['PROD'],aggfunc={'QTY':'sum','TOTAL':'sum'},fill_value=0)

ax1.bar(x=pt.index, height=pt.QTY, edgecolor='k')
ax2.bar(x=pt.index, height=pt.TOTAL)

ax1.set_title('QTY')
ax2.set_title('TOTAL')

for tick in ax1.get_xticklabels():
    tick.set_rotation(40)
for tick in ax2.get_xticklabels():
    tick.set_rotation(40)

plt.tick_params(bottom=False, left=False)
plt.show()


# %%
plt.figure(figsize=(14,6))
plt.title("SALES FOR YEARS")

pt2 = pd.pivot_table(data=datafr,values=['QTY','TOTAL','PRICE'],index=['DATE'],aggfunc={'QTY':np.sum,'TOTAL':np.sum,'PRICE':np.average},fill_value=0)

sns.lineplot(data=pt2)