* Some advice required [OT] @ 2021-12-27 9:21 Laurent 2021-12-27 11:16 ` Niklas Holsti 2021-12-27 17:18 ` Simon Wright 0 siblings, 2 replies; 25+ messages in thread From: Laurent @ 2021-12-27 9:21 UTC (permalink / raw) Hi all My problem is not directly related to Ada but on how to solve it in general. Also writing via the web interface of google groups :( I have to do statistics on the results of antimicrobial susceptibility testings. I have to keep only one strain/patient and the most resistant one. Until now I have been doing it manually by staring for hours at Excel sheets. I am trying to get it automated but I don't know how to solve my problem. I have tried calculating a checksum from the results but I have cases which are unclear/collide. The result for a strain is one row, the results are in columns. I treat the results in blocks of 3. S has a value of 1, I =2 and R=3, empty cells = 0 without weight SRS (1+3+1) and RSS (3+1+1) or SSR (1+1+3) give both 5 I would have to keep the 3 because they are different. I thought that weighting the position would solve the collisions but nope. The first cell has a value of 1, 2nd of 2 and 3rd of 3. S has a value of 1, I =2 and R=3, empty cells = 0 with weight RRS (1*3+2*3+3*1) and SSR (1*1 + 2*1+3*3) give both 12 Is there a better way doing this? What I have so far as VBA code: Public Function Test_Checksum(rng_Range As Range) As String Dim rng_Cell As Range Dim int_Counter As Integer Dim str_Result As String Dim i As Long int_Counter = 1 For Each rng_Cell In rng_Range If rng_Cell.Value = "S" Then i = i + 1 * int_Counter ElseIf rng_Cell.Value = "I" Then i = i + 2 * int_Counter ElseIf rng_Cell.Value = "R" Then i = i + 3 * int_Counter Else --empty cell i = i + 0 End If If int_Counter = 3 Then int_Counter = 0 If i < 9 Then str_Result = str_Result & "0" & CStr(i) Else str_Result = str_Result & CStr(i) End If i = 0 End If int_Counter = int_Counter + 1 Next rng_Cell --if the loop terminates but i <> 3 If i < 9 Then str_Result = str_Result & "0" & CStr(i) Else str_Result = str_Result & CStr(i) End If Test_Checksum = str_Result End Function Thank you very much Kind regards Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 9:21 Some advice required [OT] Laurent @ 2021-12-27 11:16 ` Niklas Holsti 2021-12-27 12:29 ` Laurent 2021-12-27 17:18 ` Simon Wright 1 sibling, 1 reply; 25+ messages in thread From: Niklas Holsti @ 2021-12-27 11:16 UTC (permalink / raw) On 2021-12-27 11:21, Laurent wrote: > Hi all > > My problem is not directly related to Ada but on how to solve it in general. > > Also writing via the web interface of google groups :( > > I have to do statistics on the results of antimicrobial susceptibility testings. > I have to keep only one strain/patient and the most resistant one. > Until now I have been doing it manually by staring for hours at Excel sheets. > I am trying to get it automated but I don't know how to solve my problem. [ problem description snipped ] Sorry, but I found your problem description impossible to understand. Try to describe more clearly the experiment that is done, the structure of the data the experiment provides (the meaning of the Excel rows and columns), and the statistic you want to compute. Also, if you do not intend to implement the solution in Ada, this is not the right group to discuss it. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 11:16 ` Niklas Holsti @ 2021-12-27 12:29 ` Laurent 2021-12-27 13:14 ` Ben Bacarisse ` (2 more replies) 0 siblings, 3 replies; 25+ messages in thread From: Laurent @ 2021-12-27 12:29 UTC (permalink / raw) On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > Sorry, but I found your problem description impossible to understand. > Try to describe more clearly the experiment that is done, the structure > of the data the experiment provides (the meaning of the Excel rows and > columns), and the statistic you want to compute. Sorry tried to keep it short, was too short. Columns are the antimicrobial drugs Rows are the microorganism. So every cell contains a result of S, I, R or simply an empty cell S = Sensible I = Intermediate R = Resistant empty cell <S<I<R If a patient has 3 strains of the same microorganism but with different resistance profiles I have to find the most resistant one. Or if they are different I keep them all. I have no idea how to explain what I am doing to the compiler. Why I would choose result from strain B over the result from strain A. strain A: SSSRSS strain B: SSRRRS Simply counting the number of S, I and R doesn't work. Checksum with/without weight for the column number doesn't work either. Even if I get a correct result I have still the same problem as before why result B over result A. Thought about building a tree for every family of drugs but the problem will again be the same. How to decide which result to choose. Would be easier to attach the Excel file directly. > Also, if you do not intend to implement the solution in Ada, this is not > the right group to discuss it. I would very much prefer to solve it in Ada but at work I am stuck with Excel and VBA which is better than doing it manually. After a few hours starring at a screen with thousand of rows of results... If I get an Ada solution I can adapt it. Just limited to no access/pointers in VBA which shouldn't be required? I know this is the wrong group to discuss, unfortunately I don't know of any place where I would get usable advice. Here at least I know that there are no trolls or whatever. Perhaps some miscommunication because I thought my explanation was clear but it wasn't because of missing context. I am lurking in this group for some time. Just gave up on using Ada Thanks Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 12:29 ` Laurent @ 2021-12-27 13:14 ` Ben Bacarisse 2021-12-27 18:24 ` Laurent 2021-12-27 17:41 ` Dennis Lee Bieber 2021-12-28 2:10 ` Randy Brukardt 2 siblings, 1 reply; 25+ messages in thread From: Ben Bacarisse @ 2021-12-27 13:14 UTC (permalink / raw) Laurent <lutgenl@icloud.com> writes: > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > >> Sorry, but I found your problem description impossible to understand. >> Try to describe more clearly the experiment that is done, the structure >> of the data the experiment provides (the meaning of the Excel rows and >> columns), and the statistic you want to compute. > > Sorry tried to keep it short, was too short. > > Columns are the antimicrobial drugs > Rows are the microorganism. > > So every cell contains a result of S, I, R or simply an empty cell > > S = Sensible > I = Intermediate > R = Resistant > > empty cell <S<I<R > > If a patient has 3 strains of the same microorganism but with > different resistance profiles I have to find the most resistant > one. Or if they are different I keep them all. > > I have no idea how to explain what I am doing to the compiler. I think when you can explain it to people, you'll be able to code it. I am still struggling to understand what you need. > Why I would choose result from strain B over the result from strain A. > > strain A: SSSRSS > strain B: SSRRRS Let's space it out drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 strain A S S S R S S strain B S S R R R S You want to choose B because it has is resistant to more drugs, yes? I think, from the ordering you give, you need a measure that treats an R as "more important" that any "I" which is "more important" than an "S". (We will come to empty cells later.) I think you need to treat the number of Rs, Is and Ss like digits in a number. In base 10, the strains score R S I strain A 1 5 0 = 150 strain B 3 3 0 = 330 Now, in fact, you don't need to use base 10. The smallest base you can use is one more than the maximum number of test results. If there can be up to 16 tests (say) the score is n(R)*17*17 + n(S)*17 + n(I). If this suits your needs, we can consider empty cells later on. It's not at all clear to me how to compare strain C R____ strain D RRSSSS Strain C is "less resistant" but only because there is not enough information. In fact it seems more serious as it is resistant to all tested drugs. And then what about strain D SR strain E RS Do you need to weight the drugs to break ties? I.e. is drug x more important than drug y if x < y? -- Ben. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 13:14 ` Ben Bacarisse @ 2021-12-27 18:24 ` Laurent 2021-12-27 19:51 ` Dennis Lee Bieber 2021-12-27 20:49 ` Ben Bacarisse 0 siblings, 2 replies; 25+ messages in thread From: Laurent @ 2021-12-27 18:24 UTC (permalink / raw) On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: > Laurent <lut...@icloud.com> writes: > > > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > > > >> Sorry, but I found your problem description impossible to understand. > >> Try to describe more clearly the experiment that is done, the structure > >> of the data the experiment provides (the meaning of the Excel rows and > >> columns), and the statistic you want to compute. > > > > Sorry tried to keep it short, was too short. > > > > Columns are the antimicrobial drugs > > Rows are the microorganism. > > > > So every cell contains a result of S, I, R or simply an empty cell > > > > S = Sensible > > I = Intermediate > > R = Resistant > > > > empty cell <S<I<R > > > > If a patient has 3 strains of the same microorganism but with > > different resistance profiles I have to find the most resistant > > one. Or if they are different I keep them all. > > > > I have no idea how to explain what I am doing to the compiler. > I think when you can explain it to people, you'll be able to code it. I > am still struggling to understand what you need. > > Why I would choose result from strain B over the result from strain A. > > > > strain A: SSSRSS > > strain B: SSRRRS > Let's space it out > > drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 > strain A S S S R S S > strain B S S R R R S > > You want to choose B because it has is resistant to more drugs, yes? > Yes indeed > I think, from the ordering you give, you need a measure that treats an R > as "more important" that any "I" which is "more important" than an "S". > (We will come to empty cells later.) > > I think you need to treat the number of Rs, Is and Ss like digits in a > number. In base 10, the strains score > > R S I > strain A 1 5 0 = 150 > strain B 3 3 0 = 330 > > Now, in fact, you don't need to use base 10. The smallest base you can > use is one more than the maximum number of test results. If there can > be up to 16 tests (say) the score is > > n(R)*17*17 + n(S)*17 + n(I). > > If this suits your needs, we can consider empty cells later on. It's > not at all clear to me how to compare > > strain C R____ > strain D RRSSSS > > Strain C is "less resistant" but only because there is not enough > information. In fact it seems more serious as it is resistant to all > tested drugs. > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > And then what about > > strain D SR > strain E RS > Yes those are the cases which are annoying me. That's why I came up withe idea of multiplying the value of the result (S=1, I=2 and R=3) with the position of the value. Tried it with triplets but there will still be cases where different results will give the same numeric value. Ignoring empty cells for the moment. Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 will be the same numerical value but they are different resistance profiles I would in this case keep both. How to prevent that from happening. The results are way longer than only 3 values so the possibilities for collisions are higher. R R R R R S R R R S S S R S S => numeric:1812180608 R R R R R S R R R R S S S S S => numeric:1812180806 I have to keep both and that was an easy one. Only 2 to compare not 5. A lot of R in common, only 2 differences. Ok now I have the same result in an other representation. Still no idea how to explain to the compiler why they are different. > Do you need to weight the drugs to break ties? I.e. is drug x more > important than drug y if x < y? > > -- > Ben. Yes there is a hierarchy in the drugs but that information is not available in the exported results I work with. I know that because it is part of my formation as medical technical assistant in a lab. I was hoping to not have to recreate some form of expert system. Thanks Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 18:24 ` Laurent @ 2021-12-27 19:51 ` Dennis Lee Bieber 2021-12-27 20:49 ` Ben Bacarisse 1 sibling, 0 replies; 25+ messages in thread From: Dennis Lee Bieber @ 2021-12-27 19:51 UTC (permalink / raw) On Mon, 27 Dec 2021 10:24:21 -0800 (PST), Laurent <lutgenl@icloud.com> declaimed the following: > >Yes those are the cases which are annoying me. > >That's why I came up withe idea of multiplying the value of the result (S=1, I=2 and R=3) with the position of the value. >Tried it with triplets but there will still be cases where different results will give the same numeric value. >Ignoring empty cells for the moment. > Multiplying by column position inherently gives priority to the column with the highest position. If the columns are, of themselves, not significant, your algorithm needs to ignore column (reordering the columns should not make a change in final selection). You could just about as easily do a multi-column sort giving priority to the most significant column. >Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 will be the same numerical value but they are different resistance profiles >I would in this case keep both. So far as I can make out -- ANY collision qualifies as "different resistance profiles". In that example, the count of Ss vs the count of Rs differ, but... >The results are way longer than only 3 values so the possibilities for collisions are higher. > >R R R R R S R R R S S S R S S => numeric:1812180608 >R R R R R S R R R R S S S S S => numeric:1812180806 > >I have to keep both and that was an easy one. Only 2 to compare not 5. > In this example the count of Ss and count of Rs is the same between the two. And, again, you've applied an arbitrary ranking of the columns (changing the order of the columns will tend to produce wildly different sums). >Yes there is a hierarchy in the drugs but that information is not available in the exported results I work with. In that situation I would complain to the provider that the exported data is incompletely defined. At the very least, the columns should be in ascending (or descending) order of significance, justifying use of column position as a weight (even better would be to have a row of the data containing the weight to be used for a given column, which makes the column order irrelevant). -- Wulfraed Dennis Lee Bieber AF6VN wlfraed@ix.netcom.com http://wlfraed.microdiversity.freeddns.org/ ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 18:24 ` Laurent 2021-12-27 19:51 ` Dennis Lee Bieber @ 2021-12-27 20:49 ` Ben Bacarisse 2021-12-27 22:09 ` Laurent 1 sibling, 1 reply; 25+ messages in thread From: Ben Bacarisse @ 2021-12-27 20:49 UTC (permalink / raw) Laurent <lutgenl@icloud.com> writes: > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: >> Laurent <lut...@icloud.com> writes: >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: >> > >> >> Sorry, but I found your problem description impossible to understand. >> >> Try to describe more clearly the experiment that is done, the structure >> >> of the data the experiment provides (the meaning of the Excel rows and >> >> columns), and the statistic you want to compute. >> > >> > Sorry tried to keep it short, was too short. >> > >> > Columns are the antimicrobial drugs >> > Rows are the microorganism. >> > >> > So every cell contains a result of S, I, R or simply an empty cell >> > >> > S = Sensible >> > I = Intermediate >> > R = Resistant >> > >> > empty cell <S<I<R >> > >> > If a patient has 3 strains of the same microorganism but with >> > different resistance profiles I have to find the most resistant >> > one. Or if they are different I keep them all. >> > >> > I have no idea how to explain what I am doing to the compiler. >> I think when you can explain it to people, you'll be able to code it. I >> am still struggling to understand what you need. >> > Why I would choose result from strain B over the result from strain A. >> > >> > strain A: SSSRSS >> > strain B: SSRRRS >> Let's space it out >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 >> strain A S S S R S S >> strain B S S R R R S >> >> You want to choose B because it has is resistant to more drugs, yes? >> > > Yes indeed > >> I think, from the ordering you give, you need a measure that treats an R >> as "more important" that any "I" which is "more important" than an "S". >> (We will come to empty cells later.) >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> number. In base 10, the strains score >> >> R S I >> strain A 1 5 0 = 150 >> strain B 3 3 0 = 330 >> >> Now, in fact, you don't need to use base 10. The smallest base you can >> use is one more than the maximum number of test results. If there can >> be up to 16 tests (say) the score is >> >> n(R)*17*17 + n(S)*17 + n(I). >> >> If this suits your needs, we can consider empty cells later on. It's >> not at all clear to me how to compare >> >> strain C R____ >> strain D RRSSSS >> >> Strain C is "less resistant" but only because there is not enough >> information. In fact it seems more serious as it is resistant to all >> tested drugs. >> > > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > >> And then what about >> >> strain D SR >> strain E RS >> > > Yes those are the cases which are annoying me. > > That's why I came up withe idea of multiplying the value of the result > (S=1, I=2 and R=3) with the position of the value. Tried it with > triplets but there will still be cases where different results will > give the same numeric value. Ignoring empty cell able tps for the moment. > > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 > will be the same numerical value but they are different resistance > profiles I would in this case keep both. > > How to prevent that from happening. Can you first say why the suggestion I made is not helpful? -- Ben. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 20:49 ` Ben Bacarisse @ 2021-12-27 22:09 ` Laurent 2021-12-28 0:29 ` Ben Bacarisse 0 siblings, 1 reply; 25+ messages in thread From: Laurent @ 2021-12-27 22:09 UTC (permalink / raw) On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: > Laurent <lut...@icloud.com> writes: > > > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: > >> Laurent <lut...@icloud.com> writes: > >> > >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > >> > > >> >> Sorry, but I found your problem description impossible to understand. > >> >> Try to describe more clearly the experiment that is done, the structure > >> >> of the data the experiment provides (the meaning of the Excel rows and > >> >> columns), and the statistic you want to compute. > >> > > >> > Sorry tried to keep it short, was too short. > >> > > >> > Columns are the antimicrobial drugs > >> > Rows are the microorganism. > >> > > >> > So every cell contains a result of S, I, R or simply an empty cell > >> > > >> > S = Sensible > >> > I = Intermediate > >> > R = Resistant > >> > > >> > empty cell <S<I<R > >> > > >> > If a patient has 3 strains of the same microorganism but with > >> > different resistance profiles I have to find the most resistant > >> > one. Or if they are different I keep them all. > >> > > >> > I have no idea how to explain what I am doing to the compiler. > >> I think when you can explain it to people, you'll be able to code it. I > >> am still struggling to understand what you need. > >> > Why I would choose result from strain B over the result from strain A. > >> > > >> > strain A: SSSRSS > >> > strain B: SSRRRS > >> Let's space it out > >> > >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 > >> strain A S S S R S S > >> strain B S S R R R S > >> > >> You want to choose B because it has is resistant to more drugs, yes? > >> > > > > Yes indeed > > > >> I think, from the ordering you give, you need a measure that treats an R > >> as "more important" that any "I" which is "more important" than an "S". > >> (We will come to empty cells later.) > >> > >> I think you need to treat the number of Rs, Is and Ss like digits in a > >> number. In base 10, the strains score > >> > >> R S I > >> strain A 1 5 0 = 150 > >> strain B 3 3 0 = 330 > >> > >> Now, in fact, you don't need to use base 10. The smallest base you can > >> use is one more than the maximum number of test results. If there can > >> be up to 16 tests (say) the score is > >> > >> n(R)*17*17 + n(S)*17 + n(I). > >> > >> If this suits your needs, we can consider empty cells later on. It's > >> not at all clear to me how to compare > >> > >> strain C R____ > >> strain D RRSSSS > >> > >> Strain C is "less resistant" but only because there is not enough > >> information. In fact it seems more serious as it is resistant to all > >> tested drugs. > >> > > > > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > > > >> And then what about > >> > >> strain D SR > >> strain E RS > >> > > > > Yes those are the cases which are annoying me. > > > > That's why I came up withe idea of multiplying the value of the result > > (S=1, I=2 and R=3) with the position of the value. Tried it with > > triplets but there will still be cases where different results will > > give the same numeric value. Ignoring empty cell able tps for the moment. > > > > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 > > will be the same numerical value but they are different resistance > > profiles I would in this case keep both. > > > > How to prevent that from happening. > Can you first say why the suggestion I made is not helpful? > > -- > Ben. You mean that one: > >> I think you need to treat the number of Rs, Is and Ss like digits in a > >> number. In base 10, the strains score > >> > >> R S I > >> strain A 1 5 0 = 150 > >> strain B 3 3 0 = 330 > >> Different resistance profiles same result: S S S S S S R S S S S S S S S score=1 14 0 S S S S S S S S S S S S R S S score=1 14 0 R R R R R S R R R S S S S S S score = 8 7 0 R R R R S S R R R S S S R S S score = 8 7 0 I found 6 of those cases in 69 possible duplicates. > >> Now, in fact, you don't need to use base 10. The smallest base you can > >> use is one more than the maximum number of test results. If there can > >> be up to 16 tests (say) the score is > >> > >> n(R)*17*17 + n(S)*17 + n(I). > >> The maximum is probably 20. More drugs don't fit onto one antimicrobial susceptibility test (AST) card. Just scoring the numbers together doesn't work always because of those cases as you said yourself: > >> And then what about > >> strain D SR > >> strain E RS So I jumped to the conclusion that I need to add a weight for the position. That's the solution I have figured out myself so far. But it suffers from the same problem perhaps less often. In the data I am testing I have 264 rows with results but only 69 are possible duplicates. None of those produced a collision. So I have no idea how common that problem actually is. Have to check that when I am back at work tomorrow. Thanks Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 22:09 ` Laurent @ 2021-12-28 0:29 ` Ben Bacarisse 2021-12-28 7:48 ` Laurent 0 siblings, 1 reply; 25+ messages in thread From: Ben Bacarisse @ 2021-12-28 0:29 UTC (permalink / raw) Laurent <lutgenl@icloud.com> writes: > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: >> Laurent <lut...@icloud.com> writes: >> >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: >> >> Laurent <lut...@icloud.com> writes: >> >> >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: >> >> > >> >> >> Sorry, but I found your problem description impossible to understand. >> >> >> Try to describe more clearly the experiment that is done, the structure >> >> >> of the data the experiment provides (the meaning of the Excel rows and >> >> >> columns), and the statistic you want to compute. >> >> > >> >> > Sorry tried to keep it short, was too short. >> >> > >> >> > Columns are the antimicrobial drugs >> >> > Rows are the microorganism. >> >> > >> >> > So every cell contains a result of S, I, R or simply an empty cell >> >> > >> >> > S = Sensible >> >> > I = Intermediate >> >> > R = Resistant >> >> > >> >> > empty cell <S<I<R >> >> > >> >> > If a patient has 3 strains of the same microorganism but with >> >> > different resistance profiles I have to find the most resistant >> >> > one. Or if they are different I keep them all. >> >> > >> >> > I have no idea how to explain what I am doing to the compiler. >> >> I think when you can explain it to people, you'll be able to code it. I >> >> am still struggling to understand what you need. >> >> > Why I would choose result from strain B over the result from strain A. >> >> > >> >> > strain A: SSSRSS >> >> > strain B: SSRRRS >> >> Let's space it out >> >> >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 >> >> strain A S S S R S S >> >> strain B S S R R R S >> >> >> >> You want to choose B because it has is resistant to more drugs, yes? >> >> >> > >> > Yes indeed >> > >> >> I think, from the ordering you give, you need a measure that treats an R >> >> as "more important" that any "I" which is "more important" than an "S". >> >> (We will come to empty cells later.) >> >> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> >> number. In base 10, the strains score >> >> >> >> R S I >> >> strain A 1 5 0 = 150 >> >> strain B 3 3 0 = 330 >> >> >> >> Now, in fact, you don't need to use base 10. The smallest base you can >> >> use is one more than the maximum number of test results. If there can >> >> be up to 16 tests (say) the score is >> >> >> >> n(R)*17*17 + n(S)*17 + n(I). >> >> >> >> If this suits your needs, we can consider empty cells later on. It's >> >> not at all clear to me how to compare >> >> >> >> strain C R____ >> >> strain D RRSSSS >> >> >> >> Strain C is "less resistant" but only because there is not enough >> >> information. In fact it seems more serious as it is resistant to all >> >> tested drugs. >> >> >> > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. >> > >> >> And then what about >> >> >> >> strain D SR >> >> strain E RS >> >> >> > >> > Yes those are the cases which are annoying me. >> > >> > That's why I came up withe idea of multiplying the value of the result >> > (S=1, I=2 and R=3) with the position of the value. Tried it with >> > triplets but there will still be cases where different results will >> > give the same numeric value. Ignoring empty cell able tps for the moment. >> > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 >> > will be the same numerical value but they are different resistance >> > profiles I would in this case keep both. >> > >> > How to prevent that from happening. >> Can you first say why the suggestion I made is not helpful? >> >> -- >> Ben. > > You mean that one: > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> >> number. In base 10, the strains score >> >> >> >> R S I >> >> strain A 1 5 0 = 150 >> >> strain B 3 3 0 = 330 >> >> > > Different resistance profiles same result: I don't yet understand the requirements so I am taking it in stages. The first requirement seemed to be "more or less resistant". To do that you can use digits in a large enough base but this will make the number of Rs, Ss and Is paramount. Is that acceptable as a first step? In order to help people to be able to make further suggestions, maybe you could give the relative ordering you would like to see between the following sets of profiles. For example, between SSR, SRS and RSS, I think the order you want is RSS > SRS > SSR. 1: SSR, SRS, RSS 2: RSI, RIS, SRI, SIR, IRS, ISR 3: SSSR, SSRS, SRSS, RSSS 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR It's possible you could make do with an extra field (or digits) that gives some measure of the relative ordering between otherwise similar sequences. For example, using base 10 (for convenience of arithmetic) both RRSSI and RSRSI would score 212xx but the last xx would reflect the positioning of the results in the sequence. There are lots of way to do this. One way would be use, as you were thinking, some sort of weighted count. Using S=0, I=1 and R=2 with weights 54321 RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 If you absolutely must never get duplicate numbers, but you still want to preserve a strict specified ordering, I think you will have much more work to do. Getting a unique number for each case it trivial (but the ordering will be wrong) and getting an ordering that rates every R > every S > every I is also trivial, but there will be lots of duplicates. It's finding the balance that's going to be hard. -- Ben. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 0:29 ` Ben Bacarisse @ 2021-12-28 7:48 ` Laurent 2021-12-28 9:05 ` Laurent ` (2 more replies) 0 siblings, 3 replies; 25+ messages in thread From: Laurent @ 2021-12-28 7:48 UTC (permalink / raw) On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: > Laurent <lut...@icloud.com> writes: > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: > >> Laurent <lut...@icloud.com> writes: > >> > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: > >> >> Laurent <lut...@icloud.com> writes: > >> >> > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > >> >> > > >> >> >> Sorry, but I found your problem description impossible to understand. > >> >> >> Try to describe more clearly the experiment that is done, the structure > >> >> >> of the data the experiment provides (the meaning of the Excel rows and > >> >> >> columns), and the statistic you want to compute. > >> >> > > >> >> > Sorry tried to keep it short, was too short. > >> >> > > >> >> > Columns are the antimicrobial drugs > >> >> > Rows are the microorganism. > >> >> > > >> >> > So every cell contains a result of S, I, R or simply an empty cell > >> >> > > >> >> > S = Sensible > >> >> > I = Intermediate > >> >> > R = Resistant > >> >> > > >> >> > empty cell <S<I<R > >> >> > > >> >> > If a patient has 3 strains of the same microorganism but with > >> >> > different resistance profiles I have to find the most resistant > >> >> > one. Or if they are different I keep them all. > >> >> > > >> >> > I have no idea how to explain what I am doing to the compiler. > >> >> I think when you can explain it to people, you'll be able to code it. I > >> >> am still struggling to understand what you need. > >> >> > Why I would choose result from strain B over the result from strain A. > >> >> > > >> >> > strain A: SSSRSS > >> >> > strain B: SSRRRS > >> >> Let's space it out > >> >> > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 > >> >> strain A S S S R S S > >> >> strain B S S R R R S > >> >> > >> >> You want to choose B because it has is resistant to more drugs, yes? > >> >> > >> > > >> > Yes indeed > >> > > >> >> I think, from the ordering you give, you need a measure that treats an R > >> >> as "more important" that any "I" which is "more important" than an "S". > >> >> (We will come to empty cells later.) > >> >> > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > >> >> number. In base 10, the strains score > >> >> > >> >> R S I > >> >> strain A 1 5 0 = 150 > >> >> strain B 3 3 0 = 330 > >> >> > >> >> Now, in fact, you don't need to use base 10. The smallest base you can > >> >> use is one more than the maximum number of test results. If there can > >> >> be up to 16 tests (say) the score is > >> >> > >> >> n(R)*17*17 + n(S)*17 + n(I). > >> >> > >> >> If this suits your needs, we can consider empty cells later on. It's > >> >> not at all clear to me how to compare > >> >> > >> >> strain C R____ > >> >> strain D RRSSSS > >> >> > >> >> Strain C is "less resistant" but only because there is not enough > >> >> information. In fact it seems more serious as it is resistant to all > >> >> tested drugs. > >> >> > >> > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > >> > > >> >> And then what about > >> >> > >> >> strain D SR > >> >> strain E RS > >> >> > >> > > >> > Yes those are the cases which are annoying me. > >> > > >> > That's why I came up withe idea of multiplying the value of the result > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with > >> > triplets but there will still be cases where different results will > >> > give the same numeric value. Ignoring empty cell able tps for the moment. > >> > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 > >> > will be the same numerical value but they are different resistance > >> > profiles I would in this case keep both. > >> > > >> > How to prevent that from happening. > >> Can you first say why the suggestion I made is not helpful? > >> > >> -- > >> Ben. > > > > You mean that one: > > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > >> >> number. In base 10, the strains score > >> >> > >> >> R S I > >> >> strain A 1 5 0 = 150 > >> >> strain B 3 3 0 = 330 > >> >> > > > > Different resistance profiles same result: > I don't yet understand the requirements so I am taking it in stages. > The first requirement seemed to be "more or less resistant". To do that > you can use digits in a large enough base but this will make the number > of Rs, Ss and Is paramount. Is that acceptable as a first step? > The requirements are one strain of a certain microorganism/patient The most resistant one or if they have different profiles SRS vs RRS => last one, more Rs SRS vs RSR = both, different profiles > In order to help people to be able to make further suggestions, maybe > you could give the relative ordering you would like to see between the > following sets of profiles. For example, between SSR, SRS and RSS, I > think the order you want is RSS > SRS > SSR. > > 1: SSR, SRS, RSS > > 2: RSI, RIS, SRI, SIR, IRS, ISR > > 3: SSSR, SSRS, SRSS, RSSS > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR > The order of the results is given by the ID of the drug in the extraction tool. I could probably order them by family and hierarchy of potence but would that make a difference? > It's possible you could make do with an extra field (or digits) that > gives some measure of the relative ordering between otherwise similar > sequences. For example, using base 10 (for convenience of arithmetic) > both RRSSI and RSRSI would score 212xx but the last xx would reflect the > positioning of the results in the sequence. There are lots of way to do > this. One way would be use, as you were thinking, some sort of weighted > count. Using S=0, I=1 and R=2 with weights > > 54321 > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 > So to be sure that I am following: 2*(5+4) = value of R (=2) * position of R(@5 and @4) 2*(5+3) = value of R (=2) * position of R(@5 and @3) 0*(3+2) = value of S (=0) * position of S(@3 and @2) 0*(4+2) = value of S (=0) * position of S(@4 and @2) 1*1 = value of I (=1) * position of I (@1) 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other number? But in this example I would have to keep both as drug 5,2 and 1 are common to both results but 4 and 3 are unique. The score would be completely misleading. So if my table has a width of 20 columns the first column would be 10^20, the next 10^19,.... +/- a few 0s off? I would have to implement it and see what I get as result. > If you absolutely must never get duplicate numbers, but you still want > to preserve a strict specified ordering, I think you will have much more > work to do. > > Getting a unique number for each case it trivial (but the ordering will > be wrong) and getting an ordering that rates every R > every S > every I > is also trivial, but there will be lots of duplicates. It's finding the > balance that's going to be hard. > > -- > Ben. I have prepared a cleaned up Excel workbook with only the duplicates which pose problems. The ones I would keep have an orange ID. I could upload it to Github. If that helps understanding the different cases. Thanks for your patience Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 7:48 ` Laurent @ 2021-12-28 9:05 ` Laurent 2021-12-28 12:54 ` Laurent 2021-12-28 13:43 ` Ben Bacarisse 2021-12-28 16:49 ` Dennis Lee Bieber 2 siblings, 1 reply; 25+ messages in thread From: Laurent @ 2021-12-28 9:05 UTC (permalink / raw) On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: > > Laurent <lut...@icloud.com> writes: > > > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: > > >> Laurent <lut...@icloud.com> writes: > > >> > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: > > >> >> Laurent <lut...@icloud.com> writes: > > >> >> > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > > >> >> > > > >> >> >> Sorry, but I found your problem description impossible to understand. > > >> >> >> Try to describe more clearly the experiment that is done, the structure > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and > > >> >> >> columns), and the statistic you want to compute. > > >> >> > > > >> >> > Sorry tried to keep it short, was too short. > > >> >> > > > >> >> > Columns are the antimicrobial drugs > > >> >> > Rows are the microorganism. > > >> >> > > > >> >> > So every cell contains a result of S, I, R or simply an empty cell > > >> >> > > > >> >> > S = Sensible > > >> >> > I = Intermediate > > >> >> > R = Resistant > > >> >> > > > >> >> > empty cell <S<I<R > > >> >> > > > >> >> > If a patient has 3 strains of the same microorganism but with > > >> >> > different resistance profiles I have to find the most resistant > > >> >> > one. Or if they are different I keep them all. > > >> >> > > > >> >> > I have no idea how to explain what I am doing to the compiler. > > >> >> I think when you can explain it to people, you'll be able to code it. I > > >> >> am still struggling to understand what you need. > > >> >> > Why I would choose result from strain B over the result from strain A. > > >> >> > > > >> >> > strain A: SSSRSS > > >> >> > strain B: SSRRRS > > >> >> Let's space it out > > >> >> > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 > > >> >> strain A S S S R S S > > >> >> strain B S S R R R S > > >> >> > > >> >> You want to choose B because it has is resistant to more drugs, yes? > > >> >> > > >> > > > >> > Yes indeed > > >> > > > >> >> I think, from the ordering you give, you need a measure that treats an R > > >> >> as "more important" that any "I" which is "more important" than an "S". > > >> >> (We will come to empty cells later.) > > >> >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > > >> >> number. In base 10, the strains score > > >> >> > > >> >> R S I > > >> >> strain A 1 5 0 = 150 > > >> >> strain B 3 3 0 = 330 > > >> >> > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can > > >> >> use is one more than the maximum number of test results. If there can > > >> >> be up to 16 tests (say) the score is > > >> >> > > >> >> n(R)*17*17 + n(S)*17 + n(I). > > >> >> > > >> >> If this suits your needs, we can consider empty cells later on. It's > > >> >> not at all clear to me how to compare > > >> >> > > >> >> strain C R____ > > >> >> strain D RRSSSS > > >> >> > > >> >> Strain C is "less resistant" but only because there is not enough > > >> >> information. In fact it seems more serious as it is resistant to all > > >> >> tested drugs. > > >> >> > > >> > > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > > >> > > > >> >> And then what about > > >> >> > > >> >> strain D SR > > >> >> strain E RS > > >> >> > > >> > > > >> > Yes those are the cases which are annoying me. > > >> > > > >> > That's why I came up withe idea of multiplying the value of the result > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with > > >> > triplets but there will still be cases where different results will > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. > > >> > > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 > > >> > will be the same numerical value but they are different resistance > > >> > profiles I would in this case keep both. > > >> > > > >> > How to prevent that from happening. > > >> Can you first say why the suggestion I made is not helpful? > > >> > > >> -- > > >> Ben. > > > > > > You mean that one: > > > > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > > >> >> number. In base 10, the strains score > > >> >> > > >> >> R S I > > >> >> strain A 1 5 0 = 150 > > >> >> strain B 3 3 0 = 330 > > >> >> > > > > > > Different resistance profiles same result: > > I don't yet understand the requirements so I am taking it in stages. > > The first requirement seemed to be "more or less resistant". To do that > > you can use digits in a large enough base but this will make the number > > of Rs, Ss and Is paramount. Is that acceptable as a first step? > > > The requirements are one strain of a certain microorganism/patient > The most resistant one or if they have different profiles > > SRS vs RRS => last one, more Rs > > SRS vs RSR = both, different profiles > > In order to help people to be able to make further suggestions, maybe > > you could give the relative ordering you would like to see between the > > following sets of profiles. For example, between SSR, SRS and RSS, I > > think the order you want is RSS > SRS > SSR. > > > > 1: SSR, SRS, RSS > > > > 2: RSI, RIS, SRI, SIR, IRS, ISR > > > > 3: SSSR, SSRS, SRSS, RSSS > > > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR > > > The order of the results is given by the ID of the drug in the extraction tool. > I could probably order them by family and hierarchy of potence but > would that make a difference? > > It's possible you could make do with an extra field (or digits) that > > gives some measure of the relative ordering between otherwise similar > > sequences. For example, using base 10 (for convenience of arithmetic) > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the > > positioning of the results in the sequence. There are lots of way to do > > this. One way would be use, as you were thinking, some sort of weighted > > count. Using S=0, I=1 and R=2 with weights > > > > 54321 > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 > > > So to be sure that I am following: > > 2*(5+4) = value of R (=2) * position of R(@5 and @4) > 2*(5+3) = value of R (=2) * position of R(@5 and @3) > > 0*(3+2) = value of S (=0) * position of S(@3 and @2) > 0*(4+2) = value of S (=0) * position of S(@4 and @2) > > 1*1 = value of I (=1) * position of I (@1) > > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other > number? > Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100 > But in this example I would have to keep both as drug 5,2 and 1 are common > to both results but 4 and 3 are unique. > > The score would be completely misleading. > > So if my table has a width of 20 columns the first column would be > 10^20, the next 10^19,.... +/- a few 0s off? > > I would have to implement it and see what I get as result. > > If you absolutely must never get duplicate numbers, but you still want > > to preserve a strict specified ordering, I think you will have much more > > work to do. > > > > Getting a unique number for each case it trivial (but the ordering will > > be wrong) and getting an ordering that rates every R > every S > every I > > is also trivial, but there will be lots of duplicates. It's finding the > > balance that's going to be hard. > > > > -- > > Ben. > I have prepared a cleaned up Excel workbook with only the duplicates which > pose problems. The ones I would keep have an orange ID. > I could upload it to Github. If that helps understanding the different cases. > > Thanks for your patience > > Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 9:05 ` Laurent @ 2021-12-28 12:54 ` Laurent 2021-12-28 13:57 ` Ben Bacarisse 0 siblings, 1 reply; 25+ messages in thread From: Laurent @ 2021-12-28 12:54 UTC (permalink / raw) On Tuesday, 28 December 2021 at 10:05:50 UTC+1, Laurent wrote: > On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: > > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: > > > Laurent <lut...@icloud.com> writes: > > > > > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: > > > >> Laurent <lut...@icloud.com> writes: > > > >> > > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: > > > >> >> Laurent <lut...@icloud.com> writes: > > > >> >> > > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > > > >> >> > > > > >> >> >> Sorry, but I found your problem description impossible to understand. > > > >> >> >> Try to describe more clearly the experiment that is done, the structure > > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and > > > >> >> >> columns), and the statistic you want to compute. > > > >> >> > > > > >> >> > Sorry tried to keep it short, was too short. > > > >> >> > > > > >> >> > Columns are the antimicrobial drugs > > > >> >> > Rows are the microorganism. > > > >> >> > > > > >> >> > So every cell contains a result of S, I, R or simply an empty cell > > > >> >> > > > > >> >> > S = Sensible > > > >> >> > I = Intermediate > > > >> >> > R = Resistant > > > >> >> > > > > >> >> > empty cell <S<I<R > > > >> >> > > > > >> >> > If a patient has 3 strains of the same microorganism but with > > > >> >> > different resistance profiles I have to find the most resistant > > > >> >> > one. Or if they are different I keep them all. > > > >> >> > > > > >> >> > I have no idea how to explain what I am doing to the compiler. > > > >> >> I think when you can explain it to people, you'll be able to code it. I > > > >> >> am still struggling to understand what you need. > > > >> >> > Why I would choose result from strain B over the result from strain A. > > > >> >> > > > > >> >> > strain A: SSSRSS > > > >> >> > strain B: SSRRRS > > > >> >> Let's space it out > > > >> >> > > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 > > > >> >> strain A S S S R S S > > > >> >> strain B S S R R R S > > > >> >> > > > >> >> You want to choose B because it has is resistant to more drugs, yes? > > > >> >> > > > >> > > > > >> > Yes indeed > > > >> > > > > >> >> I think, from the ordering you give, you need a measure that treats an R > > > >> >> as "more important" that any "I" which is "more important" than an "S". > > > >> >> (We will come to empty cells later.) > > > >> >> > > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > > > >> >> number. In base 10, the strains score > > > >> >> > > > >> >> R S I > > > >> >> strain A 1 5 0 = 150 > > > >> >> strain B 3 3 0 = 330 > > > >> >> > > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can > > > >> >> use is one more than the maximum number of test results. If there can > > > >> >> be up to 16 tests (say) the score is > > > >> >> > > > >> >> n(R)*17*17 + n(S)*17 + n(I). > > > >> >> > > > >> >> If this suits your needs, we can consider empty cells later on. It's > > > >> >> not at all clear to me how to compare > > > >> >> > > > >> >> strain C R____ > > > >> >> strain D RRSSSS > > > >> >> > > > >> >> Strain C is "less resistant" but only because there is not enough > > > >> >> information. In fact it seems more serious as it is resistant to all > > > >> >> tested drugs. > > > >> >> > > > >> > > > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > > > >> > > > > >> >> And then what about > > > >> >> > > > >> >> strain D SR > > > >> >> strain E RS > > > >> >> > > > >> > > > > >> > Yes those are the cases which are annoying me. > > > >> > > > > >> > That's why I came up withe idea of multiplying the value of the result > > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with > > > >> > triplets but there will still be cases where different results will > > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. > > > >> > > > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 > > > >> > will be the same numerical value but they are different resistance > > > >> > profiles I would in this case keep both. > > > >> > > > > >> > How to prevent that from happening. > > > >> Can you first say why the suggestion I made is not helpful? > > > >> > > > >> -- > > > >> Ben. > > > > > > > > You mean that one: > > > > > > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > > > >> >> number. In base 10, the strains score > > > >> >> > > > >> >> R S I > > > >> >> strain A 1 5 0 = 150 > > > >> >> strain B 3 3 0 = 330 > > > >> >> > > > > > > > > Different resistance profiles same result: > > > I don't yet understand the requirements so I am taking it in stages. > > > The first requirement seemed to be "more or less resistant". To do that > > > you can use digits in a large enough base but this will make the number > > > of Rs, Ss and Is paramount. Is that acceptable as a first step? > > > > > The requirements are one strain of a certain microorganism/patient > > The most resistant one or if they have different profiles > > > > SRS vs RRS => last one, more Rs > > > > SRS vs RSR = both, different profiles > > > In order to help people to be able to make further suggestions, maybe > > > you could give the relative ordering you would like to see between the > > > following sets of profiles. For example, between SSR, SRS and RSS, I > > > think the order you want is RSS > SRS > SSR. > > > > > > 1: SSR, SRS, RSS > > > > > > 2: RSI, RIS, SRI, SIR, IRS, ISR > > > > > > 3: SSSR, SSRS, SRSS, RSSS > > > > > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR > > > > > The order of the results is given by the ID of the drug in the extraction tool. > > I could probably order them by family and hierarchy of potence but > > would that make a difference? > > > It's possible you could make do with an extra field (or digits) that > > > gives some measure of the relative ordering between otherwise similar > > > sequences. For example, using base 10 (for convenience of arithmetic) > > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the > > > positioning of the results in the sequence. There are lots of way to do > > > this. One way would be use, as you were thinking, some sort of weighted > > > count. Using S=0, I=1 and R=2 with weights > > > > > > 54321 > > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 > > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 > > > > > So to be sure that I am following: > > > > 2*(5+4) = value of R (=2) * position of R(@5 and @4) > > 2*(5+3) = value of R (=2) * position of R(@5 and @3) > > > > 0*(3+2) = value of S (=0) * position of S(@3 and @2) > > 0*(4+2) = value of S (=0) * position of S(@4 and @2) > > > > 1*1 = value of I (=1) * position of I (@1) > > > > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other > > number? > > > Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100 > > But in this example I would have to keep both as drug 5,2 and 1 are common > > to both results but 4 and 3 are unique. > > > > The score would be completely misleading. > > > > So if my table has a width of 20 columns the first column would be > > 10^20, the next 10^19,.... +/- a few 0s off? > > > > I would have to implement it and see what I get as result. > > > If you absolutely must never get duplicate numbers, but you still want > > > to preserve a strict specified ordering, I think you will have much more > > > work to do. > > > > > > Getting a unique number for each case it trivial (but the ordering will > > > be wrong) and getting an ordering that rates every R > every S > every I > > > is also trivial, but there will be lots of duplicates. It's finding the > > > balance that's going to be hard. > > > > > > -- > > > Ben. > > I have prepared a cleaned up Excel workbook with only the duplicates which > > pose problems. The ones I would keep have an orange ID. > > I could upload it to Github. If that helps understanding the different cases. > > > > Thanks for your patience > > > > Laurent Ben, I have implemented your solution but I don't understand the reason why S would have a value of 0? I then don't need to take care of the S'es because the result will always be 0. Not that it changes a lot Because I still couldn't choose the profile of interest only based on the numbers. R R S S I Ben's Solution: 212 11 Mine: 212 1205 R S R S I 212 13 212 1405 R R R S I 311 17 311 1805 R S R R I 311 21 311 1407 S R R R I 311 23 311 1607 311 17 and 311 23 being the most likely but unclear where the difference might be. I have adapted my current solution to include the number of R,I,S weight of the results: S=1, I=2, R=3 weight of the position in the triplet: 1st=1, 2nd=2, 3rd=3 ie.: R R R => First triplet: 1*3+2*3+3*3 = 18 S I => Second triplet 1*1+2*2 = 05 RIS count: 311 Append 1st triplet: 311 18 Append 2nd triplet: 311 18 05 311 18 05 and 311 16 07 being the most likely with some clues which triplet is different. Am I not somehow introducing a bias by multiplying the value with the position in the triplet? And then there is still the case where SSR (1*1+2*1+3*3=12) and RRS (1*3+2*3+3*1=12) will both resolve to the same value. With 5 values it looks easy but with 20 I am getting headaches. I don't even know if the triplet idea is good. Got inspired by some old microorganism identification cards which put 3 test results into one digit to get a more compact identification profile. Wouldn't I need some sort of Traveling Salesman Problems algorithm to find the profile with the highest number of resistances and the highest number of triplets with high values. Thanks Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 12:54 ` Laurent @ 2021-12-28 13:57 ` Ben Bacarisse 2021-12-28 18:19 ` Laurent 0 siblings, 1 reply; 25+ messages in thread From: Ben Bacarisse @ 2021-12-28 13:57 UTC (permalink / raw) Laurent <lutgenl@icloud.com> writes: > On Tuesday, 28 December 2021 at 10:05:50 UTC+1, Laurent wrote: >> On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: >> > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: >> > > Laurent <lut...@icloud.com> writes: >> > > >> > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: >> > > >> Laurent <lut...@icloud.com> writes: >> > > >> >> > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: >> > > >> >> Laurent <lut...@icloud.com> writes: >> > > >> >> >> > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: >> > > >> >> > >> > > >> >> >> Sorry, but I found your problem description impossible to understand. >> > > >> >> >> Try to describe more clearly the experiment that is done, the structure >> > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and >> > > >> >> >> columns), and the statistic you want to compute. >> > > >> >> > >> > > >> >> > Sorry tried to keep it short, was too short. >> > > >> >> > >> > > >> >> > Columns are the antimicrobial drugs >> > > >> >> > Rows are the microorganism. >> > > >> >> > >> > > >> >> > So every cell contains a result of S, I, R or simply an empty cell >> > > >> >> > >> > > >> >> > S = Sensible >> > > >> >> > I = Intermediate >> > > >> >> > R = Resistant >> > > >> >> > >> > > >> >> > empty cell <S<I<R >> > > >> >> > >> > > >> >> > If a patient has 3 strains of the same microorganism but with >> > > >> >> > different resistance profiles I have to find the most resistant >> > > >> >> > one. Or if they are different I keep them all. >> > > >> >> > >> > > >> >> > I have no idea how to explain what I am doing to the compiler. >> > > >> >> I think when you can explain it to people, you'll be able to code it. I >> > > >> >> am still struggling to understand what you need. >> > > >> >> > Why I would choose result from strain B over the result from strain A. >> > > >> >> > >> > > >> >> > strain A: SSSRSS >> > > >> >> > strain B: SSRRRS >> > > >> >> Let's space it out >> > > >> >> >> > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 >> > > >> >> strain A S S S R S S >> > > >> >> strain B S S R R R S >> > > >> >> >> > > >> >> You want to choose B because it has is resistant to more drugs, yes? >> > > >> >> >> > > >> > >> > > >> > Yes indeed >> > > >> > >> > > >> >> I think, from the ordering you give, you need a measure that treats an R >> > > >> >> as "more important" that any "I" which is "more important" than an "S". >> > > >> >> (We will come to empty cells later.) >> > > >> >> >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> > > >> >> number. In base 10, the strains score >> > > >> >> >> > > >> >> R S I >> > > >> >> strain A 1 5 0 = 150 >> > > >> >> strain B 3 3 0 = 330 >> > > >> >> >> > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can >> > > >> >> use is one more than the maximum number of test results. If there can >> > > >> >> be up to 16 tests (say) the score is >> > > >> >> >> > > >> >> n(R)*17*17 + n(S)*17 + n(I). >> > > >> >> >> > > >> >> If this suits your needs, we can consider empty cells later on. It's >> > > >> >> not at all clear to me how to compare >> > > >> >> >> > > >> >> strain C R____ >> > > >> >> strain D RRSSSS >> > > >> >> >> > > >> >> Strain C is "less resistant" but only because there is not enough >> > > >> >> information. In fact it seems more serious as it is resistant to all >> > > >> >> tested drugs. >> > > >> >> >> > > >> > >> > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. >> > > >> > >> > > >> >> And then what about >> > > >> >> >> > > >> >> strain D SR >> > > >> >> strain E RS >> > > >> >> >> > > >> > >> > > >> > Yes those are the cases which are annoying me. >> > > >> > >> > > >> > That's why I came up withe idea of multiplying the value of the result >> > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with >> > > >> > triplets but there will still be cases where different results will >> > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. >> > > >> > >> > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 >> > > >> > will be the same numerical value but they are different resistance >> > > >> > profiles I would in this case keep both. >> > > >> > >> > > >> > How to prevent that from happening. >> > > >> Can you first say why the suggestion I made is not helpful? >> > > >> >> > > >> -- >> > > >> Ben. >> > > > >> > > > You mean that one: >> > > > >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> > > >> >> number. In base 10, the strains score >> > > >> >> >> > > >> >> R S I >> > > >> >> strain A 1 5 0 = 150 >> > > >> >> strain B 3 3 0 = 330 >> > > >> >> >> > > > >> > > > Different resistance profiles same result: >> > > I don't yet understand the requirements so I am taking it in stages. >> > > The first requirement seemed to be "more or less resistant". To do that >> > > you can use digits in a large enough base but this will make the number >> > > of Rs, Ss and Is paramount. Is that acceptable as a first step? >> > > >> > The requirements are one strain of a certain microorganism/patient >> > The most resistant one or if they have different profiles >> > >> > SRS vs RRS => last one, more Rs >> > >> > SRS vs RSR = both, different profiles >> > > In order to help people to be able to make further suggestions, maybe >> > > you could give the relative ordering you would like to see between the >> > > following sets of profiles. For example, between SSR, SRS and RSS, I >> > > think the order you want is RSS > SRS > SSR. >> > > >> > > 1: SSR, SRS, RSS >> > > >> > > 2: RSI, RIS, SRI, SIR, IRS, ISR >> > > >> > > 3: SSSR, SSRS, SRSS, RSSS >> > > >> > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR >> > > >> > The order of the results is given by the ID of the drug in the extraction tool. >> > I could probably order them by family and hierarchy of potence but >> > would that make a difference? >> > > It's possible you could make do with an extra field (or digits) that >> > > gives some measure of the relative ordering between otherwise similar >> > > sequences. For example, using base 10 (for convenience of arithmetic) >> > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the >> > > positioning of the results in the sequence. There are lots of way to do >> > > this. One way would be use, as you were thinking, some sort of weighted >> > > count. Using S=0, I=1 and R=2 with weights >> > > >> > > 54321 >> > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 >> > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 >> > > >> > So to be sure that I am following: >> > >> > 2*(5+4) = value of R (=2) * position of R(@5 and @4) >> > 2*(5+3) = value of R (=2) * position of R(@5 and @3) >> > >> > 0*(3+2) = value of S (=0) * position of S(@3 and @2) >> > 0*(4+2) = value of S (=0) * position of S(@4 and @2) >> > >> > 1*1 = value of I (=1) * position of I (@1) >> > >> > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other >> > number? >> > >> Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100 >> > But in this example I would have to keep both as drug 5,2 and 1 are common >> > to both results but 4 and 3 are unique. >> > >> > The score would be completely misleading. >> > >> > So if my table has a width of 20 columns the first column would be >> > 10^20, the next 10^19,.... +/- a few 0s off? >> > >> > I would have to implement it and see what I get as result. >> > > If you absolutely must never get duplicate numbers, but you still want >> > > to preserve a strict specified ordering, I think you will have much more >> > > work to do. >> > > >> > > Getting a unique number for each case it trivial (but the ordering will >> > > be wrong) and getting an ordering that rates every R > every S > every I >> > > is also trivial, but there will be lots of duplicates. It's finding the >> > > balance that's going to be hard. >> > > >> > > -- >> > > Ben. >> > I have prepared a cleaned up Excel workbook with only the duplicates which >> > pose problems. The ones I would keep have an orange ID. >> > I could upload it to Github. If that helps understanding the different cases. >> > >> > Thanks for your patience >> > >> > Laurent > > Ben, Posts crossed. You should probably ignore my last as it was written before I saw this one. > I have implemented your solution but I don't understand the reason why S would have a value of 0? > I then don't need to take care of the S'es because the result will always be 0. Not that it changes a lot > > Because I still couldn't choose the profile of interest only based on the numbers. > > R R S S I Ben's Solution: 212 11 Mine: 212 1205 > R S R S I 212 13 212 1405 > R R R S I 311 17 311 1805 > R S R R I 311 21 311 1407 > S R R R I 311 23 311 1607 > > 311 17 and 311 23 being the most likely but unclear where the > difference might be. This is what is so frustrating for me. What do you mean, most likely? What do you mean be what the difference might be? Can you describe to me, as a human being, which you would choose and tell me how you decided. If you can't do that then all you are doing is trying random schemes until something pops up the look right for some specific set of data! > I have adapted my current solution to include the number of R,I,S > weight of the results: S=1, I=2, R=3 > weight of the position in the triplet: 1st=1, 2nd=2, 3rd=3 > > ie.: R R R => First triplet: 1*3+2*3+3*3 = 18 > S I => Second triplet 1*1+2*2 = 05 > > RIS count: 311 > Append 1st triplet: 311 18 > Append 2nd triplet: 311 18 05 > > 311 18 05 and 311 16 07 being the most likely with some clues which > triplet is different. It sound like you want the result to "give some clues". Why not just return the string of letters? SRRRI tells you everything about the tests. What more could you want? If you want these ordered by number R, I and S counts, put these first always using two digits: "030101SRRRI" This string will sort the important, highly resistant strains to the top and also gives all the information about the individual tests. > Am I not somehow introducing a bias by multiplying the value with the position in the triplet? > And then there is still the case where SSR (1*1+2*1+3*3=12) and RRS (1*3+2*3+3*1=12) > will both resolve to the same value. Eh? Don't you want more Rs to get high scores? That's what the counts are for. > Wouldn't I need some sort of Traveling Salesman Problems algorithm to find the profile > with the highest number of resistances and the highest number of > triplets with high values. I don't understand the triplets idea. Sorry. -- Ben. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 13:57 ` Ben Bacarisse @ 2021-12-28 18:19 ` Laurent 0 siblings, 0 replies; 25+ messages in thread From: Laurent @ 2021-12-28 18:19 UTC (permalink / raw) On Tuesday, 28 December 2021 at 14:57:22 UTC+1, Ben Bacarisse wrote: > Laurent <lut...@icloud.com> writes: > > > On Tuesday, 28 December 2021 at 10:05:50 UTC+1, Laurent wrote: > >> On Tuesday, 28 December 2021 at 08:48:32 UTC+1, Laurent wrote: > >> > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: > >> > > Laurent <lut...@icloud.com> writes: > >> > > > >> > > > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: > >> > > >> Laurent <lut...@icloud.com> writes: > >> > > >> > >> > > >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: > >> > > >> >> Laurent <lut...@icloud.com> writes: > >> > > >> >> > >> > > >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > >> > > >> >> > > >> > > >> >> >> Sorry, but I found your problem description impossible to understand. > >> > > >> >> >> Try to describe more clearly the experiment that is done, the structure > >> > > >> >> >> of the data the experiment provides (the meaning of the Excel rows and > >> > > >> >> >> columns), and the statistic you want to compute. > >> > > >> >> > > >> > > >> >> > Sorry tried to keep it short, was too short. > >> > > >> >> > > >> > > >> >> > Columns are the antimicrobial drugs > >> > > >> >> > Rows are the microorganism. > >> > > >> >> > > >> > > >> >> > So every cell contains a result of S, I, R or simply an empty cell > >> > > >> >> > > >> > > >> >> > S = Sensible > >> > > >> >> > I = Intermediate > >> > > >> >> > R = Resistant > >> > > >> >> > > >> > > >> >> > empty cell <S<I<R > >> > > >> >> > > >> > > >> >> > If a patient has 3 strains of the same microorganism but with > >> > > >> >> > different resistance profiles I have to find the most resistant > >> > > >> >> > one. Or if they are different I keep them all. > >> > > >> >> > > >> > > >> >> > I have no idea how to explain what I am doing to the compiler. > >> > > >> >> I think when you can explain it to people, you'll be able to code it. I > >> > > >> >> am still struggling to understand what you need. > >> > > >> >> > Why I would choose result from strain B over the result from strain A. > >> > > >> >> > > >> > > >> >> > strain A: SSSRSS > >> > > >> >> > strain B: SSRRRS > >> > > >> >> Let's space it out > >> > > >> >> > >> > > >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 > >> > > >> >> strain A S S S R S S > >> > > >> >> strain B S S R R R S > >> > > >> >> > >> > > >> >> You want to choose B because it has is resistant to more drugs, yes? > >> > > >> >> > >> > > >> > > >> > > >> > Yes indeed > >> > > >> > > >> > > >> >> I think, from the ordering you give, you need a measure that treats an R > >> > > >> >> as "more important" that any "I" which is "more important" than an "S". > >> > > >> >> (We will come to empty cells later.) > >> > > >> >> > >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > >> > > >> >> number. In base 10, the strains score > >> > > >> >> > >> > > >> >> R S I > >> > > >> >> strain A 1 5 0 = 150 > >> > > >> >> strain B 3 3 0 = 330 > >> > > >> >> > >> > > >> >> Now, in fact, you don't need to use base 10. The smallest base you can > >> > > >> >> use is one more than the maximum number of test results. If there can > >> > > >> >> be up to 16 tests (say) the score is > >> > > >> >> > >> > > >> >> n(R)*17*17 + n(S)*17 + n(I). > >> > > >> >> > >> > > >> >> If this suits your needs, we can consider empty cells later on. It's > >> > > >> >> not at all clear to me how to compare > >> > > >> >> > >> > > >> >> strain C R____ > >> > > >> >> strain D RRSSSS > >> > > >> >> > >> > > >> >> Strain C is "less resistant" but only because there is not enough > >> > > >> >> information. In fact it seems more serious as it is resistant to all > >> > > >> >> tested drugs. > >> > > >> >> > >> > > >> > > >> > > >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. > >> > > >> > > >> > > >> >> And then what about > >> > > >> >> > >> > > >> >> strain D SR > >> > > >> >> strain E RS > >> > > >> >> > >> > > >> > > >> > > >> > Yes those are the cases which are annoying me. > >> > > >> > > >> > > >> > That's why I came up withe idea of multiplying the value of the result > >> > > >> > (S=1, I=2 and R=3) with the position of the value. Tried it with > >> > > >> > triplets but there will still be cases where different results will > >> > > >> > give the same numeric value. Ignoring empty cell able tps for the moment. > >> > > >> > > >> > > >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 > >> > > >> > will be the same numerical value but they are different resistance > >> > > >> > profiles I would in this case keep both. > >> > > >> > > >> > > >> > How to prevent that from happening. > >> > > >> Can you first say why the suggestion I made is not helpful? > >> > > >> > >> > > >> -- > >> > > >> Ben. > >> > > > > >> > > > You mean that one: > >> > > > > >> > > >> >> I think you need to treat the number of Rs, Is and Ss like digits in a > >> > > >> >> number. In base 10, the strains score > >> > > >> >> > >> > > >> >> R S I > >> > > >> >> strain A 1 5 0 = 150 > >> > > >> >> strain B 3 3 0 = 330 > >> > > >> >> > >> > > > > >> > > > Different resistance profiles same result: > >> > > I don't yet understand the requirements so I am taking it in stages. > >> > > The first requirement seemed to be "more or less resistant". To do that > >> > > you can use digits in a large enough base but this will make the number > >> > > of Rs, Ss and Is paramount. Is that acceptable as a first step? > >> > > > >> > The requirements are one strain of a certain microorganism/patient > >> > The most resistant one or if they have different profiles > >> > > >> > SRS vs RRS => last one, more Rs > >> > > >> > SRS vs RSR = both, different profiles > >> > > In order to help people to be able to make further suggestions, maybe > >> > > you could give the relative ordering you would like to see between the > >> > > following sets of profiles. For example, between SSR, SRS and RSS, I > >> > > think the order you want is RSS > SRS > SSR. > >> > > > >> > > 1: SSR, SRS, RSS > >> > > > >> > > 2: RSI, RIS, SRI, SIR, IRS, ISR > >> > > > >> > > 3: SSSR, SSRS, SRSS, RSSS > >> > > > >> > > 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR > >> > > > >> > The order of the results is given by the ID of the drug in the extraction tool. > >> > I could probably order them by family and hierarchy of potence but > >> > would that make a difference? > >> > > It's possible you could make do with an extra field (or digits) that > >> > > gives some measure of the relative ordering between otherwise similar > >> > > sequences. For example, using base 10 (for convenience of arithmetic) > >> > > both RRSSI and RSRSI would score 212xx but the last xx would reflect the > >> > > positioning of the results in the sequence. There are lots of way to do > >> > > this. One way would be use, as you were thinking, some sort of weighted > >> > > count. Using S=0, I=1 and R=2 with weights > >> > > > >> > > 54321 > >> > > RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 > >> > > RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 > >> > > > >> > So to be sure that I am following: > >> > > >> > 2*(5+4) = value of R (=2) * position of R(@5 and @4) > >> > 2*(5+3) = value of R (=2) * position of R(@5 and @3) > >> > > >> > 0*(3+2) = value of S (=0) * position of S(@3 and @2) > >> > 0*(4+2) = value of S (=0) * position of S(@4 and @2) > >> > > >> > 1*1 = value of I (=1) * position of I (@1) > >> > > >> > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other > >> > number? > >> > > >> Eh forget the last sentence, brain fart: I have 2 R's so 2*10000, 1 I so 1*1000 and 2 S's so 2*100 > >> > But in this example I would have to keep both as drug 5,2 and 1 are common > >> > to both results but 4 and 3 are unique. > >> > > >> > The score would be completely misleading. > >> > > >> > So if my table has a width of 20 columns the first column would be > >> > 10^20, the next 10^19,.... +/- a few 0s off? > >> > > >> > I would have to implement it and see what I get as result. > >> > > If you absolutely must never get duplicate numbers, but you still want > >> > > to preserve a strict specified ordering, I think you will have much more > >> > > work to do. > >> > > > >> > > Getting a unique number for each case it trivial (but the ordering will > >> > > be wrong) and getting an ordering that rates every R > every S > every I > >> > > is also trivial, but there will be lots of duplicates. It's finding the > >> > > balance that's going to be hard. > >> > > > >> > > -- > >> > > Ben. > >> > I have prepared a cleaned up Excel workbook with only the duplicates which > >> > pose problems. The ones I would keep have an orange ID. > >> > I could upload it to Github. If that helps understanding the different cases. > >> > > >> > Thanks for your patience > >> > > >> > Laurent > > > > Ben, > Posts crossed. You should probably ignore my last as it was written > before I saw this one. > > I have implemented your solution but I don't understand the reason why S would have a value of 0? > > I then don't need to take care of the S'es because the result will always be 0. Not that it changes a lot > > > > Because I still couldn't choose the profile of interest only based on the numbers. > > > > R R S S I Ben's Solution: 212 11 Mine: 212 1205 > > R S R S I 212 13 212 1405 > > R R R S I 311 17 311 1805 > > R S R R I 311 21 311 1407 > > S R R R I 311 23 311 1607 > > > > 311 17 and 311 23 being the most likely but unclear where the > > difference might be. > This is what is so frustrating for me. What do you mean, most likely? > What do you mean be what the difference might be? Can you describe to > me, as a human being, which you would choose and tell me how you > decided. If you can't do that then all you are doing is trying random > schemes until something pops up the look right for some specific set of > data! 311 17 and 311 23 Have the most a R's and are most different from each other. 311 23 has an R in a position which 311 17 doesn't have. 311 21 gets deleted because it has nothing unique which the 2 others wouldn't have. 212 11 and 212 13 are also deleted. Less R's, nothing unique. > > I have adapted my current solution to include the number of R,I,S > > weight of the results: S=1, I=2, R=3 > > weight of the position in the triplet: 1st=1, 2nd=2, 3rd=3 > > > > ie.: R R R => First triplet: 1*3+2*3+3*3 = 18 > > S I => Second triplet 1*1+2*2 = 05 > > > > RIS count: 311 > > Append 1st triplet: 311 18 > > Append 2nd triplet: 311 18 05 > > > > 311 18 05 and 311 16 07 being the most likely with some clues which > > triplet is different. > It sound like you want the result to "give some clues". Why not just > return the string of letters? SRRRI tells you everything about the > tests. What more could you want? If you want these ordered by number > R, I and S counts, put these first always using two digits: > > "030101SRRRI" > > This string will sort the important, highly resistant strains to the top > and also gives all the information about the individual tests. > > Am I not somehow introducing a bias by multiplying the value with the position in the triplet? > > And then there is still the case where SSR (1*1+2*1+3*3=12) and RRS (1*3+2*3+3*1=12) > > will both resolve to the same value. > Eh? Don't you want more Rs to get high scores? That's what the counts > are for. No more R's are not always better. If I have a strain with a R in a place all the other's don't have I have to keep that one too even with a low score. Because of that stupid requirement: > >> > The requirements are one strain of a certain microorganism/patient > >> > The most resistant one or if they have different profiles > >> > > >> > SRS vs RRS => last one, more Rs > >> > > >> > SRS vs RSR = both, different profiles The extraction tool I use is doing the stats for me. Just the "client" Dr Dr something wants it like that. So I have to do that post-treatment which cost me a lot of time. Here the Excel file with my play data: https://github.com/Chutulu/Bacterio-Statistiques.git The strains with their IDs in orange are the ones I keep. strain b has the same number of Rs but in different places aka different drugs so different behaviour if you prescribe antibiotics. strain d I keep the one with empty cells because it has an R the other one doesn't have Most R's and/or uniqueness If a docter knows the name of the microorganism but doesn't have the results of antimicrobial susceptibility testing then he can use the data I have provided to give an empirical treatment. So I have to consider all unique strains but be very pessimistic about their sensibility (most R's) Dr Dr client's idea to print them as plastic pocket table. Fortunately my name isn't mentioned anywhere. > > Wouldn't I need some sort of Traveling Salesman Problems algorithm to find the profile > > with the highest number of resistances and the highest number of > > triplets with high values. > I don't understand the triplets idea. Sorry. No problem. I have used some application specific idea while creating it. I think it might be usable. Have just to figure out how. If you want to leave the discussion I can understand. Sometimes it helps me speaking with someone who has no clue what I am talking about to see things clearer. My maths skills are very low. The reason why I am an MTA. No math required. The most difficult I have to calculate are dilutions. So one reason for miscommunication comes from there. The other being the application specific context of microbiologie. I am doing that for 18 years. Starring to long at at the results has probably also fried too many neurones. > > -- > Ben. Thanks for your time and patience Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 7:48 ` Laurent 2021-12-28 9:05 ` Laurent @ 2021-12-28 13:43 ` Ben Bacarisse 2021-12-28 16:49 ` Dennis Lee Bieber 2 siblings, 0 replies; 25+ messages in thread From: Ben Bacarisse @ 2021-12-28 13:43 UTC (permalink / raw) Laurent <lutgenl@icloud.com> writes: > On Tuesday, 28 December 2021 at 01:29:57 UTC+1, Ben Bacarisse wrote: >> Laurent <lut...@icloud.com> writes: >> >> > On Monday, 27 December 2021 at 21:49:18 UTC+1, Ben Bacarisse wrote: >> >> Laurent <lut...@icloud.com> writes: >> >> >> >> > On Monday, 27 December 2021 at 14:14:42 UTC+1, Ben Bacarisse wrote: >> >> >> Laurent <lut...@icloud.com> writes: >> >> >> >> >> >> > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: >> >> >> > >> >> >> >> Sorry, but I found your problem description impossible to understand. >> >> >> >> Try to describe more clearly the experiment that is done, the structure >> >> >> >> of the data the experiment provides (the meaning of the Excel rows and >> >> >> >> columns), and the statistic you want to compute. >> >> >> > >> >> >> > Sorry tried to keep it short, was too short. >> >> >> > >> >> >> > Columns are the antimicrobial drugs >> >> >> > Rows are the microorganism. >> >> >> > >> >> >> > So every cell contains a result of S, I, R or simply an empty cell >> >> >> > >> >> >> > S = Sensible >> >> >> > I = Intermediate >> >> >> > R = Resistant >> >> >> > >> >> >> > empty cell <S<I<R >> >> >> > >> >> >> > If a patient has 3 strains of the same microorganism but with >> >> >> > different resistance profiles I have to find the most resistant >> >> >> > one. Or if they are different I keep them all. >> >> >> > >> >> >> > I have no idea how to explain what I am doing to the compiler. >> >> >> I think when you can explain it to people, you'll be able to code it. I >> >> >> am still struggling to understand what you need. >> >> >> > Why I would choose result from strain B over the result from strain A. >> >> >> > >> >> >> > strain A: SSSRSS >> >> >> > strain B: SSRRRS >> >> >> Let's space it out >> >> >> >> >> >> drug 1 drug 2 drug 3 drug 4 drug 5 drug 6 >> >> >> strain A S S S R S S >> >> >> strain B S S R R R S >> >> >> >> >> >> You want to choose B because it has is resistant to more drugs, yes? >> >> >> >> >> > >> >> > Yes indeed >> >> > >> >> >> I think, from the ordering you give, you need a measure that treats an R >> >> >> as "more important" that any "I" which is "more important" than an "S". >> >> >> (We will come to empty cells later.) >> >> >> >> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> >> >> number. In base 10, the strains score >> >> >> >> >> >> R S I >> >> >> strain A 1 5 0 = 150 >> >> >> strain B 3 3 0 = 330 >> >> >> >> >> >> Now, in fact, you don't need to use base 10. The smallest base you can >> >> >> use is one more than the maximum number of test results. If there can >> >> >> be up to 16 tests (say) the score is >> >> >> >> >> >> n(R)*17*17 + n(S)*17 + n(I). >> >> >> >> >> >> If this suits your needs, we can consider empty cells later on. It's >> >> >> not at all clear to me how to compare >> >> >> >> >> >> strain C R____ >> >> >> strain D RRSSSS >> >> >> >> >> >> Strain C is "less resistant" but only because there is not enough >> >> >> information. In fact it seems more serious as it is resistant to all >> >> >> tested drugs. >> >> >> >> >> > >> >> > Strain C is probably garbage and I would remove it. With a bit of luck I will have the result with the same sample Id which would be complete. >> >> > >> >> >> And then what about >> >> >> >> >> >> strain D SR >> >> >> strain E RS >> >> >> >> >> > >> >> > Yes those are the cases which are annoying me. >> >> > >> >> > That's why I came up withe idea of multiplying the value of the result >> >> > (S=1, I=2 and R=3) with the position of the value. Tried it with >> >> > triplets but there will still be cases where different results will >> >> > give the same numeric value. Ignoring empty cell able tps for the moment. >> >> > >> >> > Strain F: SSR (1*1+2*1+3*3) =12 and Strain G: RRS (1*3+ 2*3+3*1) = 12 >> >> > will be the same numerical value but they are different resistance >> >> > profiles I would in this case keep both. >> >> > >> >> > How to prevent that from happening. >> >> Can you first say why the suggestion I made is not helpful? >> >> >> >> -- >> >> Ben. >> > >> > You mean that one: >> > >> >> >> I think you need to treat the number of Rs, Is and Ss like digits in a >> >> >> number. In base 10, the strains score >> >> >> >> >> >> R S I >> >> >> strain A 1 5 0 = 150 >> >> >> strain B 3 3 0 = 330 >> >> >> >> > >> > Different resistance profiles same result: >> >> I don't yet understand the requirements so I am taking it in stages. >> The first requirement seemed to be "more or less resistant". To do that >> you can use digits in a large enough base but this will make the number >> of Rs, Ss and Is paramount. Is that acceptable as a first step? > > The requirements are one strain of a certain microorganism/patient > The most resistant one or if they have different profiles > > SRS vs RRS => last one, more Rs > > SRS vs RSR = both, different profiles I think this is a "yes" to my question. The trouble is you speak in the subject domain (as one would expect) but I have to speak in the computer science domain, because that's all I know. You speak of giving profiles a score. To me, that mean giving a profile some numeric value (actually it need not be numeric, but let's stick with numbers for the moment). The score orders the profiles -- some score high (= very resistant) and some score lower. >> In order to help people to be able to make further suggestions, maybe >> you could give the relative ordering you would like to see between the >> following sets of profiles. For example, between SSR, SRS and RSS, I >> think the order you want is RSS > SRS > SSR. >> >> 1: SSR, SRS, RSS >> >> 2: RSI, RIS, SRI, SIR, IRS, ISR >> >> 3: SSSR, SSRS, SRSS, RSSS >> >> 4: RRSSS, RSSSR, RIIII, SRIII, RSIII, IIIRS, IIISR > > The order of the results is given by the ID of the drug in the extraction tool. > I could probably order them by family and hierarchy of potence but > would that make a difference? I am referring to the order you want the score to produce. You want, I think, the score for a profile with more Rs to be higher than any score for a profile with fewer. Using x for an S or an I or a missing result, you want all of RRRxxx xRRxRx xxRxRR and so on to score higher than any of RRxxxx RxxRxx xxRxRx Three Rs beats two R no matter where they are. Similarly, when the number of Rs is the same, you want a profile with more Is to "beat" (score higher) than any profile with fewer. There is a standard way to do this which can result in a pure number, but you can also think of it as a short sequence of numbers (three in this case) where the first is more important than the second, which is more important than the third. So IIISR is given the sequence (1,1,3) (1 R, 1 S and 3 Is). As a base 10 number, that's 113. In base 100 it's 10103. Bigger bases allow one to separate larger counts. Now there is also a secondary ordering. When the number of Rs, Is and Ss is the same, I think you wanted to consider some test results as more important. To do that, I am suggesting adding another number to the sequence or another digit or two if you like. >> It's possible you could make do with an extra field (or digits) that >> gives some measure of the relative ordering between otherwise similar >> sequences. For example, using base 10 (for convenience of arithmetic) >> both RRSSI and RSRSI would score 212xx but the last xx would reflect the >> positioning of the results in the sequence. There are lots of way to do >> this. One way would be use, as you were thinking, some sort of weighted >> count. Using S=0, I=1 and R=2 with weights >> >> 54321 >> RRSSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+4) + 0*(3+2) + 1*1 = 21219 >> RSRSI scores 2*10000 + 1*1000 + 2*100 + 2*(5+3) + 0*(4+2) + 1*1 = 21217 >> > > So to be sure that I am following: > > 2*(5+4) = value of R (=2) * position of R(@5 and @4) > 2*(5+3) = value of R (=2) * position of R(@5 and @3) > > 0*(3+2) = value of S (=0) * position of S(@3 and @2) > 0*(4+2) = value of S (=0) * position of S(@4 and @2) > > 1*1 = value of I (=1) * position of I (@1) Yes. > 2*10000 + 1*1000 + 2*100 Is just used as padding? So 212 could be any other > number? No, the 212 reflects the counts or Rs, Is and Ss. It's up the high digits so that these counts trump everything else in the score. > But in this example I would have to keep both as drug 5,2 and 1 are common > to both results but 4 and 3 are unique. Ah, more domain specific constraints are coming in. I think I will have to duck out of this thread soon. > The score would be completely misleading. > > So if my table has a width of 20 columns the first column would be > 10^20, the next 10^19,.... +/- a few 0s off? No. If you have twenty columns, you need to use a base of at least 21 because the R, I and S counts could be as high as 20. For convenience, use 100 and leave two base-100 digits spare "at the bottom" for the "all counts being equal" differential score: nRs nIs nSs 00 00 10 decimal digits in all. But see below for another option. > I would have to implement it and see what I get as result. To get better answers, say what it is about the results that you don't like. If you can't say what you do want, saying what you don't want is the next best thing. > I have prepared a cleaned up Excel workbook with only the duplicates which > pose problems. The ones I would keep have an orange ID. > I could upload it to Github. If that helps understanding the different > cases. Probably. I may have the wrong end of the stick altogether because you worry about duplicates but talk about scores suggesting better or worse. There is nothing logically wrong with a duplicate score. You can, very simply, assign every set of results a unique number. Just replace R with 2, I with 1 and S with 0 and concatenate the results to treat it as number: IIISR = 11102 If you need the R, I and S counts to "dominate", prepend this number with the counts (using, say, 2 digits each): IIISR = 1030111102 (1 R, 3 Is, 1 S and the number from the seqeunce) Of course you can make the numbers smaller by using smaller bases, but I can't say if this produces the kind of score that you'd find useful. -- Ben. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 7:48 ` Laurent 2021-12-28 9:05 ` Laurent 2021-12-28 13:43 ` Ben Bacarisse @ 2021-12-28 16:49 ` Dennis Lee Bieber 2021-12-29 4:20 ` Randy Brukardt 2 siblings, 1 reply; 25+ messages in thread From: Dennis Lee Bieber @ 2021-12-28 16:49 UTC (permalink / raw) On Mon, 27 Dec 2021 23:48:31 -0800 (PST), Laurent <lutgenl@icloud.com> declaimed the following: > >The requirements are one strain of a certain microorganism/patient >The most resistant one or if they have different profiles > >SRS vs RRS => last one, more Rs > >SRS vs RSR = both, different profiles > Which is still inconclusive (at least as I view it) -- your second example ALSO fits the "last one, more Rs" constraint. You haven't to define how the first doesn't qualify as "different profiles". Both examples are "1R, 2S" vs "2R, 1S". -- Wulfraed Dennis Lee Bieber AF6VN wlfraed@ix.netcom.com http://wlfraed.microdiversity.freeddns.org/ ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 16:49 ` Dennis Lee Bieber @ 2021-12-29 4:20 ` Randy Brukardt 0 siblings, 0 replies; 25+ messages in thread From: Randy Brukardt @ 2021-12-29 4:20 UTC (permalink / raw) "Dennis Lee Bieber" <wlfraed@ix.netcom.com> wrote in message news:fefmsgl4cpearv10nkdm07kirrn5gete3e@4ax.com... > On Mon, 27 Dec 2021 23:48:31 -0800 (PST), Laurent <lutgenl@icloud.com> > declaimed the following: > >> >>The requirements are one strain of a certain microorganism/patient >>The most resistant one or if they have different profiles >> >>SRS vs RRS => last one, more Rs >> >>SRS vs RSR = both, different profiles >> > > Which is still inconclusive (at least as I view it) -- your second > example ALSO fits the "last one, more Rs" constraint. You haven't to > define > how the first doesn't qualify as "different profiles". Both examples are > "1R, 2S" vs "2R, 1S". Let me try. I think he is saying that when one compares two profiles, one compares each position with the relation I < S < R. Then, if you get the same order for every position (either >= or <=), then (and only then), the profile with more R's is the one you keep (or more S's if there are the same number of R's). If you don't get the same order for each, then you keep both. So, for any pair of profiles, you can get a result of "<", ">", or incomparable. It should be easy enough to write a function to determine this result. The problem I see is that I don't think there is any way to do this across all of the data short of comparing all of the pairs The compare function needs to be a "strict weak ordering" in order that sorting and the like be meaningful between data sets. The issue here is that "incomparable" gets in the way of having A > C being true for any B such that A > B and B > C. OTOH, I wouldn't worry about that unless the data set is large. Computers are fast these days, and brute force approaches are much easier to figure out. So I suggest the OP write a function to compare two sets of data; if the result is that the sets are comparable, eliminate the set that is less interesting. Then apply that function to every pair in the data until there are no further eliminations. (Probably one would "eliminate" a set by marking that it is less interesting that some other set, as opposed to deleting it outright. I'd probably just use a spreadsheet cell pointing to the more interesting set.) If it was me, I'd do that in Ada first, to get the algorithm right. Then translate it into whatever other languages (maybe even spreadsheet formulas). Randy. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 12:29 ` Laurent 2021-12-27 13:14 ` Ben Bacarisse @ 2021-12-27 17:41 ` Dennis Lee Bieber 2021-12-27 18:56 ` Niklas Holsti 2021-12-28 2:10 ` Randy Brukardt 2 siblings, 1 reply; 25+ messages in thread From: Dennis Lee Bieber @ 2021-12-27 17:41 UTC (permalink / raw) On Mon, 27 Dec 2021 04:29:06 -0800 (PST), Laurent <lutgenl@icloud.com> declaimed the following: >Why I would choose result from strain B over the result from strain A. > >strain A: SSSRSS >strain B: SSRRRS > >Simply counting the number of S, I and R doesn't work. ?Checksum with/without weight for the column number doesn't >work either. I wouldn't expect a checksum to be of any use, since the idea of most checksums (and CRCs) is to be able to verify that a data sequence has not been corrupted. Checksums don't "rank" data. > >Even if I get a correct result I have still the same problem as before why result B over result A. > Unfortunately, until you CAN describe why one result is preferred over another, no one will be able to suggest algorithm(s) that may work (of course, once you can explain it, you may not need assistance translating it to code). For all we know, the cost of the various compounds might be a factor affecting which of two similar result rows might be desired. Actually, I'm still perplexed at the idea that the solution is picking microbe strains that are most resistant to drugs -- unless one is trying to reduce test candidates for yet undeveloped drugs ("if our new concoction kills this strain, /then/ we will test it against the rest of the strains"). I'm tempted to suggest R (or other statistical software) and experimenting with various presentation/partitioning operations to see if something reasonable pops out. Your data is NOT numerical (so don't bother assigning numbers to your <null>SIR -- after all, you could just as easily assign the ordinal position in the ASCII alphabet to them), so statistical operations that work on non-numeric "factors" makes as much, if not more, sense. (I've only toyed with R, so I don't know if it has partitioning ability for three factors -- be a bit tedious to have to specify, say, compound-X factor = R (true) (false) compound-X factor = S (true) (false) ) -- Wulfraed Dennis Lee Bieber AF6VN wlfraed@ix.netcom.com http://wlfraed.microdiversity.freeddns.org/ ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 17:41 ` Dennis Lee Bieber @ 2021-12-27 18:56 ` Niklas Holsti 2021-12-27 19:44 ` Laurent 0 siblings, 1 reply; 25+ messages in thread From: Niklas Holsti @ 2021-12-27 18:56 UTC (permalink / raw) On 2021-12-27 19:41, Dennis Lee Bieber wrote: > On Mon, 27 Dec 2021 04:29:06 -0800 (PST), Laurent > <lutgenl@icloud.com> declaimed the following: > >> Why I would choose result from strain B over the result from strain >> A. >> >> strain A: SSSRSS strain B: SSRRRS >> >> Simply counting the number of S, I and R doesn't work. ?Checksum >> with/without weight for the column number doesn't work either. > > I wouldn't expect a checksum to be of any use, since the idea of > most checksums (and CRCs) is to be able to verify that a data > sequence has not been corrupted. Checksums don't "rank" data. I believe that Laurent does not mean "checksum" in its usual meaning, but a numerical "score" computed as a sum of terms multiplied by weights. Whether such a score can solve Laurent's problem is not clear. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 18:56 ` Niklas Holsti @ 2021-12-27 19:44 ` Laurent 0 siblings, 0 replies; 25+ messages in thread From: Laurent @ 2021-12-27 19:44 UTC (permalink / raw) On Monday, 27 December 2021 at 19:56:10 UTC+1, Niklas Holsti wrote: > On 2021-12-27 19:41, Dennis Lee Bieber wrote: > > On Mon, 27 Dec 2021 04:29:06 -0800 (PST), Laurent > > <lut...@icloud.com> declaimed the following: > > > >> Why I would choose result from strain B over the result from strain > >> A. > >> > >> strain A: SSSRSS strain B: SSRRRS > >> > >> Simply counting the number of S, I and R doesn't work. ?Checksum > >> with/without weight for the column number doesn't work either. > > > > I wouldn't expect a checksum to be of any use, since the idea of > > most checksums (and CRCs) is to be able to verify that a data > > sequence has not been corrupted. Checksums don't "rank" data. > > > I believe that Laurent does not mean "checksum" in its usual meaning, > but a numerical "score" computed as a sum of terms multiplied by > weights. Whether such a score can solve Laurent's problem is not clear. I used the algorithm for calculating the checksum for the GTIN in barcodes as starting point so I got stuck on the term checksum. Half of the problem is finding the correct word with which to feed google to find an answer. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 12:29 ` Laurent 2021-12-27 13:14 ` Ben Bacarisse 2021-12-27 17:41 ` Dennis Lee Bieber @ 2021-12-28 2:10 ` Randy Brukardt 2021-12-28 6:02 ` Laurent 2 siblings, 1 reply; 25+ messages in thread From: Randy Brukardt @ 2021-12-28 2:10 UTC (permalink / raw) "Laurent" <lutgenl@icloud.com> wrote in message news:49538254-21ed-4fd0-8316-1bccc7d3c635n@googlegroups.com... On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: ... >> Also, if you do not intend to implement the solution in Ada, this is not >> the right group to discuss it. >I would very much prefer to solve it in Ada but at work I am stuck with >Excel >and VBA which is better than doing it manually. After a few hours starring >at >a screen with thousand of rows of results... If I get an Ada solution I can >adapt it. Just limited to no access/pointers in VBA which shouldn't be >required? Hybrid Ada-spreadsheet solutions are possible. It's quite easy to read/write .csv files in Ada, and those can be easily imported/exported from any spreadsheet program (I've been using Libreoffice Calc, but Excel is similar). For an example, the ACATS grading tools essentially work by expecting the vendor (or a third party) to provide a tool that converts compilation results into a .csv file. The .csv file(s) are then read by the grading tool and compared to required results to provide a grade. But it also can be read into a spreadsheet for sanity checking as well as additional analysis. Similarly (and probably more useful to you), I've used spreadsheet data for various traffic in AdaIC (retrieved from Google) as input to Ada programs that analyze the data to provide information that Google is unable to (in particular, usage of the various Ada standards, which are split up into usage of several hundred separate files). I then take the results of the Ada program (which is also a .csv file), open that, and paste the results into a previously created spreadsheet that generates charts for showing to management. (Even highly skilled programmers don't like looking through columns of numbers for trends. :-) But you do have to be able to describe the results that you are looking for. Having read the entire thread, I'm more confused than I started. :-) I suspect when you can describe your problem algorithmically, the solution will be obvious. Good luck finding a solution. Randy. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 2:10 ` Randy Brukardt @ 2021-12-28 6:02 ` Laurent 2021-12-29 3:58 ` Randy Brukardt 0 siblings, 1 reply; 25+ messages in thread From: Laurent @ 2021-12-28 6:02 UTC (permalink / raw) On Tuesday, 28 December 2021 at 03:10:54 UTC+1, Randy Brukardt wrote: > "Laurent" <lut...@icloud.com> wrote in message > news:49538254-21ed-4fd0...@googlegroups.com... > On Monday, 27 December 2021 at 12:16:27 UTC+1, Niklas Holsti wrote: > ... > >> Also, if you do not intend to implement the solution in Ada, this is not > >> the right group to discuss it. > > >I would very much prefer to solve it in Ada but at work I am stuck with > >Excel > >and VBA which is better than doing it manually. After a few hours starring > >at > >a screen with thousand of rows of results... If I get an Ada solution I can > >adapt it. Just limited to no access/pointers in VBA which shouldn't be > >required? > Hybrid Ada-spreadsheet solutions are possible. It's quite easy to read/write > .csv files in Ada, and those can be easily imported/exported from any > spreadsheet program (I've been using Libreoffice Calc, but Excel is > similar). > > For an example, the ACATS grading tools essentially work by expecting the > vendor (or a third party) to provide a tool that converts compilation > results into a .csv file. The .csv file(s) are then read by the grading tool > and compared to required results to provide a grade. But it also can be read > into a spreadsheet for sanity checking as well as additional analysis. > > Similarly (and probably more useful to you), I've used spreadsheet data for > various traffic in AdaIC (retrieved from Google) as input to Ada programs > that analyze the data to provide information that Google is unable to (in > particular, usage of the various Ada standards, which are split up into > usage of several hundred separate files). I then take the results of the Ada > program (which is also a .csv file), open that, and paste the results into a > previously created spreadsheet that generates charts for showing to > management. (Even highly skilled programmers don't like looking through > columns of numbers for trends. :-) > > But you do have to be able to describe the results that you are looking for. > Having read the entire thread, I'm more confused than I started. :-) I > suspect when you can describe your problem algorithmically, the solution > will be obvious. Good luck finding a solution. > > Randy. The problem is not that I don't want to use Ada. We are using Citrix so I am stuck with the programs the IT departments allows me to use. Was already a chore to get MS Access made available. I could send all the data home but then I have to be very careful to not have some patient information somewhere floating around. Otherwise finding a solution to automate this will be the least of my problems. Thanks for the motivation ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-28 6:02 ` Laurent @ 2021-12-29 3:58 ` Randy Brukardt 0 siblings, 0 replies; 25+ messages in thread From: Randy Brukardt @ 2021-12-29 3:58 UTC (permalink / raw) "Laurent" <lutgenl@icloud.com> wrote in message news:b50bf401-87e7-4352-b517-7fe6b6ded42dn@googlegroups.com... ... > The problem is not that I don't want to use Ada. We are using Citrix so I > am stuck with the programs > the IT departments allows me to use. Was already a chore to get MS Access > made available. Understood. But this is an Ada group, and we're not very motivated to talk about stuff that can't be written in Ada. Besides, if you had an Ada solution, you probably could figure out how to transcribe it into some other language. Randy. ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 9:21 Some advice required [OT] Laurent 2021-12-27 11:16 ` Niklas Holsti @ 2021-12-27 17:18 ` Simon Wright 2021-12-27 18:30 ` Laurent 1 sibling, 1 reply; 25+ messages in thread From: Simon Wright @ 2021-12-27 17:18 UTC (permalink / raw) Laurent <lutgenl@icloud.com> writes: > Until now I have been doing it manually by staring for hours at Excel > sheets. I am trying to get it automated but I don't know how to solve > my problem. You must go through some mental process while staring at the spreadsheets; what's that process? It can't involve checksums! In a post below, you said you had to choose the most resistant, or if different all of them, which doesn't make sense. Are you perhaps thinking of ties? in which case you must have some notion of scoring profiles so you can determine which profiles come equal-first. Does RSSSSS score higher or lower than SIIIII? ^ permalink raw reply [flat|nested] 25+ messages in thread
* Re: Some advice required [OT] 2021-12-27 17:18 ` Simon Wright @ 2021-12-27 18:30 ` Laurent 0 siblings, 0 replies; 25+ messages in thread From: Laurent @ 2021-12-27 18:30 UTC (permalink / raw) On Monday, 27 December 2021 at 18:18:54 UTC+1, Simon Wright wrote: > Laurent <lut...@icloud.com> writes: > > > Until now I have been doing it manually by staring for hours at Excel > > sheets. I am trying to get it automated but I don't know how to solve > > my problem. > You must go through some mental process while staring at the > spreadsheets; what's that process? It can't involve checksums! > Perhaps not calculating but somehow estimating which has the most R's and considering the positions they are at. Considering the family of drug it belongs to. With certain microorganisms it works quite good because they are not very variable. Others are a real pain. Mostly finding the least resistant with the most in common of the more resistant and then deleting one after another. Then comparing the leftover ones. Takes time, high risk of messing up things because I begin to see things which are not there... > In a post below, you said you had to choose the most resistant, or if > different all of them, which doesn't make sense. Are you perhaps > thinking of ties? in which case you must have some notion of scoring > profiles so you can determine which profiles come equal-first. > > Does RSSSSS score higher or lower than SIIIII? That would be 2 different strains which I would keep both. Thanks Laurent ^ permalink raw reply [flat|nested] 25+ messages in thread
end of thread, other threads:[~2021-12-29 4:20 UTC | newest] Thread overview: 25+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2021-12-27 9:21 Some advice required [OT] Laurent 2021-12-27 11:16 ` Niklas Holsti 2021-12-27 12:29 ` Laurent 2021-12-27 13:14 ` Ben Bacarisse 2021-12-27 18:24 ` Laurent 2021-12-27 19:51 ` Dennis Lee Bieber 2021-12-27 20:49 ` Ben Bacarisse 2021-12-27 22:09 ` Laurent 2021-12-28 0:29 ` Ben Bacarisse 2021-12-28 7:48 ` Laurent 2021-12-28 9:05 ` Laurent 2021-12-28 12:54 ` Laurent 2021-12-28 13:57 ` Ben Bacarisse 2021-12-28 18:19 ` Laurent 2021-12-28 13:43 ` Ben Bacarisse 2021-12-28 16:49 ` Dennis Lee Bieber 2021-12-29 4:20 ` Randy Brukardt 2021-12-27 17:41 ` Dennis Lee Bieber 2021-12-27 18:56 ` Niklas Holsti 2021-12-27 19:44 ` Laurent 2021-12-28 2:10 ` Randy Brukardt 2021-12-28 6:02 ` Laurent 2021-12-29 3:58 ` Randy Brukardt 2021-12-27 17:18 ` Simon Wright 2021-12-27 18:30 ` Laurent
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox