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

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.

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

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.

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?

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/

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

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

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.

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.

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/

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.

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

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.

"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.

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

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.

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?
>

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

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.

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,

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/

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!

"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.

"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.