python - Performing A Computation When Two Values Are The Same

Question

I have two pandas dataframes first and second. For each Second_ID in second, I want to identify where in first the same ID is and then multiply the corresponding values. For example, 'Tony'in second corresponds to a value of 4 and 11. 'Tony' in first corresponds to a value of 20. I would like to somehow be able to multiply the the 4 and 11 by 20, and add the result as a new column to second.

In other words, I want to see where the ID in second matches the ID in first, multiply the two values, and add those new values as a column to the second dataframe. The entries in first are all unique, where as the entries in second are not necessarily unique.

df1 = pd.DataFrame({'First_ID': ['Jill', 'John', 'Marc', 'Tony', 'Bob']})
df2 = pd.DataFrame({'Value': [6, 10, 0, 20, 100]})
first = pd.DataFrame.join(df1,df2)

df4 = pd.DataFrame({'Second_ID': ['Jill', 'John', 'Tony', 'Bob', 'Tony']})
df5 = pd.DataFrame({'Value': [2, 3, 4, 5, 11]})
second = pd.DataFrame.join(df4,df5)

------------------
   First_ID Value
0   Jill    6
1   John    10
2   Marc    0
3   Tony    20
4   Bob     100



  Second_ID Value
0   Jill    2
1   John    3
2   Tony    4
3   Bob     5
4   Tony    11

Output:
  Second_ID Value NewVal
0   Jill    2       12
1   John    3       30
2   Tony    4       80
3   Bob     5       500
4   Tony    11      220

question from:https://stackoverflow.com/questions/65944915/performing-a-computation-when-two-values-are-the-same

Answer 1

You can use map:

second['new_val'] = (second['Second_ID'].map(first.set_index('First_ID')['Value'])
                                        .fillna(1).mul(second['Value'])
                    )

Output:

  Second_ID  Value  new_val
0      Jill      2      2.0
1      John      3     30.0
2      Tony      4     80.0
3       Bob      5    500.0
4      Tony     11    220.0