For what you want to do, the "toJulian" from the J datetime library, its
inverse, and a number of other goodies there will save you re-inventing the
wheel.
load '~addons/types/datetime/datetime.ijs'
names_rgsdatetime_ 0
DATECODES J0Date Linux0DateTime MONTHS MS0Date
TIMECODES WKDAYS
names_rgsdatetime_ 3
daysDiff escaped fmt fmtDate
fmtDateTime fmtTime fromJulian getDateFormats
getTimeFormats getTimeZoneInfo toDateTime toDayNo
toJulian tsDiff tsMinus tsPlus
If you look at the tests in "~addons/types/datetime/test/test_datetime.ijs' ",
you will find examples of how to use these.
For example, to generate all dates in 1952 in the format you've shown:
'YYYY-MM-DD' fmtDate toDayNo 1952 5 30 0 0 0 0
1952-05-30
st=. toDayNo dt0=. 1952 1 1 0 0 0 0
55516
end=. toDayNo dt1=. 1952 12 31 23 59 59 999
55882
dt0=. 1952 1 1 0 0 0 0
dt1=. 1952 12 31 23 59 59 999
dt0 tsDiff dt1
_1 0 0 0 0 1 _1
dt1 tsDiff dt0
0 11 30 23 59 59 0
end-st
366
-/toDayNo&>dt1;<dt0 NB. 1952 was a leap year
366
dts=. st+i.366 NB. All dates in 1952
(<'YYYY-MM-DD') fmtDate &.> 5{.dts
+----------+----------+----------+----------+----------+
|1952-01-01|1952-01-02|1952-01-03|1952-01-04|1952-01-05|
+----------+----------+----------+----------+----------+
So, if you've saved your table of data with each item boxed, you should be
able to compare your date column to this to find where the gaps are.
A1=. <;._1&>',',&.><;._2 ] LF (],[#~[ ~: [:{:]) CR-.~0 : 0
1952-05-22,261.78,264.02,261.54,263.33,1360000
1952-05-23,263.33,264.59,262.54,263.27,1150000
1952-05-24,263.27,263.47,262.84,263.23,300000
1952-05-26,263.23,264.87,262.62,264.22,940000
1952-05-27,264.22,265.17,262.82,263.92,1040000
1952-05-28,263.92,264.54,262.25,262.78,1130000
1952-05-29,262.78,263.51,261.62,262.94,1100000
1952-06-02,262.94,264.61,261.48,262.31,1190000
1952-06-03,262.31,262.82,260.83,262.09,940000
1952-06-04,262.09,264.20,261.91,263.67,1200000
1952-06-05,263.77,266.80,263.77,266.29,1410000
1952-06-06,266.29,268.95,266.21,268.03,1520000
1952-06-09,268.03,269.92,267.67,269.15,1270000
1952-06-10,269.15,269.15,266.76,267.67,1220000
1952-06-11,267.67,268.52,266.33,267.93,1190000
)
Special-purpose verb to convert from our formatted date to a timestamp:
ymd2ts=: 7 {. [: ; [: ".&> [: <;._1 '-' , ] NB. 'YYYY-MM-DD' -> year mo
day hour min sec ms
A1dts=. 0{"1 A1 NB. Dates from our sample in A1
'st end'=. (0{dts)-~toDayNo ymd2ts &> ({.,{:) A1dts
142 162
dts0=. st}.(>:end){.dts NB. All dates in test range
(<'YYYY-MM-DD') fmtDate &.> 5{.dts0
+----------+----------+----------+----------+----------+
|1952-05-22|1952-05-23|1952-05-24|1952-05-25|1952-05-26|
+----------+----------+----------+----------+----------+
Compare day numbers to find gaps:
]whMissing=. dts0 e. toDayNo ymd2ts&>0{"1 A1
1 1 1 0 1 1 1 1 0 0 0 1 1 1 1 1 0 0 1 1 1
(<'YYYY-MM-DD') fmtDate &.> dts0#~-.whMissing
+----------+----------+----------+----------+----------+----------+
|1952-05-25|1952-05-30|1952-05-31|1952-06-01|1952-06-07|1952-06-08|
+----------+----------+----------+----------+----------+----------+
I hope this helps,
Devon
On Mon, Mar 30, 2020 at 10:55 PM Raul Miller <[email protected]> wrote:
> Well... let's try working through this. If it goes well, we can tackle
> the others.
>
> For this problem, we might want to use several J libraries:
>
> https://code.jsoftware.com/wiki/Addons/tables/csv
> https://code.jsoftware.com/wiki/Addons/types/datetime
> https://code.jsoftware.com/wiki/Vocabulary/Printf
>
> And, here's an example implementation:
>
> require'csv dates format/printf'
> A1=: fixcsv 0 :0
> 1952-05-22,261.78,264.02,261.54,263.33,1360000
> 1952-05-23,263.33,264.59,262.54,263.27,1150000
> 1952-05-24,263.27,263.47,262.84,263.23,300000
> 1952-05-26,263.23,264.87,262.62,264.22,940000
> 1952-05-27,264.22,265.17,262.82,263.92,1040000
> 1952-05-28,263.92,264.54,262.25,262.78,1130000
> 1952-05-29,262.78,263.51,261.62,262.94,1100000
> 1952-06-02,262.94,264.61,261.48,262.31,1190000
> 1952-06-03,262.31,262.82,260.83,262.09,940000
> 1952-06-04,262.09,264.20,261.91,263.67,1200000
> 1952-06-05,263.77,266.80,263.77,266.29,1410000
> 1952-06-06,266.29,268.95,266.21,268.03,1520000
> 1952-06-09,268.03,269.92,267.67,269.15,1270000
> 1952-06-10,269.15,269.15,266.76,267.67,1220000
> 1952-06-11,267.67,268.52,266.33,267.93,1190000
> )
>
> fill_in_blank_dates=: 3 :0
> days=. todayno 0 ". ({."1 y) -.&.;:every '-'
> 'first last'=. (<./,>./) days
> alldays=. first+i.1+last-first
> zeros=. ((#alldays),_1+{:$y)$0
> dates=. '%4d-%2.2d-%2.2d'&sprintf@todate each alldays
> data=. (alldays,days) +//. zeros,0 ".every }."1 y
> dates,.<every data
> )
>
> Example use might be
> fill_in_blank_dates A1
>
> or
> makecsv fill_in_blank_dates A1
>
> That said, if you can think up good names, you might feel more
> comfortable breaking up some of that routine into smaller steps.
>
> You should also take some time to inspect intermediate results.
>
> For example:
>
> 0 ". '1952-06-11' -.&.;: '-'
> 1952 6 11
>
> This is the numeric form of
> '1952-06-11' -.&.;: '-'
> 1952 06 11
>
> Which could also have been expressed
> ;:inv (;:'1952-06-11') -. ;: '-'
> 1952 06 11
>
> And is based on J's tokenizing verb:
> ;:'1952-06-11'
> +----+-+--+-+--+
> |1952|-|06|-|11|
> +----+-+--+-+--+
>
> etc...
>
> It's better, though, if you spend a little time looking up the
> definitions of these things, and making sure you're comfortable seeing
> what they do to your data.
>
> The inverse is quite a bit simpler -- you look for all zero rows and
> remove them. The only awkwardness is that you've specified a character
> representation of the dates. This would be a little simpler without
> that:
>
> remove_blank_dates=: 3 :0
> y #~ -.*/@}."1(<0)=y
> )
>
> Note that this assumes that the non-date data is in the numeric form
> produced by the first routine...
>
> Anyways, I hope this helps.
>
> But let's work through any questions you have before moving on to any
> of the other mechanisms you had questions about.
>
> Thanks,
>
> --
> Raul
>
> On Mon, Mar 30, 2020 at 8:53 PM HH PackRat <[email protected]> wrote:
> >
> > I need your help, please! I also hope to learn more about using J in
> > other contexts from your responses.
> >
> > I'm writing some software to manipulate my extensive stock market data
> > in different ways, and I need *explicit* code (NOT tacit code) for
> > several functions, which I'm splitting between multiple messages to
> > keep related responses together. My first 3 function requests deal
> > with getting data into particular formats for subsequent processing by
> > my programs. The 4th request deals with retrieving related data from
> > a huge file.
> >
> > My first request actually requires 2 functions: a function and its
> > inverse. The two data sets below show two approaches to represent
> > market data--trading days and calendar days--and I need functions to
> > convert the data both ways. (For me, the tricky part is how to insert
> > dates [and data]--that is, seeking missing dates and processing
> > accordingly when converting A1 to A2.)
> > __________________________________________________
> >
> > Function #1a needs to convert data set A1 to data set A2 (that is,
> > from trading days to calendar days):
> >
> > DATA SET A1:
> >
> > 1952-05-22,261.78,264.02,261.54,263.33,1360000
> > 1952-05-23,263.33,264.59,262.54,263.27,1150000
> > 1952-05-24,263.27,263.47,262.84,263.23,300000
> > 1952-05-26,263.23,264.87,262.62,264.22,940000
> > 1952-05-27,264.22,265.17,262.82,263.92,1040000
> > 1952-05-28,263.92,264.54,262.25,262.78,1130000
> > 1952-05-29,262.78,263.51,261.62,262.94,1100000
> > 1952-06-02,262.94,264.61,261.48,262.31,1190000
> > 1952-06-03,262.31,262.82,260.83,262.09,940000
> > 1952-06-04,262.09,264.20,261.91,263.67,1200000
> > 1952-06-05,263.77,266.80,263.77,266.29,1410000
> > 1952-06-06,266.29,268.95,266.21,268.03,1520000
> > 1952-06-09,268.03,269.92,267.67,269.15,1270000
> > 1952-06-10,269.15,269.15,266.76,267.67,1220000
> > 1952-06-11,267.67,268.52,266.33,267.93,1190000
> >
> > DATA SET A2:
> >
> > 1952-05-22,261.78,264.02,261.54,263.33,1360000
> > 1952-05-23,263.33,264.59,262.54,263.27,1150000
> > 1952-05-24,263.27,263.47,262.84,263.23,300000
> > 1952-05-25,0,0,0,0,0
> > 1952-05-26,263.23,264.87,262.62,264.22,940000
> > 1952-05-27,264.22,265.17,262.82,263.92,1040000
> > 1952-05-28,263.92,264.54,262.25,262.78,1130000
> > 1952-05-29,262.78,263.51,261.62,262.94,1100000
> > 1952-05-30,0,0,0,0,0
> > 1952-05-31,0 0 0 0 0
> > 1952-06-01,0,0,0,0,0
> > 1952-06-02,262.94,264.61,261.48,262.31,1190000
> > 1952-06-03,262.31,262.82,260.83,262.09,940000
> > 1952-06-04,262.09,264.20,261.91,263.67,1200000
> > 1952-06-05,263.77,266.80,263.77,266.29,1410000
> > 1952-06-06,266.29,268.95,266.21,268.03,1520000
> > 1952-06-07,0,0,0,0,0
> > 1952-06-08,0,0,0,0,0
> > 1952-06-09,268.03,269.92,267.67,269.15,1270000
> > 1952-06-10,269.15,269.15,266.76,267.67,1220000
> > 1952-06-11,267.67,268.52,266.33,267.93,1190000
> > __________________________________________________
> >
> > Function #1b needs to convert data set A2 to data set A1 (that is,
> > from calendar days to trading days). I presume this would be easier
> > than Function #1a.
> > __________________________________________________
> >
> > Thanks in advance for any and all help you can give me!
> >
> > Harvey
> > ----------------------------------------------------------------------
> > For information about J forums see http://www.jsoftware.com/forums.htm
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
>
--
Devon McCormick, CFA
Quantitative Consultant
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
