pandas df多层级访问

import numpy as np
import pandas as pd 
from pandas import Series, DataFrame

df1 = pd.read_excel("D:\Desktop\data.xlsx", header=[0,1], index_col=[0])
df1

df1.columns

# 一、构造多层索引

# 方式一：df.MultiIndex.from_product()构造
columns = pd.MultiIndex.from_product([["上半年","下半年"], ["收入", "成本", "费用"]])
index = ["#92", "#95", "#90"]
data = np.random.randint(0,10000,(3,6))
df2 = DataFrame(data,index,columns)
df2

# 方式二：使用数组(arrays)
columns = pd.MultiIndex.from_arrays([["上半年", "上半年", "上半年", "下半年", "下半年", "下半年"], ["收入","成本","费用","收入","成本","费用"]])
index = ["#92", "#95", "#90"]
data = np.random.randint(0,10000,(3,6))
df3 = DataFrame(data,index,columns)
df3

# 方式三：使用元组(tuple)
tuple1 = (("上半年", "收入"), ("上半年", "成本"), ("上半年", "费用"), ("下半年", "收入"), ("下半年", "成本"), ("下半年", "费用"))
columns = pd.MultiIndex.from_tuples(tuple1)
index = ["#92", "#95", "#90"]
data = np.random.randint(0,10000,(3,6))
df4 = DataFrame(data,index,columns)
df4

# 练习：创建一个DataFrame，表示出张三李四期中期末各科成绩（python,java,c）
index = ["张三", "李四"]
columns = pd.MultiIndex.from_product([["期中", "期末"], ["python", "java", "c"]])
data = np.random.randint(0,150,(2,6))
df5 = DataFrame(data,index,columns)
df5

# 将期中期末变为行多层索引
index = pd.MultiIndex.from_product([["期中", "期末"], ["张三", "李四"]])
columns = ["python", "java", "c"]
data = np.random.randint(0,150,(4,3))
df6 = DataFrame(data,index,columns)
df6

# 二、多层级索引的访问与切片
python = df6["python"]  # 返回的是Series
python

python.loc[("期中","张三")]
python.loc[("期中", "张三"): ("期末", "张三")]

# 访问张三期中的java成绩
df6.loc[("期中", "张三"), "java"]

# 也可以赋值
df6.loc[("期中", "张三"), "java"] = 100
df6

# 获取python,java两列

# 直接使用中括号，索引是列索引，切片是行切片
df6[["python", "java"]]

# 这样写会报错，因为是行切片
# df6["python", "java"]  
# 正确写法
df6.loc[:, "python":"java"]

# 获取期中的张三李四成绩
df6.iloc[[0,1]]
df6.loc["期中"]

# 获取期中李四和期末张三的成绩
df6.loc[[("期中", "李四"), ("期末","张三")]]
df6.iloc[[1,2]]

# 练习：假设张三在一次在期中考试的时候因为特殊原因放弃英语考试，如何实现？
df6.loc[("期中", "张三"), "c"] = np.nan
df6

# 多层级索引访问的核心
# 1.多层级的索引的表达方式变成元组
# 2.隐式索引的访问方式不受影响

# 三、变形
df6

# 有两种机制
# 期中  张三
#       李四
# 期末  张三
#       李四
# -2   -1    里层是-1，外层是-2
# 0     1 

# .unstack()变上去到列索引
df7 = df6.unstack(level=-2)  # level默认是-1  
df7

# 将python	java	c变下来到行索引
df7.stack(level=-2)

# 练习
# 1.使用unstack(将ddd变为两行，分别为期中期末
# 2.使用unstack(）将ddd变为四行，分别为四个科目
ddd = df7
ddd

ddd.stack(future_stack=True).unstack(-2)

ddd.stack(-2,future_stack=True).unstack(-2)