Here is how I would first approach this problem. (I might have to
rephrase if the U/. mechanism is too slow on large data sets.)
tableA=:".;._2(0 :0)-.'-'
2020-05-04,119.39,121.97
2020-05-05,122.47,124.31
2020-05-06,122.41,124.05
2020-05-07,121.06,123.26
2020-05-08,121.06,123.23
2020-05-11,120.67,123.45
2020-05-12,120.26,124.31
2020-05-13,114.84,119.98
2020-05-14,111.81,117.09
2020-05-15,115.25,117.39
2020-05-18,119.75,122.36
2020-05-19,120.14,122.17
2020-05-20,120.59,122.47
2020-05-21,118.97,121.72
2020-05-22,117.59,119.47
2020-05-26,121.01,122.62
2020-05-27,123.47,125.83
)
B=:1
T=:_1
tableB=:".;._2(0 :0)-.'-'
2020-05-04,B
2020-05-05,T
2020-05-14,B
2020-05-20,T
2020-05-22,B
)
tableC=:".;._2 rplc&(',,';',0,')0 :0-.'-'
2020-05-04,B,119.39,121.97
2020-05-05,T,122.47,124.31
2020-05-06,,122.41,124.05
2020-05-07,,121.06,123.26
2020-05-08,,121.06,123.23
2020-05-11,,120.67,123.45
2020-05-12,,120.26,124.31
2020-05-13,,114.84,119.98
2020-05-14,B,111.81,117.09
2020-05-15,,115.25,117.39
2020-05-18,,119.75,122.36
2020-05-19,,120.14,122.17
2020-05-20,T,120.59,122.47
2020-05-21,,118.97,121.72
2020-05-22,B,117.59,119.47
2020-05-26,,121.01,122.62
2020-05-27,,123.47,125.83
)
result=: ({."1 (~.@[,.+//.) }."1)(1j1 1 1#"1 tableA),1 1j2#"1 tableB
assert result-:tableC
Note also that I have not addressed formatting the result using the ,,
,B, ,T, notation. (Because I do not know how important that is.)
I hope this helps,
--
Raul
On Sat, Jun 6, 2020 at 12:45 AM HH PackRat <[email protected]> wrote:
>
> Another project, another question....
>
> Below are two tables. Table A is daily stock data showing date, daily
> low, and daily high (columns 0, 1, 2). Table B is stock pivots only
> (based on a particular percentage between them), showing date and
> whether the pivot was a top (T) or a bottom (B) (columns 0, 1). Table
> C is the output I want, showing date, pivot, low, and high (columns 0,
> 1, 2, 3).
>
> Table A:
>
> 2020-05-04,119.39,121.97
> 2020-05-05,122.47,124.31
> 2020-05-06,122.41,124.05
> 2020-05-07,121.06,123.26
> 2020-05-08,121.06,123.23
> 2020-05-11,120.67,123.45
> 2020-05-12,120.26,124.31
> 2020-05-13,114.84,119.98
> 2020-05-14,111.81,117.09
> 2020-05-15,115.25,117.39
> 2020-05-18,119.75,122.36
> 2020-05-19,120.14,122.17
> 2020-05-20,120.59,122.47
> 2020-05-21,118.97,121.72
> 2020-05-22,117.59,119.47
> 2020-05-26,121.01,122.62
> 2020-05-27,123.47,125.83
>
> Table B:
>
> 2020-05-04,B
> 2020-05-05,T
> 2020-05-14,B
> 2020-05-20,T
> 2020-05-22,B
>
>
> Table C:
>
> 2020-05-04,B,119.39,121.97
> 2020-05-05,T,122.47,124.31
> 2020-05-06,,122.41,124.05
> 2020-05-07,,121.06,123.26
> 2020-05-08,,121.06,123.23
> 2020-05-11,,120.67,123.45
> 2020-05-12,,120.26,124.31
> 2020-05-13,,114.84,119.98
> 2020-05-14,B,111.81,117.09
> 2020-05-15,,115.25,117.39
> 2020-05-18,,119.75,122.36
> 2020-05-19,,120.14,122.17
> 2020-05-20,T,120.59,122.47
> 2020-05-21,,118.97,121.72
> 2020-05-22,B,117.59,119.47
> 2020-05-26,,121.01,122.62
> 2020-05-27,,123.47,125.83
>
> (1) Am I correct to first create another column in Table A between
> columns 0 and 1?
>
> (2) In J, is it preferable for that inserted column to be empty or to
> contain spaces for this particular purpose? Table C will be imported
> into Excel, and I don't think Excel cares one way or the other. I
> don't know if it will be a J problem amending (or whatever verb is
> best) if a literal character is attempted to be inserted into an empty
> box. (I've never had to do that before. I also know there's a first
> time for everything.)
>
> (3) The main idea is to match dates in Table B with dates in Table A,
> and, if there is a match, to insert the pivot value into column 1
> (empty or spaces) in Table C. Is there a verb (or combination of
> verbs) that will accomplish that at once, or is this too complicated
> and it would be a better approach to use a "for" loop?
>
> (4) Eventually, specialized daily astronomical data will need to be
> inserted in another column, so I presume the adding technique for
> dates that the markets are closed that was shared with me a couple of
> weeks ago would be the approach to use here, too. Correct?
>
> Just FYI, after the full Table C is complete, it will be imported into
> Excel, where formulas will color each top pivot cell green and each
> bottom pivot cell red (in their respective columns). This will make
> analysis much easier than doing everything manually, as now.
>
> Many thanks in advance for help with this project!
>
> 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
