Some advice required [OT]

[Laurent]

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

[Niklas Holsti]

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.

[Laurent]

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

[Ben Bacarisse]

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?

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.

[Simon Wright]

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!

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?

[Laurent]

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

[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

[Niklas Holsti]

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.

[Laurent]

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.

[Ben Bacarisse]

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

[Laurent]

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

[Ben Bacarisse]

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.

[Randy Brukardt]

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

[Laurent]

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

[Laurent]

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

[Laurent]

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

[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

[Ben Bacarisse]

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.

[Ben Bacarisse]

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.

[Laurent]

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

[Randy Brukardt]

"Laurent" <lut...@icloud.com> wrote in message

news:b50bf401-87e7-4352...@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.

[Randy Brukardt]

"Dennis Lee Bieber" <wlf...@ix.netcom.com> wrote in message
news:fefmsgl4cpearv10n...@4ax.com...
> On Mon, 27 Dec 2021 23:48:31 -0800 (PST), Laurent <lut...@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.