Pandas - fill a new column with a single value, based on .loc value within dataframe

Question

I have a DataFrame with a list of returns data. One row in the DataFrame contains the return for the benchmark, 'ASX', which in this example has a value of 6. (Values are updated using market data, so will change day to day, the below is for simplicity).

Example DataFrame:

import pandas as pd

IDs = ['ABC', 'DEF', 'GHI', 'ASX']
Returns = [5, 7, 10, 6]

data = {'ID': ['ABC', 'DEF', 'GHI', 'ASX'],
        'Ret': [5, 7, 10, 6]}

frame = pd.DataFrame(data)

bmk = frame.loc[frame['ID'] == ('ASX'), 'Ret']

frame['Rel Ret'] = frame['Ret'] - bmk
frame

The current output looks like this:

    ID  Ret Rel Ret
0   ABC 5   NaN
1   DEF 7   NaN
2   GHI 10  NaN
3   ASX 6   0.0

My problem is, I want to fill the whole column 'Rel Ret' with the value in Ret column minus 'bmk', i.e. I want to return values -1 (5-6), 1 (7-6), 4 (10-6), 0 (6-6). How do I do for the entire column?

akuiper · Accepted Answer · 2021-07-04 17:45:54Z

1

Your bmk is a Series object, when you subtract two pandas Series, their index are aligned, so only rows at same index are subtracted. If you are sure you have one value in bmk, you can extract the value using .iat[0] and then subtract with it, in which case, you are subtracting a scalar from a Series object and the calculation would be broadcasted to every element in the Series:

frame['Rel Ret'] = frame['Ret'] - bmk.iat[0]
frame
#    ID  Ret  Rel Ret
#0  ABC    5       -1
#1  DEF    7        1
#2  GHI   10        4
#3  ASX    6        0

answered Jul 4, 2021 at 17:45

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marmot Over a year ago

Thank you @Psidom, works perfectly. I hadn't come across .iat before.

Collectives™ on Stack Overflow

Pandas - fill a new column with a single value, based on .loc value within dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related