pandas读书笔记--数据规整化

合并数据集

数据库风格的DataFrame合并

## merge 默认总是copy，产生新对象
df1 = DataFrame({'key':['b','b','a','c','a','a','b'],'data1':range(7)})
df2 = DataFrame({'key':['a','b','d'],'data2':range(3)})
# merge会将重叠的列名当做键，但是最好指定
pd.merge(df1,df2)
pd.merge(df1,df2,on='key')

# 列名不同也可以指定
df3 = DataFrame({'lkey':['b','b','a','c','a','a','b'],'data1':range(7)})
df4 = DataFrame({'rkey':['a','b','d'],'data2':range(3)})
pd.merge(df3,df4,left_on='lkey',right_on='rkey')

# 外连接
pd.merge(df1,df2,how='outer')

# 多对多的合并，笛卡尔积
df1 = DataFrame({'key':['b','b','a','c','a','b'],'data1':range(6)})
df2 = DataFrame({'key':['a','b','a','b','d'],'data2':range(5)})
pd.merge(df1,df2,on='key',how='left')

# 根据多个键进行合并
left = DataFrame({'key1':['foo','foo','bar'],'key2':['one','two','one'],'lval':[1,2,3]})
right = DataFrame({'key1':['foo','foo','bar','bar'],'key2':['one','one','one','two'],'rval':[4,5,6,7]})
pd.merge(left,right,on=['key1','key2'],how='outer')

# 如果左右两个列名有重叠
pd.merge(left,right,on='key1')
pd.merge(left,fight,on='key1',suffixes=('_left','_right'))

索引上的合并

# 连接键位于索引中
left1 = DataFrame({'key':['a','b','a','a','b','c'],'value':range(6)})
right1 = DataFrame({'group_val':[3.5,7]},index=['a','b'])
pd.merge(left1, right1, left_on='key',right_index=True)

# 对于层次化的索引
lefth = DataFrame({'key1':['Ohio','Ohio','Ohio','Nevada','Nevada'],'key2':[2000,2001,2002,2001,2002],'data':np.arange(5.)})
righth = DataFrame(np.arange(12).reshape((6,2)),index=[['Nevada','Nevada','Ohio','Ohio','Ohio','Ohio'],[2001,2000,2000,2000,2001,2002]],columns=['event1','event2'])
# 必须以列表的形式指明用作合并键的列
pd.merge(lefth,righth, left_on=['key1','key2'],right_index=True)

# 使用合并双方的索引
left2 = DataFrame([[1.,2.],[3.,4.],[5.,6.]], index=['a','c','e'],columns=['Ohio','Nevada'])
right2 = DataFrame([[7.,8.],[9.,10.],[11.,12.],[13.,14.]],index=['b','c','d','e'],columns=['Missiuri','Alabama'])
np.merge(left2,right2,how='outer',left_index=True,right_index=True)

# join:更方便的实现按索引合并
left2.join(right2,how='outer')

# join:支持参数的DataFrame索引和调用者的某个列之间链接
df1 = DataFrame(np.arange(12).reshape((3,4)))
df2 = DataFrame({'a':[1,5,8],'b':[2,6,8],'c':[1,2,3]})
df2.join(df, on='c')

# join:简单的索引合并
another = DataFrame([[7.,8.],[9.,10.],[11.,12.],[16.,17.]],index=['a','c','e','f'],columns=['New York','Oregon'])

轴向链接

concat 函数

# numpy有一个合并原始数组的concatenation函数
arr = np.arange(12).reshape((3,4))
np.concatenate([arr,arr],axis=1)

## pandas concat函数,最终产生一个新对象
s1 = Series([0,1],index=['a','b'])
s2 = Series([2,3,4],index=['c','d','e'])
s3 = Series([5,6],index=['f','g'])
pd.concat([s1,s2,s3])
pd.concat([s1,s2,s3],axis=1)
pd.concat([s1,s2,s3],axis=1,join='inner')

# 在一个链接轴上创建层次化索引
result = pd.concat([s1,s2,s3],keys=['one','two','three'])

# 如果沿着axis=1进行合并，keys就会成为DataFrame的头
pd.concat([s1,s2,s3],axis=1,keys=['one','two','three'])

# 对于DataFrame的合并
df1 = DataFrame(np.arange(6).reshape((3,2)),index=['a','b','c'],columns=['one','two'])
df2 = DataFrame(np.arange(4).reshape((2,2))+5,index=['a','c'],columns=['three','four'])
pd.concat([df1,df2],axis=1,keys=['level1','level2'])
pd.concat({'level1':df1,'level2':df2},axis=1)

# 管理层次化索引创建方式
pd.concat([df1,df2],axis=1,keys=['level1','level2'],names=['upper','lower'])

# 忽略索引
pd.concat([df1,df2],ignore_index=True)

合并重叠数据

a = Series([np.nan,2.5,np.nan,3.5,4.5,np.nan],index=['f','e','d','c','b','a'])
b = 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)
b[:-2].combine_first(a[2:])

# 使用参数上的数据为调用者打补丁
df1 = DataFrame({'a':[1.,np.nan,5.,np.nan],'b':[np.nan,2.,np.nan,6],'c':range(2,18,4)})
df2 = DataFrame({'a':[5.,4.,np.nan,3.,7.],'b':[np.nan,3.,4.,6.,8.]})