CODEWITHSUNDEEP
pythonpandasfillnapandas-loc
Marmot
Marmot

Reputation: 79

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

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?

Upvotes: 0

Views: 661

Answers (1)

akuiper
akuiper

Reputation: 214957

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

Upvotes: 1

Related Questions