0
$\begingroup$

My script takes some data from IEX and then outputs a pandas dataframe: 

           date    open     high      low   close    volume       timestamp
148  2018-08-03  206.3064  208.0105  204.7621  207.2631  33447396  736909.0
149  2018-08-06  207.2730  208.5187  206.3463  208.3393  25425387  736912.0
150  2018-08-07  208.5884  208.7678  206.0374  206.3862  25587387  736913.0
151  2018-08-08  205.3299  207.0837  203.8052  206.5257  22525487  736914.0
152  2018-08-09  206.5556  209.0468  206.4758  208.1500  23492626  736915.0
153  2018-08-10  207.3600  209.1000  206.6700  207.5300  24611202  736916.0
154  2018-08-13  207.7000  210.9520  207.7000  208.8700  25890880  736919.0
155  2018-08-14  210.1550  210.5600  208.2600  209.7500  20748010  736920.0
156  2018-08-15  209.2200  210.7400  208.3300  210.2400  28807564  736921.0
157  2018-08-16  211.7500  213.8121  211.4700  213.3200  28500367  736922.0
158  2018-08-17  213.4400  217.9500  213.1600  217.5800  35426997  736923.0
159  2018-08-20  218.1000  219.1800  215.1100  215.4600  30287695  736926.0
160  2018-08-21  216.8000  217.1900  214.0250  215.0400  26159755  736927.0
161  2018-08-22  214.1000  216.3600  213.8400  215.0500  19018131  736928.0
162  2018-08-23  214.6500  217.0500  214.6000  215.4900  18883224  736929.0
163  2018-08-24  216.6000  216.9000  215.1100  216.1600  18476356  736930.0
164  2018-08-27  217.1500  218.7400  216.3300  217.9400  20525117  736933.0
165  2018-08-28  219.0100  220.5400  218.9200  219.7000  22776766  736934.0
166  2018-08-29  220.1500  223.4900  219.4100  222.9800  27254804  736935.0
167  2018-08-30  223.2500  228.2600  222.4000  225.0300  48793824  736936.0
168  2018-08-31  226.5100  228.8700  226.0000  227.6300  43340134  736937.0
169  2018-09-04  228.4100  229.1800  226.6300  228.3600  27390132  736941.0
170  2018-09-05  228.9900  229.6700  225.1000  226.8700  33332960  736942.0
171  2018-09-06  226.2300  227.3500  221.3000  223.1000  34289976  736943.0
172  2018-09-07  221.8500  225.3700  220.7100  221.3000  37619810  736944.0
173  2018-09-10  220.9500  221.8500  216.4700  218.3300  39516453  736947.0
174  2018-09-11  218.0100  224.2990  216.5600  223.8500  35749049  736948.0
175  2018-09-12  224.9400  225.0000  219.8400  221.0700  49278740  736949.0
176  2018-09-13  223.5200  228.3500  222.5700  226.4100  41706377  736950.0
177  2018-09-14  225.7500  226.8400  222.5220  223.8400  31999289  736951.0

When indexing this table I use different functions to select for example Week to date or month to date values such as this:

def mtd():
    today = date.today()
    start = date.today().replace(day=1)
    delta = today.days-start.days

The issue is, that the delta calculation doesn't exclude weekends, meaning my index is off when trying to extract data from this range of dates from the df. For example if I want data for the month of September, my program would calculate 2018-09-17 - 2018-09-01 = 16, then $177 - 16 = 161$, the value I need get get the first data point in September is $169$. Is there a way to do this also for holidays ?

|improve this question
$\endgroup$
1
$\begingroup$

Yes you can build holiday calendars using Pandas, see here: Pandas Timeseries Docs

But you don't really need it here, just create a DateTimeIndex from your given column and lookup the specific date in the list: 

import pandas as pd
df = pd.DataFrame(data=[['2018-08-03', 206.3064],
                        ['2018-08-06', 207.2730],
                        ['2018-08-07', 208.5884],
                        ['2018-08-08', 205.3299]], columns=['date', 'open'])
dt_idx = pd.DateTimeIndex(df['date'])
start_date = '2018-08-03'
today_date = '2018-08-08'
start_idx = dt_idx.get_loc(start_date)
# >>> 0
today_idx = dt_idx.get_loc(today_date)
# >>> 3
print(df.loc[start_idx, 'open']
# >>> 206.3064
print(df.loc[today_idx, 'open']
# >>> 205.3299
|improve this answer
$\endgroup$
  • $\begingroup$ Thank you for the detailed answer, is there a disadvantage to using "delta = np.busday_count( start, today )"? $\endgroup$ – Joan Arau Sep 17 '18 at 19:22
  • 1
    $\begingroup$ Besides the fact you have to directly specify the holidays which differ by geographies and by product, your table might have bad data (which is dropped) or mislabelled. From a coding perspective busday_count is a disaster waiting to happen. It is safer to index the specific values you know to be the ones you want directly from the table, than create the indices from another source you assume to be correct and then apply to the table you assume is in the same format and also has an uncorruptible structure. $\endgroup$ – Attack68♦ Sep 17 '18 at 19:29
  • 1
    $\begingroup$ Additionally you have more routes available to you for error trapping in my way. If you try to index a date and you find it isn't in the table you can react. If you use busday_count you will be returned an index as usual but not the right one and you won't realise the actual look up date isn't in the table so you have no error trap. $\endgroup$ – Attack68♦ Sep 17 '18 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.