第08章 数据规整：聚合、合并和重塑
8.1 层次化索引
import pandas as pd
import numpy as np
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]])
print(data)
data.index
a 1 1.134771
2 -0.654912
3 0.048867
b 1 -1.380427
3 -0.231125
c 1 1.131460
2 1.109319
d 2 0.064049
3 -1.437599
dtype: float64
MultiIndex([('a', 1),
('a', 2),
('a', 3),
('b', 1),
('b', 3),
('c', 1),
('c', 2),
('d', 2),
('d', 3)],
)
#可以对数据进行多层次的选择
print(data['b':'c'])
print('\n')
print(data.loc[['b', 'd']])
print('\n')
print(data.loc[:, 2])
b 1 -1.380427
3 -0.231125
c 1 1.131460
2 1.109319
dtype: float64
b 1 -1.380427
3 -0.231125
d 2 0.064049
3 -1.437599
dtype: float64
a -0.654912
c 1.109319
d 0.064049
dtype: float64
#可以通过unstack方法将这段数据重新安排到1个DataFrame中
print(data.unstack())
1 2 3
a 1.134771 -0.654912 0.048867
b -1.380427 NaN -0.231125
c 1.131460 1.109319 NaN
d NaN 0.064049 -1.437599
#unstack的逆运算是stack：
print(data.unstack().stack())
a 1 1.134771
2 -0.654912
3 0.048867
b 1 -1.380427
3 -0.231125
c 1 1.131460
2 1.109319
d 2 0.064049
3 -1.437599
dtype: float64
#对于1个DataFrame，每条轴都可以有分层索引
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']])
print(frame)
Ohio Colorado
Green Red Green
a 1 0 1 2
2 3 4 5
b 1 6 7 8
2 9 10 11
#如果指定了名称，它们就会显示在控制台输出中：
frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
print(frame)
state Ohio Colorado
color Green Red Green
key1 key2
a 1 0 1 2
2 3 4 5
b 1 6 7 8
2 9 10 11
重排与分级
state Ohio Colorado
color Green Red Green
key2 key1
1 a 0 1 2
2 a 3 4 5
1 b 6 7 8
2 b 9 10 11
#sort_index则根据单个级别中的值对数据进行排序。
print(frame.sort_index(level=1))
print('\n')
print(frame.swaplevel(0, 1).sort_index(level=0))
state Ohio Colorado
color Green Red Green
key1 key2
a 1 0 1 2
b 1 6 7 8
a 2 3 4 5
b 2 9 10 11
state Ohio Colorado
color Green Red Green
key2 key1
1 a 0 1 2
b 6 7 8
2 a 3 4 5
b 9 10 11
根据级别汇总统计
对DataFrame和Series的描述和汇总统计都有1个level选项，它用于指定在某条轴上求和的级
别。
print(frame.sum(level='key2'))
print('\n')
print(frame.sum(level='color', axis=1))
state Ohio Colorado
color Green Red Green
key2
1 6 8 10
2 12 14 16
color Green Red
key1 key2
a 1 2 1
2 8 4
b 1 14 7
2 20 10
使用DataFrame的列进行索引
frame = 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]})
print(frame)
a b c d
0 0 7 one 0
1 1 6 one 1
2 2 5 one 2
3 3 4 two 0
4 4 3 two 1
5 5 2 two 2
6 6 1 two 3
#DataFrame的set_index函数会将其1个或多个列转换为行索引，并创建1个新的DataFrame
frame2 = frame.set_index(['c', 'd'])
print(frame2)
print('\n')
print( frame.set_index(['c', 'd'], drop=False))
a b
c d
one 0 0 7
1 1 6
2 2 5
two 0 3 4
1 4 3
2 5 2
3 6 1
a b c d
c d
one 0 0 7 one 0
1 1 6 one 1
2 2 5 one 2
two 0 3 4 two 0
1 4 3 two 1
2 5 2 two 2
3 6 1 two 3
#reset_index的功能跟set_index刚好相反，层次化索引的级别会被转移到列
print(frame2.reset_index())
c d a b
0 one 0 0 7
1 one 1 1 6
2 one 2 2 5
3 two 0 3 4
4 two 1 4 3
5 two 2 5 2
6 two 3 6 1
合并数据集
pandas对象中的数据可以通过1些方式进行合并:
pandas.merge可根据1个或多个键将不同DataFrame中的行连接起来。SQL或其他关系型数据库
的用户对此应该会比较熟悉，因为它实现的就是数据库的join操作。
pandas.concat可以沿着1条轴将多个对象堆叠到1起。
实例方法combine_first可以将重复数据拼接在1起，用一个对象中的值填充另1个对象中的缺失值。
数据库风格的DataFrame合并
数据集的合并（merge）或连接（join）运算是通过1个或多个键将行连接起来的。
df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
'data1': range(7)})
df2 = pd.DataFrame({'key': ['a', 'b', 'd'],
'data2': range(3)})
print(df1)
print('\n')
print(df2)
print('\n')
print( pd.merge(df1, df2))
key data1
0 b 0
1 b 1
2 a 2
3 c 3
4 a 4
5 a 5
6 b 6
key data2
0 a 0
1 b 1
2 d 2
key data1 data2
0 b 0 1
1 b 1 1
2 b 6 1
3 a 2 0
4 a 4 0
5 a 5 0
#如果没有指定，merge就会将重叠列的列名当做键
#可以指明一下的。
print(pd.merge(df1, df2, on='key'))
key data1 data2
0 b 0 1
1 b 1 1
2 b 6 1
3 a 2 0
4 a 4 0
5 a 5 0
df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'],
'data1': range(7)})
df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'],
'data2': range(3)})
print(pd.merge(df3, df4, left_on='lkey', right_on='rkey'))
lkey data1 rkey data2
0 b 0 b 1
1 b 1 b 1
2 b 6 b 1
3 a 2 a 0
4 a 4 a 0
5 a 5 a 0
#认情况下，merge做的是“内连接”；结果中的键是交集。其他方式还有"left"、"right"以及"outer"。
#how选项，说明
#inner；使用两个表都有的键
#left：使用左表中的所有的键
#right：使用右表中所有的键
#outer：使用两个表中所有的键
df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'],
'data1': range(6)})
df2 = pd.DataFrame({'key': ['a', 'b', 'a', 'b', 'd'],
'data2': range(5)})
print(df1)
print('\n')
print(df2)
print('\n')
print(pd.merge(df1, df2, on='key', how='left'))
key data1
0 b 0
1 b 1
2 a 2
3 c 3
4 a 4
5 b 5
key data2
0 a 0
1 b 1
2 a 2
3 b 3
4 d 4
key data1 data2
0 b 0 1.0
1 b 0 3.0
2 b 1 1.0
3 b 1 3.0
4 a 2 0.0
5 a 2 2.0
6 c 3 NaN
7 a 4 0.0
8 a 4 2.0
9 b 5 1.0
10 b 5 3.0
left = pd.DataFrame({'key1': ['foo', 'foo', 'bar'],
'key2': ['one', 'two', 'one'],
'lval': [1, 2, 3]})
right = pd.DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],
'key2': ['one', 'one', 'one', 'two'],
'rval': [4, 5, 6, 7]})
print( pd.merge(left, right, on=['key1', 'key2'], how='outer'))
key1 key2 lval rval
0 foo one 1.0 4.0
1 foo one 1.0 5.0
2 foo two 2.0 NaN
3 bar one 3.0 6.0
4 bar two NaN 7.0
merge函数的参数如下：
索引上的合并
可以传入left_index=True或right_index=True（或两个都传）以说明索引应该被用作连接键.
DataFrame还有1个便捷的join实例方法，它能更为方便地实现按索引合并。它还可用于合并多个带有相同或相似索引的DataFrame对象，但要求没有重叠的列。
left1 = pd.DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'],
'value': range(6)})
right1 = pd.DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])
print(pd.merge(left1, right1, left_on='key', right_index=True))
print('\n')
print( pd.merge(left1, right1, left_on='key', right_index=True, how='outer'))
key value group_val
0 a 0 3.5
2 a 2 3.5
3 a 3 3.5
1 b 1 7.0
4 b 4 7.0
key value group_val
0 a 0 3.5
2 a 2 3.5
3 a 3 3.5
1 b 1 7.0
4 b 4 7.0
5 c 5 NaN
left2 = pd.DataFrame([[1., 2.], [3., 4.], [5., 6.]],
index=['a', 'c', 'e'],
columns=['Ohio', 'Nevada'])
right2 = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [13, 14]],
index=['b', 'c', 'd', 'e'],
columns=['Missouri', 'Alabama'])
another = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]],
index=['a', 'c', 'e', 'f'],
columns=['New York','Oregon'])
print( left2.join([right2, another]))
print('\n')
print( left2.join([right2, another], how='outer'))
Ohio Nevada Missouri Alabama New York Oregon
a 1.0 2.0 NaN NaN 7.0 8.0
c 3.0 4.0 9.0 10.0 9.0 10.0
e 5.0 6.0 13.0 14.0 11.0 12.0
Ohio Nevada Missouri Alabama New York Oregon
a 1.0 2.0 NaN NaN 7.0 8.0
c 3.0 4.0 9.0 10.0 9.0 10.0
e 5.0 6.0 13.0 14.0 11.0 12.0
b NaN NaN 7.0 8.0 NaN NaN
d NaN NaN 11.0 12.0 NaN NaN
f NaN NaN NaN NaN 16.0 17.0
轴向连接
NumPy的concatenation函数可以用NumPy数组来做
arr = np.arange(12).reshape((3, 4))
print(np.concatenate([arr, arr], axis=1))
[[ 0 1 2 3 0 1 2 3]
[ 4 5 6 7 4 5 6 7]
[ 8 9 10 11 8 9 10 11]]
s1 = pd.Series([0, 1], index=['a', 'b'])
s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])
s3 = pd.Series([5, 6], index=['f', 'g'])
print(pd.concat([s1, s2, s3]))
a 0
b 1
c 2
d 3
e 4
f 5
g 6
dtype: int64
#如果传入axis=1，则结果就会变成1个DataFrame（axis=1是列）
print( pd.concat([s1, s2, s3], axis=1))
0 1 2
a 0.0 NaN NaN
b 1.0 NaN NaN
c NaN 2.0 NaN
d NaN 3.0 NaN
e NaN 4.0 NaN
f NaN NaN 5.0
g NaN NaN 6.0
E:\anaconda\lib\site-packages\ipykernel_launcher.py:2: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.
To accept the future behavior, pass 'sort=False'.
To retain the current behavior and silence the warning, pass 'sort=True'.
s4 = pd.concat([s1, s3])
print(pd.concat([s1, s4], axis=1, join_axes=[['a', 'c', 'b', 'e']]))
print('\n')
result = pd.concat([s1, s1, s3], keys=['one','two', 'three'])
print(result )
0 1
a 0.0 0.0
c NaN NaN
b 1.0 1.0
e NaN NaN
one a 0
b 1
two a 0
b 1
three f 5
g 6
dtype: int64
E:\anaconda\lib\site-packages\ipykernel_launcher.py:2: FutureWarning: The join_axes-keyword is deprecated. Use .reindex or .reindex_like on the result to achieve the same functionality.
df1 = pd.DataFrame(np.random.randn(3, 4), columns=['a', 'b', 'c', 'd'])
df2 = pd.DataFrame(np.random.randn(2, 3), columns=['b', 'd', 'a'])
print( pd.concat([df1, df2], ignore_index=True))
a b c d
0 0.939916 0.058807 0.934696 0.423455
1 2.174978 -0.967914 -1.201105 0.652898
2 -0.954785 -0.290665 -1.765156 0.421590
3 -0.884476 -1.116925 NaN 0.022045
4 0.642564 -1.195369 NaN 0.862140
E:\anaconda\lib\site-packages\ipykernel_launcher.py:3: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.
To accept the future behavior, pass 'sort=False'.
To retain the current behavior and silence the warning, pass 'sort=True'.
This is separate from the ipykernel package so we can avoid doing imports until
合并重叠数据
a = pd.Series([np.nan, 2.5, np.nan, 3.5, 4.5, np.nan],
index=['f', 'e', 'd', 'c', 'b', 'a'])
b = pd.Series(np.arange(len(a), dtype=np.float64),
index=['f', 'e', 'd', 'c', 'b', 'a'])
b[-1] = np.nan
np.where(pd.isnull(a), b, a)
array([0. , 2.5, 2. , 3.5, 4.5, nan])
a NaN
b 4.5
c 3.0
d 2.0
e 1.0
f 0.0
dtype: float64
8.3 重塑和轴向旋转
重塑层次化索引
stack：将数据的列“旋转”为行。
unstack：将数据的⾏“旋转”为列。
data = pd.DataFrame(np.arange(6).reshape((2, 3)),
index=pd.Index(['Ohio','Colorado'], name='state'),
columns=pd.Index(['one', 'two', 'three'],
name='number'))
print(data)
result = data.stack()
print('\n')
print(result)
number one two three
state
Ohio 0 1 2
Colorado 3 4 5
state number
Ohio one 0
two 1
three 2
Colorado one 3
two 4
three 5
dtype: int32
s1 = pd.Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])
s2 = pd.Series([4, 5, 6], index=['c', 'd', 'e'])
data2 = pd.concat([s1, s2], keys=['one', 'two'])
print(data2)
print('\n')
print(data2.unstack())
print('\n')
print(data2.unstack().stack())
one a 0
b 1
c 2
d 3
two c 4
d 5
e 6
dtype: int64
a b c d e
one 0.0 1.0 2.0 3.0 NaN
two NaN NaN 4.0 5.0 6.0
one a 0.0
b 1.0
c 2.0
d 3.0
two c 4.0
d 5.0
e 6.0
dtype: float64
将“长格式”旋转为“宽格式”
data = pd.read_csv('examples/macrodata.csv')
print(data.head())
year quarter realgdp realcons realinv realgovt realdpi cpi \
0 1959.0 1.0 2710.349 1707.4 286.898 470.045 1886.9 28.98
1 1959.0 2.0 2778.801 1733.7 310.859 481.301 1919.7 29.15
2 1959.0 3.0 2775.488 1751.8 289.226 491.260 1916.4 29.35
3 1959.0 4.0 2785.204 1753.7 299.356 484.052 1931.3 29.37
4 1960.0 1.0 2847.699 1770.5 331.722 462.199 1955.5 29.54
m1 tbilrate unemp pop infl realint
0 139.7 2.82 5.8 177.146 0.00 0.00
1 141.7 3.08 5.1 177.830 2.34 0.74
2 140.5 3.82 5.3 178.657 2.74 1.09
3 140.0 4.33 5.6 179.386 0.27 4.06
4 139.6 3.50 5.2 180.007 2.31 1.19
periods = pd.PeriodIndex(year=data.year, quarter=data.quarter,
name='date')
columns = pd.Index(['realgdp', 'infl', 'unemp'], name='item')
data = data.reindex(columns=columns)
data.index = periods.to_timestamp('D', 'end')
ldata = data.stack().reset_index().rename(columns={0: 'value'})
pivoted = ldata.pivot('date', 'item', 'value')
print(pivoted)
item infl realgdp unemp
date
1959-03-31 23:59:59.999999999 0.00 2710.349 5.8
1959-06-30 23:59:59.999999999 2.34 2778.801 5.1
1959-09-30 23:59:59.999999999 2.74 2775.488 5.3
1959-12-31 23:59:59.999999999 0.27 2785.204 5.6
1960-03-31 23:59:59.999999999 2.31 2847.699 5.2
... ... ... ...
2008-09-30 23:59:59.999999999 -3.16 13324.600 6.0
2008-12-31 23:59:59.999999999 -8.79 13141.920 6.9
2009-03-31 23:59:59.999999999 0.94 12925.410 8.1
2009-06-30 23:59:59.999999999 3.37 12901.504 9.2
2009-09-30 23:59:59.999999999 3.56 12990.341 9.6
[203 rows x 3 columns]
ldata['value2'] = np.random.randn(len(ldata))
print(ldata[:10])
date item value value2
0 1959-03-31 23:59:59.999999999 realgdp 2710.349 1.227679
1 1959-03-31 23:59:59.999999999 infl 0.000 0.659434
2 1959-03-31 23:59:59.999999999 unemp 5.800 0.834788
3 1959-06-30 23:59:59.999999999 realgdp 2778.801 -0.311874
4 1959-06-30 23:59:59.999999999 infl 2.340 -1.235061
5 1959-06-30 23:59:59.999999999 unemp 5.100 1.055687
6 1959-09-30 23:59:59.999999999 realgdp 2775.488 0.856784
7 1959-09-30 23:59:59.999999999 infl 2.740 -0.009388
8 1959-09-30 23:59:59.999999999 unemp 5.300 0.661178
9 1959-12-31 23:59:59.999999999 realgdp 2785.204 0.116387
将“宽格式”旋转为“长格式”
df = pd.DataFrame({'key': ['foo', 'bar', 'baz'],
'A': [1, 2, 3],
'B': [4, 5, 6],
'C': [7, 8, 9]})
print(df)
key A B C
0 foo 1 4 7
1 bar 2 5 8
2 baz 3 6 9
#旋转DataFrame的逆运算是pandas.melt。当使用pandas.melt，我们必须指明哪些列是分组指标。
#下面使用key作为唯一的分组指标：
melted = pd.melt(df, ['key'])
print(melted)
key variable value
0 foo A 1
1 bar A 2
2 baz A 3
3 foo B 4
4 bar B 5
5 baz B 6
6 foo C 7
7 bar C 8
8 baz C 9
#使用pivot，可以重塑回原来的样子
reshaped = melted.pivot('key', 'variable', 'value')
print(reshaped)
variable A B C
key
bar 2 5 8
baz 3 6 9
foo 1 4 7
data200=pd.melt(df, id_vars=['key'], value_vars=['A', 'B'])
print(data200)
key variable value
0 foo A 1
1 bar A 2
2 baz A 3
3 foo B 4
4 bar B 5
5 baz B 6