Merge_And_Join

게시 2024/04/28

By Jinhyeok Ko

조회 13 분읽는 시간

Merge_And_Join

Combining Datasets: Merge and Join

  
import pandas as pd
import numpy as np


class display(object):
    """Display HTML representation of multiple objects"""
    template = """<div style="float: left; padding: 10px;">
    <p style='font-family:"Courier New", Courier, monospace'>{0}</p>{1}
    </div>"""

    def __init__(self, *args):
        self.args = args

    def _repr_html_(self):
        return '\n'.join(self.template.format(a, eval(a)._repr_html_())
                         for a in self.args)

    def __repr__(self):
        return '\n\n'.join(a + '\n' + repr(eval(a))
                           for a in self.args)

관계 대수 Relational Algebra

pd.merge()에는 관계 데이터를 조작하는 규칙의 정형 집합이자 대부분의 데이터베이스에서 사용할 수 있는 연산의 개념적 기반을 형성하는 관계 대수의 하위 집합에 해당하는 행위가 구현
관계 대수 방식의 강점으로 데이터세트에 대한 복잡한 연산의 기본 구성요소가 되는 몇 가지 기초 연산은 제안함

조인 작업의 분류 Categories of Joins

pd.merge() 함수는 일대일, 다대일, 다대다 조인 같은 여러 가지 조인 유형을 구현
이 세가지 유형의 조인은 pd.merge() 인터페이스에서 동일한 호출을 통해 사용

일대일 조인 One-to-one joins

  
df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                    'hire_date': [2004, 2008, 2012, 2014]})
# 공통된 열을 가지고 있는 것을 파악해 자동으로 키로 사용해 조인 --> 인덱스를 버림
df3 = pd.merge(df1, df2)
df3
display('df1', 'df2', 'df3')

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df2

	employee	hire_date
0	Lisa	2004
1	Bob	2008
2	Jake	2012
3	Sue	2014

df3

	employee	group	hire_date
0	Bob	Accounting	2008
1	Jake	Engineering	2012
2	Lisa	Engineering	2004
3	Sue	HR	2014

다대일 조인 Many-to-one joins

  
# 두 개의 키 열 중 하나가 중복된 항목을 포함하는 경우 --> 중복을 보존
df4 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],
                    'supervisor': ['Carly', 'Guido', 'Steve']})
display('df3', 'df4', 'pd.merge(df3, df4)')

df3

	employee	group	hire_date
0	Bob	Accounting	2008
1	Jake	Engineering	2012
2	Lisa	Engineering	2004
3	Sue	HR	2014

df4

	group	supervisor
0	Accounting	Carly
1	Engineering	Guido
2	HR	Steve

pd.merge(df3, df4)

	employee	group	hire_date	supervisor
0	Bob	Accounting	2008	Carly
1	Jake	Engineering	2012	Guido
2	Lisa	Engineering	2004	Guido
3	Sue	HR	2014	Steve

다대다 조인 Many-to-many joins

  
# 두 배열의 키 열에 모두 중복 항목이 존재
df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',
                              'Engineering', 'Engineering', 'HR', 'HR'],
                    'skills': ['math', 'spreadsheets', 'coding', 'linux',
                               'spreadsheets', 'organization']})
display('df1', 'df5', "pd.merge(df1, df5)")

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df5

	group	skills
0	Accounting	math
1	Accounting	spreadsheets
2	Engineering	coding
3	Engineering	linux
4	HR	spreadsheets
5	HR	organization

pd.merge(df1, df5)

	employee	group	skills
0	Bob	Accounting	math
1	Bob	Accounting	spreadsheets
2	Jake	Engineering	coding
3	Jake	Engineering	linux
4	Lisa	Engineering	coding
5	Lisa	Engineering	linux
6	Sue	HR	spreadsheets
7	Sue	HR	organization

병합 키 지정 Specification of the Merge Key

The on keyword

  
# 키 열의 이름을 명시적으로 지정
display('df1', 'df2', "pd.merge(df1, df2, on='employee')")

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df2

	employee	hire_date
0	Lisa	2004
1	Bob	2008
2	Jake	2012
3	Sue	2014

pd.merge(df1, df2, on='employee')

	employee	group	hire_date
0	Bob	Accounting	2008
1	Jake	Engineering	2012
2	Lisa	Engineering	2004
3	Sue	HR	2014

