Htykuut

This is hard to explain but I will try to represent this in a small example:

NDD = 11/1/2018

number of payments:

1 0 2 0 2 1 1 0 2 1 1 1

Since the first month starts with 11 in NDD then the first element of my list will be 11, to compute the next element I take the first month (11) and subtract the first payment 1 and then the second element is 10. This proceeds and the pattern is clear if you follow the logic so I will have

11 10 10 8 8 6 5 4 4 2 1 12

To make it even more clear:

number_of_payments = [1 0 2 0 2 1 1 0 2 1 1 1]

Algorithm:

Step 1 - Create an empty list:

dates = 

Step 2 - Append the first month of NDD to the first index of dates

dates.append(NDD.month)

Step 3 - Now perform this formula:

for i in range(1,12):

dates[i] = (dates[i-1] + 12 - number_of_payments[i-1]) % 12

Step 4 - The final result will be

dates = [11 10 10 8 8 6 5 4 4 2 1 12]

Although I was able to do this I need to factor in the years of what NDD started with so what I want to have is THE RESULT SHOULD BE:

11/18 10/18 10/18 8/18 8/18 6/18 5/18 4/18 4/18 2/18 1/18 12/17

Now to go with what I have. This is what I have for NDD:

print(type(NDD))

Here is a view values from NDD

print(NDD[0:3])

0   2018-08-01

1   2018-07-01

2   2018-11-01

Here are the number_of_payments information:

print(type(number_of_payments))

<class 'list'>

Here is the first row (same as the example above)

print(number_of_payments[0])

[ 0.  1.  0.  1.  1.  1.  0.  5.  1.  0.  2.  1.]

This is what I am trying to do to get the result but it does not work:

dates = 

for i in range(len(number_of_payments)):

    dates.append([NDD[i]])

    for j in range(1, len(number_of_payments[i])):

        dates[i].append((dates[i][j-1] + 12 - number_of_payments[i][j-1]) % 12)

for date_row in dates:

    for n, i in enumerate(date_row):

        if i == 0:

            date_row[n] = 12

print(dates[0])

I get this error:

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

<ipython-input-123-907a0962fd65> in <module>()

      4     dates.append([NDD[i]])

      5     for j in range(1, len(number_of_payments[i])):

----> 6         dates[i].append((dates[i][j-1] + 12 - number_of_payments[i][j-1]) % 12)

      7 for date_row in dates:

      8     for n, i in enumerate(date_row):



pandas/_libs/tslib.pyx in pandas._libs.tslib._Timestamp.__add__ (pandas_libstslib.c:22331)()



ValueError: Cannot add integral value to Timestamp without freq.

I hope this is clear.

Entire Code:

# In[9]:





# Import modules

import numpy as np

import pandas as pd

import datetime as dt

from functools import reduce

import datetime

from dateutil.relativedelta import *





# In[10]:





# Import data file

df = pd.read_csv("Paystring Data.csv")

df.head()





# In[11]:





# Get column data into a list

x = list(df)





# In[12]:





# Append column data into cpi, NDD, and as of dates

NDD = df['NDD 8/31']

cpi = df['Contractual PI']

as_of_date = pd.Series(pd.to_datetime(df.columns.str[:8], errors='coerce'))

as_of_date = as_of_date[1:13]

payment_months =  pd.to_datetime(as_of_date, errors = 'coerce').dt.month.tolist()





# In[13]:





# Get cash flows

cf = df.iloc[:,1:13].replace('[^0-9.]', '', regex=True).astype(float)

cf = cf.values





# In[14]:





# Calculate number of payments

number_of_payments = 

i = 0

while i < len(cpi):

    number_of_payments.append(np.round_(cf[:i + 1] / cpi[i]))

    i = i + 1





# In[15]:





# Calculate the new NDD dates

# dates = 

# for i in range(len(number_of_payments)):

#     dates.append([NDD_month[i]])

#     for j in range(1, len(number_of_payments[i][0])):

#         dates[i].append((dates[i][j-1] + 12 - number_of_payments[i][0][j-1]) % 12)

