# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Merging, Joining, and Concatenating
#
# There are 3 main ways of combining DataFrames together: Merging, Joining and Concatenating. In this lecture we will discuss these 3 methods with examples.
#
# ____

# ### Example DataFrames

import pandas as pd

df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3'],
                        'C': ['C0', 'C1', 'C2', 'C3'],
                        'D': ['D0', 'D1', 'D2', 'D3']},
                        index=[0, 1, 2, 3])

df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                        'B': ['B4', 'B5', 'B6', 'B7'],
                        'C': ['C4', 'C5', 'C6', 'C7'],
                        'D': ['D4', 'D5', 'D6', 'D7']},
                         index=[4, 5, 6, 7]) 

df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10'],
                        'B': ['B8', 'B9', 'B10'],
                        'C': ['C8', 'C9', 'C10']},
                        index=[8, 9, 10])

df1

df2

df3

# ## Concatenation
#
# Concatenation basically glues together DataFrames. Keep in mind that dimensions should match along the axis you are concatenating on. You can use **pd.concat** and pass in a list of DataFrames to concatenate together:

pd.concat([df1,df2])    # Concatenating on the default axis=0 (rows).For this axis df1 & df2 have values in each of the four columns A,B,C,D

pd.concat([df1,df2, df3])

pd.concat([df1,df2,df3],axis=1) # On axis=1 ie. cols, all the 3 dfs dont have values on cols A-D for all rows/indices 0 - 11, thus the NaNs where values are missing in either dfs.

# _____
# ## Example DataFrames

# +
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
   
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K4'],
                          'C': ['C0', 'C1', 'C2', 'C3'],
                          'D': ['D0', 'D1', 'D2', 'D3']})    
# -

left

right

# ___

# ## Merging
#
# The **merge** function allows you to merge DataFrames together using a similar logic as merging SQL Tables together. For example:

# +
pd.merge(left,right,how='inner',on='key') # Merge df right and left (SQL-INNER: Returns records/rows that have matching values in both tables) on column 'key'. In this case, keys K0, K1 and K2 are common in to both dfs and are returned, K3 & K4 are not common to both dfs and are thus left out.

# SQL equivalent: 'SELECT * FROM left INNER JOIN right ON left.key = right.key;

# If the 'how' parameter is not specified, merge defaults to INNER.
# SQL Joins: https://www.w3schools.com/sql/sql_join.asp
# -

# Or to show a more complicated example:

# +
left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                        'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})
    
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                               'key2': ['K0', 'K0', 'K0', 'K0'],
                                  'C': ['C0', 'C1', 'C2', 'C3'],
                                  'D': ['D0', 'D1', 'D2', 'D3']})
# -

left

right

pd.merge(left, right, on=['key1', 'key2']) # In this case, a merge between K0 - K0 in both dfs is retuned, because row 0 in both dfs have these two key combinations. K1 - K0 combination appears twice in df 'right' on row index 1 and 2 but only once in df 'left' on row index 2.

pd.merge(left, right, how='outer', on=['key1', 'key2']) # Returns all key combinations from both dfs, where values are availble from both dfs, they are filled in, where values are missing for a particular key combination from a df, NaN is filled in instead.

left

right

pd.merge(left, right, how='right', on=['key1', 'key2']) # returns all the rows from the second df arg and only the matching rows from the first df arg i.e where both key combinations appear in both dfs.

pd.merge(left, right, how='left', on=['key1', 'key2']) # returns all the rows from the first df arg and only the matching rows from the second df arg i.e where both key combinations appear in both dfs.

# ## Joining
# Joining is a convenient method for combining the columns of two potentially differently-indexed DataFrames into a single result DataFrame. Similar to merge except the keys to join on are in the row indices and not in columns.

# +
left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                      index=['K0', 'K1', 'K2']) 

right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']},
                      index=['K0', 'K2', 'K3'])
# -

left

right

left.join(right) # Default to a left join i.e return all rows from the left df and on the matching rows from the right df. This is similar to how='left' as shown below

left.join(right, how='left') # Alternative syntax for above

left.join(right, how='right') # Performs a right join

left.join(right, how='inner') # performs an inner join i.e only returning rows that are common to both dfs

left.join(right, how='outer')   # Performs an outter join i.e returning all rows from both dfs but filling NaN values where values are not avaiable from either dfs.

# # Great Job!

StarcoderdataJupyterScriptsDedupFiltered