4

I Have such DataFrame:

enter image description here

df = pd.DataFrame({'id': [111,111,111, 222,222,222],\
                   'Date': ['30.04.2020', '31.05.2020', '30.06.2020', \
                            '30.04.2020', '31.05.2020', '30.06.2020'],\
                   'Debt': [100,100,70, 200,200,200] , \
                   'Ear_coef': [0,0.2,0.2, 0,0,0.3]}) 
df['Date'] = pd.to_datetime(df['Date'] ) 
df['Contract'] = pd.DataFrame(df.groupby(['id']).apply(lambda x: x.Debt - x.Debt.shift(1))).reset_index().Debt
# df.groupby(['id']).
df

I need to get such DataFrame:

enter image description here

The start DataFrame:

  • the first column is contract id;
  • the second column is Date;
  • the third column is coeficient of prepayment (EAR);
  • 4-th column is contract payment;

The result DataFrame:

  • 5-th column is EAR. It equels Ear_coef(t) * Debt_with_EAR(t-1)
  • 6-th column is Debt_with_EAR. It equels Debt_with_EAR(t-1)+Contract(t)+EAR(t)

Ear and Debt_with_EAR at first date equels 0 and Debt at respectively.

I have tried to solve such task with apply. But I have not had a success since I need to use previous value which is also calculated. This answers do not help me Is there a way in Pandas to use previous row value in dataframe.apply when previous value is also calculated in the apply? since I Have hundreds id.

I will be grateful for the help.

Roman
  • 105
  • 5

1 Answers1

1

You are looking for .shift().

It does not lend itself easily for .apply() however. A work-around would be:

df['EAR'] = df['EAR_coef'] * df['Debt with EAR'].shift(1)

For you last column you might need .rolling(), but I am not sure about your formula? It seems never-ending.

gosuto
  • 5,422
  • 6
  • 36
  • 57
  • Your approuch does not work since you do not know df['Debt with EAR'] for all horizont of prediction. For example, for id 111 with your method df['EAR'] = [NaN, 20,NaN ]. It is not correct. – Roman Apr 11 '20 at 09:14
  • Yes but that is a problem with your formulas; `EAR` is dependent on `Debt_with_EAR` and `Debt_with_EAR` is dependent on `EAR`. – gosuto Apr 11 '20 at 09:23
  • As I describes `Ear` and `Debt_with_EAR` at first date equels 0 and `Debt` at respectively. But then at second date, I can calculate at the begining `EAR`, and then I can calculate `Debt_with_EAR` – Roman Apr 11 '20 at 16:24