Pandas DataFrame update and sum by matching multi-column index from another Pandas Series

user_103780 0
Viewed 218

I have

df =

     B     TF    C   N
0  356   True  714   1
1  357   True  718   2
2  358   True  722   3
3  359   True  726   4
4  360  False  730   5

lt =

 B    C  
356  714    223
360  730    101
400  800    200
Name: N, dtype: int64

type(lt) => pandas.core.series.Series

I like to treat the series lt as a multi-column lookup table

So, if keys B and C from dataframe are found exactly in the Series index, I like to update the dataframe by summing the corresponding values of N.

So my final dataframe should look like:

       B     TF    C  N
0    356   True  714  224
1    357   True  718  2
2    358   True  722  3
3    359   True  726  4
4    360  False  730  106

How should I go about this? I tried various option such as :

df['N'] = df['N'] + df.apply(lambda x:lt[x[['B','C']]],axis=1)

But it gives:

IndexError: only integers, slices (:), ellipsis (...), numpy.newaxis (None) and integer or boolean arrays are valid indices

Also:

df.apply(lambda x:lt[x.B,x.C],axis=1)

raises KeyError (357, 718)

How should I go about this? Thanks.

pandas dataframe merge python
2 Answers

Use merge and eval:

out = df.merge(lt.reset_index(), on=['B', 'C'], how='left') \
        .fillna(0).eval('N = N_x + N_y') \
        .drop(columns=['N_x', 'N_y'])

>>> out
     B     TF    C      N
0  356   True  714  224.0
1  357   True  718    2.0
2  358   True  722    3.0
3  359   True  726    4.0
4  360  False  730  106.0
user_152399510

Your approach is close. Just need to fine-tune the way you map the value pair of columns B & C to the index of lt. See below for details:

You can use .apply() + .map() + fillna(), as follows:

Turn the value pair of columns B & C into tuple before mapping lt so that you can get the mapped values from lt. For values not in lt, we set it to default 0 by fillna(0):

df['N'] =  df['N'] + df[['B', 'C']].apply(tuple, axis=1).map(lt).fillna(0, downcast='infer')

Result:

print(df)


     B     TF    C    N
0  356   True  714  224
1  357   True  718    2
2  358   True  722    3
3  359   True  726    4
4  360  False  730  106
user_150706970
Related