# print(dates[0])

d = 

for i in range(len(number_of_payments)):

    d.append(datetime.datetime.strptime(NDD[i], '%m/%d/%Y'))

def calc_payment(previous_payment,i):

    return previous_payment+relativedelta(months=(-1*i)) 

dates = [d]

for p in number_of_payments:

    dates += [calc_payment(result[-1],p)]











# In[ ]:





# Calculate paystring

paystring = 

for i in range(len(payment_months)):

    for j in range(len(dates[i])):

        if payment_months[i] < dates[i][j]:

            paystring.append(0)

        elif NDD_day[j] > 1:

            paystring.append((payment_months[i] + 12 - dates[i][j]) % 12)

        else:

            paystring.append( (payment_months[i] + 12 - dates[i][j]) + 1) % 12)

print(paystring[0])

I am currently stuck on implementing Arnon Rotem-Gal-Oz solution to adapt to this. Here is also a screen shot of the data frame. Please let me know if more information would help.

Update:

I cannot seem to get any good answers since the only person that had a close solution deleted it. I have now posted this to https://www.codementor.io/u/dashboard/my-requests/5p8xirscop?from=active. Paying 100 USD for anyone to give me a complete solution and I mean totally complete not just sort of complete.

Edit:

I try to run this code

import numpy as np

import pandas as pd

from datetime import datetime, timedelta

from functools import reduce

from dateutil.relativedelta import *

df=pd.read_csv('Paystring Data.csv')

cpi=df['Contractual PI']

start=df['NDD 8/31'].apply(pd.to_datetime).astype(datetime)

cf = df.iloc[:,1:13].replace('[^0-9.]', '', regex=True).astype(float)

payments =  cf.apply(lambda p: round(p/cpi))

diffs=payments.cumsum(axis=1).applymap(lambda i: relativedelta(months=(-1*i)))

payments=diffs.apply(lambda x: start+x)

result=pd.concat([start,payments],axis=1)

and I am getting this error:

---------------------------------------------------------------------------

TypeError                                 Traceback (most recent call last)

~AppDataLocalContinuumanaconda3libsite-packagespandascoreops.py in na_op(x, y)

    657             result = expressions.evaluate(op, str_rep, x, y,

--> 658                                           raise_on_error=True, **eval_kwargs)

    659         except TypeError:



~AppDataLocalContinuumanaconda3libsite-packagespandascorecomputationexpressions.py in evaluate(op, op_str, a, b, raise_on_error, use_numexpr, **eval_kwargs)

    210         return _evaluate(op, op_str, a, b, raise_on_error=raise_on_error,

--> 211                          **eval_kwargs)

    212     return _evaluate_standard(op, op_str, a, b, raise_on_error=raise_on_error)



~AppDataLocalContinuumanaconda3libsite-packagespandascorecomputationexpressions.py in _evaluate_numexpr(op, op_str, a, b, raise_on_error, truediv, reversed, **eval_kwargs)

    121     if result is None:

--> 122         result = _evaluate_standard(op, op_str, a, b, raise_on_error)

    123 



~AppDataLocalContinuumanaconda3libsite-packagespandascorecomputationexpressions.py in _evaluate_standard(op, op_str, a, b, raise_on_error, **eval_kwargs)

     63     with np.errstate(all='ignore'):

---> 64         return op(a, b)

     65 



~AppDataLocalContinuumanaconda3libsite-packagesdateutilrelativedelta.py in __radd__(self, other)

    390     def __radd__(self, other):

--> 391         return self.__add__(other)

    392 



~AppDataLocalContinuumanaconda3libsite-packagesdateutilrelativedelta.py in __add__(self, other)

    362                 month += 12

--> 363         day = min(calendar.monthrange(year, month)[1],

    364                   self.day or other.day)



~AppDataLocalContinuumanaconda3libcalendar.py in monthrange(year, month)

    123         raise IllegalMonthError(month)

--> 124     day1 = weekday(year, month, 1)

    125     ndays = mdays[month] + (month == February and isleap(year))



