python数据分析——pandas的拼接操作
摘要:panda的拼接操作分为两种类型:级联:pd.concat,pd.append合并:pd.merge,pd.join1。使用pd.contat()级联熊猫。使用pd.concat函数,该函数与np.concatenate函数类似,但具有一些附加参数:objsaxis=0keysjiin='outer'/'inner':它表示级联模式,外部将级联所有项(忽略匹配和不匹配
pandas的拼接分为两种:
- 级联:pd.concat, pd.append
- 合并:pd.merge, pd.join
pandas使用pd.concat函数,与np.concatenate函数类似,只是多了一些参数:
objs
axis=0
keys
join='outer' / 'inner':表示的是级联的方式,outer会将所有的项进行级联(忽略匹配和不匹配),而inner只会将匹配的项级联到一起,不匹配的不级联
ignore_index=False
In [1]:
import numpy as np
import pandas as pd
from pandas import Series,DataFrame
In [2]:
df1 = DataFrame(data=np.random.randint(0,100,size=(3,3)),index=['a','b','c'],columns=['A','B','C'])
df2 = DataFrame(data=np.random.randint(0,100,size=(3,3)),index=['a','d','c'],columns=['A','d','C'])
In [7]:
pd.concat((df1,df1),axis=0,join='inner')
Out[7]:
| A | B | C |
---|
a | 59 | 40 | 89 |
---|
b | 71 | 5 | 76 |
---|
c | 29 | 34 | 87 |
---|
a | 59 | 40 | 89 |
---|
b | 71 | 5 | 76 |
---|
c | 29 | 34 | 87 |
---|
不匹配指的是级联的维度的索引不一致。例如纵向级联时列索引不一致,横向级联时行索引不一致
In [11]:
pd.concat((df1,df2),axis=1,join='outer')
Out[11]:
| A | B | C | A | d | C |
---|
a | 59.0 | 40.0 | 89.0 | 50.0 | 26.0 | 45.0 |
---|
b | 71.0 | 5.0 | 76.0 | NaN | NaN | NaN |
---|
c | 29.0 | 34.0 | 87.0 | 31.0 | 82.0 | 35.0 |
---|
d | NaN | NaN | NaN | 23.0 | 95.0 | 94.0 |
---|
由于在后面级联的使用非常普遍,因此有一个函数append专门用于在后面添加
merge与concat的区别在于,merge需要依据某一共同的列来进行合并
使用pd.merge()合并时,会自动根据两者相同column名称的那一列,作为key来进行合并。
注意每一列元素的顺序不要求一致
- on:当有多列相同的时候,可以使用on来指定使用那一列进行合并,on的值为一个列表
In [12]:
df1 = DataFrame({'employee':['Bob','Jake','Lisa'],
'group':['Accounting','Engineering','Engineering'],
})
df1
Out[12]:
| employee | group |
---|
0 | Bob | Accounting |
---|
1 | Jake | Engineering |
---|
2 | Lisa | Engineering |
---|
In [13]:
df2 = DataFrame({'employee':['Lisa','Bob','Jake'],
'hire_date':[2004,2008,2012],
})
df2
Out[13]:
| employee | hire_date |
---|
0 | Lisa | 2004 |
---|
1 | Bob | 2008 |
---|
2 | Jake | 2012 |
---|
In [14]:
pd.merge(df1,df2,how='outer')
Out[14]:
| employee | group | hire_date |
---|
0 | Bob | Accounting | 2008 |
---|
1 | Jake | Engineering | 2012 |
---|
2 | Lisa | Engineering | 2004 |
---|
In [15]:
df3 = DataFrame({
'employee':['Lisa','Jake'],
'group':['Accounting','Engineering'],
'hire_date':[2004,2016]})
df3
Out[15]:
| employee | group | hire_date |
---|
0 | Lisa | Accounting | 2004 |
---|
1 | Jake | Engineering | 2016 |
---|
In [16]:
df4 = DataFrame({'group':['Accounting','Engineering','Engineering'],
'supervisor':['Carly','Guido','Steve']
})
df4
Out[16]:
| group | supervisor |
---|
0 | Accounting | Carly |
---|
1 | Engineering | Guido |
---|
2 | Engineering | Steve |
---|
Out[17]:
| employee | group | hire_date | supervisor |
---|
0 | Lisa | Accounting | 2004 | Carly |
---|
1 | Jake | Engineering | 2016 | Guido |
---|
2 | Jake | Engineering | 2016 | Steve |
---|
In [18]:
df1 = DataFrame({'employee':['Bob','Jake','Lisa'],
'group':['Accounting','Engineering','Engineering']})
df1
Out[18]:
| employee | group |
---|
0 | Bob | Accounting |
---|
1 | Jake | Engineering |
---|
2 | Lisa | Engineering |
---|
In [19]:
df5 = DataFrame({'group':['Engineering','Engineering','HR'],
'supervisor':['Carly','Guido','Steve']
})
df5
Out[19]:
| group | supervisor |
---|
0 | Engineering | Carly |
---|
1 | Engineering | Guido |
---|
2 | HR | Steve |
---|
In [21]:
pd.merge(df1,df5,how='outer')
Out[21]:
| employee | group | supervisor |
---|
0 | Bob | Accounting | NaN |
---|
1 | Jake | Engineering | Carly |
---|
2 | Jake | Engineering | Guido |
---|
3 | Lisa | Engineering | Carly |
---|
4 | Lisa | Engineering | Guido |
---|
5 | NaN | HR | Steve |
---|
- 加载excl数据:pd.read_excel('excl_path',sheetname=1)
- 当列冲突时,即有多个列名称相同时,需要使用on=来指定哪一个列作为key,配合suffixes指定冲突列名
In [10]:
df1 = DataFrame({'employee':['Jack',"Summer","Steve"],
'group':['Accounting','Finance','Marketing']})
In [11]:
df2 = DataFrame({'employee':['Jack','Bob',"Jake"],
'hire_date':[2003,2009,2012],
'group':['Accounting','sell','ceo']})
| employee | group |
---|
0 | Bob | Accounting |
---|
1 | Jake | Engineering |
---|
2 | Lisa | Engineering |
---|
| employee | hire_date |
---|
0 | Lisa | 2004 |
---|
1 | Bob | 2008 |
---|
2 | Jake | 2012 |
---|
- 当两张表没有可进行连接的列时,可使用left_on和right_on手动指定merge中左右两边的哪一列列作为连接的列
In [12]:
df1 = DataFrame({'employee':['Bobs','Linda','Bill'],
'group':['Accounting','Product','Marketing'],
'hire_date':[1998,2017,2018]})
In [13]:
df5 = DataFrame({'name':['Lisa','Bobs','Bill'],
'hire_dates':[1998,2016,2007]})
| employee | group |
---|
0 | Bob | Accounting |
---|
1 | Jake | Engineering |
---|
2 | Lisa | Engineering |
---|
| group | supervisor |
---|
0 | Engineering | Carly |
---|
1 | Engineering | Guido |
---|
2 | HR | Steve |
---|
5) 内合并与外合并:out取并集 inner取交集
In [25]:
df6 = DataFrame({'name':['Peter','Paul','Mary'],
'food':['fish','beans','bread']}
)
df7 = DataFrame({'name':['Mary','Joseph'],
'drink':['wine','beer']})
| name | food |
---|
0 | Peter | fish |
---|
1 | Paul | beans |
---|
2 | Mary | bread |
---|
| name | drink |
---|
0 | Mary | wine |
---|
1 | Joseph | beer |
---|
In [27]:
df6 = DataFrame({'name':['Peter','Paul','Mary'],
'food':['fish','beans','bread']}
)
df7 = DataFrame({'name':['Mary','Joseph'],
'drink':['wine','beer']})
display(df6,df7)
pd.merge()
| name | food |
---|
0 | Peter | fish |
---|
1 | Paul | beans |
---|
2 | Mary | bread |
---|
| name | drink |
---|
0 | Mary | wine |
---|
1 | Joseph | beer |
---|
免责声明:文章转载自《python数据分析——pandas的拼接操作》仅用于学习参考。如对内容有疑问,请及时联系本站处理。
上篇Python网络爬虫与信息提取ES入门 (6) 语法(4)DQL(1)查看文档/查询所有文档/匹配查询/字段匹配查询下篇
宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=
1.准备工作 python3.6连接sqlserver数据库需要引入pymssql模块 pymssql官方:https://pypi.org/project/pymssql/ 没有安装的话需要: pip安装: pip install pymssql 2.连接数据库 首先你得明确目标数据库的:'服务器名称',"账户名称","密码","数据库名称"...