python - 根据 Pandas 中的 pct_change 和之前的值计算当前值

Question

对于数据框如下:

   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     b      NaN  0.010984  2019/9/1
4     c   9466.0       NaN  2019/6/1
5     c      NaN  0.177160  2019/9/1
6     d  13637.0       NaN  2019/6/1
7     d      NaN  0.124661  2019/9/1
8     e  11774.0       NaN  2019/6/1
9     e      NaN -0.033124  2019/9/1
10    f      NaN  0.023124  2019/9/2

我希望首先过滤不重复的行:

df = df[df.duplicated(subset=['type'], keep=False)]

然后根据pct和2019/6/1的值计算2019/9/1日期的价格 .

最终结果将如下所示:

  type  price       pct      date
0    a  10918       NaN  2019/6/1
1    a  11176  0.023631  2019/9/1
2    b  10379       NaN  2019/6/1
3    b  10493  0.010984  2019/9/1
4    c   9466       NaN  2019/6/1
5    c  11143  0.177160  2019/9/1
6    d  13637       NaN  2019/6/1
7    d  15337  0.124661  2019/9/1
8    e  11774       NaN  2019/6/1
9    e  11384 -0.033124  2019/9/1

我怎样才能做到这一点？谢谢。

Answer 1

如果需要保证基于 2019/6/1 的百分比和 2019/6/1 的值在 2019/9/1 的价格，您可以使用 MultiIndex - 对于选择列使用的元组:

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)
  type      date       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

如果每组始终只有 2 个日期时间:

#removed duplicates
df = df[df.duplicated(subset=['type'], keep=False)]
#sorting for guarateed ordering
df = 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