How to get a value in a column as an index-CodePudding

I assign the eligible index value to A column and then df.ffill()

Now I want to use the value of A column as an index and assign the obtained value to the expcted column

I try df['expected']=df['price'][df['A']] but it doesn't work.

input

import pandas as pd
import numpy as np
d={'trade_date':['2021-08-10','2021-08-11','2021-08-12','2021-08-13','2021-08-14','2021-08-15','2021-08-16','2021-08-17','2021-08-18','2021-08-19','2021-08-20',],'price':[2,12,8,10,11,18,7,19,9,8,12],'cond':[True,False,True,False,True,False,True,False,True,True,True]}
df = pd.DataFrame(d)
df.index=pd.to_datetime(df.trade_date)
df['A']=df.index.where(df['cond'])
df['A']=df['A'].ffill()
df.to_clipboard()
df

expected result table

 trade_date price   cond    A   expected
2021/8/10   2   TRUE    2021/8/10   2 
2021/8/11   12  FALSE   2021/8/10   2 
2021/8/12   8   TRUE    2021/8/12   8 
2021/8/13   10  FALSE   2021/8/12   8 
2021/8/14   11  TRUE    2021/8/14   11 
2021/8/15   18  FALSE   2021/8/14   11 
2021/8/16   7   TRUE    2021/8/16   7 
2021/8/17   19  FALSE   2021/8/16   7 
2021/8/18   9   TRUE    2021/8/18   9 
2021/8/19   8   TRUE    2021/8/19   8 
2021/8/20   12  TRUE    2021/8/20   12

CodePudding user response：

Try this:

df['expected'] = df['A'].map(df['price'])
print(df)

            price   cond          A  expected
trade_date                                   
2021-08-10      2   True 2021-08-10         2
2021-08-11     12  False 2021-08-10         2
2021-08-12      8   True 2021-08-12         8
2021-08-13     10  False 2021-08-12         8
2021-08-14     11   True 2021-08-14        11
2021-08-15     18  False 2021-08-14        11
2021-08-16      7   True 2021-08-16         7
2021-08-17     19  False 2021-08-16         7
2021-08-18      9   True 2021-08-18         9
2021-08-19      8   True 2021-08-19         8
2021-08-20     12   True 2021-08-20        12

CodePudding user response：

Alternatively, you could use groupby and transform:

df.assign(expected=df.groupby(['A'])['price'].transform('first'))

-------------------------------------------------
            price   cond          A  expected
trade_date                                   
2021-08-10      2   True 2021-08-10         2
2021-08-11     12  False 2021-08-10         2
2021-08-12      8   True 2021-08-12         8
2021-08-13     10  False 2021-08-12         8
2021-08-14     11   True 2021-08-14        11
2021-08-15     18  False 2021-08-14        11
2021-08-16      7   True 2021-08-16         7
2021-08-17     19  False 2021-08-16         7
2021-08-18      9   True 2021-08-18         9
2021-08-19      8   True 2021-08-19         8
2021-08-20     12   True 2021-08-20        12
-------------------------------------------------

This approach groups by A, takes the first value of price for each group and assigns it to the corresponding group.