Datetime manipulation with lists

Question

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\_libs\tslib.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)
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\ops.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:

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\computation\expressions.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)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\computation\expressions.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 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\computation\expressions.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 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\dateutil\relativedelta.py in __radd__(self, other)
    390     def __radd__(self, other):
--> 391         return self.__add__(other)
    392 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\dateutil\relativedelta.py in __add__(self, other)
    362                 month += 12
--> 363         day = min(calendar.monthrange(year, month)[1],
    364                   self.day or other.day)

~\AppData\Local\Continuum\anaconda3\lib\calendar.py in monthrange(year, month)
    123         raise IllegalMonthError(month)
--> 124     day1 = weekday(year, month, 1)
    125     ndays = mdays[month] + (month == February and isleap(year))

~\AppData\Local\Continuum\anaconda3\lib\calendar.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)
~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\ops.py in safe_na_op(lvalues, rvalues)
    681             with np.errstate(all='ignore'):
--> 682                 return na_op(lvalues, rvalues)
    683         except Exception:

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\ops.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):

~\AppData\Local\Continuum\anaconda3\lib\site-packages\dateutil\relativedelta.py in __radd__(self, other)
    390     def __radd__(self, other):
--> 391         return self.__add__(other)
    392 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\dateutil\relativedelta.py in __add__(self, other)
    362                 month += 12
--> 363         day = min(calendar.monthrange(year, month)[1],
    364                   self.day or other.day)

~\AppData\Local\Continuum\anaconda3\lib\calendar.py in monthrange(year, month)
    123         raise IllegalMonthError(month)
--> 124     day1 = weekday(year, month, 1)
    125     ndays = mdays[month] + (month == February and isleap(year))

~\AppData\Local\Continuum\anaconda3\lib\calendar.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)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\frame.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)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\frame.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)

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\ops.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,

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\ops.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\_libs\algos_common_helper.pxi in pandas._libs.algos.arrmap_object()

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\core\ops.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 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\dateutil\relativedelta.py in __radd__(self, other)
    389 
    390     def __radd__(self, other):
--> 391         return self.__add__(other)
    392 
    393     def __rsub__(self, other):

~\AppData\Local\Continuum\anaconda3\lib\site-packages\dateutil\relativedelta.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}

~\AppData\Local\Continuum\anaconda3\lib\calendar.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

~\AppData\Local\Continuum\anaconda3\lib\calendar.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)')

Answer 1

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)

Answer 2

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=" ")

Answer 3

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.