Cannot convert input to Timestamp, bday_range(...) - Pandas/Python

Question

Looking to generate a number for the days in business days between current date and the end of the month of a pandas dataframe. E.g. 26/06/2017 - 4, 23/06/2017 - 5

I'm having trouble as I keep getting a Type Error:

TypeError: Cannot convert input to Timestamp

From line:

result['bdaterange'] = pd.bdate_range(pd.to_datetime(result['dte'], unit='ns').values, pd.to_datetime(result['bdate'], unit='ns').values)

I have a Data Frame result with the column dte in a date format and I'm trying to create a new column (bdaterange) as a simple integer/float that I can use to see how far from month end in business days it has.

Sample data:
           bid   ask  spread         dte        day      bdate
01:49:00  2.17  3.83    1.66  2016-12-20  20.858333 2016-12-30
02:38:00   2.2   3.8    1.60  2016-12-20  20.716667 2016-12-30
22:15:00  2.63  3.12    0.49  2016-12-20  21.166667 2016-12-30
03:16:00  1.63  2.38    0.75  2016-12-21  21.391667 2016-12-30
07:11:00  1.46  2.54    1.08  2016-12-21  21.475000 2016-12-30

I've tried BDay() and using that the day cannot be 6 & 7 in the calculation but have not got anywhere. I came across bdate_range which I believe will be exactly what I'm looking for, but the closest I've got gives me the error Cannot convert input to Timestamp.

My attempt is:

result['bdate'] = pd.to_datetime(result['dte']) + BMonthEnd(0)

result['bdaterange'] = pd.bdate_range(pd.to_datetime(result['dte'], unit='ns').values, pd.to_datetime(result['bdate'], unit='ns').values)

print(result['bdaterange'])

Not sure how to solve the error though.

jezrael · Accepted Answer · 2017-07-04 12:40:57Z

1

I think you need length of bdate_range for each row, so need custom function with apply:

#convert only once to datetime
result['dte'] = pd.to_datetime(result['dte'])

f = lambda x: len(pd.bdate_range(x['dte'], x['dte'] + pd.offsets.BMonthEnd(0)))
result['bdaterange'] = result.apply(f, axis=1)
print (result)
           bid   ask  spread        dte        day  bdaterange
01:49:00  2.17  3.83    1.66 2016-12-20  20.858333           9
02:38:00  2.20  3.80    1.60 2016-12-20  20.716667           9
22:15:00  2.63  3.12    0.49 2016-12-20  21.166667           9
03:16:00  1.63  2.38    0.75 2016-12-21  21.391667           8
07:11:00  1.46  2.54    1.08 2016-12-21  21.475000           8

answered Jul 4, 2017 at 12:40

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LLC Over a year ago

This works perfectly - thanks. Why did it have to use a len() function instead of values?

jezrael Over a year ago

I think there in each row get output bdate_range, what is array (Datetimeindex). and simplier get length of array is len

Collectives™ on Stack Overflow

Cannot convert input to Timestamp, bday_range(...) - Pandas/Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related