I have a dataframe with unique values (df_values) that I want to subtract from all rows in a column of another dataframe (df)

>>> df_values
    Id  value  
0   1    2    
1   2    3    
2   3    2       

>>> df
    Id  T_air  
0   1    2    
1   1    4    
2   1    2    
3   2    3
4   2    4    
5   2    3    
6   3    4     

I can do it by defining a function like this:

def conv(x, y, p):
    if y == 1:
        return x-p[0]
    elif y == 2:
        return x-p[1]
    elif y == 3:
        return x-p[2]

df['norm']=df.apply(lambda x : conv(x['T_air'], x['Id'],df_value['value']), axis=1)

So the result would be:

>>> df
    Id  T_air  norm
0   1    2      0
1   1    4      2
2   1    2      0
3   2    3      0
4   2    4      1    
5   2    3      0    
6   3    4      2     

But since I have so many groupby items I would like to find a simpler way to achieve this. Any Ideas would help :)

CodePudding user response：

You can vectorize your by using map, you do not need to groupby:

df['norm'] = df['T_air'] - df['Id'].map(df_values.set_index('Id')['value'])

output:

   Id  T_air  norm
0   1      2     0
1   1      4     2
2   1      2     0
3   2      3     0
4   2      4     1
5   2      3     0
6   3      4     2

CodePudding user response：

Use Series.sub with mapping values by Series.map:

df['norm'] = df['T_air'].sub(df['Id'].map(df_value.set_index('Id')['value']))
print (df)
   Id  T_air  norm
0   1      2     0
1   1      4     2
2   1      2     0
3   2      3     0
4   2      4     1
5   2      3     0
6   3      4     2