The left_on and right_on keywords

  
# 다른 열 이름을 가진 두 데이터세트 병합 --> 불필요한 중복 열 발생
df3 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'salary': [70000, 80000, 120000, 90000]})
display('df1', 'df3', 'pd.merge(df1, df3, left_on="employee", right_on="name")')

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df3

	name	salary
0	Bob	70000
1	Jake	80000
2	Lisa	120000
3	Sue	90000

pd.merge(df1, df3, left_on="employee", right_on="name")

	employee	group	name	salary
0	Bob	Accounting	Bob	70000
1	Jake	Engineering	Jake	80000
2	Lisa	Engineering	Lisa	120000
3	Sue	HR	Sue	90000

  
# drop() 메서드를 통한 중복된 열 삭제
pd.merge(df1, df3, left_on="employee", right_on="name").drop('name', axis=1)

	employee	group	salary
0	Bob	Accounting	70000
1	Jake	Engineering	80000
2	Lisa	Engineering	120000
3	Sue	HR	90000

The left_index and right_index keywords

  
# 병합 키로 인덱스를 사용
df1a = df1.set_index('employee')
df2a = df2.set_index('employee')
display('df1a', 'df2a', "pd.merge(df1a, df2a, left_index=True, right_index=True)")

df1a

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df2a

	hire_date
employee
Lisa	2004
Bob	2008
Jake	2012
Sue	2014

pd.merge(df1a, df2a, left_index=True, right_index=True)

	group	hire_date
employee
Bob	Accounting	2008
Jake	Engineering	2012
Lisa	Engineering	2004
Sue	HR	2014

  
# DataFrame은 기본적으로 join() 메서드를 통해 인덱스 기반 조인
display('df1a', 'df2a', 'df1a.join(df2a)')

df1a

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df2a

	hire_date
employee
Lisa	2004
Bob	2008
Jake	2012
Sue	2014

df1a.join(df2a)

	group	hire_date
employee
Bob	Accounting	2008
Jake	Engineering	2012
Lisa	Engineering	2004
Sue	HR	2014

  
# 인덱스와 열을 섞고자 할 때 사용
display('df1a', 'df3', "pd.merge(df1a, df3, left_index=True, right_on='name')")

df1a

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df3

	name	salary
0	Bob	70000
1	Jake	80000
2	Lisa	120000
3	Sue	90000

pd.merge(df1a, df3, left_index=True, right_on='name')

	group	name	salary
0	Accounting	Bob	70000
1	Engineering	Jake	80000
2	Engineering	Lisa	120000
3	HR	Sue	90000

조인을 위한 집합 연산 지정 Specifying Set Arithmetic for Joins

  
df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'],
                    'food': ['fish', 'beans', 'bread']},
                   columns=['name', 'food'])
df7 = pd.DataFrame({'name': ['Mary', 'Joseph'],
                    'drink': ['wine', 'beer']},
                   columns=['name', 'drink'])
# 기본적으로 교집합 --> how='inner' 를 통해 명시적 지정
display('df6', 'df7', "pd.merge(df6, df7, how='inner')")

df6

	name	food
0	Peter	fish
1	Paul	beans
2	Mary	bread

df7

	name	drink
0	Mary	wine
1	Joseph	beer

pd.merge(df6, df7, how='inner')

	name	food	drink
0	Mary	bread	wine

  
# 합집합 --> 누락된 값은 NA로 채움
display('df6', 'df7', "pd.merge(df6, df7, how='outer')")

df6

	name	food
0	Peter	fish
1	Paul	beans
2	Mary	bread

df7

	name	drink
0	Mary	wine
1	Joseph	beer

pd.merge(df6, df7, how='outer')

	name	food	drink
0	Joseph	NaN	beer
1	Mary	bread	wine
2	Paul	beans	NaN
3	Peter	fish	NaN

  
# 오른쪽 항목과 왼쪽 항목을 기준으로 조인
display("pd.merge(df6, df7, how='right')", "pd.merge(df6, df7, how='left')")

pd.merge(df6, df7, how='right')

	name	food	drink
0	Mary	bread	wine
1	Joseph	NaN	beer

pd.merge(df6, df7, how='left')

	name	food	drink
0	Peter	fish	NaN
1	Paul	beans	NaN
2	Mary	bread	wine

열 이름이 겹치는 경우(Overlapping): The suffixes Keyword

  
df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [1, 2, 3, 4]})
df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [3, 1, 4, 2]})
# 충돌 열이 있을 경우 자동으로 접미사 _x나 _y를 덧붙임
display('df8', 'df9', 'pd.merge(df8, df9, on="name")')

