```python
import pandas as pd
import numpy as np

df = pd.read_excel('D://test/ppp.xlsx',sheet_name="Phylum")
# print(df)


# df1=df.groupby(by='Phylum')
#
# df

# df1.to_excel("D://test//PP_Des.xlsx")
# df['X.mean'] = df['SUM'].mean()
#df.groupby(by='Phylum').describe()
#
# df1 = df.query('Phylum=="p__Firmicutes"')
#
# df1['xi-x'] =np.power(df1['SUM'] - df['X.mean'],2)
# std = np.power(df1['xi-x'].sum()/df['Phylum'].count(),0.5)
# print(std)

# df['S'] = np.power(df['SUM']-df['SUM'].mean(),2)
#
# df['x'] = np.power(df["S"],0.5)/df['Phylum'].count()-1
# print(df['Phylum'].count())
#
#
#
# print(df)


# df1 = df.groupby(by='Phylum').describe()
# df1 = df.groupby(by='Family').describe()
# df1 = df.describe()

df2 = df.groupby (by='Phylum').sum ( )
df2.index
# print(df2.index)
# # print(df2.index)
name = df2.index
# # print(name)
# # print(name)
list1=[]
# print(df['Phylum'])
my = {}
x=pd.DataFrame()
for i in name:
    # print(i)
    df1 = df.query('Phylum=="%s"'%str(i))
    count = df1["Phylum"].count()
    print(df1)
    df1['per'] = df1['SUM'] / df1['SUM'].sum ( )
    df1['X.mean'] = df1['per'].mean()
    df1['(xi-x)^2'] =np.power(df1['per'] - df1['X.mean'],2)
    H = df1['(xi-x)^2'].sum()
    x[i] = H
    df1 = np.power(H/(df1['Phylum'].count()-1),0.5)
    # count =  df1['Phylum'].count( )
    xh = pd.DataFrame({'Phylum': i, 'std': H, 'count': count},index=[0])


    list1.append(xh)
    df4=pd.concat(list1)
df4.to_excel('D://test/xh1.xlsx')
print(xh)
# # df3 = pd.DataFrame.from_dict(my)
# # df3 = pd.DataFrame(my)
#
# # df3 = pd.DataFrame.from_dict(my,orient = 'index')
# print(df4)
# print(list1)
# df3.to_excel ('D://test/xh1.xlsx')
#
# #
# print(df4)

    # df3 = df1.groupby (by='Phylum').describe ( )
#     list1.append(df3)
#     df4=pd.concat(list1)
# df4["all.sum"] = df2['SUM'].sum()
# #
# df4.to_excel ('D://test/xh1.xlsx')
# print(df4)
# # df4= pd.DataFrame(list1)
# # print(df4)
# # df1 = df.query('Phylum=="p__Firmicutes"')
# #
# # df1['per'] = df1['SUM'] /df1['SUM'].sum()
# #
# #
# # df2 = df1.groupby(by='Phylum').describe()
# #
# #
# #
# # # df2 = df.groupby(by='Phylum').describe()
# #
# # # df1['perc'] = df1['SUM']/df1['SUM'].sum()
# # #
# df2.to_excel('D://test/xh2.xlsx')
# df1.to_excel('D://test/xxh.xlsx')