Merging Dataframes With Different Dimensions And Related Data

I have 2 dataframes with different size with related data to be merged in an efficient way: master_df = pd.DataFrame({'kpi_1': [1,2,3,4]}, index=['dn1_app1

Solution 1:

If use pandas 0.25+ is possible pass array, here index to on parameter of merge with left join:

master_df = master_df.merge(guard_df, 
                            left_on=master_df.index.str.replace('_.+_', '_'), 
                            right_index=True, 
                            how='left')
print (master_df)
                  kpi_1  kpi_2  kpi_3
dn1_app1_bar.com      1110
dn1_app2_bar.com      2110
dn2_app1_foo.com      3220
dn2_app2_foo.com      4220

Solution 2:

Try this one:

>>> pd.merge(master_df.assign(guard_df_id=master_df.index.str.split("_").map(lambda x: "{0}_{1}".format(x[0], x[-1]))), guard_df, left_on="guard_df_id", right_index=True).drop(["guard_df_id"], axis=1)
                  kpi_1  kpi_2  kpi_3
dn1_app1_bar.com      1110
dn1_app2_bar.com      2110
dn2_app1_foo.com      3220
dn2_app2_foo.com      4220