Home > Software engineering >  Import multiple CSV files into pandas and merge those based on column values
Import multiple CSV files into pandas and merge those based on column values

Time:03-30

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.

CodePudding user response:

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')
  • Related