Integrating Weighted Average calculator results with tabular Report CSVs

[sbodorkos]

@NicoleRayner I have reached the point where I am looking to integrate Squid3 Report-CSVs with database systems, and I am wishing the results from my Weighted Mean calculator (Appended to my Summary file) talked directly to the associated tabular report CSVs (for the Samples in particular, although I'd also like some connection for the Ref Materials as well).

I imagine you have well and truly reached/passed this point yourself, so I would be interested to know how you are dealing with it and/or whether any of the below is helpful.

I bounced some ideas off Jim @bowring on Monday, with the essential aim of labelling individual sample-rows (in the Unknowns Report-CSV) with "included" or "excluded", in line with a calculation performed and Appended within the Weighted Mean calculator. After we disappeared down a couple of rabbit-holes, we realised that there would need to be some caveats:

1. At present, we're talking about weighted mean ages - we did not talk about weighted means of anything else (but our preliminary solution is probably extensible - see below).
2. Numerical changes within a Report are quite widespread when you change age-type. So rather than writing a whole new Report each time a new weighted mean was Appended using the calculator, we thought the Report-CSV would only capture the most recent weighted mean age you calculated. This is to ensure that all the other numerical data in the report is internally consistent with that mean.

So our idea at the moment is to add 7 automated "age-flag" columns to the CSV reports, corresponding to the 7 possible age-types (@bowring I guess this is strictly "up to 7", to account for the possibility that a user's preferred Report-template might not actually deliver all 7 age-types). When you use the weighted mean calculator to Append a weighted mean for a Sample, the "age flag" column for the chosen age-type (only), for the chosen Sample (only) is filled with "included" or "excluded" in line with the actual rejections.

All the other "age flag" columns for that Sample (i.e. the other 6 age-types, which weren't used), would be filled "N/A". Any Sample for which a weighted mean is not Appended via the calculator would have "N/A" for all rows and all "age flag" columns when the report CSV is generated.

Jim hit on a pretty good way of ensuring we know exactly which weighted mean (if there have been multiple Appended to the summary-report) the age-flag columns relate to: every row of the relevant Sample will have the identical weighted mean Summary information (as written to the Summary file) appended to the right-hand side of the tabular Report-CSV, as a long text-string. This ensures connectedness of age-flags and calculated means, even after all the CSVs are extracted from Squid3 and moved on to the next step in their processing.

[Jim: as per the above, I'd like a similar mechanism covering calibration constant(s) for the Reference Material analyses — which we didn't talk about! In theory, it should be simpler: there is no "N/A" status, because every calibration-constant value is either "included" or "excluded" from its weighted mean calculation, at all times. I guess it is just a matter of polling those statuses and writing them to automated "calibration-flag" columns at reporting-time.]

[bowring]

@sbodorkos - I am not sure where we stand here ... do you want to make issues to get the ball rolling?

[sbodorkos]

Thanks Jim @bowring - I still had the draft Issue sitting in Notepad++. Finished now and posted.