python|jupyter|pandas|5.1合并数据

1.堆叠

#横向堆叠concat
pandas.concat(objs,axis=0,join='outer',join_axes=None,ignore_index=False,keys=None,lebels=None,names=None,verify_integrity=False,copy=True)
#axis=0 按照X轴拼接 ：行对齐，合并列（左右拼接）
#inner 内连接 交集
#outer 外连接 并集 此时不足的地方用空值填补

import numpy as np
import pandas as pd
detail=pd.read_excel('E:\大三上\张宇萌\实习任务安排（2019-9-26）\meal_order_detail.xlsx')
df1=detail.iloc[:,:10]#取出detail前10列数据
df2=detail.iloc[:,10:]#取出detail后9列数据
df1.shape
df2.shape
print('外连接df1,df2：',pd.concat([df1,df2],axis=1,join='inner').shape)
print('内连接df1,df2：',pd.concat([df1,df2],axis=1,join='outer').shape)

#纵向堆叠concat
#axis=0 y轴拼接（上下拼接）
df3=detail.iloc[:1500,:]#前1500行
df4=detail.iloc[1500:,:]#1500行后的数据
df3.shape
df4.shape
print('内连接',pd.concat([df3,df4],axis=0,join='inner').shape)
print('外连接',pd.concat([df3,df4],axis=0,join='outer').shape)

#纵向堆叠Append
pandas.dataframe.append(self,other,ignore_index=False,verify_integrity=False)

df3.shape
df4.shape
df3.append(df4).shape

2.主键合并数据

含义：通过一个或多个键将两个数据集的行连接起来，类似于SQL中join

#合并函数merge/join
pandas.merge(left,right,how='inner',on=None,left_on=None,right_on=None,left_index=False,right_index=False,sort=False,suffixes=('_x','_y'),copy=True,indicator=False)
#left左连接 right右连接 inner outer

order=pd.read_csv('E:\大三上\张宇萌\实习任务安排（2019-9-26）\meal_order_info.csv',sep=',',encoding='gb18030')
order['info_id']=order['info_id'].astype('str')
order_detail=pd.merge(detail,order,left_on='order_id',right_on='info_id')

#合并函数merge/join
#用join时两张表的主键名称必须相同
pandas.dataframe.join(self,other,on=None,how='left',lsuffix='',rsuffix='',sort=False)

order.rename({'info_id':'order_id'},inplace=True)#修改键名称
order_detail1=detail.join(order,on='order_id',rsuffix='1')#合并

重叠合并数据

pandas.dataframe.combine_first(other)

#建立两个字典，除了ID外，别的特征互补
dict1={'ID':[1,2,3,4,5,6,7,8,9],'system':['win10','win10',np.nan,'win10',np.nan,np.nan,'win7','win7','win8'],'cpu':['i7','i5',np.nan,'i7',np.nan,np.nan,'i5','i5','i3']}
dict2={'ID':[1,2,3,4,5,6,7,8,9],'system':[np.nan,np.nan,'win7',np.nan,'win8','win7',np.nan,np.nan,np.nan],'cpu':[np.nan,np.nan,'i3',np.nan,'i7','i5',np.nan,np.nan,np.nan]}
#转换2个字典为Dataframe
df5=pd.dataframe(dict1)
df6=pd.dataframe(dict2)
print('合并后',df5.combine_first(df6))

任务实现

#堆叠mean_order_detail1,mean_order_detail2,mean_order_detail3
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/testdb?charset=utf8')
detail1=pd.read_sql('meal_order_detail1',con=engine)
detail2=pd.read_sql('meal_order_detail2',con=engine)
detail3=pd.read_sql('meal_order_detail3',con=engine)
detail=detail1.append(detail2)
detail=detail.append(detail3)
detail.shape

#主键合并订单详情表、订单信息表、客户信息表
order=pd.read_csv('E:\大三上\张宇萌\实习任务安排（2019-9-26）\meal_order_info.csv',sep=',',encoding='gb18030')
user=pd.read_excel(r'E:\大三上\张宇萌\实习任务安排（2019-9-26）\userinfo.xlsx')
order['info_id']=order['info_id'].astype(str)
order['emp_id']=order['emp_id'].astype(str)
user['USER_ID']=user['USER_ID'].astype(str)
data=pd.merge(detail,order,left_on=['order_id','emp_id'],right_on=['info_id','emp_id'])
data=pd.merge(data,user,left_on='emp_id',right_on='USER_ID',how='inner')
data.shape