Pandas数据规整 - 转换 - 层次化索引

层次化索引（hierarchical indexing）使你能在一个轴上拥有超过1个索引级别

层次化索引能以低维度形式处理高维度数据

In [1]:

import numpy as np
import pandas as pd

In [2]:

data = pd.Series(np.random.randn(9), index=[['a', 'a', 'a', 'b', 'b', 'c', 'c', 'd', 'd'], [1, 2, 3, 1, 3, 1, 2, 2, 3]])
data

Out[2]:

a  1    1.109219
   2   -1.157204
   3   -0.193946
b  1    1.446870
   3    1.178472
c  1   -0.021896
   2    0.517590
d  2   -1.629166
   3   -0.174633
dtype: float64

In [3]:

data.index

Out[3]:

MultiIndex(levels=[['a', 'b', 'c', 'd'], [1, 2, 3]],
           labels=[[0, 0, 0, 1, 1, 2, 2, 3, 3], [0, 1, 2, 0, 2, 0, 1, 1, 2]])

层次化索引的一维数据可以模拟二维数据

In [4]:

data.unstack()

Out[4]:

Series层次化索引的查询

In [5]:

data

Out[5]:

a  1    1.109219
   2   -1.157204
   3   -0.193946
b  1    1.446870
   3    1.178472
c  1   -0.021896
   2    0.517590
d  2   -1.629166
   3   -0.174633
dtype: float64

In [7]:

# 默认索引
data[0]  # 查询单值
data[[0, 3]]  # 查询多值

Out[7]:

a  1    1.109219
b  1    1.446870
dtype: float64

In [10]:

# 外层索引
data['a']  # 查询单值
data[['a', 'c']]  # 查询多值
data['a':'c']  # 切片

Out[10]:

a  1    1.109219
   2   -1.157204
   3   -0.193946
b  1    1.446870
   3    1.178472
c  1   -0.021896
   2    0.517590
dtype: float64

In [13]:

# loc查询
data.loc['a']  # 查询外层索引
data.loc['a', 2]  # 外层、内层
data.loc[:, 2]  # 外层所有，内层2

Out[13]:

a   -1.157204
c    0.517590
d   -1.629166
dtype: float64

将层次化索引的Series转为DataFrame

In [14]:

data

Out[14]:

a  1    1.109219
   2   -1.157204
   3   -0.193946
b  1    1.446870
   3    1.178472
c  1   -0.021896
   2    0.517590
d  2   -1.629166
   3   -0.174633
dtype: float64

In [15]:

data.unstack()

Out[15]:

In [16]:

data.unstack().stack()

Out[16]:

a  1    1.109219
   2   -1.157204
   3   -0.193946
b  1    1.446870
   3    1.178472
c  1   -0.021896
   2    0.517590
d  2   -1.629166
   3   -0.174633
dtype: float64

In [17]:

data

Out[17]:

a  1    1.109219
   2   -1.157204
   3   -0.193946
b  1    1.446870
   3    1.178472
c  1   -0.021896
   2    0.517590
d  2   -1.629166
   3   -0.174633
dtype: float64

In [22]:

# 交换索引顺序
data.unstack().unstack().dropna()
data.unstack().T.stack()

Out[22]:

1  a    1.109219
   b    1.446870
   c   -0.021896
2  a   -1.157204
   c    0.517590
   d   -1.629166
3  a   -0.193946
   b    1.178472
   d   -0.174633
dtype: float64

DataFrame层次化索引

In [23]:

frame = pd.DataFrame(
    np.arange(12).reshape((4, 3)),
    index=[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],
    columns=[['Ohio', 'Ohio', 'Colorado'],['Green', 'Red', 'Green']],
)
frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
frame

Out[23]:

image.png

In [24]:

frame.index
frame.columns

Out[24]:

MultiIndex(levels=[['Colorado', 'Ohio'], ['Green', 'Red']],
           labels=[[1, 1, 0], [0, 1, 0]],
           names=['state', 'color'])

DataFrame层次化索引查询

In [25]:

frame

Out[25]:

image.png

In [26]:

frame.loc['a']  # 外层行索引

Out[26]:

image.png

In [35]:

# 外层行索引，内层行索引
frame.loc['a', 2]

Out[35]:

state     color
Ohio      Green    3
          Red      4
Colorado  Green    5
Name: (a, 2), dtype: int32

In [27]:

frame.loc['a', 'Ohio']  # 外层行，外层列

Out[27]:

In [28]:

# 外层列，内层列
frame['Ohio', 'Green']

Out[28]:

key1  key2
a     1       0
      2       3
b     1       6
      2       9
Name: (Ohio, Green), dtype: int32

In [29]:

frame

Out[29]:

image.png

综合应用

In [33]:

