ces

{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# pandas的拼接操作"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import numpy as np\n",
"import pandas as pd\n",
"from pandas import Series,DataFrame"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"pandas的拼接分为两种：\n",
"- 级联：pd.concat, pd.append\n",
"- 合并：pd.merge, pd.join"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 1. 使用pd.concat()级联"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"pandas使用pd.concat函数，与np.concatenate函数类似，只是多了一些参数：\n",
"\n", "objs\n", "axis=0\n", "keys\n", "join='outer' / 'inner':表示的是级联的方式，outer会将所有的项进行级联（忽略匹配和不匹配），而inner只会将匹配的项级联到一起，不匹配的不级联\n", "ignore_index=False\n", ""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 1)匹配级联"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" a\n",
" b\n",
" c\n",
" \n",
" \n",
" \n",
" \n",
" A\n",
" 52\n",
" 12\n",
" 54\n",
" \n",
" \n",
" B\n",
" 22\n",
" 88\n",
" 96\n",
" \n",
" \n",
" C\n",
" 55\n",
" 89\n",
" 68\n",
" \n",
" \n",
" A\n",
" 52\n",
" 12\n",
" 54\n",
" \n",
" \n",
" B\n",
" 22\n",
" 88\n",
" 96\n",
" \n",
" \n",
" C\n",
" 55\n",
" 89\n",
" 68\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" a b c\n",
"A 52 12 54\n",
"B 22 88 96\n",
"C 55 89 68\n",
"A 52 12 54\n",
"B 22 88 96\n",
"C 55 89 68"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1 = DataFrame(data=np.random.randint(0,100,size=(3,3)),index=['A','B','C'],columns=['a','b','c'])\n",
"df2 = DataFrame(data=np.random.randint(0,100,size=(3,3)),index=['A','B','D'],columns=['a','b','d'])\n",
"pd.concat((df1,df1),axis=0)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 2) 不匹配级联"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"不匹配指的是级联的维度的索引不一致。例如纵向级联时列索引不一致，横向级联时行索引不一致"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"有2种连接方式：\n",
"\n",
"- 外连接：补NaN（默认模式）"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- 内连接：只连接匹配的项"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" a\n",
" b\n",
" c\n",
" a\n",
" b\n",
" d\n",
" \n",
" \n",
" \n",
" \n",
" A\n",
" 52.0\n",
" 12.0\n",
" 54.0\n",
" 75.0\n",
" 32.0\n",
" 37.0\n",
" \n",
" \n",
" B\n",
" 22.0\n",
" 88.0\n",
" 96.0\n",
" 87.0\n",
" 0.0\n",
" 54.0\n",
" \n",
" \n",
" C\n",
" 55.0\n",
" 89.0\n",
" 68.0\n",
" NaN\n",
" NaN\n",
" NaN\n",
" \n",
" \n",
" D\n",
" NaN\n",
" NaN\n",
" NaN\n",
" 68.0\n",
" 30.0\n",
" 80.0\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" a b c a b d\n",
"A 52.0 12.0 54.0 75.0 32.0 37.0\n",
"B 22.0 88.0 96.0 87.0 0.0 54.0\n",
"C 55.0 89.0 68.0 NaN NaN NaN\n",
"D NaN NaN NaN 68.0 30.0 80.0"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.concat((df1,df2),axis=1)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" a\n",
" b\n",
" c\n",
" a\n",
" b\n",
" d\n",
" \n",
" \n",
" \n",
" \n",
" A\n",
" 52\n",
" 12\n",
" 54\n",
" 75\n",
" 32\n",
" 37\n",
" \n",
" \n",
" B\n",
" 22\n",
" 88\n",
" 96\n",
" 87\n",
" 0\n",
" 54\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" a b c a b d\n",
"A 52 12 54 75 32 37\n",
"B 22 88 96 87 0 54"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.concat((df1,df2),axis=1,join='inner')"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"## 2. 使用pd.merge()合并"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"merge与concat的区别在于，merge需要依据某一共同的列来进行合并\n",
"\n",
"使用pd.merge()合并时，会自动根据两者相同column名称的那一列，作为key来进行合并。\n",
"\n",
"注意每一列元素的顺序不要求一致"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"参数：\n",
"- how：out取并集 inner取交集"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- on：当有多列相同的时候，可以使用on来指定使用那一列进行合并，on的值为一个列表"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 1) 一对一合并"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bob\n",
" Accounting\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" \n",
" \n",
" 2\n",
" Lisa\n",
" Engineering\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group\n",
"0 Bob Accounting\n",
"1 Jake Engineering\n",
"2 Lisa Engineering"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1 = DataFrame({'employee':['Bob','Jake','Lisa'],\n",
" 'group':['Accounting','Engineering','Engineering'],\n",
" })\n",
"df1"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" hire_date\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Lisa\n",
" 2004\n",
" \n",
" \n",
" 1\n",
" Bob\n",
" 2008\n",
" \n",
" \n",
" 2\n",
" Jake\n",
" 2012\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee hire_date\n",
"0 Lisa 2004\n",
"1 Bob 2008\n",
"2 Jake 2012"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df2 = DataFrame({'employee':['Lisa','Bob','Jake'],\n",
" 'hire_date':[2004,2008,2012],\n",
" })\n",
"df2"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" hire_date\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bob\n",
" Accounting\n",
" 2008\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" 2012\n",
" \n",
" \n",
" 2\n",
" Lisa\n",
" Engineering\n",
" 2004\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group hire_date\n",
"0 Bob Accounting 2008\n",
"1 Jake Engineering 2012\n",
"2 Lisa Engineering 2004"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df1,df2)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 2) 多对一合并"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" hire_date\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Lisa\n",
" Accounting\n",
" 2004\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" 2016\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group hire_date\n",
"0 Lisa Accounting 2004\n",
"1 Jake Engineering 2016"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df3 = DataFrame({\n",
" 'employee':['Lisa','Jake'],\n",
" 'group':['Accounting','Engineering'],\n",
" 'hire_date':[2004,2016]})\n",
"df3"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" group\n",
" supervisor\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Accounting\n",
" Carly\n",
" \n",
" \n",
" 1\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
" 2\n",
" Engineering\n",
" Steve\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" group supervisor\n",
"0 Accounting Carly\n",
"1 Engineering Guido\n",
"2 Engineering Steve"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df4 = DataFrame({'group':['Accounting','Engineering','Engineering'],\n",
" 'supervisor':['Carly','Guido','Steve']\n",
" })\n",
"df4"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" hire_date\n",
" supervisor\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Lisa\n",
" Accounting\n",
" 2004\n",
" Carly\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" 2016\n",
" Guido\n",
" \n",
" \n",
" 2\n",
" Jake\n",
" Engineering\n",
" 2016\n",
" Steve\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group hire_date supervisor\n",
"0 Lisa Accounting 2004 Carly\n",
"1 Jake Engineering 2016 Guido\n",
"2 Jake Engineering 2016 Steve"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df3,df4)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 3) 多对多合并"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bob\n",
" Accounting\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" \n",
" \n",
" 2\n",
" Lisa\n",
" Engineering\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group\n",
"0 Bob Accounting\n",
"1 Jake Engineering\n",
"2 Lisa Engineering"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1 = DataFrame({'employee':['Bob','Jake','Lisa'],\n",
" 'group':['Accounting','Engineering','Engineering']})\n",
"df1"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" group\n",
" supervisor\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 1\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
" 2\n",
" HR\n",
" Steve\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" group supervisor\n",
"0 Engineering Carly\n",
"1 Engineering Guido\n",
"2 HR Steve"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df5 = DataFrame({'group':['Engineering','Engineering','HR'],\n",
" 'supervisor':['Carly','Guido','Steve']\n",
" })\n",
"df5"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" supervisor\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bob\n",
" Accounting\n",
" NaN\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 2\n",
" Jake\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
" 3\n",
" Lisa\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 4\n",
" Lisa\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
" 5\n",
" NaN\n",
" HR\n",
" Steve\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group supervisor\n",
"0 Bob Accounting NaN\n",
"1 Jake Engineering Carly\n",
"2 Jake Engineering Guido\n",
"3 Lisa Engineering Carly\n",
"4 Lisa Engineering Guido\n",
"5 NaN HR Steve"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df1,df5,how='outer',on='group')"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" supervisor\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Jake\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
" 2\n",
" Lisa\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 3\n",
" Lisa\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group supervisor\n",
"0 Jake Engineering Carly\n",
"1 Jake Engineering Guido\n",
"2 Lisa Engineering Carly\n",
"3 Lisa Engineering Guido"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df1,df5,how='inner',on='group')"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" supervisor\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bob\n",
" Accounting\n",
" NaN\n",
" \n",
" \n",
" 1\n",
" Jake\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 2\n",
" Jake\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
" 3\n",
" Lisa\n",
" Engineering\n",
" Carly\n",
" \n",
" \n",
" 4\n",
" Lisa\n",
" Engineering\n",
" Guido\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group supervisor\n",
"0 Bob Accounting NaN\n",
"1 Jake Engineering Carly\n",
"2 Jake Engineering Guido\n",
"3 Lisa Engineering Carly\n",
"4 Lisa Engineering Guido"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df1,df5,how='left',on='group')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 4) key的规范化"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- 当列冲突时，即有多个列名称相同时，需要使用on=来指定哪一个列作为key，配合suffixes指定冲突列名"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Jack\n",
" Accounting\n",
" \n",
" \n",
" 1\n",
" Summer\n",
" Finance\n",
" \n",
" \n",
" 2\n",
" Steve\n",
" Marketing\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group\n",
"0 Jack Accounting\n",
"1 Summer Finance\n",
"2 Steve Marketing"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1 = DataFrame({'employee':['Jack',"Summer","Steve"],\n",
" 'group':['Accounting','Finance','Marketing']})\n",
"df1"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" hire_date\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Jack\n",
" Accounting\n",
" 2003\n",
" \n",
" \n",
" 1\n",
" Bob\n",
" sell\n",
" 2009\n",
" \n",
" \n",
" 2\n",
" Jake\n",
" ceo\n",
" 2012\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group hire_date\n",
"0 Jack Accounting 2003\n",
"1 Bob sell 2009\n",
"2 Jake ceo 2012"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df2 = DataFrame({'employee':['Jack','Bob',"Jake"],\n",
" 'hire_date':[2003,2009,2012],\n",
" 'group':['Accounting','sell','ceo']})\n",
"df2"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee_x\n",
" group\n",
" employee_y\n",
" hire_date\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Jack\n",
" Accounting\n",
" Jack\n",
" 2003\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee_x group employee_y hire_date\n",
"0 Jack Accounting Jack 2003"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df1,df2,on='group')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" - 当两张表没有可进行连接的列时，可使用left_on和right_on手动指定merge中左右两边的哪一列列作为连接的列"
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" hire_date\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bobs\n",
" Accounting\n",
" 1998\n",
" \n",
" \n",
" 1\n",
" Linda\n",
" Product\n",
" 2017\n",
" \n",
" \n",
" 2\n",
" Bill\n",
" Marketing\n",
" 2018\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group hire_date\n",
"0 Bobs Accounting 1998\n",
"1 Linda Product 2017\n",
"2 Bill Marketing 2018"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df1 = DataFrame({'employee':['Bobs','Linda','Bill'],\n",
" 'group':['Accounting','Product','Marketing'],\n",
" 'hire_date':[1998,2017,2018]})\n",
"df1"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" hire_dates\n",
" name\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" 1998\n",
" Lisa\n",
" \n",
" \n",
" 1\n",
" 2016\n",
" Bobs\n",
" \n",
" \n",
" 2\n",
" 2007\n",
" Bill\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" hire_dates name\n",
"0 1998 Lisa\n",
"1 2016 Bobs\n",
"2 2007 Bill"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df5 = DataFrame({'name':['Lisa','Bobs','Bill'],\n",
" 'hire_dates':[1998,2016,2007]})\n",
"df5"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"

\n",
"\n",
"<table border="1" class="dataframe">\n",
" \n",
" <tr style="text-align: right;">\n",
" \n",
" employee\n",
" group\n",
" hire_date\n",
" hire_dates\n",
" name\n",
" \n",
" \n",
" \n",
" \n",
" 0\n",
" Bobs\n",
" Accounting\n",
" 1998\n",
" 2016\n",
" Bobs\n",
" \n",
" \n",
" 1\n",
" Bill\n",
" Marketing\n",
" 2018\n",
" 2007\n",
" Bill\n",
" \n",
" \n",
"\n",
"

"
],
"text/plain": [
" employee group hire_date hire_dates name\n",
"0 Bobs Accounting 1998 2016 Bobs\n",
"1 Bill Marketing 2018 2007 Bill"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.merge(df1,df5,left_on='employee',right_on='name')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"练习：\n",
"\n",
"\n",
"1. 自行练习多对一，多对多的情况 \n",
"\n",
"2. 自学left_index,right_index\n",
"\n",
""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 5) 内合并与外合并:out取并集 inner取交集"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- 内合并：只保留两者都有的key（默认模式）"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [],
"source": [
"df6 = DataFrame({'name':['Peter','Paul','Mary'],\n",
" 'food':['fish','beans','bread']}\n",
" )\n",
"df7 = DataFrame({'name':['Mary','Joseph'],\n",
" 'drink':['wine','beer']})\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- 外合并 how='outer'：补NaN"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [],
"source": [
"df6 = DataFrame({'name':['Peter','Paul','Mary'],\n",
" 'food':['fish','beans','bread']}\n",
" )\n",
"df7 = DataFrame({'name':['Mary','Joseph'],\n",
" 'drink':['wine','beer']})\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"## 作业\n",
"## 3. 案例分析：美国各州人口数据分析"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}