1

I have two different dataframes of different length but have two columns in common.

enter image description here

enter image description here

I need to perform a math operation let say multiply the float value in VALUE column of 1st dataframe with value in NUMBER column in 2nd dataframe if the condition matches with same ID and Class.

Eg:

In 1st dataframe if ID is 103 and Class is A, then the value 2.301308 must be multiplied with the value 0.15 in 2nd dataframe whose ID is 103 and Class is A. Likewise for each ID and Class of two dataframes matches the value in VALUE column should be multiplied with value in NUMBER column.

I tried by df.assign function

df1.assign(VALUE = df1['VALUE']*(df2.NUMBER.loc[(df2.ID == df1.ID) & (df2.Class == df1.Class)]))

Got the error

ValueError: Can only compare identically-labeled Series objects

What went wrong or any other solution for this?

Thanks in advance!

Nandhakumar Rajendran
  • 393
  • 1
  • 3
  • 11

1 Answers1

1

Use MultiIndex if unique MultiIndex values:

df11 = df1.set_index(['ID','Class'])
df11['VALUE'] = df11['VALUE'].mul(df2.set_index(['ID','Class'])['NUMBER'])
df = df11.reset_index()

Or use left join in DataFrame.merge and multiple column VALUE with NUMBER with DataFrame.pop for remove after this operation:

df = df1.merge(df2, on=['ID','Class'], how='left')
df['VALUE'] *= df.pop('NUMBER')

Or:

df1['VALUE'] *= df1.merge(df2, on=['ID','Class'], how='left')['NUMBER']
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks! it works fine. Can you just explain why this was not working ```df1.assign(VALUE = df1['VALUE']*(df2.NUMBER.loc[(df2.ID == df1.ID) & (df2.Class == df1.Class)]))``` – Nandhakumar Rajendran Dec 22 '21 at 07:14
  • @NandhakumarRajendran - because cannot compare both DataFrames with different indices, then raise error like posted in question. – jezrael Dec 22 '21 at 07:16
  • @NandhakumarRajendran - so `MultiIndex` create indices with both columns, so possible compare. Unfortunately here are not unique, so failed. if use `merge` get same indices in `df1` and `df`, so possible multiple. – jezrael Dec 22 '21 at 07:22