df8

	name	rank
0	Bob	1
1	Jake	2
2	Lisa	3
3	Sue	4

df9

	name	rank
0	Bob	3
1	Jake	1
2	Lisa	4
3	Sue	2

pd.merge(df8, df9, on="name")

	name	rank_x	rank_y
0	Bob	1	3
1	Jake	2	1
2	Lisa	3	4
3	Sue	4	2

  
# 접미사를 suffixes를 통해 별도로 지정
display('df8', 'df9', 'pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])')

df8

	name	rank
0	Bob	1
1	Jake	2
2	Lisa	3
3	Sue	4

df9

	name	rank
0	Bob	3
1	Jake	1
2	Lisa	4
3	Sue	2

pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])

	name	rank_L	rank_R
0	Bob	1	3
1	Jake	2	1
2	Lisa	3	4
3	Sue	4	2

Example: US States Data

  
pop = pd.read_csv('./state-population.csv')
areas = pd.read_csv('./state-areas.csv')
abbrevs = pd.read_csv('./state-abbrevs.csv')

display('pop.head()', 'areas.head()', 'abbrevs.head()')

pop.head()

	state/region	ages	year	population
0	AL	under18	2012	1117489.0
1	AL	total	2012	4817528.0
2	AL	under18	2010	1130966.0
3	AL	total	2010	4785570.0
4	AL	under18	2011	1125763.0

areas.head()

	state	area (sq. mi)
0	Alabama	52423
1	Alaska	656425
2	Arizona	114006
3	Arkansas	53182
4	California	163707

abbrevs.head()

	state	abbreviation
0	Alabama	AL
1	Alaska	AK
2	Arizona	AZ
3	Arkansas	AR
4	California	CA

  
# 데이터 제거를 막기 위해 outer 사용
merged = pd.merge(pop, abbrevs, how='outer',
                  left_on='state/region', right_on='abbreviation')
merged = merged.drop('abbreviation', axis=1)  # drop duplicate info
merged.head()

	state/region	ages	year	population	state
0	AK	total	1990	553290.0	Alaska
1	AK	under18	1990	177502.0	Alaska
2	AK	total	1992	588736.0	Alaska
3	AK	under18	1991	182180.0	Alaska
4	AK	under18	1992	184878.0	Alaska

  
# 널 값을 가진 행을 찾아 불일치 항목 확인
merged.isnull().any()

state/region    False
ages            False
year            False
population       True
state            True
dtype: bool

  
# 널 값 확인
merged[merged['population'].isnull()].head()

	state/region	ages	year	population	state
1872	PR	under18	1990	NaN	NaN
1873	PR	total	1990	NaN	NaN
1874	PR	total	1991	NaN	NaN
1875	PR	under18	1991	NaN	NaN
1876	PR	total	1993	NaN	NaN

  
# 일치되는 값이 없는 지역 확인
merged.loc[merged['state'].isnull(), 'state/region'].unique()

array(['PR', 'USA'], dtype=object)

  
# 주 이름의 약어 키에는 없는 항목(state)을 채워 넣기
merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'
merged.loc[merged['state/region'] == 'USA', 'state'] = 'United States'
merged.isnull().any()

state/region    False
ages            False
year            False
population       True
state           False
dtype: bool

  
final = pd.merge(merged, areas, on='state', how='left')
final.head()

	state/region	ages	year	population	state	area (sq. mi)
0	AK	total	1990	553290.0	Alaska	656425.0
1	AK	under18	1990	177502.0	Alaska	656425.0
2	AK	total	1992	588736.0	Alaska	656425.0
3	AK	under18	1991	182180.0	Alaska	656425.0
4	AK	under18	1992	184878.0	Alaska	656425.0

  
# area 널 값 발생
final.isnull().any()

state/region     False
ages             False
year             False
population        True
state            False
area (sq. mi)     True
dtype: bool

  
# area 널 값을 가진 지역 확인
final['state'][final['area (sq. mi)'].isnull()].unique()

array(['United States'], dtype=object)

  
# 널 값 삭제
final.dropna(inplace=True)
final.head()

	state/region	ages	year	population	state	area (sq. mi)
