comp.lang.ada
 help / color / mirror / Atom feed
* Some advice required [OT]
@ 2021-12-27  9:21 Laurent
  2021-12-27 11:16 ` Niklas Holsti
  2021-12-27 17:18 ` Simon Wright
  0 siblings, 2 replies; 25+ messages in thread
From: Laurent @ 2021-12-27  9:21 UTC (permalink / raw)


Hi all

My problem is not directly related to Ada but on how to solve it in general.

Also writing via the web interface of google groups :(

I have to do statistics on the results of antimicrobial susceptibility testings.
I have to keep only one strain/patient and the most resistant one.
Until now I have been doing it manually by staring for hours at Excel sheets.
I am trying to get it automated but I don't know how to solve my problem.

I have tried calculating a checksum from the results but I have cases
which are unclear/collide.

The result for a strain is one row, the results are in columns.

I treat the results in blocks of 3.
S has a value of 1, I =2 and R=3, empty cells = 0

without weight SRS (1+3+1) and RSS (3+1+1) or SSR (1+1+3) give both 5
I would have to keep the 3 because they are different.

I thought that weighting the position would solve the collisions
but nope.
The first cell has a value of 1, 2nd of 2 and 3rd of 3.
S has a value of 1, I =2 and R=3, empty cells = 0
with weight RRS (1*3+2*3+3*1) and SSR (1*1 + 2*1+3*3) give both 12

Is there a better way doing this?

What I have so far as VBA code:

Public Function Test_Checksum(rng_Range As Range) As String

    Dim rng_Cell As Range
    Dim int_Counter As Integer
    Dim str_Result As String
    Dim i As Long
        
        int_Counter = 1
        
        For Each rng_Cell In rng_Range
            
            If rng_Cell.Value = "S" Then
                i = i + 1 * int_Counter
            ElseIf rng_Cell.Value = "I" Then
                i = i + 2 * int_Counter
            ElseIf rng_Cell.Value = "R" Then
                i = i + 3 * int_Counter
            Else
                --empty cell
                i = i + 0
            End If
                        
            If int_Counter = 3 Then
                int_Counter = 0
                
                If i < 9 Then
                    str_Result = str_Result & "0" & CStr(i)
                Else
                    str_Result = str_Result & CStr(i)
                End If
                i = 0
            End If

            int_Counter = int_Counter + 1
            
        Next rng_Cell
        
        --if the loop terminates but i <> 3
        If i < 9 Then
            str_Result = str_Result & "0" & CStr(i)
        Else
            str_Result = str_Result & CStr(i)
        End If
        
        Test_Checksum = str_Result
End Function  

Thank you very much

Kind regards

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27  9:21 Some advice required [OT] Laurent
@ 2021-12-27 11:16 ` Niklas Holsti
  2021-12-27 12:29   ` Laurent
  2021-12-27 17:18 ` Simon Wright
  1 sibling, 1 reply; 25+ messages in thread
From: Niklas Holsti @ 2021-12-27 11:16 UTC (permalink / raw)


On 2021-12-27 11:21, Laurent wrote:
> Hi all
> 
> My problem is not directly related to Ada but on how to solve it in general.
> 
> Also writing via the web interface of google groups :(
> 
> I have to do statistics on the results of antimicrobial susceptibility testings.
> I have to keep only one strain/patient and the most resistant one.
> Until now I have been doing it manually by staring for hours at Excel sheets.
> I am trying to get it automated but I don't know how to solve my problem.


    [ problem description snipped ]


Sorry, but I found your problem description impossible to understand. 
Try to describe more clearly the experiment that is done, the structure 
of the data the experiment provides (the meaning of the Excel rows and 
columns), and the statistic you want to compute.

Also, if you do not intend to implement the solution in Ada, this is not 
the right group to discuss it.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 11:16 ` Niklas Holsti
@ 2021-12-27 12:29   ` Laurent
  2021-12-27 13:14     ` Ben Bacarisse
                       ` (2 more replies)
  0 siblings, 3 replies; 25+ messages in thread
From: Laurent @ 2021-12-27 12:29 UTC (permalink / raw)


On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote:

> Sorry, but I found your problem description impossible to understand. 
> Try to describe more clearly the experiment that is done, the structure 
> of the data the experiment provides (the meaning of the Excel rows and 
> columns), and the statistic you want to compute. 

Sorry tried to keep it short, was too short.

Columns are the antimicrobial drugs
Rows are the microorganism.

So every cell contains a result of S, I, R or simply an empty cell

S = Sensible
I = Intermediate
R = Resistant

empty cell <S<I<R

If a patient has 3 strains of the same microorganism but with different resistance profiles
I have to find the most resistant one. Or if they are different I keep them all.

I have no idea how to explain what I am doing to the compiler.
Why I would choose result from strain B over the result from strain A.

strain A: SSSRSS
strain B: SSRRRS

Simply counting the number of S, I and R doesn't work. ​Checksum with/without weight for the column number doesn't
work either.

Even if I get a correct result I have still the same problem as before why result B over result A.

Thought about building a tree for every family of drugs but the problem will again be the same.
How to decide which result to choose. 

Would be easier to attach the Excel file directly. 

> Also, if you do not intend to implement the solution in Ada, this is not 
> the right group to discuss it.

I would very much prefer to solve it in Ada but at work I am stuck with Excel and VBA which is better than
doing it manually. After a few hours starring at a screen with thousand of rows of results...
If I get an Ada solution I can adapt it. Just limited to no access/pointers in VBA which shouldn't be required?

I know this is the wrong group to discuss, unfortunately I don't know of any place where I would get usable advice.
Here at least I know that there are no trolls or whatever. Perhaps some miscommunication because I thought my
explanation was clear but it wasn't because of missing context. 

I am lurking in this group for some time. Just gave up on using Ada

Thanks

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 12:29   ` Laurent
@ 2021-12-27 13:14     ` Ben Bacarisse
  2021-12-27 18:24       ` Laurent
  2021-12-27 17:41     ` Dennis Lee Bieber
  2021-12-28  2:10     ` Randy Brukardt
  2 siblings, 1 reply; 25+ messages in thread
From: Ben Bacarisse @ 2021-12-27 13:14 UTC (permalink / raw)


Laurent <lutgenl@icloud.com> writes:

> On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote:
>
>> Sorry, but I found your problem description impossible to understand. 
>> Try to describe more clearly the experiment that is done, the structure 
>> of the data the experiment provides (the meaning of the Excel rows and 
>> columns), and the statistic you want to compute. 
>
> Sorry tried to keep it short, was too short.
>
> Columns are the antimicrobial drugs
> Rows are the microorganism.
>
> So every cell contains a result of S, I, R or simply an empty cell
>
> S = Sensible
> I = Intermediate
> R = Resistant
>
> empty cell <S<I<R
>
> If a patient has 3 strains of the same microorganism but with
> different resistance profiles I have to find the most resistant
> one. Or if they are different I keep them all.
>
> I have no idea how to explain what I am doing to the compiler.

I think when you can explain it to people, you'll be able to code it.  I
am still struggling to understand what you need.

> Why I would choose result from strain B over the result from strain A.
>
> strain A: SSSRSS
> strain B: SSRRRS

Let's space it out

          drug 1  drug 2  drug 3  drug 4  drug 5  drug 6
strain A    S       S       S       R       S       S
strain B    S       S       R       R       R       S

You want to choose B because it has is resistant to more drugs, yes?

I think, from the ordering you give, you need a measure that treats an R
as "more important" that any "I" which is "more important" than an "S".
(We will come to empty cells later.)

I think you need to treat the number of Rs, Is and Ss like digits in a
number.  In base 10, the strains score

           R  S  I
strain A   1  5  0  = 150
strain B   3  3  0  = 330

Now, in fact, you don't need to use base 10.  The smallest base you can
use is one more than the maximum number of test results.  If there can
be up to 16 tests (say) the score is

 n(R)*17*17 + n(S)*17 + n(I).

If this suits your needs, we can consider empty cells later on.  It's
not at all clear to me how to compare

strain C  R____
strain D  RRSSSS

Strain C is "less resistant" but only because there is not enough
information.  In fact it seems more serious as it is resistant to all
tested drugs.

And then what about

strain D  SR
strain E  RS

Do you need to weight the drugs to break ties?  I.e. is drug x more
important than drug y if x < y?

-- 
Ben.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27  9:21 Some advice required [OT] Laurent
  2021-12-27 11:16 ` Niklas Holsti
@ 2021-12-27 17:18 ` Simon Wright
  2021-12-27 18:30   ` Laurent
  1 sibling, 1 reply; 25+ messages in thread
From: Simon Wright @ 2021-12-27 17:18 UTC (permalink / raw)


Laurent <lutgenl@icloud.com> writes:

> Until now I have been doing it manually by staring for hours at Excel
> sheets.  I am trying to get it automated but I don't know how to solve
> my problem.

You must go through some mental process while staring at the
spreadsheets; what's that process? It can't involve checksums!

In a post below, you said you had to choose the most resistant, or if
different all of them, which doesn't make sense. Are you perhaps
thinking of ties? in which case you must have some notion of scoring
profiles so you can determine which profiles come equal-first.

Does RSSSSS score higher or lower than SIIIII?

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 12:29   ` Laurent
  2021-12-27 13:14     ` Ben Bacarisse
@ 2021-12-27 17:41     ` Dennis Lee Bieber
  2021-12-27 18:56       ` Niklas Holsti
  2021-12-28  2:10     ` Randy Brukardt
  2 siblings, 1 reply; 25+ messages in thread
From: Dennis Lee Bieber @ 2021-12-27 17:41 UTC (permalink / raw)


On Mon, 27 Dec 2021 04:29:06 -0800 (PST), Laurent <lutgenl@icloud.com>
declaimed the following:

>Why I would choose result from strain B over the result from strain A.
>
>strain A: SSSRSS
>strain B: SSRRRS
>
>Simply counting the number of S, I and R doesn't work. ?Checksum with/without weight for the column number doesn't
>work either.

	I wouldn't expect a checksum to be of any use, since the idea of most
checksums (and CRCs) is to be able to verify that a data sequence has not
been corrupted. Checksums don't "rank" data.

>
>Even if I get a correct result I have still the same problem as before why result B over result A.
>

	Unfortunately, until you CAN describe why one result is preferred over
another, no one will be able to suggest algorithm(s) that may work (of
course, once you can explain it, you may not need assistance translating it
to code). For all we know, the cost of the various compounds might be a
factor affecting which of two similar result rows might be desired.

	Actually, I'm still perplexed at the idea that the solution is picking
microbe strains that are most resistant to drugs -- unless one is trying to
reduce test candidates for yet undeveloped drugs ("if our new concoction
kills this strain, /then/ we will test it against the rest of the
strains").

	I'm tempted to suggest R (or other statistical software) and
experimenting with various presentation/partitioning operations to see if
something reasonable pops out. Your data is NOT numerical (so don't bother
assigning numbers to your <null>SIR -- after all, you could just as easily
assign the ordinal position in the ASCII alphabet to them), so statistical
operations that work on non-numeric "factors" makes as much, if not more,
sense. (I've only toyed with R, so I don't know if it has partitioning
ability for three factors -- be a bit tedious to have to specify, say,

	compound-X factor = R
(true)					(false)
						compound-X factor = S
						(true)				(false)
)


-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed@ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 13:14     ` Ben Bacarisse
@ 2021-12-27 18:24       ` Laurent
  2021-12-27 19:51         ` Dennis Lee Bieber
  2021-12-27 20:49         ` Ben Bacarisse
  0 siblings, 2 replies; 25+ messages in thread
From: Laurent @ 2021-12-27 18:24 UTC (permalink / raw)


On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote:
> Laurent <lut...@icloud.com> writes: 
> 
> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
> > 
> >> Sorry, but I found your problem description impossible to understand. 
> >> Try to describe more clearly the experiment that is done, the structure 
> >> of the data the experiment provides (the meaning of the Excel rows and 
> >> columns), and the statistic you want to compute. 
> > 
> > Sorry tried to keep it short, was too short. 
> > 
> > Columns are the antimicrobial drugs 
> > Rows are the microorganism. 
> > 
> > So every cell contains a result of S, I, R or simply an empty cell 
> > 
> > S = Sensible 
> > I = Intermediate 
> > R = Resistant 
> > 
> > empty cell <S<I<R 
> > 
> > If a patient has 3 strains of the same microorganism but with 
> > different resistance profiles I have to find the most resistant 
> > one. Or if they are different I keep them all. 
> > 
> > I have no idea how to explain what I am doing to the compiler.
> I think when you can explain it to people, you'll be able to code it. I 
> am still struggling to understand what you need.
> > Why I would choose result from strain B over the result from strain A. 
> > 
> > strain A: SSSRSS 
> > strain B: SSRRRS
> Let's space it out 
> 
> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
> strain A S S S R S S 
> strain B S S R R R S 
> 
> You want to choose B because it has is resistant to more drugs, yes? 
> 

Yes indeed

> I think, from the ordering you give, you need a measure that treats an R 
> as "more important" that any "I" which is "more important" than an "S". 
> (We will come to empty cells later.) 
> 
> I think you need to treat the number of Rs, Is and Ss like digits in a 
> number. In base 10, the strains score 
> 
> R S I 
> strain A 1 5 0 = 150 
> strain B 3 3 0 = 330 
> 
> Now, in fact, you don't need to use base 10. The smallest base you can 
> use is one more than the maximum number of test results. If there can 
> be up to 16 tests (say) the score is 
> 
> n(R)*17*17 + n(S)*17 + n(I). 
> 
> If this suits your needs, we can consider empty cells later on. It's 
> not at all clear to me how to compare 
> 
> strain C R____ 
> strain D RRSSSS 
> 
> Strain C is "less resistant" but only because there is not enough 
> information. In fact it seems more serious as it is resistant to all 
> tested drugs. 
> 

Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete.

> And then what about 
> 
> strain D SR 
> strain E RS 
> 

Yes those are the cases which are annoying me.

That's why I came up withe idea of multiplying the value of the result (S=1, I=2 and R=3) with the position of the value. 
Tried it with triplets but there will still be cases where different results will give the same numeric value.
Ignoring empty cells for the moment.

Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 will be the same numerical value but they are different resistance profiles
I would in this case keep both.

How to prevent that from happening.

The results are way longer than only 3 values so the possibilities for collisions are higher.

R	R	R	R	R	S	R	R	R	S	S	S	R	S	S  => numeric:1812180608
R	R	R	R	R	S	R	R	R	R	S	S	S	S	S  => numeric:1812180806

I have to keep both and that was an easy one. Only 2 to compare not 5.

A lot of R in common, only 2 differences.
Ok now I have the same result in an other representation. 
Still no idea how to explain to the compiler why they are different.

> Do you need to weight the drugs to break ties? I.e. is drug x more 
> important than drug y if x < y? 
> 
> -- 
> Ben.

Yes there is a hierarchy in the drugs but that information is not available in the exported results I work with.
I know that because it is part of my formation as medical technical assistant in a lab.
I was hoping to not have to recreate some form of expert system.

Thanks

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 17:18 ` Simon Wright
@ 2021-12-27 18:30   ` Laurent
  0 siblings, 0 replies; 25+ messages in thread
From: Laurent @ 2021-12-27 18:30 UTC (permalink / raw)


On Monday, 27 December 2021 at 18:18:54 UTC+1, Simon Wright wrote:
> Laurent <lut...@icloud.com> writes: 
> 
> > Until now I have been doing it manually by staring for hours at Excel 
> > sheets. I am trying to get it automated but I don't know how to solve 
> > my problem.
> You must go through some mental process while staring at the 
> spreadsheets; what's that process? It can't involve checksums! 
> 

Perhaps not calculating but somehow estimating which has the most R's and considering
the positions they are at. Considering the family of drug it belongs to.

With certain microorganisms it works quite good because they are not very variable.
Others are a real pain. Mostly finding the least resistant with the most in common of
the more resistant and then deleting one after another. Then comparing the leftover ones.

Takes time, high risk of messing up things because I begin to see things which are not there...

> In a post below, you said you had to choose the most resistant, or if 
> different all of them, which doesn't make sense. Are you perhaps 
> thinking of ties? in which case you must have some notion of scoring 
> profiles so you can determine which profiles come equal-first. 
> 
> Does RSSSSS score higher or lower than SIIIII?

That would be 2 different strains which I would keep both.

Thanks

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 17:41     ` Dennis Lee Bieber
@ 2021-12-27 18:56       ` Niklas Holsti
  2021-12-27 19:44         ` Laurent
  0 siblings, 1 reply; 25+ messages in thread
From: Niklas Holsti @ 2021-12-27 18:56 UTC (permalink / raw)


On 2021-12-27 19:41, Dennis Lee Bieber wrote:
> On Mon, 27 Dec 2021 04:29:06 -0800 (PST), Laurent
> <lutgenl@icloud.com> declaimed the following:
> 
>> Why I would choose result from strain B over the result from strain
>> A.
>> 
>> strain A: SSSRSS strain B: SSRRRS
>> 
>> Simply counting the number of S, I and R doesn't work. ?Checksum
>> with/without weight for the column number doesn't work either.
> 
> I wouldn't expect a checksum to be of any use, since the idea of
> most checksums (and CRCs) is to be able to verify that a data
> sequence has not been corrupted. Checksums don't "rank" data.


I believe that Laurent does not mean "checksum" in its usual meaning, 
but a numerical "score" computed as a sum of terms multiplied by 
weights. Whether such a score can solve Laurent's problem is not clear.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 18:56       ` Niklas Holsti
@ 2021-12-27 19:44         ` Laurent
  0 siblings, 0 replies; 25+ messages in thread
From: Laurent @ 2021-12-27 19:44 UTC (permalink / raw)


On Monday, 27 December 2021 at 19:56:10 UTC+1, Niklas Holsti wrote:
> On 2021-12-27 19:41, Dennis Lee Bieber wrote: 
> > On Mon, 27 Dec 2021 04:29:06 -0800 (PST), Laurent 
> > <lut...@icloud.com> declaimed the following:
> > 
> >> Why I would choose result from strain B over the result from strain 
> >> A. 
> >> 
> >> strain A: SSSRSS strain B: SSRRRS 
> >>
> >> Simply counting the number of S, I and R doesn't work. ?Checksum
> >> with/without weight for the column number doesn't work either. 
> >
> > I wouldn't expect a checksum to be of any use, since the idea of 
> > most checksums (and CRCs) is to be able to verify that a data 
> > sequence has not been corrupted. Checksums don't "rank" data. 
> 
> 
> I believe that Laurent does not mean "checksum" in its usual meaning, 
> but a numerical "score" computed as a sum of terms multiplied by 
> weights. Whether such a score can solve Laurent's problem is not clear.

I used the algorithm for calculating the checksum for the GTIN in barcodes as
starting point so I got stuck on the term checksum.

Half of the problem is finding the correct word with which to feed google to find an answer.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 18:24       ` Laurent
@ 2021-12-27 19:51         ` Dennis Lee Bieber
  2021-12-27 20:49         ` Ben Bacarisse
  1 sibling, 0 replies; 25+ messages in thread
From: Dennis Lee Bieber @ 2021-12-27 19:51 UTC (permalink / raw)


On Mon, 27 Dec 2021 10:24:21 -0800 (PST), Laurent <lutgenl@icloud.com>
declaimed the following:

>
>Yes those are the cases which are annoying me.
>
>That's why I came up withe idea of multiplying the value of the result (S=1, I=2 and R=3) with the position of the value. 
>Tried it with triplets but there will still be cases where different results will give the same numeric value.
>Ignoring empty cells for the moment.
>
	Multiplying by column position inherently gives priority to the column
with the highest position. If the columns are, of themselves, not
significant, your algorithm needs to ignore column (reordering the columns
should not make a change in final selection). You could just about as
easily do a multi-column sort giving priority to the most significant
column.

>Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 will be the same numerical value but they are different resistance profiles
>I would in this case keep both.

	So far as I can make out -- ANY collision qualifies as "different
resistance profiles".  In that example, the count of Ss vs the count of Rs
differ, but...

>The results are way longer than only 3 values so the possibilities for collisions are higher.
>
>R	R	R	R	R	S	R	R	R	S	S	S	R	S	S  => numeric:1812180608
>R	R	R	R	R	S	R	R	R	R	S	S	S	S	S  => numeric:1812180806
>
>I have to keep both and that was an easy one. Only 2 to compare not 5.
>

	In this example the count of Ss and count of Rs is the same between the
two. And, again, you've applied an arbitrary ranking of the columns
(changing the order of the columns will tend to produce wildly different
sums).

>Yes there is a hierarchy in the drugs but that information is not available in the exported results I work with.

	In that situation I would complain to the provider that the exported
data is incompletely defined. At the very least, the columns should be in
ascending (or descending) order of significance, justifying use of column
position as a weight (even better would be to have a row of the data
containing the weight to be used for a given column, which makes the column
order irrelevant).


-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed@ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 18:24       ` Laurent
  2021-12-27 19:51         ` Dennis Lee Bieber
@ 2021-12-27 20:49         ` Ben Bacarisse
  2021-12-27 22:09           ` Laurent
  1 sibling, 1 reply; 25+ messages in thread
From: Ben Bacarisse @ 2021-12-27 20:49 UTC (permalink / raw)


Laurent <lutgenl@icloud.com> writes:

> On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote:
>> Laurent <lut...@icloud.com> writes: 
>> 
>> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
>> > 
>> >> Sorry, but I found your problem description impossible to understand. 
>> >> Try to describe more clearly the experiment that is done, the structure 
>> >> of the data the experiment provides (the meaning of the Excel rows and 
>> >> columns), and the statistic you want to compute. 
>> > 
>> > Sorry tried to keep it short, was too short. 
>> > 
>> > Columns are the antimicrobial drugs 
>> > Rows are the microorganism. 
>> > 
>> > So every cell contains a result of S, I, R or simply an empty cell 
>> > 
>> > S = Sensible 
>> > I = Intermediate 
>> > R = Resistant 
>> > 
>> > empty cell <S<I<R 
>> > 
>> > If a patient has 3 strains of the same microorganism but with 
>> > different resistance profiles I have to find the most resistant 
>> > one. Or if they are different I keep them all. 
>> > 
>> > I have no idea how to explain what I am doing to the compiler.
>> I think when you can explain it to people, you'll be able to code it. I 
>> am still struggling to understand what you need.
>> > Why I would choose result from strain B over the result from strain A. 
>> > 
>> > strain A: SSSRSS 
>> > strain B: SSRRRS
>> Let's space it out 
>> 
>> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
>> strain A S S S R S S 
>> strain B S S R R R S 
>> 
>> You want to choose B because it has is resistant to more drugs, yes? 
>> 
>
> Yes indeed
>
>> I think, from the ordering you give, you need a measure that treats an R 
>> as "more important" that any "I" which is "more important" than an "S". 
>> (We will come to empty cells later.) 
>> 
>> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> number. In base 10, the strains score 
>> 
>> R S I 
>> strain A 1 5 0 = 150 
>> strain B 3 3 0 = 330 
>> 
>> Now, in fact, you don't need to use base 10. The smallest base you can 
>> use is one more than the maximum number of test results. If there can 
>> be up to 16 tests (say) the score is 
>> 
>> n(R)*17*17 + n(S)*17 + n(I). 
>> 
>> If this suits your needs, we can consider empty cells later on. It's 
>> not at all clear to me how to compare 
>> 
>> strain C R____ 
>> strain D RRSSSS 
>> 
>> Strain C is "less resistant" but only because there is not enough 
>> information. In fact it seems more serious as it is resistant to all 
>> tested drugs. 
>> 
>
> Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete.
>
>> And then what about 
>> 
>> strain D SR 
>> strain E RS 
>> 
>
> Yes those are the cases which are annoying me.
>
> That's why I came up withe idea of multiplying the value of the result
> (S=1, I=2 and R=3) with the position of the value.  Tried it with
> triplets but there will still be cases where different results will
> give the same numeric value.  Ignoring empty cell able tps for the moment.
>
> Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12
> will be the same numerical value but they are different resistance
> profiles I would in this case keep both.
>
> How to prevent that from happening.

Can you first say why the suggestion I made is not helpful?

-- 
Ben.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 20:49         ` Ben Bacarisse
@ 2021-12-27 22:09           ` Laurent
  2021-12-28  0:29             ` Ben Bacarisse
  0 siblings, 1 reply; 25+ messages in thread
From: Laurent @ 2021-12-27 22:09 UTC (permalink / raw)


On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote:
> Laurent <lut...@icloud.com> writes: 
> 
> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
> >> Laurent <lut...@icloud.com> writes: 
> >> 
> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
> >> > 
> >> >> Sorry, but I found your problem description impossible to understand. 
> >> >> Try to describe more clearly the experiment that is done, the structure 
> >> >> of the data the experiment provides (the meaning of the Excel rows and 
> >> >> columns), and the statistic you want to compute. 
> >> > 
> >> > Sorry tried to keep it short, was too short. 
> >> > 
> >> > Columns are the antimicrobial drugs 
> >> > Rows are the microorganism. 
> >> > 
> >> > So every cell contains a result of S, I, R or simply an empty cell 
> >> > 
> >> > S = Sensible 
> >> > I = Intermediate 
> >> > R = Resistant 
> >> > 
> >> > empty cell <S<I<R 
> >> > 
> >> > If a patient has 3 strains of the same microorganism but with 
> >> > different resistance profiles I have to find the most resistant 
> >> > one. Or if they are different I keep them all. 
> >> > 
> >> > I have no idea how to explain what I am doing to the compiler. 
> >> I think when you can explain it to people, you'll be able to code it. I 
> >> am still struggling to understand what you need. 
> >> > Why I would choose result from strain B over the result from strain A. 
> >> > 
> >> > strain A: SSSRSS 
> >> > strain B: SSRRRS 
> >> Let's space it out 
> >> 
> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
> >> strain A S S S R S S 
> >> strain B S S R R R S 
> >> 
> >> You want to choose B because it has is resistant to more drugs, yes? 
> >> 
> > 
> > Yes indeed 
> > 
> >> I think, from the ordering you give, you need a measure that treats an R 
> >> as "more important" that any "I" which is "more important" than an "S". 
> >> (We will come to empty cells later.) 
> >> 
> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> >> number. In base 10, the strains score 
> >> 
> >> R S I 
> >> strain A 1 5 0 = 150 
> >> strain B 3 3 0 = 330 
> >> 
> >> Now, in fact, you don't need to use base 10. The smallest base you can 
> >> use is one more than the maximum number of test results. If there can 
> >> be up to 16 tests (say) the score is 
> >> 
> >> n(R)*17*17 + n(S)*17 + n(I). 
> >> 
> >> If this suits your needs, we can consider empty cells later on. It's 
> >> not at all clear to me how to compare 
> >> 
> >> strain C R____ 
> >> strain D RRSSSS 
> >> 
> >> Strain C is "less resistant" but only because there is not enough 
> >> information. In fact it seems more serious as it is resistant to all 
> >> tested drugs. 
> >> 
> > 
> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
> > 
> >> And then what about 
> >> 
> >> strain D SR 
> >> strain E RS 
> >> 
> > 
> > Yes those are the cases which are annoying me. 
> > 
> > That's why I came up withe idea of multiplying the value of the result 
> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
> > triplets but there will still be cases where different results will
> > give the same numeric value. Ignoring empty cell able tps for the moment.
> > 
> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
> > will be the same numerical value but they are different resistance 
> > profiles I would in this case keep both. 
> > 
> > How to prevent that from happening.
> Can you first say why the suggestion I made is not helpful? 
> 
> -- 
> Ben.

You mean that one:

> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> >> number. In base 10, the strains score 
> >> 
> >> R S I 
> >> strain A 1 5 0 = 150 
> >> strain B 3 3 0 = 330 
> >> 

Different resistance profiles same result:

S	S	S	S	S	S	R	S	S	S	S	S	S	S	S 
score=1	14 0

S	S	S	S	S	S	S	S	S	S	S	S	R	S	S
score=1	14 0

R	R	R	R	R	S	R	R	R	S	S	S	S	S	S
score = 8 7 0

R	R	R	R	S	S	R	R	R	S	S	S	R	S	S
score = 8 7 0

I found 6 of those cases in 69 possible duplicates.

> >> Now, in fact, you don't need to use base 10. The smallest base you can 
> >> use is one more than the maximum number of test results. If there can 
> >> be up to 16 tests (say) the score is 
> >> 
> >> n(R)*17*17 + n(S)*17 + n(I). 
> >> 

The maximum is probably 20. More drugs don't fit onto one antimicrobial susceptibility test (AST) card.

Just scoring the numbers together doesn't work always because of those cases as you said yourself: 
> >> And then what about 
> >> strain D SR 
> >> strain E RS 

So I jumped to the conclusion that I need to add a weight for the position.

That's the solution I have figured out myself so far. But it suffers from the same problem perhaps
less often.

In the data I am testing I have 264 rows with results but only 69 are possible duplicates.
None of those produced a collision. So I have no idea how common that problem actually is.

Have to check that when I am back at work tomorrow.

Thanks

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 22:09           ` Laurent
@ 2021-12-28  0:29             ` Ben Bacarisse
  2021-12-28  7:48               ` Laurent
  0 siblings, 1 reply; 25+ messages in thread
From: Ben Bacarisse @ 2021-12-28  0:29 UTC (permalink / raw)


Laurent <lutgenl@icloud.com> writes:

> On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote:
>> Laurent <lut...@icloud.com> writes: 
>> 
>> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
>> >> Laurent <lut...@icloud.com> writes: 
>> >> 
>> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
>> >> > 
>> >> >> Sorry, but I found your problem description impossible to understand. 
>> >> >> Try to describe more clearly the experiment that is done, the structure 
>> >> >> of the data the experiment provides (the meaning of the Excel rows and 
>> >> >> columns), and the statistic you want to compute. 
>> >> > 
>> >> > Sorry tried to keep it short, was too short. 
>> >> > 
>> >> > Columns are the antimicrobial drugs 
>> >> > Rows are the microorganism. 
>> >> > 
>> >> > So every cell contains a result of S, I, R or simply an empty cell 
>> >> > 
>> >> > S = Sensible 
>> >> > I = Intermediate 
>> >> > R = Resistant 
>> >> > 
>> >> > empty cell <S<I<R 
>> >> > 
>> >> > If a patient has 3 strains of the same microorganism but with 
>> >> > different resistance profiles I have to find the most resistant 
>> >> > one. Or if they are different I keep them all. 
>> >> > 
>> >> > I have no idea how to explain what I am doing to the compiler. 
>> >> I think when you can explain it to people, you'll be able to code it. I 
>> >> am still struggling to understand what you need. 
>> >> > Why I would choose result from strain B over the result from strain A. 
>> >> > 
>> >> > strain A: SSSRSS 
>> >> > strain B: SSRRRS 
>> >> Let's space it out 
>> >> 
>> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
>> >> strain A S S S R S S 
>> >> strain B S S R R R S 
>> >> 
>> >> You want to choose B because it has is resistant to more drugs, yes? 
>> >> 
>> > 
>> > Yes indeed 
>> > 
>> >> I think, from the ordering you give, you need a measure that treats an R 
>> >> as "more important" that any "I" which is "more important" than an "S". 
>> >> (We will come to empty cells later.) 
>> >> 
>> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> >> number. In base 10, the strains score 
>> >> 
>> >> R S I 
>> >> strain A 1 5 0 = 150 
>> >> strain B 3 3 0 = 330 
>> >> 
>> >> Now, in fact, you don't need to use base 10. The smallest base you can 
>> >> use is one more than the maximum number of test results. If there can 
>> >> be up to 16 tests (say) the score is 
>> >> 
>> >> n(R)*17*17 + n(S)*17 + n(I). 
>> >> 
>> >> If this suits your needs, we can consider empty cells later on. It's 
>> >> not at all clear to me how to compare 
>> >> 
>> >> strain C R____ 
>> >> strain D RRSSSS 
>> >> 
>> >> Strain C is "less resistant" but only because there is not enough 
>> >> information. In fact it seems more serious as it is resistant to all 
>> >> tested drugs. 
>> >> 
>> > 
>> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
>> > 
>> >> And then what about 
>> >> 
>> >> strain D SR 
>> >> strain E RS 
>> >> 
>> > 
>> > Yes those are the cases which are annoying me. 
>> > 
>> > That's why I came up withe idea of multiplying the value of the result 
>> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
>> > triplets but there will still be cases where different results will
>> > give the same numeric value. Ignoring empty cell able tps for the moment.
>> > 
>> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
>> > will be the same numerical value but they are different resistance 
>> > profiles I would in this case keep both. 
>> > 
>> > How to prevent that from happening.
>> Can you first say why the suggestion I made is not helpful? 
>> 
>> -- 
>> Ben.
>
> You mean that one:
>
>> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> >> number. In base 10, the strains score 
>> >> 
>> >> R S I 
>> >> strain A 1 5 0 = 150 
>> >> strain B 3 3 0 = 330 
>> >> 
>
> Different resistance profiles same result:

I don't yet understand the requirements so I am taking it in stages.
The first requirement seemed to be "more or less resistant".  To do that
you can use digits in a large enough base but this will make the number
of Rs, Ss and Is paramount.  Is that acceptable as a first step?

In order to help people to be able to make further suggestions, maybe
you could give the relative ordering you would like to see between the
following sets of profiles.  For example, between SSR, SRS and RSS, I
think the order you want is RSS > SRS > SSR.

1: SSR, SRS, RSS

2: RSI, RIS, SRI, SIR, IRS, ISR

3: SSSR, SSRS, SRSS, RSSS

4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR

It's possible you could make do with an extra field (or digits) that
gives some measure of the relative ordering between otherwise similar
sequences.  For example, using base 10 (for convenience of arithmetic)
both RRSSI and RSRSI would score 212xx but the last xx would reflect the
positioning of the results in the sequence.  There are lots of way to do
this.  One way would be use, as you were thinking, some sort of weighted
count.  Using S=0, I=1 and R=2 with weights

54321
RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219
RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217

If you absolutely must never get duplicate numbers, but you still want
to preserve a strict specified ordering, I think you will have much more
work to do.

Getting a unique number for each case it trivial (but the ordering will
be wrong) and getting an ordering that rates every R > every S > every I
is also trivial, but there will be lots of duplicates.  It's finding the
balance that's going to be hard.

-- 
Ben.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-27 12:29   ` Laurent
  2021-12-27 13:14     ` Ben Bacarisse
  2021-12-27 17:41     ` Dennis Lee Bieber
@ 2021-12-28  2:10     ` Randy Brukardt
  2021-12-28  6:02       ` Laurent
  2 siblings, 1 reply; 25+ messages in thread
From: Randy Brukardt @ 2021-12-28  2:10 UTC (permalink / raw)


"Laurent" <lutgenl@icloud.com> wrote in message 
news:49538254-21ed-4fd0-8316-1bccc7d3c635n@googlegroups.com...
On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote:

...
>> Also, if you do not intend to implement the solution in Ada, this is not
>> the right group to discuss it.

>I would very much prefer to solve it in Ada but at work I am stuck with 
>Excel
>and VBA which is better than doing it manually. After a few hours starring 
>at
>a screen with thousand of rows of results... If I get an Ada solution I can
>adapt it. Just limited to no access/pointers in VBA which shouldn't be 
>required?

Hybrid Ada-spreadsheet solutions are possible. It's quite easy to read/write 
.csv files in Ada, and those can be easily imported/exported from any 
spreadsheet program (I've been using Libreoffice Calc, but Excel is 
similar).

For an example, the ACATS grading tools essentially work by expecting the 
vendor (or a third party) to provide a tool that converts compilation 
results into a .csv file. The .csv file(s) are then read by the grading tool 
and compared to required results to provide a grade. But it also can be read 
into a spreadsheet for sanity checking as well as additional analysis.

Similarly (and probably more useful to you), I've used spreadsheet data for 
various traffic in AdaIC (retrieved from Google) as input to Ada programs 
that analyze the data to provide information that Google is unable to (in 
particular, usage of the various Ada standards, which are split up into 
usage of several hundred separate files). I then take the results of the Ada 
program (which is also a .csv file), open that, and paste the results into a 
previously created spreadsheet that generates charts for showing to 
management. (Even highly skilled programmers don't like looking through 
columns of numbers for trends. :-)

But you do have to be able to describe the results that you are looking for. 
Having read the entire thread, I'm more confused than I started. :-)  I 
suspect when you can describe your problem algorithmically, the solution 
will be obvious. Good luck finding a solution.

                                          Randy.


^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  2:10     ` Randy Brukardt
@ 2021-12-28  6:02       ` Laurent
  2021-12-29  3:58         ` Randy Brukardt
  0 siblings, 1 reply; 25+ messages in thread
From: Laurent @ 2021-12-28  6:02 UTC (permalink / raw)


On Tuesday, 28 December 2021 at 03:10:54 UTC+1, Randy Brukardt wrote:
> "Laurent" <lut...@icloud.com> wrote in message 
> news:49538254-21ed-4fd0...@googlegroups.com...
> On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote:
> ...
> >> Also, if you do not intend to implement the solution in Ada, this is not 
> >> the right group to discuss it. 
> 
> >I would very much prefer to solve it in Ada but at work I am stuck with 
> >Excel 
> >and VBA which is better than doing it manually. After a few hours starring 
> >at 
> >a screen with thousand of rows of results... If I get an Ada solution I can 
> >adapt it. Just limited to no access/pointers in VBA which shouldn't be 
> >required?
> Hybrid Ada-spreadsheet solutions are possible. It's quite easy to read/write 
> .csv files in Ada, and those can be easily imported/exported from any 
> spreadsheet program (I've been using Libreoffice Calc, but Excel is 
> similar). 
> 
> For an example, the ACATS grading tools essentially work by expecting the 
> vendor (or a third party) to provide a tool that converts compilation 
> results into a .csv file. The .csv file(s) are then read by the grading tool 
> and compared to required results to provide a grade. But it also can be read 
> into a spreadsheet for sanity checking as well as additional analysis. 
> 
> Similarly (and probably more useful to you), I've used spreadsheet data for 
> various traffic in AdaIC (retrieved from Google) as input to Ada programs 
> that analyze the data to provide information that Google is unable to (in 
> particular, usage of the various Ada standards, which are split up into 
> usage of several hundred separate files). I then take the results of the Ada 
> program (which is also a .csv file), open that, and paste the results into a 
> previously created spreadsheet that generates charts for showing to 
> management. (Even highly skilled programmers don't like looking through 
> columns of numbers for trends. :-) 
> 
> But you do have to be able to describe the results that you are looking for. 
> Having read the entire thread, I'm more confused than I started. :-) I 
> suspect when you can describe your problem algorithmically, the solution 
> will be obvious. Good luck finding a solution. 
> 
> Randy.

The problem is not that I don't want to use Ada. We are using Citrix so I am stuck with the programs
the IT departments allows me to use. Was already a chore to get MS Access made available.

I could send all the data home but then I have to be very careful to not have some patient
information somewhere floating around.  Otherwise finding a solution to automate
this will be the least of my problems.

Thanks for the motivation

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  0:29             ` Ben Bacarisse
@ 2021-12-28  7:48               ` Laurent
  2021-12-28  9:05                 ` Laurent
                                   ` (2 more replies)
  0 siblings, 3 replies; 25+ messages in thread
From: Laurent @ 2021-12-28  7:48 UTC (permalink / raw)


On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote:
> Laurent <lut...@icloud.com> writes: 
> 
> > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: 
> >> Laurent <lut...@icloud.com> writes: 
> >> 
> >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
> >> >> Laurent <lut...@icloud.com> writes: 
> >> >> 
> >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
> >> >> > 
> >> >> >> Sorry, but I found your problem description impossible to understand. 
> >> >> >> Try to describe more clearly the experiment that is done, the structure 
> >> >> >> of the data the experiment provides (the meaning of the Excel rows and 
> >> >> >> columns), and the statistic you want to compute. 
> >> >> > 
> >> >> > Sorry tried to keep it short, was too short. 
> >> >> > 
> >> >> > Columns are the antimicrobial drugs 
> >> >> > Rows are the microorganism. 
> >> >> > 
> >> >> > So every cell contains a result of S, I, R or simply an empty cell 
> >> >> > 
> >> >> > S = Sensible 
> >> >> > I = Intermediate 
> >> >> > R = Resistant 
> >> >> > 
> >> >> > empty cell <S<I<R 
> >> >> > 
> >> >> > If a patient has 3 strains of the same microorganism but with 
> >> >> > different resistance profiles I have to find the most resistant 
> >> >> > one. Or if they are different I keep them all. 
> >> >> > 
> >> >> > I have no idea how to explain what I am doing to the compiler. 
> >> >> I think when you can explain it to people, you'll be able to code it. I 
> >> >> am still struggling to understand what you need. 
> >> >> > Why I would choose result from strain B over the result from strain A. 
> >> >> > 
> >> >> > strain A: SSSRSS 
> >> >> > strain B: SSRRRS 
> >> >> Let's space it out 
> >> >> 
> >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
> >> >> strain A S S S R S S 
> >> >> strain B S S R R R S 
> >> >> 
> >> >> You want to choose B because it has is resistant to more drugs, yes? 
> >> >> 
> >> > 
> >> > Yes indeed 
> >> > 
> >> >> I think, from the ordering you give, you need a measure that treats an R 
> >> >> as "more important" that any "I" which is "more important" than an "S". 
> >> >> (We will come to empty cells later.) 
> >> >> 
> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> >> >> number. In base 10, the strains score 
> >> >> 
> >> >> R S I 
> >> >> strain A 1 5 0 = 150 
> >> >> strain B 3 3 0 = 330 
> >> >> 
> >> >> Now, in fact, you don't need to use base 10. The smallest base you can 
> >> >> use is one more than the maximum number of test results. If there can 
> >> >> be up to 16 tests (say) the score is 
> >> >> 
> >> >> n(R)*17*17 + n(S)*17 + n(I). 
> >> >> 
> >> >> If this suits your needs, we can consider empty cells later on. It's 
> >> >> not at all clear to me how to compare 
> >> >> 
> >> >> strain C R____ 
> >> >> strain D RRSSSS 
> >> >> 
> >> >> Strain C is "less resistant" but only because there is not enough 
> >> >> information. In fact it seems more serious as it is resistant to all 
> >> >> tested drugs. 
> >> >> 
> >> > 
> >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
> >> > 
> >> >> And then what about 
> >> >> 
> >> >> strain D SR 
> >> >> strain E RS 
> >> >> 
> >> > 
> >> > Yes those are the cases which are annoying me. 
> >> > 
> >> > That's why I came up withe idea of multiplying the value of the result 
> >> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
> >> > triplets but there will still be cases where different results will 
> >> > give the same numeric value. Ignoring empty cell able tps for the moment. 
> >> > 
> >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
> >> > will be the same numerical value but they are different resistance 
> >> > profiles I would in this case keep both. 
> >> > 
> >> > How to prevent that from happening. 
> >> Can you first say why the suggestion I made is not helpful? 
> >> 
> >> -- 
> >> Ben. 
> > 
> > You mean that one: 
> > 
> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> >> >> number. In base 10, the strains score 
> >> >> 
> >> >> R S I 
> >> >> strain A 1 5 0 = 150 
> >> >> strain B 3 3 0 = 330 
> >> >> 
> > 
> > Different resistance profiles same result:
> I don't yet understand the requirements so I am taking it in stages. 
> The first requirement seemed to be "more or less resistant". To do that 
> you can use digits in a large enough base but this will make the number 
> of Rs, Ss and Is paramount. Is that acceptable as a first step? 
> 

The requirements are one strain of a certain microorganism/patient
The most resistant one or if they have different profiles

SRS vs RRS => last one, more Rs

SRS vs RSR = both, different profiles

> In order to help people to be able to make further suggestions, maybe 
> you could give the relative ordering you would like to see between the 
> following sets of profiles. For example, between SSR, SRS and RSS, I 
> think the order you want is RSS > SRS > SSR. 
> 
> 1: SSR, SRS, RSS 
> 
> 2: RSI, RIS, SRI, SIR, IRS, ISR 
> 
> 3: SSSR, SSRS, SRSS, RSSS 
> 
> 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR 
> 

The order of the results is given by the ID of the drug in the extraction tool.
I could probably order them by family and hierarchy of potence but 
would that make a difference?

> It's possible you could make do with an extra field (or digits) that 
> gives some measure of the relative ordering between otherwise similar 
> sequences. For example, using base 10 (for convenience of arithmetic) 
> both RRSSI and RSRSI would score 212xx but the last xx would reflect the 
> positioning of the results in the sequence. There are lots of way to do 
> this. One way would be use, as you were thinking, some sort of weighted 
> count. Using S=0, I=1 and R=2 with weights 
> 
> 54321 
> RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 
> RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 
> 

So to be sure that  I am following:

2*(5+4) = value of R (=2) * position of R(@5 and @4)
2*(5+3) = value of R (=2) * position of R(@5 and @3)

0*(3+2) = value of S (=0) * position of S(@3 and @2)
0*(4+2) = value of S (=0) * position of S(@4 and @2)

1*1 = value of I (=1) * position of I (@1)

2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other
number?

But in this example I would have to keep both as drug 5,2 and 1 are common
to both results but 4 and 3 are unique.

The score would be completely misleading.

So if my table has a width of 20 columns the first column would be
10^20, the next 10^19,.... +/- a few 0s off?

I would have to implement it and see what I get as result.

> If you absolutely must never get duplicate numbers, but you still want 
> to preserve a strict specified ordering, I think you will have much more 
> work to do. 
> 
> Getting a unique number for each case it trivial (but the ordering will 
> be wrong) and getting an ordering that rates every R > every S > every I 
> is also trivial, but there will be lots of duplicates. It's finding the 
> balance that's going to be hard. 
> 
> -- 
> Ben.

I have prepared a cleaned up Excel workbook with only the duplicates which
pose problems. The ones I would keep have an orange ID.
I could upload it to Github. If that helps understanding the different cases.

Thanks for your patience

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  7:48               ` Laurent
@ 2021-12-28  9:05                 ` Laurent
  2021-12-28 12:54                   ` Laurent
  2021-12-28 13:43                 ` Ben Bacarisse
  2021-12-28 16:49                 ` Dennis Lee Bieber
  2 siblings, 1 reply; 25+ messages in thread
From: Laurent @ 2021-12-28  9:05 UTC (permalink / raw)


On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote:
> On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: 
> > Laurent <lut...@icloud.com> writes: 
> > 
> > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: 
> > >> Laurent <lut...@icloud.com> writes: 
> > >> 
> > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
> > >> >> Laurent <lut...@icloud.com> writes: 
> > >> >> 
> > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
> > >> >> > 
> > >> >> >> Sorry, but I found your problem description impossible to understand. 
> > >> >> >> Try to describe more clearly the experiment that is done, the structure 
> > >> >> >> of the data the experiment provides (the meaning of the Excel rows and 
> > >> >> >> columns), and the statistic you want to compute. 
> > >> >> > 
> > >> >> > Sorry tried to keep it short, was too short. 
> > >> >> > 
> > >> >> > Columns are the antimicrobial drugs 
> > >> >> > Rows are the microorganism. 
> > >> >> > 
> > >> >> > So every cell contains a result of S, I, R or simply an empty cell 
> > >> >> > 
> > >> >> > S = Sensible 
> > >> >> > I = Intermediate 
> > >> >> > R = Resistant 
> > >> >> > 
> > >> >> > empty cell <S<I<R 
> > >> >> > 
> > >> >> > If a patient has 3 strains of the same microorganism but with 
> > >> >> > different resistance profiles I have to find the most resistant 
> > >> >> > one. Or if they are different I keep them all. 
> > >> >> > 
> > >> >> > I have no idea how to explain what I am doing to the compiler. 
> > >> >> I think when you can explain it to people, you'll be able to code it. I 
> > >> >> am still struggling to understand what you need. 
> > >> >> > Why I would choose result from strain B over the result from strain A. 
> > >> >> > 
> > >> >> > strain A: SSSRSS 
> > >> >> > strain B: SSRRRS 
> > >> >> Let's space it out 
> > >> >> 
> > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
> > >> >> strain A S S S R S S 
> > >> >> strain B S S R R R S 
> > >> >> 
> > >> >> You want to choose B because it has is resistant to more drugs, yes? 
> > >> >> 
> > >> > 
> > >> > Yes indeed 
> > >> > 
> > >> >> I think, from the ordering you give, you need a measure that treats an R 
> > >> >> as "more important" that any "I" which is "more important" than an "S". 
> > >> >> (We will come to empty cells later.) 
> > >> >> 
> > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> > >> >> number. In base 10, the strains score 
> > >> >> 
> > >> >> R S I 
> > >> >> strain A 1 5 0 = 150 
> > >> >> strain B 3 3 0 = 330 
> > >> >> 
> > >> >> Now, in fact, you don't need to use base 10. The smallest base you can 
> > >> >> use is one more than the maximum number of test results. If there can 
> > >> >> be up to 16 tests (say) the score is 
> > >> >> 
> > >> >> n(R)*17*17 + n(S)*17 + n(I). 
> > >> >> 
> > >> >> If this suits your needs, we can consider empty cells later on. It's 
> > >> >> not at all clear to me how to compare 
> > >> >> 
> > >> >> strain C R____ 
> > >> >> strain D RRSSSS 
> > >> >> 
> > >> >> Strain C is "less resistant" but only because there is not enough 
> > >> >> information. In fact it seems more serious as it is resistant to all 
> > >> >> tested drugs. 
> > >> >> 
> > >> > 
> > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
> > >> > 
> > >> >> And then what about 
> > >> >> 
> > >> >> strain D SR 
> > >> >> strain E RS 
> > >> >> 
> > >> > 
> > >> > Yes those are the cases which are annoying me. 
> > >> > 
> > >> > That's why I came up withe idea of multiplying the value of the result 
> > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
> > >> > triplets but there will still be cases where different results will 
> > >> > give the same numeric value. Ignoring empty cell able tps for the moment. 
> > >> > 
> > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
> > >> > will be the same numerical value but they are different resistance 
> > >> > profiles I would in this case keep both. 
> > >> > 
> > >> > How to prevent that from happening. 
> > >> Can you first say why the suggestion I made is not helpful? 
> > >> 
> > >> -- 
> > >> Ben. 
> > > 
> > > You mean that one: 
> > > 
> > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> > >> >> number. In base 10, the strains score 
> > >> >> 
> > >> >> R S I 
> > >> >> strain A 1 5 0 = 150 
> > >> >> strain B 3 3 0 = 330 
> > >> >> 
> > > 
> > > Different resistance profiles same result: 
> > I don't yet understand the requirements so I am taking it in stages. 
> > The first requirement seemed to be "more or less resistant". To do that 
> > you can use digits in a large enough base but this will make the number 
> > of Rs, Ss and Is paramount. Is that acceptable as a first step? 
> >
> The requirements are one strain of a certain microorganism/patient 
> The most resistant one or if they have different profiles 
> 
> SRS vs RRS => last one, more Rs 
> 
> SRS vs RSR = both, different profiles
> > In order to help people to be able to make further suggestions, maybe 
> > you could give the relative ordering you would like to see between the 
> > following sets of profiles. For example, between SSR, SRS and RSS, I 
> > think the order you want is RSS > SRS > SSR. 
> > 
> > 1: SSR, SRS, RSS 
> > 
> > 2: RSI, RIS, SRI, SIR, IRS, ISR 
> > 
> > 3: SSSR, SSRS, SRSS, RSSS 
> > 
> > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR 
> >
> The order of the results is given by the ID of the drug in the extraction tool. 
> I could probably order them by family and hierarchy of potence but 
> would that make a difference?
> > It's possible you could make do with an extra field (or digits) that 
> > gives some measure of the relative ordering between otherwise similar 
> > sequences. For example, using base 10 (for convenience of arithmetic) 
> > both RRSSI and RSRSI would score 212xx but the last xx would reflect the 
> > positioning of the results in the sequence. There are lots of way to do 
> > this. One way would be use, as you were thinking, some sort of weighted 
> > count. Using S=0, I=1 and R=2 with weights 
> > 
> > 54321 
> > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 
> > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 
> >
> So to be sure that I am following: 
> 
> 2*(5+4) = value of R (=2) * position of R(@5 and @4) 
> 2*(5+3) = value of R (=2) * position of R(@5 and @3) 
> 
> 0*(3+2) = value of S (=0) * position of S(@3 and @2) 
> 0*(4+2) = value of S (=0) * position of S(@4 and @2) 
> 
> 1*1 = value of I (=1) * position of I (@1) 
> 
> 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other 
> number? 
> 

Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100


> But in this example I would have to keep both as drug 5,2 and 1 are common 
> to both results but 4 and 3 are unique. 
> 
> The score would be completely misleading. 
> 
> So if my table has a width of 20 columns the first column would be 
> 10^20, the next 10^19,.... +/- a few 0s off? 
> 
> I would have to implement it and see what I get as result.
> > If you absolutely must never get duplicate numbers, but you still want 
> > to preserve a strict specified ordering, I think you will have much more 
> > work to do. 
> > 
> > Getting a unique number for each case it trivial (but the ordering will 
> > be wrong) and getting an ordering that rates every R > every S > every I 
> > is also trivial, but there will be lots of duplicates. It's finding the 
> > balance that's going to be hard. 
> > 
> > -- 
> > Ben.
> I have prepared a cleaned up Excel workbook with only the duplicates which 
> pose problems. The ones I would keep have an orange ID. 
> I could upload it to Github. If that helps understanding the different cases. 
> 
> Thanks for your patience 
> 
> Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  9:05                 ` Laurent
@ 2021-12-28 12:54                   ` Laurent
  2021-12-28 13:57                     ` Ben Bacarisse
  0 siblings, 1 reply; 25+ messages in thread
From: Laurent @ 2021-12-28 12:54 UTC (permalink / raw)


On Tuesday, 28 December 2021 at 10:05:50 UTC+1, Laurent wrote:
> On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: 
> > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: 
> > > Laurent <lut...@icloud.com> writes: 
> > > 
> > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: 
> > > >> Laurent <lut...@icloud.com> writes: 
> > > >> 
> > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
> > > >> >> Laurent <lut...@icloud.com> writes: 
> > > >> >> 
> > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
> > > >> >> > 
> > > >> >> >> Sorry, but I found your problem description impossible to understand. 
> > > >> >> >> Try to describe more clearly the experiment that is done, the structure 
> > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and 
> > > >> >> >> columns), and the statistic you want to compute. 
> > > >> >> > 
> > > >> >> > Sorry tried to keep it short, was too short. 
> > > >> >> > 
> > > >> >> > Columns are the antimicrobial drugs 
> > > >> >> > Rows are the microorganism. 
> > > >> >> > 
> > > >> >> > So every cell contains a result of S, I, R or simply an empty cell 
> > > >> >> > 
> > > >> >> > S = Sensible 
> > > >> >> > I = Intermediate 
> > > >> >> > R = Resistant 
> > > >> >> > 
> > > >> >> > empty cell <S<I<R 
> > > >> >> > 
> > > >> >> > If a patient has 3 strains of the same microorganism but with 
> > > >> >> > different resistance profiles I have to find the most resistant 
> > > >> >> > one. Or if they are different I keep them all. 
> > > >> >> > 
> > > >> >> > I have no idea how to explain what I am doing to the compiler. 
> > > >> >> I think when you can explain it to people, you'll be able to code it. I 
> > > >> >> am still struggling to understand what you need. 
> > > >> >> > Why I would choose result from strain B over the result from strain A. 
> > > >> >> > 
> > > >> >> > strain A: SSSRSS 
> > > >> >> > strain B: SSRRRS 
> > > >> >> Let's space it out 
> > > >> >> 
> > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
> > > >> >> strain A S S S R S S 
> > > >> >> strain B S S R R R S 
> > > >> >> 
> > > >> >> You want to choose B because it has is resistant to more drugs, yes? 
> > > >> >> 
> > > >> > 
> > > >> > Yes indeed 
> > > >> > 
> > > >> >> I think, from the ordering you give, you need a measure that treats an R 
> > > >> >> as "more important" that any "I" which is "more important" than an "S". 
> > > >> >> (We will come to empty cells later.) 
> > > >> >> 
> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> > > >> >> number. In base 10, the strains score 
> > > >> >> 
> > > >> >> R S I 
> > > >> >> strain A 1 5 0 = 150 
> > > >> >> strain B 3 3 0 = 330 
> > > >> >> 
> > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can 
> > > >> >> use is one more than the maximum number of test results. If there can 
> > > >> >> be up to 16 tests (say) the score is 
> > > >> >> 
> > > >> >> n(R)*17*17 + n(S)*17 + n(I). 
> > > >> >> 
> > > >> >> If this suits your needs, we can consider empty cells later on. It's 
> > > >> >> not at all clear to me how to compare 
> > > >> >> 
> > > >> >> strain C R____ 
> > > >> >> strain D RRSSSS 
> > > >> >> 
> > > >> >> Strain C is "less resistant" but only because there is not enough 
> > > >> >> information. In fact it seems more serious as it is resistant to all 
> > > >> >> tested drugs. 
> > > >> >> 
> > > >> > 
> > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
> > > >> > 
> > > >> >> And then what about 
> > > >> >> 
> > > >> >> strain D SR 
> > > >> >> strain E RS 
> > > >> >> 
> > > >> > 
> > > >> > Yes those are the cases which are annoying me. 
> > > >> > 
> > > >> > That's why I came up withe idea of multiplying the value of the result 
> > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
> > > >> > triplets but there will still be cases where different results will 
> > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. 
> > > >> > 
> > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
> > > >> > will be the same numerical value but they are different resistance 
> > > >> > profiles I would in this case keep both. 
> > > >> > 
> > > >> > How to prevent that from happening. 
> > > >> Can you first say why the suggestion I made is not helpful? 
> > > >> 
> > > >> -- 
> > > >> Ben. 
> > > > 
> > > > You mean that one: 
> > > > 
> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> > > >> >> number. In base 10, the strains score 
> > > >> >> 
> > > >> >> R S I 
> > > >> >> strain A 1 5 0 = 150 
> > > >> >> strain B 3 3 0 = 330 
> > > >> >> 
> > > > 
> > > > Different resistance profiles same result: 
> > > I don't yet understand the requirements so I am taking it in stages. 
> > > The first requirement seemed to be "more or less resistant". To do that 
> > > you can use digits in a large enough base but this will make the number 
> > > of Rs, Ss and Is paramount. Is that acceptable as a first step? 
> > > 
> > The requirements are one strain of a certain microorganism/patient 
> > The most resistant one or if they have different profiles 
> > 
> > SRS vs RRS => last one, more Rs 
> > 
> > SRS vs RSR = both, different profiles 
> > > In order to help people to be able to make further suggestions, maybe 
> > > you could give the relative ordering you would like to see between the 
> > > following sets of profiles. For example, between SSR, SRS and RSS, I 
> > > think the order you want is RSS > SRS > SSR. 
> > > 
> > > 1: SSR, SRS, RSS 
> > > 
> > > 2: RSI, RIS, SRI, SIR, IRS, ISR 
> > > 
> > > 3: SSSR, SSRS, SRSS, RSSS 
> > > 
> > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR 
> > > 
> > The order of the results is given by the ID of the drug in the extraction tool. 
> > I could probably order them by family and hierarchy of potence but 
> > would that make a difference? 
> > > It's possible you could make do with an extra field (or digits) that 
> > > gives some measure of the relative ordering between otherwise similar 
> > > sequences. For example, using base 10 (for convenience of arithmetic) 
> > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the 
> > > positioning of the results in the sequence. There are lots of way to do 
> > > this. One way would be use, as you were thinking, some sort of weighted 
> > > count. Using S=0, I=1 and R=2 with weights 
> > > 
> > > 54321 
> > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 
> > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 
> > > 
> > So to be sure that I am following: 
> > 
> > 2*(5+4) = value of R (=2) * position of R(@5 and @4) 
> > 2*(5+3) = value of R (=2) * position of R(@5 and @3) 
> > 
> > 0*(3+2) = value of S (=0) * position of S(@3 and @2) 
> > 0*(4+2) = value of S (=0) * position of S(@4 and @2) 
> > 
> > 1*1 = value of I (=1) * position of I (@1) 
> > 
> > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other 
> > number? 
> >
> Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100
> > But in this example I would have to keep both as drug 5,2 and 1 are common 
> > to both results but 4 and 3 are unique. 
> > 
> > The score would be completely misleading. 
> > 
> > So if my table has a width of 20 columns the first column would be 
> > 10^20, the next 10^19,.... +/- a few 0s off? 
> > 
> > I would have to implement it and see what I get as result. 
> > > If you absolutely must never get duplicate numbers, but you still want 
> > > to preserve a strict specified ordering, I think you will have much more 
> > > work to do. 
> > > 
> > > Getting a unique number for each case it trivial (but the ordering will 
> > > be wrong) and getting an ordering that rates every R > every S > every I 
> > > is also trivial, but there will be lots of duplicates. It's finding the 
> > > balance that's going to be hard. 
> > > 
> > > -- 
> > > Ben. 
> > I have prepared a cleaned up Excel workbook with only the duplicates which 
> > pose problems. The ones I would keep have an orange ID. 
> > I could upload it to Github. If that helps understanding the different cases. 
> > 
> > Thanks for your patience 
> > 
> > Laurent

Ben,

I have implemented your solution but I don't understand the reason why S would have a value of 0?
I then don't need to take care of the S'es because the result will always be 0. Not that it changes a lot

Because I still couldn't choose the profile of interest only based on the numbers.

R	R	S	S	I	 Ben's Solution: 212 11 Mine: 212 1205 
R	S	R	S	I	                            212 13            212 1405 
R	R	R	S	I	                            311 17            311 1805
R	S	R	R	I	                            311 21            311 1407
S	R	R	R	I		                    311 23            311 1607

311 17 and 311 23 being the most likely but unclear where the difference might be.

I have adapted my current solution to include the number of R,I,S
weight of the results: S=1, I=2, R=3
weight of the position in the triplet: 1st=1, 2nd=2, 3rd=3

ie.:  R	R	R => First triplet: 1*3+2*3+3*3 = 18 
        S	I          => Second triplet 1*1+2*2 = 05

RIS count: 311 
Append 1st triplet: 311 18 
Append 2nd triplet: 311 18 05

311 18 05 and 311 16 07  being the most likely with some clues which triplet is different.

Am I not somehow introducing a bias by multiplying the value with the position in the triplet?
And then there is still the case where SSR (1*1+2*1+3*3=12) and RRS (1*3+2*3+3*1=12)
will both resolve to the same value.

With 5 values it looks easy but with 20 I am getting headaches.

I don't even know if the triplet idea is good. Got inspired by some old microorganism identification 
cards which put 3 test results into one digit to get a more compact identification profile.

Wouldn't I need some sort of Traveling Salesman Problems algorithm to find the profile
with the highest number of resistances and the highest number of triplets with high values.

Thanks

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  7:48               ` Laurent
  2021-12-28  9:05                 ` Laurent
@ 2021-12-28 13:43                 ` Ben Bacarisse
  2021-12-28 16:49                 ` Dennis Lee Bieber
  2 siblings, 0 replies; 25+ messages in thread
From: Ben Bacarisse @ 2021-12-28 13:43 UTC (permalink / raw)


Laurent <lutgenl@icloud.com> writes:

> On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote:
>> Laurent <lut...@icloud.com> writes: 
>> 
>> > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: 
>> >> Laurent <lut...@icloud.com> writes: 
>> >> 
>> >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
>> >> >> Laurent <lut...@icloud.com> writes: 
>> >> >> 
>> >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
>> >> >> > 
>> >> >> >> Sorry, but I found your problem description impossible to understand. 
>> >> >> >> Try to describe more clearly the experiment that is done, the structure 
>> >> >> >> of the data the experiment provides (the meaning of the Excel rows and 
>> >> >> >> columns), and the statistic you want to compute. 
>> >> >> > 
>> >> >> > Sorry tried to keep it short, was too short. 
>> >> >> > 
>> >> >> > Columns are the antimicrobial drugs 
>> >> >> > Rows are the microorganism. 
>> >> >> > 
>> >> >> > So every cell contains a result of S, I, R or simply an empty cell 
>> >> >> > 
>> >> >> > S = Sensible 
>> >> >> > I = Intermediate 
>> >> >> > R = Resistant 
>> >> >> > 
>> >> >> > empty cell <S<I<R 
>> >> >> > 
>> >> >> > If a patient has 3 strains of the same microorganism but with 
>> >> >> > different resistance profiles I have to find the most resistant 
>> >> >> > one. Or if they are different I keep them all. 
>> >> >> > 
>> >> >> > I have no idea how to explain what I am doing to the compiler. 
>> >> >> I think when you can explain it to people, you'll be able to code it. I 
>> >> >> am still struggling to understand what you need. 
>> >> >> > Why I would choose result from strain B over the result from strain A. 
>> >> >> > 
>> >> >> > strain A: SSSRSS 
>> >> >> > strain B: SSRRRS 
>> >> >> Let's space it out 
>> >> >> 
>> >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
>> >> >> strain A S S S R S S 
>> >> >> strain B S S R R R S 
>> >> >> 
>> >> >> You want to choose B because it has is resistant to more drugs, yes? 
>> >> >> 
>> >> > 
>> >> > Yes indeed 
>> >> > 
>> >> >> I think, from the ordering you give, you need a measure that treats an R 
>> >> >> as "more important" that any "I" which is "more important" than an "S". 
>> >> >> (We will come to empty cells later.) 
>> >> >> 
>> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> >> >> number. In base 10, the strains score 
>> >> >> 
>> >> >> R S I 
>> >> >> strain A 1 5 0 = 150 
>> >> >> strain B 3 3 0 = 330 
>> >> >> 
>> >> >> Now, in fact, you don't need to use base 10. The smallest base you can 
>> >> >> use is one more than the maximum number of test results. If there can 
>> >> >> be up to 16 tests (say) the score is 
>> >> >> 
>> >> >> n(R)*17*17 + n(S)*17 + n(I). 
>> >> >> 
>> >> >> If this suits your needs, we can consider empty cells later on. It's 
>> >> >> not at all clear to me how to compare 
>> >> >> 
>> >> >> strain C R____ 
>> >> >> strain D RRSSSS 
>> >> >> 
>> >> >> Strain C is "less resistant" but only because there is not enough 
>> >> >> information. In fact it seems more serious as it is resistant to all 
>> >> >> tested drugs. 
>> >> >> 
>> >> > 
>> >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
>> >> > 
>> >> >> And then what about 
>> >> >> 
>> >> >> strain D SR 
>> >> >> strain E RS 
>> >> >> 
>> >> > 
>> >> > Yes those are the cases which are annoying me. 
>> >> > 
>> >> > That's why I came up withe idea of multiplying the value of the result 
>> >> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
>> >> > triplets but there will still be cases where different results will 
>> >> > give the same numeric value. Ignoring empty cell able tps for the moment. 
>> >> > 
>> >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
>> >> > will be the same numerical value but they are different resistance 
>> >> > profiles I would in this case keep both. 
>> >> > 
>> >> > How to prevent that from happening. 
>> >> Can you first say why the suggestion I made is not helpful? 
>> >> 
>> >> -- 
>> >> Ben. 
>> > 
>> > You mean that one: 
>> > 
>> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> >> >> number. In base 10, the strains score 
>> >> >> 
>> >> >> R S I 
>> >> >> strain A 1 5 0 = 150 
>> >> >> strain B 3 3 0 = 330 
>> >> >> 
>> > 
>> > Different resistance profiles same result:
>>
>> I don't yet understand the requirements so I am taking it in stages. 
>> The first requirement seemed to be "more or less resistant". To do that 
>> you can use digits in a large enough base but this will make the number 
>> of Rs, Ss and Is paramount. Is that acceptable as a first step?  
>
> The requirements are one strain of a certain microorganism/patient
> The most resistant one or if they have different profiles
>
> SRS vs RRS => last one, more Rs
>
> SRS vs RSR = both, different profiles

I think this is a "yes" to my question.  The trouble is you speak in the
subject domain (as one would expect) but I have to speak in the computer
science domain, because that's all I know.

You speak of giving profiles a score.  To me, that mean giving a
profile some numeric value (actually it need not be numeric, but let's
stick with numbers for the moment).  The score orders the profiles --
some score high (= very resistant) and some score lower.

>> In order to help people to be able to make further suggestions, maybe 
>> you could give the relative ordering you would like to see between the 
>> following sets of profiles. For example, between SSR, SRS and RSS, I 
>> think the order you want is RSS > SRS > SSR. 
>> 
>> 1: SSR, SRS, RSS 
>> 
>> 2: RSI, RIS, SRI, SIR, IRS, ISR 
>> 
>> 3: SSSR, SSRS, SRSS, RSSS 
>> 
>> 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR 
>
> The order of the results is given by the ID of the drug in the extraction tool.
> I could probably order them by family and hierarchy of potence but 
> would that make a difference?

I am referring to the order you want the score to produce.  You want, I
think, the score for a profile with more Rs to be higher than any score
for a profile with fewer.  Using x for an S or an I or a missing result,
you want all of

  RRRxxx  xRRxRx  xxRxRR

and so on to score higher than any of

  RRxxxx  RxxRxx  xxRxRx

Three Rs beats two R no matter where they are.  Similarly, when the
number of Rs is the same, you want a profile with more Is to "beat"
(score higher) than any profile with fewer.

There is a standard way to do this which can result in a pure number,
but you can also think of it as a short sequence of numbers (three in
this case) where the first is more important than the second, which is
more important than the third.

So IIISR is given the sequence (1,1,3) (1 R, 1 S and 3 Is).  As a base
10 number, that's 113.  In base 100 it's 10103.  Bigger bases allow one
to separate larger counts.

Now there is also a secondary ordering.  When the number of Rs, Is and
Ss is the same, I think you wanted to consider some test results as more
important.  To do that, I am suggesting adding another number to the
sequence or another digit or two if you like.

>> It's possible you could make do with an extra field (or digits) that 
>> gives some measure of the relative ordering between otherwise similar 
>> sequences. For example, using base 10 (for convenience of arithmetic) 
>> both RRSSI and RSRSI would score 212xx but the last xx would reflect the 
>> positioning of the results in the sequence. There are lots of way to do 
>> this. One way would be use, as you were thinking, some sort of weighted 
>> count. Using S=0, I=1 and R=2 with weights 
>> 
>> 54321 
>> RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 
>> RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 
>> 
>
> So to be sure that  I am following:
>
> 2*(5+4) = value of R (=2) * position of R(@5 and @4)
> 2*(5+3) = value of R (=2) * position of R(@5 and @3)
>
> 0*(3+2) = value of S (=0) * position of S(@3 and @2)
> 0*(4+2) = value of S (=0) * position of S(@4 and @2)
>
> 1*1 = value of I (=1) * position of I (@1)

Yes.

> 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other
> number?

No, the 212 reflects the counts or Rs, Is and Ss.  It's up the high
digits so that these counts trump everything else in the score.

> But in this example I would have to keep both as drug 5,2 and 1 are common
> to both results but 4 and 3 are unique.

Ah, more domain specific constraints are coming in.  I think I will have
to duck out of this thread soon.

> The score would be completely misleading.
>
> So if my table has a width of 20 columns the first column would be
> 10^20, the next 10^19,.... +/- a few 0s off?

No.  If you have twenty columns, you need to use a base of at least 21
because the R, I and S counts could be as high as 20.  For convenience,
use 100 and leave two base-100 digits spare "at the bottom" for the "all
counts being equal" differential score:

  nRs nIs nSs 00 00

10 decimal digits in all.  But see below for another option.

> I would have to implement it and see what I get as result.

To get better answers, say what it is about the results that you don't
like.  If you can't say what you do want, saying what you don't want is
the next best thing.

> I have prepared a cleaned up Excel workbook with only the duplicates which
> pose problems. The ones I would keep have an orange ID.
> I could upload it to Github. If that helps understanding the different
> cases.

Probably.  I may have the wrong end of the stick altogether because you
worry about duplicates but talk about scores suggesting better or
worse.  There is nothing logically wrong with a duplicate score.

You can, very simply, assign every set of results a unique number.  Just
replace R with 2, I with 1 and S with 0 and concatenate the results to
treat it as number:

  IIISR = 11102

If you need the R, I and S counts to "dominate", prepend this number
with the counts (using, say, 2 digits each):

  IIISR = 1030111102  (1 R, 3 Is, 1 S and the number from the seqeunce)

Of course you can make the numbers smaller by using smaller bases, but I
can't say if this produces the kind of score that you'd find useful.

-- 
Ben.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28 12:54                   ` Laurent
@ 2021-12-28 13:57                     ` Ben Bacarisse
  2021-12-28 18:19                       ` Laurent
  0 siblings, 1 reply; 25+ messages in thread
From: Ben Bacarisse @ 2021-12-28 13:57 UTC (permalink / raw)


Laurent <lutgenl@icloud.com> writes:

> On Tuesday, 28 December 2021 at 10:05:50 UTC+1, Laurent wrote:
>> On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: 
>> > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: 
>> > > Laurent <lut...@icloud.com> writes: 
>> > > 
>> > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: 
>> > > >> Laurent <lut...@icloud.com> writes: 
>> > > >> 
>> > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
>> > > >> >> Laurent <lut...@icloud.com> writes: 
>> > > >> >> 
>> > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
>> > > >> >> > 
>> > > >> >> >> Sorry, but I found your problem description impossible to understand. 
>> > > >> >> >> Try to describe more clearly the experiment that is done, the structure 
>> > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and 
>> > > >> >> >> columns), and the statistic you want to compute. 
>> > > >> >> > 
>> > > >> >> > Sorry tried to keep it short, was too short. 
>> > > >> >> > 
>> > > >> >> > Columns are the antimicrobial drugs 
>> > > >> >> > Rows are the microorganism. 
>> > > >> >> > 
>> > > >> >> > So every cell contains a result of S, I, R or simply an empty cell 
>> > > >> >> > 
>> > > >> >> > S = Sensible 
>> > > >> >> > I = Intermediate 
>> > > >> >> > R = Resistant 
>> > > >> >> > 
>> > > >> >> > empty cell <S<I<R 
>> > > >> >> > 
>> > > >> >> > If a patient has 3 strains of the same microorganism but with 
>> > > >> >> > different resistance profiles I have to find the most resistant 
>> > > >> >> > one. Or if they are different I keep them all. 
>> > > >> >> > 
>> > > >> >> > I have no idea how to explain what I am doing to the compiler. 
>> > > >> >> I think when you can explain it to people, you'll be able to code it. I 
>> > > >> >> am still struggling to understand what you need. 
>> > > >> >> > Why I would choose result from strain B over the result from strain A. 
>> > > >> >> > 
>> > > >> >> > strain A: SSSRSS 
>> > > >> >> > strain B: SSRRRS 
>> > > >> >> Let's space it out 
>> > > >> >> 
>> > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
>> > > >> >> strain A S S S R S S 
>> > > >> >> strain B S S R R R S 
>> > > >> >> 
>> > > >> >> You want to choose B because it has is resistant to more drugs, yes? 
>> > > >> >> 
>> > > >> > 
>> > > >> > Yes indeed 
>> > > >> > 
>> > > >> >> I think, from the ordering you give, you need a measure that treats an R 
>> > > >> >> as "more important" that any "I" which is "more important" than an "S". 
>> > > >> >> (We will come to empty cells later.) 
>> > > >> >> 
>> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> > > >> >> number. In base 10, the strains score 
>> > > >> >> 
>> > > >> >> R S I 
>> > > >> >> strain A 1 5 0 = 150 
>> > > >> >> strain B 3 3 0 = 330 
>> > > >> >> 
>> > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can 
>> > > >> >> use is one more than the maximum number of test results. If there can 
>> > > >> >> be up to 16 tests (say) the score is 
>> > > >> >> 
>> > > >> >> n(R)*17*17 + n(S)*17 + n(I). 
>> > > >> >> 
>> > > >> >> If this suits your needs, we can consider empty cells later on. It's 
>> > > >> >> not at all clear to me how to compare 
>> > > >> >> 
>> > > >> >> strain C R____ 
>> > > >> >> strain D RRSSSS 
>> > > >> >> 
>> > > >> >> Strain C is "less resistant" but only because there is not enough 
>> > > >> >> information. In fact it seems more serious as it is resistant to all 
>> > > >> >> tested drugs. 
>> > > >> >> 
>> > > >> > 
>> > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
>> > > >> > 
>> > > >> >> And then what about 
>> > > >> >> 
>> > > >> >> strain D SR 
>> > > >> >> strain E RS 
>> > > >> >> 
>> > > >> > 
>> > > >> > Yes those are the cases which are annoying me. 
>> > > >> > 
>> > > >> > That's why I came up withe idea of multiplying the value of the result 
>> > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
>> > > >> > triplets but there will still be cases where different results will 
>> > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. 
>> > > >> > 
>> > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
>> > > >> > will be the same numerical value but they are different resistance 
>> > > >> > profiles I would in this case keep both. 
>> > > >> > 
>> > > >> > How to prevent that from happening. 
>> > > >> Can you first say why the suggestion I made is not helpful? 
>> > > >> 
>> > > >> -- 
>> > > >> Ben. 
>> > > > 
>> > > > You mean that one: 
>> > > > 
>> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
>> > > >> >> number. In base 10, the strains score 
>> > > >> >> 
>> > > >> >> R S I 
>> > > >> >> strain A 1 5 0 = 150 
>> > > >> >> strain B 3 3 0 = 330 
>> > > >> >> 
>> > > > 
>> > > > Different resistance profiles same result: 
>> > > I don't yet understand the requirements so I am taking it in stages. 
>> > > The first requirement seemed to be "more or less resistant". To do that 
>> > > you can use digits in a large enough base but this will make the number 
>> > > of Rs, Ss and Is paramount. Is that acceptable as a first step? 
>> > > 
>> > The requirements are one strain of a certain microorganism/patient 
>> > The most resistant one or if they have different profiles 
>> > 
>> > SRS vs RRS => last one, more Rs 
>> > 
>> > SRS vs RSR = both, different profiles 
>> > > In order to help people to be able to make further suggestions, maybe 
>> > > you could give the relative ordering you would like to see between the 
>> > > following sets of profiles. For example, between SSR, SRS and RSS, I 
>> > > think the order you want is RSS > SRS > SSR. 
>> > > 
>> > > 1: SSR, SRS, RSS 
>> > > 
>> > > 2: RSI, RIS, SRI, SIR, IRS, ISR 
>> > > 
>> > > 3: SSSR, SSRS, SRSS, RSSS 
>> > > 
>> > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR 
>> > > 
>> > The order of the results is given by the ID of the drug in the extraction tool. 
>> > I could probably order them by family and hierarchy of potence but 
>> > would that make a difference? 
>> > > It's possible you could make do with an extra field (or digits) that 
>> > > gives some measure of the relative ordering between otherwise similar 
>> > > sequences. For example, using base 10 (for convenience of arithmetic) 
>> > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the 
>> > > positioning of the results in the sequence. There are lots of way to do 
>> > > this. One way would be use, as you were thinking, some sort of weighted 
>> > > count. Using S=0, I=1 and R=2 with weights 
>> > > 
>> > > 54321 
>> > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 
>> > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 
>> > > 
>> > So to be sure that I am following: 
>> > 
>> > 2*(5+4) = value of R (=2) * position of R(@5 and @4) 
>> > 2*(5+3) = value of R (=2) * position of R(@5 and @3) 
>> > 
>> > 0*(3+2) = value of S (=0) * position of S(@3 and @2) 
>> > 0*(4+2) = value of S (=0) * position of S(@4 and @2) 
>> > 
>> > 1*1 = value of I (=1) * position of I (@1) 
>> > 
>> > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other 
>> > number? 
>> >
>> Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100
>> > But in this example I would have to keep both as drug 5,2 and 1 are common 
>> > to both results but 4 and 3 are unique. 
>> > 
>> > The score would be completely misleading. 
>> > 
>> > So if my table has a width of 20 columns the first column would be 
>> > 10^20, the next 10^19,.... +/- a few 0s off? 
>> > 
>> > I would have to implement it and see what I get as result. 
>> > > If you absolutely must never get duplicate numbers, but you still want 
>> > > to preserve a strict specified ordering, I think you will have much more 
>> > > work to do. 
>> > > 
>> > > Getting a unique number for each case it trivial (but the ordering will 
>> > > be wrong) and getting an ordering that rates every R > every S > every I 
>> > > is also trivial, but there will be lots of duplicates. It's finding the 
>> > > balance that's going to be hard. 
>> > > 
>> > > -- 
>> > > Ben. 
>> > I have prepared a cleaned up Excel workbook with only the duplicates which 
>> > pose problems. The ones I would keep have an orange ID. 
>> > I could upload it to Github. If that helps understanding the different cases. 
>> > 
>> > Thanks for your patience 
>> > 
>> > Laurent
>
> Ben,

Posts crossed.  You should probably ignore my last as it was written
before I saw this one.

> I have implemented your solution but I don't understand the reason why S would have a value of 0?
> I then don't need to take care of the S'es because the result will always be 0. Not that it changes a lot
>
> Because I still couldn't choose the profile of interest only based on the numbers.
>
> R	R	S	S	I	 Ben's Solution: 212 11 Mine: 212 1205 
> R	S	R	S	I	                            212 13            212 1405 
> R	R	R	S	I	                            311 17            311 1805
> R	S	R	R	I	                            311 21            311 1407
> S	R	R	R	I		                    311 23            311 1607
>
> 311 17 and 311 23 being the most likely but unclear where the
> difference might be.

This is what is so frustrating for me.  What do you mean, most likely?
What do you mean be what the difference might be?  Can you describe to
me, as a human being, which you would choose and tell me how you
decided.  If you can't do that then all you are doing is trying random
schemes until something pops up the look right for some specific set of
data!

> I have adapted my current solution to include the number of R,I,S
> weight of the results: S=1, I=2, R=3
> weight of the position in the triplet: 1st=1, 2nd=2, 3rd=3
>
> ie.:  R	R	R => First triplet: 1*3+2*3+3*3 = 18 
>         S	I          => Second triplet 1*1+2*2 = 05
>
> RIS count: 311 
> Append 1st triplet: 311 18 
> Append 2nd triplet: 311 18 05
>
> 311 18 05 and 311 16 07  being the most likely with some clues which
> triplet is different.

It sound like you want the result to "give some clues".  Why not just
return the string of letters?  SRRRI tells you everything about the
tests.  What more could you want?  If you want these ordered by number
R, I and S counts, put these first always using two digits:

 "030101SRRRI"

This string will sort the important, highly resistant strains to the top
and also gives all the information about the individual tests.

> Am I not somehow introducing a bias by multiplying the value with the position in the triplet?
> And then there is still the case where SSR (1*1+2*1+3*3=12) and RRS (1*3+2*3+3*1=12)
> will both resolve to the same value.

Eh?  Don't you want more Rs to get high scores?  That's what the counts
are for.

> Wouldn't I need some sort of Traveling Salesman Problems algorithm to find the profile
> with the highest number of resistances and the highest number of
> triplets with high values.

I don't understand the triplets idea.  Sorry.

-- 
Ben.

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  7:48               ` Laurent
  2021-12-28  9:05                 ` Laurent
  2021-12-28 13:43                 ` Ben Bacarisse
@ 2021-12-28 16:49                 ` Dennis Lee Bieber
  2021-12-29  4:20                   ` Randy Brukardt
  2 siblings, 1 reply; 25+ messages in thread
From: Dennis Lee Bieber @ 2021-12-28 16:49 UTC (permalink / raw)


On Mon, 27 Dec 2021 23:48:31 -0800 (PST), Laurent <lutgenl@icloud.com>
declaimed the following:

>
>The requirements are one strain of a certain microorganism/patient
>The most resistant one or if they have different profiles
>
>SRS vs RRS => last one, more Rs
>
>SRS vs RSR = both, different profiles
>

	Which is still inconclusive (at least as I view it) -- your second
example ALSO fits the "last one, more Rs" constraint. You haven't to define
how the first doesn't qualify as "different profiles". Both examples are
"1R, 2S" vs "2R, 1S".



-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
	wlfraed@ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28 13:57                     ` Ben Bacarisse
@ 2021-12-28 18:19                       ` Laurent
  0 siblings, 0 replies; 25+ messages in thread
From: Laurent @ 2021-12-28 18:19 UTC (permalink / raw)


On Tuesday, 28 December 2021 at 14:57:22 UTC+1, Ben Bacarisse wrote:
> Laurent <lut...@icloud.com> writes: 
> 
> > On Tuesday, 28 December 2021 at 10:05:50 UTC+1, Laurent wrote: 
> >> On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: 
> >> > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: 
> >> > > Laurent <lut...@icloud.com> writes: 
> >> > > 
> >> > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: 
> >> > > >> Laurent <lut...@icloud.com> writes: 
> >> > > >> 
> >> > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: 
> >> > > >> >> Laurent <lut...@icloud.com> writes: 
> >> > > >> >> 
> >> > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: 
> >> > > >> >> > 
> >> > > >> >> >> Sorry, but I found your problem description impossible to understand. 
> >> > > >> >> >> Try to describe more clearly the experiment that is done, the structure 
> >> > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and 
> >> > > >> >> >> columns), and the statistic you want to compute. 
> >> > > >> >> > 
> >> > > >> >> > Sorry tried to keep it short, was too short. 
> >> > > >> >> > 
> >> > > >> >> > Columns are the antimicrobial drugs 
> >> > > >> >> > Rows are the microorganism. 
> >> > > >> >> > 
> >> > > >> >> > So every cell contains a result of S, I, R or simply an empty cell 
> >> > > >> >> > 
> >> > > >> >> > S = Sensible 
> >> > > >> >> > I = Intermediate 
> >> > > >> >> > R = Resistant 
> >> > > >> >> > 
> >> > > >> >> > empty cell <S<I<R 
> >> > > >> >> > 
> >> > > >> >> > If a patient has 3 strains of the same microorganism but with 
> >> > > >> >> > different resistance profiles I have to find the most resistant 
> >> > > >> >> > one. Or if they are different I keep them all. 
> >> > > >> >> > 
> >> > > >> >> > I have no idea how to explain what I am doing to the compiler. 
> >> > > >> >> I think when you can explain it to people, you'll be able to code it. I 
> >> > > >> >> am still struggling to understand what you need. 
> >> > > >> >> > Why I would choose result from strain B over the result from strain A. 
> >> > > >> >> > 
> >> > > >> >> > strain A: SSSRSS 
> >> > > >> >> > strain B: SSRRRS 
> >> > > >> >> Let's space it out 
> >> > > >> >> 
> >> > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 
> >> > > >> >> strain A S S S R S S 
> >> > > >> >> strain B S S R R R S 
> >> > > >> >> 
> >> > > >> >> You want to choose B because it has is resistant to more drugs, yes? 
> >> > > >> >> 
> >> > > >> > 
> >> > > >> > Yes indeed 
> >> > > >> > 
> >> > > >> >> I think, from the ordering you give, you need a measure that treats an R 
> >> > > >> >> as "more important" that any "I" which is "more important" than an "S". 
> >> > > >> >> (We will come to empty cells later.) 
> >> > > >> >> 
> >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> >> > > >> >> number. In base 10, the strains score 
> >> > > >> >> 
> >> > > >> >> R S I 
> >> > > >> >> strain A 1 5 0 = 150 
> >> > > >> >> strain B 3 3 0 = 330 
> >> > > >> >> 
> >> > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can 
> >> > > >> >> use is one more than the maximum number of test results. If there can 
> >> > > >> >> be up to 16 tests (say) the score is 
> >> > > >> >> 
> >> > > >> >> n(R)*17*17 + n(S)*17 + n(I). 
> >> > > >> >> 
> >> > > >> >> If this suits your needs, we can consider empty cells later on. It's 
> >> > > >> >> not at all clear to me how to compare 
> >> > > >> >> 
> >> > > >> >> strain C R____ 
> >> > > >> >> strain D RRSSSS 
> >> > > >> >> 
> >> > > >> >> Strain C is "less resistant" but only because there is not enough 
> >> > > >> >> information. In fact it seems more serious as it is resistant to all 
> >> > > >> >> tested drugs. 
> >> > > >> >> 
> >> > > >> > 
> >> > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. 
> >> > > >> > 
> >> > > >> >> And then what about 
> >> > > >> >> 
> >> > > >> >> strain D SR 
> >> > > >> >> strain E RS 
> >> > > >> >> 
> >> > > >> > 
> >> > > >> > Yes those are the cases which are annoying me. 
> >> > > >> > 
> >> > > >> > That's why I came up withe idea of multiplying the value of the result 
> >> > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with 
> >> > > >> > triplets but there will still be cases where different results will 
> >> > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. 
> >> > > >> > 
> >> > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 
> >> > > >> > will be the same numerical value but they are different resistance 
> >> > > >> > profiles I would in this case keep both. 
> >> > > >> > 
> >> > > >> > How to prevent that from happening. 
> >> > > >> Can you first say why the suggestion I made is not helpful? 
> >> > > >> 
> >> > > >> -- 
> >> > > >> Ben. 
> >> > > > 
> >> > > > You mean that one: 
> >> > > > 
> >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a 
> >> > > >> >> number. In base 10, the strains score 
> >> > > >> >> 
> >> > > >> >> R S I 
> >> > > >> >> strain A 1 5 0 = 150 
> >> > > >> >> strain B 3 3 0 = 330 
> >> > > >> >> 
> >> > > > 
> >> > > > Different resistance profiles same result: 
> >> > > I don't yet understand the requirements so I am taking it in stages. 
> >> > > The first requirement seemed to be "more or less resistant". To do that 
> >> > > you can use digits in a large enough base but this will make the number 
> >> > > of Rs, Ss and Is paramount. Is that acceptable as a first step? 
> >> > > 
> >> > The requirements are one strain of a certain microorganism/patient 
> >> > The most resistant one or if they have different profiles 
> >> > 
> >> > SRS vs RRS => last one, more Rs 
> >> > 
> >> > SRS vs RSR = both, different profiles 
> >> > > In order to help people to be able to make further suggestions, maybe 
> >> > > you could give the relative ordering you would like to see between the 
> >> > > following sets of profiles. For example, between SSR, SRS and RSS, I 
> >> > > think the order you want is RSS > SRS > SSR. 
> >> > > 
> >> > > 1: SSR, SRS, RSS 
> >> > > 
> >> > > 2: RSI, RIS, SRI, SIR, IRS, ISR 
> >> > > 
> >> > > 3: SSSR, SSRS, SRSS, RSSS 
> >> > > 
> >> > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR 
> >> > > 
> >> > The order of the results is given by the ID of the drug in the extraction tool. 
> >> > I could probably order them by family and hierarchy of potence but 
> >> > would that make a difference? 
> >> > > It's possible you could make do with an extra field (or digits) that 
> >> > > gives some measure of the relative ordering between otherwise similar 
> >> > > sequences. For example, using base 10 (for convenience of arithmetic) 
> >> > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the 
> >> > > positioning of the results in the sequence. There are lots of way to do 
> >> > > this. One way would be use, as you were thinking, some sort of weighted 
> >> > > count. Using S=0, I=1 and R=2 with weights 
> >> > > 
> >> > > 54321 
> >> > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 
> >> > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 
> >> > > 
> >> > So to be sure that I am following: 
> >> > 
> >> > 2*(5+4) = value of R (=2) * position of R(@5 and @4) 
> >> > 2*(5+3) = value of R (=2) * position of R(@5 and @3) 
> >> > 
> >> > 0*(3+2) = value of S (=0) * position of S(@3 and @2) 
> >> > 0*(4+2) = value of S (=0) * position of S(@4 and @2) 
> >> > 
> >> > 1*1 = value of I (=1) * position of I (@1) 
> >> > 
> >> > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other 
> >> > number? 
> >> > 
> >> Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100 
> >> > But in this example I would have to keep both as drug 5,2 and 1 are common 
> >> > to both results but 4 and 3 are unique. 
> >> > 
> >> > The score would be completely misleading. 
> >> > 
> >> > So if my table has a width of 20 columns the first column would be 
> >> > 10^20, the next 10^19,.... +/- a few 0s off? 
> >> > 
> >> > I would have to implement it and see what I get as result. 
> >> > > If you absolutely must never get duplicate numbers, but you still want 
> >> > > to preserve a strict specified ordering, I think you will have much more 
> >> > > work to do. 
> >> > > 
> >> > > Getting a unique number for each case it trivial (but the ordering will 
> >> > > be wrong) and getting an ordering that rates every R > every S > every I 
> >> > > is also trivial, but there will be lots of duplicates. It's finding the 
> >> > > balance that's going to be hard. 
> >> > > 
> >> > > -- 
> >> > > Ben. 
> >> > I have prepared a cleaned up Excel workbook with only the duplicates which 
> >> > pose problems. The ones I would keep have an orange ID. 
> >> > I could upload it to Github. If that helps understanding the different cases. 
> >> > 
> >> > Thanks for your patience 
> >> > 
> >> > Laurent 
> > 
> > Ben,
> Posts crossed. You should probably ignore my last as it was written 
> before I saw this one.
> > I have implemented your solution but I don't understand the reason why S would have a value of 0? 
> > I then don't need to take care of the S'es because the result will always be 0. Not that it changes a lot 
> > 
> > Because I still couldn't choose the profile of interest only based on the numbers. 
> > 
> > R R S S I Ben's Solution: 212 11 Mine: 212 1205 
> > R S R S I 212 13 212 1405 
> > R R R S I 311 17 311 1805 
> > R S R R I 311 21 311 1407 
> > S R R R I 311 23 311 1607 
> > 
> > 311 17 and 311 23 being the most likely but unclear where the 
> > difference might be.
> This is what is so frustrating for me. What do you mean, most likely? 
> What do you mean be what the difference might be? Can you describe to 
> me, as a human being, which you would choose and tell me how you 
> decided. If you can't do that then all you are doing is trying random 
> schemes until something pops up the look right for some specific set of 
> data!

