Caculate Current Values Based On Pct_change And Previous Values In Pandas

For a dataframe as follows: type price pct date 0 a 10918.0 NaN 2019/6/1 1 a NaN 0.023631 2019/9/1 2 b 10379.0 NaN 2019/6/1 3

Solution 1:

If need guarenteed price in the date of 2019/9/1 based on pct and values from 2019/6/1 you can working with MultiIndex - for select columns are used tuples:

df = df[df.duplicated(subset=['type'], keep=False)]
df = df.pivot_table(index='type', columns='date')
df[('price', '2019/9/1')] = (df[('pct', '2019/9/1')]*df[('price', '2019/6/1')] + 
                             df[('price', '2019/6/1')])
df = df.stack().reset_index()
print (df)
  typedate       pct         price
0    a  2019/6/1       NaN  10918.000000
1    a  2019/9/1  0.023631  11176.003258
2    b  2019/6/1       NaN  10379.000000
3    b  2019/9/1  0.010984  10493.002936
4    c  2019/6/1       NaN   9466.000000
5    c  2019/9/1  0.177160  11142.996560
6    d  2019/6/1       NaN  13637.000000
7    d  2019/9/1  0.124661  15337.002057
8    e  2019/6/1       NaN  11774.000000
9    e  2019/9/1 -0.033124  11383.998024

If always only 2 datetimes per each group:

#removed duplicatesdf = df[df.duplicated(subset=['type'], keep=False)]
#sorting for guarateed orderingdf = df.sort_values(['type','date'])

df['price'] = df['price'].ffill().mul(df['pct']).add(df['price'].ffill(), fill_value=0)
print (df)
  type         price       pct      date
0    a  10918.000000       NaN  2019/6/1
1    a  11176.003258  0.023631  2019/9/1
2    b  10379.000000       NaN  2019/6/1
3    b  10493.002936  0.010984  2019/9/1
4    c   9466.000000       NaN  2019/6/1
5    c  11142.996560  0.177160  2019/9/1
6    d  13637.000000       NaN  2019/6/1
7    d  15337.002057  0.124661  2019/9/1
8    e  11774.000000       NaN  2019/6/1
9    e  11383.998024 -0.033124  2019/9/1

Solution 2:

Try using:

df = df[df.duplicated(subset=['type'], keep=False)]
df2 = df.copy()
df2['price'] = df2['price'].ffill()
df2['pct'] = df2['pct'].fillna(1)
df['price'][1::2] = df2['price'][1::2] + (df2['price']*df2['pct'])[1::2].round()

Output:

typepricepctdate0a10918.0NaN2019/6/11a11176.00.0236312019/9/12b10379.0NaN2019/6/13b10493.00.0109842019/9/14c9466.0       NaN2019/6/15c11143.00.1771602019/9/16d13637.0NaN2019/6/17d15337.00.1246612019/9/18e11774.0NaN2019/6/19e11384.0-0.0331242019/9/1