Using .csv headers (string dates) as variables

Question

I have a csv file of index rebalance information (component x rebalance effective date) and want to determine the number of days in between each reblance date. I've gotten to the point where I've been able to isolate the header row and convert each date from string to datetime. I am having issues figuring out how select a specific item in the list to subtract it from another to cal the delta. When I try to select a specific item I get the following error:

"TypeError: list indices must be integers or slices, not tuple".

I have tried converting the datetime results to int but I get:

"Attribute Error: 'list' object has no attribute 'astype'"

In other parts of my project I have simply indexed dates (dates in column[0]) and tested for location to pull the right data from other columns but that won't work here. I still need to find the delta(days) and convert that days number into an (int) so I can us it to iterate through the next step.

Here is the code to import and pull out the headers:

basketfile = "C:\\....\\Test_Baskets.csv"
rebaldates = pd.read_csv(basketfile, nrows=0)
newdates = [datetime.strptime(x, '%m/%d/%Y') for x in rebaldates]

this gives me:

In [141]: newdates
Out[141]: 
[datetime.datetime(2010, 12, 31, 0, 0),
 datetime.datetime(2007, 12, 31, 0, 0),
 datetime.datetime(2008, 6, 20, 0, 0),
 datetime.datetime(2008, 12, 19, 0, 0),
 datetime.datetime(2009, 6, 19, 0, 0),
 datetime.datetime(2009, 12, 18, 0, 0),
 datetime.datetime(2010, 6, 18, 0, 0),
 datetime.datetime(2010, 12, 17, 0, 0)]

I want to be able to use each element to calc the delta but I get this error when I try to select a specific element:

newdates[:, :8]
Traceback (most recent call last):
   File "<ipython-input-142-34368864ad97>", line 1, in <module>
    newdates[:, :8]
TypeError: list indices must be integers or slices, not tuple

I would expect that once the date strings have been converted to datetime I would be able to use them more like numbers but there is obviously another step I need to take in formatting either the items or how they are being housed. I'm just not sure what that is...

Patrick Artner · Answer 1 · 2019-03-26T17:46:27.763

You are using simply lists:

newdates = [datetime.strptime(x, '%m/%d/%Y') for x in rebaldates]
print(newdates)

Output:

[datetime.datetime(2010, 12, 31, 0, 0),
 datetime.datetime(2007, 12, 31, 0, 0),
 datetime.datetime(2008, 6, 20, 0, 0),
 datetime.datetime(2008, 12, 19, 0, 0),
 datetime.datetime(2009, 6, 19, 0, 0),
 datetime.datetime(2009, 12, 18, 0, 0),
 datetime.datetime(2010, 6, 18, 0, 0),
 datetime.datetime(2010, 12, 17, 0, 0)]

This

newdates[:, :8]

is numpy style slicing - it is not supported in normal lists - you can only slice with intergers: my_list[inclusive_start:exclusive_stop:steps] (all integers)

To calculate a (pairwise) delta you can do:

import datetime

newdates=[datetime.datetime(2010, 12, 31, 0, 0),
     datetime.datetime(2007, 12, 31, 0, 0),
     datetime.datetime(2008, 6, 20, 0, 0),
     datetime.datetime(2008, 12, 19, 0, 0),
     datetime.datetime(2009, 6, 19, 0, 0),
     datetime.datetime(2009, 12, 18, 0, 0),
     datetime.datetime(2010, 6, 18, 0, 0),
     datetime.datetime(2010, 12, 17, 0, 0)]

zipper = zip(newdates,newdates[1:])

delta = [(a-b) for a,b in zipper]

print(delta)
print( [d.days for d in delta] )

Output:

[datetime.timedelta(1096), datetime.timedelta(-172), datetime.timedelta(-182),
 datetime.timedelta(-182), datetime.timedelta(-182), datetime.timedelta(-182), 
 datetime.timedelta(-182)]
[1096, -172, -182, -182, -182, -182, -182]

See:

Thank you very much! So a follow up... To get positive numbers I modified: — All The Spam Goes Here, Mar 26 '19 at 17:45
@AllTheSpamGoesHere you can do `delta = [ max(a-b, b-a) for a,b in zipper]` if you want a positive result no matter the "direction" — Patrick Artner, Mar 26 '19 at 17:53
Sorry for the noob comment action here: Thank you very much! To get positive numbers I modified: delta = [(a-b) for ...] to read delta = [(b-a) for...] I set delta_d = [d.days for d in delta] which gives me the list of numbers (-1096, 172, 182,...) The next step was to set a loop using delta_d[1] as the end: dd = delta_d[1] for d in range(0,dd): 'do the thing' and that's what I needed to do! Thanks again! — All The Spam Goes Here, Mar 26 '19 at 18:08

Using .csv headers (string dates) as variables

1 Answers1