Inner Merge DataFrame With Identical Column Names And Apply Aggregate Function

I want to inner merge two dataFrames that both have some columns with same name. On the columns with same name, I don't want a new column with a suffix in merged dataFrame, but ins

Solution 1:

Use:

print (d1)
   col1  col3
0     1     6
1     2     3
print (d2)
   col1  col2
0     3     2
2     5     4

Your solution - add groupby by splitted columns names and aggregate sum:

df = pd.merge(d1, d2, how='inner', left_index=True, right_index=True)

df = df.groupby(lambda x: x.split('_')[0], axis=1).sum()
#alternative
#df = df.groupby(df.columns.str.split('_').str[0], axis=1).sum()

Here si simplier use concat by join='inner' with sum by columns:

df = pd.concat([d1, d2], axis=1, join='inner').sum(level=0, axis=1)
print (df)
   col1  col3  col2
0     4     6     2