Note: If I did have to support the textual representation, I would
change my B and T to be values which could never appear otherwise.
For example:
B=:0.12345
T=:9.78654
Then I could use:
require'csv'
rplc&(',0.12345,';',B,';',9.78654,';',T,';',0.00000,';',,') makecsv result
to format the result.. (or something faster, if makecsv is too slow).
FYI,
--
Raul
On Sat, Jun 6, 2020 at 2:25 AM Raul Miller <[email protected]> wrote:
>
> 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
