import pandas as pd
import numpy as np
Filename = r'F:\信科工作文件\课件\数据科学入门课件\alco_2.xlsx'
df = pd.DataFrame(pd.read_excel(Filename))#读入第一列数据——State
df_index=df['State']#去掉州列表中的重复项
df_index = df_index.drop_duplicates(keep='first')#取后三列的数据
df_data=df[['Beer','Wine','Spirits']]#生成年份列表
year_list =list(range(2009,1976,-1))#由州名和年份生成二维元组((state,year))
mul_tuple =tuple([(i,j)for i in df_index for j in year_list])#将保存的DataFrame格式的数据df_data转换成元组列表的格式,以便创建多层索引
data =[(df['Beer'][i],df['Wine'][i],df['Spirits'][i])for i inrange(0,1683)]#生成多层索引
alco = pd.DataFrame(data,columns=('Beer','Wine','Spirits'),
index=pd.MultiIndex.from_tuples(mul_tuple))
alco.index.names=["State","Year"]