0

I have a DataFrame like the below, where the Diff column is Value - PreviousValue.

Date        Value   Diff
2010-01-01  100     Na
2010-02-01  110     10
2010-03-01  130     20
2010-04-01  100     -30
2010-05-01  Na      20
2010-06-01  Na      30

As you can see, some of the Values are missing, even though we have the Diff. I wish to reconstruct the table so that the Values are correct:

Date        Value   Diff
2010-01-01  100     Na
2010-02-01  110     10
2010-03-01  130     20
2010-04-01  100     -30
2010-05-01  120     20
2010-06-01  150     30

How can this be done?

gberger
  • 2,813
  • 3
  • 28
  • 50

1 Answers1

1

Use fillna by reverse decoding values with ffill and cumsum

In [1525]: df['Value'].fillna(df['Value'].ffill() + df['Diff'].cumsum())
Out[1525]:
0    100.0
1    110.0
2    130.0
3    100.0
4    120.0
5    150.0
Name: Value, dtype: float64

In [1526]: df['Value'] = df['Value'].fillna(df['Value'].ffill() + df['Diff'].cumsum())

In [1527]: df
Out[1527]:
         Date  Value  Diff
0  2010-01-01  100.0   NaN
1  2010-02-01  110.0  10.0
2  2010-03-01  130.0  20.0
3  2010-04-01  100.0 -30.0
4  2010-05-01  120.0  20.0
5  2010-06-01  150.0  30.0
Zero
  • 74,117
  • 18
  • 147
  • 154
  • This works for my case, with the cumsum. However, what if it were more general? Say I have a column Value, some of its values are missing. Each Value is a function of the previous value and some other column, and I want to reconstruct the value column. – gberger Sep 20 '17 at 07:29
  • I guess similar to this: https://stackoverflow.com/questions/34855859/is-there-a-way-in-pandas-to-use-previous-row-value-in-dataframe-apply-when-previ – gberger Sep 20 '17 at 07:32