# DataFrame merge

# inner join의 효과

import numpy as np
import pandas as pd

data1 = { "학번" : [1,2,3,4],
          "이름" : ["이지안","박동훈","이순신","강감찬"],
          "학년" : [2,4,1,3]}

data2 = { "학번" : [1,2,4,5],
          "학과" : ["CS","MATH","MATH","CS"],
          "학점" : [3.4,2.9,4.5,1.2]}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

display(df1)
display(df2)

display(pd.merge(df1, df2, on="학번", how="inner"))

# DataFrame merge

# full outer join, left outer join, right outer join

import numpy as np
import pandas as pd

data1 = { "학번" : [1,2,3,4],
          "이름" : ["이지안","박동훈","이순신","강감찬"],
          "학년" : [2,4,1,3]}

data2 = { "학번" : [1,2,4,5],
          "학과" : ["CS","MATH","MATH","CS"],
          "학점" : [3.4,2.9,4.5,1.2]}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

display(df1)
display(df2)

display(pd.merge(df1, df2, on="학번", how="outer"))
display(pd.merge(df1, df2, on="학번", how="left"))
display(pd.merge(df1, df2, on="학번", how="right"))

# DataFrame merge

# 컬럼명이 다른 경우의 merge

import numpy as np
import pandas as pd

data1 = { "학번" : [1,2,3,4],
          "이름" : ["이지안","박동훈","이순신","강감찬"],
          "학년" : [2,4,1,3]}

data2 = { "학생학번" : [1,2,4,5],
          "학과" : ["CS","MATH","MATH","CS"],
          "학점" : [3.4,2.9,4.5,1.2]}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# DataFrame의 column명이 다른 경우
pd.merge(df1, df2, left_on="학번", right_on="학생학번", how="inner")

# DataFrame merge

# DataFrame의 column과 index를 이용한 merge

import numpy as np
import pandas as pd

data1 = { "학번" : [1,2,3,4],
          "이름" : ["이지안","박동훈","이순신","강감찬"],
          "학년" : [2,4,1,3]}

data2 = { "학과" : ["CS","MATH","MATH","CS"],
          "학점" : [3.4,2.9,4.5,1.2]}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2, index=[1,2,4,5]) # 학번이 index로 사용

display(df1)
display(df2)

result = pd.merge(df1, df2, 
                  left_on="학번", 
                  right_index=True, 
                  how="inner")
display(result)

result.loc[2] # Error
result.loc[3] # OK

result.iloc[2] # OK
result.iloc[3] # Error

# DataFrame merge

# DataFrame의 index를 이용한 merge

import numpy as np
import pandas as pd

data1 = { "이름" : ["이지안","박동훈","이순신","강감찬"],
          "학년" : [2,4,1,3]}

data2 = { "학과" : ["CS","MATH","MATH","CS"],
          "학점" : [3.4,2.9,4.5,1.2]}

df1 = pd.DataFrame(data1, index=[1,2,3,4]) # 학번이 index로 사용
df2 = pd.DataFrame(data2, index=[1,2,4,5]) # 학번이 index로 사용

display(df1)
display(df2)

result = pd.merge(df1, df2, 
                  left_index=True, 
                  right_index=True, 
                  how="inner")
display(result)

result.iloc[2]  # OK, 강감찬
result.loc[2]  # OK, 박동훈
# result.loc[3]   # Error

# DataFrame concatenation

# Series를 1차원, 2차원으로 연결

import numpy as np
import pandas as pd

s1 = pd.Series([0,1], index=["a","c"])
s2 = pd.Series([4,3,2], index=["b","c","e"])
s3 = pd.Series([5,6], index=["f","g"])

# Series를 1차원으로 연결
display(pd.concat([s1,s2,s3], axis=0))

# Series를 2차원으로 연결 => DataFrame생성

# 중복된 index처리에 주의
result = pd.concat([s1,s2,s3], 
                   axis=1, 
                   sort=True)  # index 정렬
display(result)

# Series를 2차원으로 연결 후 columns명 설정
result = pd.concat([s1,s2,s3], 
                   axis=1, 
                   sort=True, 
                   keys=["one","two","three"])
display(result)

# DataFrame concatenation

# DataFrame을 열과 행 방향으로 연결

import numpy as np
import pandas as pd

df1 = pd.DataFrame(np.arange(6).reshape(3,2),
                   index=["a","c","b"], columns=["one","two"])
df2 = pd.DataFrame(5 + np.arange(4).reshape(2,2),
                   index=["a","b"], columns=["three","four"])

display(df1)
display(df2)

# DataFrame의 concatenate
result = pd.concat([df1, df2], 
                   axis=1, 
                   sort=True)  # index 정렬
display(result)

# DataFrame의 concatenate ( index를 새로 설정 )
result = pd.concat([df1, df2], 
                   axis=0, 
                   ignore_index=False, 
                   sort=False) # column 정렬
display(result)