0	AK	total	1990	553290.0	Alaska	656425.0
1	AK	under18	1990	177502.0	Alaska	656425.0
2	AK	total	1992	588736.0	Alaska	656425.0
3	AK	under18	1991	182180.0	Alaska	656425.0
4	AK	under18	1992	184878.0	Alaska	656425.0

  
import numexpr as ne

data2010 = final.query("year == 2010 & ages == 'total'")
data2011 = final.query("year == 2011 & ages == 'total'")
data2012 = final.query("year == 2012 & ages == 'total'")
display("data2010.head()", "data2011.head()", "data2012.head()")

data2010.head()

	state/region	ages	year	population	state	area (sq. mi)
43	AK	total	2010	713868.0	Alaska	656425.0
51	AL	total	2010	4785570.0	Alabama	52423.0
141	AR	total	2010	2922280.0	Arkansas	53182.0
149	AZ	total	2010	6408790.0	Arizona	114006.0
197	CA	total	2010	37333601.0	California	163707.0

data2011.head()

	state/region	ages	year	population	state	area (sq. mi)
45	AK	total	2011	723375.0	Alaska	656425.0
53	AL	total	2011	4801627.0	Alabama	52423.0
139	AR	total	2011	2938506.0	Arkansas	53182.0
147	AZ	total	2011	6468796.0	Arizona	114006.0
195	CA	total	2011	37668681.0	California	163707.0

data2012.head()

	state/region	ages	year	population	state	area (sq. mi)
47	AK	total	2012	730307.0	Alaska	656425.0
49	AL	total	2012	4817528.0	Alabama	52423.0
143	AR	total	2012	2949828.0	Arkansas	53182.0
145	AZ	total	2012	6551149.0	Arizona	114006.0
193	CA	total	2012	37999878.0	California	163707.0

  
# 각 연도별 인구 밀도 계산 및 재배열
data2010.set_index('state', inplace=True)
density10 = data2010['population'] / data2010['area (sq. mi)']
density10.sort_values(ascending=False, inplace=True)
# density2010.sort_values(ascending=False, inplace=True)

data2011.set_index('state', inplace=True)
density11 = data2011['population'] / data2011['area (sq. mi)']
density11.sort_values(ascending=False, inplace=True)

data2012.set_index('state', inplace=True)
density12 = data2012['population'] / data2012['area (sq. mi)']
density12.sort_values(ascending=False, inplace=True)
# density2012.sort_values(ascending=False, inplace=True)

# 인구 밀도가 가장 높은 지역과 낮은 지역
print(density11.head(), "\n\n", density11.tail())

state
District of Columbia    9112.117647
Puerto Rico             1048.813656
New Jersey              1013.143660
Rhode Island             679.838188
Connecticut              647.357143
dtype: float64 

 state
South Dakota    10.681552
North Dakota     9.686397
Montana          6.784272
Wyoming          5.799843
Alaska           1.101992
dtype: float64

  
# 인구 밀도가 매 해 증가하는 지역
increasing_states = []
for state in final['state'].unique():
    density2010 = density10[state]
    density2011 = density11[state]
    density2012 = density12[state]

    if (density2010 < density2011).all() and (density2011 < density2012).all():
        increasing_states.append(state)

print(increasing_states)

['Alaska', 'Alabama', 'Arkansas', 'Arizona', 'California', 'Colorado', 'Connecticut', 'District of Columbia', 'Delaware', 'Florida', 'Georgia', 'Hawaii', 'Iowa', 'Idaho', 'Illinois', 'Indiana', 'Kansas', 'Kentucky', 'Louisiana', 'Massachusetts', 'Maryland', 'Maine', 'Minnesota', 'Missouri', 'Mississippi', 'Montana', 'North Carolina', 'North Dakota', 'Nebraska', 'New Hampshire', 'New Jersey', 'New Mexico', 'Nevada', 'New York', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Virginia', 'Washington', 'Wisconsin', 'West Virginia', 'Wyoming']

Python Data Science

Pandas

Combining Datasets: Merge and Join

관계 대수 Relational Algebra

조인 작업의 분류 Categories of Joins

일대일 조인 One-to-one joins

다대일 조인 Many-to-one joins

다대다 조인 Many-to-many joins

병합 키 지정 Specification of the Merge Key

The on keyword

The left_on and right_on keywords

The left_index and right_index keywords

조인을 위한 집합 연산 지정 Specifying Set Arithmetic for Joins

열 이름이 겹치는 경우(Overlapping): The suffixes Keyword

Example: US States Data

인기 태그