311 17 and 311 23 

Have the most a R's and are most different from each other. 311 23 has an R
in a position which 311 17 doesn't have.

311 21 gets deleted because it has nothing unique which the 2 others wouldn't have.
 212 11 and 212 13 are also deleted. Less R's, nothing unique.

> > I have adapted my current solution to include the number of R,I,S 
> > weight of the results: S=1, I=2, R=3 
> > weight of the position in the triplet: 1st=1, 2nd=2, 3rd=3 
> > 
> > ie.: R R R => First triplet: 1*3+2*3+3*3 = 18 
> > S I => Second triplet 1*1+2*2 = 05 
> > 
> > RIS count: 311 
> > Append 1st triplet: 311 18 
> > Append 2nd triplet: 311 18 05 
> > 
> > 311 18 05 and 311 16 07 being the most likely with some clues which 
> > triplet is different.
> It sound like you want the result to "give some clues". Why not just 
> return the string of letters? SRRRI tells you everything about the 
> tests. What more could you want? If you want these ordered by number 
> R, I and S counts, put these first always using two digits: 
> 
> "030101SRRRI" 
> 
> This string will sort the important, highly resistant strains to the top 
> and also gives all the information about the individual tests.
> > Am I not somehow introducing a bias by multiplying the value with the position in the triplet? 
> > And then there is still the case where SSR (1*1+2*1+3*3=12) and RRS (1*3+2*3+3*1=12) 
> > will both resolve to the same value.
> Eh? Don't you want more Rs to get high scores? That's what the counts 
> are for.