~AppDataLocalContinuumanaconda3libcalendar.py in weekday(year, month, day)

    115        day (1-31)."""

--> 116     return datetime.date(year, month, day).weekday()

    117 



TypeError: integer argument expected, got float



During handling of the above exception, another exception occurred:



TypeError                                 Traceback (most recent call last)

~AppDataLocalContinuumanaconda3libsite-packagespandascoreops.py in safe_na_op(lvalues, rvalues)

    681             with np.errstate(all='ignore'):

--> 682                 return na_op(lvalues, rvalues)

    683         except Exception:



~AppDataLocalContinuumanaconda3libsite-packagespandascoreops.py in na_op(x, y)

    663                 mask = notnull(x) & notnull(y)

--> 664                 result[mask] = op(x[mask], _values_from_object(y[mask]))

    665             elif isinstance(x, np.ndarray):



~AppDataLocalContinuumanaconda3libsite-packagesdateutilrelativedelta.py in __radd__(self, other)

    390     def __radd__(self, other):

--> 391         return self.__add__(other)

    392 



~AppDataLocalContinuumanaconda3libsite-packagesdateutilrelativedelta.py in __add__(self, other)

    362                 month += 12

--> 363         day = min(calendar.monthrange(year, month)[1],

    364                   self.day or other.day)



~AppDataLocalContinuumanaconda3libcalendar.py in monthrange(year, month)

    123         raise IllegalMonthError(month)

--> 124     day1 = weekday(year, month, 1)

    125     ndays = mdays[month] + (month == February and isleap(year))



~AppDataLocalContinuumanaconda3libcalendar.py in weekday(year, month, day)

    115        day (1-31)."""

--> 116     return datetime.date(year, month, day).weekday()

    117 



TypeError: integer argument expected, got float



During handling of the above exception, another exception occurred:



TypeError                                 Traceback (most recent call last)

<ipython-input-1-6cf75731780d> in <module>()

     10 payments =  cf.apply(lambda p: round(p/cpi))

     11 diffs=payments.cumsum(axis=1).applymap(lambda i: relativedelta(months=(-1*i)))

---> 12 payments=diffs.apply(lambda x: start+x)

     13 result=pd.concat([start,payments],axis=1)



~AppDataLocalContinuumanaconda3libsite-packagespandascoreframe.py in apply(self, func, axis, broadcast, raw, reduce, args, **kwds)

   4260                         f, axis,

   4261                         reduce=reduce,

-> 4262                         ignore_failures=ignore_failures)

   4263             else:

   4264                 return self._apply_broadcast(f, axis)



~AppDataLocalContinuumanaconda3libsite-packagespandascoreframe.py in _apply_standard(self, func, axis, ignore_failures, reduce)

   4356             try:

   4357                 for i, v in enumerate(series_gen):

-> 4358                     results[i] = func(v)

   4359                     keys.append(v.name)

   4360             except Exception as e:



<ipython-input-1-6cf75731780d> in <lambda>(x)

     10 payments =  cf.apply(lambda p: round(p/cpi))

     11 diffs=payments.cumsum(axis=1).applymap(lambda i: relativedelta(months=(-1*i)))

---> 12 payments=diffs.apply(lambda x: start+x)

     13 result=pd.concat([start,payments],axis=1)



~AppDataLocalContinuumanaconda3libsite-packagespandascoreops.py in wrapper(left, right, name, na_op)

    719                 lvalues = lvalues.values

    720 

--> 721         result = wrap_results(safe_na_op(lvalues, rvalues))

    722         return construct_result(

    723             left,



~AppDataLocalContinuumanaconda3libsite-packagespandascoreops.py in safe_na_op(lvalues, rvalues)

    690                 if is_object_dtype(lvalues):

    691                     return libalgos.arrmap_object(lvalues,

--> 692                                                   lambda x: op(x, rvalues))

    693             raise

    694 



pandas_libsalgos_common_helper.pxi in pandas._libs.algos.arrmap_object()



~AppDataLocalContinuumanaconda3libsite-packagespandascoreops.py in <lambda>(x)

    690                 if is_object_dtype(lvalues):

    691                     return libalgos.arrmap_object(lvalues,

--> 692                                                   lambda x: op(x, rvalues))

    693             raise

    694 



~AppDataLocalContinuumanaconda3libsite-packagesdateutilrelativedelta.py in __radd__(self, other)

    389 

    390     def __radd__(self, other):

--> 391         return self.__add__(other)

    392 

    393     def __rsub__(self, other):



~AppDataLocalContinuumanaconda3libsite-packagesdateutilrelativedelta.py in __add__(self, other)

    361                 year -= 1

    362                 month += 12

--> 363         day = min(calendar.monthrange(year, month)[1],

    364                   self.day or other.day)

    365         repl = {"year": year, "month": month, "day": day}



~AppDataLocalContinuumanaconda3libcalendar.py in monthrange(year, month)

    122     if not 1 <= month <= 12:

    123         raise IllegalMonthError(month)

--> 124     day1 = weekday(year, month, 1)

    125     ndays = mdays[month] + (month == February and isleap(year))

    126     return day1, ndays



~AppDataLocalContinuumanaconda3libcalendar.py in weekday(year, month, day)

    114     """Return weekday (0-6 ~ Mon-Sun) for year (1970-...), month (1-12),

    115        day (1-31)."""

--> 116     return datetime.date(year, month, day).weekday()

    117 

    118 



TypeError: ('integer argument expected, got float', 'occurred at index Aug 2018(P&I Applied)')

This is for Python 3 (you need to pip install python-dateutil).
(edited per comments)

df=pd.read_csv('Paystring Data.csv')

cpi=df['Contractual PI']

start=df['NDD 8/31'].apply(pd.to_datetime).astype(datetime)

cf = df.iloc[:,1:13].replace('[^0-9.]', '', regex=True).astype(float)

payments =  cf.apply(lambda p: round(p/cpi))

diffs=payments.cumsum(axis=1).applymap(lambda i: relativedelta(months=(-1*i)))

payments=diffs.apply(lambda x: start+x)

result=pd.concat([start,payments],axis=1)

pd.to_datetime + np.cumsum

Since you are using Pandas, I recommend you take advantage of the vectorised methods available to Pandas / NumPy. In this case, it seems like you wish to subtract the cumulative sum of a list from a fixed starting point.

import pandas as pd

import numpy as np



NDD = '11/1/2018'

date = pd.to_datetime(NDD)

number_of_payments = [1, 0, 2, 0, 2, 1, 1, 0, 2, 1, 1, 1]



res = date.month - np.cumsum([0] + number_of_payments[:-1])

res[res <= 0] += 12



print(res)



array([11, 10, 10,  8,  8,  6,  5,  4,  4,  2,  1, 12], dtype=int32)

You haven't provided an input dataframe, so it's difficult to determine exactly what help you need to implement the above logic, but it is easily extendable to larger data sets.

Python3 (datetime & list)
Comments are in-lined.

from datetime import datetime, timedelta





def calc_payments(ndd: str, num_of_payments: list):

    def subtract_months(month: datetime, num: int):

        # Make sure given month is at 1 day of month

        month = month.replace(day=1)

        for _ in range(num):

            # Subtract the date by 1 day to calc prev month last day

            # Change the calculated prev month last day to 1st day

            month = (month - timedelta(days=1)).replace(day=1)

        return month



    ndd_date: datetime = datetime.strptime(ndd, '%m/%d/%Y')

    payments = list()



    payments.append(ndd_date)



    # Loop/logic as described in Step 3 by OP

    for i in range(1, len(num_of_payments)):

        ndd_date = subtract_months(ndd_date, num_of_payments[i - 1])

        payments.append(ndd_date)



    return payments





if __name__ == '__main__':

    NDD = "11/1/2018"

    number_of_payments = [1, 0, 2, 0, 2, 1, 1, 0, 2, 1, 1, 1]

    for f in calc_payments(NDD, number_of_payments):

        # Format date to month/2-digit-year

        print(f"{f.month}/{f.strftime('%y')}", end=" ")