Test Data CSV transition

[SFJohnson24]

The dev team is seeking input from Rule Authors regarding the transition of test data/rules into github.
https://github.com/cdisc-org/cdisc-rules-engine/issues/1559#issuecomment-3952292326
@eljanssens @JozefAerts @mhungria @alexfurmenkov
We are currently discussing formatting of the CSV as we move the test data from sharepoint into github. We are debating the merits of a single file with the column metadata at the top:

STUDYID,DOMAIN,USUBJID,SUBJID,RFSTDTC,RFENDTC,RFXSTDTC,RFXENDTC,RFICDTC,RFPENDTC,DTHDTC,DTHFL,SITEID,BRTHDTC,AGE,AGEU,SEX,RACE,ETHNIC,ARMCD,ARM,ACTARMCD,ACTARM,ARMNRS,ACTARMUD
Study Identifier,Domain Abbreviation,Unique Subject Identifier,Subject Identifier for the Study,Subject Reference Start Date/Time,Subject Reference End Date/Time,Date/Time of First Study Treatment,Date/Time of Last Study Treatment,Date/Time of Informed Consent,Date/Time of End of Participation,Date/Time of Death,Subject Death Flag,Study Site Identifier,Date/Time of Birth,Age,Age Units,Sex,Race,Ethnicity,Planned Arm Code,Description of Planned Arm,Actual Arm Code,Description of Actual Arm,Reason Arm and/or Actual Arm is Null,Description of Unplanned Actual Arm
char,char,char,char,char,char,char,char,char,char,char,char,char,char,num,char,char,char,char,char,char,char,char,char,char
12,2,8,4,10,10,10,10,10,10,10,1,3,10,8,5,1,41,22,8,28,8,28,14,200
STUDYID,DOMAIN,USUBJID,SUBJID,RFSTDTC,RFENDTC,RFXSTDTC,RFXENDTC,RFICDTC,RFPENDTC,DTHDTC,DTHFL,SITEID,BRTHDTC,AGE,AGEU,SEX,RACE,ETHNIC,ARMCD,ARM,ACTARMCD,ACTARM,ARMNRS,ACTARMUD,COUNTRY
CDISCPILOT01,DM,CDISC001,1115,2012-11-30,2013-01-23,2012-11-30,2013-01-23,2012-11-23,2013-05-20,,,701,1928,84,YEARS,M,WHITE,NOT HISPANIC OR LATINO,ZAN_LOW,Zanomaline Low Dose (54 mg),ZAN_LOW,Zanomaline Low Dose (54 mg),,,USA
CDISCPILOT01,DM,CDISC002,1211,2012-11-15,2013-01-14,2012-11-15,2013-01-12,2012-10-30,2013-01-14,2013-01-14,Y,701,1936,76,YEARS,F,WHITE,NOT HISPANIC OR LATINO,ZAN_LOW,Zanomaline Low Dose (54 mg),ZAN_LOW,Zanomaline Low Dose (54 mg),,,USA
CDISCPILOT01,DM,CDISC003,1302,2013-08-29,2013-11-05,2013-08-29,2013-11-05,2013-08-20,2014-02-13,,,701,1951,61,YEARS,M,WHITE,NOT HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA
CDISCPILOT01,DM,CDISC004,1345,2013-10-08,2014-03-18,2013-10-08,2014-03-18,2013-10-01,2014-03-18,,,701,1950,63,YEARS,F,WHITE,NOT HISPANIC OR LATINO,PLACEBO,Placebo,PLACEBO,Placebo,,,USA
CDISCPILOT01,DM,CDISC005,1383,2013-02-04,2013-08-06,2013-02-04,2013-08-06,2013-01-22,2013-08-06,,,701,1941,72,YEARS,F,WHITE,NOT HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA
CDISCPILOT01,DM,CDISC006,1429,2013-03-19,2013-04-30,2013-03-19,2013-04-30,2013-02-25,2013-04-30,,,701,1929,84,YEARS,F,WHITE,NOT HISPANIC OR LATINO,ZAN_LOW,Zanomaline Low Dose (54 mg),ZAN_LOW,Zanomaline Low Dose (54 mg),,,USA
CDISCPILOT01,DM,CDISC007,1444,2013-01-05,2013-02-13,2013-01-05,2013-02-12,2012-12-31,2013-06-20,,,701,1949,63,YEARS,M,WHITE,HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA
CDISCPILOT01,DM,CDISC008,1445,2014-05-11,2014-11-01,2014-05-11,2014-11-01,2014-05-01,2014-11-01,2014-11-01,Y,704,1939,75,YEARS,M,MULTIPLE,NOT HISPANIC OR LATINO,PLACEBO,Placebo,PLACEBO,Placebo,,,USA
CDISCPILOT01,DM,CDISC009,1087,2012-10-22,2013-04-28,2012-10-22,2013-04-28,2012-10-06,2013-04-28,,,708,1938,74,YEARS,F,WHITE,NOT HISPANIC OR LATINO,PLACEBO,Placebo,PLACEBO,Placebo,,,USA
CDISCPILOT01,DM,CDISC010,1236,2013-09-21,2013-09-26,2013-09-21,2013-09-21,2013-09-08,2013-09-26,,,708,1927,86,YEARS,F,WHITE,NOT HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA
CDISCPILOT01,DM,CDISC011,1336,2012-12-07,2013-06-05,2012-12-07,2013-06-05,2012-11-21,2013-07-05,,,708,1939,73,YEARS,M,WHITE,NOT HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA
CDISCPILOT01,DM,CDISC012,1378,2013-09-03,2014-01-28,2013-09-03,2014-01-28,2013-08-24,2014-01-28,,,708,1946,67,YEARS,M,BLACK OR AFRICAN AMERICAN,NOT HISPANIC OR LATINO,PLACEBO,Placebo,PLACEBO,Placebo,,,USA
CDISCPILOT01,DM,CDISC013,1083,2013-07-22,2013-08-03,2013-07-22,2013-08-01,2013-07-09,2013-08-03,2013-08-03,Y,710,1924,89,YEARS,F,WHITE,NOT HISPANIC OR LATINO,PLACEBO,Placebo,PLACEBO,Placebo,,,USA
CDISCPILOT01,DM,CDISC014,1012,2013-04-03,2013-05-02,2013-04-03,2013-04-29,2013-03-20,2013-09-18,,,711,1945,67,YEARS,F,WHITE,NOT HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA
CDISCPILOT01,DM,CDISC015,1022,,,,,2014-03-17,2014-03-17,,,711,1928,86,YEARS,F,WHITE,NOT HISPANIC OR LATINO,,,,,SCREEN FAILURE,,USA
CDISCPILOT01,DM,CDISC016,1143,2013-04-03,2013-06-01,2013-04-03,2013-05-30,2013-03-30,2013-09-22,,,711,1936,76,YEARS,F,WHITE,NOT HISPANIC OR LATINO,ZAN_LOW,Zanomaline Low Dose (54 mg),ZAN_LOW,Zanomaline Low Dose (54 mg),,,USA
CDISCPILOT01,DM,CDISC017,1250,2013-09-21,2014-02-08,2013-09-21,2014-01-31,2013-08-21,2014-03-08,,,718,1931,82,YEARS,F,WHITE,HISPANIC OR LATINO,ZAN_LOW,Zanomaline Low Dose (54 mg),ZAN_LOW,Zanomaline Low Dose (54 mg),,,USA
CDISCPILOT01,DM,CDISC018,1427,2012-12-17,2013-02-18,2012-12-17,2013-02-11,2012-12-13,2013-06-03,,,718,1938,74,YEARS,F,BLACK OR AFRICAN AMERICAN,NOT HISPANIC OR LATINO,ZAN_HIGH,Zanomaline High Dose (81 mg),ZAN_HIGH,Zanomaline High Dose (81 mg),,,USA

