Here is my dataframe.
There is a date index and there are 4 symbols for each date.
I want to loop over each date for each symbol.
The ‘quantity’ column is calculated based on the ‘tot_value’ of the previous date. The ‘tot_value’ is computed for a specific date and is common for all symbols. The ‘value’ column varies for each symbol for each date.
It is an issue with the way I am using shift here. It does not reference the previous date value.
Instead it uses the default value of tot_value that I use while populating the dataframe.
However, in the final result the tot_value is getting computed correctly.
I am new to python and would appreciate any help with this loop.
dataframe
Here is my code.
import pandas as pd
create the dataframe
data = {‘symbol’: [‘A’, ‘B’, ‘C’, ‘D’,’A’, ‘B’, ‘C’, ‘D’,’A’, ‘B’, ‘C’, ‘D’,’A’, ‘B’, ‘C’, ‘D’],
‘date’:[’05/06/2024′,’05/06/2024′,’05/06/2024′,’05/06/2024′,
’05/07/2024′,’05/07/2024′,’05/07/2024′,’05/07/2024′,
’05/08/2024′,’05/08/2024′,’05/08/2024′,’05/08/2024′,
’05/09/2024′,’05/09/2024′,’05/09/2024′,’05/09/2024′],
‘tot_value’: [1000, 1000, 1000, 1000,1000, 1000, 1000, 1000,1000, 1000, 1000, 1000,1000, 1000, 1000, 1000],
‘mult’: [1, 1.1, 1.2, 1.3,1.4, 1.5, 1.6, 1.7,1.8, 1.9, 2, 2.1,2.2, 2.3, 2.4, 2.5],
‘quantity’: [0, 0, 0, 0,0, 0, 0, 0,0, 0, 0, 0,0, 0, 0, 0],
‘value’: [0, 0, 0, 0,0, 0, 0, 0,0, 0, 0, 0,0, 0, 0, 0],
}
df = pd.DataFrame(data)
df.set_index([‘date’], inplace = True)
loop by date index and symbol
for ind in df.index.unique():
for symbol in symbols:
df[‘quantity’][ind] = df[‘tot_value’][ind].shift(1) * df[‘mult’][ind]
df[‘value’][ind] = df[‘quantity’][ind] * 5
g = df.groupby('date')['value'].sum()
df['tot_value'][ind] = g.sum()
df
rumkey is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.