1.缺失值处理 - 拉格朗日插值法
input_file数据文件内容（存在部分缺失值）：
from scipy.interpolate import lagrange import pandas as pd input_file = ‘./data/catering_sale.xls‘ output_file = ‘./data/sales.xls‘ data = pd.read_excel(input_file) data[‘销量‘][(data[‘销量‘] < 400) | (data[‘销量‘] > 5000)] = None # 销量小于400及大于5000的视为异常值，置为None # 自定义列向量插值函数 # 问题：当n<k时，list(range(n-k, n))会出现负数，导致y的值出现空值，会影响最终的插值结果，这个问题还未解决。。。 def ployinterp_column(s, n, k=5): # s为列向量，n为被插值的位置，k为取前后的数据个数，默认为5 y = s[list(range(n-k, n)) + list(range(n+1, n+k+1))] y = y[y.notnull()] # 剔除空值 if n-k < 0: # 如果NaN值在前5位，则插值结果取k-n位 return lagrange(y.index, list(y))(k-n) else: return lagrange(y.index, list(y))(n) # 插值并返回插值结果 # 逐个元素判断是否需要插值 for j in range(len(data)): if (data[‘销量‘].isnull())[j]: # 如果元素为空，则进行插值 data[‘销量‘][j] = ployinterp_column(data[‘销量‘], j) data.to_excel(output_file)
output_file结果：
# np.where() 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‘]) # 如果a有缺失值，则用相应位置的b填充，否则使用a的原有元素 print(np.where(pd.isnull(a), b, a)) # result [ 0. 2.5 2. 3.5 4.5 5. ]
# df.combine_first() df1 = pd.DataFrame({‘a‘: [1., np.nan, 5., np.nan], ‘b‘: [np.nan, 2., np.nan, 6.], ‘c‘: range(2, 18, 4)}) df2 = pd.DataFrame({‘a‘: [5., 4., np.nan, 3., 7.], ‘b‘: [np.nan, 3., 4., 6., 8.]}) # 将df1中的缺失值用df2中相同位置的元素填充，如果没有缺失值则保持df1的原有元素 df1.combine_first(df2) # result a b c 0 1.0 NaN 2.0 1 4.0 2.0 6.0 2 5.0 4.0 10.0 3 3.0 6.0 14.0 4 7.0 8.0 NaN
2.数据合并：
# pd.merge() # 使用列或者索引，以类似数据库连接的方式合并多个DataFrame对象 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(pd.merge(df1, df2)) # 自动匹配合并列， 默认内连接 print(pd.merge(df1, df2, on=‘key‘)) # 显式指定
# result
data1 key data2
0 0 b 1
1 1 b 1
2 6 b 1
3 2 a 0
4 4 a 0
5 5 a 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‘)) # 当不存在相同column时，需要分别指定连接列名
# result
data1 lkey data2 rkey
0 0 b 1 b
1 1 b 1 b
2 6 b 1 b
3 2 a 0 a
4 4 a 0 a
5 5 a 0 a
## 指定连接方式 # 外连接 print(pd.merge(df1, df2, how=‘outer‘)) # result data1 key data2 0 0.0 b 1.0 1 1.0 b 1.0 2 6.0 b 1.0 3 2.0 a 0.0 4 4.0 a 0.0 5 5.0 a 0.0 6 3.0 c NaN 7 NaN d 2.0
# 左连接 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(pd.merge(df1, df2, how=‘left‘)) # result data1 key data2 0 0 b 1.0 1 0 b 3.0 2 1 b 1.0 3 1 b 3.0 4 2 a 0.0 5 2 a 2.0 6 3 c NaN 7 4 a 0.0 8 4 a 2.0 9 5 b 1.0 10 5 b 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‘])) # 默认内连接 # result key1 key2 lval rval 0 foo one 1 4 1 foo one 1 5 2 bar one 3 6 print(pd.merge(left, right, on=[‘key1‘, ‘key2‘], how=‘outer‘)) # 外连接 # result 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
# 只以其中一个列连接，会出现冗余列 pd.merge(left, right, on=‘key1‘) # result key1 key2_x lval key2_y rval 0 foo one 1 one 4 1 foo one 1 one 5 2 foo two 2 one 4 3 foo two 2 one 5 4 bar one 3 one 6 5 bar one 3 two 7 print(pd.merge(left, right, on=‘key1‘, suffixes=(‘_left‘, ‘_right‘))) # 给冗余列增加后缀 # result key1 key2_left lval key2_right rval 0 foo one 1 one 4 1 foo one 1 one 5 2 foo two 2 one 4 3 foo two 2 one 5 4 bar one 3 one 6 5 bar one 3 two 7
# 使用索引与列进行合并 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)) # left1使用key列连接，right1使用index列连接 # result 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
# 多列索引连接 lefth = pd.DataFrame({‘key1‘: [‘Ohio‘, ‘Ohio‘, ‘Ohio‘, ‘Nevada‘, ‘Nevada‘], ‘key2‘: [2000, 2001, 2002, 2001, 2002], ‘data‘: np.arange(5.)}) righth = pd.DataFrame(np.arange(12).reshape((6, 2)), index=[[‘Nevada‘, ‘Nevada‘, ‘Ohio‘, ‘Ohio‘, ‘Ohio‘, ‘Ohio‘], [2001, 2000, 2000, 2000, 2001, 2002]], columns=[‘event1‘, ‘event2‘]) print(pd.merge(lefth, righth, left_on=[‘key1‘, ‘key2‘], right_index=True)) # result
data key1 key2 event1 event2
0 0.0 Ohio 2000 4 5
0 0.0 Ohio 2000 6 7
1 1.0 Ohio 2001 8 9
2 2.0 Ohio 2002 10 11
3 3.0 Nevada 2001 0 1
# pd.join()
# pd.join()可以使用index或key合并两个及以上的DataFrame（列方向上的合并）
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‘]) print(left2.join(right2, how=‘outer‘)) # result Ohio Nevada Missouri Alabama a 1.0 2.0 NaN NaN b NaN NaN 7.0 8.0 c 3.0 4.0 9.0 10.0 d NaN NaN 11.0 12.0 e 5.0 6.0 13.0 14.0
# 合并多个DataFrame another = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [16., 17.]], index=[‘a‘, ‘c‘, ‘e‘, ‘f‘], columns=[‘New York‘, ‘Oregon‘]) left2.join([right2, another], how=‘outer‘) # result Ohio Nevada Missouri Alabama New York Oregon a 1.0 2.0 NaN NaN 7.0 8.0 b NaN NaN 7.0 8.0 NaN NaN c 3.0 4.0 9.0 10.0 9.0 10.0 d NaN NaN 11.0 12.0 NaN NaN e 5.0 6.0 13.0 14.0 11.0 12.0 f NaN NaN NaN NaN 16.0 17.0
# 轴向连接 import numpy as np # np.concatenate() arr = np.arange(12).reshape((3,4)) print(np.concatenate([arr, arr], axis=1)) # 在column方向上连接 # result array([[ 0, 1, 2, ..., 1, 2, 3], [ 4, 5, 6, ..., 5, 6, 7], [ 8, 9, 10, ..., 9, 10, 11]])
# pd.concat() 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])) # axis参数默认为0，row方向的 # result a 0 b 1 c 2 d 3 e 4 f 5 g 6 dtype: int64 print(pd.concat([s1, s2, s3], axis=1)) # column方向合并，值如果不存在则记为NaN # result 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 s4 = pd.concat([s1 * 5, s3]) s5 = pd.concat([s1, s4], axis=1) s5.columns = [‘s1‘, ‘s4‘] print(s5) # result s1 s4 a 0.0 0 b 1.0 5 f NaN 5 g NaN 6 print(pd.concat([s1, s4], axis=1, join=‘inner‘)) # join参数指定连接方式 # result 0 1 a 0 0 b 1 5 print(pd.concat([s1, s4], axis=1, join_axes=[[‘a‘, ‘c‘, ‘b‘, ‘e‘]])) # 手动指定要连接的index # result 0 1 a 0.0 0.0 c NaN NaN b 1.0 5.0 e NaN NaN
# 使用keys参数对索引进行分级 result = pd.concat([s1, s2, s3], keys=[‘one‘, ‘two‘, ‘three‘]) # 在row方向合并时，keys对应每个Series的一级index，每个Series原有的index则作为二级index print(result) # result one a 0 b 1 two c 2 d 3 e 4 three f 5 g 6 dtype: int64
# Series.unstack() 将Seris格式转换为DataFrame格式 print(result.unstack()) # 一级索引将作为index，二级索引作为columns # result a b c d e f g one 0.0 1.0 NaN NaN NaN NaN NaN two NaN NaN 2.0 3.0 4.0 NaN NaN three NaN NaN NaN NaN NaN 5.0 6.0
# 在列合并时使用keys参数指定column名称 print(pd.concat([s1, s2, s3], axis=1, keys=[‘one‘, ‘two‘, ‘three‘])) # 在column方向合并时，keys对应每个合并的Series的column # result one two three 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
# 指定分级column df1 = pd.DataFrame(np.arange(6).reshape(3, 2), index=[‘a‘, ‘b‘, ‘c‘], columns=[‘one‘, ‘two‘]) df2 = pd.DataFrame(5 + np.arange(4).reshape(2, 2), index=[‘a‘, ‘c‘], columns=[‘three‘, ‘four‘])
# 因为DataFrame对象已经有了column，所以keys参数会设置新的一级column， df原有的column则作为二级column df3 = pd.concat([df1, df2], axis=1, keys=[‘level1‘, ‘level2‘]) print(df3) print(df3.columns) # result level1 level2 one two three four a 0 1 5.0 6.0 b 2 3 NaN NaN c 4 5 7.0 8.0 MultiIndex(levels=[[‘level1‘, ‘level2‘], [‘four‘, ‘one‘, ‘three‘, ‘two‘]], labels=[[0, 0, 1, 1], [1, 3, 2, 0]]) # 使用字典实现相同的功能 print(pd.concat({‘level1‘: df1, ‘level2‘: df2}, axis=1)) #result level1 level2 one two three four a 0 1 5.0 6.0 b 2 3 NaN NaN c 4 5 7.0 8.0 # 指定分级column名称 df = pd.concat([df1, df2], axis=1, keys=[‘level1‘, ‘level2‘], names=[‘levels‘, ‘number‘]) print(df) print(df.columns) # result levels level1 level2 number one two three four a 0 1 5.0 6.0 b 2 3 NaN NaN c 4 5 7.0 8.0 MultiIndex(levels=[[‘level1‘, ‘level2‘], [‘four‘, ‘one‘, ‘three‘, ‘two‘]], labels=[[0, 0, 1, 1], [1, 3, 2, 0]], names=[‘levels‘, ‘number‘])
# ignore_index 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‘]) # row方向忽略索引 print(pd.concat([df1, df2], ignore_index=True)) # result a b c d 0 1.261208 0.022188 -2.489475 -1.098245 1 0.618618 -1.179827 1.475738 0.334444 2 -0.319088 -0.153492 0.029245 0.336055 3 -0.999023 -0.502154 NaN 0.722256 4 1.428007 -0.726810 NaN 0.432440 # column方向忽略列名 print(pd.concat([df1, df2], axis=1, ignore_index=True)) # result 0 1 2 3 4 5 6 0 1.261208 0.022188 -2.489475 -1.098245 -0.502154 0.722256 -0.999023 1 0.618618 -1.179827 1.475738 0.334444 -0.726810 0.432440 1.428007 2 -0.319088 -0.153492 0.029245 0.336055 NaN NaN NaN
3.重塑层次化索引
data = pd.DataFrame(np.arange(6).reshape((2, 3)), index=pd.Index([‘Ohio‘, ‘Colorado‘], name=‘state‘), columns=pd.Index([‘one‘, ‘two‘, ‘three‘], name=‘number‘)) # 轴向旋转 result = data.stack() print(result) # result state number Ohio one 0 two 1 three 2 Colorado one 3 two 4 three 5 # 还原操作 print(result.unstack()) # result number one two three state Ohio 0 1 2 Colorado 3 4 5 # 行列转置 print(result.unstack(0)) # result state Ohio Colorado number one 0 3 two 1 4 three 2 5 # 指定要转置的索引名 print(result.unstack(‘number‘)) # result number one two three state Ohio 0 1 2 Colorado 3 4 5
# 例1：
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.unstack()) # result a b c d e one 0.0 1.0 2.0 3.0 NaN two NaN NaN 4.0 5.0 6.0 print(data2.unstack().stack()) # result 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 # 不dropnan值 print(data2.unstack().stack(dropna=False)) # result one a 0.0 b 1.0 c 2.0 d 3.0 e NaN two a NaN b NaN c 4.0 d 5.0 e 6.0 dtype: float64
# 例2：
df = pd.DataFrame({‘left‘: result, ‘right‘: result + 5},
columns=pd.Index([‘left‘, ‘right‘], name=‘side‘))
print(df.unstack(‘state‘))
# result
side left right
state Ohio Colorado Ohio Colorado
number
one 0 3 5 8
two 1 4 6 9
three 2 5 7 10
print(df.unstack(‘state‘).stack(‘side‘))
# result
state Colorado Ohio
number side
one left 3 0
right 8 5
two left 4 1
right 9 6
three left 5 2
right 10 7
# 未完待续。。。 有点多
原文：https://www.cnblogs.com/dev-liu/p/pandas_3.html