versus a metadata csv file with 1 variable per row separate from the data csv file. It would look something like this:

dataset,variable,label,type,length
dm.xpt,STUDYID,Study Identifier,Char,200
dm.xpt,DOMAIN,Domain Abbreviation,Char,2
dm.xpt,USUBJID,Unique Subject Identifier,Char,200
dm.xpt,SUBJID,Subject Identifier for the Study,Char,40
dm.xpt,RFSTDTC,Subject Reference Start Date/Time,Char,20
pp.xpt,STUDYID,Study Identifier,Char,200
pp.xpt,DOMAIN,Domain Abbreviation,Char,2
pp.xpt,USUBJID,Unique Subject Identifier,Char,200
pp.xpt,PPSEQ,Sequence Number,Num,8
pp.xpt,PPGRPID,Group ID,Char,40
pp.xpt,PPTESTCD,Parameter Short Name,Char,8
pp.xpt,PPTEST,Parameter Name,Char,40

@gerrycampion pointed out that most csv tools (and vscode extensions) expect a single row as a header for csv files, so it messes up the filtering and sorting. Also, the rfc says "an optional header line"

We would love input on the preferred formatting to help facilitate authoring

[mhungria]

@SFJohnson24 , dev team: I vote for "a metadata csv file with 1 variable per row separate from the data csv file.".

[SFJohnson24]

this is how the dm with metadata option looks using CSV editor from the excel viewer extension. @eljanssens this seems to be what you suggested on slack--easy to edit/alter the csv in columnar format https://marketplace.visualstudio.com/items?itemName=GrapeCity.gc-excelviewer

[SFJohnson24]

@mhungria we are working to move away from editor and sharepoint. This would mean our rules/test data would be hosted on github and you would work on authoring outside of editor so this csv design does not involve viewing the test data within editor.

[mhungria]

@SFJohnson24 , sorry I forgot that the rule editor is getting decommissioned. As a rule author, I would still prefer to keep the data csv separate from the metadata csv. It allows for easier integration with other software tools.

[mhungria]

Very simple to update either of the two components:

[eljanssens]

@SFJohnson24 thanks. I do prefer what you show as a rule author. Everything in 1 workbook (data, variables and metadata). That's easier to adapt for a rule author. I would avoid having to open 2 workbooks. Can this viewer also highlight cells? For the verisian github for now, the cells are hightlighted and there is a validation sheet that needs to be completed (just an additional sheet in the workbook) where you predefine the issues you expect so that the automated testing can tell if this is indeed also the output. If we have that sheet, then the highlighting might not be needed, it would just help the rule author think and transfer correctly to the validation sheet. I'll show it on Tuesday :-).

[SFJohnson24]

@eljanssens it appears to only allow text editing, not highlighting. I tried a few extensions and none of them seem to allow highlighting even using the ctrl+alt+h

[JozefAerts]

I have no real preference, I can easily handle both.
Moving away from Excel is good - I regularly had problems during testing because of "hidden blanks" in the Excel. Sometimes made me crazy ...