[PYTHON] Pandas ile veri birleştirme, inner, left, right ve outer join formülleri ve 'concat' kavramı.

pandas merge join.py

import pandas as pd


customers = {

    'customerID': [1,2,3,4],
    'firstName': ['Ahmet','Ali','Hasan','Canan'],
    'lastName' : ['Yılmaz','Korkmaz','Çelik','Toprak'],

}


orders = {
    'orderID' : [10,11,12,13],
    'customerID' : [1,2,5,7],
    'orderDate' : ['2010-07-04','2010-08-04','2010-08-08','2010-07-07']
}


df_customers = pd.DataFrame(customers, columns=['customerID','firstName','lastName'])
df_orders = pd.DataFrame(orders, columns=['orderID','customerID','orderDate'])


print(df_customers)
print(df_orders)



########################       INNER JOIN       ##############################
##############################################################################
# Sadece sipariş bilgisi ve müşteri bilgisi uyuşanlar gelsin. (ortak küme)
result = pd.merge(df_customers,df_orders,how='inner')

'''
           customerID firstName lastName  orderID   orderDate
        0           1     Ahmet   Yılmaz       10  2010-07-04
        1           2       Ali  Korkmaz       11  2010-08-04

'''
##############################################################################



#######################       LEFT JOIN       ################################
##############################################################################
# Sipariş bilgisi olmasa dahi tüm müşteriler gelsin.
result = pd.merge(df_customers,df_orders,how='left')

'''
           customerID firstName lastName  orderID   orderDate
        0           1     Ahmet   Yılmaz     10.0  2010-07-04
        1           2       Ali  Korkmaz     11.0  2010-08-04
        2           3     Hasan    Çelik      NaN         NaN
        3           4     Canan   Toprak      NaN         NaN
        
'''
##############################################################################



#######################       RIGHT JOIN      ################################
##############################################################################
# Müşteri bilgisi olmasa dahi tüm siparişler gelsin.
result = pd.merge(df_customers,df_orders,how='right')

'''
           customerID firstName lastName  orderID   orderDate
        0           1     Ahmet   Yılmaz       10  2010-07-04
        1           2       Ali  Korkmaz       11  2010-08-04
        2           5       NaN      NaN       12  2010-08-08
        3           7       NaN      NaN       13  2010-07-07

'''
##############################################################################



#######################       OUTER JOIN      ################################
##############################################################################
# Müşteri ve Sipariş bilgilerini alır, ortak kısımları birleştirir.
# Diğer kısımlar ise değer olmamasına rağmen birleştirilir.
result = pd.merge(df_customers,df_orders,how='outer')

'''
           customerID firstName lastName  orderID   orderDate
        0           1     Ahmet   Yılmaz     10.0  2010-07-04
        1           2       Ali  Korkmaz     11.0  2010-08-04
        2           3     Hasan    Çelik      NaN         NaN
        3           4     Canan   Toprak      NaN         NaN
        4           5       NaN      NaN     12.0  2010-08-08
        5           7       NaN      NaN     13.0  2010-07-07

'''
##############################################################################



                        ############################
                        ## CONCAT ILE BIRLESTIRME ##
                        ############################

customersA = {

    'customerID': [1,2,3,4],
    'firstName': ['Ahmet','Ali','Hasan','Canan'],
    'lastName' : ['Yılmaz','Korkmaz','Çelik','Toprak'],

}


customersB = {

    'customerID': [4,5,6,7],
    'firstName': ['Yağmur','Çınar','Cengiz','Can'],
    'lastName' : ['Bilgili','Turan','Yılmaz','Turan'],

}

df_customersA = pd.DataFrame(customersA,columns=['customerID','firstName','lastName'])
df_customersB = pd.DataFrame(customersB,columns=['customerID','firstName','lastName'])

''' Concat ile aynı bilgilere sahip, iki veya daha fazla DataFrame'i birleştirebiliriz. '''

#########################################################################
result = pd.concat([df_customersA,df_customersB])

'''
       customerID firstName lastName
    0           1     Ahmet   Yılmaz
    1           2       Ali  Korkmaz
    2           3     Hasan    Çelik
    3           4     Canan   Toprak
    0           4    Yağmur  Bilgili
    1           5     Çınar    Turan
    2           6    Cengiz   Yılmaz
    3           7       Can    Turan

'''

#########################################################################
result = pd.concat([df_customersA,df_customersB],axis = 1)

'''
       customerID firstName lastName  customerID firstName lastName
    0           1     Ahmet   Yılmaz           4    Yağmur  Bilgili
    1           2       Ali  Korkmaz           5     Çınar    Turan
    2           3     Hasan    Çelik           6    Cengiz   Yılmaz
    3           4     Canan   Toprak           7       Can    Turan

'''


print(result)