No more R's are not always better. If I have a strain with a R in a place all the other's don't
have I have to keep that one too even with a low score.

Because of that stupid requirement:

> >> > The requirements are one strain of a certain microorganism/patient 
> >> > The most resistant one or if they have different profiles 
> >> > 
> >> > SRS vs RRS => last one, more Rs 
> >> > 
> >> > SRS vs RSR = both, different profiles

The extraction tool I use is doing the stats
for me. Just the "client" Dr Dr something wants it like that. So I have
to do that post-treatment which cost me a lot of time.

Here the Excel file with my play data:
https://github.com/Chutulu/Bacterio-Statistiques.git

The strains with their IDs in orange are the ones I keep.

strain b has the same number of Rs but in different places aka different drugs so different
behaviour if you prescribe antibiotics.

strain d I keep the one with empty cells because it has an R the other one doesn't have

Most R's and/or uniqueness

If a docter knows the name of the microorganism but doesn't have the 
results of antimicrobial susceptibility testing then he can use the data
I have provided to give an empirical treatment.

So I have to consider all unique strains but be very pessimistic about their
sensibility (most R's)

Dr Dr client's idea to print them as plastic pocket table.
Fortunately my name isn't mentioned anywhere.

> > Wouldn't I need some sort of Traveling Salesman Problems algorithm to find the profile 
> > with the highest number of resistances and the highest number of 
> > triplets with high values.
> I don't understand the triplets idea. Sorry. 

No problem. I have used some application specific idea while creating it. 

I think it might be usable. 
Have just to figure out how.

If you want to leave the discussion I can understand. Sometimes it helps me
speaking with someone who has no clue what I am talking about to see
things clearer.

My maths skills are very low. The reason why I am an MTA. No math required.
The most difficult I have to calculate are dilutions. So one reason for
miscommunication comes from there.

The other being the application specific context of microbiologie.
I am doing that for 18 years.

Starring to long at at the results has probably also fried too many
neurones.

> 
> -- 
> Ben.

Thanks for your time and patience

Laurent

^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28  6:02       ` Laurent
@ 2021-12-29  3:58         ` Randy Brukardt
  0 siblings, 0 replies; 25+ messages in thread
From: Randy Brukardt @ 2021-12-29  3:58 UTC (permalink / raw)


"Laurent" <lutgenl@icloud.com> wrote in message 
news:b50bf401-87e7-4352-b517-7fe6b6ded42dn@googlegroups.com...
...
> The problem is not that I don't want to use Ada. We are using Citrix so I 
> am stuck with the programs
> the IT departments allows me to use. Was already a chore to get MS Access 
> made available.

Understood. But this is an Ada group, and we're not very motivated to talk 
about stuff that can't be written in Ada. Besides, if you had an Ada 
solution, you probably could figure out how to transcribe it into some other 
language.

                                   Randy.


^ permalink raw reply	[flat|nested] 25+ messages in thread

* Re: Some advice required [OT]
  2021-12-28 16:49                 ` Dennis Lee Bieber
@ 2021-12-29  4:20                   ` Randy Brukardt
  0 siblings, 0 replies; 25+ messages in thread
From: Randy Brukardt @ 2021-12-29  4:20 UTC (permalink / raw)


"Dennis Lee Bieber" <wlfraed@ix.netcom.com> wrote in message 
news:fefmsgl4cpearv10nkdm07kirrn5gete3e@4ax.com...
> On Mon, 27 Dec 2021 23:48:31 -0800 (PST), Laurent <lutgenl@icloud.com>
> declaimed the following:
>
>>
>>The requirements are one strain of a certain microorganism/patient
>>The most resistant one or if they have different profiles
>>
>>SRS vs RRS => last one, more Rs
>>
>>SRS vs RSR = both, different profiles
>>
>
> Which is still inconclusive (at least as I view it) -- your second
> example ALSO fits the "last one, more Rs" constraint. You haven't to 
> define
> how the first doesn't qualify as "different profiles". Both examples are
> "1R, 2S" vs "2R, 1S".

Let me try. I think he is saying that when one compares two profiles, one 
compares each position with the relation I < S < R. Then, if you get the 
same order for every position (either >= or <=), then (and only then), the 
profile with more R's is the one you keep (or more S's if there are the same 
number of R's). If you don't get the same order for each, then you keep 
both.

So, for any pair of profiles, you can get a result of "<", ">", or 
incomparable. It should be easy enough to write a function to determine this 
result.

The problem I see is that I don't think there is any way to do this across 
all of the data short of comparing all of the pairs The compare function 
needs to be a "strict weak ordering" in order that sorting and the like be 
meaningful between data sets. The issue here is that "incomparable" gets in 
the way of having A > C being true for any B such that A > B and B > C.

OTOH, I wouldn't worry about that unless the data set is large. Computers 
are fast these days, and brute force approaches are much easier to figure 
out.

So I suggest the OP write a function to compare two sets of data; if the 
result is that the sets are comparable, eliminate the set that is less 
interesting. Then apply that function to every pair in the data until there 
are no further eliminations. (Probably one would "eliminate" a set by 
marking that it is less interesting that some other set, as opposed to 
deleting it outright. I'd probably just use a spreadsheet cell pointing to 
the more interesting set.)

If it was me, I'd do that in Ada first, to get the algorithm right. Then 
translate it into whatever other languages (maybe even spreadsheet 
formulas).

                               Randy.


^ permalink raw reply	[flat|nested] 25+ messages in thread

end of thread, other threads:[~2021-12-29  4:20 UTC | newest]

Thread overview: 25+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2021-12-27  9:21 Some advice required [OT] Laurent
2021-12-27 11:16 ` Niklas Holsti
2021-12-27 12:29   ` Laurent
2021-12-27 13:14     ` Ben Bacarisse
2021-12-27 18:24       ` Laurent
2021-12-27 19:51         ` Dennis Lee Bieber
2021-12-27 20:49         ` Ben Bacarisse
2021-12-27 22:09           ` Laurent
2021-12-28  0:29             ` Ben Bacarisse
2021-12-28  7:48               ` Laurent
2021-12-28  9:05                 ` Laurent
2021-12-28 12:54                   ` Laurent
2021-12-28 13:57                     ` Ben Bacarisse
2021-12-28 18:19                       ` Laurent
2021-12-28 13:43                 ` Ben Bacarisse
2021-12-28 16:49                 ` Dennis Lee Bieber
2021-12-29  4:20                   ` Randy Brukardt
2021-12-27 17:41     ` Dennis Lee Bieber
2021-12-27 18:56       ` Niklas Holsti
2021-12-27 19:44         ` Laurent
2021-12-28  2:10     ` Randy Brukardt
2021-12-28  6:02       ` Laurent
2021-12-29  3:58         ` Randy Brukardt
2021-12-27 17:18 ` Simon Wright
2021-12-27 18:30   ` Laurent

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox