I tinkered some ugly codes to deal with this but wanted to find out a pythonic way:
df = pd.DataFrame({'signal':[1,0,0,1,0,0,0,0,1,0,0,1,0,0],'product':['A','A','A','A','A','A','A','B','B','B','B','B','B','B'],'price':[1,2,3,4,5,6,7,1,2,3,4,5,6,7],'price_B':[1,1,1,4,4,4,4,0,2,2,2,5,5,5,]})
I want to create the 'Price_B' column. for every 'product' subgroup, Price_B equals to Price if 'signal' is 1. Price_B equals previous row's Price if signal is 0. If the subgroup starts with a 0 'signal', then 'price_B' will be kept at 0 until 'signal' turns 1.
Here are what I wrote:
dfb = df.groupby('product').get_group('B')
for i in dfb.index:
if dfb.loc[i, 'signal'] == 1:
dfb.loc[i, 'test'] = dfb.loc[i, 'price']
else:
try:
dfb.loc[i, 'test'] = dfb.loc[i - 1, 'test']
except KeyError:
dfb.loc[i, 'test'] = 0
I know these codes are not legal. Can someone help?