Import multiple CSV files into pandas and merge those based on column values

I have 4 dataframes:

import pandas as pd
df_inventory_parts = pd.read_csv('inventory_parts.csv')
df_colors = pd.read_csv('colors.csv')
df_part_categories = pd.read_csv('part_categories.csv')
df_parts = pd.read_csv('parts.csv')

Now I have merged them into 1 new dataframe like:

merged = pd.merge(
    left=df_inventory_parts, 
    right=df_colors, 
    how='left', 
    left_on='color_id', 
    right_on='id')

merged = pd.merge(
    left=merged, 
    right=df_parts, 
    how='left', 
    left_on='part_num', 
    right_on='part_num')

merged = pd.merge(
    left=merged, 
    right=df_part_categories, 
    how='left', 
    left_on='part_cat_id', 
    right_on='id')

merged.head(20)

This gives the correct dataset that I’m looking for. However, I was wondering if there’s a shorter way / faster way of writing this. Using pd.merge 3 times one seems a bit excessive.

>Solution :

You have a pretty clear section of code that does exactly what you want. You want to do three merges so using merge() three times is adequate rather than excessive.

You can make your code a bit shorter by using the fact DataFrames have a merge function so you don’t need the left argument. You can also chain them but I would point out my example does not look as neat and readable as your longer form code.

merged = df_inventory_parts.merge(
    right=df_colors, 
    how='left', 
    left_on='color_id', 
    right_on='id').merge(
    right=df_parts, 
    how='left', 
    left_on='part_num', 
    right_on='part_num').merge(
    right=df_part_categories, 
    how='left', 
    left_on='part_cat_id', 
    right_on='id')