# 外层行，内层行，外层列，内层列
frame.loc['a', 1]['Ohio', 'Red']

# 外层行，外层列，内层行，内层列
frame.loc['a', 'Ohio'].loc[1, 'Green']

Out[33]:

0

重排与分级排序

调整某条轴上各级别的顺序，或根据指定级别上的值对数据进行排序

In [44]:

frame

Out[44]:

image.png

In [59]:

# 行索引交换层级
frame.swaplevel()
frame.swaplevel('key1', 'key2')
frame.swaplevel('key2', 'key1')
frame.swaplevel(1, 0)
frame.swaplevel(0, 1)

Out[59]:

image.png

In [60]:

# 列索引交换层级
frame.swaplevel(axis=1)

Out[60]:

image.png

按索引层级排序

In [48]:

frame.sort_index(ascending=False)  # 行索引排序

Out[48]:

image.png

In [49]:

frame.sort_index(ascending=False, level='key2')  # 排序索引层级

Out[49]:

image.png

In [50]:

frame.sort_index(ascending=False, axis=1)  # 列索引排序

Out[50]:

image.png

根据级别汇总统计

许多对DataFrame和Series的描述和汇总统计函数都有一个level选项，它用于指定在某条轴上计算的级别

其实是利用了pandas的groupby功能

In [51]:

frame

Out[51]:

image.png

In [61]:

frame.sum()  # 按行求和

Out[61]:

state     color
Ohio      Green    18
          Red      22
Colorado  Green    26
dtype: int64

In [53]:

frame.sum(level='key1'))  # 以key1索引分组求和

Out[53]:

image.png

In [64]:

# 用groupby实现

# 以外层行索引为分组基准
frame.groupby('key1').sum()  # 分组基准，行索引name
frame.groupby(level='key1').sum()  # level传入分组基准
frame.groupby(['a', 'a', 'b', 'b']).sum()  # 手动构造分组基准

Out[64]:

In [66]:

# 以内层行索引为分组基准
frame.groupby('key2').sum()  # 分组基准，行索引name
frame.groupby(level='key2').sum()  # level传入分组基准
frame.groupby([1,2,1,2]).sum()  # 手动构造分组基准

Out[66]:

按列求和

In [57]:

frame

Out[57]:

image.png

In [37]:

frame.sum()  # 按行求和

Out[37]:

state     color
Ohio      Green    18
          Red      22
Colorado  Green    26
dtype: int64

In [58]:

frame.sum(axis=1)   # 按列求和

Out[58]:

key1  key2
a     1        3
      2       12
b     1       21
      2       30
dtype: int64

以内层列索引为基准实现

In [67]:

frame.sum(axis=1, level='color')  # 两个 Green 相加

Out[67]:

image.png

In [60]:

# 用分组实现
frame.groupby(['Green', 'Red', 'Green'], axis=1).sum()
frame.groupby(axis=1, level='color').sum()

Out[60]:

image.png

以外层列索引为基准

In [62]:

frame

Out[62]:

image.png

In [68]:

frame.sum(axis=1, level='state')  # Ohio下的两列相加

Out[68]:

image.png

In [73]:

frame.groupby(axis=1, level='state').sum().sort_index(ascending=False, axis=1)

# 报错，直接传入分组索引值，默认使用最里层列索引
# frame.groupby(['Ohio', 'Ohio', 'Colorado'], axis=1).sum().sort_index(ascending=False, axis=1)
# 列索引交换层级
frame.swaplevel(axis=1)
frame.swaplevel(axis=1).groupby(['Ohio', 'Ohio', 'Colorado'], axis=1).sum().sort_index(ascending=False, axis=1)

Out[73]:

image.png

使用DataFrame的列或行进行索引

将DataFrame的一个或多个列当做行索引来用，或者将行索引变成DataFrame的列

In [74]:

frame2 = pd.DataFrame(
    {'a': range(7), 'b': range(7, 0, -1),
     'c': ['one', 'one', 'one', 'two', 'two', 'two', 'two'],
     'd': [0, 1, 2, 0, 1, 2, 3]}
)
frame2

Out[74]:

将普通列转为行索引

In [75]:

frame2.set_index('a')
frame3 = frame2.set_index(['c', 'd'])
frame3

Out[75]:

image.png

In [76]:

frame2.set_index(['c', 'd'], append=True)  # 增加索引，非替换，保留原索引

Out[76]:

In [77]:

frame2.set_index(['c', 'd'], drop=False)  # 列转索引后，保留原列

Out[77]:

image.png

行索引转为普通列

In [78]:

frame3

Out[78]:

image.png

In [79]:

frame3.reset_index()

Out[79]:

转换列索引

In [53]:

frame2

Out[53]:

In [82]:

# 先将原表转置，修改行索引后，再转置
frame2.T.set_index(0, append=True).T

Out[82]: