<jupyter_start><jupyter_text>Pandas - Merge Dataframes **Tags:** pandas python merging merge dataframes consolidate operations snippet dataframe **Author:** [<NAME>](https://www.linkedin.com/in/oludolapo-oketunji/) This notebook will help you understand how to use the pandas merge function. It explains how to merge two datasets together and consolidate multiple dataset into one.  Input  Import Library<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Create dataframes to be merged  Dataframe 1<jupyter_code># Creating values to be used as datasets
dict1 = {
    "student_id": [1,2,3,4,5,6,7,8,9,10],
    "student_name": ["Peter","Dolly","Maggie","David","Isabelle","Harry","Akin","Abbey","Victoria","Sam"],
    "student_course": np.random.choice(["Biology","Physics","Chemistry"], size=10)
}
# Create dataframe
df_1 = pd.DataFrame(dict1)
df_1<jupyter_output><empty_output><jupyter_text>Dataframe 2<jupyter_code># Creating values to be used as datasets
dict2 = {
    "student_id": np.random.choice([1,2,3,4,5,6,7,8,9,10], size=100),
    "student_grade": np.random.choice(["A","B","C","D","E","F"], size=100),
    "professors": np.random.choice(["<NAME>","<NAME>","<NAME>","<NAME>"], size=100),
}
# Create dataframe
df_2 = pd.DataFrame(dict2)  # OR Data2=pd.read_csv(filepath)
df_2<jupyter_output><empty_output><jupyter_text>Modelpd.merge: acts like an SQL inner join and joins based on similar columns or index unless specified to join differently  Merging dataframes with same values with same column namesUsing pd.merge(left, right) acts like sql inner join and only joins on the common column they have.It tries finding everything from the right and append to left 'student_id' is common to both so it has been merged into one and included all the other df_2 columns to df_1 table.<jupyter_code>df = pd.merge(df_1, df_2)<jupyter_output><empty_output><jupyter_text>Output  Display result<jupyter_code>df<jupyter_output><empty_output><jupyter_text>Other options  Specifiying the comon column using parameters "on"<jupyter_code>df = pd.merge(df_1, df_2, on="student_id")
df<jupyter_output><empty_output><jupyter_text>Specifying what kind of Joins you want since merging does inner joins by default - "inner" > Inner Join: INCLUDING ROWS OF FIRST AND SECOND ONLY IF THE VALUE IS THE SAME IN BOTH DATAFRAMES- "outer" > Outer Join: IT JOINS ALL THE ROWS OF FIRST AND SECOND DATAFRAMES TOGETHER AND CREATE NaN VALUE IF A ROW DOESN'T HAVE A VALUE AFTER JOINING- "left" > Left Join: INCLUDES ALL THE ROWS IN THE FIRST DATAFRAME AND ADDS THE COLUMNS OF SECOND DATAFRAME BUT IT WON'T INCLUDE THE ROWS OF THE SECOND DATAFRAME IF IT'S NOT THE SAME WITH THE FIRST- "right" > Right Join: INCLUDES ALL THE ROWS OF SECOND DATAFRAME AND THE COLUMNS OF THE FIRST DATAFRAME BUT WON'T INCLUDE THE ROWS OF THE FIRST DATAFRAME IF IT'S NOT SIMILAR TO THE SECOND DATAFRAME<jupyter_code>df = pd.merge(df_1, df_2, on="student_id", how='left')
df<jupyter_output><empty_output><jupyter_text>Merging dataframes with same values but different column namesWe add two more parameters :- Left_on means merge using this column name- Right_on means merge using this column namei.e merge both id and student_id togethersince they don't have same name, they will create different columns on the new table<jupyter_code>df_1 = df_1.rename(columns={"student_id": "id"}) # Renamed student_id to id so as to give this example
df_1
df = pd.merge(df_1, df_2, left_on="id", right_on="student_id")
df<jupyter_output><empty_output><jupyter_text>Merging with the index of the first dataframe<jupyter_code>df_1.set_index("id") # this will make id the new index for df_1
df = pd.merge(df_1, df_2, left_index=True, right_on="student_id")#the new index will be from index of df_2 where they joined
df<jupyter_output><empty_output><jupyter_text>Merging both table on their index i.e two indexes<jupyter_code>df_2.set_index("student_id") # making student_id the index of Data2
df = pd.merge(df_1, df_2, left_index=True, right_index=True) # new index will be from the left index unlike when joining only one index
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09995171414775471, 'fraction_numerical': 0.0159343312409464, 'mean_word_length': 6.345744680851064, 'pattern_counts': {'":': 7, '<': 46, '<?xml version=': 0, '>': 50, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '145350', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1413, 'n_tokens_neox': 1356, 'n_words': 461, 'path': 'Pandas/Pandas_Merge_Dataframes.ipynb', 'repo_name': 'krajai/testt', 'size': '4183'}