时间戳

首页 > 解决方案 > range() 用于计算带间隔的余额

python - range() 用于计算带间隔的余额

问题描述

我的输出如下：

        ISIN    DatesforAccrual          Days   Accrual@Coupon  Balance       AccrualYield
    0   Test1   October 22nd, 2019       0      0.00            4,875,358.20    nan
    1   Test1   February 8th, 2020       106    83,638.42       4,819,281.81    27,562.03
    2   Test1   August 8th, 2020         180    142,027.50      4,723,519.41    46,265.11
    3   Test1   February 8th, 2021       180    142,027.50      4,626,837.70    45,345.79
    4   Test1   June 19th, 2021          131    103,364.46      4,555,799.41    32,326.17
    5   Test2   July 31st, 2020          0      0.00            4,555,799.41    0.00
    6   Test2   September 21st, 2020    51      12,661.46       4,555,529.73    12,391.77
    7   Test2   March 21st, 2021        180     44,687.50       4,554,575.32    43,733.09
    8   Test2   June 19th, 2021          88     21,847.22       4,554,104.23    21,376.14
    9   Test3   January 1st, 2021         0      0.00           4,554,104.23    0.00
    10  Test3   April 3rd, 2021           92     8,025.98       4,568,423.73    22,345.47
    11  Test3   June 19th, 2021           76     6,630.16       4,580,310.92    18,517.34

代码如下


       import QuantLib as ql
        import pandas as pd
       import numpy as np
       import datetime

       data = pd.read_excel (r'C:\Users\Avishen\Desktop\Python\BONDDATA.xlsx')
       data['issuedate'] = data['issuedate'].dt.strftime('%d-%m-%Y')
       data['maturitydate'] = data['maturitydate'].dt.strftime('%d-%m-%Y')

       t_list = []
       t1_list = []
       t2_list = []
       t3_list = []
       t4_list = []

       for _, row in data.iterrows():
           today = ql.Date.todaysDate()
           issueDate = ql.Date(row['issuedate'], '%d-%m-%Y')
           maturityDate = ql.Date(row['maturitydate'], '%d-%m-%Y')
           tenor = ql.Period(ql.Semiannual)
           day_count = ql.Thirty360
           coupon=(row['coupon'])
           Tradeyield = (row['Tradeyield'])
           Bond_Price = (row['Bond_Price'])
           face_value=100
           Nominal_Amount=(row['Nominal_Amount'])
           calendar = ql.UnitedStates()
           businessConvention = ql.Unadjusted
           dateGeneration = ql.DateGeneration.Backward
           monthEnd = False
           schedule = ql.Schedule (issueDate, maturityDate, tenor,        calendar, ql.Unadjusted,ql.Unadjusted, dateGeneration, monthEnd)
           DatesinCoupon  =  list(schedule)
           Balance = 100

           DatesinCouponfiltered = list(filter(lambda score: score        <today, DatesinCoupon))
           DatesforAccrual = DatesinCouponfiltered + [today]

           NoofDaysinAccrual=[0]+[int(ql.Thirty360().yearFraction(start, end)*360) for start, end in zip(DatesforAccrual[:-1],        DatesforAccrual[1:])]

       Calculate Accrued interest in each coupon period 

           if day_count == ql.Thirty360:
               DC = (360)
           elif day_count == ql.Thirty365:
               DC = (365)
           else:
               DC = (366)
       
           AccruedCoupon = [(i * (coupon))/(DC) *face_value *Nominal_Amount/100 for i in NoofDaysinAccrual]

           t_list.append(row['ISIN'])
           t1_list.append(DatesforAccrual)
           t2_list.append(NoofDaysinAccrual)
           t3_list.append(AccruedCoupon)
           t4_list.append(Nominal_Amount*Bond_Price/100)
           new_df = pd.DataFrame({'ISIN':t_list,        'DatesforAccrual':t1_list,'Days':t2_list,'Accrual@Coupon':t3_list})
           s = new_df.apply(pd.Series.explode).reset_index(drop=True)
           s.loc[s.drop_duplicates('ISIN').index, "Balance"] = (t4_list)

           for i in range(1, len(s),1):
               s.loc[i, 'AccrualYield'] = s.loc[i-1,'Balance']*s.loc[i,'Days']* Tradeyield / DC
               s.loc[i , 'Balance'] = s.loc[i-1,'Balance']-s.loc[i,'Accrual@Coupon']+ s.loc[i,'AccrualYield']

excel中的值如下

ISIN    issuedate   maturitydate    coupon  Tradeyield  Bond_Price  MarketPrice Nominal_Amount
Test1   22-Oct-19   08-Aug-23   6.53%   2.37%       112.0772    110.787     4,350,000 
Test2   31-Jul-20   21-Mar-25   3.25%   3.30%       111.235     109.253     2,750,000 
Test3   01-Jan-21   03-Apr-24   2.51%   1.92%       109.4521    113.4561     1,251,235

我希望天平在第 4 行停止计算，并在第 6 行重新开始，然后在第 8 行再次停止并在第 10 行重新开始，然后在第 11 行停止。这将根据ISINdf 中的再次继续。我怎样才能做到这一点？

扩展输出：


    ISIN    DatesforAccrual     Days    Accrual@Coupon  AccrualYield    Balance
0   Test1   October 22nd, 2019          0   0       4,875,358.20
1   Test1   February 8th, 2020  106 83,638.42   34,021.87   4,825,741.65
2   Test1   August 8th, 2020    180 142,027.50  57,185.04   4,740,899.19
3   Test1   February 8th, 2021  180 142,027.50  56,179.66   4,655,051.35
4   Test1   June 19th, 2021     131 103,364.46  40,145.94   4,591,832.83
5   Test2   July 31st, 2020         0   0           3,058,962.50
6   Test2   September 21st, 2020    51  12,661.46   14,300.65   3,060,601.69
7   Test2   March 21st, 2021    180 44,687.50   50,499.93   3,066,414.12
8   Test2   June 19th, 2021     88  21,847.22   24,735.74   3,069,302.64
9   Test3   January 1st, 2021       0   0           1,369,502.98
10  Test3   April 3rd, 2021     92  8,025.98    11,549.48   1,373,026.48
11  Test3   June 19th, 2021     76  6,630.16    9,565.42    1,375,961.74

标签： pythonpandas

解决方案

s.groupby('ISIN')

然后的想法是将其链接到.agg()，.apply()等等。

此外，要遍历数据帧的行，您通常不需要编写 for 循环并使用s.loc[i, colname]. 使用.apply()或.sum()或其他操作。当您需要访问前一行（在该 ISIN 组中）时，请使用.shift(-1)

就像是：

s['AccrualYield'] = s.['Balance'].shift(-1) * s['NoofDaysinAccrual'] * Tradeyield / DC

如果您可以发布可重现的数据片段，那么我将尝试显示完整的表达式s['Balance']

推荐阅读