CODEWITHSUNDEEP
pythonpandasdataframe
Hamed
Hamed

Reputation: 797

Mapping in Pandas with multiple keys

I have two Pandas Datafrmae as

df:                         df2:
   col1 col2  val              col1 col2   s
0     1    a  1.2           0     1    a  0.90   
1     2    b  3.2           1     3    b  0.70
2     2    a  4.2           2     1    b  0.02
3     1    b -1.2           3     2    a  0.10

and I want to use df2['s'] and multiply it in df['val'] whenever the combination of ['col1', 'col2'] match. If a row does not match, I don't need to do the multiplication

I create a mapper 

mapper = df2.set_index(['col1','col2'])['s']

where I get

mapper
col1  col2
1     a       0.90
3     b       0.70
1     b       0.02
2     a       0.10
Name: s, dtype: float64

and I want to match it with df[['col1','col2']]

df[['col1','col2']]

   col1 col2
0     1    a
1     2    b
2     2    a
3     1    b

but when I do the mapping 

mapped_value = df[['col1','col2']].map(mapper)

I get the following error

AttributeError: 'DataFrame' object has no attribute 'map'

any hint?

Upvotes: 2

Views: 322

Answers (1)

jezrael
jezrael

Reputation: 863216

I think you need mul:

df = df2.set_index(['col1','col2'])['s'].mul(df.set_index(['col1','col2'])['val'])
print (df)

col1  col2
1     a       1.080
      b      -0.024
2     a       0.420
      b         NaN
3     b         NaN
dtype: float64

If need replace NaN:

df = df2.set_index(['col1','col2'])['s']
        .mul(df.set_index(['col1','col2'])['val'], fill_value=1)
print (df)
col1  col2
1     a       1.080
      b      -0.024
2     a       0.420
      b       3.200
3     b       0.700
dtype: float64

Upvotes: 1

Related Questions